ADO.Net 操作 SqlServer 2012 数据库封装

ADO.Net  操作 SqlServer 2012 数据库封装

一共包括MyDbTool_SqlServer.cs和Program.cs以及App.config 三个文件

MyDbTool_SqlServer.cs

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


namespace ADO.NET练习
{
    class MyDbTool_SqlServer
    {
        private string DBtype = null;

        private SqlConnection SqlConn = null;
        private string SqlConnStr = null;//记录连接服务器字符串
        private SqlCommand SqlCommand = null;


        public MyDbTool_SqlServer(string inType)
        {
            this.DBtype = inType;
            switch (DBtype.ToUpper())
            {
                case "LFC0001":
                    //SqlConnStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=LFC0001)));User Id=sa;Password=123456;";
                    //SqlConnStr = "server=.;uid=sa;pwd=123456;database=LFC0001;"//连接池默认是开启的
                    SqlConnStr = "server=.;uid=sa;pwd=123456;database=LFC0001;pooling=true";
                    //SqlConnStr = "data source=.;user id=sa;password=123456;initial catalog=LFC0001;pooling=true ;min pool size=4";
                    break;
                case "": ; break;
            }
        }

        #region DB的关闭\连接\连接状态的获取
        /// <summary>
        /// 连接指定数据库
        /// </summary>
        public void OpenConnection()
        {

            try
            {
                SqlConn = new SqlConnection();
                SqlConn.ConnectionString = SqlConnStr;

                if (SqlConn.State == System.Data.ConnectionState.Closed)
                {
                    //环境变量 设置
                    //System.Environment.SetEnvironmentVariable("nls_lang", "AMERICAN_AMERICA.WE8ISO8859P1");
                    SqlConn.Open();
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

        }

        /// <summary>
        /// 关闭当前数据库连接
        /// </summary>
        public void CloseConnection()
        {
            SqlConn.Close();
            SqlConn.Dispose();//释放连接资源
        }

        /// <summary>
        /// 获取当前数据库连接状态
        /// </summary>
        /// <returns></returns>
        public string GetConnectionState()
        {
            return SqlConn.State.ToString();
        }
        #endregion

        #region DB的查询操作

        /// <summary>
        /// 在數據庫中查詢一個數值。（需連接數據庫和處理異常）
        /// </summary>
        /// <param name="strQuery">查询语句</param>
        /// <returns>查詢數值對象</returns>
        public Object ExecuteScalarQuery(string strQuery)
        {
            SqlCommand cmd = new SqlCommand(strQuery);
            cmd.Connection = SqlConn;
            cmd.CommandType = CommandType.Text;
            Object result = cmd.ExecuteScalar();

            return result;

        }

        /// <summary>
        /// 在數據庫中查詢一個數值。（外部不需連接數據庫和處理異常） 
        /// </summary>
        /// <param name="strQuery">查询语句</param>
        /// <returns>查詢數值對象</returns>
        public Object LazyExecuteScalarQuery(string strQuery)
        {
            try
            {
                OpenConnection();
                return ExecuteScalarQuery(strQuery);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }


        /// <summary>
        /// 使用DataReader存放SELECT查詢結果，不緩存到內存。（需連接數據庫和處理異常）
        /// </summary>
        /// <param name="strQuery">sql語句</param>
        /// <returns></returns>
        public SqlDataReader ExecuteNoCacheQuery(string strQuery)
        {
            SqlCommand cmd = new SqlCommand(strQuery);
            cmd.Connection = SqlConn;
            cmd.CommandType = CommandType.Text;
            SqlDataReader reader = cmd.ExecuteReader();
            cmd.Dispose();

            return reader;
        }

        /// <summary>
        /// 使用DataReader存放SELECT查詢結果，不緩存到內存。（外部不需連接數據庫和處理異常）
        /// </summary>
        /// <param name="strQuery">sql語句</param>
        /// <returns></returns>
        public SqlDataReader LazyExecuteNoCacheQuery(string strQuery)
        {
            try
            {
                OpenConnection();
                return ExecuteNoCacheQuery(strQuery);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
                return null;
            }
            finally
            {
                //loseConnection();//SqlDataReader使用的时候得保持连接数据库不关闭
            }
        }



        /// <summary>
        /// 執行SELECT，將查詢結果存儲到DataSet中。（需連接數據庫和處理異常）
        /// </summary>
        /// <param name="strQuery">sql語句</param>
        /// <returns></returns>
        public DataSet ExecuteCacheQuery(string strQuery)
        {
            DataSet dsResult = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(strQuery, SqlConn);
            adapter.Fill(dsResult);
            adapter.Dispose();

            return dsResult;

        }

        /// <summary>
        /// 執行SELECT，將查詢結果存儲到DataSet中。（外部不需連接數據庫和處理異常）
        /// </summary>
        /// <param name="strQuery">sql語句</param>
        /// <returns></returns>
        public DataSet LazyExecuteCacheQuery(string strQuery)
        {
            try
            {
                OpenConnection();
                return ExecuteCacheQuery(strQuery);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        
        #endregion

        #region DB的增删改查操作

        /// <summary>
        /// 通过SqlDataAdapter快使更新DB数据.（外部不需連接數據庫和處理異常）
        /// </summary>
        /// <param name="strQuery">一定要与查询的时候的sql语句一样</param>
        /// <param name="dt">DataSource 可以是DataTable类型 </param>
        public bool LazyQuickUpdateDbRowsBySqlDataAdapter(string strQuery, DataTable dt)
        {

            try
            {

                using (SqlDataAdapter adapter = new SqlDataAdapter(strQuery, SqlConnStr))
                {
                    //SqlCommandBuilder 帮我们的Adapter生成相关的CRUD
                    using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter))
                    {

                        adapter.Update(dt);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
                return false;
            }

        }

        /// <summary>
        /// 執行一条UPDATE、DELETE、INSERT。（外部不需連接數據庫和處理異常）
        /// </summary>
        /// <param name="str">sql語句</param>
        /// <returns>執行狀態：1： 成功； 0：不成功</returns>
        public int LazyExecuteNonQuery(string str)
        {
            int iResult = 0;
            try
            {
                OpenConnection();
                SqlCommand = new SqlCommand(str, SqlConn);
                iResult = SqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
            }
            finally
            {
                SqlCommand.Dispose();
                CloseConnection();                
            }
            return iResult;
        }

        /// <summary>
        /// 執行一条UPDATE、DELETE、INSERT。（需連接數據庫和處理異常）
        /// </summary>
        /// <param name="str">查詢sql語句</param>
        /// <returns>執行狀態：1： 成功； 0：不成功</returns>
        public int ExecuteNonQuery(string str)
        {
            int iResult = 0;
            SqlCommand = new SqlCommand(str, SqlConn);
            iResult = SqlCommand.ExecuteNonQuery();
            SqlCommand.Dispose();

            return iResult;
        }


        /// <summary>
        /// 執行多條UPDATE、DELETE、INSERT。（外部不需連接數據庫和處理異常）
        /// </summary>
        /// <param name="list">sql語句列表</param>
        public void LazyExecuteNonQueryTransac(List<string> list)
        {
            SqlTransaction txn = null;
            SqlCommand cmd = null;
            try
            {
                OpenConnection();
                txn = SqlConn.BeginTransaction();
                cmd = new SqlCommand("", SqlConn, txn);
                for (int i = 0; i < list.Count; i++)
                {
                    string sql = list[i].Trim();
                    if (sql.Length > 1)
                    {
                        cmd.CommandText = list[i];
                        cmd.ExecuteNonQuery();
                    }
                }
                txn.Commit();
            }
            catch (Exception ex)
            {
                txn.Rollback();
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
            }
            finally
            {
                cmd.Dispose();
                txn.Dispose();
                CloseConnection();
            }
        }

        /// <summary>
        /// 執行多條UPDATE、DELETE、INSERT。（外部需連接數據庫和處理異常）
        /// </summary>
        /// <param name="list">sql語句列表</param>
        public void ExecuteNonQueryTransac(List<string> list)
        {
            SqlCommand cmd = new SqlCommand("", SqlConn);
            SqlTransaction txn = SqlConn.BeginTransaction();            
            cmd.Transaction = txn;
            try
            {
                for (int i = 0; i < list.Count; i++)
                {
                    string sql = list[i].Trim();
                    if (sql.Length > 1)
                    {
                        cmd.CommandText = list[i];
                        cmd.ExecuteNonQuery();
                    }
                }
                txn.Commit();
            }
            catch (Exception ex)
            {
                txn.Rollback();
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                txn.Dispose();
            }
        }


        #endregion 

        #region 執行存儲過程,無返回記錄 。除此之外当前MyDbTool_SqlServer类中所有的NonQuery的方法均可执行过程化sql


        /// <summary>
        /// 執行存儲過程,無返回記錄。（外部不需連接數據庫和處理異常）。
        /// </summary>
        /// <param name="spname">spname 存儲過程名稱</param>
        /// <param name="hxqb">hxqb  參數</param>
        public void ExecuteSPNonQuery(string spname, string hxqb)
        {
            int iResult = 0;
            OpenConnection();
            SqlCommand = new SqlCommand(spname, SqlConn);
            SqlCommand.CommandType = CommandType.StoredProcedure;
            SqlCommand.Parameters.Add("ID", hxqb);
            try
            {
                iResult = SqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                SqlCommand.Dispose();
                CloseConnection();
            }
        }
        #endregion


    }
}

Program.cs

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


namespace ADO.NET练习
{
    class Program
    {
        static void Main(string[] args)
        {
            MyDbTool_SqlServer myDb = new MyDbTool_SqlServer("LFC0001");
            
            //string sql = "select * from TEST0001 ;";
            //string value= GetValue(myDb, sql);

           // GetSqlDataReader(myDb, sql);

            // GetDataSetToDataTable(myDb, sql);

          
           //bool flag= UpdateDbRowsBySqlDataAdapter(myDb, sql, GetDataSetToDataTable(myDb, sql));

//            List<string> sqlList = new List<string>(){
//           string.Format(@"INSERT INTO [dbo].[TEST0001]
//           ([ID]
//           ,[AA]
//           ,[BB]
//           ,[CC])
//     VALUES
//           (5,null,null,null      
//		   );"),
//                 string.Format(@"INSERT INTO [dbo].[TEST0001]
//           ([ID]
//           ,[AA]
//           ,[BB]
//           ,[CC])
//     VALUES
//           (6,null,null,null      
//		   );")

//        };
//            myDb.OpenConnection();
//         myDb.ExecuteNonQueryTransac(sqlList);
//         myDb.CloseConnection();

            string sql = string.Format(@"
DECLARE	@return_value int

EXEC	@return_value = [dbo].[SP_Insert_Test0001]
		@id = N'5'

SELECT	'Return Value' = @return_value

");

            myDb.LazyExecuteNonQuery(sql);

            //myDb.OpenConnection();
            //myDb.ExecuteSPNonQuery("[dbo].[SP_Insert_Test0001]","5");
            //myDb.CloseConnection();

           

            Console.WriteLine(myDb.GetConnectionState());



           


            Console.ReadKey();
        }


        /// <summary>
        /// 使用DataAdapter去更新数据库
        /// </summary>
        /// <param name="myDb"></param>
        /// <param name="sql">必须与查询出DataTable的SQL一致</param>
        /// <param name="dt"></param>
        /// <returns></returns>
        private static bool UpdateDbRowsBySqlDataAdapter(MyDbTool_SqlServer myDb, string sql, DataTable dt)
        {
            DataRow dr = dt.NewRow();//添加行数据

            dr["ID"] = "4";
            dr["aa"] = "aaaa";
            dr["bb"] = "bbbb";
            dr[3] = "cccc";

            dt.Rows.Add(dr);

            bool flag = myDb.LazyQuickUpdateDbRowsBySqlDataAdapter(sql, dt);

            return flag;
        }

        /// <summary>
        /// 获取DataSet，并返回DataSet中指定的DataTable
        /// </summary>
        /// <param name="myDb"></param>
        /// <param name="sql"></param>
        private static DataTable GetDataSetToDataTable(MyDbTool_SqlServer myDb, string sql)
        {
            DataSet dset = myDb.LazyExecuteCacheQuery(sql);
            DataTable dt = dset.Tables["table"];//dset.Tables[0];

            //打印dt内容
            //foreach (DataRow item in dt.Rows)
            //{
            
            //    Console.WriteLine(
            //        item[0].ToString()+
            //        item[1].ToString()+
            //        item["bb"].ToString() +
            //        item["cc"].ToString());
                
            //}

            return dt;
        }




        /// <summary>
        /// 获取SqlDataReader并打印
        /// </summary>
        /// <param name="myDb"></param>
        /// <param name="sql"></param>

        private static void GetSqlDataReader(MyDbTool_SqlServer myDb, string sql)
        {
            SqlDataReader reader = myDb.LazyExecuteNoCacheQuery(sql);
            //DataTable dt = reader.GetSchemaTable();

            while (reader.Read())
            {

                Console.WriteLine(
                    reader["id"].ToString()
                  + reader[1].ToString()
                  + reader["bb"].ToString()
                  + reader["cc"].ToString()
                  );

            }
            // reader.Close();
            myDb.CloseConnection();//使用SqlDataReader后别忘了关闭连接
        }


        /// <summary>
        /// 查询一个值出来，（首行首列的值）
        /// </summary>
        /// <param name="myDb"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static string  GetValue(MyDbTool_SqlServer myDb, string sql)
        {
            string value = myDb.LazyExecuteScalarQuery(sql).ToString();
            return value;
        }
    }
}

App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <!--应用程序配置-->
  <appSettings>
    <add key="Version" value="1.0.0.1"></add>
    <add key="SqlConn" value="server=.;uid=sa;pwd=123456;database=LFC0001;pooling=true"></add>
  </appSettings>

  <!--连接字符串的配置节点-->
  <connectionStrings>
    <add name="SqlConn" connectionString="server=.;uid=sa;pwd=123456;database=LFC0001;pooling=true"/>
  </connectionStrings>
  
</configuration>

<!--
在C#中添加System.Configuration程序集引用来访问该config内容（适用于控制台和winform项目）
 Console.WriteLine(ConfigurationManager.AppSettings["Version"]);
            Console.WriteLine(ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString);
-->