C#数据库操作类

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

namespace Jerry.DBOP
{
    /// <summary>
    /// CDataAccess 的摘要说明。
    /// <description>数据处理基类,调用方式：
    /// CDataAccess.DataSet((string)sqlstr);
    /// 或者
    /// CDataAccess.DataSet((string)sqlstr,ref DataSet ds); </description>
    /// </summary>
    public class CDataAccess
    {
        private static string ConnectionString;
        public CDataAccess()
        {}
        public CDataAccess(string ConnectionString_f)
        {
            ConnectionString = ConnectionString_f;
        }     
        protected static SqlConnection conn = new SqlConnection();
        protected static SqlCommand comm = new SqlCommand();

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private static void openConnection()
        {
            if (conn.State == ConnectionState.Closed)
            {
                //SysConfig.ConnectionString 为系统配置类中连接字符串，如："server=localhost;database=databasename;uid=sa;pwd=;"

                //if (ConnectionString.Length = 0) mbox; 
                 //ConnectionString = "Data Source=HostPC,PcPort;Initial Catalog=Engineer;User ID=sa;PWD=USERPWD";
                ConnectionString ="server =127.0.0.1;database=Engineer;uid=sa;pwd=USERPWD";
                conn.ConnectionString = ConnectionString;// SysConfig.ConnectionString;
                comm.Connection = conn;
                try
                {
                    conn.Open();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
            }
        }
        /// <summary>
        /// 关闭当前数据库连接
        /// </summary>
        private static void closeConnection()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            conn.Dispose();
            comm.Dispose();
        }
         /// <summary>
        /// 执行Sql查询语句
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        public static void ExecuteSql(string sqlstr)
        {
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message  );
            }
            finally
            {
                closeConnection();
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="coll">SqlParameters 集合</param>
        public static void ExecutePorcedure(string procName, SqlParameter[] coll)
        {
            try
            {
                openConnection();
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = procName;
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                comm.Parameters.Clear();
                closeConnection();
            }
        }

        /// <summary>
        /// 执行存储过程并返回数据集
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="coll">SqlParameter集合</param>
        /// <param name="ds">DataSet </param>
        public static void ExecutePorcedure(string procName, SqlParameter[] coll, ref DataSet ds)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                openConnection();
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = procName;

                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                comm.Parameters.Clear();
                closeConnection();
            }
        }

        /// <summary>
        /// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>object 返回值 </returns>
        public static object ExecuteScalar(string sqlstr)
        {
            object obj = new object();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                obj = comm.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return obj;
        }

        /// <summary>
        /// 执行Sql查询语句,同时进行事务处理.傳入的語句請使用;(分號)分開...注意只有一條語句后面也需要帶分號.
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        public static int ExecuteSqlWithTransaction(string sqlstr)
        {
            openConnection();
            SqlTransaction trans;
            trans = conn.BeginTransaction();
            comm.Transaction = trans;
            try
            {
              
                comm.CommandType = CommandType.Text;
                //string[] st = sqlstr.Split(';');
               // for (int i = 0; i < st.Length; i++)
               // {
                //    comm.CommandText = st[i];
                comm.CommandText = sqlstr;
                    comm.ExecuteNonQuery();
               // }

                trans.Commit();
                return 0;
            }
            catch
            {
                trans.Rollback();
                return 1;

            }
            finally
            {
                closeConnection();
        
            }
        }
      
        /// <summary>
        /// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接
        /// 方法关闭数据库连接
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>SqlDataReader对象</returns>
        public static SqlDataReader dataReader(string sqlstr)
        {
            SqlDataReader dr = null;
            try
            {
                openConnection();
                comm.CommandText = sqlstr;
                comm.CommandType = CommandType.Text;
                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                try
                {
                    dr.Close();
                    closeConnection();
                }
                catch
                {
                }
            }
            return dr;
        }
        /// <summary>
        /// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接
        /// 方法关闭数据库连接
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <param name="dr">传入的ref DataReader 对象</param>
        public static void dataReader(string sqlstr, ref SqlDataReader dr)
        {
            try
            {
                openConnection();
                comm.CommandText = sqlstr;
                comm.CommandType = CommandType.Text;
                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                try
                {
                    if (dr != null && !dr.IsClosed)
                        dr.Close();
                }
                catch
                {
                }
                finally
                {
                    closeConnection();
                }
            }
        }

        /// <summary>
        /// 返回指定Sql语句的DataSet
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>DataSet</returns>
        public static DataSet dataSet(string sqlstr)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message + " // " + sqlstr);
            }
            finally
            {
                closeConnection();
            }
            return ds;
        }

        /// <summary>
        /// 返回指定Sql语句的DataSet
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <param name="ds">传入的引用DataSet对象</param>
        public static void dataSet(string sqlstr, ref DataSet ds)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
        }
        /// <summary>
        /// 返回指定Sql语句的DataTable
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>DataTable</returns>
        public static DataTable dataTable(string sqlstr)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable datatable = new DataTable();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(datatable);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return datatable;
        }

        /// <summary>
        /// 执行指定Sql语句,同时给传入DataTable进行赋值
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <param name="dt">ref DataTable dt </param>
        public static void dataTable(string sqlstr, ref DataTable dt)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
        }
        /// <summary>
        /// 执行带参数存储过程并返回数据集合
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">SqlParameterCollection 输入参数</param>
        /// <returns></returns>
        public static DataTable dataTable(string procName, SqlParameterCollection parameters)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable datatable = new DataTable();
            try
            {
                openConnection();
                comm.Parameters.Clear();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = procName;
                foreach (SqlParameter para in parameters)
                {
                    SqlParameter p = (SqlParameter)para;
                    comm.Parameters.Add(p);
                }
                da.SelectCommand = comm;
                da.Fill(datatable);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return datatable;
        }

        public static DataView dataView(string sqlstr)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataView dv = new DataView();
            DataSet ds = new DataSet();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(ds);
                dv = ds.Tables[0].DefaultView;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return dv;
        }
    }
}

转载于:https://www.cnblogs.com/Jerry7750/archive/2008/07/16/1244685.html