Default.aspx
View Code
<%@ 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
View Code
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; } }
