ACCESS数据导入SQL SERVER 2005

 

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;


public partial class CopyAcess : System.Web.UI.Page
...{

    private int _id;
    private int _qid;
    private int _uid;
    private string _content;
    private string  _subdate;
    private string  _update;
    private string _onred;
    private string _passed;
    private string _closed;

    public void copyToSQLServer()
    ...{
       
        string AceConnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/DB/base.mdb");
        OleDbConnection myAceConnection = new OleDbConnection(AceConnectionstring);
        string SQLconnectionstring = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString;
        SqlConnection mySqlConnection = new SqlConnection(SQLconnectionstring);
        string InsertSQL = "";
        string AceSelectString = "select * from answer";
        DataSet dsAce = new DataSet();
        OleDbDataAdapter Aceda = new OleDbDataAdapter(AceSelectString, myAceConnection);
        SqlCommand myCommand = new SqlCommand();
          int succ = 0;
        try
        ...{
            myAceConnection.Open();
            mySqlConnection.Open();
            Aceda.Fill(dsAce, "answer");
            DataView myAceDv = dsAce.Tables[0].DefaultView;
            for (int i = 0; i < myAceDv.Count; i++)
            ...{
              
                _id =Convert.ToInt32( myAceDv[i][0].ToString());
                _qid = Convert.ToInt32(myAceDv[i][1].ToString());
                _uid = Convert.ToInt32(myAceDv[i][2].ToString());
                _content = myAceDv[i][3].ToString();
                _subdate = myAceDv[i][4].ToString();
                _update = myAceDv[i][5].ToString();
                _onred = myAceDv[i][6].ToString();
                _passed = myAceDv[i][7].ToString();
                _closed = myAceDv[i][8].ToString();
                InsertSQL=  "insert into answer values(" + _id + "," + _qid + "," + _uid + ",'" + _content + "','" + _subdate + "','" + _update + "','" + _onred + "','" + _passed + "','" + _closed + "')";
                myCommand.CommandText = InsertSQL;
                myCommand.Connection = mySqlConnection;
                succ=myCommand.ExecuteNonQuery();   
             
            }
            if (succ > 0)
            ...{
                Response.Write("successful!");
            }
            else
            ...{
                Response.Write("false!");
            }

        }
        catch (Exception ex)
        ...{
            throw new Exception(ex.Message, ex);
        }
        finally
        ...{
            myAceConnection.Close();
            mySqlConnection.Close();
            myAceConnection.Dispose();
            mySqlConnection.Dispose();
        }       
    }



    protected void Page_Load(object sender, EventArgs e)
    ...{

    }


    protected void Button1_Click(object sender, EventArgs e)
    ...{
        copyToSQLServer();
    }
}