将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">

.style1

{

height: 16px;

}

.style3

{

height: 23px;

}

</style>

</head>

<body>

<div>

</div>

<table align="center" border="1" bordercolor="honeydew" cellpadding="0"

cellspacing="0">

<tr>

<td colspan="2"

将Access数据库中数据写入SQL Server数据库中</td>

</tr>

<tr>

<td>

<asp:GridView ID="GridView2" runat="server" CellPadding="4" ForeColor="#333333"

GridLines="None" Width="331px">

</asp:GridView>

</td>

<td>

<asp:GridView ID="GridView1" runat="server" CellPadding="4" Font-Size="9pt"

ForeColor="#333333" GridLines="None" Width="228px">

</asp:GridView>

</td>

</tr>

<tr>

<td

valign="top">

<asp:Button ID="Button3" runat="server" Font-Size="9pt"

Text="Access数据写入SQL数据库中" />

<asp:Label ID="Label1" runat="server" Text="Label" Visible="False"

></asp:Label>

</td>

<td>

<asp:Button ID="Button2" runat="server" Font-Size="9pt"

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;

}