将Access数据库中数据导入到SQLServer中的详细方法实例

publicpartialclassAccessToSQL:System.Web.UI.Page
{
   protectedvoidPage_Load(objectsender,EventArgse)
   {
       if(!IsPostBack)
       {
           AccessLoadData();
       }
   }
   publicOleDbConnectionCreateCon()
   {
       stringstrconn="Provider=Microsoft.Jet.OLEDB.4.0;Datasource="+Server.MapPath("UserScore.mdb")+";UserId=admin;Password=;";
       OleDbConnectionodbc=newOleDbConnection(strconn);
       returnodbc;
   }
   publicSqlConnectionCreateSQLCon()
   {
       stringsqlcon=ConfigurationSettings.AppSettings["strCon"];
       SqlConnectionmycon=newSqlConnection(sqlcon);
       returnmycon;
   }
   protectedvoidButton1_Click(objectsender,EventArgse)
   {
       stringsql="";
       OleDbConnectioncon=CreateCon();//创建数据库连接
       con.Open();
       DataSetds=newDataSet();//创建数据集
       sql="select*fromScore";
       OleDbDataAdaptermyCommand=newOleDbDataAdapter(sql,con);//创建数据适配器
       myCommand.Fill(ds,"Score");
       myCommand.Dispose();
       DataTableDT=ds.Tables["Score"];
       con.Close();
       myCommand.Dispose();
       for(intj=0;j<DT.Rows.Count;j++)//循环ACCESS中数据获取相应信息
       {
           stringsqlstr="";
           stringID=DT.Rows[j][0].ToString();
           stringUserName=DT.Rows[j][1].ToString();
           stringPaperName=DT.Rows[j][2].ToString();
           stringUserScore=DT.Rows[j][3].ToString();
           stringExamTime=DT.Rows[j][4].ToString();
           stringselsql="selectcount(*)fromAccessToSQLwhere用户姓名=""+UserName+""";
           if(ExScalar(selsql)>0)//判断数据是否已经添加
           {
               Label1.Visible=true;
               Label1.Text="<scriptlanguage=javascript>alert("该Access数据库中数据已经导入SQL数据库中!");location="AccessToSQL.aspx";</script>";
           }
           else
           {
               stringAccessPath=Server.MapPath("UserScore.mdb");//获取ACCESS数据库路径
               //应用OPENROWSET函数访问OLEDB数据源中的远程数据所需的全部连接信息
               sqlstr="insertintoAccessToSQL(ID,用户姓名,试卷,成绩,考试时间)Values(""+ID+"",""+UserName+"",""+PaperName+"",""+UserScore+"",""+ExamTime+"")";
               sqlstr+="select*FROMOPENROWSET("Microsoft.Jet.OLEDB.4.0",""+AccessPath+"";"admin";"",Score)";
               SqlConnectionconn=CreateSQLCon();
               conn.Open();
               SqlCommandmycom=newSqlCommand(sqlstr,conn);
               mycom.ExecuteNonQuery();//执行添加操作
               if(j==DT.Rows.Count-1)
               {
                   Label1.Visible=true;
                   Label1.Text="<scriptlanguage=javascript>alert("数据导入成功.");location="AccessToSQL.aspx";</script>";
               }
               else
               {
                   Label1.Visible=true;
                   Label1.Text="<scriptlanguage=javascript>alert("数据导入失败.");location="AccessToSQL.aspx";</script>";
               }
               conn.Close();
           }
       }

   }
   publicvoidAccessLoadData()
   {
       OleDbConnectionmyConn=CreateCon();
       myConn.Open();  //打开数据链接，得到一个数据集    
       DataSetmyDataSet=newDataSet();  //创建DataSet对象    
       stringStrSql="select  *  from Score";
       OleDbDataAdaptermyCommand=newOleDbDataAdapter(StrSql,myConn);
       myCommand.Fill(myDataSet,"Score");
       GridView2.DataSource=myDataSet;
       GridView2.DataBind();
       myConn.Close();
   }
   publicintExScalar(stringsql)
   {
       SqlConnectionconn=CreateSQLCon();
       conn.Open();
       SqlCommandcom=newSqlCommand(sql,conn);
       returnConvert.ToInt32(com.ExecuteScalar());
       conn.Close();
   }
   protectedvoidButton2_Click(objectsender,EventArgse)
   {
       stringsqlstr="select*fromAccessToSQL";
       SqlConnectionconn=CreateSQLCon();
       conn.Open();
       SqlCommandmycom=newSqlCommand(sqlstr,conn);
       SqlDataReaderdr=mycom.ExecuteReader();
       dr.Read();
       if(dr.HasRows)
       {
           GetDataSet(sqlstr);
       }
       else
       {
           Label1.Visible=true;
           Label1.Text="<scriptlanguage=javascript>alert("数据库中没有数据信息，请先导入再查询！");location="AccessToSQL.aspx";</script>";
       }
       dr.Close();
       conn.Close();
   }
   publicDataSetGetDataSet(stringsqlstr)
   {
       SqlConnectionconn=CreateSQLCon();
       SqlDataAdaptermyda=newSqlDataAdapter(sqlstr,conn);
       DataSetds=newDataSet();
       myda.Fill(ds);
       GridView1.DataSource=ds;
       GridView1.DataBind();
       returnds;
   }
}