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

于 2014-03-31 16:33:54 发布
阅读量1.3k 收藏 3
点赞数 2
CC 4.0 BY-SA版权
分类专栏： C# 数据库 文章标签： c# sql server ado.net 数据库类 sqlClient
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/bipedal_bit/article/details/22676029

    这是这学期做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();
        }
    }
}

这个类虽然也有不满意的地方，但是写得挺认真的，自己觉得格式还是挺工整。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值