程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> C#入門知識 >> ADO.NET之常用功能的封裝,ado.net封裝

ADO.NET之常用功能的封裝,ado.net封裝

編輯:C#入門知識

ADO.NET之常用功能的封裝,ado.net封裝


  一、連接字符串(使用配置文件)

  添加對 System.Configuration 的引用,並且在封裝類中引入該namespace

        /// <summary>
        /// 功能:讀取配置文件中的連接字符串
        /// 返回值類型:string
        /// </summary>
        /// <param name="conName">參數:配置文件中的連接字符串名稱</param>
        /// <returns>返回值:string 連接字符串</returns>
        public static string GetConnectString(string conName)
        {
            try
            {
                return ConfigurationManager.ConnectionStrings[conName].ConnectionString;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

  二、SqlConnection對象

        /// <summary>
        /// 功能:根據給定的連接字符串創建一個SqlConnection對象
        /// 返回類型:SqlConnection
        /// </summary>
        /// <param name="conStr">參數:連接字符串</param>
        /// <returns>返回值:SqlConnection對象</returns>
        public static SqlConnection GetConnect(string conStr)
        {
            return new SqlConnection(conStr);
        }
        /// <summary>
        /// 功能:打開數據庫連接
        /// </summary>
        /// <param name="Con">參數:SqlConnection對象</param>
        public static void OpenDBConnet(SqlConnection Con)
        {
            try
            {
                if (Con.State == ConnectionState.Open)
                {
                    return;
                }
                else
                {
                    Con.Open();
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
        }
        /// <summary>
        /// 功能:關閉數據庫連接
        /// </summary>
        /// <param name="Con">參數:SqlConnection對象</param>
        public static void CloseDBConnect(SqlConnection Con)
        {
            try
            {
                if (Con.State == ConnectionState.Closed)
                {
                    return;
                }
                else
                {
                    Con.Close();
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
        }

  三、同步操作數據庫

  SqlDataReader對象 

        /// <summary>
        /// 功能:執行存儲過程並返回一個SqlDataReader對象
        /// </summary>
        /// <param name="sql">參數:存儲過程名</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter參數數組</param>
        /// <returns>返回值:SqlDataReader對象</returns>
        public static SqlDataReader GetSdrBySp(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary>
        /// 功能:執行T-SQL語句語句並返回一個SqlDataReader對象
        /// </summary>
        /// <param name="sql">參數:T-SQL語句</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter參數數組</param>
        /// <returns>返回值:SqlDataReader對象</returns>
        public static SqlDataReader GetSdrByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.Text;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

  ExecuteNonQuery

        /// <summary>
        /// 功能:執行存儲過程返回受影響的行數
        /// 返回類型:int
        /// </summary>
        /// <param name="sql">參數:存儲過程名</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter參數數組</param>
        /// <returns>返回值:int 受影響的行數</returns>
        public static int GetEnqBySp(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 功能:執行T-SQL語句返回受影響的行數
        /// </summary>
        /// <param name="sql">參數:T-SQL語句</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter參數數組</param>
        /// <returns>返回值:int 受影響的行數</returns>
        public static int GetEnqByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.Text;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Cmd.ExecuteNonQuery();
        }

  ExecuteScalar

        /// <summary>
        /// 功能:通過T-SQl語句執行SqlCommand的ExecuteScalar()方法返回object類型對象
        /// </summary>
        /// <param name="sql">參數:T-SQL語句</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter參數數組</param>
        /// <returns>返回值:object對象</returns>
        public static object GetEScalarByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.Text;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Cmd.ExecuteScalar();
        }
        /// <summary>
        /// 功能:通過執行T-SQL語句返回一個DataSet數據集對象
        /// </summary>
        /// <param name="sql">參數:T-SQL語句</param>
        /// <param name="tableName">參數:DataSet表名</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:可變SqlParameter參數數組</param>
        /// <returns>返回值:DataSet數據集對象</returns>
        public static DataSet GetDsByTSql(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            DataSet Ds = null;
            SqlDataAdapter Sda = null;
            try
            {
                Ds = new DataSet(tableName);
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.Text;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
                Sda = new SqlDataAdapter(Cmd);
                Sda.Fill(Ds, tableName);
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Ds;
        }

  DataSet

        /// <summary>
        /// 功能:通過執行存儲過程返回一個DataSet數據集對象
        /// </summary>
        /// <param name="sql">參數:存儲過程名</param>
        /// <param name="tableName">參數:DataSet表名</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:可變SqlParameter參數數組</param>
        /// <returns>返回值:DataSet數據集對象</returns>
        public static DataSet GetDsBySp(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            DataSet Ds = null;
            SqlDataAdapter Sda = null;
            try
            {
                Ds = new DataSet(tableName);
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
                Sda = new SqlDataAdapter(Cmd);
                Sda.Fill(Ds, tableName);
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Ds;
        }
        /// <summary>
        /// 功能:執行存儲過程返回一個DataTable數據表對象
        /// </summary>
        /// <param name="sql">參數:存儲過程名</param>
        /// <param name="tableName">參數:DataTable表名</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter可變參數數組</param>
        /// <returns>返回值:DataTable</returns>
        public static DataTable GetDtBySp(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            DataTable Dt = null;
            SqlDataAdapter Sda = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                Dt = new DataTable(tableName);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
                Sda = new SqlDataAdapter(Cmd);
                Sda.Fill(Dt);
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Dt;
        }

  DataTable

        /// <summary>
        /// 功能:執行T-SQL語句返回一個DataTable數據表對象
        /// </summary>
        /// <param name="sql">參數:T-SQL語句</param>
        /// <param name="tableName">參數:DataTable表名</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter可變參數數組</param>
        /// <returns>返回值:DataTable</returns>
        public static DataTable GetDtByTSql(string sql, string tableName, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            DataTable Dt = null;
            SqlDataAdapter Sda = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                Dt = new DataTable(tableName);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.Text;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
                Sda = new SqlDataAdapter(Cmd);
                Sda.Fill(Dt);
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Dt;
        }

  其它同步ADO.NET操作

        /// <summary>
        /// 功能:執行T-SQL語句判斷表中是否包含指定的內容
        /// </summary>
        /// <param name="sql">參數:T-SQL語句</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter可變參數數組</param>
        /// <returns>返回值:bool值</returns>
        public static bool IsContainFiledByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            try
            {
                using (SqlCommand Cmd = new SqlCommand(sql, Con))
                {
                    DBHelper.OpenDBConnet(Con);
                    Cmd.CommandType = CommandType.Text;

                    foreach (SqlParameter parameter in parameters)
                    {
                        Cmd.Parameters.Add(parameter);
                    }

                    using (SqlDataReader Sdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (Sdr.HasRows)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
        }

  異步操作數據庫

        /// <summary>
        /// 功能:通過存儲過程異步操作數據庫,返回SqlDataReader對象
        /// </summary>
        /// <param name="sql">參數:存儲過程名</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter可變參數數組</param>
        /// <returns>返回值:SqlDataReader對象</returns>
        public static SqlDataReader AsyncGetSdrBySp(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            IAsyncResult Iasy = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
                Iasy = Cmd.BeginExecuteReader();
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Cmd.EndExecuteReader(Iasy);
        }

        /// <summary>
        /// 功能:通過T-SQL語句異步操作數據庫,返回SqlDataReader對象
        /// </summary>
        /// <param name="sql">參數:T-SQL語句</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter可變參數數組</param>
        /// <returns>返回值:SqlDataReader對象</returns>
        public static SqlDataReader AsyncGetSdrByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            IAsyncResult Iasy = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.Text;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
                Iasy = Cmd.BeginExecuteReader();
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Cmd.EndExecuteReader(Iasy);
        }

        /// <summary>
        /// 功能:通過存儲過程異步操作數據庫返回受影響的行數
        /// </summary>
        /// <param name="sql">參數:存儲過程</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter可變參數數組</param>
        /// <returns>返回值:int 受影響行數</returns>
        public static int AsyncGetEnqBySp(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            IAsyncResult Iasy = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
                Iasy = Cmd.BeginExecuteNonQuery();
            }
            catch (SqlException s)
            {
                throw s;
            }

            return Cmd.EndExecuteNonQuery(Iasy);
        }

        /// <summary>
        /// 功能:通過T-SQL語句異步操作數據庫返回受影響的行數
        /// </summary>
        /// <param name="sql">參數:T-SQL語句</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter可變參數數組</param>
        /// <returns>返回值:int 受影響行數</returns>
        public static int AsyncGetEnqByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            IAsyncResult Iasy = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.Text;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
                Iasy = Cmd.BeginExecuteNonQuery();
            }
            catch (SqlException s)
            {
                throw s;
            }

            return Cmd.EndExecuteNonQuery(Iasy);
        }

        /// <summary>
        /// 功能:通過存儲過程異步操作數據庫返回XmlReader對象
        /// </summary>
        /// <param name="sql">參數:存儲過程名</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter可變參數數組</param>
        /// <returns>返回值:XmlReader</returns>
        public static XmlReader AsyncGetXrBySp(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            IAsyncResult Iasy = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
                Iasy = Cmd.BeginExecuteXmlReader();
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Cmd.EndExecuteXmlReader(Iasy);
        }

        /// <summary>
        /// 功能:通過T-SQL語句異步操作數據庫返回XmlReader對象
        /// </summary>
        /// <param name="sql">參數:T-SQL語句</param>
        /// <param name="Con">參數:SqlConnection對象</param>
        /// <param name="parameters">參數:SqlParameter可變參數數組</param>
        /// <returns>返回值:XmlReader</returns>
        public static XmlReader AsyncGetXrByTSql(string sql, SqlConnection Con, params SqlParameter[] parameters)
        {
            SqlCommand Cmd = null;
            IAsyncResult Iasy = null;
            try
            {
                Cmd = new SqlCommand(sql, Con);
                DBHelper.OpenDBConnet(Con);
                Cmd.CommandType = CommandType.Text;
                foreach (SqlParameter parameter in parameters)
                {
                    Cmd.Parameters.Add(parameter);
                }
                Iasy = Cmd.BeginExecuteXmlReader();
            }
            catch (SqlException s)
            {
                throw s;
            }
            return Cmd.EndExecuteXmlReader(Iasy);
        }

  其它重要方法封裝

        /// <summary>
        /// 功能:檢查字符串類型數據是不是空,為空則向數據庫插入Null
        /// </summary>
        /// <param name="notNullStr">字符串可變參數數組</param>
        /// <returns>最終要插入數據庫的數據</returns>
        public static List<object> CheckDBNullValue(params string[] notNullStr)
        {
            List<object> TheDBNllResult = new List<object>(notNullStr.Length);
            foreach (string s in notNullStr)
            {
                if ((s == string.Empty) || (s == ""))
                {
                    TheDBNllResult.Add(DBNull.Value);
                }
                else
                {
                    TheDBNllResult.Add(s.Trim());
                }
            }
            return TheDBNllResult;
        }

        /// <summary>
        /// 功能:判斷數據閱讀器讀到的指定列是否是Null,如果是Null就返回空字符串,否則就讀取字段的值
        /// </summary>
        /// <param name="Sdr">SqlDataReader</param>
        /// <param name="filedName">數據庫字段名</param>
        /// <returns>字符串表示的字段值</returns>
        public static string GetDBValue(SqlDataReader Sdr, string filedName)
        {
            string result = null;

            if (Sdr.IsDBNull(Sdr.GetOrdinal(filedName)))
            {
                result = string.Empty;
            }
            else
            {
                result = Sdr.GetString(Sdr.GetOrdinal(filedName));
            }

            return result;
        }

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved