【下载文档:  将Access数据库中数据导入到SQL Server中的详细方法实例.txt 】

---

将Access数据库中数据导入到SQL Server中的详细方法实例
Default.aspx
复制代码 代码如下:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AccessToSQL.aspx.cs" Inherits="AccessToSQL" %>

将Access数据库中数据写入SQL Server数据库中

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 = ""; } 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 = ""; } else { Label1.Visible = true; Label1.Text = ""; } 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 = ""; } 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; }}