ASP.NET(C#)通用数据库类

 

---------.NET Framework 2.0下(ACCESS和SqlServer数据库)测试通过

Web.Config文件代码：(配置文件)

<? xml version="1.0" encoding="utf-8" ?>
< configuration >
   < appSettings >
     < add  key ="Myconn"  value ="Access" />
   </ appSettings >
   < connectionStrings >
     < add  name ="oleconn"  providerName ="System.Data.OleDb"  connectionString ="Provider=Microsoft.Jet.OleDb.4.0;Data 

Source=|DataDirectory|92dotnet.mdb" />
     < add  name ="sqlconn"  providerName ="System.Data.Sql"  connectionString ="server=

(local);database=92dotnet;uid=sa;pwd=gmajlhx" />
   </ connectionStrings >
</ configuration >

Mydataone.cs文件代码：(基类)

using  System;
using  System.Data;
using  System.Configuration;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;
using  System.Data.OleDb;
using  System.Data.SqlClient;
namespace  Mydotnet     
{
    public class Mydataone   //数据层基类
    {
        public Mydataone()
        {
            
        }
        /**//// <summary>
        /// 返回一个Connection对象 
        /// </summary>
        /// <param name="Connstring">Myconn的值,判断Connection对象属于哪种类型</param>
        /// <returns>conn</returns>
        protected static IDbConnection Createconnection(string Connstring)
        {
            IDbConnection conn=null;
            switch (Connstring)
            {
                case "Access":
                    conn = new OleDbConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings

["oleconn"].ConnectionString);
                    break;
                case "SqlServer":
                    conn = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings

["sqlconn"].ConnectionString);
                    break;
            }
            return conn;

        }
        /**//// <summary>
        /// 返回一个Command对象(重载方法一)
        /// </summary>
        /// <param name="com">sql语句或存储过程名</param>
        /// <param name="commandtype">命令类型</param>
        /// <param name="Connstring">Myconn的值,判断Command对象属于哪种类型</param>
        /// <param name="conn">Connection接口对象</param>
        /// <returns>comm</returns>
        protected static IDbCommand Createcommand(string com,CommandType commandtype,string Connstring,IDbConnection conn)
        {
            IDbCommand comm=null;
            switch (Connstring)
            {
                case "Access":
                    comm = new OleDbCommand();
                    comm.Connection =(OleDbConnection)conn;
                    comm.CommandText = com;
                    comm.CommandType = commandtype;
                    break;
                case "SqlServer":
                    comm = new SqlCommand();
                    comm.Connection = (SqlConnection)conn;
                    comm.CommandText = com;
                    comm.CommandType = commandtype;
                    break;
            }
            return comm;
        }
        /**//// <summary>
        /// 返回一个Command对象(重载方法二)
        /// </summary>
        /// <param name="com">sql语句或存储过程名</param>
        /// <param name="commandtype">命令类型</param>
        /// <param name="Connstring">Myconn的值,判断Command对象属于哪种类型</param>
        /// <param name="conn">Connection接口对象</param>
        /// <param name="param">参数数组</param>
        /// <returns>comm</returns>
        protected static IDbCommand Createcommand(string com, CommandType commandtype, string Connstring,IDataParameter[] 

param,IDbConnection conn)
        {
            IDbCommand comm = null;
            switch (Connstring)
            {
                case "Access":
                    comm = new OleDbCommand();
                    comm.Connection = (OleDbConnection)conn;
                    comm.CommandText = com;
                    comm.CommandType = commandtype;
                    foreach (OleDbParameter par in param)
                    {
                        comm.Parameters.Add(par);
                    }
                    break;
                case "SqlServer":
                    comm = new SqlCommand();
                    comm.Connection = (SqlConnection)conn;
                    comm.CommandText = com;
                    comm.CommandType = commandtype;
                    foreach (SqlParameter par in param)
                    {
                        comm.Parameters.Add(par);
                    }
                    break;
            }
            return comm;
        }
        /**//// <summary>
        /// 返回一个DataAdapter对象
        /// </summary>
        /// <param name="comm">command接口对象</param>
        /// <param name="Connstring">Myconn的值,判断DataAdapter对象属于哪种类型</param>
        /// <returns></returns>
        protected static IDbDataAdapter Createadapter(IDbCommand comm,string Connstring)
        {
            IDbDataAdapter adapter=null;
            switch (Connstring)
            {
                case "Access":
                    adapter= new OleDbDataAdapter((OleDbCommand)comm);
                    break;
                case "SqlServer":
                    adapter= new SqlDataAdapter((SqlCommand)comm);
                    break;
            }
            return adapter;
        }
    }
        
}

Mydatatwo.cs文件代码：(继承类)

