資料庫操作基本方法實現

一、擷取資料庫連接和基本的資訊

1定義介面

namespace Com.Mycompany.Data{     public interface IDbSetting       {string ConnectionString{get;}DbServerType DbServerType{get;}        string COID        {            get;        }        string UserNO        {            get;        }…………    }}

2繼承介面

namespace Com.Mycompany.Web.WebUI{    public class MyDBSetting : IDbSetting    {        public MyDBSetting(string language, UserInformation user)        {        }        /// <summary>        /// 資料連線串        /// </summary>        public string ConnectionString        {            get            {                if (HttpContext.Current.Application["ConnectionString"] == null)                {                    if (ConfigurationManager.ConnectionStrings["Conncetion"] == null)                    {                        throw new Exception(KDCommon.DataConnectionInfo);                    }                    else                    {                        HttpContext.Current.Application["ConnectionString"] = ConfigurationManager.ConnectionStrings["Conncetion"].ToString();                    }                }                return HttpContext.Current.Application["ConnectionString"].ToString();            }        }        /// <summary>        /// 資料庫類型        /// </summary>        public DbServerType DbServerType        {            get            {                return DbServerType.SqlServer;            }        }        /// <summary>        /// 當前公司coid        /// </summary>        public string COID        {            get            {                SessionTimeOut("coid");                return HttpContext.Current.Session["coid"].ToString();             }        }        public string UserNO        {            get            {                SessionTimeOut("loginuserno");                return HttpContext.Current.Session["loginuserno"].ToString();             }        }…………    }}

 3封裝介面

namespace Com.Mycompany.Common{public class BaseInfo{private static IDbSetting s_dbSetting = null;public static IDbSetting DbSetting{get{return s_dbSetting;}                                     set                                    {                                                 BaseInfo.s_dbSetting = value;                                     }}public BaseInfo(){}               }…………}

 二、資料庫基本操作  /// <summary>
 /// 訪問資料庫物件（協助工具輔助）
 /// </summary>
 /// <remarks></remarks>
 public sealed class DbUtils 1、建立串連 

/// <summary>/// 資料連線串/// </summary>public static string ConnectionString{get{if( _connectionString == null || _connectionString.Length == 0){return BaseInfo.DbSetting.ConnectionString;}else{return _connectionString;}}set{_connectionString =value;}}/// <summary>/// 資料服務器類型/// </summary>public static DbServerType DbServerType{get{return BaseInfo.DbSetting.DbServerType;}}        /// <summary>        /// 產品id號        /// </summary>        public static string COID        {            get            {                return BaseInfo.DbSetting.COID;            }        }        /// <summary>        /// 使用者編號        /// </summary>        public static string UserNO        {            get            {                return BaseInfo.DbSetting.UserNO;            }        }…………

 3、執行select查詢語句

/// <summary>/// 執行查詢命令/// </summary>/// <param name="commandString">Sql命令</param>/// <param name="datareader">輸出DataReader</param>public static void RunSelectCommand(string connectionString,string commandString,out IDataReader datareader) {IDbCommand __command;OleDbConnection conn = GetConnection(connectionString);__command = new OleDbCommand(commandString, conn);__command.CommandTimeout = 0; //設定等待命令執行的時間為無限期if(__command.Connection.State != ConnectionState.Open){__command.Connection.Open();}datareader = __command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);__command.Dispose();//釋放__command}             /// <summary>        /// 傳遞連線物件，用於建立暫存資料表的操作，不能每次建立連線物件（否則暫存資料表每次SQL執行完後會自動刪除）        /// </summary>        /// <param name="commandString">Sql命令</param>        /// <param name="dataTable">輸出資料表</param>        /// <param name="prams">查詢命令參數列表</param>        public static void RunSelectCommand(            OleDbConnection conn,            string commandString,            out DataTable dataTable,            params IDbDataParameter[] prams            )        {            IDbDataAdapter __dataadapter;            DataSet __dataSet;            __dataadapter = new OleDbDataAdapter(commandString, conn);            __dataadapter.SelectCommand.CommandTimeout = 0;//設定等待命令執行的時間為無限期            if (prams != null)            {                foreach (IDbDataParameter pram in prams)                {                    //if (pram.DbType == DbType.String)                    //{                    //    pram.Value = ReplaceSQLEscape(pram.Value);                    //}                    __dataadapter.SelectCommand.Parameters.Add(pram);                }            }            __dataSet = new DataSet();            try            {                __dataadapter.Fill(__dataSet);                dataTable = __dataSet.Tables[0];            }            catch (Exception e)            {                throw new Exception(commandString, e);            }        }

