MS Access에서 대량 가져 오기 및 SQL Server에 삽입

asp.net c# ms-access sqlbulkcopy sql-server

문제

MS Access 데이터베이스에서 레코드를 읽고 Sql 서버 데이터베이스에 삽입하려면 프로세스를 대량 삽입해야합니다. 나는 asp.net/vb.net를 사용하고있다.

수락 된 답변

먼저 Excel 시트에서 데이터를 읽습니다.

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/temp/") + "FileName.xlsx; Extended Properties=Excel 12.0;";

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
        DbDataAdapter adapter = factory.CreateDataAdapter();
        DbCommand selectCommand = factory.CreateCommand();
        selectCommand.CommandText = "SELECT ColumnNames FROM [Sheet1$]";
        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;
        selectCommand.Connection = connection;
        adapter.SelectCommand = selectCommand;
        DataTable dtbl = new DataTable();
        adapter.Fill(dtbl);
 // Then use SQL Bulk query to insert those data

        if (dtbl.Rows.Count > 0)
{

 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnection))
        {
            bulkCopy.ColumnMappings.Add("ColumnName", "ColumnName");
            bulkCopy.ColumnMappings.Add("ColumnName", "ColumnName");
        bulkCopy.DestinationTableName = "DBTableName";
        bulkCopy.WriteToServer(dtblNew);
    }
}

인기 답변

private void Synchronize()
{           
    SqlConnection con = new SqlConnection("Database=DesktopNotifier;Server=192.168.1.100\\sql2008;User=common;Password=k25#ap;");
    con.Open();
    SqlDataAdapter adap = new SqlDataAdapter("SELECT * FROM CustomerData", con);
    DataSet ds = new DataSet();
    adap.Fill(ds, "CustomerData");

    DataTable dt = new DataTable();
    dt = ds.Tables["CustomerData"];

    foreach (DataRow dr in dt.Rows)
    {                
        string File = dr["CustomerFile"].ToString();
        string desc = dr["Description"].ToString();

        string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=D:\\DesktopNotifier\\DesktopNotifier.accdb";

        OleDbConnection conn = new OleDbConnection(conString);
        conn.Open();
        string dbcommand = "insert into CustomerData (CustomerFile, Description) VALUES ('" + File + "', '" + desc + "')";
        OleDbCommand mscmd = new OleDbCommand(dbcommand, conn);

        mscmd.ExecuteNonQuery();                 
    }
}

private void Configuration_Load(object sender, EventArgs e)
{
    LoadGridData();
    LoadSettings();                     
}


아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.