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 namespace LTP.SQLServerDAL{ /// <summary> /// ADO.NET数据库操作基础类。 /// </summary> public abstract Class DbManagerSQLclass 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 }}