C#语言SqlClient接口SQL Server数据库类

来源：互联网发布：ios软件开发是什么编辑：程序博客网时间：2024/05/21 11:24
这是这学期做ASP.NET课作业时手痒写的SQL数据库类，微软支持4种数据库接口（SqlClient、OLE DB、ODBC、OracleClient）操作，因为平时学习用SQL Server 数据库比较多，所以暂时只写了SqlClient接口的SQL Server数据库操作类，以后要用别的数据库可能会类比着再补充进来吧。
using System;using System.Data;using System.Data.SqlClient;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 SQLDB()        {}        /// <summary>        /// 用给定数据库连接字符串创建SQL数据库实例        /// </summary>        /// <param name="connectionString"></param>        public SQLDB(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 SQLDB(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 SQLDB(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 SQLDB(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 SQLDB(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 SQLDB(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();                return false;            }        }        /// <summary>        /// 执行sql语句，成功返回true，失败返回false        /// </summary>        /// <param name="cmd"></param>        /// <returns></returns>        public bool runSqlCmd(string cmd)        {            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();                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();                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();                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();                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 * from " + tableName + " where " + columnName + "='" + value + "'", sqlConn);            openConnection();            reader = sqlCmd.ExecuteReader(CommandBehavior.SingleResult);            int count = 0;            if (reader.HasRows)            {                while (reader.Read())                {                    count++;                }            }            reader.Close();            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();        }    }}
这个类虽然也有不满意的地方，但是写得挺认真的，自己觉得格式还是挺工整。
0 0