c# 数据库操作类sql server篇

数据库连接类库大全  可以胜任sqlserver数据库操作  简化编程难度 在页面中可以直接调用 也可以供其他类库调用  以下是类库 直接复制到cs文件中既可 需要添加System.Configuration引用


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;

namespace ZfServer
{
    public class DBAction
    {
        private SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["DB"].ToString()); //获取config AppSettings节点设置的数据库连接用户名和密码

        /// <summary>
        /// 根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet returnDS(SqlCommand cmd, string TempTableName)
        {

            DataSet ds = new DataSet();
            try
            {
                cmd.Connection = con;
                cmd.CommandTimeout = 30;
                this.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, TempTableName);
            }
            catch (Exception e)
            {
                throw (e);
            }
            finally
            {
                this.Close();
            }

            return ds;

        }

        /// <summary>
        /// 根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sRecord">开始记录数</param>
        /// <param name="mRecord">最大记录数</param>
        /// <returns>DataSet</returns>
        public DataSet returnDS(SqlCommand cmd, string TempTableName, int sRecord, int mRecord)
        {

            DataSet ds = new DataSet();
            try
            {
                cmd.Connection = con;
                cmd.CommandTimeout = 30;
                this.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, sRecord, mRecord, TempTableName);
            }
            catch (Exception e)
            {
                ds = null;
                throw (e);
            }
            finally
            {
                this.Close();
            }
            return ds;
        }
        /// <summary>
        /// 返回DataTable对象
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public DataTable returnDb(SqlCommand cmd)
        {

            DataTable ds = new DataTable();
            try
            {
                cmd.Connection = con;
                cmd.CommandTimeout = 30;
                this.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            catch (Exception e)
            {
                throw (e);
            }
            finally
            {
                this.Close();
            }

            return ds;

        }

        /// <summary>
        /// 根据SqlDataComand对象返回查找的记录集,如果没有则返回NULL
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <returns>SqlDataReader对象</returns>
        public SqlDataReader returnReader(SqlCommand cmd)
        {
            SqlDataReader reader;
            try
            {
                cmd.Connection = con;
                cmd.CommandTimeout = 30;
                this.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                reader = null;
                throw (e);
            }
            return reader;
        }

        /// <summary>
        /// 根据SqlDataComand对象返回查找的值,如果没有则返回NULL
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <returns>Object对象</returns>
        public object returnScalar(SqlCommand cmd)
        {
            object obj;
            try
            {
                cmd.Connection = con;
                cmd.CommandTimeout = 30;
                this.Open();
                obj = cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                obj = null;
                throw (e);
            }
            return obj;
        }

        /// <summary>
        /// 对数据库的增，删，改的操作
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>是否成功</returns>
        public bool OperateDB(SqlCommand cmd)
        {
            bool succeed = false;
            int cnt = 0;
            try
            {
                cmd.Connection = con;
                cmd.CommandTimeout =30;
                this.Open();
                cnt = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw (e);
            }
            finally
            {
                if (cnt > 0)
                {
                    succeed = true;
                }
                this.Close();
            }

            return succeed;
        }

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

            return tb;
        }

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

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

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

    }
}