C#工具类SqlServerHelper，基于System.Data.SqlClient封装
源码：
1 using System; 2 using System.Collections.Generic; 3 using System.Data; 4 using System.Linq; 5 using System.Text; 6 using System.Threading.Tasks; 7 using System.Data.SqlClient; 8 9 namespace Fly.Util.DataBase 10 { 11 /// <summary> 12 /// SqlServer数据库操作类 13 /// </summary> 14 public static class SqlServerHelper 15 { 16 /// <summary> 17 /// 执行数据库非查询操作,返回受影响的行数 18 /// </summary> 19 /// <param name="connectionString">数据库连接字符串</param> 20 /// <param name="cmdType">命令的类型</param> 21 /// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param> 22 /// <param name="cmdParms">命令参数集合</param> 23 /// <returns>当前操作影响的数据行数</returns> 24 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 25 { 26 SqlCommand cmd = new SqlCommand(); 27 using (SqlConnection conn = new SqlConnection(connectionString)) 28 { 29 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 30 int val = cmd.ExecuteNonQuery(); 31 cmd.Parameters.Clear(); 32 return val; 33 } 34 } 35 36 /// <summary> 37 /// 执行数据库事务非查询操作,返回受影响的行数 38 /// </summary> 39 /// <param name="transaction">数据库事务对象</param> 40 /// <param name="cmdType">Command类型</param> 41 /// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param> 42 /// <param name="cmdParms">命令参数集合</param> 43 /// <returns>当前事务操作影响的数据行数</returns> 44 public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 45 { 46 SqlCommand cmd = new SqlCommand(); 47 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms); 48 int val = cmd.ExecuteNonQuery(); 49 cmd.Parameters.Clear(); 50 return val; 51 } 52 53 /// <summary> 54 /// 执行数据库非查询操作,返回受影响的行数 55 /// </summary> 56 /// <param name="connection">SqlServer数据库连接对象</param> 57 /// <param name="cmdType">Command类型</param> 58 /// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param> 59 /// <param name="cmdParms">命令参数集合</param> 60 /// <returns>当前操作影响的数据行数</returns> 61 public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 62 { 63 if (connection == null) 64 throw new ArgumentNullException("当前数据库连接不存在"); 65 SqlCommand cmd = new SqlCommand(); 66 PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms); 67 int val = cmd.ExecuteNonQuery(); 68 cmd.Parameters.Clear(); 69 return val; 70 } 71 72 /// <summary> 73 /// 执行数据库查询操作,返回SqlDataReader类型的内存结果集 74 /// </summary> 75 /// <param name="connectionString">数据库连接字符串</param> 76 /// <param name="cmdType">命令的类型</param> 77 /// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param> 78 /// <param name="cmdParms">命令参数集合</param> 79 /// <returns>当前查询操作返回的SqlDataReader类型的内存结果集</returns> 80 public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 81 { 82 SqlCommand cmd = new SqlCommand(); 83 SqlConnection conn = new SqlConnection(connectionString); 84 try 85 { 86 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 87 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 88 cmd.Parameters.Clear(); 89 return reader; 90 } 91 catch 92 { 93 cmd.Dispose(); 94 conn.Close(); 95 throw; 96 } 97 } 98 99 /// <summary> 100 /// 执行数据库查询操作,返回DataSet类型的结果集 101 /// </summary> 102 /// <param name="connectionString">数据库连接字符串</param> 103 /// <param name="cmdType">命令的类型</param> 104 /// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param> 105 /// <param name="cmdParms">命令参数集合</param> 106 /// <returns>当前查询操作返回的DataSet类型的结果集</returns> 107 public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 108 { 109 SqlCommand cmd = new SqlCommand(); 110 SqlConnection conn = new SqlConnection(connectionString); 111 DataSet ds = null; 112 try 113 { 114 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 115 SqlDataAdapter adapter = new SqlDataAdapter(); 116 adapter.SelectCommand = cmd; 117 ds = new DataSet(); 118 adapter.Fill(ds); 119 cmd.Parameters.Clear(); 120 } 121 catch 122 { 123 throw; 124 } 125 finally 126 { 127 cmd.Dispose(); 128 conn.Close(); 129 conn.Dispose(); 130 } 131 132 return ds; 133 } 134 135 /// <summary> 136 /// 执行数据库查询操作,返回DataTable类型的结果集 137 /// </summary> 138 /// <param name="connectionString">数据库连接字符串</param> 139 /// <param name="cmdType">命令的类型</param> 140 /// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param> 141 /// <param name="cmdParms">命令参数集合</param> 142 /// <returns>当前查询操作返回的DataTable类型的结果集</returns> 143 public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 144 { 145 SqlCommand cmd = new SqlCommand(); 146 SqlConnection conn = new SqlConnection(connectionString); 147 DataTable dt = null; 148 149 try 150 { 151 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 152 SqlDataAdapter adapter = new SqlDataAdapter(); 153 adapter.SelectCommand = cmd; 154 dt = new DataTable(); 155 adapter.Fill(dt); 156 cmd.Parameters.Clear(); 157 } 158 catch 159 { 160 throw; 161 } 162 finally 163 { 164 cmd.Dispose(); 165 conn.Close(); 166 conn.Dispose(); 167 } 168 169 return dt; 170 } 171 172 /// <summary> 173 /// 执行数据库查询操作,返回结果集中位于第一行第一列的Object类型的值 174 /// </summary> 175 /// <param name="connectionString">数据库连接字符串</param> 176 /// <param name="cmdType">命令的类型</param> 177 /// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param> 178 /// <param name="cmdParms">命令参数集合</param> 179 /// <returns>当前查询操作返回的结果集中位于第一行第一列的Object类型的值</returns> 180 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 181 { 182 SqlCommand cmd = new SqlCommand(); 183 SqlConnection conn = new SqlConnection(connectionString); 184 object result = null; 185 try 186 { 187 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 188 result = cmd.ExecuteScalar(); 189 cmd.Parameters.Clear(); 190 } 191 catch 192 { 193 throw; 194 } 195 finally 196 { 197 cmd.Dispose(); 198 conn.Close(); 199 conn.Dispose(); 200 } 201 202 return result; 203 } 204 205 /// <summary> 206 /// 执行数据库事务查询操作,返回结果集中位于第一行第一列的Object类型的值 207 /// </summary> 208 /// <param name="trans">一个已存在的数据库事务对象</param> 209 /// <param name="commandType">命令类型</param> 210 /// <param name="commandText">SqlServer存储过程名称或PL/SQL命令</param> 211 /// <param name="cmdParms">命令参数集合</param> 212 /// <returns>当前事务查询操作返回的结果集中位于第一行第一列的Object类型的值</returns> 213 public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 214 { 215 if (trans == null) 216 throw new ArgumentNullException("当前数据库事务不存在"); 217 SqlConnection conn = trans.Connection; 218 if (conn == null) 219 throw new ArgumentException("当前事务所在的数据库连接不存在"); 220 221 SqlCommand cmd = new SqlCommand(); 222 object result = null; 223 224 try 225 { 226 PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms); 227 result = cmd.ExecuteScalar(); 228 cmd.Parameters.Clear(); 229 } 230 catch 231 { 232 throw; 233 } 234 finally 235 { 236 trans.Dispose(); 237 cmd.Dispose(); 238 conn.Close(); 239 conn.Dispose(); 240 } 241 242 return result; 243 } 244 245 /// <summary> 246 /// 执行数据库查询操作,返回结果集中位于第一行第一列的Object类型的值 247 /// </summary> 248 /// <param name="conn">数据库连接对象</param> 249 /// <param name="cmdType">Command类型</param> 250 /// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param> 251 /// <param name="cmdParms">命令参数集合</param> 252 /// <returns>当前查询操作返回的结果集中位于第一行第一列的Object类型的值</returns> 253 public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 254 { 255 if (conn == null) throw new ArgumentException("当前数据库连接不存在"); 256 SqlCommand cmd = new SqlCommand(); 257 object result = null; 258 259 try 260 { 261 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 262 result = cmd.ExecuteScalar(); 263 cmd.Parameters.Clear(); 264 } 265 catch 266 { 267 throw; 268 } 269 finally 270 { 271 cmd.Dispose(); 272 conn.Close(); 273 conn.Dispose(); 274 } 275 276 return result; 277 } 278 279 /// <summary> 280 /// 执行存储过程 281 /// </summary> 282 /// <param name="connection">SqlServer数据库连接对象</param> 283 /// <param name="storedProcName">存储过程名</param> 284 /// <param name="parameters">存储过程参数</param> 285 /// <returns>SqlDataReader对象</returns> 286 public static SqlDataReader RunStoredProcedure(SqlConnection connection, string storedProcName, IDataParameter[] parameters) 287 { 288 SqlDataReader returnReader = null; 289 connection.Open(); 290 SqlCommand command = BuildSqlCommand(connection, storedProcName, parameters); 291 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); 292 return returnReader; 293 } 294 295 296 /// <summary> 297 /// 执行数据库命令前的准备工作 298 /// </summary> 299 /// <param name="cmd">Command对象</param> 300 /// <param name="conn">数据库连接对象</param> 301 /// <param name="trans">事务对象</param> 302 /// <param name="cmdType">Command类型</param> 303 /// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param> 304 /// <param name="cmdParms">命令参数集合</param> 305 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) 306 { 307 if (conn.State != ConnectionState.Open) 308 conn.Open(); 309 310 cmd.Connection = conn; 311 cmd.CommandText = cmdText; 312 313 if (trans != null) 314 cmd.Transaction = trans; 315 316 cmd.CommandType = cmdType; 317 318 if (cmdParms != null) 319 { 320 foreach (SqlParameter parm in cmdParms) 321 cmd.Parameters.Add(parm); 322 } 323 } 324 325 /// <summary> 326 /// 构建SqlCommand对象 327 /// </summary> 328 /// <param name="connection">数据库连接</param> 329 /// <param name="storedProcName">存储过程名</param> 330 /// <param name="parameters">存储过程参数</param> 331 /// <returns>SqlCommand</returns> 332 private static SqlCommand BuildSqlCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) 333 { 334 SqlCommand command = new SqlCommand(storedProcName, connection); 335 command.CommandType = CommandType.StoredProcedure; 336 foreach (SqlParameter parameter in parameters) 337 { 338 command.Parameters.Add(parameter); 339 } 340 return command; 341 } 342 } 343 }
