访问数据库的通用类

 

 

using System;
using System.Data;
using System.Data.SqlClient;


namespace CommonClass
...{
    /**//// <summary>
    /// DBClient 的摘要说明。
    /// </summary>
    public class DBClient
    ...{
        //数据库连接字符串,(开发过程中)到app.config里修改
        private string connectionString;
        
        public DBClient()
        ...{
            // 读取数据库连接字符串并赋给字符串引用变量connectionString;
            connectionString=System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
        }
        public SqlConnection createConnection()
        ...{ 
            // 返回一个用户连接数据库的一个实例。
            return new SqlConnection(connectionString);

        }
    
    　
      /**//// <summary>
      ///  
      /// </summary>
      /// <param name="selectSQL"></param>
      /// <param name="tableName"></param>
      /// <returns></returns>
        
        public DataTable getSQLTable(string selectSQL,string tableName)
        ...{
            DataTable table=new DataTable(tableName);

              SqlConnection sqlConnection=this.createConnection();
            try
            ...{
                SqlDataAdapter sqlDataAdapter=new SqlDataAdapter(selectSQL,sqlConnection);
                sqlDataAdapter.Fill(table);
            }
            catch(System.Data.SqlClient.SqlException ex)
            ...{ 
                throw ex;
            }
            catch(System.Exception ex)
            ...{ 
                throw ex;

            }
            finally
            ...{ 
                if(sqlConnection.State != ConnectionState.Closed)
                ...{
                    sqlConnection.Close();
                }
            }
            return table;

        } 
    
        public DataTable getSQLTable(string selectSQL,string tableName,string []sourceColumn ,string []strMapField)
        ...{
            if(sourceColumn.Length!=strMapField.Length)
            ...{ 
                throw new Exception("源数据例名与映射例名个数不相等！");
            }
            DataTable table=new DataTable(tableName);
            
            SqlConnection sqlConnection=this.createConnection();
            try
            ...{
                
                System.Data.Common.DataTableMapping dtMappings=new System.Data.Common.DataTableMapping(tableName,tableName);

                for(int i=0;i<strMapField.Length;i++)
                  ...{
                   dtMappings.ColumnMappings.Add(sourceColumn[i],strMapField[i]);
                    
                }

                SqlDataAdapter sqlDataAdapter=new SqlDataAdapter(selectSQL,sqlConnection);
                sqlDataAdapter.TableMappings.Add(dtMappings);
                sqlDataAdapter.Fill(table);
            }
            catch(System.Data.SqlClient.SqlException ex)
            ...{ 
                throw ex;
            }
            catch(System.Exception ex)
            ...{ 
                throw ex;

            }
            finally
            ...{ 
                if(sqlConnection.State != ConnectionState.Closed)
                ...{
                    sqlConnection.Close();
                }
            }
            return table;
        }
        public int executeSQL(string cmdText)
        ...{ 
            int iValue=-1;
            SqlConnection sqlConnection=this.createConnection();
                          sqlConnection.Open();
             SqlTransaction sqlTransaction=sqlConnection.BeginTransaction();
            try
            ...{
                SqlCommand sqlCommand=new SqlCommand();
                sqlCommand.Connection=sqlConnection;
                sqlCommand.CommandText=cmdText;
                sqlCommand.Transaction=sqlTransaction;
                
                iValue=sqlCommand.ExecuteNonQuery();
                sqlTransaction.Commit();
            }
            catch(System.Data.SqlClient.SqlException ex)
            ...{
                sqlTransaction.Rollback();
                throw ex;

            }
            catch(System.Exception ex2)
            ...{ 
                throw ex2;
            }
            finally
            ...{ 
                sqlConnection.Close();
            }
            return iValue;

        }
    }
}