 4、執行預存程序

#region RunStoredProcedure without out datatablepublic static void RunStoredProcedure(string connString,string procName){using(OleDbConnection conn = GetConnection(connString)){try{IDbCommand command = conn.CreateCommand();command.CommandText = procName;command.CommandType = CommandType.StoredProcedure;command.CommandTimeout = 0;//設定等待命令執行的時間為無限期command.ExecuteNonQuery();command.Dispose();}catch(Exception ex){throw new Exception(procName + ":" + ex.Message);}finally{conn.Dispose();}}}public static void RunStoredProcedure(string connString,string procName,params IDbDataParameter[] paramsArray){using(OleDbConnection conn = GetConnection(connString)){try{IDbCommand command = conn.CreateCommand();command.CommandText = procName;command.CommandType = CommandType.StoredProcedure;command.CommandTimeout = 0;//設定等待命令執行的時間為無限期foreach(IDbDataParameter param in paramsArray){command.Parameters.Add(param);}command.ExecuteNonQuery();command.Dispose();}catch(Exception ex){throw new Exception(procName + ":" + ex.Message);}finally{conn.Dispose();}}}#endregion#region RunStoredProcedure with out datatablepublic static void RunStoredProcedure(string connString,string procName,out DataTable dataTable){OleDbDataAdapter adapter;DataSet dataSet;using(OleDbConnection conn = GetConnection(connString)){try{dataSet = new DataSet();IDbCommand command = conn.CreateCommand();command.CommandText = procName;command.CommandType = CommandType.StoredProcedure;command.CommandTimeout = 0;//設定等待命令執行的時間為無限期adapter = new OleDbDataAdapter((OleDbCommand)command);adapter.Fill(dataSet);adapter.Dispose();command.Dispose();dataTable = dataSet.Tables[0];}catch(Exception ex){throw new Exception(procName + ":" + ex.Message);}finally{conn.Dispose();}}}public static void RunStoredProcedure(string connString,string procName,out DataTable dataTable,params IDbDataParameter[] paramsArray){OleDbDataAdapter adapter;DataSet dataSet;            dataTable = null;using(OleDbConnection conn = GetConnection(connString)){try{dataSet = new DataSet();IDbCommand command = conn.CreateCommand();command.CommandText = procName;command.CommandType = CommandType.StoredProcedure;command.CommandTimeout = 0;//設定等待命令執行的時間為無限期foreach(IDbDataParameter param in paramsArray){command.Parameters.Add(param);}adapter = new OleDbDataAdapter((OleDbCommand)command);adapter.Fill(dataSet);adapter.Dispose();command.Dispose();                    if (dataSet != null && dataSet.Tables.Count > 0)                    {                        dataTable = dataSet.Tables[0];                    }}catch(Exception ex){throw new Exception(procName + ":" + ex.Message);}finally{conn.Dispose();}}}#endregion

 5、執行sql語句，返回影響資料行

  //傳回值表示操作影響的記錄數public static int RunCommand(string connectionString,string commandString,params IDbDataParameter[] prams) {IDbCommand __command;            int __rtnRowCount = -1;using(OleDbConnection conn = GetConnection(connectionString)){__command = new OleDbCommand(commandString,conn);__command.CommandTimeout = 0; //設定等待命令執行的時間為無限期if(prams != null){foreach(IDbDataParameter pram in prams){__command.Parameters.Add(pram);}}if(__command.Connection.State != ConnectionState.Open){__command.Connection.Open();}                __rtnRowCount = __command.ExecuteNonQuery();__command.Dispose();}            return __rtnRowCount;}        /// <summary>        /// 傳遞連線物件，用於建立暫存資料表的操作，不能每次建立連線物件（否則暫存資料表每次SQL執行完後會自動刪除）        /// </summary>        /// <param name="conn"></param>        /// <param name="commandString"></param>        /// <param name="prams"></param>        public static int RunCommand(            OleDbConnection conn,            string commandString,            params IDbDataParameter[] prams            )        {            IDbCommand __command;            int __rtnRowCount = -1;            __command = new OleDbCommand(commandString, conn);            __command.CommandTimeout = 0; //設定等待命令執行的時間為無限期            if (prams != null)            {                foreach (IDbDataParameter pram in prams)                {                    __command.Parameters.Add(pram);                }            }            if (__command.Connection.State != ConnectionState.Open)            {                __command.Connection.Open();            }            __rtnRowCount=__command.ExecuteNonQuery();            __command.Parameters.Clear();            return __rtnRowCount;        }