数据库的基础类

---

2009-11-29 23:27:10

以前写了一个，但是由于硬盘挂了，所以又写了一遍，这次我没有生成dll文件，直接写的，现在备份一份

大家可以直接拷贝使用

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;

/// <summary>
///datahelp 的摘要说明
/// </summary>
namespace shijiu
{
    /// <summary>
    /// datahelp 的摘要说明。
    /// 用来数据库连接以及数据存储的方法
    /// </summary>
    
    public enum dbType
    {
        /// <summary>
        /// SQL数据库
        /// </summary>
        sql,
        /// <summary>
        /// access数据库
        /// </summary>
        access
    }

    public class datahelp
    {
        // 连接数据源
        private SqlConnection con = null;
        private OleDbConnection conn = null;

        //连接数据类型
        dbType cType;

        public datahelp(dbType type)//选择数据库，以及相应数据库的连接字符串
        {
            //web.cofig定义数据库的连接字符串
            string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStr"].ConnectionString;

            this.cType = type;

            if (type == dbType.sql)  //sqlsever 数据库
            {
                con = new SqlConnection(conStr);
            }
            else
            {
                if (type == dbType.access)  //access 数据库库
                {
                    conn = new OleDbConnection(conStr);
                }
            }
        }

       /// <summary>
       /// 根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
       /// </summary>
       /// <param name="sql">查询语句</param>
       /// <returns>DataSet</returns>

        public DataSet returnDS(string sql)
        {

            DataSet ds = new DataSet();
            try
            {
                if (cType == dbType.sql)
                {
                    SqlCommand cmd = new SqlCommand(sql, con);
                    cmd.CommandTimeout = 20;//允许由于网络拥塞或服务器负载过重产生的延迟而取消 Execute 方法调用。如果在 CommandTimeout 属性中设置的时间间隔内没有完成命令执行，将产生错误，然后 ADO 将取消该命令。如果将该属性设置为零，ADO 将无限期等待直到命令执行完毕。请确保正在写入代码的提供者和数据源支持 CommandTimeout 功能。

                    this.Open();

                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds, "tempTable");

                }
                else if (cType == dbType.access)
                {
                    OleDbCommand cmd = new OleDbCommand(sql, conn);
                    cmd.CommandTimeout = 20;
                    this.Open();
                    System.Data.OleDb.OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                    adapter.Fill(ds, "tempTable");
                }
            }
            catch (Exception e)
            {
                throw (e);
                ds = null;
            }
            finally
            {
                this.Close();
            }

            return ds;

        }  //

        /// <summary>
        /// 对数据库的增，删，改的操作
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>是否成功</returns>
        /// 
        public bool OperateDB(string sql)
        {
            bool succeed = false;
            int cnt = 0;
            try
            {
                if (cType == dbType.sql)
                {
                    SqlCommand cmd = new SqlCommand(sql, con);
                    cmd.CommandTimeout = 20;
                    this.Open();
                    cnt = cmd.ExecuteNonQuery();//ExecuteNonQuery对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于所有其他类型的语句，返回值为 -1。如果发生回滚，返回值也为 -1
                    

                }
                else if (cType == dbType.access)
                {
                    OleDbCommand cmd = new OleDbCommand(sql, conn);
                    cmd.CommandTimeout = 20;
                    this.Open();
                    cnt = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                throw (e);
            }
            finally
            {
                if (cnt > 0)
                {
                    succeed = true;
                }
                this.Close();
            }

            return succeed;
        }

        /// <summary>
        /// 获得该SQL查询返回的第一行第一列的值，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>返回的第一行第一列的值</returns>
        public string getfirstValue(string sql)
        {
            string str = null;
            try
            {
                if (cType == dbType.sql)
                {
                    SqlCommand cmd = new SqlCommand(sql, con);
                    this.Open();
                    str = cmd.ExecuteScalar().ToString();

                }
                else if (cType == dbType.access)
                {
                    OleDbCommand cmd = new OleDbCommand(sql, conn);
                    this.Open();
                    str = cmd.ExecuteScalar().ToString();
                }
            }
            catch (Exception e)
            {
                throw (e);
            }
            finally
            {
                this.Close();
            }

            return str;
        }

        /// <summary>
        ///  获得该SQL查询返回DataTable，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public DataTable getTable(string sql)
        {
            DataTable tb = null;
            DataSet ds = this.returnDS(sql);
            if (ds != null)
            {
                tb = ds.Tables["tempTable"];
            }

            return tb;
        }

        /// <summary>
        /// 打开数据库连接.
        /// </summary>
        private void Open()
        {
            if (cType == dbType.sql)
            {
                if (con.State == System.Data.ConnectionState.Closed)
                {
                    con.Open();
                }
                else if (con.State == System.Data.ConnectionState.Broken)
                {
                    con.Close();
                    con.Open();
                }
            }
            else if (cType == dbType.access)
            {
                if (conn.State == System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                }
                else if (conn.State == System.Data.ConnectionState.Broken)
                {
                    conn.Close();
                    conn.Open();
                }
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (cType == dbType.sql)
            {
                if (con != null)
                {
                    con.Close();
                }
            }
            else if (cType == dbType.access)
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (cType == dbType.sql)
            {
                // 确认连接是否已经关闭
                if (con != null)
                {
                    con.Dispose();
                    con = null;
                }
            }
            else if (cType == dbType.access)
            {
                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }
            }

        }






    }

    

 
}