- 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 = BuildQueryCom
- mand( 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
- }
- }
转载于:https://www.cnblogs.com/cean/archive/2008/12/05/1348523.html