C# 操作数据库常用的 SqlDbHelper
asp.net 项目基本上都是有数据库服务支持的，这就需要有一个比较常用的类支持文件。闲话不多说，直接上代码
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Data.SqlClient; 6 using System.Data; 7 using System.Configuration; 8 9 namespace ImportExcel 10 { 11 public class SqlDbHelper 12 { 13 /// <summary> 14 /// 连接字符串 15 /// </summary> 16 public static readonly string connectionString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString; 17 18 #region ExecuteNonQuery命令 19 /// <summary> 20 /// 对数据库执行增、删、改命令 21 /// </summary> 22 /// <param name="safeSql">T-Sql语句</param> 23 /// <returns>受影响的记录数</returns> 24 public static int ExecuteNonQuery(string safeSql) 25 { 26 using (SqlConnection Connection = new SqlConnection(connectionString)) 27 { 28 Connection.Open(); 29 SqlTransaction trans = Connection.BeginTransaction(); 30 try 31 { 32 SqlCommand cmd = new SqlCommand(safeSql, Connection); 33 cmd.Transaction = trans; 34 35 if (Connection.State != ConnectionState.Open) 36 { 37 Connection.Open(); 38 } 39 int result = cmd.ExecuteNonQuery(); 40 trans.Commit(); 41 return result; 42 } 43 catch 44 { 45 trans.Rollback(); 46 return 0; 47 } 48 } 49 } 50 51 /// <summary> 52 /// 对数据库执行增、删、改命令 53 /// </summary> 54 /// <param name="sql">T-Sql语句</param> 55 /// <param name="values">参数数组</param> 56 /// <returns>受影响的记录数</returns> 57 public static int ExecuteNonQuery(string sql, SqlParameter[] values) 58 { 59 using (SqlConnection Connection = new SqlConnection(connectionString)) 60 { 61 Connection.Open(); 62 SqlTransaction trans = Connection.BeginTransaction(); 63 try 64 { 65 SqlCommand cmd = new SqlCommand(sql, Connection); 66 cmd.Transaction = trans; 67 cmd.Parameters.AddRange(values); 68 if (Connection.State != ConnectionState.Open) 69 { 70 Connection.Open(); 71 } 72 int result = cmd.ExecuteNonQuery(); 73 trans.Commit(); 74 return result; 75 } 76 catch (Exception ex) 77 { 78 trans.Rollback(); 79 return 0; 80 } 81 } 82 } 83 #endregion 84 85 #region ExecuteScalar命令 86 /// <summary> 87 /// 查询结果集中第一行第一列的值 88 /// </summary> 89 /// <param name="safeSql">T-Sql语句</param> 90 /// <returns>第一行第一列的值</returns> 91 public static int ExecuteScalar(string safeSql) 92 { 93 using (SqlConnection Connection = new SqlConnection(connectionString)) 94 { 95 if (Connection.State != ConnectionState.Open) 96 Connection.Open(); 97 SqlCommand cmd = new SqlCommand(safeSql, Connection); 98 int result = Convert.ToInt32(cmd.ExecuteScalar()); 99 return result; 100 } 101 } 102 103 /// <summary> 104 /// 查询结果集中第一行第一列的值 105 /// </summary> 106 /// <param name="sql">T-Sql语句</param> 107 /// <param name="values">参数数组</param> 108 /// <returns>第一行第一列的值</returns> 109 public static int ExecuteScalar(string sql, SqlParameter[] values) 110 { 111 using (SqlConnection Connection = new SqlConnection(connectionString)) 112 { 113 if (Connection.State != ConnectionState.Open) 114 Connection.Open(); 115 SqlCommand cmd = new SqlCommand(sql, Connection); 116 cmd.Parameters.AddRange(values); 117 int result = Convert.ToInt32(cmd.ExecuteScalar()); 118 return result; 119 } 120 } 121 #endregion 122 123 #region ExecuteReader命令 124 /// <summary> 125 /// 创建数据读取器 126 /// </summary> 127 /// <param name="safeSql">T-Sql语句</param> 128 /// <param name="Connection">数据库连接</param> 129 /// <returns>数据读取器对象</returns> 130 public static SqlDataReader ExecuteReader(string safeSql, SqlConnection Connection) 131 { 132 if (Connection.State != ConnectionState.Open) 133 Connection.Open(); 134 SqlCommand cmd = new SqlCommand(safeSql, Connection); 135 SqlDataReader reader = cmd.ExecuteReader(); 136 return reader; 137 } 138 139 /// <summary> 140 /// 创建数据读取器 141 /// </summary> 142 /// <param name="sql">T-Sql语句</param> 143 /// <param name="values">参数数组</param> 144 /// <param name="Connection">数据库连接</param> 145 /// <returns>数据读取器</returns> 146 public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values, SqlConnection Connection) 147 { 148 if (Connection.State != ConnectionState.Open) 149 Connection.Open(); 150 SqlCommand cmd = new SqlCommand(sql, Connection); 151 cmd.Parameters.AddRange(values); 152 SqlDataReader reader = cmd.ExecuteReader(); 153 return reader; 154 } 155 #endregion 156 157 #region ExecuteDataTable命令 158 /// <summary> 159 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable 160 /// </summary> 161 /// <param name="type">命令类型(T-Sql语句或者存储过程)</param> 162 /// <param name="safeSql">T-Sql语句或者存储过程的名称</param> 163 /// <param name="values">参数数组</param> 164 /// <returns>结果集DataTable</returns> 165 public static DataTable ExecuteDataTable(CommandType type, string safeSql, params SqlParameter[] values) 166 { 167 using (SqlConnection Connection = new SqlConnection(connectionString)) 168 { 169 if (Connection.State != ConnectionState.Open) 170 Connection.Open(); 171 DataSet ds = new DataSet(); 172 SqlCommand cmd = new SqlCommand(safeSql, Connection); 173 cmd.CommandType = type; 174 SqlDataAdapter da = new SqlDataAdapter(cmd); 175 da.Fill(ds); 176 return ds.Tables[0]; 177 } 178 } 179 180 /// <summary> 181 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable 182 /// </summary> 183 /// <param name="safeSql">T-Sql语句</param> 184 /// <returns>结果集DataTable</returns> 185 public static DataTable ExecuteDataTable(string safeSql) 186 { 187 using (SqlConnection Connection = new SqlConnection(connectionString)) 188 { 189 if (Connection.State != ConnectionState.Open) 190 Connection.Open(); 191 DataSet ds = new DataSet(); 192 SqlCommand cmd = new SqlCommand(safeSql, Connection); 193 SqlDataAdapter da = new SqlDataAdapter(cmd); 194 try 195 { 196 da.Fill(ds); 197 } 198 catch (Exception ex) 199 { 200 201 } 202 return ds.Tables[0]; 203 } 204 } 205 206 /// <summary> 207 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable 208 /// </summary> 209 /// <param name="sql">T-Sql语句</param> 210 /// <param name="values">参数数组</param> 211 /// <returns>结果集DataTable</returns> 212 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] values) 213 { 214 using (SqlConnection Connection = new SqlConnection(connectionString)) 215 { 216 if (Connection.State != ConnectionState.Open) 217 Connection.Open(); 218 DataSet ds = new DataSet(); 219 SqlCommand cmd = new SqlCommand(sql, Connection); 220 cmd.CommandTimeout = 0; 221 cmd.Parameters.AddRange(values); 222 SqlDataAdapter da = new SqlDataAdapter(cmd); 223 da.Fill(ds); 224 return ds.Tables[0]; 225 } 226 } 227 #endregion 228 229 #region GetDataSet命令 230 /// <summary> 231 /// 取出数据 232 /// </summary> 233 /// <param name="safeSql">sql语句</param> 234 /// <param name="tabName">DataTable别名</param> 235 /// <param name="values"></param> 236 /// <returns></returns> 237 public static DataSet GetDataSet(string safeSql, string tabName, params SqlParameter[] values) 238 { 239 using (SqlConnection Connection = new SqlConnection(connectionString)) 240 { 241 if (Connection.State != ConnectionState.Open) 242 Connection.Open(); 243 DataSet ds = new DataSet(); 244 SqlCommand cmd = new SqlCommand(safeSql, Connection); 245 246 if (values != null) 247 cmd.Parameters.AddRange(values); 248 249 SqlDataAdapter da = new SqlDataAdapter(cmd); 250 try 251 { 252 da.Fill(ds, tabName); 253 } 254 catch (Exception ex) 255 { 256 257 } 258 return ds; 259 } 260 } 261 #endregion 262 263 #region ExecureData 命令 264 /// <summary> 265 /// 批量修改数据 266 /// </summary> 267 /// <param name="ds">修改过的DataSet</param> 268 /// <param name="strTblName">表名</param> 269 /// <returns></returns> 270 public static int ExecureData(DataSet ds, string strTblName) 271 { 272 try 273 { 274 //创建一个数据库连接 275 using (SqlConnection Connection = new SqlConnection(connectionString)) 276 { 277 if (Connection.State != ConnectionState.Open) 278 Connection.Open(); 279 280 //创建一个用于填充DataSet的对象 281 SqlCommand myCommand = new SqlCommand("SELECT * FROM " + strTblName, Connection); 282 SqlDataAdapter myAdapter = new SqlDataAdapter(); 283 //获取SQL语句，用于在数据库中选择记录 284 myAdapter.SelectCommand = myCommand; 285 286 //自动生成单表命令，用于将对DataSet所做的更改与数据库更改相对应 287 SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter); 288 289 return myAdapter.Update(ds, strTblName); //更新ds数据 290 } 291 292 } 293 catch (Exception err) 294 { 295 throw err; 296 } 297 } 298 299 #endregion 300 } 301 }
