将Access数据库中数据导入到SQL Server中的详细方法实例
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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
<style type="text/css">
.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;
}
}
