C# 用配置文件连接数据库（Winform和Webform）

引用自：https://blog.csdn.net/sinat_35187039/article/details/82630836

Winform中用配置文件连接数据库

数据库连接类YSqlHelper代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace WindowsFormsApp1
{
    public class YSqlHelper
    {

        //配置文件的引用对象ConfigurationManager
        //ConnectionSQLString：配置文件中数据库连接字符串的标签名
        private static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionSQLString"].ConnectionString;

        //查询数据库得到结果放入Datatable并返回
        public static DataTable ExecuteNonQuery(String cmdText,CommandType ct)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                    {
                        cmd.CommandType = ct;
                        using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            dt.Load(sdr);
                        }
                    }
                }
                catch(Exception e)
                {
                    System.Diagnostics.Debug.WriteLine(e.Message);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    System.Diagnostics.Debug.WriteLine(dt.Rows.Count);
                }
            }
                return dt;
        }
    }
}

 

项目配置文件app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
  <connectionStrings>
    <add name="ConnectionSQLString" connectionString="Data Source=192.168.50.40;Initial Catalog=Training;Persist Security Info=True;User ID=sa;Password=Sitri.123"/>
  </connectionStrings>
</configuration>

在解决方案中选择“引用”，右键选择“添加引用”。 

 
搜索system.configuration并勾选添加到引用。 
 
添加后引用中出现system.configuration，在YSqlHelper类代码头部添加 
using System.Configuration后ConfigurationManager方有效可用。 
 
测试数据库连接成功，获得dt

using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            DataTable dt = getTable();
        }
        private DataTable getTable()
        {
            string  sql  = "select DISTINCT  DepartmentsID as Dept_id,DepartmentsName as Dept_name,CourseID as Course_id,CourseName as Course_name,CoursePath as Course_path from Course, Department where Course.DepartmentID = Department.DepartmentsID order by DepartmentsID asc, CourseID asc; ";
            DataTable dt = YSqlHelper.ExecuteNonQuery(sql, CommandType.Text);
            return dt;
        }
    } 
}

Webform用配置文件连接数据库

数据库连接帮助类YSqlHelper代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace WebForm_2._0
{
    public class YSqlHelper
    {
        private static string connectionString = ConfigurationManager.AppSettings["sqlStr"];
        /// <param name="cmdText">查询SQL语句或存储过程ì</param>
        /// <param name="ct">命令类型</param>
        /// <returns>DataTable对象</returns>
        public static DataTable ExecuteQuery(string cmdText, CommandType ct)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                System.Diagnostics.Debug.Write(conn);
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                    {
                        cmd.CommandType = ct;
                        using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            System.Diagnostics.Debug.Write(sdr);
                            dt.Load(sdr);
                        }
                    }
                }
                catch(Exception e)
                {
                    System.Diagnostics.Debug.Write(e.Message);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    System.Diagnostics.Debug.Write(dt);
                    System.Diagnostics.Debug.Write(dt.Rows.Count);
                }
            }
            return dt;
        }
    }
}

web配置文件web.config

<?xml version="1.0" encoding="utf-8"?>

<configuration>
  <appSettings>
    <add key="sqlStr" value="Data Source=192.168.50.40;Initial Catalog=Training;Persist Security Info=True;User ID=sa;Password=Sitri.123"/>
  </appSettings>
  <system.web>
    <compilation debug="true" targetFramework="4.6.1"/>
    <httpRuntime targetFramework="4.6.1"/>
  </system.web>

</configution>

测试数据库连接成功，获得dt查询结果：

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebForm_2._0
{
    public partial class treeview02 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataTable dt = getTable();
        }
        private DataTable getTable()
        {
            string sqlStr = "select DISTINCT  DepartmentsID as Dept_id,DepartmentsName as Dept_name,CourseID as Course_id,CourseName as Course_name,CoursePath as Course_path from Course, Department where Course.DepartmentID = Department.DepartmentsID order by DepartmentsID asc, CourseID asc; ";
            DataTable dt = YSqlHelper.ExecuteQuery(sqlStr, CommandType.Text);
            return dt;
        }
    }
}