mysql 数据库dbhelp_C# 操作数据库常用的 SqlDbHelper

最新推荐文章于 2023-11-07 19:56:11 发布

哦哦OK看看

最新推荐文章于 2023-11-07 19:56:11 发布

阅读量220

点赞数

文章标签： mysql 数据库dbhelp

本文链接：https://blog.csdn.net/weixin_29070861/article/details/114334777

版权

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Web;5 usingSystem.Data.SqlClient;6 usingSystem.Data;7 usingSystem.Configuration;8

9 namespaceImportExcel10 {11 public classSqlDbHelper12 {13 ///

14 ///连接字符串15 ///

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

18 #region ExecuteNonQuery命令

19 ///

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

22 /// T-Sql语句

23 /// 受影响的记录数

24 public static int ExecuteNonQuery(stringsafeSql)25 {26 using (SqlConnection Connection = newSqlConnection(connectionString))27 {28 Connection.Open();29 SqlTransaction trans =Connection.BeginTransaction();30 try

31 {32 SqlCommand cmd = newSqlCommand(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 returnresult;42 }43 catch

44 {45 trans.Rollback();46 return 0;47 }48 }49 }50

51 ///

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

54 /// T-Sql语句

55 /// 参数数组

56 /// 受影响的记录数

57 public static int ExecuteNonQuery(stringsql, SqlParameter[] values)58 {59 using (SqlConnection Connection = newSqlConnection(connectionString))60 {61 Connection.Open();62 SqlTransaction trans =Connection.BeginTransaction();63 try

64 {65 SqlCommand cmd = newSqlCommand(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 returnresult;75 }76 catch(Exception ex)77 {78 trans.Rollback();79 return 0;80 }81 }82 }83 #endregion

85 #region ExecuteScalar命令

86 ///

87 ///查询结果集中第一行第一列的值88 ///

89 /// T-Sql语句

90 /// 第一行第一列的值

91 public static int ExecuteScalar(stringsafeSql)92 {93 using (SqlConnection Connection = newSqlConnection(connectionString))94 {95 if (Connection.State !=ConnectionState.Open)96 Connection.Open();97 SqlCommand cmd = newSqlCommand(safeSql, Connection);98 int result =Convert.ToInt32(cmd.ExecuteScalar());99 returnresult;100 }101 }102

103 ///

104 ///查询结果集中第一行第一列的值105 ///

106 /// T-Sql语句

107 /// 参数数组

108 /// 第一行第一列的值

109 public static int ExecuteScalar(stringsql, SqlParameter[] values)110 {111 using (SqlConnection Connection = newSqlConnection(connectionString))112 {113 if (Connection.State !=ConnectionState.Open)114 Connection.Open();115 SqlCommand cmd = newSqlCommand(sql, Connection);116 cmd.Parameters.AddRange(values);117 int result =Convert.ToInt32(cmd.ExecuteScalar());118 returnresult;119 }120 }121 #endregion

122

123 #region ExecuteReader命令

124 ///

125 ///创建数据读取器126 ///

127 /// T-Sql语句

128 /// 数据库连接

129 /// 数据读取器对象

130 public static SqlDataReader ExecuteReader(stringsafeSql, SqlConnection Connection)131 {132 if (Connection.State !=ConnectionState.Open)133 Connection.Open();134 SqlCommand cmd = newSqlCommand(safeSql, Connection);135 SqlDataReader reader =cmd.ExecuteReader();136 returnreader;137 }138

139 ///

140 ///创建数据读取器141 ///

142 /// T-Sql语句

143 /// 参数数组

144 /// 数据库连接

145 /// 数据读取器

146 public static SqlDataReader ExecuteReader(stringsql, SqlParameter[] values, SqlConnection Connection)147 {148 if (Connection.State !=ConnectionState.Open)149 Connection.Open();150 SqlCommand cmd = newSqlCommand(sql, Connection);151 cmd.Parameters.AddRange(values);152 SqlDataReader reader =cmd.ExecuteReader();153 returnreader;154 }155 #endregion

156

157 #region ExecuteDataTable命令

158 ///

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

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

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

163 /// 参数数组

164 /// 结果集DataTable

165 public static DataTable ExecuteDataTable(CommandType type, string safeSql, paramsSqlParameter[] values)166 {167 using (SqlConnection Connection = newSqlConnection(connectionString))168 {169 if (Connection.State !=ConnectionState.Open)170 Connection.Open();171 DataSet ds = newDataSet();172 SqlCommand cmd = newSqlCommand(safeSql, Connection);173 cmd.CommandType =type;174 SqlDataAdapter da = newSqlDataAdapter(cmd);175 da.Fill(ds);176 return ds.Tables[0];177 }178 }179

180 ///

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

183 /// T-Sql语句

184 /// 结果集DataTable

185 public static DataTable ExecuteDataTable(stringsafeSql)186 {187 using (SqlConnection Connection = newSqlConnection(connectionString))188 {189 if (Connection.State !=ConnectionState.Open)190 Connection.Open();191 DataSet ds = newDataSet();192 SqlCommand cmd = newSqlCommand(safeSql, Connection);193 SqlDataAdapter da = newSqlDataAdapter(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 ///

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

209 /// T-Sql语句

210 /// 参数数组

211 /// 结果集DataTable

212 public static DataTable ExecuteDataTable(string sql, paramsSqlParameter[] values)213 {214 using (SqlConnection Connection = newSqlConnection(connectionString))215 {216 if (Connection.State !=ConnectionState.Open)217 Connection.Open();218 DataSet ds = newDataSet();219 SqlCommand cmd = newSqlCommand(sql, Connection);220 cmd.CommandTimeout = 0;221 cmd.Parameters.AddRange(values);222 SqlDataAdapter da = newSqlDataAdapter(cmd);223 da.Fill(ds);224 return ds.Tables[0];225 }226 }227 #endregion

228

229 #region GetDataSet命令

230 ///

231 ///取出数据232 ///

233 /// sql语句

234 /// DataTable别名

235 ///

236 ///

237 public static DataSet GetDataSet(string safeSql, string tabName, paramsSqlParameter[] values)238 {239 using (SqlConnection Connection = newSqlConnection(connectionString))240 {241 if (Connection.State !=ConnectionState.Open)242 Connection.Open();243 DataSet ds = newDataSet();244 SqlCommand cmd = newSqlCommand(safeSql, Connection);245

246 if (values != null)247 cmd.Parameters.AddRange(values);248

249 SqlDataAdapter da = newSqlDataAdapter(cmd);250 try

251 {252 da.Fill(ds, tabName);253 }254 catch(Exception ex)255 {256

257 }258 returnds;259 }260 }261 #endregion

262

263 #region ExecureData 命令

264 ///

265 ///批量修改数据266 ///

267 /// 修改过的DataSet

268 /// 表名

269 ///

270 public static int ExecureData(DataSet ds, stringstrTblName)271 {272 try

273 {274 //创建一个数据库连接

275 using (SqlConnection Connection = newSqlConnection(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 = newSqlDataAdapter();283 //获取SQL语句，用于在数据库中选择记录

284 myAdapter.SelectCommand =myCommand;285

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

287 SqlCommandBuilder myCommandBuilder = newSqlCommandBuilder(myAdapter);288

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

290 }291

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

299 #endregion

300 }301 }