资料来源：不详，请见谅
简易型：
C# DBHelper Code
1 using System; 2 using System.Collections.Generic; 3 using System.Text; 4 using System.Data; 5 using System.Data.SqlClient; 6 using System.Configuration; 7 8 namespace ADODoNETDemo 9 { 10 /// <summary> 11 /// 针对SQL Server数据库操作的通用类 12 /// 作者：周公 13 /// 日期：2009-01-08 14 /// Version:1.0 15 /// </summary> 16 public class SqlDbHelper 17 { 18 private string connectionString; 19 /// <summary> 20 /// 设置数据库连接字符串 21 /// </summary> 22 public string ConnectionString 23 { 24 set { connectionString = value; } 25 } 26 /// <summary> 27 /// 构造函数 28 /// </summary> 29 public SqlDbHelper() 30 : this(ConfigurationManager.ConnectionStrings["Conn"].ConnectionString) 31 { 32 33 } 34 /// <summary> 35 /// 构造函数 36 /// </summary> 37 /// <param name="connectionString">数据库连接字符串</param> 38 public SqlDbHelper(string connectionString) 39 { 40 this.connectionString = connectionString; 41 } 42 /// <summary> 43 /// 执行一个查询，并返回结果集 44 /// </summary> 45 /// <param name="sql">要执行的查询SQL文本命令</param> 46 /// <returns>返回查询结果集</returns> 47 public DataTable ExecuteDataTable(string sql) 48 { 49 return ExecuteDataTable(sql, CommandType.Text, null); 50 } 51 /// <summary> 52 /// 执行一个查询,并返回查询结果 53 /// </summary> 54 /// <param name="sql">要执行的SQL语句</param> 55 /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 56 /// <returns>返回查询结果集</returns> 57 public DataTable ExecuteDataTable(string sql, CommandType commandType) 58 { 59 return ExecuteDataTable(sql, commandType, null); 60 } 61 /// <summary> 62 /// 执行一个查询,并返回查询结果 63 /// </summary> 64 /// <param name="sql">要执行的SQL语句</param> 65 /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 66 /// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param> 67 /// <returns></returns> 68 public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters) 69 { 70 DataTable data = new DataTable();//实例化DataTable，用于装载查询结果集 71 using (SqlConnection connection = new SqlConnection(connectionString)) 72 { 73 using (SqlCommand command = new SqlCommand(sql, connection)) 74 { 75 command.CommandType = commandType;//设置command的CommandType为指定的CommandType 76 //如果同时传入了参数，则添加这些参数 77 if (parameters != null) 78 { 79 foreach (SqlParameter parameter in parameters) 80 { 81 command.Parameters.Add(parameter); 82 } 83 } 84 //通过包含查询SQL的SqlCommand实例来实例化SqlDataAdapter 85 SqlDataAdapter adapter = new SqlDataAdapter(command); 86 87 adapter.Fill(data);//填充DataTable 88 } 89 } 90 return data; 91 } 92 /// <summary> 93 /// 94 /// </summary> 95 /// <param name="sql">要执行的查询SQL文本命令</param> 96 /// <returns></returns> 97 public SqlDataReader ExecuteReader(string sql) 98 { 99 return ExecuteReader(sql, CommandType.Text, null); 100 } 101 /// <summary> 102 /// 103 /// </summary> 104 /// <param name="sql">要执行的SQL语句</param> 105 /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 106 /// <returns></returns> 107 public SqlDataReader ExecuteReader(string sql, CommandType commandType) 108 { 109 return ExecuteReader(sql, commandType, null); 110 } 111 /// <summary> 112 /// 113 /// </summary> 114 /// <param name="sql">要执行的SQL语句</param> 115 /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 116 /// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param> 117 /// <returns></returns> 118 public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters) 119 { 120 SqlConnection connection = new SqlConnection(connectionString); 121 SqlCommand command = new SqlCommand(sql, connection); 122 //如果同时传入了参数，则添加这些参数 123 if (parameters != null) 124 { 125 foreach (SqlParameter parameter in parameters) 126 { 127 command.Parameters.Add(parameter); 128 } 129 } 130 connection.Open(); 131 //CommandBehavior.CloseConnection参数指示关闭Reader对象时关闭与其关联的Connection对象 132 return command.ExecuteReader(CommandBehavior.CloseConnection); 133 } 134 /// <summary> 135 /// 136 /// </summary> 137 /// <param name="sql">要执行的查询SQL文本命令</param> 138 /// <returns></returns> 139 public Object ExecuteScalar(string sql) 140 { 141 return ExecuteScalar(sql, CommandType.Text, null); 142 } 143 /// <summary> 144 /// 145 /// </summary> 146 /// <param name="sql">要执行的SQL语句</param> 147 /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 148 /// <returns></returns> 149 public Object ExecuteScalar(string sql, CommandType commandType) 150 { 151 return ExecuteScalar(sql, commandType, null); 152 } 153 /// <summary> 154 /// 155 /// </summary> 156 /// <param name="sql">要执行的SQL语句</param> 157 /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 158 /// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param> 159 /// <returns></returns> 160 public Object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters) 161 { 162 object result = null; 163 using (SqlConnection connection = new SqlConnection(connectionString)) 164 { 165 using (SqlCommand command = new SqlCommand(sql, connection)) 166 { 167 command.CommandType = commandType;//设置command的CommandType为指定的CommandType 168 //如果同时传入了参数，则添加这些参数 169 if (parameters != null) 170 { 171 foreach (SqlParameter parameter in parameters) 172 { 173 command.Parameters.Add(parameter); 174 } 175 } 176 connection.Open();//打开数据库连接 177 result = command.ExecuteScalar(); 178 } 179 } 180 return result;//返回查询结果的第一行第一列，忽略其它行和列 181 } 182 /// <summary> 183 /// 对数据库执行增删改操作 184 /// </summary> 185 /// <param name="sql">要执行的查询SQL文本命令</param> 186 /// <returns></returns> 187 public int ExecuteNonQuery(string sql) 188 { 189 return ExecuteNonQuery(sql, CommandType.Text, null); 190 } 191 /// <summary> 192 /// 对数据库执行增删改操作 193 /// </summary> 194 /// <param name="sql">要执行的SQL语句</param> 195 /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 196 /// <returns></returns> 197 public int ExecuteNonQuery(string sql, CommandType commandType) 198 { 199 return ExecuteNonQuery(sql, commandType, null); 200 } 201 /// <summary> 202 /// 对数据库执行增删改操作 203 /// </summary> 204 /// <param name="sql">要执行的SQL语句</param> 205 /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param> 206 /// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param> 207 /// <returns></returns> 208 public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters) 209 { 210 int count = 0; 211 using (SqlConnection connection = new SqlConnection(connectionString)) 212 { 213 using (SqlCommand command = new SqlCommand(sql, connection)) 214 { 215 command.CommandType = commandType;//设置command的CommandType为指定的CommandType 216 //如果同时传入了参数，则添加这些参数 217 if (parameters != null) 218 { 219 foreach (SqlParameter parameter in parameters) 220 { 221 command.Parameters.Add(parameter); 222 } 223 } 224 connection.Open();//打开数据库连接 225 count = command.ExecuteNonQuery(); 226 } 227 } 228 return count;//返回执行增删改操作之后，数据库中受影响的行数 229 } 230 /// <summary> 231 /// 返回当前连接的数据库中所有由用户创建的数据库 232 /// </summary> 233 /// <returns></returns> 234 public DataTable GetTables() 235 { 236 DataTable data = null; 237 using (SqlConnection connection = new SqlConnection(connectionString)) 238 { 239 connection.Open();//打开数据库连接 240 data = connection.GetSchema("Tables"); 241 } 242 return data; 243 } 244 245 } 246 }
复杂型：
C# DBUtility Code
1 using System; 2 using System.Collections; 3 using System.Collections.Specialized; 4 using System.Data; 5 using System.Data.SqlClient; 6 using System.Configuration; 7 using System.Data.Common; 8 using System.Collections.Generic; 9 10 using TL.DataAccess; 11 12 namespace TL.Business 13 { 14 public enum EffentNextType 15 { 16 /// <summary> 17 /// 对其他语句无任何影响 18 /// </summary> 19 None, 20 /// <summary> 21 /// 当前语句必须为"SELECT COUNT(1) FROM .."格式，如果存在则继续执行，不存在回滚事务 22 /// </summary> 23 WhenHaveContine, 24 /// <summary> 25 /// 当前语句必须为"SELECT COUNT(1) FROM .."格式，如果不存在则继续执行，存在回滚事务 26 /// </summary> 27 WhenNoHaveContine, 28 /// <summary> 29 /// 当前语句影响到的行数必须大于0，否则回滚事务 30 /// </summary> 31 ExcuteEffectRows, 32 /// <summary> 33 /// 引发事件-当前语句必须为"SELECT COUNT(1) FROM .."格式，如果不存在则继续执行，存在回滚事务 34 /// </summary> 35 SolicitationEvent 36 } 37 public class DBUtility 38 { 39 public object ShareObject = null; 40 public object OriginalData = null; 41 event EventHandler _solicitationEvent; 42 public event EventHandler SolicitationEvent 43 { 44 add 45 { 46 _solicitationEvent += value; 47 } 48 remove 49 { 50 _solicitationEvent -= value; 51 } 52 } 53 public void OnSolicitationEvent() 54 { 55 if (_solicitationEvent != null) 56 { 57 _solicitationEvent(this,new EventArgs()); 58 } 59 } 60 public string CommandText; 61 public System.Data.Common.DbParameter[] Parameters; 62 public EffentNextType EffentNextType = EffentNextType.None; 63 public DBUtility() 64 { 65 } 66 public DBUtility(SqlParameter[] para) 67 { 68 this.Parameters = para; 69 } 70 public DBUtility(string sqlText, SqlParameter[] para) 71 { 72 this.CommandText = sqlText; 73 this.Parameters = para; 74 } 75 public DBUtility(string sqlText, SqlParameter[] para, EffentNextType type) 76 { 77 this.CommandText = sqlText; 78 this.Parameters = para; 79 this.EffentNextType = type; 80 } 81 } 82 /// <summary> 83 /// 数据访问抽象基础类 84 /// Copyright (C) 2004-2008 By LiTianPing 85 /// </summary> 86 public class SQLHelper 87 { 88 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. 89 public string connectionString = ""; 90 public SQLHelper() 91 { 92 connectionString = @"DATA SOURCE=192.168.0.103,2433;UID=cdteam;PWD=cd-team2011_;DATABASE=TongLing"; 93 } 94 #region 公用方法 95 /// <summary> 96 /// 读取指定图片的二进制信息 97 /// </summary> 98 /// <param name="id"></param> 99 /// <returns></returns> 100 public object LoadImage(int id) 101 { 102 SqlConnection myConnection = new SqlConnection(connectionString); 103 SqlCommand myCommand = new SqlCommand("SELECT [bImage] FROM [tblCAIPIAO_NEWS_IMG] WHERE id=@id", myConnection); 104 myCommand.CommandType = CommandType.Text; 105 myCommand.Parameters.Add(new SqlParameter("@id", id)); 106 myConnection.Open(); 107 object result = myCommand.ExecuteScalar(); 108 try 109 { 110 return new System.IO.MemoryStream((byte[])result); 111 } 112 catch (ArgumentNullException e) 113 { 114 return null; 115 } 116 finally 117 { 118 myConnection.Close(); 119 } 120 } 121 /// <summary> 122 /// 判断是否存在某表的某个字段 123 /// </summary> 124 /// <param name="tableName">表名称</param> 125 /// <param name="columnName">列名称</param> 126 /// <returns>是否存在</returns> 127 public bool ColumnExists(string tableName, string columnName) 128 { 129 string sql = "select count(1) from syscolumns where id=object_id(" + tableName + ") and name=" + columnName + ""; 130 object res = GetSingle(sql); 131 if (res == null) 132 { 133 return false; 134 } 135 return Convert.ToInt32(res) > 0; 136 } 137 public int GetMaxID(string FieldName, string TableName) 138 { 139 string strsql = "select max(" + FieldName + ")+1 from " + TableName; 140 object obj = this.GetSingle(strsql); 141 if (obj == null) 142 { 143 return 1; 144 } 145 else 146 { 147 return int.Parse(obj.ToString()); 148 } 149 } 150 public bool Exists(string strSql) 151 { 152 object obj = this.GetSingle(strSql); 153 int cmdresult; 154 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 155 { 156 cmdresult = 0; 157 } 158 else 159 { 160 cmdresult = int.Parse(obj.ToString()); 161 } 162 if (cmdresult == 0) 163 { 164 return false; 165 } 166 else 167 { 168 return true; 169 } 170 } 171 /// <summary> 172 /// 表是否存在 173 /// </summary> 174 /// <param name="TableName"></param> 175 /// <returns></returns> 176 public bool TabExists(string TableName) 177 { 178 string strsql = "select count(*) from sysobjects where id = object_id(N[" + TableName + "]) and OBJECTPROPERTY(id, N[IsUserTable]) = 1"; 179 //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N"[dbo].[" + TableName + "]") AND type in (N"U")"; 180 object obj = this.GetSingle(strsql); 181 int cmdresult; 182 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 183 { 184 cmdresult = 0; 185 } 186 else 187 { 188 cmdresult = int.Parse(obj.ToString()); 189 } 190 if (cmdresult == 0) 191 { 192 return false; 193 } 194 else 195 { 196 return true; 197 } 198 } 199 public bool Exists(string strSql, params SqlParameter[] cmdParms) 200 { 201 object obj = this.GetSingle(strSql, cmdParms); 202 int cmdresult; 203 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 204 { 205 cmdresult = 0; 206 } 207 else 208 { 209 cmdresult = int.Parse(obj.ToString()); 210 } 211 if (cmdresult == 0) 212 { 213 return false; 214 } 215 else 216 { 217 return true; 218 } 219 } 220 #endregion 221 #region 执行简单SQL语句 222 /// <summary> 223 /// 执行SQL语句，返回影响的记录数 224 /// </summary> 225 /// <param name="SQLString">SQL语句</param> 226 /// <returns>影响的记录数</returns> 227 public int ExecuteSql(string SQLString) 228 { 229 using (SqlConnection connection = new SqlConnection(connectionString)) 230 { 231 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 232 { 233 try 234 { 235 connection.Open(); 236 int rows = cmd.ExecuteNonQuery(); 237 return rows; 238 } 239 catch (System.Data.SqlClient.SqlException e) 240 { 241 connection.Close(); 242 throw e; 243 } 244 } 245 } 246 } 247 public int ExecuteSqlByTime(string SQLString, int Times) 248 { 249 using (SqlConnection connection = new SqlConnection(connectionString)) 250 { 251 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 252 { 253 try 254 { 255 connection.Open(); 256 cmd.CommandTimeout = Times; 257 int rows = cmd.ExecuteNonQuery(); 258 return rows; 259 } 260 catch (System.Data.SqlClient.SqlException e) 261 { 262 connection.Close(); 263 throw e; 264 } 265 } 266 } 267 } 268 /// <summary> 269 /// 执行Sql和Oracle滴混合事务 270 /// </summary> 271 /// <param name="list">SQL命令行列表</param> 272 /// <param name="oracleCmdSqlList">Oracle命令行列表</param> 273 /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns> 274 public int ExecuteSqlTran(List<DBUtility> list, List<DBUtility> oracleCmdSqlList) 275 { 276 using (SqlConnection conn = new SqlConnection(connectionString)) 277 { 278 conn.Open(); 279 SqlCommand cmd = new SqlCommand(); 280 cmd.Connection = conn; 281 SqlTransaction tx = conn.BeginTransaction(); 282 cmd.Transaction = tx; 283 try 284 { 285 foreach (DBUtility myDE in list) 286 { 287 string cmdText = myDE.CommandText; 288 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; 289 PrepareCommand(cmd, conn, tx, cmdText, cmdParms); 290 if (myDE.EffentNextType == EffentNextType.SolicitationEvent) 291 { 292 if (myDE.CommandText.ToLower().IndexOf("count(") == -1) 293 { 294 tx.Rollback(); 295 throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式"); 296 //return 0; 297 } 298 object obj = cmd.ExecuteScalar(); 299 bool isHave = false; 300 if (obj == null && obj == DBNull.Value) 301 { 302 isHave = false; 303 } 304 isHave = Convert.ToInt32(obj) > 0; 305 if (isHave) 306 { 307 //引发事件 308 myDE.OnSolicitationEvent(); 309 } 310 } 311 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) 312 { 313 if (myDE.CommandText.ToLower().IndexOf("count(") == -1) 314 { 315 tx.Rollback(); 316 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式"); 317 //return 0; 318 } 319 object obj = cmd.ExecuteScalar(); 320 bool isHave = false; 321 if (obj == null && obj == DBNull.Value) 322 { 323 isHave = false; 324 } 325 isHave = Convert.ToInt32(obj) > 0; 326 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) 327 { 328 tx.Rollback(); 329 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0"); 330 //return 0; 331 } 332 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) 333 { 334 tx.Rollback(); 335 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0"); 336 //return 0; 337 } 338 continue; 339 } 340 int val = cmd.ExecuteNonQuery(); 341 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) 342 { 343 tx.Rollback(); 344 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行"); 345 //return 0; 346 } 347 cmd.Parameters.Clear(); 348 } 349 //string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC"); 350 //bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList); 351 //if (!res) 352 //{ 353 // tx.Rollback(); 354 // throw new Exception("Oracle执行失败"); 355 // return -1; 356 //} 357 tx.Commit(); 358 return 1; 359 } 360 catch (System.Data.SqlClient.SqlException e) 361 { 362 tx.Rollback(); 363 throw e; 364 } 365 catch (Exception e) 366 { 367 tx.Rollback(); 368 throw e; 369 } 370 } 371 } 372 /// <summary> 373 /// 执行多条SQL语句，实现数据库事务。 374 /// </summary> 375 /// <param name="SQLStringList">多条SQL语句</param> 376 public int ExecuteSqlTran(List<String> SQLStringList) 377 { 378 using (SqlConnection conn = new SqlConnection(connectionString)) 379 { 380 conn.Open(); 381 SqlCommand cmd = new SqlCommand(); 382 cmd.Connection = conn; 383 SqlTransaction tx = conn.BeginTransaction(); 384 cmd.Transaction = tx; 385 try 386 { 387 int count = 0; 388 for (int n = 0; n < SQLStringList.Count; n++) 389 { 390 string strsql = SQLStringList[n]; 391 if (strsql.Trim().Length > 1) 392 { 393 cmd.CommandText = strsql; 394 count += cmd.ExecuteNonQuery(); 395 } 396 } 397 tx.Commit(); 398 return count; 399 } 400 catch 401 { 402 tx.Rollback(); 403 return 0; 404 } 405 } 406 } 407 /// <summary> 408 /// 执行带一个存储过程参数的的SQL语句。 409 /// </summary> 410 /// <param name="SQLString">SQL语句</param> 411 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> 412 /// <returns>影响的记录数</returns> 413 public int ExecuteSql(string SQLString, string content) 414 { 415 using (SqlConnection connection = new SqlConnection(connectionString)) 416 { 417 SqlCommand cmd = new SqlCommand(SQLString, connection); 418 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); 419 myParameter.Value = content; 420 cmd.Parameters.Add(myParameter); 421 try 422 { 423 connection.Open(); 424 int rows = cmd.ExecuteNonQuery(); 425 return rows; 426 } 427 catch (System.Data.SqlClient.SqlException e) 428 { 429 throw e; 430 } 431 finally 432 { 433 cmd.Dispose(); 434 connection.Close(); 435 } 436 } 437 } 438 /// <summary> 439 /// 执行带一个存储过程参数的的SQL语句。 440 /// </summary> 441 /// <param name="SQLString">SQL语句</param> 442 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> 443 /// <returns>影响的记录数</returns> 444 public object ExecuteSqlGet(string SQLString, string content) 445 { 446 using (SqlConnection connection = new SqlConnection(connectionString)) 447 { 448 SqlCommand cmd = new SqlCommand(SQLString, connection); 449 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); 450 myParameter.Value = content; 451 cmd.Parameters.Add(myParameter); 452 try 453 { 454 connection.Open(); 455 object obj = cmd.ExecuteScalar(); 456 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 457 { 458 return null; 459 } 460 else 461 { 462 return obj; 463 } 464 } 465 catch (System.Data.SqlClient.SqlException e) 466 { 467 throw e; 468 } 469 finally 470 { 471 cmd.Dispose(); 472 connection.Close(); 473 } 474 } 475 } 476 /// <summary> 477 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) 478 /// </summary> 479 /// <param name="strSQL">SQL语句</param> 480 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> 481 /// <returns>影响的记录数</returns> 482 public int ExecuteSqlInsertImg(string strSQL, byte[] fs) 483 { 484 using (SqlConnection connection = new SqlConnection(connectionString)) 485 { 486 SqlCommand cmd = new SqlCommand(strSQL, connection); 487 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image); 488 myParameter.Value = fs; 489 cmd.Parameters.Add(myParameter); 490 try 491 { 492 connection.Open(); 493 int rows = cmd.ExecuteNonQuery(); 494 return rows; 495 } 496 catch (System.Data.SqlClient.SqlException e) 497 { 498 throw e; 499 } 500 finally 501 { 502 cmd.Dispose(); 503 connection.Close(); 504 } 505 } 506 } 507 /// <summary> 508 /// 执行一条计算查询结果语句，返回查询结果（object）。 509 /// </summary> 510 /// <param name="SQLString">计算查询结果语句</param> 511 /// <returns>查询结果（object）</returns> 512 public object GetSingle(string SQLString) 513 { 514 using (SqlConnection connection = new SqlConnection(connectionString)) 515 { 516 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 517 { 518 try 519 { 520 connection.Open(); 521 object obj = cmd.ExecuteScalar(); 522 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 523 { 524 return null; 525 } 526 else 527 { 528 return obj; 529 } 530 } 531 catch (System.Data.SqlClient.SqlException e) 532 { 533 connection.Close(); 534 throw e; 535 } 536 } 537 } 538 } 539 public object GetSingle(string SQLString, int Times) 540 { 541 using (SqlConnection connection = new SqlConnection(connectionString)) 542 { 543 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 544 { 545 try 546 { 547 connection.Open(); 548 cmd.CommandTimeout = Times; 549 object obj = cmd.ExecuteScalar(); 550 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 551 { 552 return null; 553 } 554 else 555 { 556 return obj; 557 } 558 } 559 catch (System.Data.SqlClient.SqlException e) 560 { 561 connection.Close(); 562 throw e; 563 } 564 } 565 } 566 } 567 /// <summary> 568 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) 569 /// </summary> 570 /// <param name="strSQL">查询语句</param> 571 /// <returns>SqlDataReader</returns> 572 public SqlDataReader ExecuteReader(string strSQL) 573 { 574 SqlConnection connection = new SqlConnection(connectionString); 575 SqlCommand cmd = new SqlCommand(strSQL, connection); 576 try 577 { 578 connection.Open(); 579 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 580 return myReader; 581 } 582 catch (System.Data.SqlClient.SqlException e) 583 { 584 throw e; 585 } 586 } 587 /// <summary> 588 /// 执行查询语句，返回DataSet 589 /// </summary> 590 /// <param name="SQLString">查询语句</param> 591 /// <returns>DataSet</returns> 592 public DataSet Query(string SQLString) 593 { 594 using (SqlConnection connection = new SqlConnection(connectionString)) 595 { 596 DataSet ds = new DataSet(); 597 try 598 { 599 connection.Open(); 600 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); 601 command.Fill(ds, "ds"); 602 } 603 catch (System.Data.SqlClient.SqlException ex) 604 { 605 throw new Exception(ex.Message); 606 } 607 return ds; 608 } 609 } 610 /// <summary> 611 /// 查询并得到数据集DataSet 612 /// </summary> 613 /// <param name="SQLString"></param> 614 /// <param name="Times"></param> 615 /// <returns></returns> 616 public DataSet Query(string SQLString, int Times) 617 { 618 using (SqlConnection connection = new SqlConnection(connectionString)) 619 { 620 DataSet ds = new DataSet(); 621 try 622 { 623 connection.Open(); 624 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); 625 command.SelectCommand.CommandTimeout = Times; 626 command.Fill(ds, "ds"); 627 } 628 catch (System.Data.SqlClient.SqlException ex) 629 { 630 throw new Exception(ex.Message); 631 } 632 return ds; 633 } 634 } 635 #endregion 636 #region 执行带参数的SQL语句 637 /// <summary> 638 /// 执行SQL语句，返回影响的记录数 639 /// </summary> 640 /// <param name="SQLString">SQL语句</param> 641 /// <returns>影响的记录数</returns> 642 public int ExecuteSql(string SQLString, params SqlParameter[] cmdParms) 643 { 644 using (SqlConnection connection = new SqlConnection(connectionString)) 645 { 646 using (SqlCommand cmd = new SqlCommand()) 647 { 648 try 649 { 650 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 651 int rows = cmd.ExecuteNonQuery(); 652 cmd.Parameters.Clear(); 653 return rows; 654 } 655 catch (System.Data.SqlClient.SqlException e) 656 { 657 throw e; 658 } 659 } 660 } 661 } 662 /// <summary> 663 /// 执行多条SQL语句，实现数据库事务。 664 /// </summary> 665 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> 666 public void ExecuteSqlTran(Hashtable SQLStringList) 667 { 668 using (SqlConnection conn = new SqlConnection(connectionString)) 669 { 670 conn.Open(); 671 using (SqlTransaction trans = conn.BeginTransaction()) 672 { 673 SqlCommand cmd = new SqlCommand(); 674 try 675 { 676 //循环 677 foreach (DictionaryEntry myDE in SQLStringList) 678 { 679 string cmdText = myDE.Key.ToString(); 680 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; 681 PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 682 int val = cmd.ExecuteNonQuery(); 683 cmd.Parameters.Clear(); 684 } 685 trans.Commit(); 686 } 687 catch 688 { 689 trans.Rollback(); 690 throw; 691 } 692 } 693 } 694 } 695 /// <summary> 696 /// 执行多条SQL语句，实现数据库事务。 697 /// </summary> 698 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> 699 public int ExecuteSqlTran(System.Collections.Generic.List<DBUtility> cmdList) 700 { 701 using (SqlConnection conn = new SqlConnection(connectionString)) 702 { 703 conn.Open(); 704 using (SqlTransaction trans = conn.BeginTransaction()) 705 { 706 SqlCommand cmd = new SqlCommand(); 707 try 708 { 709 int count = 0; 710 //循环 711 foreach (DBUtility myDE in cmdList) 712 { 713 string cmdText = myDE.CommandText; 714 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; 715 PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 716 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) 717 { 718 if (myDE.CommandText.ToLower().IndexOf("count(") == -1) 719 { 720 trans.Rollback(); 721 return 0; 722 } 723 object obj = cmd.ExecuteScalar(); 724 bool isHave = false; 725 if (obj == null && obj == DBNull.Value) 726 { 727 isHave = false; 728 } 729 isHave = Convert.ToInt32(obj) > 0; 730 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) 731 { 732 trans.Rollback(); 733 return 0; 734 } 735 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) 736 { 737 trans.Rollback(); 738 return 0; 739 } 740 continue; 741 } 742 int val = cmd.ExecuteNonQuery(); 743 count += val; 744 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) 745 { 746 trans.Rollback(); 747 return 0; 748 } 749 cmd.Parameters.Clear(); 750 } 751 trans.Commit(); 752 return count; 753 } 754 catch 755 { 756 trans.Rollback(); 757 throw; 758 } 759 } 760 } 761 } 762 /// <summary> 763 /// 执行多条SQL语句，实现数据库事务。 764 /// </summary> 765 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> 766 public void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<DBUtility> SQLStringList) 767 { 768 using (SqlConnection conn = new SqlConnection(connectionString)) 769 { 770 conn.Open(); 771 using (SqlTransaction trans = conn.BeginTransaction()) 772 { 773 SqlCommand cmd = new SqlCommand(); 774 try 775 { 776 int indentity = 0; 777 //循环 778 foreach (DBUtility myDE in SQLStringList) 779 { 780 string cmdText = myDE.CommandText; 781 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; 782 foreach (SqlParameter q in cmdParms) 783 { 784 if (q.Direction == ParameterDirection.InputOutput) 785 { 786 q.Value = indentity; 787 } 788 } 789 PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 790 int val = cmd.ExecuteNonQuery(); 791 foreach (SqlParameter q in cmdParms) 792 { 793 if (q.Direction == ParameterDirection.Output) 794 { 795 indentity = Convert.ToInt32(q.Value); 796 } 797 } 798 cmd.Parameters.Clear(); 799 } 800 trans.Commit(); 801 } 802 catch 803 { 804 trans.Rollback(); 805 throw; 806 } 807 } 808 } 809 } 810 /// <summary> 811 /// 执行多条SQL语句，实现数据库事务。 812 /// </summary> 813 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> 814 public void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) 815 { 816 using (SqlConnection conn = new SqlConnection(connectionString)) 817 { 818 conn.Open(); 819 using (SqlTransaction trans = conn.BeginTransaction()) 820 { 821 SqlCommand cmd = new SqlCommand(); 822 try 823 { 824 int indentity = 0; 825 //循环 826 foreach (DictionaryEntry myDE in SQLStringList) 827 { 828 string cmdText = myDE.Key.ToString(); 829 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; 830 foreach (SqlParameter q in cmdParms) 831 { 832 if (q.Direction == ParameterDirection.InputOutput) 833 { 834 q.Value = indentity; 835 } 836 } 837 PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 838 int val = cmd.ExecuteNonQuery(); 839 foreach (SqlParameter q in cmdParms) 840 { 841 if (q.Direction == ParameterDirection.Output) 842 { 843 indentity = Convert.ToInt32(q.Value); 844 } 845 } 846 cmd.Parameters.Clear(); 847 } 848 trans.Commit(); 849 } 850 catch 851 { 852 trans.Rollback(); 853 throw; 854 } 855 } 856 } 857 } 858 /// <summary> 859 /// 执行一条计算查询结果语句，返回查询结果（object）。 860 /// </summary> 861 /// <param name="SQLString">计算查询结果语句</param> 862 /// <returns>查询结果（object）</returns> 863 public object GetSingle(string SQLString, params SqlParameter[] cmdParms) 864 { 865 using (SqlConnection connection = new SqlConnection(connectionString)) 866 { 867 using (SqlCommand cmd = new SqlCommand()) 868 { 869 try 870 { 871 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 872 object obj = cmd.ExecuteScalar(); 873 cmd.Parameters.Clear(); 874 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 875 { 876 return null; 877 } 878 else 879 { 880 return obj; 881 } 882 } 883 catch (System.Data.SqlClient.SqlException e) 884 { 885 throw e; 886 } 887 } 888 } 889 } 890 /// <summary> 891 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) 892 /// </summary> 893 /// <param name="strSQL">查询语句</param> 894 /// <returns>SqlDataReader</returns> 895 public SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms) 896 { 897 SqlConnection connection = new SqlConnection(connectionString); 898 SqlCommand cmd = new SqlCommand(); 899 try 900 { 901 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 902 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 903 cmd.Parameters.Clear(); 904 return myReader; 905 } 906 catch (System.Data.SqlClient.SqlException e) 907 { 908 throw e; 909 } 910 // finally 911 // { 912 // cmd.Dispose(); 913 // connection.Close(); 914 // } 915 } 916 /// <summary> 917 /// 执行查询语句，返回DataSet 918 /// </summary> 919 /// <param name="SQLString">查询语句</param> 920 /// <returns>DataSet</returns> 921 public DataSet Query(string SQLString, params SqlParameter[] cmdParms) 922 { 923 using (SqlConnection connection = new SqlConnection(connectionString)) 924 { 925 SqlCommand cmd = new SqlCommand(); 926 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 927 using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 928 { 929 DataSet ds = new DataSet(); 930 try 931 { 932 da.Fill(ds, "ds"); 933 cmd.Parameters.Clear(); 934 } 935 catch (System.Data.SqlClient.SqlException ex) 936 { 937 throw new Exception(ex.Message); 938 } 939 return ds; 940 } 941 } 942 } 943 private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms) 944 { 945 if (conn.State != ConnectionState.Open) 946 conn.Open(); 947 cmd.Connection = conn; 948 cmd.CommandText = cmdText; 949 if (trans != null) 950 cmd.Transaction = trans; 951 cmd.CommandType = CommandType.Text;//cmdType; 952 if (cmdParms != null) 953 { 954 foreach (SqlParameter parameter in cmdParms) 955 { 956 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && 957 (parameter.Value == null)) 958 { 959 parameter.Value = DBNull.Value; 960 } 961 cmd.Parameters.Add(parameter); 962 } 963 } 964 } 965 #endregion 966 #region 存储过程操作 967 /// <summary> 968 /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) 969 /// </summary> 970 /// <param name="storedProcName">存储过程名</param> 971 /// <param name="parameters">存储过程参数</param> 972 /// <returns>SqlDataReader</returns> 973 public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) 974 { 975 SqlConnection connection = new SqlConnection(connectionString); 976 SqlDataReader returnReader; 977 connection.Open(); 978 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); 979 command.CommandType = CommandType.StoredProcedure; 980 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); 981 return returnReader; 982 } 983 /// <summary> 984 /// 执行存储过程 985 /// </summary> 986 /// <param name="storedProcName">存储过程名</param> 987 /// <param name="parameters">存储过程参数</param> 988 /// <param name="tableName">DataSet结果中的表名</param> 989 /// <returns>DataSet</returns> 990 public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) 991 { 992 using (SqlConnection connection = new SqlConnection(connectionString)) 993 { 994 DataSet dataSet = new DataSet(); 995 connection.Open(); 996 SqlDataAdapter sqlDA = new SqlDataAdapter(); 997 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); 998 sqlDA.Fill(dataSet, tableName); 999 connection.Close(); 1000 return dataSet; 1001 } 1002 } 1003 1004 public DataSet RunProcedure2(string storedProcName, string tableName) 1005 { 1006 using (SqlConnection connection = new SqlConnection(connectionString)) 1007 { 1008 DataSet dataSet = new DataSet(); 1009 connection.Open(); 1010 SqlDataAdapter sqlDA = new SqlDataAdapter(); 1011 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName); 1012 sqlDA.Fill(dataSet, tableName); 1013 connection.Close(); 1014 return dataSet; 1015 } 1016 } 1017 public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times) 1018 { 1019 using (SqlConnection connection = new SqlConnection(connectionString)) 1020 { 1021 DataSet dataSet = new DataSet(); 1022 connection.Open(); 1023 SqlDataAdapter sqlDA = new SqlDataAdapter(); 1024 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); 1025 sqlDA.SelectCommand.CommandTimeout = Times; 1026 sqlDA.Fill(dataSet, tableName); 1027 connection.Close(); 1028 return dataSet; 1029 } 1030 } 1031 /// <summary> 1032 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) 1033 /// </summary> 1034 /// <param name="connection">数据库连接</param> 1035 /// <param name="storedProcName">存储过程名</param> 1036 /// <param name="parameters">存储过程参数</param> 1037 /// <returns>SqlCommand</returns> 1038 private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) 1039 { 1040 SqlCommand command = new SqlCommand(storedProcName, connection); 1041 command.CommandType = CommandType.StoredProcedure; 1042 foreach (SqlParameter parameter in parameters) 1043 { 1044 if (parameter != null) 1045 { 1046 // 检查未分配值的输出参数,将其分配以DBNull.Value. 1047 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && 1048 (parameter.Value == null)) 1049 { 1050 parameter.Value = DBNull.Value; 1051 } 1052 command.Parameters.Add(parameter); 1053 } 1054 } 1055 return command; 1056 } 1057 private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName) 1058 { 1059 SqlCommand command = new SqlCommand(storedProcName, connection); 1060 command.CommandType = CommandType.StoredProcedure; 1061 return command; 1062 } 1063 /// <summary> 1064 /// 执行存储过程，返回影响的行数 1065 /// </summary> 1066 /// <param name="storedProcName">存储过程名</param> 1067 /// <param name="parameters">存储过程参数</param> 1068 /// <param name="rowsAffected">影响的行数</param> 1069 /// <returns></returns> 1070 public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) 1071 { 1072 using (SqlConnection connection = new SqlConnection(connectionString)) 1073 { 1074 int result; 1075 connection.Open(); 1076 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); 1077 rowsAffected = command.ExecuteNonQuery(); 1078 result = (int)command.Parameters["ReturnValue"].Value; 1079 //Connection.Close(); 1080 return result; 1081 } 1082 } 1083 public int RunProcedure1(string storedProcName, IDataParameter[] parameters) 1084 { 1085 //connectionString = @"DATA SOURCE=192.168.0.103,2433;UID=cdteam;PWD=cd-team2011_;DATABASE=ShiNianCMS"; 1086 using (SqlConnection connection = new SqlConnection(connectionString)) 1087 { 1088 int result; 1089 connection.Open(); 1090 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); 1091 command.ExecuteNonQuery(); 1092 result = (int)command.Parameters["ReturnValue"].Value; 1093 //Connection.Close(); 1094 return result; 1095 } 1096 } 1097 public int RunProcedure3(string storedProcName) 1098 { 1099 //connectionString = @"DATA SOURCE=192.168.0.103,2433;UID=cdteam;PWD=cd-team2011_;DATABASE=ShiNianCMS"; 1100 using (SqlConnection connection = new SqlConnection(connectionString)) 1101 { 1102 int result; 1103 connection.Open(); 1104 SqlCommand command = BuildQueryCommand(connection, storedProcName); 1105 command.ExecuteNonQuery(); 1106 result = (int)command.Parameters["ReturnValue"].Value; 1107 //Connection.Close(); 1108 return result; 1109 } 1110 } 1111 /// <summary> 1112 /// 创建 SqlCommand 对象实例(用来返回一个整数值) 1113 /// </summary> 1114 /// <param name="storedProcName">存储过程名</param> 1115 /// <param name="parameters">存储过程参数</param> 1116 /// <returns>SqlCommand 对象实例</returns> 1117 private SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) 1118 { 1119 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); 1120 command.Parameters.Add(new SqlParameter("ReturnValue", 1121 SqlDbType.Int, 4, ParameterDirection.ReturnValue, 1122 false, 0, 0, string.Empty, DataRowVersion.Default, null)); 1123 return command; 1124 } 1125 #endregion 1126 } 1127 }
1173
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
