using System; using System.Collections; using System.Collections.Specialized; using System.Runtime.Remoting.Messaging; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace LTP.SQLServerDAL ... { /**//// <summary> /// ADO.NET数据库操作基础类。 /// </summary> public abstract class DbManagerSQL ...{ //数据库连接字符串 protected static string connectionString = ConfigurationSettings.AppSettings["ConnectionString"]; public DbManagerSQL() ...{ // // TODO: 在此处添加构造函数逻辑 // } /**//// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString"></param> /// <returns></returns> public static int ExecuteSql(string SQLString) ...{ using (SqlConnection connection = new SqlConnection(connectionString)) ...{ using (SqlCommand cmd = new SqlCommand(SQLString,connection)) ...{ try ...{ connection.Open(); int rows=cmd.ExecuteNonQuery(); return rows; } catch(System.Data.SqlClient.SqlException E) ...{ throw new Exception(E.Message); } } } } /**//// <summary> /// 执行两条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLString1"></param> /// <param name="SQLString2"></param> public static void ExecuteSqlTran(string SQLString1,string SQLString2) ...{ using (SqlConnection connection = new SqlConnection(connectionString)) ...{ connection.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection=connection; SqlTransaction tx=connection.BeginTransaction(); cmd.Transaction=tx; try ...{ cmd.CommandText=SQLString1; cmd.ExecuteNonQuery(); cmd.CommandText=SQLString2; cmd.ExecuteNonQuery(); tx.Commit(); } catch(System.Data.SqlClient.SqlException E) ...{ tx.Rollback(); throw new Exception(E.Message); } finally ...{ cmd.Dispose(); connection.Close(); } } } /**//// <summary> /// 执行多条SQL语句,实现数据库事务,每条语句以“;”分割。 /// </summary> /// <param name="SQLStringList"></param> public static void ExecuteSqlTran(string SQLStringList) ...{ using (OdbcConnection conn = new OdbcConnection(connectionString)) ...{ conn.Open(); OdbcCommand cmd = new OdbcCommand(); cmd.Connection=conn; OdbcTransaction tx=conn.BeginTransaction(); cmd.Transaction=tx; try ...{ string [] split= SQLStringList.Split(new Char [] ...{ ';'}); foreach (string strsql in split) ...{ if (strsql.Trim()!="") ...{ cmd.CommandText=strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch(System.Data.Odbc.OdbcException E) ...{ tx.Rollback(); throw new Exception(E.Message); } } } /**//// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString"></param> /// <param name="content"></param> /// <returns></returns> public static int ExecuteSql(string SQLString,string content) ...{ using (SqlConnection connection = new SqlConnection(connectionString)) ...{ SqlCommand cmd = new SqlCommand(SQLString,connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText); myParameter.Value = content ; cmd.Parameters.Add(myParameter); try ...{ connection.Open(); int rows=cmd.ExecuteNonQuery(); return rows; } catch(System.Data.SqlClient.SqlException E) ...{ throw new Exception(E.Message); } finally ...{ cmd.Dispose(); connection.Close(); } } } /**//// <summary> /// 向数据库里插入图像格式的字段 /// </summary> /// <param name="strSQL"></param> /// <param name="fs"></param> /// <returns></returns> public static int ExecuteSqlInsertImg(string strSQL,byte[] fs) ...{ using (SqlConnection connection = new SqlConnection(connectionString)) ...{ SqlCommand cmd = new SqlCommand(strSQL,connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@fs", SqlDbType.Image); myParameter.Value = fs ; cmd.Parameters.Add(myParameter); try ...{ connection.Open(); int rows=cmd.ExecuteNonQuery(); return rows; } catch(System.Data.SqlClient.SqlException E) ...{ throw new Exception(E.Message); } finally ...{ cmd.Dispose(); connection.Close(); } } } /**//// <summary> /// 执行一条计算查询结果语句,返回查询结果(整数)。 /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public static int GetCount(string strSQL) ...{ using (SqlConnection connection = new SqlConnection(connectionString)) ...{ SqlCommand cmd = new SqlCommand(strSQL,connection); try ...{ connection.Open(); SqlDataReader result = cmd.ExecuteReader(); int i=0; while(result.Read()) ...{ i=result.GetInt32(0); } result.Close(); return i; } catch(System.Data.SqlClient.SqlException e) ...{ throw new Exception(e.Message); } finally ...{ cmd.Dispose(); connection.Close(); } } } /**//// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="SQLString"></param> /// <returns></returns> public static object GetSingle(string SQLString) ...{ using (SqlConnection connection = new SqlConnection(connectionString)) ...{ SqlCommand cmd = new SqlCommand(SQLString,connection); try ...{ connection.Open(); object obj = cmd.ExecuteScalar(); if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value))) ...{ return null; } else ...{ return obj; } } catch(System.Data.SqlClient.SqlException e) ...{ throw new Exception(e.Message); } finally ...{ cmd.Dispose(); connection.Close(); } } } /**//// <summary> /// 执行查询语句,返回SqlDataReader /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public static SqlDataReader ExecuteReader(string strSQL) ...{ using (SqlConnection connection = new SqlConnection(connectionString)) ...{ SqlCommand cmd = new SqlCommand(strSQL,connection); SqlDataReader myReader; try ...{ connection.Open(); myReader = cmd.ExecuteReader(); return myReader; } catch(System.Data.SqlClient.SqlException e) ...{ throw new Exception(e.Message); } finally ...{ cmd.Dispose(); connection.Close(); } } } /**//// <summary> /// 执行查询语句,返回DataSet /// </summary> /// <param name="SQLString"></param> /// <returns></returns> public static DataSet Query(string SQLString) ...{ using (SqlConnection connection = new SqlConnection(connectionString)) ...{ DataSet ds = new DataSet(); try ...{ connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString,connection); command.Fill(ds,"ds"); } catch(System.Data.SqlClient.SqlException ex) ...{ throw new Exception(ex.Message); } return ds; } } 存储过程操作#region 存储过程操作 /**//// <summary> /// 运行存储过程 /// </summary> /// <param name="storedProcName"></param> /// <param name="parameters"></param> /// <returns></returns> public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters ) ...{ using (SqlConnection connection = new SqlConnection(connectionString)) ...{ SqlDataReader returnReader; connection.Open(); SqlCommand command = BuildQueryCommand( connection,storedProcName, parameters ); command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(); //Connection.Close(); return returnReader; } } private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters) ...{ SqlCommand command = new SqlCommand( storedProcName, connection ); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) ...{ command.Parameters.Add( parameter ); } return command; } public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName ) ...{ using (SqlConnection connection = new SqlConnection(connectionString)) ...{ DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters ); sqlDA.Fill( dataSet, tableName ); connection.Close(); return dataSet; } } #endregion }}