Importation en bloc à partir de MS Access et insertion dans SQL Server

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

Question

Je veux lire les enregistrements de la base de données MS Access et insérer dans la base de données du serveur SQL, le processus devrait être l'insertion en bloc J'utilise asp.net/vb.net

Réponse acceptée

Tout d'abord lire les données de la feuille 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);
    }
}

Réponse populaire

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();                     
}


Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi