首頁 > 開發者 > SQL Server

將Access資料庫中資料匯入到SQL Server中的詳細方法執行個體_實用技巧

來源：互聯網
上載者：User
創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

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

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

聯繫我們

該頁面正文內容均來源於網絡整理，並不代表阿里雲官方的觀點，該頁面所提到的產品和服務也與阿里云無關，如果該頁面內容對您造成了困擾，歡迎寫郵件給我們，收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容，歡迎發送郵件至： info-contact@alibabacloud.com 進行舉報並提供相關證據，工作人員會在 5 個工作天內聯絡您，一經查實，本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    Open a Ticket

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

    Learn More