【Access】将Access数据库中数据导入到SQL Server中的详细方法实例-数据-SQL

欢迎您访问我爱IT技术网，今天小编为你分享的编程技术是：【将Access数据库中数据导入到SQL Server中的详细方法实例】，下面是详细的讲解！

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

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="<script language=javascript>alert('该Access数据库中数据已经导入SQL数据库中!');location='AccessToSQL.aspx';</script>";
            }
            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="<script language=javascript>alert('数据导入成功.');location='AccessToSQL.aspx';</script>";
                }
                else
                {
                    Label1.Visible=true;
                    Label1.Text="<script language=javascript>alert('数据导入失败.');location='AccessToSQL.aspx';</script>";
                }
                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="<script language=javascript>alert('数据库中没有数据信息，请先导入再查询！');location='AccessToSQL.aspx';</script>";
        }
        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;
    }
}

关于将Access数据库中数据导入到SQL Server中的详细方法实例的用户互动如下：

　　相关问题：

　　答： >>详细

　　相关问题：

　　答： >>详细

　　相关问题：

　　答： >>详细