将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;
}
}
推荐阅读
-
将Excel中数据导入到Access数据库中的方法
-
将Access数据库中数据导入到SQL Server中的详细方法实例
-
SQL Server 2000中修改数据库COLLATE的实例
-
SQL Server中聚合历史备份信息对比数据库增长的方法
-
C#实现Excel表数据导入Sql Server数据库中的方法
-
如何将数据导入到 SQL Server Compact Edition 数据库中（四）
-
如何将数据导入到 SQL Server Compact Edition 数据库中（五）
-
Linux下将excel数据导入到mssql数据库中的方法
-
sql server中通过查询分析器实现数据库的备份与恢复方法分享
-
SQL Server数据库中批量导入数据的四种方法总结