C#语言 SqlClient接口SQL Sever数据库类+OleDb接口Access数据库类

有空再写Oracle、IBM的吧。其实除了连接字符串格式，也只是换换数据库接口名。

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

namespace IDataBase
{
    public class SQLServer
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            get;
            set;
        }

        /// <summary>
        /// 上一次数据库异常
        /// </summary>
        public string sqlException
        {
            get;
            set;
        }
        
        SqlConnection sqlConn;
        SqlCommand sqlCmd;
        SqlDataReader reader;
        DataSet ds;
        SqlCommandBuilder cmdBuilder;
        SqlDataAdapter adapter;

        /// <summary>
        /// 创建一个SQL数据库实例，在设置ConnectionString属性之前无法连接到数据库
        /// </summary>
        public SQLServer()
        {}

        /// <summary>
        /// 用给定数据库连接字符串创建SQL数据库实例
        /// </summary>
        /// <param name="connectionString"></param>
        public SQLServer(string connectionString)
        {
            ConnectionString = connectionString;
            sqlConn = new SqlConnection(ConnectionString);
        }

        /// <summary>
        /// 用SQL server身份验证方式创建SQL数据库实例，若不使用SQLserver2000应在server后加访问端口“,2317”或“\SQLExpress”
        /// </summary>
        /// <param name="AttachDBFilename"></param>
        /// <param name="server"></param>
        /// <param name="DataBase"></param>
        /// <param name="uid"></param>
        /// <param name="pwd"></param>
        public SQLServer(string server ,string DataBase, string uid, string pwd, string AttachDBFilename = null)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            if (AttachDBFilename != null)
            {
                builder.AttachDBFilename = AttachDBFilename;
            }
            builder.DataSource = server;
            builder.InitialCatalog = DataBase;
            builder.UserID = uid;
            builder.Password = pwd;
            ConnectionString = builder.ConnectionString;
            sqlConn = new SqlConnection(ConnectionString);
        }

        /// <summary>
        /// 用SQL server身份验证方式连接默认数据库创建SQL数据库实例，若不使用SQLserver2000应在server后加访问端口“,2317”或“\SQLExpress”
        /// </summary>
        /// <param name="AttachDBFilename"></param>
        /// <param name="server"></param>
        /// <param name="uid"></param>
        /// <param name="pwd"></param>
        public SQLServer(string server, string uid, string pwd, string AttachDBFilename)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.AttachDBFilename = AttachDBFilename;
            builder.DataSource = server;
            builder.UserInstance = true;
            builder.UserID = uid;
            builder.Password = pwd;
            ConnectionString = builder.ConnectionString;
            sqlConn = new SqlConnection(ConnectionString);
        }

        /// <summary>
        /// 用windows身份验证方式创建SQL数据库实例，若不使用SQLserver2000应在server后加访问端口“,2317”或“\SQLExpress”
        /// </summary>
        /// <param name="server"></param>
        /// <param name="DataBase"></param>
        /// <param name="AttachDBFilename"></param>
        public SQLServer(string server, string DataBase ,string AttachDBFilename = null)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            if( AttachDBFilename != null )
            {
                builder.AttachDBFilename = AttachDBFilename;
            }
            builder.DataSource = server;
            builder.InitialCatalog = DataBase;
            builder.IntegratedSecurity = true;
            ConnectionString = builder.ConnectionString;
            sqlConn = new SqlConnection(ConnectionString);
        }

        /// <summary>
        /// 用windows身份验证方式连接默认数据库创建SQL数据库实例，若不使用SQLserver2000应在server后加访问端口“,2317”或“\SQLExpress”
        /// </summary>
        /// <param name="server"></param>
        /// <param name="AttachDBFilename"></param>
        public SQLServer(string server, string AttachDBFilename)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.AttachDBFilename = AttachDBFilename;
            builder.DataSource = server;
            builder.UserInstance = true;
            builder.IntegratedSecurity = true;
            ConnectionString = builder.ConnectionString;
            sqlConn = new SqlConnection(ConnectionString);
        }

        /// <summary>
        /// 使用SQL连接字符串编辑器完成连接字符串的微调，并用创建的连接字符串创建数据库实例
        /// </summary>
        /// <param name="sqlConnStrBuilder"></param>
        public SQLServer(SqlConnectionStringBuilder sqlConnStrBuilder)
        {
            ConnectionString = sqlConnStrBuilder.ConnectionString;
            sqlConn = new SqlConnection(ConnectionString);
        }

        /// <summary>
        /// 打开SQL数据库连接
        /// </summary>
        /// <returns></returns>
        private bool openConnection()
        {
            try
            {
                sqlConn.Open();
                return true;
            }
            catch (Exception e)
            {
                sqlException = e.ToString();
                return false;
            }
        }

        /// <summary>
        /// 关闭SQL数据库连接
        /// </summary>
        private void closeConnection()
        {
            sqlConn.Close();
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="cmd"></param>
        private bool excuteCmd()
        {
            try
            {
                openConnection();
                sqlCmd.ExecuteNonQuery();
                closeConnection();
                return true;
            }
            catch (Exception e)
            {
                sqlException = e.ToString();
		closeConnection();
                return false;
            }
        }

        /// <summary>
        /// 执行sql语句，成功返回true，失败返回false
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public bool runSqlCmd(string cmd,sqlConn)
        {
            sqlCmd = new SqlCommand(cmd);
            return excuteCmd();
        }

        /// <summary>
        /// 由用户自行生成一条sql命令并执行
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public bool runSqlCmd(SqlCommand cmd)
        {
            sqlCmd = cmd;
            return excuteCmd();
        }

        /// <summary>
        /// 按sql查询语句获得目标数据表的数据，返回DataTable。
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public DataTable getDataTableBySQL(string sqlQuery)
        {
            ds = new DataSet();
            adapter = new SqlDataAdapter(sqlQuery, sqlConn);
            cmdBuilder = new SqlCommandBuilder(adapter);
            openConnection();
            adapter.Fill(ds);
            closeConnection();
            return ds.Tables[0];
        }

        /// <summary>
        /// 获得目标数据表的数据，返回DataTable。
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable getDataTableByName(string tableName)
        {
            ds = new DataSet();
            adapter = new SqlDataAdapter("select * from "+tableName,sqlConn);
            cmdBuilder = new SqlCommandBuilder(adapter);
            openConnection();
            adapter.Fill(ds,tableName);
            closeConnection();
            return ds.Tables[tableName];
        }

        /// <summary>
        /// 将数据库中指定的一或多个数据表填入DataSet并返回。
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public DataSet getDataSet(string[] tableNameArray)
        {
            ds = new DataSet();
            openConnection();
            foreach (string i in tableNameArray)
            {
                adapter = new SqlDataAdapter("select * from "+i, sqlConn);
                cmdBuilder = new SqlCommandBuilder(adapter);
                adapter.Fill(ds,i);
            }
            closeConnection();
            return ds;
        }

        /// <summary>
        /// 执行带1个参数的sql插入语句。sql语句中的参数名，与对应parameterName的值都请用“@para”的格式。成功返回true，失败返回false。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public bool insert(string sql, string parameterName, SqlDbType dbType , int size, object value)
        {
            try
            {
                openConnection();
                sqlCmd = new SqlCommand();
                sqlCmd.Parameters.Add(parameterName, dbType, size);
                sqlCmd.Parameters[parameterName].Value = value;
                sqlCmd.ExecuteNonQuery();
                closeConnection();
                return true;
            }
            catch (Exception e)
            {
                sqlException = e.ToString();
		closeConnection();
                return false;
            }
        }

        /// <summary>
        /// 生成一个指定数据表的空数据列
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataRow creatDataRow(string tableName)
        {
            ds = new DataSet();
            adapter = new SqlDataAdapter("select * from "+tableName, sqlConn);
            cmdBuilder = new SqlCommandBuilder(adapter);
            openConnection();
            adapter.Fill(ds, tableName);
            closeConnection();
            return ds.Tables[tableName].NewRow();
        }

        /// <summary>
        /// 向指定数据表插入数据列，空数据列可以用当前类的creatDataRow方法获得。成功返回true，失败返回false。
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        public bool insert(string tableName, DataRow row)
        {
            try
            {
                ds = new DataSet();
                adapter = new SqlDataAdapter("select * from "+tableName, sqlConn);
                cmdBuilder = new SqlCommandBuilder(adapter);
                openConnection();
                adapter.Fill(ds, tableName);
                ds.Tables[tableName].Rows.Add(row.ItemArray);
                adapter.Update(ds, tableName);
                closeConnection();
                return true;
            }
            catch (Exception e)
            {
                sqlException = e.ToString();
		closeConnection();
                return false;
            }
        }

        /// <summary>
        /// 执行带1个参数的sql更新语句。sql语句中的参数名，与对应parameterName的值都请用“@para”的格式。成功返回true，失败或没找到指定行返回false。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public bool update(string sql, string parameterName, SqlDbType dbType, int size, object value)
        {
            try
            {
                openConnection();
                sqlCmd = new SqlCommand();
                sqlCmd.Parameters.Add(parameterName, dbType, size);
                sqlCmd.Parameters[parameterName].Value = value;
                if (sqlCmd.ExecuteNonQuery() == 0)
                {
                    closeConnection();
                    return false;
                }
                closeConnection();
                return true;
            }
            catch (Exception e)
            {
                sqlException = e.ToString();
		closeConnection();
                return false;
            }
        }

        /// <summary>
        /// 用当前类的getDataSet方法获取指定数据表，修改后用本方法批量地更新指定数据表。成功返回true，失败返回false。
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dataSet"></param>
        /// <returns></returns>
        public bool update(string tableName, DataSet dataSet)
        {
            try
            {
                adapter = new SqlDataAdapter("select * from "+tableName, sqlConn);
                cmdBuilder = new SqlCommandBuilder(adapter);
                openConnection();
                adapter.Update(dataSet);
                closeConnection();
                return true;
            }
            catch (Exception e)
            {
                sqlException = e.ToString();
		closeConnection();
                return false;
            }
        }

        /// <summary>
        /// 返回指定数据表的指定列中是否存在指定值
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public bool existInTable(string tableName, string columnName, string value)
        {
            sqlCmd = new SqlCommand("select * from "+tableName+" where "+columnName+"='"+value+"'",sqlConn);
            openConnection();
            reader = sqlCmd.ExecuteReader(CommandBehavior.SingleResult);
            bool exist = reader.HasRows;
            reader.Close();
            closeConnection();
            return exist;
        }

        /// <summary>
        /// 返回指定数据表的指定列中有多少个指定值
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public int countInTable(string tableName, string columnName, string value)
        {
            sqlCmd = new SqlCommand("select count(*) from " + tableName + " where " + columnName + "='" + value + "'", sqlConn);
            openConnection();
            int count = (int)sqlCmd.ExecuteScalar();
            closeConnection();
            return count;
        }

        /// <summary>
        /// DataReader使用完毕必须用当前类的close方法将DataReader关闭，并关闭数据库连接
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public SqlDataReader select(string sql)
        {
            sqlCmd = new SqlCommand(sql, sqlConn);
            openConnection();
            reader = sqlCmd.ExecuteReader();
            return reader;
        }

        /// <summary>
        /// 如果本类提供的DataReader未关闭，将其关闭，同时关闭未关闭的数据库连接
        /// </summary>
        public void close()
        {
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            sqlConn.Close();
        }
    }

    public class Access
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            get;
            set;
        }

        /// <summary>
        /// 上一次数据库异常
        /// </summary>
        public string oleException
        {
            get;
            set;
        }

        OleDbConnection conn;
        OleDbCommand cmd;
        OleDbDataReader reader;
        DataSet ds;
        OleDbCommandBuilder cmdBuilder;
        OleDbDataAdapter adapter;

        /// <summary>
        /// 创建一个OleDb数据库实例，在设置ConnectionString属性之前无法连接到数据库
        /// </summary>
        public Access()
        { }

        /// <summary>
        /// 用给定数据库连接字符串创建OleDb数据库实例
        /// </summary>
        /// <param name="connectionString"></param>
        public Access(string connectionString)
        {
            ConnectionString = connectionString;
            conn = new OleDbConnection(ConnectionString);
        }

        /// <summary>
        /// 用数据源提供程序和数据库文件名进行简易连接，数据库文件在项目默认数据库路径下
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="dataBase"></param>
        public Access(string provider, string dataBaseName)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
            builder.Provider = provider;
            builder.DataSource = "|DataDirectory|"+dataBaseName;
            ConnectionString = builder.ConnectionString;
            conn = new OleDbConnection(ConnectionString);
        }

        /// <summary>
        /// 使用OleDb连接字符串编辑器完成连接字符串的微调，并用创建的连接字符串创建数据库实例
        /// </summary>
        /// <param name="sqlConnStrBuilder"></param>
        public Access(OleDbConnectionStringBuilder oleConnStrBuilder)
        {
            ConnectionString = oleConnStrBuilder.ConnectionString;
            conn = new OleDbConnection(ConnectionString);
        }

        /// <summary>
        /// 打开OleDb数据库连接
        /// </summary>
        /// <returns></returns>
        private bool openConnection()
        {
            try
            {
                conn.Open();
                return true;
            }
            catch (Exception e)
            {
                oleException = e.ToString();
                return false;
            }
        }

        /// <summary>
        /// 关闭OleDb数据库连接
        /// </summary>
        private void closeConnection()
        {
            conn.Close();
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="cmd"></param>
        private bool excuteCmd()
        {
            try
            {
                openConnection();
                cmd.ExecuteNonQuery();
                closeConnection();
                return true;
            }
            catch (Exception e)
            {
                oleException = e.ToString();
                closeConnection();
                return false;
            }
        }

        /// <summary>
        /// 执行sql语句，成功返回true，失败返回false
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public bool runSqlCmd(string _cmd)
        {
            cmd = new OleDbCommand(_cmd,conn);
            return excuteCmd();
        }

        /// <summary>
        /// 由用户自行生成一条sql命令并执行
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public bool runSqlCmd(OleDbCommand _cmd)
        {
            cmd = _cmd;
            return excuteCmd();
        }

        /// <summary>
        /// 按sql查询语句获得目标数据表的数据，返回DataTable。
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public DataTable getDataTableBySQL(string sqlQuery)
        {
            ds = new DataSet();
            adapter = new OleDbDataAdapter(sqlQuery, conn);
            cmdBuilder = new OleDbCommandBuilder(adapter);
            openConnection();
            adapter.Fill(ds);
            closeConnection();
            return ds.Tables[0];
        }

        /// <summary>
        /// 获得目标数据表的数据，返回DataTable。
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable getDataTableByName(string tableName)
        {
            ds = new DataSet();
            adapter = new OleDbDataAdapter("select * from " + tableName, conn);
            cmdBuilder = new OleDbCommandBuilder(adapter);
            openConnection();
            adapter.Fill(ds, tableName);
            closeConnection();
            return ds.Tables[tableName];
        }

        /// <summary>
        /// 将数据库中指定的一或多个数据表填入DataSet并返回。
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public DataSet getDataSet(string[] tableNameArray)
        {
            ds = new DataSet();
            openConnection();
            foreach (string i in tableNameArray)
            {
                adapter = new OleDbDataAdapter("select * from " + i, conn);
                cmdBuilder = new OleDbCommandBuilder(adapter);
                adapter.Fill(ds, i);
            }
            closeConnection();
            return ds;
        }

        /// <summary>
        /// 执行带1个参数的sql插入语句。sql语句中的参数名，与对应parameterName的值都请用“@para”的格式。成功返回true，失败返回false。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public bool insert(string sql, string parameterName, OleDbType dbType, int size, object value)
        {
            try
            {
                openConnection();
                cmd = new OleDbCommand();
                cmd.Parameters.Add(parameterName, dbType, size);
                cmd.Parameters[parameterName].Value = value;
                cmd.ExecuteNonQuery();
                closeConnection();
                return true;
            }
            catch (Exception e)
            {
                oleException = e.ToString();
                closeConnection();
                return false;
            }
        }

        /// <summary>
        /// 生成一个指定数据表的空数据列
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataRow creatDataRow(string tableName)
        {
            ds = new DataSet();
            adapter = new OleDbDataAdapter("select * from " + tableName, conn);
            cmdBuilder = new OleDbCommandBuilder(adapter);
            openConnection();
            adapter.Fill(ds, tableName);
            closeConnection();
            return ds.Tables[tableName].NewRow();
        }

        /// <summary>
        /// 向指定数据表插入数据列，空数据列可以用当前类的creatDataRow方法获得。成功返回true，失败返回false。
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        public bool insert(string tableName, DataRow row)
        {
            try
            {
                ds = new DataSet();
                adapter = new OleDbDataAdapter("select * from " + tableName, conn);
                cmdBuilder = new OleDbCommandBuilder(adapter);
                openConnection();
                adapter.Fill(ds, tableName);
                ds.Tables[tableName].Rows.Add(row.ItemArray);
                adapter.Update(ds, tableName);
                closeConnection();
                return true;
            }
            catch (Exception e)
            {
                oleException = e.ToString();
                closeConnection();
                return false;
            }
        }

        /// <summary>
        /// 执行带1个参数的sql更新语句。sql语句中的参数名，与对应parameterName的值都请用“@para”的格式。成功返回true，失败或没找到指定行返回false。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public bool update(string sql, string parameterName, OleDbType dbType, int size, object value)
        {
            try
            {
                openConnection();
                cmd = new OleDbCommand();
                cmd.Parameters.Add(parameterName, dbType, size);
                cmd.Parameters[parameterName].Value = value;
                if (cmd.ExecuteNonQuery() == 0)
                {
                    closeConnection();
                    return false;
                }
                closeConnection();
                return true;
            }
            catch (Exception e)
            {
                oleException = e.ToString();
                closeConnection();
                return false;
            }
        }

        /// <summary>
        /// 用当前类的getDataSet方法获取指定数据表，修改后用本方法批量地更新指定数据表。成功返回true，失败返回false。
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dataSet"></param>
        /// <returns></returns>
        public bool update(string tableName, DataSet dataSet)
        {
            try
            {
                adapter = new OleDbDataAdapter("select * from " + tableName, conn);
                cmdBuilder = new OleDbCommandBuilder(adapter);
                openConnection();
                adapter.Update(dataSet);
                closeConnection();
                return true;
            }
            catch (Exception e)
            {
                oleException = e.ToString();
                closeConnection();
                return false;
            }
        }

        /// <summary>
        /// 返回指定数据表的指定列中是否存在指定值
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public bool existInTable(string tableName, string columnName, string value)
        {
            cmd = new OleDbCommand("select * from " + tableName + " where " + columnName + "='" + value + "'", conn);
            openConnection();
            reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
            bool exist = reader.HasRows;
            reader.Close();
            closeConnection();
            return exist;
        }

        /// <summary>
        /// 返回指定数据表的指定列中有多少个指定值
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public int countInTable(string tableName, string columnName, string value)
        {
            cmd = new OleDbCommand("select count(*) from " + tableName + " where " + columnName + "='" + value + "'", conn);
            openConnection();
            int count = (int)cmd.ExecuteScalar();
            closeConnection();
            return count;
        }

        /// <summary>
        /// DataReader使用完毕必须用当前类的close方法将DataReader关闭，并关闭数据库连接
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public OleDbDataReader select(string sql)
        {
            cmd = new OleDbCommand(sql, conn);
            openConnection();
            reader = cmd.ExecuteReader();
            return reader;
        }

        /// <summary>
        /// 如果本类提供的DataReader未关闭，将其关闭，同时关闭未关闭的数据库连接
        /// </summary>
        public void close()
        {
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            conn.Close();
        }
    }
}