C#操作数据库常用的SqlDbHelper

网站建设1700 更新时间：2025-06-08 04:39:53

2023年6月21日发(作者：)

C#操作数据库常⽤的项⽬基本上都是有数据库服务⽀持的，这就需要有⼀个⽐较常⽤的类⽀持⽂件。闲话不多说，直接上代码 1 using System; 2 using c; 3 using ; 4 using ; 5 using ent; 6 using ; 7 using uration; 8

9 namespace ImportExcel 10 { 11 public class SqlDbHelper 12 { 13 ///

14 /// 连接字符串

15 ///

16 public static readonly string connectionString = tionStrings["ConnString"].ConnectionString; 17

18 #region ExecuteNonQuery命令 19 ///

20 /// 对数据库执⾏增、删、改命令

21 ///

22 /// T-Sql语句

23 /// 受影响的记录数

24 public static int ExecuteNonQuery(string safeSql) 25 { 26 using (SqlConnection Connection = new SqlConnection(connectionString)) 27 { 28 (); 29 SqlTransaction trans = ransaction(); 30 try 31 { 32 SqlCommand cmd = new SqlCommand(safeSql, Connection); 33 ction = trans; 34

35 if ( != ) 36 { 37 (); 38 } 39 int result = eNonQuery(); 40 (); 41 return result; 42 } 43 catch 44 { 45 ck(); 46 return 0; 47 } 48 } 49 } 50

51 ///

52 /// 对数据库执⾏增、删、改命令

53 ///

54 /// T-Sql语句

55 /// 参数数组

56 /// 受影响的记录数

57 public static int ExecuteNonQuery(string sql, SqlParameter[] values) 58 { 59 using (SqlConnection Connection = new SqlConnection(connectionString)) 60 { 61 (); 62 SqlTransaction trans = ransaction(); 63 try 64 { 65 SqlCommand cmd = new SqlCommand(sql, Connection); 66 ction = trans; 67 ge(values); 68 if ( != ) 69 { 70 (); 71 } 72 int result = eNonQuery(); 73 (); 74 return result; 75 } 76 catch (Exception ex) 77 { 78 ck(); 79 return 0; 80 } 81 } 82 } 83 #endregion 84

85 #region ExecuteScalar命令 86 ///

87 /// 查询结果集中第⼀⾏第⼀列的值

88 ///

89 /// T-Sql语句

90 /// 第⼀⾏第⼀列的值

91 public static int ExecuteScalar(string safeSql) 92 { 93 using (SqlConnection Connection = new SqlConnection(connectionString)) 94 { 95 if ( != ) 96 (); 97 SqlCommand cmd = new SqlCommand(safeSql, Connection); 98 int result = 32(eScalar()); 99 return result;100 }101 }102

103 ///

104 /// 查询结果集中第⼀⾏第⼀列的值

105 ///

106 /// T-Sql语句

107 /// 参数数组

108 /// 第⼀⾏第⼀列的值

109 public static int ExecuteScalar(string sql, SqlParameter[] values)110 {111 using (SqlConnection Connection = new SqlConnection(connectionString))112 {113 if ( != )114 ();115 SqlCommand cmd = new SqlCommand(sql, Connection);116 ge(values);117 int result = 32(eScalar());118 return result;119 }120 }121 #endregion122

123 #region ExecuteReader命令124 ///

125 /// 创建数据读取器

126 ///

127 /// T-Sql语句

128 /// 数据库连接

129 /// 数据读取器对象

130 public static SqlDataReader ExecuteReader(string safeSql, SqlConnection Connection)131 {132 if ( != )133 ();134 SqlCommand cmd = new SqlCommand(safeSql, Connection);135 SqlDataReader reader = eReader();136 return reader;137 }138

139 ///

140 /// 创建数据读取器

141 ///

142 /// T-Sql语句

143 /// 参数数组

144 /// 数据库连接

145 /// 数据读取器

146 public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values, SqlConnection Connection)147 {148 if ( != )149 ();150 SqlCommand cmd = new SqlCommand(sql, Connection);151 ge(values);152 SqlDataReader reader = eReader();153 return reader;154 }155 #endregion156

157 #region ExecuteDataTable命令158 ///

159 /// 执⾏指定数据库连接对象的命令,指定存储过程参数,返回DataTable

160 ///

161 /// 命令类型(T-Sql语句或者存储过程)

162 /// T-Sql语句或者存储过程的名称 163 /// 参数数组

164 /// 结果集DataTable

165 public static DataTable ExecuteDataTable(CommandType type, string safeSql, params SqlParameter[] values)166 {167 using (SqlConnection Connection = new SqlConnection(connectionString))168 {169 if ( != )170 ();171 DataSet ds = new DataSet();172 SqlCommand cmd = new SqlCommand(safeSql, Connection);173 dType = type;174 SqlDataAdapter da = new SqlDataAdapter(cmd);175 (ds);176 return [0];177 }178 }179

180 ///

181 /// 执⾏指定数据库连接对象的命令,指定存储过程参数,返回DataTable

182 ///

183 /// T-Sql语句

184 /// 结果集DataTable

185 public static DataTable ExecuteDataTable(string safeSql)186 {187 using (SqlConnection Connection = new SqlConnection(connectionString))188 {189 if ( != )190 ();191 DataSet ds = new DataSet();192 SqlCommand cmd = new SqlCommand(safeSql, Connection);193 SqlDataAdapter da = new SqlDataAdapter(cmd);194 try195 {196 (ds);197 }198 catch (Exception ex)199 {200

201 }202 return [0];203 }204 }205

206 ///

207 /// 执⾏指定数据库连接对象的命令,指定存储过程参数,返回DataTable

208 ///

209 /// T-Sql语句

210 /// 参数数组

211 /// 结果集DataTable

212 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] values)213 {214 using (SqlConnection Connection = new SqlConnection(connectionString))215 {216 if ( != )217 ();218 DataSet ds = new DataSet();219 SqlCommand cmd = new SqlCommand(sql, Connection);220 dTimeout = 0;221 ge(values);222 SqlDataAdapter da = new SqlDataAdapter(cmd);223 (ds);224 return [0];225 }226 }227 #endregion228

