C# 操作SQLServer数据库

一、引入数据库连接System.Data.SqlClient和读取配置文件Configuration依赖

 

二、在配置文件App.config中加入SQLServer数据库连接字符

<!--SQLServer数据库连接配置-->
    <connectionStrings>
        <add name="SqlServerConnectionString"
             connectionString="Data Source=DESKTOP-C1F0DQP\SQLEXPRESS;Initial Catalog=HTH_PC_DATA;Persist Security Info=True;User ID=sa;Password=123"
             providerName="System.Data.SqlClient" />
    </connectionStrings>

 

三、编写数据库操作基础工具类

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

namespace HTH_BatteryMonitorSystem
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public static class SQLUtil
    {

        /// <summary>
        /// 连接字符串
        /// </summary>
        private static string strconn;

        static SQLUtil()
        {
            //读取配置文件
            strconn = ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ToString();
        }

        /// <summary>
        /// 执行增删改SQL语句，只返回受影响的行数
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string cmdText)
        {
            using (SqlConnection conn = new SqlConnection(strconn))
            {
                conn.Open();
                return ExecuteNonQuery(conn, cmdText);
            }
        }

        /// <summary>
        /// 执行增删改SQL语句
        /// </summary>
        /// <param name="conn">SqlConnection</param>
        /// <param name="cmdText">SQL语句<</param>
        /// <returns></returns>
        private static int ExecuteNonQuery(SqlConnection conn, string cmdText)
        {
            int res;
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                cmd.CommandType = CommandType.Text;
                res = cmd.ExecuteNonQuery();
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return res;
        }

        /// <summary>
        /// 执行查询SQL语句
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string cmdText)
        {
            using (SqlConnection conn = new SqlConnection(strconn))
            {
                conn.Open();
                return ExecuteDataTable(conn, cmdText);
            }
        }

        /// <summary>
        /// 执行查询SQL语句
        /// </summary>
        /// <param name="conn">SqlConnection</param>
        /// <param name="cmdText">SQL语句</param>
        /// <returns></returns>
        private static DataTable ExecuteDataTable(SqlConnection conn, string cmdText)
        {
            DataTable dt = new DataTable();    
            using (SqlDataAdapter sda = new SqlDataAdapter(cmdText,conn))
            {
                sda.Fill(dt);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return dt;
        }

        /// <summary>
        /// 执行查询SQL语句
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <returns></returns>
        public static object[] ExecuteQuery(string cmdText)
        {
            using (SqlConnection conn = new SqlConnection(strconn))
            {
                conn.Open();
                return ExecuteQuery(conn, cmdText);
            }
        }

        /// <summary>
        /// 执行查询SQL语句
        /// </summary>
        /// <param name="conn">SqlConnection</param>
        /// <param name="cmdText">SQL语句</param>
        /// <returns></returns>
        private static object[] ExecuteQuery(SqlConnection conn, string cmdText)
        {
          
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    //dt.Load(sdr);
                    //sdr.Close();
                    //sdr.Dispose();
                    //if (conn.State == ConnectionState.Open)
                    //{
                    //    conn.Close();
                    //    conn.Dispose();
                    //}
                    if (sdr.Read())
                    {
                        // 返回整行数据
                        object[] rowData = new object[sdr.FieldCount];
                        sdr.GetSqlValues(rowData);
                        return rowData;
                    }
                }
            }
            return null;
        }

    }


}

 

四、带事务的数据库操作工具类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;//引入相关数据操作类
using System.Windows.Forms;//引入Application类
using System.Data;//引入相关数据操作类
using ERP.ComClass;//引入OperatorFile类

namespace ERP.DataClass
{
    /// <summary>
    /// 主要用于数据的基础操作
    /// </summary>
    public class DataBase
    {
        private SqlConnection m_Conn = null;
        private SqlCommand m_Cmd = null;

        /// <summary>
        /// 创建数据库连接和SqlCommand实例
        /// </summary>
        public DataBase()
        {
            //获取服务器名
            string strServer = OperatorFile.GetIniFileString("DataBase", "Server", "", Application.StartupPath + "\\ERP.ini");
            //获取登录用户
            string strUserID = OperatorFile.GetIniFileString("DataBase", "UserID", "", Application.StartupPath + "\\ERP.ini");
            //获取登录密码
            string strPwd = OperatorFile.GetIniFileString("DataBase", "Pwd", "", Application.StartupPath + "\\ERP.ini");
            //数据库连接字符串
            string strConn = "Server = " + strServer + ";Database=db_ERP;User id=" + strUserID + ";PWD=" + strPwd;
            
            try
            {
                m_Conn = new SqlConnection(strConn);//产生数据库连接
                m_Cmd = new SqlCommand();//实例化SqlCommand
                m_Cmd.Connection = m_Conn;//设置SqlCommand的Connection属性
            }
            catch(Exception e)//捕获异常
            {
                throw e;//抛出异常
            }
        }

        public SqlConnection Conn
        {
            get { return m_Conn; }
        }

        public SqlCommand Cmd
        {
            get { return m_Cmd; }
        }

        /// <summary>
        /// 通过Transact-SQL语句提交数据
        /// </summary>
        /// <param name="strSql">Transact-SQL语句</param>
        /// <returns>受影响的行数</returns>
        public int ExecDataBySql(string strSql)
        {
            int intReturnValue;

            m_Cmd.CommandType = CommandType.Text;
            m_Cmd.CommandText = strSql;

            try
            {
                if (m_Conn.State == ConnectionState.Closed)
                {
                    m_Conn.Open();
                }

                intReturnValue = m_Cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                m_Conn.Close();//连接关闭，但不释放掉该对象所占的内存单元
            }

            return intReturnValue;
        }

