ADO.NET 连接数据库的另一种方法（通过配置文件和基类转化，适用于后期维护）

本文参考C#高级编程（第七版）30.2

首先是书上一个简单的数据库连接的demo

using System;
using System.Data.SqlClient;

public class ExecuteNonQueryExample
{
   public static void Main(string[] args)
   {
      string source = "server=(local);" +
                      "integrated security=SSPI;" +
                      "database=Northwind";
      string select = "UPDATE Customers " +
                      "SET ContactName = 'Bob' " +
                      "WHERE ContactName = 'Bill'";
      SqlConnection  conn = new SqlConnection(source);
      conn.Open();
      SqlCommand cmd = new SqlCommand(select, conn);
      int rowsReturned = cmd.ExecuteNonQuery();
      Console.WriteLine("{0} rows returned.", rowsReturned);
      conn.Close();
   }
}

为了方便管理连接数据库，更为了后期维护的方便。采用另外一种方式

将数据库的的配置信息放到配置文件（app.config）中，如下

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="db"
         providerName="System.Data.SqlClient"
         connectionString="server=(local)\SQLEXPRESS;integrated security=SSPI;database=test;" />
  </connectionStrings>
</configuration>

 其次为了让适用于各种类型的数据库，将连接方式专门新建一个静态类，命名databases.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Configuration;

namespace ConsoleApplication1
{
    static class databases
    {
        static public DbConnection GetDatabaseConnection(string name)
        {
            ConnectionStringSettings settings =
              ConfigurationManager.ConnectionStrings[name];

            DbProviderFactory factory = DbProviderFactories.GetFactory(settings.ProviderName);

            DbConnection conn = factory.CreateConnection();
            conn.ConnectionString = settings.ConnectionString;

            return conn;
        }
    }
}

如果出现 ConnectionStringSettings 无法识别的情况，需要在解决方案的那里引用一个System.Configuration

那么新的数据库操作方式就是

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string select = "insert into person(name,sex) values('bb','sb')";
            try
            {
                //返回DbConnection类型的连接，强制转换成SqlConnection类型，SqlConnection用以连接sql server数据库
                using (SqlConnection conn = (SqlConnection)databases.GetDatabaseConnection("db"))
                {

                    conn.Open();
                    SqlCommand cmd = new SqlCommand(select, conn);
                    int rowsReturned = cmd.ExecuteNonQuery();
                    Console.WriteLine("{0} rows returned.", rowsReturned);
                    conn.Close();
                    Console.ReadLine();
                }
                //useing的对象在结束后会自动销毁对象。
            }
            catch(SqlException e)
            {
                throw e;
            }
       }


    }
}