using System;   
using System.Collections.Generic;   
using System.Text;   
using System.Data.SqlClient;   
using System.Data;   
namespace My_Connection   
{   
    class DbConn   
     {   
        private const string ConnString = "server=localhost;integrated security=sspi;database=pubs;";   
        //---------------------常用的连接字符串参数-------------------------------------------------   
        //server=locahost . 登录服务器地址这里是本地   
        //Data Source   
        //Address   
        //Addr   
        //Network Address   
        //integrated security=sspitrue Windows当前身份登录   
        //uid=sa; 登录用户名:sa   
        //pwd=sa;    登录密码:sa   
        //Connect Timeout=15 //设置连接等待时间,以秒为单位   
        //Trusted_Connection=True 设置信任连接   
        //Asynchronous Processing=true 设置异步访问数据库,默认关闭   
        //MultipleActiveResultSets=True 在单个连接上得到和管理多个、仅向前引用和只读的结果集(ADO.NET2.0,SQL 2005)   
        //database=pubs Initial Catalog=pubs 指定数据库:pubs   
        //Max Pool Size 最大连接数   
        //Min Pool Size 最小连接数          
        //Pooling 当设置为true,SQL连接将从连接池获得,如果没有则新建并添加到连接池中,默认是true.false则不设连接池   
        //Connection Lifetime 在连接池中应用,指定一个连接处于close后的生存时间大于指定时间并属于最小连接数以外的将自动消毁           
        //Application Name 应用程序名称或者当没有提供应用程序时为.Net SqlClient数据提供者   
        //Connection Reset true 当连接从连接池移走时决定是否重置数据库连接.当设置为 false 时用于避免获得连接时的额外服务器往复代价   
        //Enlist true 为真时,连接池自动列出创建线程的当前事务上下文中的连接   
        //Workstation ID 指定工作组的名称   
        //Packet Size= 就是设置网络数据包的大小值,默认为8192   
        //Network Library 设置网络连接协议   
  
        //当通过SQLOLEDB提供者进行连接时使用以下语法:   
        //Network Library=dbmssocn   
        //但通过MSDASQL提供者进行连接时使用以下语法:   
        //Network=dbmssocn    
  
        //名称            网络协议库   
        //dbnmpntw Win32 Named Pipes   
        //dbmssocn Win32 Winsock TCP/IP   
        //dbmsspxn Win32 SPX/IPX   
        //dbmsvinn Win32 Banyan Vines   
        //dbmsrpcn Win32 Multi-Protocol (Windows RPC)   
        //------------------------连接字符串示例   -----------------------------------------------------------------------------   
        //通过IP地址连接,必需确保SQL服务器开启1433端口和检查SQL网络连接启用TCP/IP协议   
        //Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;    
        //默认情况下, SQL服务器的微软.NET框架数据提供者设置网络包大小对8192个字节.   
        //然而这不一定是最好的,你可以根据你觉的合适的包大小设置包的大小   
        //Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;Packet Size=4096;    
        //-----------------------------------------------------------------------------------------------   
        //因为考虑到实现复杂业务逻辑时,需要同进使用GetDataReaderGetDataAdapterGetTransaction   
        //连接那里会变得麻烦,所以区分定义三种方式的连接对象   
        private SqlConnection SqlDrConn = new SqlConnection(ConnString);   
        private SqlConnection SqlDaConn = new SqlConnection(ConnString);   
        private SqlConnection SqlTrConn = new SqlConnection(ConnString);   
  
        public DataTable GetDataReader(string StrSql)//数据查询   
         {   
            //当连接处于打开状态时关闭,然后再打开,避免有时候数据不能及时更新   
            if (SqlDrConn.State == ConnectionState.Open)   
             {   
                 SqlDrConn.Close();                   
             }   
            try  
             {   
                 SqlDrConn.Open();   
                 SqlCommand SqlCmd = new SqlCommand(StrSql, SqlDrConn);   
                 SqlDataReader SqlDr = SqlCmd.ExecuteReader();   
                if (SqlDr.HasRows)   
                 {   
                     DataTable dt = new DataTable();   
                    //---------------Method 1-------------------   
                    //for (int i = 0; i < SqlDr.FieldCount; i++)   
                    //{   
                    //     dt.Columns.Add(SqlDr.GetName(i), SqlDr.GetFieldType(i));   
                    //}   
                    //while (SqlDr.Read())   
                    //{   
                    //     DataRow dr= dt.NewRow();   
                    //     for (int i = 0; i < SqlDr.FieldCount; i++)   
                    //     {   
                    //         dr[i] = SqlDr[i];                               
                    //     }   
                    //     dt.Rows.Add(dr);                           
                    //}       
                    //---------------Method 2-------------------   
                    //更为简单   
                     dt.Load(SqlDr);   
                    //关闭对象和连接   
                     SqlDr.Close();   
                     SqlDrConn.Close();   
                    return dt;   
                 }   
                return null;   
             }   
            catch (Exception ex)   
             {   
                 System.Windows.Forms.MessageBox.Show(ex.Message);             
                return null;   
             }   
            finally  
             {           
                 SqlDrConn.Close();   
             }   
         }   
  
        public SqlDataAdapter GetDataAdapter(string StrSql)//数据增删修查   
         {   
            if (SqlDaConn.State == ConnectionState.Open)   
             {   
                 SqlDaConn.Close();   
             }   
            try  
             {   
                 SqlDaConn.Open();   
                 SqlDataAdapter SqlDa = new SqlDataAdapter(StrSql, SqlDaConn);   
                //提供自动生成单表命令的一种方式   
                 SqlCommandBuilder SqlCb = new SqlCommandBuilder(SqlDa);   
                return SqlDa;   
             }   
            catch (Exception ex)   
             {   
                 System.Windows.Forms.MessageBox.Show(ex.Message);   
                return null;   
             }   
            finally  
             {                   
                 SqlDaConn.Close();   
             }   
         }   
  
        //这里使用了ArrayList数组来存放SQL语句集   
        public bool GetTransaction(System.Collections.ArrayList StrSqlList)//实现事务   
         {   
            if (SqlTrConn.State == ConnectionState.Open)   
             {   
                 SqlTrConn.Close();   
             }   
             SqlTransaction SqlTr = null;   
            try  
             {   
                 SqlTrConn.Open();   
                 SqlTr = SqlTrConn.BeginTransaction();   
                 SqlCommand SqlCmd = new SqlCommand();   
                 SqlCmd.Connection = SqlTrConn;   
                 SqlCmd.Transaction = SqlTr;               
                //加载数组里的SQL语句   
                for (int i = 0; i < StrSqlList.Count; i++)   
                 {   
                     SqlCmd.CommandText = StrSqlList[i].ToString();   
                     SqlCmd.ExecuteNonQuery();   
                 }   
                //提交所有事务   
                 SqlTr.Commit();                   
                return true;   
             }   
            catch (Exception ex)   
             {   
                //一但出错,回滚所有事务,还原状态   
                if(SqlTr!=null) SqlTr.Rollback();                         
                 System.Windows.Forms.MessageBox.Show(ex.Message);   
                return false;   
             }                          
            finally  
             {                
                 SqlTrConn.Close();   
             }   
         }   
     }   
}