数据库操作基类,实现对Sql数据库的各种操作.

using System;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace 4ec
{
   
    /// <summary>
    /// 数据库操作基类
    /// 实现对Sql数据库的各种操作
    /// 创建时间：2007-3-30
    /// </summary>
    public class SqlDataBase
    {
        //获取Web.Config数据库连接字符串
        private readonly string OleDbConnectionString = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + HttpContext.Current.Server.MapPath(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        private OleDbConnection cn;  //创建SQL连接
        private OleDbDataAdapter sda;  //创建SQL数据适配器
        private OleDbDataReader sdr;  //创建SQL数据读取器
        private OleDbCommand cmd;   //创建SQL命令对象
       // private OleDbParameter param;     //创建SQL参数
        private DataSet ds;    //创建数据集
        private DataView dv;   //创建视图       

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            #region
            cn = new OleDbConnection(OleDbConnectionString);
            cn.Open();
            #endregion
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            #region
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
            }
            #endregion
        }

        /// <summary>
        /// 返回DataSet数据集
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        public DataSet GetDs(string strSql)
        {
            #region
            Open();
            sda = new OleDbDataAdapter(strSql, cn);
            ds = new DataSet();
            sda.Fill(ds);
            Close();
            return ds;
            #endregion
        }

        /// <summary>
        /// 添加DataSet表
        /// </summary>
        /// <param name="ds">DataSet对象</param>
        /// <param name="strSql">Sql语句</param>
        /// <param name="strTableName">表名</param>
        public void GetDs(DataSet ds, string strSql, string strTableName)
        {
            #region
            Open();
            sda = new OleDbDataAdapter(strSql, cn);
            sda.Fill(ds, strTableName);
            Close();
            #endregion
        }

        /// <summary>
        /// 返回DataView数据视图
        /// </summary>
        /// <param name="strSql">Sql语句</param>
        public DataView GetDv(string strSql)
        {
            #region
            dv = GetDs(strSql).Tables[0].DefaultView;
            return dv;
            #endregion
        }

        /// <summary>
        /// 获得DataTable对象
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns></returns>
        public DataTable GetTable(string strSql)
        {
            #region
            return GetDs(strSql).Tables[0];
            #endregion
        }

        /// <summary>
        /// 获得OleDbDataReader对象 使用完须关闭DataReader,关闭数据库连接
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns></returns>
        public OleDbDataReader GetDataReader(string strSql)
        {
            #region
            Open();
            cmd = new OleDbCommand(strSql, cn);
            sdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            return sdr;
            #endregion
        }

 

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="strSql"></param>
        public void RunSql(string strSql)
        {
            #region
            Open();
            cmd = new OleDbCommand(strSql, cn);
            cmd.ExecuteNonQuery();
            Close();
            #endregion
        }

 

        /// <summary>
        /// 执行SQL语句，并返回第一行第一列结果
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns></returns>
        public string RunSqlReturn(string strSql)
        {
            #region
            string strReturn = "";
            Open();
            try
            {
                cmd = new OleDbCommand(strSql, cn);
                strReturn = cmd.ExecuteScalar().ToString();
            }
            catch { }
            Close();
            return strReturn;
            #endregion
        }

    }

}