C에서 대량 삽입 SQL # 값을 삽입하지 않음

c# oledbdatareader sqlbulkcopy

문제

저는 C #을 처음부터 완전히 익혔습니다. 따라서 코드가 어떻게 형식화되었는지에 대한 많은 의견을 얻을 것입니다. 나는 그것을 환영합니다. 길을 따라 가면서 조언이나 건설적인 비판을 던지 셔도 좋습니다.

나는 결국 하루에 여러 번 여러 크기의 Excel 파일에서 데이터를 가져 와서 SQL Server 2005의 테이블에 삽입하기로되어있는 매우 간단한 Windows Form App을 구축하고 있습니다. 그 후 데이터베이스 내의 저장 프로 시저 이 테이블에 삽입 된 값에 따라 다양한 업데이트 및 삽입 작업을 수행합니다.

이러한 이유 때문에, 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 명령으로 WriteToServer 경우 행이 삽입됩니다. 그러나 bulkImport 는 아무것도 삽입하지 않습니다.

참고 : 이 예제에는 하나의 필드 만 있고 실제 테스트를 위해 실행중인 실제 함수에는 필드가 하나 있습니다. 하지만 결국 수십개의 필드가 삽입 될 것이고, 나는 그들 모두를 위해 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(...)  

인기 답변

나는 당신의 코드에 주석을 달고 동일한 메시지에서 포인터 샘플 코드를 주었기 때문에 두 개의 메시지로 나누기로 결정했다면 매우 길고 지저분한 대답이라고 생각한다. 먼저 코멘트 :

당신은 자동화를 사용하여 무엇을 얻고 있습니까? 이미 시트 이름을 보았고 나빠질수록 app.Quit ()를 끝내고 있습니다. 해당 자동화 코드를 완전히 제거하십시오. 시트 이름, 열 이름과 같은 Excel의 정보가 필요하다면 OleDbConnecton의 GetOleDbSchemaTable 메서드를 사용할 수 있습니다. 기본적으로 두 가지 방법으로 매핑을 수행 할 수 있습니다.

  1. Excel 열 서수에서 SQL 테이블 열 이름으로
  2. Excel 열 이름 - SQL 테이블 열 이름

둘 다 할 것이다. 일반 코드에서 두 원본에서 같은 열 이름을 가졌지 만 서수와 개수가 다를 수 있다고 가정하면 OleDbConnection 스키마 테이블에서 열 이름을 가져 와서 루프에서 매핑을 수행 할 수 있습니다.

임시 데이터 삽입 목적으로 "ImportFromExcel"이라는 테이블을 삭제하고 생성하는 경우 테이블 이름에 접두사 #를 사용하여 임시 SQL 서버 테이블을 만들지 않는 이유는 무엇입니까? OTOH 코드 조각이 조금 이상하다면, "ImportFromExcel"이 있으면 거기에서 가져 오기를 수행 한 다음, 새 항목을 삭제하고 새로 작성하여 대량 가져 오기를 시도합니다. 첫 번째 실행에서는 SqlBulkCopy (SBC)가 ImportFromExcel을 채우고 다음 실행시에는 (DateTime.Now ...) 테이블에 복사 된 다음 드롭을 통해 비어서 다시 만듭니다. BTW, 명명 :

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

기분이 좋지 않습니다. 유혹을 불러 일으키는 것처럼 보이지만 정렬 할 수는 없지만 대신 다음과 같은 것을 원할 것입니다.

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

또는 더 나은 아직 :

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

그래서 와일드 카드 또는 루핑으로 모든 수입품을 분류하고 선택할 수있는 무언가가있을 것입니다.

그런 다음 reader.Read () 루프 내에서 서버에 쓰고 있습니다. 그것은 WriteToServer가 작동하는 방식이 아닙니다. 당신은 reader.Read ()를 사용하지 않을 것입니다 :

sbc.WriteToServer(reader);

저의 다음 메시지에서 간단한 스키마 읽기와 간단한 SBC 샘플을 임시 테이블로 가져올뿐만 아니라 대신 어떻게해야하는지 제안 할 것입니다.



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