C#操作sql server 資料庫訪問類

http://blog.csdn.net/notrynogain/archive/2007/10/25/1843974.aspx

 

說明:以下是我近兩年來開發中最常用的C#操作sql server資料庫訪問類,對初學者非常有用,多研究研究,有什麼問題聯絡我QQ:115216552,在web.config中配置一下連接字串就可用了.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

public class SqlClass
{
    protected SqlConnection Connection;
    protected string connectionstring;

    public SqlClass()
    {
        connectionstring = System.Configuration.ConfigurationSettings.AppSettings["connString"];
        Connection = new SqlConnection(connectionstring);
    }

    public SqlClass(string connstring)
    {
        connectionstring = connstring;
        Connection = new SqlConnection(connectionstring);
    }

    /*執行返回結果和不返回結果的預存程序方法*/
    /// <summary>
    /// 根據預存程序名稱和參數產生對應的SQL命令對象
    /// </summary>
    /// <param name="storedProcName">預存程序名</param>
    /// <param name="parameters">預存程序參數</param>
    /// <returns></returns>
    private SqlCommand BuilderQueryCommand(string storedProcName, SqlParameter[] parameters)
    {
        SqlCommand command = new SqlCommand();
        command.Connection = Connection;
        command.CommandText = storedProcName.Trim();
        command.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
        {
            foreach (SqlParameter p in parameters)
            {
                command.Parameters.Add(p);
            }
        }
        return command;
    }
    /// <summary>
    /// 返回結果的預存程序
    /// </summary>
    /// <param name="storedProcName">預存程序名</param>
    /// <param name="parameters">參數值</param>
    /// <returns></returns>
    public SqlDataReader GetDataReader(string storedProcName, SqlParameter[] parameters)
    {
        SqlDataReader reader;
        SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return reader;
    }
    /// <summary>
    /// 不返回結果的預存程序
    /// </summary>
    /// <param name="storedProcName">預存程序名</param>
    /// <param name="parameters">參數值</param>
    /// <returns></returns>
    public int GetEffect(string storedProcName, SqlParameter[] parameters)
    {
        int result = 0;
        try
        {
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
            SqlTransaction trans = Connection.BeginTransaction();
            try
            {
                SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
                cmd.Transaction = trans;
                result = cmd.ExecuteNonQuery();
                trans.Commit();
                Connection.Close();
                return result;
            }
            catch (Exception ex)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    return result;
                }
                throw ex;

            }
            finally
            {
                if (trans != null)
                {
                    trans.Dispose();
                }
                Connection.Close();
            }
        }
        catch (Exception ex1)
        {
            return 0;
            //  throw new Exception(ex1.Message);
        }
    }
    /// <summary>
    /// 返回dateSet
    /// </summary>
    /// <param name="ProcName"></param>
    /// <param name="parameters"></param>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public DataSet GetDataSet(string ProcName, SqlParameter[] parameters, string tableName)
    {
        try
        {

            DataSet ds = new DataSet();
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }

            SqlDataAdapter myDa = new SqlDataAdapter();
            myDa.SelectCommand = BuilderQueryCommand(ProcName, parameters);
            myDa.Fill(ds, tableName);
            return ds;
        }
        catch
        {
            return null;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 自訂分頁
    /// </summary>
    /// <param name="ProcName">預存程序名</param>
    /// <param name="parameters">參數名</param>
    /// <param name="start">起始頁</param>
    /// <param name="maxRecord">記錄數</param>
    /// <param name="tableName">表名</param>
    /// <returns></returns>
    public DataSet GetDataSet(string ProcName, SqlParameter[] parameters, int start, int maxRecord, string tableName)
    {
        try
        {
            DataSet ds = new DataSet();
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
            Connection.Open();
            SqlDataAdapter myDa = new SqlDataAdapter();
            myDa.SelectCommand = BuilderQueryCommand(ProcName, parameters);
            myDa.Fill(ds, start, maxRecord, tableName);
            return ds;
        }
        catch
        {
            Connection.Close();
            return null;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 返回object類型　比如首行首列
    /// </summary>
    /// <param name="storedProcName">預存程序名稱</param>
    /// <param name="parameters">參數列表</param>
    /// <returns></returns>
    public object GetObject(string storedProcName, SqlParameter[] parameters)
    {
        object result = null;

        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        //   SqlTransaction trans = Connection.BeginTransaction();
        try
        {
            SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
            //  cmd.Transaction = trans;
            result = cmd.ExecuteScalar();
            //  trans.Commit();
            Connection.Close();
            return result;
        }
        catch
        {
            //  trans.Rollback();
            return result;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 通過ＩＤ號操作某條記錄,比如刪除ＩＤ號的記錄
    /// </summary>
    /// <param name="ProcName">預存程序名</param>
    /// <param name="id">值</param>
    /// <param name="paraname">參數名如：@ID</param>
    /// <returns></returns>
    public int RunProcByID(string ProcName, int id, string paraname)
    {
        try
        {
            SqlParameter[] p ={ new SqlParameter(paraname, SqlDbType.Int) };
            p[0].Value = id;
            return GetEffect(ProcName, p);
        }
        catch
        {
            Connection.Close();
            return 0;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 修改ＩＤ號為keyValue的記錄的ParaDieldName欄位的值為FiledValue
    /// </summary>
    /// <param name="ProcName">相信過程名</param>
    /// <param name="keyValue">ID號的值</param>
    /// <param name="paraKeyName">ID號對應的欄位名</param>
    /// <param name="paraFieldName">要修改的欄位名</param>
    /// <param name="FieldValue">要修改欄位的值</param>
    /// <param name="length">欄位的長度</param>
    /// <returns></returns>
   
public int UpdateByID(string ProcName, int keyValue, string
paraKeyName, string paraFieldName, string FieldValue, int length)
    {
        try
        {
            SqlParameter[] p ={ new SqlParameter(paraFieldName,SqlDbType.VarChar,length),
                                    new SqlParameter(paraFieldName, SqlDbType.Int)
                                   };
            p[0].Value = FieldValue;
            p[1].Value = keyValue;
            return GetEffect(ProcName, p);
        }
        catch
        {
            Connection.Close();
            return 0;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 執行ＳＱＬ語句返回DataSet
    /// </summary>
    /// <param name="strSql">ＳＱＬ語句</param>
    /// <param name="tableName">表名</param>
    /// <returns></returns>
    public DataSet RunSql(string strSql, string tableName)
    {
        try
        {

            DataSet ds = new DataSet();
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
            SqlDataAdapter myDa = new SqlDataAdapter();
            myDa.SelectCommand = new SqlCommand(strSql, Connection);
            myDa.Fill(ds, tableName);
            return ds;
        }
        catch
        {
            return null;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 執行ＳＱＬ語句返回影響行數
    /// </summary>
    /// <param name="strSql">sql語句</param>
    /// <param name="effect">輸出行數</param>
    /// <returns></returns>
    public int RunSql(string strSql, out int effect)
    {
        effect = 0;
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        SqlTransaction trans = Connection.BeginTransaction();
        try
        {
            SqlCommand cmd = new SqlCommand(strSql, Connection);
            cmd.Transaction = trans;
            effect = cmd.ExecuteNonQuery();
            trans.Commit();
            return effect;
        }
        catch
        {
            trans.Rollback();
            Connection.Close();
            return effect;
        }
        finally
        {
            Connection.Close();
        }
    }
    /// <summary>
    /// 執行ＳＱＬ語句返回SqlDataReader
    /// </summary>
    /// <param name="strsql">sql語句</param>
    /// <returns></returns>
    public SqlDataReader RunSql(string strsql)
    {
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        try
        {
            SqlCommand cmd = new SqlCommand(strsql, Connection);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            Connection.Close();
            return null;
        }
      
    }
    /// <summary>
    /// 執行ＳＱＬ語句返回object
    /// </summary>
    /// <param name="strsql">sql語句</param>
    /// <returns></returns>
    public object GetObjectByRunSQL(string strsql)
    {
        if (Connection.State == ConnectionState.Closed)
        {
            Connection.Open();
        }
        object obj = null;
        try
        {
            SqlCommand cmd = new SqlCommand(strsql, Connection);
            obj = cmd.ExecuteScalar();
            Connection.Close();
            return obj;
        }
        catch
        {
            Connection.Close();
            return null;
        }
        finally
        {
            Connection.Close();
        }
    }
}