SQL Bulk Insert in C # n'insère pas de valeurs

c# oledbdatareader sqlbulkcopy

Question

Je suis complètement nouveau en C #, alors je suis sûr que je vais recevoir beaucoup de commentaires sur la façon dont mon code est formaté - je les accueille. N'hésitez pas à formuler des conseils ou des critiques constructives que vous pourriez avoir en cours de route.

Je construis une application Windows Form très simple qui doit éventuellement prendre les données d'un fichier Excel de taille variable, éventuellement plusieurs fois par jour, et les insérer dans une table de SQL Server 2005. Ensuite, une procédure stockée dans la base de données prend le relais pour effectuer diverses tâches de mise à jour et d’insertion en fonction des valeurs insérées dans ce tableau.

Pour cette raison, j'ai décidé d'utiliser la méthode SQL Bulk Insert, car je ne peux pas savoir si l'utilisateur n'insérera que 10 lignes - ou 10 000 - à une exécution donnée.

La fonction que j'utilise ressemble à ceci:

public void BulkImportFromExcel(string excelFilePath)
{
    excelApp = new Excel.Application();
    excelBook = excelApp.Workbooks.Open(excelFilePath);
    excelSheet = excelBook.Worksheets.get_Item(sheetName);
    excelRange = excelSheet.UsedRange;
    excelBook.Close(0);
    try
    {
        using (SqlConnection sqlConn = new SqlConnection())
        {
            sqlConn.ConnectionString =
            "Data Source=" + serverName + ";" +
            "Initial Catalog=" + dbName + ";" +
            "User id=" + dbUserName + ";" +
            "Password=" + dbPassword + ";";
            using (OleDbConnection excelConn = new OleDbConnection())
            {
                excelQuery = "SELECT InvLakNo FROM [" + sheetName + "$]";
                excelConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 8.0;HDR=Yes'";
                excelConn.Open();
                using (OleDbCommand oleDBCmd = new OleDbCommand(excelQuery, excelConn))
                {
                    OleDbDataReader dataReader = oleDBCmd.ExecuteReader();
                    using (SqlBulkCopy bulkImport = new SqlBulkCopy(sqlConn.ConnectionString))
                    {
                        bulkImport.DestinationTableName = sqlTable;
                        SqlBulkCopyColumnMapping InvLakNo = new SqlBulkCopyColumnMapping("InvLakNo", "InvLakNo");
                        bulkImport.ColumnMappings.Add(InvLakNo);
                        sqlQuery = "IF OBJECT_ID('ImportFromExcel') IS NOT NULL BEGIN SELECT * INTO [" + DateTime.Now.ToString().Replace(" ", "_") + "_ImportFromExcel] FROM ImportFromExcel; DROP TABLE ImportFromExcel; END CREATE TABLE ImportFromExcel (InvLakNo INT);";
                        using (SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn))
                        {
                            sqlConn.Open();
                            sqlCmd.ExecuteNonQuery();
                            while (dataReader.Read())
                            {
                                bulkImport.WriteToServer(dataReader);
                            }
                        }
                    }
                }
            }
        }
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        excelApp.Quit();
    }
}

La fonction s'exécute sans erreur ni avertissement. Si je remplace le WriteToServer par des commandes SQL manuelles, les lignes sont insérées. mais le bulkImport rien.

Remarque: il n'y a qu'un seul champ dans cet exemple et dans la fonction réelle, je suis en train de tester; mais à la fin, il y aura des dizaines et des dizaines de champs insérés, et je vais faire un ColumnMapping pour tous.

En outre, comme indiqué, je suis conscient que mon code est probablement horrible - n'hésitez pas à me donner des indications que vous jugerez utiles. Je suis prêt et disposé à apprendre.

Merci!

Réponse acceptée

Voici l'exemple de lecture des informations de schéma à partir d'Excel (nous lisons ici les noms de tables - noms de feuilles contenant des tables):

private IEnumerable<string> GetTablesFromExcel(string dataSource)
{
    IEnumerable<string> tables;
    using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" +
    string.Format("Data Source={0};", dataSource) +
    "Extended Properties=\"Excel 12.0;HDR=Yes\""))
    {
        con.Open();
        var schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        tables = schemaTable.AsEnumerable().Select(t => t.Field<string>("TABLE_NAME")); 
        con.Close();
    }
    return tables;
}

Et voici un exemple qui permet à SBC d’exceler dans une table temporaire :

