SQLの一括挿入はC#で値を挿入しない

c# oledbdatareader sqlbulkcopy

質問

私はC#を全く新しくしているので、私のコードがどのようにフォーマットされているかについて多くのコメントを得るつもりです - 私はそれらを歓迎します。あなたが道に沿って持っている可能性のあるアドバイスや建設的な批判を放棄してください。

私は最終的に、さまざまなサイズのExcelファイルから1日に複数回データを取り込み、SQL Server 2005のテーブルに挿入することになっている非常に単純なWindowsフォームアプリケーションを構築しています。その後、データベース内のストアドプロシージャこの表に挿入された値に応じて、さまざまな更新および挿入タスクを実行するために引き継ぎます。

この理由から、SQL Bulk Insertメソッドを使用することに決めました。ユーザーが任意の実行で10行または10,000行しか挿入しないかどうかわかりません。

私が使用している関数は次のようになります:

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

この関数はエラーや警告なしで実行され、 WriteToServerを手動のSQLコマンドに置き換えると行が挿入されます。 bulkImportは何も挿入していません。

注:この例では1つのフィールドしかありません。実際にはテスト中です。最終的には何十ものフィールドが挿入され、それらのすべてに対してColumnMappingを実行します。

また、述べたように、私のコードはおそらく恐ろしいことに気付いています。あなたが助けてくれるポインタを私に教えてください。私は準備ができており、学びたいと思っています。

ありがとう!

受け入れられた回答

Excelからスキーマ情報を読み込むサンプルを以下に示します(ここではテーブル名のテーブル名をテーブルに読み込みます)。

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

そしてここに、SBCが臨時のテーブルに勝るサンプルがあります:

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

これは動作しますが、長期的に考えるとカラム名が必要ですが、そのタイプが異なる可能性があります.SBCを使用してこのような処理を行うのは難しいかもしれません。代わりにMS SQLサーバのOpenQueryから直接行うこともできます:

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

人気のある回答

私はあなたのコードにコメントし、同じメッセージでポインタのサンプルコードを与えた場合、私はそれを2つのメッセージに分割することにしました。最初のコメント:

何を得るためにオートメーションを使用していますか?あなたはすでにシート名を持っていますが、最後にapp.Quit()を行っていると悪化しています。そのオートメーションコードを完全に削除します。 Excel(シート名、列名など)からの情報が必要な場合は、OleDbConnectonのGetOleDbSchemaTableメソッドを使用できます。基本的に2つの方法でマッピングを行うことができます:

  1. Excelの列序数からSQLテーブルの列名
  2. Excelの列名からSQLの表の列名

どちらもします。一般的なコードでは、両方のソースで列名が同じであるが、序数と数が異なる場合、OleDbConnectionスキーマテーブルから列名を取得し、ループ内でマッピングを実行できます。

tempデータの挿入の目的で "ImportFromExcel"という名前のテーブルを作成して作成したら、単にテーブル名に#プレフィックスを付けてtemp SQLサーバーテーブルを作成しないのはなぜですか? OTOHのコード部分はちょっと変わっていますが、 "ImportFromExcel"があればそれをインポートし、新しいものをドロップして作成し、新しいものに一括インポートしようとします。最初の実行では、SqlBulkCopy(SBC)はImportFromExcelを満たし、次回の実行時には(DateTime.Now ...)という名前のテーブルにコピーされ、次にドロップによって空になり、再び作成されます。ところで、命名:

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

気分が悪いそれは魅力的ですが、それはソート可能ではありません。おそらく、代わりに次のようなものが必要でしょう。

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

それとももっと良い:

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

あなたは何らかの理由でワイルドカードまたはループとしてすべてのインポートに対してソートされ、選択可能なものを持っています。

次に、reader.Read()ループ内のサーバーに書き込んでいます。これはWriteToServerが動作する方法ではありません。あなたはreader.Read()ではなく単純に次のようにします:

sbc.WriteToServer(reader);

私の次のメッセージでは、シンプルなスキーマの読書と簡単なSBCのサンプルをtempテーブルに加えて、その代わりにどのようにすべきかを提案します。



ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ
ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