将SQL Server数据库数据导入到Access中

Default.aspx

View Code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SQLToAccess.aspx.cs" Inherits="SQLToAccess" %>

<!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>
</head>
<body>
    <form id="form1" runat="server">
    <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">
                将SQL Server数据库中数据写入Access数据库中</td>
        </tr>
        <tr>
            <td style="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>
            <td style="WIDTH: 190px; 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>
        </tr>
        <tr>
            <td style="HEIGHT: 23px; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center" 
                valign="top">
    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" 
        Text="将SQL数据库中数据导入到Access数据库" Height="26px" Width="251px" />
    <asp:Label ID="Label1" runat="server" Text="Label" Visible="False"></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="ACCESS数据库中显示导入的数据" Height="25px" Width="229px" />
            </td>
        </tr>
        </table>
    </div>
    </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.SqlClient;
using System.Data.OleDb;

public partial class SQLToAccess : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadData();
    }
    public SqlConnection CreateSQLCon()
    {
        string sqlcon = ConfigurationSettings.AppSettings["strCon"];
        SqlConnection mycon = new SqlConnection(sqlcon);
        return mycon;
    }
    public OleDbConnection CreateAccessCon()
    {
        string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Server.MapPath("mrfdw.mdb") + ";User Id=admin;Password=;";
        OleDbConnection odbc = new OleDbConnection(strconn);
        return odbc;
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            string sqlstr = "";
            string AccessPath = Server.MapPath("mrfdw.mdb");//查找ACCESS路径
            //使用openrowset执行将SQL Server数据库中数据导入到ACCESS中
            sqlstr = "insert into openrowset('Microsoft.Jet.OLEDB.4.0','" + AccessPath + "';'admin';'',tb_mrfdw22) select * from tb_mrgwh22";
            ExSQL(sqlstr);//执行导入操作
            Label1.Visible = true;
            Label1.Text = "<script language=javascript>alert('数据导入成功.');location='SQLToAccess.aspx';</script>";
        }
        catch
        {
            Label1.Visible = true;
            Label1.Text = "<script language=javascript>alert('数据导入失败.');location='SQLToAccess.aspx';</script>";
        }
    }
    public void ExSQL(string strsql)
    {
        SqlConnection myconn = CreateSQLCon();
        myconn.Open();
        SqlCommand mycom = new SqlCommand(strsql, myconn);
        mycom.ExecuteNonQuery();
        myconn.Close();
    }
    public void LoadData()
    {
        string strsql = "select * from tb_mrgwh22";
        SqlConnection cn = CreateSQLCon();
        cn.Open();
        SqlDataAdapter da = new SqlDataAdapter(strsql, cn);
        DataSet ds = new DataSet();
        da.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        cn.Close();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        string sql = "";
        OleDbConnection con = CreateAccessCon();//创建数据库连接
        con.Open();
        DataSet ds = new DataSet(); //创建数据集
        sql = "select * from tb_mrfdw22";
        OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, con);//创建数据适配器
        myCommand.Fill(ds, "tb_mrfdw22");
        myCommand.Dispose();
        GridView2.DataSource = ds;
        GridView2.DataBind();
        con.Close();
    }
}

posted on 2013-03-19 21:27 松竹梅阅读(752) 评论(0) 编辑收藏举报