動態更改TableAdapter的ConnectionString

2009-08-18

7435
0
ASP.NET
2009-09-11

Eric隨手小記-動態更改TableAdapter的ConnectionString

因為網站需求而需要中英文兩個資料庫來做為變動，而我又不想使用復製在貼上做成兩個網站!

所以只好重連線字串這裡想辦法!

首先我使用北風資料庫當做範例

我總建立了

website

類別庫

DAL

DBUtility DataSet.xsd

第一步驟

首先將資料表用拖曳的方式，拉入DataSet中，並在TableAdapter上點選右鍵查看屬性

並將Connection->Modifier設定改成為Public

這樣一來就可以直接更改取得與設定他的連線字串

Adapter.Connection.ConnectionString = “XXXXXX”;

第二步驟

在DAL類別庫中先參考DBUtility專案

建立一個新的類別，為changeConnection，用來更換TableAdapter的連線字串

因為要使用到ConfigurationManager這個類別

所以要加入參考System.Configuration


using System;
using System.Collections.Generic;
using System.Text;

namespace DAL
{
    public class changeConnection
    {
        public static string change(string Name)
        {
            string connectionString = "";
            //將web.cofing中的參數分割成字串陣列
            string[] connLn = GetAppSettings("ConnectionLn").Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
            //將web.cofing中的參數分割成字串陣列
            string[] connString = GetAppSettings("ConnectionString").Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
            //用來查尋是否與輸入的語言相同在傳出該連線字串
            for (int i = 0; i < connLn.Length; i++)
            {
                if (connLn[i] == Name)
                {
                    connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[connString[i].ToString()].ConnectionString;
                }
            }
            return connectionString;
        }
        //取得Web.Config中的參數
        public static string GetAppSettings(string name)
        {
            string r = "";

            try
            {
                r = System.Configuration.ConfigurationManager.AppSettings[name];
            }
            catch (Exception ex)
            {
                r = ex.Message;
            }

            return r;
        }
    }

接下來

在建立DALCustomers.cs


using System;
using System.Collections.Generic;
using System.Text;
//一定要記得引用喔
using DBUtility.CustomersTableAdapters;
using DBUtility;
namespace DAL
{
    public class DALCustomers
    {
        //建立DBUtility中的Adapter
        private CustomersTableAdapter adapter = new CustomersTableAdapter();
        
        public void Connection(string Name)
        {
            //設定連線字串
            adapter.Connection.ConnectionString = changeConnection.changeConnection(Name);
        }

        public Customers.CustomersDataTable getData()
        {
            return adapter.GetData();
        }
    }
}

在網站這裡參考DAL類別庫

接下來在web.con 加入 appSettings & ConnectionStrings

已供DAL.changeConnection.changeConnection() 抓取


<appSettings>
    <add key="ConnectionLn" value="CH,EN"/>
    <add key="ConnectionString" value="ConnectionString,ConnectionStringEN" />
  </appSettings>
  <connectionStrings>
    <add name="ConnectionString"
    connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ChangeConnection\App_Data\NorthWind.mdb"
    providerName="System.Data.OleDb" />
    <add name="ConnectionStringEN"
            connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ChangeConnection\App_Data\NorthWindEN.mdb"
            providerName="System.Data.OleDb" />
  </connectionStrings>

第三步驟

前台設計


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>未命名頁面</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" >
        </asp:DropDownList>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    </div>
    </form>
</body>
</html>


using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
//參考類別庫
using DAL;
public partial class _Default : System.Web.UI.Page 
{
    DALCustomers customers = new DALCustomers();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //抓取web.config參數
            string[] connLn = changeConnection.GetAppSettings("ConnectionLn").Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
            for (int i = 0; i < connLn.Length; i++)
            {
                DropDownList1.Items.Add(connLn[i]);
            }            
        }
        Session["ln"] = DropDownList1.SelectedValue;
        if (Session["ln"] != null)
        {
            DropDownList1.SelectedValue = Session["ln"].ToString();
            customers.Connection(Session["ln"].ToString());
            GridView1.DataSource = customers.getData();
            GridView1.DataBind();
        }
    }
}

結果:

中文

英文

這只是我目前採用的方法但是還是寫的很死希望有大大可以提供更好的方法!!

ASP.NET

回首頁