將Access資料庫中資料匯入到SQL Server中的詳細方法執行個體

最後更新：2018-12-08 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

Default.aspx

複製代碼代碼如下:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AccessToSQL.aspx.cs" Inherits="AccessToSQL" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

.style1
{
height: 16px;
}
.style3
{
height: 23px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
<table align="center" border="1" bordercolor="honeydew" cellpadding="0"
cellspacing="0">
<tr>
<td colspan="2"
style="FONT-SIZE: 9pt; COLOR: #ffffff; HEIGHT: 16px; BACKGROUND-COLOR: #ff9933; TEXT-ALIGN: center">
將Access資料庫中資料寫入SQL Server資料庫中</td>
</tr>
<tr>
<td style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center">
<asp:GridView ID="GridView2" runat="server" CellPadding="4" ForeColor="#333333"
GridLines="None" style="font-size: small" Width="331px">
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</td>
<td style="WIDTH: 190px; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center">
<asp:GridView ID="GridView1" runat="server" CellPadding="4" Font-Size="9pt"
ForeColor="#333333" GridLines="None" Width="228px">
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</td>
</tr>
<tr>
<td style="HEIGHT: 23px; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center"
valign="top">
<asp:Button ID="Button3" runat="server" Font-Size="9pt" onclick="Button1_Click"
Text="Access資料寫入SQL資料庫中" />
<asp:Label ID="Label1" runat="server" Text="Label" Visible="False"
style="font-size: x-small"></asp:Label>
</td>
<td style="WIDTH: 190px; HEIGHT: 23px; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">
<asp:Button ID="Button2" runat="server" Font-Size="9pt" onclick="Button2_Click"
Text="SQL資料庫中顯示匯入的資料" />
</td>
</tr>
</table>
</form>
</body>
</html>

Default.aspx.cs

複製代碼代碼如下:using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
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 System.Xml.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;

public partial class AccessToSQL : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
AccessLoadData();
}
}
public OleDbConnection CreateCon()
{
string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Server.MapPath("UserScore.mdb") + ";User Id=admin;Password=;";
OleDbConnection odbc = new OleDbConnection(strconn);
return odbc;
}
public SqlConnection CreateSQLCon()
{
string sqlcon = ConfigurationSettings.AppSettings["strCon"];
SqlConnection mycon = new SqlConnection(sqlcon);
return mycon;
}
protected void Button1_Click(object sender, EventArgs e)
{
string sql = "";
OleDbConnection con = CreateCon();//建立資料庫連接
con.Open();
DataSet ds = new DataSet(); //建立資料集
sql = "select * from Score";
OleDbDataAdapter myCommand = new OleDbDataAdapter(sql,con);//建立資料配接器
myCommand.Fill(ds, "Score");
myCommand.Dispose();
DataTable DT = ds.Tables["Score"];
con.Close();
myCommand.Dispose();
for (int j = 0; j < DT.Rows.Count; j++)//迴圈ACCESS中資料擷取相應資訊
{
string sqlstr = "";
string ID = DT.Rows[j][0].ToString();
string UserName = DT.Rows[j][1].ToString();
string PaperName = DT.Rows[j][2].ToString();
string UserScore = DT.Rows[j][3].ToString();
string ExamTime = DT.Rows[j][4].ToString();
string selsql = "select count(*) from AccessToSQL where 使用者姓名='" + UserName + "'";
if (ExScalar(selsql) > 0)//判斷資料是否已經添加
{
Label1.Visible = true;
Label1.Text = "<script language=javascript>alert('該Access資料庫中資料已經匯入SQL資料庫中!');location='AccessToSQL.aspx';</script>";
}
else
{
string AccessPath = Server.MapPath("UserScore.mdb");//擷取ACCESS資料庫路徑
//應用OPENROWSET函數訪問 OLE DB 資料來源中的遠端資料所需的全部串連資訊
sqlstr = "insert into AccessToSQL(ID,使用者姓名,試卷,成績,考試時間)Values('" + ID + "','" + UserName + "','" + PaperName + "','" + UserScore + "','" + ExamTime + "')";
sqlstr += "select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','" + AccessPath + "';'admin';'',Score)";
SqlConnection conn = CreateSQLCon();
conn.Open();
SqlCommand mycom = new SqlCommand(sqlstr, conn);
mycom.ExecuteNonQuery();//執行添加操作
if (j == DT.Rows.Count - 1)
{
Label1.Visible = true;
Label1.Text = "<script language=javascript>alert('資料匯入成功.');location='AccessToSQL.aspx';</script>";
}
else
{
Label1.Visible = true;
Label1.Text = "<script language=javascript>alert('資料匯入失敗.');location='AccessToSQL.aspx';</script>";
}
conn.Close();
}
}

}
public void AccessLoadData()
{
OleDbConnection myConn = CreateCon();
myConn.Open(); //開啟資料連結，得到一個資料集
DataSet myDataSet = new DataSet(); //建立DataSet對象
string StrSql = "select * from Score";
OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, myConn);
myCommand.Fill(myDataSet, "Score");
GridView2.DataSource = myDataSet;
GridView2.DataBind();
myConn.Close();
}
public int ExScalar(string sql)
{
SqlConnection conn = CreateSQLCon();
conn.Open();
SqlCommand com = new SqlCommand(sql, conn);
return Convert.ToInt32(com.ExecuteScalar());
conn.Close();
}
protected void Button2_Click(object sender, EventArgs e)
{
string sqlstr = "select * from AccessToSQL";
SqlConnection conn = CreateSQLCon();
conn.Open();
SqlCommand mycom = new SqlCommand(sqlstr, conn);
SqlDataReader dr = mycom.ExecuteReader();
dr.Read();
if (dr.HasRows)
{
GetDataSet(sqlstr);
}
else
{
Label1.Visible = true;
Label1.Text = "<script language=javascript>alert('資料庫中沒有資料資訊，請先匯入再查詢！');location='AccessToSQL.aspx';</script>";
}
dr.Close();
conn.Close();
}
public DataSet GetDataSet(string sqlstr)
{
SqlConnection conn = CreateSQLCon();
SqlDataAdapter myda = new SqlDataAdapter(sqlstr, conn);
DataSet ds = new DataSet();
myda.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
return ds;
}
}

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More