将mysql表导入sqlserver中_将Access数据库中数据导入到SQL Server中的详细方法实例

Default.aspx

无标题页

.style1

{

height: 16px;

}

.style3

{

height: 23px;

}

cellspacing="0">

style="FONT-SIZE: 9pt; COLOR: #ffffff; HEIGHT: 16px; BACKGROUND-COLOR: #ff9933; TEXT-ALIGN: center">

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

GridLines="None" style="font-size: small" Width="331px">

ForeColor="#333333" GridLines="None" Width="228px">

valign="top">

Text="Access数据写入SQL数据库中" />

style="font-size: x-small">

Text="SQL数据库中显示导入的数据" />

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;

}

}