%@ 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>
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;
}
}