229 #region GetDataSet命令230 ///

231 /// 取出数据

232 ///

233 /// sql语句

234 /// DataTable别名

235 ///

236 ///

237 public static DataSet GetDataSet(string safeSql, string tabName, params SqlParameter[] values)238 {239 using (SqlConnection Connection = new SqlConnection(connectionString))240 {241 if ( != )242 ();243 DataSet ds = new DataSet();244 SqlCommand cmd = new SqlCommand(safeSql, Connection);245

246 if (values != null)247 ge(values);248

249 SqlDataAdapter da = new SqlDataAdapter(cmd);250 try251 {252 (ds, tabName);253 }254 catch (Exception ex)255 {256

257 }258 return ds;259 }260 }261 #endregion262

263 #region ExecureData 命令264 ///

265 /// 批量修改数据

266 ///

267 /// 修改过的DataSet

268 /// 表名

269 ///

270 public static int ExecureData(DataSet ds, string strTblName)271 {272 try273 {274 //创建⼀个数据库连接

275 using (SqlConnection Connection = new SqlConnection(connectionString))276 {277 if ( != )278 ();279

280 //创建⼀个⽤于填充DataSet的对象

281 SqlCommand myCommand = new SqlCommand("SELECT * FROM " + strTblName, Connection);282 SqlDataAdapter myAdapter = new SqlDataAdapter();283 //获取SQL语句，⽤于在数据库中选择记录

284 Command = myCommand;285

286 //⾃动⽣成单表命令，⽤于将对DataSet所做的更改与数据库更改相对应

287 SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);288

289 return (ds, strTblName); //更新ds数据

290 }291

292 }293 catch (Exception err)294 {295 throw err;296 }297 }298

299 #endregion300 }301 }