使用微软企业库构建简单的数据访问层，供新人参考(C#+SqlServer)

 

1.下载微软的企业库中以下dll引用项目中

   Microsoft.Practices.EnterpriseLibrary.Common.dll

   Microsoft.Practices.EnterpriseLibrary.Data.dll

   Microsoft.Practices.ObjectBuilder.dll

2.web.config中<connectionStrings> </connectionStrings>节点中添加数据库链接配置

   <add name="数据库链接名称1" connectionString="server=IP地址,端口;Database=数据库名称;User ID=用户id;Password=用户密码" providerName="System.Data.SqlClient"/>

 可以添加多少，例如：

<connectionStrings>
    <clear/>
    <add name="db_write" connectionString="server=192.168.0.169,1439;Database=TCFly;User ID=sa;Password=123456" providerName="System.Data.SqlClient"/>
    <add name="db_flightread" connectionString="server=192.168.0.154,1433;Database=TCFly;User ID=sa;Password=123456" providerName="System.Data.SqlClient"/>
</connectionStrings>

3.新建数据库操作帮助类DBHelper

   a.获取Database对象 

      传入参数就是2中定义的各个数据库链接名称

       public static Database GetDB()
        {
            return GetDB("db_write");//有默认值
        }

        public static Database GetDB(string connectionName) 
        {
            return DatabaseFactory.CreateDatabase(connectionName);
        }  

 b.  获取SqlConnection对象未打开

       public static SqlConnection GetSqlConnection()
        {
            Database db = GetDB();
            SqlConnection con = db.CreateConnection() as SqlConnection;
            return con;
        }

       public static SqlConnection GetSqlConnection(string connectionName)
        {
            Database db = GetDB(connectionName);
            SqlConnection con = db.CreateConnection() as SqlConnection;
            return con;
        }

c.  执行插入操作，返回自增长ID

        public static int ExecuteIdentity(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);

            int id = 0;
            Database db = GetDB();
            if (db != null)
            {
                object obj = db.ExecuteScalar(cmd);
                if (obj != null)
                    int.TryParse(obj.ToString(), out id);
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            //这里一定要关闭链接
            try
            {
                cmd.Connection.Close();
            }
            catch { }

            return id;
        }

 

     

       public static int ExecuteIdentity(string sql, SqlParameter[] paramArray)
        {
            Database db = GetDB();
            SqlConnection con = db.CreateConnection() as SqlConnection;
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            if (paramArray != null && paramArray.Length > 0)
                cmd.Parameters.AddRange(paramArray);
            int id = 0;

            string identity = " select @@IDENTITY as ID ";
            cmd.CommandText = sql + identity;
            object obj = cmd.ExecuteScalar();
            if (obj != null)
                int.TryParse(obj.ToString(), out id);

            //关闭连接
            try
            {
                con.Close();
            }
            catch { }

            return id;
        }

d.执行SQL语句返回DataTable 如果失败或异常，可能返回null   

      public static DataTable ExecuteDataTable(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);

            Database db = GetDB();
            DataTable dt = null;
            if (db != null)
            {
                DataSet ds = db.ExecuteDataSet(cmd);

                if (ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            //关闭连接
            try
            {
                cmd.Connection.Close();
            }
            catch { }

            return dt;
        }

    

   

     public static DataTable ExecuteDataTable(string sql, string connectionName)
        {
            SqlCommand cmd = new SqlCommand(sql);

            Database db = GetDB(connectionName);
            DataTable dt = null;
            if (db != null)
            {
                DataSet ds = db.ExecuteDataSet(cmd);

                if (ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            //关闭连接
            try
            {
                cmd.Connection.Close();
            }
            catch { }

            return dt;
        }

 

e.执行SQL语句，返回影响行数

       public static int ExecuteNonQuery(string sql)

       {
            SqlCommand cmd = new SqlCommand(sql);

            Database db = GetDB();
            if (db == null)
                throw new CustomException("获取Database错误，返回null");

            int n = 0;
            try
            {
                n = db.ExecuteNonQuery( cmd );
            }
            catch ( Exception ex )
            {
                Logger.WriteLog( "执行SQL语句，返回影响行数异常", ex );
            }
            finally
            {
                //关闭连接
                try
                {
                    cmd.Connection.Close();
                }
                catch { }
            }

            return n;
        }

 

    

      public static int ExecuteNonQuery(string sql, string connectionName)
        {
            SqlCommand cmd = new SqlCommand(sql);

            Database db = GetDB(connectionName);
            if (db == null)
                throw new CustomException("获取Database错误，返回null");

            int n = 0;
            try
            {
                n = db.ExecuteNonQuery( cmd );
            }
            catch ( Exception ex )
            {
                Logger.WriteLog( "执行SQL语句，返回影响行数异常", ex );
            }
            finally
            {
                //关闭连接
                try
                {
                    cmd.Connection.Close();
                }
                catch { }
            }

            return n;
        }

4.事务操作  

        /// <summary>
        /// 执行插入操作，返回自增长ID
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteIdentity(string sql, SqlTransaction tran)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = tran.Connection;
            cmd.Transaction = tran;
            int id = 0;
            string identity = " select @@IDENTITY as ID ";
            cmd.CommandText = sql + identity;
            object obj = cmd.ExecuteScalar();
            if (obj != null)
                int.TryParse(obj.ToString(), out id);

            return id;
        }

        /// <summary>
        /// 执行插入操作，返回自增长ID
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteIdentity(string sql, SqlParameter[] paramArray, SqlTransaction tran)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = tran.Connection;
            cmd.Transaction = tran;
            cmd.Parameters.AddRange(paramArray);

            int id = 0;
            string identity = " select @@IDENTITY as ID ";
            cmd.CommandText = sql + identity;
            object obj = cmd.ExecuteScalar();
            if (obj != null)
                int.TryParse(obj.ToString(), out id);

            return id;
        }

        /// <summary>
        /// 事务中执行SQL语句，返回影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, SqlTransaction tran)
        {
            SqlCommand command = new SqlCommand(sql, tran.Connection);
            command.Transaction = tran;
            return command.ExecuteNonQuery();
        }

        /// <summary>
        /// 事务中执行SQL语句，返回影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramArray"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] paramArray, SqlTransaction tran)
        {
            SqlCommand command = new SqlCommand(sql, tran.Connection);
            command.Transaction = tran;
            command.Parameters.AddRange(paramArray);
            return command.ExecuteNonQuery();
        }

        /// <summary>
        /// 事务中执行SQL语句返回DT
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, SqlTransaction tran)
        {
            SqlCommand cmd = new SqlCommand(sql, tran.Connection);
            cmd.Transaction = tran;
            Database db = GetDB();
            DataTable dt = null;
            if (db != null)
            {
                DataSet ds = db.ExecuteDataSet(cmd);

                if (ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            return dt;
        }

        /// <summary>
        /// 事务中执行SQL语句返回DT
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramArray"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, SqlParameter[] paramArray, SqlTransaction tran)
        {
            SqlCommand cmd = new SqlCommand(sql, tran.Connection);
            cmd.Transaction = tran;
            cmd.Parameters.AddRange(paramArray);

            Database db = GetDB();
            DataTable dt = null;
            if (db != null)
            {
                DataSet ds = db.ExecuteDataSet(cmd);

                if (ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            return dt;
        }

        #endregion