        /// <summary>
        /// 多条Transact-SQL语句提交数据
        /// </summary>
        /// <param name="strSqls">使用List泛型封装多条SQL语句</param>
        /// <returns>bool值(提交是否成功)</returns>
        public bool ExecDataBySqls(List<string> strSqls)
        {
            bool booIsSucceed;//定义返回值变量

            if (m_Conn.State == ConnectionState.Closed)//判断当前的数据库连接状态
            {
                  m_Conn.Open();//打开连接
            }

            SqlTransaction sqlTran = m_Conn.BeginTransaction();//开始数据库事务

            try
            {
                m_Cmd.Transaction = sqlTran;//设置m_Cmd的事务属性
                //循环取出封装在列表strSqls中表示SQL语句的字符串
                foreach (string item in strSqls)
                {
                    m_Cmd.CommandType = CommandType.Text;//设置命令类型为SQL文本命令
                    m_Cmd.CommandText = item;//设置要对数据源执行的SQL语句
                    m_Cmd.ExecuteNonQuery();//执行SQL语句并返回受影响的行数
                }

                sqlTran.Commit();//提交事务，持久化数据
                booIsSucceed = true;  //表示提交数据库成功
            }
            catch
            {
                sqlTran.Rollback();//回滚事务，恢复数据
                booIsSucceed = false;  //表示提交数据库失败！
            }
            finally
            {
                m_Conn.Close();//关闭连接
                strSqls.Clear();//清除列表strSqls中的元素
            }

            return booIsSucceed;
        }

        /// <summary>
        /// 通过Transact-SQL语句得到DataSet实例
        /// </summary>
        /// <param name="strSql">Transact-SQL语句</param>
        /// <param name="strTable">相关的数据表</param>
        /// <returns>DataSet实例的引用</returns>
        public DataSet GetDataSet(string strSql,string strTable)
        {
            DataSet ds = null;

            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(strSql, m_Conn);
                ds = new DataSet();
                sda.Fill(ds, strTable);
            }
            catch (Exception e)
            {
                throw e;
            }
           
            return ds;
        }

        /// <summary>
        /// 通过Transact-SQL语句得到SqlDataReader实例
        /// </summary>
        /// <param name="strSql">Transact-SQL语句</param>
        /// <returns>SqlDataReader实例的引用</returns>
        public SqlDataReader GetDataReader(string strSql)
        {
            SqlDataReader sdr;//声明SqlDataReader引用

            m_Cmd.CommandType = CommandType.Text;//设置命令类型为SQL文本命令
            m_Cmd.CommandText = strSql;//设置要对数据源执行的SQL语句

            try
            {
                if (m_Conn.State == ConnectionState.Closed)//判断数据库连接的状态
                {
                    m_Conn.Open();//打开连接
                }
                //执行Transact-SQL语句（若SqlDataReader对象关闭，则对应数据连接也关闭）
                sdr = m_Cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)//捕获异常
            {
                throw e;//抛出异常
            }
            
            //sdr对象和m_Conn对象暂时不能关闭和释放掉，否则在调用时无法使用
            //待使用完毕sdr，再关闭sdr对象（同时会自动关闭关联的m_Conn对象）
            //m_Conn的关闭是指关闭连接通道，但连接对象依然存在
            //m_Conn的释放掉是指销毁连接对象
            return sdr;
        }

        /// <summary>
        /// 重新封装ExecuteScalar方法，得到结果集中的第一行的第一列
        /// </summary>
        /// <param name="strSql">Transact-SQL语句</param>
        /// <returns>结果集中的第一行的第一列</returns>
        public object GetSingleObject(string strSql)
        {
            object obj = null;
            m_Cmd.CommandType = CommandType.Text;
            m_Cmd.CommandText = strSql;

            try
            {
                if (m_Conn.State == ConnectionState.Closed)
                {
                    m_Conn.Open();
                }

                obj = m_Cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw e;//向上一层抛出异常（上一层使用try{}catch{}）或立刻中断(上一层未使用try{}catch{})
            }
            finally
            {
                m_Conn.Close();
            }

            return obj;
        }

        /// <summary>
        /// 通过Transact-SQL语句，得到DataTable实例
        /// </summary>
        /// <param name="strSqlCode">Transact-SQL语句</param>
        /// <param name="strTableName">数据表的名称</param>
        /// <returns>DataTable实例的引用</returns>
        public DataTable GetDataTable(string strSqlCode, string strTableName)
        {
            DataTable dt = null;//声明DataTable引用
            SqlDataAdapter sda = null;//声明SqlDataAdapter引用

            try
            {
                sda = new SqlDataAdapter(strSqlCode,m_Conn);//实例化SqlDataAdapter
                dt = new DataTable(strTableName);//使用指定字符串初始化DataTable实例
                sda.Fill(dt);//将得到的数据源填入dt中        
            }
            catch (Exception ex)//捕获异常
            {
                throw ex;//抛出异常
            }

            return dt; //dt.Rows.Count可能等于零
        }

        /// <summary>
        /// 通过存储过程，得到DataTable实例
        /// </summary>
        /// <param name="strProcedureName">存储过程名称</param>
        /// <param name="inputParameters">存储过程的参数数组</param>
        /// <returns>DataTable实例的引用</returns>
        public DataTable GetDataTable(string strProcedureName,SqlParameter[] inputParameters)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sda = null;

            try
            {
                m_Cmd.CommandType = CommandType.StoredProcedure;
                m_Cmd.CommandText = strProcedureName;
                sda = new SqlDataAdapter(m_Cmd);
                m_Cmd.Parameters.Clear();

                foreach (SqlParameter param in inputParameters)
                {
                    param.Direction = ParameterDirection.Input;
                    m_Cmd.Parameters.Add(param);
                }

                sda.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt; //dt.Rows.Count可能等于零
        }
    }
}