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 8 namespace LiTianPing.SQLServerDAL // 可以修改成实际项目的命名空间名称 9 { 10 /**/ /// <summary> 11 /// Copyright (C) 2004-2008 LiTianPing 12 /// 数据访问基础类(基于SQLServer) 13 /// 用户可以修改满足自己项目的需要。 14 /// </summary> 15 public abstract class DbHelperSQL 16 { 17 // 数据库连接字符串(web.config来配置) 18 // <add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" /> 19 protected static string connectionString = ConfigurationSettings.AppSettings[ " ConnectionString " ]; 20 public DbHelperSQL() 21 { 22 } 23 24 公用方法 #region 公用方法 25 26 public static int GetMaxID( string FieldName, string TableName) 27 { 28 string strsql = " select max( " + FieldName + " )+1 from " + TableName; 29 object obj = GetSingle(strsql); 30 if (obj == null ) 31 { 32 return 1 ; 33 } 34 else 35 { 36 return int .Parse(obj.ToString()); 37 } 38 } 39 public static bool Exists( string strSql, params SqlParameter[] cmdParms) 40 { 41 object obj = GetSingle(strSql, cmdParms); 42 int cmdresult; 43 if ((Object.Equals(obj, null )) || (Object.Equals(obj, System.DBNull.Value))) 44 { 45 cmdresult = 0 ; 46 } 47 else 48 { 49 cmdresult = int .Parse(obj.ToString()); 50 } 51 if (cmdresult == 0 ) 52 { 53 return false ; 54 } 55 else 56 { 57 return true ; 58 } 59 } 60 #endregion 61 62 执行简单SQL语句 #region 执行简单SQL语句 63 64 /**/ /// <summary> 65 /// 执行SQL语句，返回影响的记录数 66 /// </summary> 67 /// <param name="SQLString"> SQL语句 </param> 68 /// <returns> 影响的记录数 </returns> 69 public static int ExecuteSql( string SQLString) 70 { 71 using (SqlConnection connection = new SqlConnection(connectionString)) 72 { 73 using (SqlCommand cmd = new SqlCommand(SQLString,connection)) 74 { 75 try 76 { 77 connection.Open(); 78 int rows = cmd.ExecuteNonQuery(); 79 return rows; 80 } 81 catch (System.Data.SqlClient.SqlException E) 82 { 83 connection.Close(); 84 throw new Exception(E.Message); 85 } 86 } 87 } 88 } 89 90 /**/ /// <summary> 91 /// 执行多条SQL语句，实现数据库事务。 92 /// </summary> 93 /// <param name="SQLStringList"> 多条SQL语句 </param> 94 public static void ExecuteSqlTran(ArrayList SQLStringList) 95 { 96 using (SqlConnection conn = new SqlConnection(connectionString)) 97 { 98 conn.Open(); 99 SqlCommand cmd = new SqlCommand(); 100 cmd.Connection = conn; 101 SqlTransaction tx = conn.BeginTransaction(); 102 cmd.Transaction = tx; 103 try 104 { 105 for ( int n = 0 ;n < SQLStringList.Count;n ++ ) 106 { 107 string strsql = SQLStringList[n].ToString(); 108 if (strsql.Trim().Length > 1 ) 109 { 110 cmd.CommandText = strsql; 111 cmd.ExecuteNonQuery(); 112 } 113 } 114 tx.Commit(); 115 } 116 catch (System.Data.SqlClient.SqlException E) 117 { 118 tx.Rollback(); 119 throw new Exception(E.Message); 120 } 121 } 122 } 123 /**/ /// <summary> 124 /// 执行带一个存储过程参数的的SQL语句。 125 /// </summary> 126 /// <param name="SQLString"> SQL语句 </param> 127 /// <param name="content"> 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 </param> 128 /// <returns> 影响的记录数 </returns> 129 public static int ExecuteSql( string SQLString, string content) 130 { 131 using (SqlConnection connection = new SqlConnection(connectionString)) 132 { 133 SqlCommand cmd = new SqlCommand(SQLString,connection); 134 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( " @content " , SqlDbType.NText); 135 myParameter.Value = content ; 136 cmd.Parameters.Add(myParameter); 137 try 138 { 139 connection.Open(); 140 int rows = cmd.ExecuteNonQuery(); 141 return rows; 142 } 143 catch (System.Data.SqlClient.SqlException E) 144 { 145 throw new Exception(E.Message); 146 } 147 finally 148 { 149 cmd.Dispose(); 150 connection.Close(); 151 } 152 } 153 } 154 /**/ /// <summary> 155 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) 156 /// </summary> 157 /// <param name="strSQL"> SQL语句 </param> 158 /// <param name="fs"> 图像字节,数据库的字段类型为image的情况 </param> 159 /// <returns> 影响的记录数 </returns> 160 public static int ExecuteSqlInsertImg( string strSQL, byte [] fs) 161 { 162 using (SqlConnection connection = new SqlConnection(connectionString)) 163 { 164 SqlCommand cmd = new SqlCommand(strSQL,connection); 165 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( " @fs " , SqlDbType.Image); 166 myParameter.Value = fs ; 167 cmd.Parameters.Add(myParameter); 168 try 169 { 170 connection.Open(); 171 int rows = cmd.ExecuteNonQuery(); 172 return rows; 173 } 174 catch (System.Data.SqlClient.SqlException E) 175 { 176 throw new Exception(E.Message); 177 } 178 finally 179 { 180 cmd.Dispose(); 181 connection.Close(); 182 } 183 } 184 } 185 186 /**/ /// <summary> 187 /// 执行一条计算查询结果语句，返回查询结果（object）。 188 /// </summary> 189 /// <param name="SQLString"> 计算查询结果语句 </param> 190 /// <returns> 查询结果（object） </returns> 191 public static object GetSingle( string SQLString) 192 { 193 using (SqlConnection connection = new SqlConnection(connectionString)) 194 { 195 using (SqlCommand cmd = new SqlCommand(SQLString,connection)) 196 { 197 try 198 { 199 connection.Open(); 200 object obj = cmd.ExecuteScalar(); 201 if ((Object.Equals(obj, null )) || (Object.Equals(obj,System.DBNull.Value))) 202 { 203 return null ; 204 } 205 else 206 { 207 return obj; 208 } 209 } 210 catch (System.Data.SqlClient.SqlException e) 211 { 212 connection.Close(); 213 throw new Exception(e.Message); 214 } 215 } 216 } 217 } 218 /**/ /// <summary> 219 /// 执行查询语句，返回SqlDataReader 220 /// </summary> 221 /// <param name="strSQL"> 查询语句 </param> 222 /// <returns> SqlDataReader </returns> 223 public static SqlDataReader ExecuteReader( string strSQL) 224 { 225 SqlConnection connection = new SqlConnection(connectionString); 226 SqlCommand cmd = new SqlCommand(strSQL,connection); 227 try 228 { 229 connection.Open(); 230 SqlDataReader myReader = cmd.ExecuteReader(); 231 return myReader; 232 } 233 catch (System.Data.SqlClient.SqlException e) 234 { 235 throw new Exception(e.Message); 236 } 237 238 } 239 /**/ /// <summary> 240 /// 执行查询语句，返回DataSet 241 /// </summary> 242 /// <param name="SQLString"> 查询语句 </param> 243 /// <returns> DataSet </returns> 244 public static DataSet Query( string SQLString) 245 { 246 using (SqlConnection connection = new SqlConnection(connectionString)) 247 { 248 DataSet ds = new DataSet(); 249 try 250 { 251 connection.Open(); 252 SqlDataAdapter command = new SqlDataAdapter(SQLString,connection); 253 command.Fill(ds, " ds " ); 254 } 255 catch (System.Data.SqlClient.SqlException ex) 256 { 257 throw new Exception(ex.Message); 258 } 259 return ds; 260 } 261 } 262 263 264 #endregion 265 266 执行带参数的SQL语句 #region 执行带参数的SQL语句 267 268 /**/ /// <summary> 269 /// 执行SQL语句，返回影响的记录数 270 /// </summary> 271 /// <param name="SQLString"> SQL语句 </param> 272 /// <returns> 影响的记录数 </returns> 273 public static int ExecuteSql( string SQLString, params SqlParameter[] cmdParms) 274 { 275 using (SqlConnection connection = new SqlConnection(connectionString)) 276 { 277 using (SqlCommand cmd = new SqlCommand()) 278 { 279 try 280 { 281 PrepareCommand(cmd, connection, null ,SQLString, cmdParms); 282 int rows = cmd.ExecuteNonQuery(); 283 cmd.Parameters.Clear(); 284 return rows; 285 } 286 catch (System.Data.SqlClient.SqlException E) 287 { 288 throw new Exception(E.Message); 289 } 290 } 291 } 292 } 293 294 295 /**/ /// <summary> 296 /// 执行多条SQL语句，实现数据库事务。 297 /// </summary> 298 /// <param name="SQLStringList"> SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） </param> 299 public static void ExecuteSqlTran(Hashtable SQLStringList) 300 { 301 using (SqlConnection conn = new SqlConnection(connectionString)) 302 { 303 conn.Open(); 304 using (SqlTransaction trans = conn.BeginTransaction()) 305 { 306 SqlCommand cmd = new SqlCommand(); 307 try 308 { 309 // 循环 310 foreach (DictionaryEntry myDE in SQLStringList) 311 { 312 string cmdText = myDE.Key.ToString(); 313 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; 314 PrepareCommand(cmd,conn,trans,cmdText, cmdParms); 315 int val = cmd.ExecuteNonQuery(); 316 cmd.Parameters.Clear(); 317 318 trans.Commit(); 319 } 320 } 321 catch 322 { 323 trans.Rollback(); 324 throw ; 325 } 326 } 327 } 328 } 329 330 331 /**/ /// <summary> 332 /// 执行一条计算查询结果语句，返回查询结果（object）。 333 /// </summary> 334 /// <param name="SQLString"> 计算查询结果语句 </param> 335 /// <returns> 查询结果（object） </returns> 336 public static object GetSingle( string SQLString, params SqlParameter[] cmdParms) 337 { 338 using (SqlConnection connection = new SqlConnection(connectionString)) 339 { 340 using (SqlCommand cmd = new SqlCommand()) 341 { 342 try 343 { 344 PrepareCommand(cmd, connection, null ,SQLString, cmdParms); 345 object obj = cmd.ExecuteScalar(); 346 cmd.Parameters.Clear(); 347 if ((Object.Equals(obj, null )) || (Object.Equals(obj,System.DBNull.Value))) 348 { 349 return null ; 350 } 351 else 352 { 353 return obj; 354 } 355 } 356 catch (System.Data.SqlClient.SqlException e) 357 { 358 throw new Exception(e.Message); 359 } 360 } 361 } 362 } 363 364 /**/ /// <summary> 365 /// 执行查询语句，返回SqlDataReader 366 /// </summary> 367 /// <param name="strSQL"> 查询语句 </param> 368 /// <returns> SqlDataReader </returns> 369 public static SqlDataReader ExecuteReader( string SQLString, params SqlParameter[] cmdParms) 370 { 371 SqlConnection connection = new SqlConnection(connectionString); 372 SqlCommand cmd = new SqlCommand(); 373 try 374 { 375 PrepareCommand(cmd, connection, null ,SQLString, cmdParms); 376 SqlDataReader myReader = cmd.ExecuteReader(); 377 cmd.Parameters.Clear(); 378 return myReader; 379 } 380 catch (System.Data.SqlClient.SqlException e) 381 { 382 throw new Exception(e.Message); 383 } 384 385 } 386 387 /**/ /// <summary> 388 /// 执行查询语句，返回DataSet 389 /// </summary> 390 /// <param name="SQLString"> 查询语句 </param> 391 /// <returns> DataSet </returns> 392 public static DataSet Query( string SQLString, params SqlParameter[] cmdParms) 393 { 394 using (SqlConnection connection = new SqlConnection(connectionString)) 395 { 396 SqlCommand cmd = new SqlCommand(); 397 PrepareCommand(cmd, connection, null ,SQLString, cmdParms); 398 using ( SqlDataAdapter da = new SqlDataAdapter(cmd) ) 399 { 400 DataSet ds = new DataSet(); 401 try 402 { 403 da.Fill(ds, " ds " ); 404 cmd.Parameters.Clear(); 405 } 406 catch (System.Data.SqlClient.SqlException ex) 407 { 408 throw new Exception(ex.Message); 409 } 410 return ds; 411 } 412 } 413 } 414 415 416 private static void PrepareCommand(SqlCommand cmd,SqlConnection conn,SqlTransaction trans, string cmdText, SqlParameter[] cmdParms) 417 { 418 if (conn.State != ConnectionState.Open) 419 conn.Open(); 420 cmd.Connection = conn; 421 cmd.CommandText = cmdText; 422 if (trans != null ) 423 cmd.Transaction = trans; 424 cmd.CommandType = CommandType.Text; // cmdType; 425 if (cmdParms != null ) 426 { 427 foreach (SqlParameter parm in cmdParms) 428 cmd.Parameters.Add(parm); 429 } 430 } 431 432 #endregion 433 434 存储过程操作 #region 存储过程操作 435 436 /**/ /// <summary> 437 /// 执行存储过程 438 /// </summary> 439 /// <param name="storedProcName"> 存储过程名 </param> 440 /// <param name="parameters"> 存储过程参数 </param> 441 /// <returns> SqlDataReader </returns> 442 public static SqlDataReader RunProcedure( string storedProcName, IDataParameter[] parameters ) 443 { 444 SqlConnection connection = new SqlConnection(connectionString); 445 SqlDataReader returnReader; 446 connection.Open(); 447 SqlCommand command = BuildQueryCommand( connection,storedProcName, parameters ); 448 command.CommandType = CommandType.StoredProcedure; 449 returnReader = command.ExecuteReader(); 450 return returnReader; 451 } 452 453 454 /**/ /// <summary> 455 /// 执行存储过程 456 /// </summary> 457 /// <param name="storedProcName"> 存储过程名 </param> 458 /// <param name="parameters"> 存储过程参数 </param> 459 /// <param name="tableName"> DataSet结果中的表名 </param> 460 /// <returns> DataSet </returns> 461 public static DataSet RunProcedure( string storedProcName, IDataParameter[] parameters, string tableName ) 462 { 463 using (SqlConnection connection = new SqlConnection(connectionString)) 464 { 465 DataSet dataSet = new DataSet(); 466 connection.Open(); 467 SqlDataAdapter sqlDA = new SqlDataAdapter(); 468 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters ); 469 sqlDA.Fill( dataSet, tableName ); 470 connection.Close(); 471 return dataSet; 472 } 473 } 474 475 476 /**/ /// <summary> 477 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) 478 /// </summary> 479 /// <param name="connection"> 数据库连接 </param> 480 /// <param name="storedProcName"> 存储过程名 </param> 481 /// <param name="parameters"> 存储过程参数 </param> 482 /// <returns> SqlCommand </returns> 483 private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) 484 { 485 SqlCommand command = new SqlCommand( storedProcName, connection ); 486 command.CommandType = CommandType.StoredProcedure; 487 foreach (SqlParameter parameter in parameters) 488 { 489 command.Parameters.Add( parameter ); 490 } 491 return command; 492 } 493 494 /**/ /// <summary> 495 /// 执行存储过程，返回影响的行数 496 /// </summary> 497 /// <param name="storedProcName"> 存储过程名 </param> 498 /// <param name="parameters"> 存储过程参数 </param> 499 /// <param name="rowsAffected"> 影响的行数 </param> 500 /// <returns></returns> 501 public static int RunProcedure( string storedProcName, IDataParameter[] parameters, out int rowsAffected ) 502 { 503 using (SqlConnection connection = new SqlConnection(connectionString)) 504 { 505 int result; 506 connection.Open(); 507 SqlCommand command = BuildIntCommand(connection,storedProcName, parameters ); 508 rowsAffected = command.ExecuteNonQuery(); 509 result = ( int )command.Parameters[ " ReturnValue " ].Value; 510 // Connection.Close(); 511 return result; 512 } 513 } 514 515 /**/ /// <summary> 516 /// 创建 SqlCommand 对象实例(用来返回一个整数值) 517 /// </summary> 518 /// <param name="storedProcName"> 存储过程名 </param> 519 /// <param name="parameters"> 存储过程参数 </param> 520 /// <returns> SqlCommand 对象实例 </returns> 521 private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) 522 { 523 SqlCommand command = BuildQueryCommand(connection,storedProcName, parameters ); 524 command.Parameters.Add( new SqlParameter ( " ReturnValue " , 525 SqlDbType.Int, 4 ,ParameterDirection.ReturnValue, 526 false , 0 , 0 , string .Empty,DataRowVersion.Default, null )); 527 return command; 528 } 529 #endregion 530 531 } 532 } 533 534
1092
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
