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;
        }

    }


}

后续随着深入学习再会优化