none
How to insert dataset into Oracle database directly RRS feed

  • Question

  • I have one Excel sheet.
    I am loading data from excel sheet to a dataset.
    Now i want to insert this dataset to oracle database table directly.

    Can you suggest me?

    My code goes here-----

    Excel sheet name:Book1
    Oracle Table Name:Import1

    OracleConnection destination=

    new OracleConnection();
    string lsConnectionString = ConfigurationSettings.AppSettings["CCMdsn"];
    destination.ConnectionString=lsConnectionString;
    if(destination.State != ConnectionState.Open)
    {
    destination.Open();
    }
    OracleTransaction trans=
    null
    OracleCommand command1 =
    new OracleCommand("Select * from import1", destination);
    OracleDataAdapter adapter =n
    ew OracleDataAdapter(command1);
    DataSet dataSet =
    new DataSet(); 
    int b =adapter.Fill(dataSet, "import1");
    string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\loader\Book1.xls; Extended Properties=""Excel 8.0;HDR=YES""";
    OleDbConnection source =new OleDbConnection(excelConnectionString);
    source.Open();
    OleDbCommand command =new OleDbCommand("SELECT * FROM [Book1$]", source);
    oleDbDataAdapter dataAdapter =
    new OleDbDataAdapter(command);
    int a =dataAdapter.Fill(dataSet, "import1"); 
    OracleCommandBuilder builder =new OracleCommandBuilder(adapter);
    trans = destination.BeginTransaction();
    command1.Transaction = trans;
    adapter.InsertCommand = builder.GetInsertCommand();
    int check = adapter.Update(dataSet, "import1");
    trans.Commit();
    if(destination.State == ConnectionState.Open)
    destination.Close();
    source.Close();


    It is executing with out any error.But database table is not updating with any changes.

    Thursday, November 12, 2009 11:35 AM

Answers

  • The problem is that your DataTable has no changes. The .Fill() methods leaves the DataSet in an unchanged state. Since you're filling the exact same DataSet/Table in both of your .Fill() calls, there is  nothing for the .Update() method to update. IOW, each row in the DataTable must have a RowState of Added in order for the .Update() to insert the data.

    What you probably want to do is fill a different DataTable from Excel and then use ImportRow on each DataRow to get it into your Import1 DataTable. So, something like this:
    dataAdapter.Fill(dataSet, "Excel");
    foreach (DataRow rowExcel in dataSet.Tables["Excel"])
    {
        rowExcel.SetAdded(); // sets its RowState to "Added"
        dataSet.Tables["import1"].ImportRow(rowExcel);
    }

    If you don't actually have data in the first table (dataSet.Tables["import1"]), then you can skip the whole ImportRow stuff and simply use the .SetAdded() method for each row.

    Does this help?
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Yichun_Feng Wednesday, November 18, 2009 2:19 AM
    Friday, November 13, 2009 12:57 AM

All replies

  • I have one Excel sheet.
    I am loading data from excel sheet to a dataset.
    Now i want to insert this dataset to oracle database table directly.

    Can you suggest me?

    My code goes here-----

    Excel sheet name:Book1
    Oracle Table Name:Import1

    OracleConnection destination=

    new OracleConnection();
    string lsConnectionString = ConfigurationSettings.AppSettings["CCMdsn"];
    destination.ConnectionString=lsConnectionString;
    if(destination.State != ConnectionState.Open)
    {
    destination.Open();
    }
    OracleTransaction trans=
    null
    OracleCommand command1 =
    new OracleCommand("Select * from import1", destination);
    OracleDataAdapter adapter =n
    ew OracleDataAdapter(command1);
    DataSet dataSet =
    new DataSet(); 
    int b =adapter.Fill(dataSet, "import1");
    string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\loader\Book1.xls; Extended Properties=""Excel 8.0;HDR=YES""";
    OleDbConnection source =new OleDbConnection(excelConnectionString);
    source.Open();
    OleDbCommand command =new OleDbCommand("SELECT * FROM [Book1$]", source);
    oleDbDataAdapter dataAdapter =
    new OleDbDataAdapter(command);
    int a =dataAdapter.Fill(dataSet, "import1"); 
    OracleCommandBuilder builder =new OracleCommandBuilder(adapter);
    trans = destination.BeginTransaction();
    command1.Transaction = trans;
    adapter.InsertCommand = builder.GetInsertCommand();
    int check = adapter.Update(dataSet, "import1");
    trans.Commit();
    if(destination.State == ConnectionState.Open)
    destination.Close();
    source.Close();


    It is executing with out any error.But database table is not updating with any changes.


    Ramesh Sabbani
    • Merged by Yichun_Feng Tuesday, November 17, 2009 8:34 AM duplicate
    Thursday, November 12, 2009 1:05 PM
  • Hello,

    Read this my article on here,

    http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/aaff7509-27c0-4c46-a62b-10eeb8feec54

    Here I have explained how to insert data in SQL Server from DataSet in that code you have to just change the SQL related classes like SQLAdapter replaced by OracleAdapter etc....

    Have look and hope It wil work.


    Tejas Mer
    Thursday, November 12, 2009 5:55 PM
  • The problem is that your DataTable has no changes. The .Fill() methods leaves the DataSet in an unchanged state. Since you're filling the exact same DataSet/Table in both of your .Fill() calls, there is  nothing for the .Update() method to update. IOW, each row in the DataTable must have a RowState of Added in order for the .Update() to insert the data.

    What you probably want to do is fill a different DataTable from Excel and then use ImportRow on each DataRow to get it into your Import1 DataTable. So, something like this:
    dataAdapter.Fill(dataSet, "Excel");
    foreach (DataRow rowExcel in dataSet.Tables["Excel"])
    {
        rowExcel.SetAdded(); // sets its RowState to "Added"
        dataSet.Tables["import1"].ImportRow(rowExcel);
    }

    If you don't actually have data in the first table (dataSet.Tables["import1"]), then you can skip the whole ImportRow stuff and simply use the .SetAdded() method for each row.

    Does this help?
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Yichun_Feng Wednesday, November 18, 2009 2:19 AM
    Friday, November 13, 2009 12:57 AM
  • I didn't realize that you had double-posted this question ... I answered you in the other post.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Friday, November 13, 2009 1:27 AM
  • Hii Bonnie,

    * instead of looping whole dataset for state . we can set the the dataAdapter's one property as below.
    * dataAdapter.AcceptChangesDuringFill = false;
    * This will tells the adapter that whatever rows added into dataset, its rowstate as default added.
    * We can use dataAdapter.AcceptChangesDuringUpdate = false; as an when required according to scenario.



    • Proposed as answer by BonnieBMVP Tuesday, November 24, 2009 4:31 PM
    Tuesday, November 24, 2009 8:52 AM
  • I had forgotten about those DataAdapter properties (or perhaps I never knew because I didn't need them) ... but anyway, I think you probably have a better answer than I did. Thanks for your input!
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, November 24, 2009 4:31 PM