using  System;
using  System.Data;
using  System.Configuration;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;
namespace  Mydotnet     // 同一个名字空间
{
    public class Mydatatwo : Mydataone  //继承数据类
    {
        public static string Connstring=System.Configuration.ConfigurationSettings.AppSettings["Myconn"]; //得到Web.Config里的值
        public static IDbConnection conn=Createconnection(Connstring);  //调用基类方法得到Connection对象 
        public Mydatatwo()
        {
            
        }
        /**//// <summary>
        /// 有返回值的Executescalar方法和数据表第一行第一列的值(string类型)
        /// </summary>
        /// <param name="com">sql语句或存储过程名</param>
        /// <param name="commandtype">命令类型</param>
        /// <returns>str</returns>
        public static string Executescalar(string com, CommandType commandtype)
        {
                string str = "";
                try
                {
                   if (conn.State == ConnectionState.Closed)
                   {
                      conn.Open();
                      IDbCommand comm = Createcommand(com, commandtype, Connstring, conn);
                      if (comm.ExecuteScalar() != null)
                      {
                         str = comm.ExecuteScalar().ToString();
                      }
                   }
                }
                catch(Exception ee)
                {
                    throw ee;
                }
                finally
                {
                   if (conn.State == ConnectionState.Open)
                   {
                      conn.Close();
                   }
                }
            return str;
        }
        /**//// <summary>
        /// 有返回值的Executescalar方法和数据表第一行第一列的值(string类型)
        /// </summary>
        /// <param name="com">sql语句或存储过程名</param>
        /// <param name="commandtype">命令类型</param>
        /// <param name="param">参数数组</param>
        /// <returns>str</returns>
        public static string Executescalar(string com, CommandType commandtype,IDataParameter[] param)
        {
                string str = "";
                try
                {
                   if (conn.State == ConnectionState.Closed)
                   {
                      conn.Open();
                      IDbCommand comm = Createcommand(com, commandtype, Connstring,param,conn);
                      if (comm.ExecuteScalar() != null)
                      {
                        str = comm.ExecuteScalar().ToString();
                      }
                   }
                }
                catch (Exception ee)
                {
                    throw ee;
                }
                finally
                {
                   if (conn.State == ConnectionState.Open)
                   {
                       conn.Close();
                   }
                }
            return str;
        }
        /**//// <summary>
        /// 一个无返回值的Executenonquery方法和被影响记录条数
        /// </summary>
        /// <param name="com">sql语句或存储过程名</param>
        /// <param name="commandtype">命令类型</param>
        /// <returns>icount</returns>
        public static int Executenonquery(string com, CommandType commandtype)
        {
                int icount=0;
                try
                {
                   if (conn.State == ConnectionState.Closed)
                   {
                     conn.Open();
                     IDbCommand comm = Createcommand(com, commandtype, Connstring,conn);
                     icount = comm.ExecuteNonQuery();
                   }
                }
                catch (Exception ee)
                {
                    throw ee;
                }
                finally
                {
                   if (conn.State == ConnectionState.Open)
                   {
                     conn.Close();
                   } 
                }
            return icount;
        }
        /**//// <summary>
        /// 一个无返回值的Executenonquery方法和被影响记录条数
        /// </summary>
        /// <param name="com">sql语句或存储过程名</param>
        /// <param name="commandtype">命令类型</param>
        /// <param name="param">参数数组</param>
        /// <returns>icount</returns>
        public static int Executenonquery(string com, CommandType commandtype,IDataParameter[] param)
        {
                int icount = 0;
                try
                {
                   if (conn.State == ConnectionState.Closed)
                   {
                     conn.Open();
                     IDbCommand comm = Createcommand(com, commandtype, Connstring,param,conn);
                     icount= comm.ExecuteNonQuery();
                   }
                }
                catch (Exception ee)
                {
                    throw ee;
                }
                finally
                {
                   if (conn.State == ConnectionState.Open)
                   {
                      conn.Close();
                   }
                }
            return icount;
        }
        /**//// <summary>
        /// 返回一个DataReader对象
        /// </summary>
        /// <param name="com">sql语句或存储过程名</param>
        /// <param name="commandtype">命令类型</param>
        /// <returns>dr</returns>
        public static IDataReader Executereader(string com, CommandType commandtype)
        {
                IDataReader dr=null;
                try
                {
                   if (conn.State == ConnectionState.Closed)
                   {
                     conn.Open();
                     IDbCommand comm = Createcommand(com, commandtype, Connstring, conn);
                     dr = comm.ExecuteReader(CommandBehavior.CloseConnection); //也就是说如果后面调用了dr.Close()方法，则conn.Close()也会随之调用
                   }
                }
                catch (Exception ee)
                {
                    throw ee;
                }
            
            return dr;
        }
        /**//// <summary>
        /// 返回一个DataReader对象
        /// </summary>
        /// <param name="com">sql语句或存储过程名</param>
        /// <param name="commandtype">命令类型</param>
        /// <param name="param">参数数组</param>
        /// <returns>dr</returns>
        public static IDataReader Executereader(string com, CommandType commandtype, IDataParameter[] param)
        {
                IDataReader dr=null;
                try
                {
                   if (conn.State == ConnectionState.Closed)
                   {
                     conn.Open();
                     IDbCommand comm = Createcommand(com, commandtype, Connstring, param, conn);
                     dr = comm.ExecuteReader(CommandBehavior.CloseConnection);  //也就是说如果后面调用了dr.Close()方法，则conn.Close()也会随之调用
                   }
                }
                catch (Exception ee)
                {
                    throw ee;
                }
            return dr;
        }
        /**//// <summary>
        /// 返回一个DataSet对象
        /// </summary>
        /// <param name="com">sql语句或存储过程名</param>
        /// <param name="commandtype">命令类型</param>
        /// <returns>ds</returns>
        public static DataSet Executedataset(string com, CommandType commandtype)
        {
                DataSet ds=null;
                try
                {
                   if (conn.State == ConnectionState.Closed)
                   {
                     conn.Open();
                     IDbCommand comm = Createcommand(com, commandtype, Connstring,conn);
                     IDbDataAdapter adapter = Createadapter(comm, Connstring);
                     ds = new DataSet();
                     adapter.Fill(ds);
                   }
                }
                catch (Exception ee)
                {
                    throw ee;
                }
                finally
                {
                   if (conn.State == ConnectionState.Open)
                   {
                      conn.Close();
                   } 
                }
            return ds;
        }
        /**//// <summary>
        /// 返回一个DataSet对象
        /// </summary>
        /// <param name="com">sql语句或存储过程名</param>
        /// <param name="commandtype">命令类型</param>
        /// <param name="param">参数数组</param>
        /// <returns>ds</returns>
        public static DataSet Executedataset(string com, CommandType commandtype,IDataParameter[] param)
        {
                DataSet ds=null;
                try
                {
                   if (conn.State == ConnectionState.Closed)
                   {
                     conn.Open();
                     IDbCommand comm = Createcommand(com, commandtype, Connstring, param, conn);
                     IDbDataAdapter adapter = Createadapter(comm, Connstring);
                     ds =new DataSet();
                     adapter.Fill(ds);
                   }
                }
                catch (Exception ee)
                {
                    throw ee;
                }
                finally
                {
                   if (conn.State == ConnectionState.Open)
                   {
                     conn.Close();
                   } 
                }
            return ds;
        }
        /**//// <summary>
        /// 返回一个DataTable对象
        /// </summary>
        /// <param name="com">Sql语句或存储过程名</param>
        /// <param name="commandtype">命令对象</param>
        /// <returns>dt</returns>
        public static DataTable Executetable(string com, CommandType commandtype)
        {
                DataSet ds=null;
                DataTable dt=null;
                try
                {
                   if (conn.State == ConnectionState.Closed)
                   {
                      conn.Open();
                      IDbCommand comm = Createcommand(com, commandtype, Connstring,conn);
                      IDbDataAdapter adapter = Createadapter(comm, Connstring);
                      ds = new DataSet();
                      adapter.Fill(ds);
                      dt = ds.Tables[0];
                   }
                }
                catch (Exception ee)
                {
                    throw ee;
                }
                finally
                {
                   if (conn.State == ConnectionState.Open)
                   {
                      conn.Close();
                   }
                }
            return dt;
        }
        /**//// <summary>
        /// 返回一个DataTable对象
        /// </summary>
        /// <param name="com">Sql语句或存储过程名</param>
        /// <param name="commandtype">命令对象</param>
        /// <param name="param">参数数组</param>
        /// <returns>dt</returns>
        public static DataTable Executetable(string com, CommandType commandtype, IDataParameter[] param)
        {
            DataSet ds=null;
            DataTable dt=null;
            try
                {
                   if (conn.State == ConnectionState.Closed)
                   {
                     conn.Open();
                     IDbCommand comm = Createcommand(com, commandtype, Connstring, param, conn);
                     IDbDataAdapter adapter = Createadapter(comm, Connstring);
                     ds = new DataSet();
                     adapter.Fill(ds);
                     dt = ds.Tables[0];
                   }
                }
                catch (Exception ee)
                {
                    throw ee;
                }
                finally
                {
                   if (conn.State == ConnectionState.Open)
                   {
                      conn.Close();
                   }
                }
            }
            return dt;
        }
        public static void Closeconnection()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Close();
            }
        }
        //继续更新中。。。。。

    }
}