none
Updating a table on Oracle DB considering changes on a grid in C# RRS feed

  • Question

  • I'm filling a Data Grid in C# (WinForms) via a System.Data.DataTableDataTable is filled from a DB table via ODP.

    I have a data navigator in Data Grid for updating, deleting and inserting rows.

    I want to use DataTable to commit all changes made in Data Grid to the database.

    I have to use OracleDataAdapter but I couldn't figure out how to achieve this.

    What kind of a CommandText should I use to achieve all three commands (updatedelete,insert)?

    The code below didn't work (maybe because CommandText I inserted is not appropriate)

    public void ExecuteNonQuery(string commandText, OracleCommand oracleCommand, CommandType commandType, DataTable dataTable)
    {
            oracleCommand.CommandText = commandText;
            oracleCommand.CommandType = commandType;
    
            try
            {
                oracleCommand.Connection = m_Connection;
                OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(oracleCommand);
                oracleDataAdapter.Update(dataTable);
            }
            catch (Exception)
            {
                LoggerTrace.Instance.Write(TraceEventType.Error, LoggerTrace.LoggerTraceSource.DatabaseManagerError, "Query could not be executed!");
                throw;
            }
    }

    Monday, May 27, 2013 9:56 AM

Answers

  • Hi Igaiga,

    Welcome to the MSDN Forum.

    DataAdapter can update the database, no matter it is oracle or not: http://msdn.microsoft.com/en-us/library/33y2221y(v=vs.110).aspx 

    The Update method of the DataAdapter is called to resolve changes from a DataSet back to the data source. The Update method, like the Fill method, takes as arguments an instance of a DataSet, and an optional DataTable object or DataTable name. The DataSet instance is the DataSet that contains the changes that have been made, and the DataTable identifies the table from which to retrieve the changes. If no DataTable is specified, the first DataTable in theDataSet is used.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 28, 2013 2:41 AM
    Moderator
  • Hi igaiga,

    Actrually, you can use the DataTable to replace above dataset. It is OK.

    Here is a code sample about updateCommand:

    private static void AdapterUpdate(string connectionString)
    {
    using (SqlConnection connection =
    new SqlConnection(connectionString))
    {
    SqlDataAdapter dataAdpater = new SqlDataAdapter(
    "SELECT CategoryID, CategoryName FROM Categories",
    connection);
    
    dataAdpater.UpdateCommand = new SqlCommand(
    "UPDATE Categories SET CategoryName = @CategoryName " +
    "WHERE CategoryID = @CategoryID", connection);
    
    dataAdpater.UpdateCommand.Parameters.Add(
    "@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");
    
    SqlParameter parameter = dataAdpater.UpdateCommand.Parameters.Add(
    "@CategoryID", SqlDbType.Int);
    parameter.SourceColumn = "CategoryID";
    parameter.SourceVersion = DataRowVersion.Original;
    
    DataTable categoryTable = new DataTable();
    dataAdpater.Fill(categoryTable);
    
    DataRow categoryRow = categoryTable.Rows[0];
    categoryRow["CategoryName"] = "New Beverages";
    
    dataAdpater.Update(categoryTable);
    
    Console.WriteLine("Rows after update.");
    foreach (DataRow row in categoryTable.Rows)
    {
    {
    Console.WriteLine("{0}: {1}", row[0], row[1]);
    }
    }
    }
    }

    It is at the bottom of this page: http://msdn.microsoft.com/en-us/library/33y2221y(v=vs.110).aspx  

    You can do the similar things for delete, insert.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 29, 2013 12:08 AM
    Moderator

All replies

  • Hi Igaiga,

    Welcome to the MSDN Forum.

    DataAdapter can update the database, no matter it is oracle or not: http://msdn.microsoft.com/en-us/library/33y2221y(v=vs.110).aspx 

    The Update method of the DataAdapter is called to resolve changes from a DataSet back to the data source. The Update method, like the Fill method, takes as arguments an instance of a DataSet, and an optional DataTable object or DataTable name. The DataSet instance is the DataSet that contains the changes that have been made, and the DataTable identifies the table from which to retrieve the changes. If no DataTable is specified, the first DataTable in theDataSet is used.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 28, 2013 2:41 AM
    Moderator
  • Hi,

    Thank you for your valuable information.

    I'm using DataTable in my project because all the queries return just one table(That's my assumption)

    But as i understand tracking the changes is handled by DataSet so i have to use DataSet as data holder and get the first and only DataTable in it for processing.

    Am i right?

    And what sould i set as CommandText in OracleDataAdapter? Do i have to write the plain query? if yes how will i handle update, delete and insert in one call for OracleDataAdapter.Update() ?

    Regards.


    • Edited by igaiga Tuesday, May 28, 2013 7:27 AM one more question
    Tuesday, May 28, 2013 6:57 AM
  • Hi igaiga,

    Actrually, you can use the DataTable to replace above dataset. It is OK.

    Here is a code sample about updateCommand:

    private static void AdapterUpdate(string connectionString)
    {
    using (SqlConnection connection =
    new SqlConnection(connectionString))
    {
    SqlDataAdapter dataAdpater = new SqlDataAdapter(
    "SELECT CategoryID, CategoryName FROM Categories",
    connection);
    
    dataAdpater.UpdateCommand = new SqlCommand(
    "UPDATE Categories SET CategoryName = @CategoryName " +
    "WHERE CategoryID = @CategoryID", connection);
    
    dataAdpater.UpdateCommand.Parameters.Add(
    "@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");
    
    SqlParameter parameter = dataAdpater.UpdateCommand.Parameters.Add(
    "@CategoryID", SqlDbType.Int);
    parameter.SourceColumn = "CategoryID";
    parameter.SourceVersion = DataRowVersion.Original;
    
    DataTable categoryTable = new DataTable();
    dataAdpater.Fill(categoryTable);
    
    DataRow categoryRow = categoryTable.Rows[0];
    categoryRow["CategoryName"] = "New Beverages";
    
    dataAdpater.Update(categoryTable);
    
    Console.WriteLine("Rows after update.");
    foreach (DataRow row in categoryTable.Rows)
    {
    {
    Console.WriteLine("{0}: {1}", row[0], row[1]);
    }
    }
    }
    }

    It is at the bottom of this page: http://msdn.microsoft.com/en-us/library/33y2221y(v=vs.110).aspx  

    You can do the similar things for delete, insert.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 29, 2013 12:08 AM
    Moderator