void Main()
{
  string sqlConnectionString = @"server=.\SQLExpress;Trusted_Connection=yes;Database=Test";

  string path = @"C:\Users\Cetin\Documents\ExcelFill.xlsx"; // sample excel sheet
  string sheetName = "Sheet1$";

  using (OleDbConnection cn = new OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+path+
    ";Extended Properties=\"Excel 8.0;HDR=Yes\""))

  using (SqlConnection scn = new SqlConnection( sqlConnectionString ))
  {

    scn.Open();
    // create temp SQL server table
    new SqlCommand(@"create table #ExcelData 
    (
      [Id] int, 
      [Barkod] varchar(20)
    )", scn).ExecuteNonQuery();

    // get data from Excel and write to server via SBC  
    OleDbCommand cmd = new OleDbCommand(String.Format("select * from [{0}]",sheetName), cn);
    SqlBulkCopy sbc = new SqlBulkCopy(scn);

    // Mapping sample using column ordinals
    sbc.ColumnMappings.Add(0,"[Id]");
    sbc.ColumnMappings.Add(1,"[Barkod]");

    cn.Open();
    OleDbDataReader rdr = cmd.ExecuteReader();
    // SqlBulkCopy properties
    sbc.DestinationTableName = "#ExcelData";
    // write to server via reader
    sbc.WriteToServer(rdr);
    if (!rdr.IsClosed) { rdr.Close(); }
    cn.Close();

    // Excel data is now in SQL server temp table
    // It might be used to do any internal insert/update 
    // i.e.: Select into myTable+DateTime.Now
    new SqlCommand(string.Format(@"select * into [{0}] 
                from [#ExcelData]", 
                "ImportFromExcel_" +DateTime.Now.ToString("yyyyMMddHHmmss")),scn)
        .ExecuteNonQuery();
    scn.Close();
  }
}

Bien que cela puisse fonctionner, à long terme, vous avez besoin de noms de colonnes et peut-être que leurs types diffèrent, il peut être excessif de faire cela à l'aide de SBC et vous pouvez le faire directement à partir d'OpenQuery du serveur MS SQL:

SELECT * into ... from OpenQuery(...)  

Réponse populaire

Je pense que la réponse serait longue et compliquée si je commentais votre code et donnais également des exemples de code de pointeur dans le même message. J'ai donc décidé de le diviser en deux messages. Commentaires d'abord:

Vous utilisez l'automatisation pour obtenir quoi? Vous avez déjà le nom de la feuille tel que je le vois et, pire encore, vous utilisez app.Quit () à la fin. Supprimez complètement ce code d'automatisation. Si vous aviez besoin d'informations d'Excel (comme des noms de feuille, des noms de colonne), vous pouvez utiliser la méthode GetOleDbSchemaTable de OleDbConnecton. Vous pouvez effectuer le mappage essentiellement de 2 manières:

  1. Colonne Excel en nom de colonne de la table SQL
  2. Nom de colonne Excel en nom de colonne de table SQL

les deux feraient. Dans un code générique, en supposant que les noms de colonne soient identiques dans les deux sources, mais leur nombre et leur nombre peuvent différer, vous pouvez obtenir les noms de colonne de la table de schéma OleDbConnection et effectuer le mappage en boucle.

Vous déposez et créez une table nommée "ImportFromExcel" dans le but d'insérer des données temporaires , alors pourquoi ne pas simplement créer une table de serveur SQL temporaire en utilisant un préfixe # dans le nom de la table? OTOH cette pièce de code est un peu bizarre, elle ferait une importation à partir de "ImportFromExcel" si elle existe, puis supprimait et en créait une nouvelle et tentait de faire une importation en bloc dans cette nouvelle. Lors de la première exécution, SqlBulkCopy (SBC) remplirait ImportFromExcel et lors de la prochaine exécution, il serait copié dans une table nommée (DateTime.Now ...) puis vidé par drop et créé à nouveau. BTW, nommant:

DateTime.Now.ToString().Replace(" ", "_") + "_ImportFromExcel"

ne se sent pas bien. Bien que cela ait l'air tentant, ce n'est pas triable, vous voudriez probablement quelque chose comme ça à la place:

DateTime.Now.ToString("yyyyMMddHHmmss") + "_ImportFromExcel"

Ou mieux encore:

"ImportFromExcel_" +DateTime.Now.ToString("yyyyMMddHHmmss")

de sorte que vous auriez quelque chose qui est trié et sélectionnable pour toutes les importations en tant que caractère générique ou en boucle pour une raison quelconque.

Ensuite, vous écrivez sur le serveur dans une boucle reader.Read (). Ce n'est pas comme cela que WriteToServer fonctionne. Vous ne feriez pas reader.Read () mais simplement:

sbc.WriteToServer(reader);

Dans mon prochain message, je vais vous donner une lecture de schéma simple, un exemple SBC simple d’Excel dans une table temporaire , ainsi qu’une suggestion sur la façon de procéder.



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