C#簡單代碼轉移資料庫資料

標籤：window   ons   資料庫   update   c#   hand   initial   refresh   esc   

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

namespace WangwoSoft.ShejiWorld.WebSite.GeneralHandler
{
/// <summary>
/// MemberHandler 的摘要說明
/// </summary>
public class MemberHandler : IHttpHandler
{

public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
//要複製的表名
string table = "member_user";

//構造連接字串
SqlConnectionStringBuilder sqlConnect1 = new SqlConnectionStringBuilder();
sqlConnect1.DataSource = "211.149.***.***";
sqlConnect1.InitialCatalog = "*****";//目標資料庫
sqlConnect1.IntegratedSecurity = false;
sqlConnect1.UserID = "*****";//登入ID
sqlConnect1.Password = "*******";//資料庫密碼

SqlConnectionStringBuilder sqlConnect2 = new SqlConnectionStringBuilder();
sqlConnect2.DataSource = "211.149.***.***";
sqlConnect2.InitialCatalog = "*****";//來源資料庫
sqlConnect2.IntegratedSecurity = false;//當true的時候為windows身分識別驗證
sqlConnect2.UserID = "*******";//登入ID
sqlConnect2.Password = "*******";//資料庫密碼

//調用複製資料庫函數
string result = InsertTable(sqlConnect1.ConnectionString, sqlConnect2.ConnectionString, table);
context.Response.Write(result);
}

//參數為兩個資料庫的連接字串
private string InsertTable(string conString1,string conString2,string tabString)
{
//串連資料庫
SqlConnection conn1 = new SqlConnection();
conn1.ConnectionString = conString1;
conn1.Open();

SqlConnection conn2 = new SqlConnection();
conn2.ConnectionString = conString2;
conn2.Open();

//填充DataSet1
SqlDataAdapter adapter1 = new SqlDataAdapter("select * from "+tabString,conn1);
DataSet dataSet1 = new DataSet();
if (dataSet1!=null)
{
adapter1.Fill(dataSet1, tabString);
}

SqlDataAdapter adapter2 = new SqlDataAdapter("select * from " + tabString, conn2);
DataSet dataSet2 = new DataSet();

SqlCommand cmd2 = new SqlCommand("select COUNT(*) from "+tabString,conn2);

Object res2 = cmd2.ExecuteScalar();
if (res2!=null)
{
int nCount = Convert.ToInt32(res2.ToString());
if (nCount==0)
{
conn1.Close();
conn2.Close();
return "沒有資料";
}
}

//填充DataSet2
if (dataSet2!=null)
{
adapter2.Fill(dataSet2, tabString);
}

//複製資料
for (int i = 0; i < dataSet2.Tables[0].Rows.Count; i++)
{
dataSet1.Tables[0].LoadDataRow(dataSet2.Tables[0].Rows[i].ItemArray, false);
}

//將DataSet變換顯示在與其關聯的目標資料庫
SqlCommandBuilder cb = new SqlCommandBuilder(adapter1);
adapter1.Update(dataSet1, tabString);
cb.RefreshSchema();

return "表" + tabString + "複製成功！";
conn1.Close();
conn2.Close();
}

public bool IsReusable
{
get
{
return false;
}
}
}
}

C#簡單代碼轉移資料庫資料