Update Oracle Database with DatagridView

Hi,
Currently I'm working on Visual C#2008 express edition and this is the problem I faced.

I populated the DataGridView table with excel sheet file...no problems displaying that.
But I'm stuck as I want to update the table to a Oracle database.

Coding wise I've reached the point till connecting to the Oracle database and at lost of what to do next.

I've came to know this Data.Adapter.Update function which i Hope can be of use as I'm trying to avoid a for loop update for every row...

The part 1 of the code I've attached is the displaying of the DataGridView(which works) and the 2nd part is the part i'm trying to update to Oracle database.
// 1st part
public void displayexcel_Load(object sender, EventArgs e)
        {
            try
            {
                string connString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + gotdirectory + @";Extended Properties=""Excel 8.0;HDR=YES;""";
                OleDbConnection oledbConn = new OleDbConnection(connString);
                oledbConn.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [A2:O36]", oledbConn);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;
                oleda.Fill(ds);
                userTable = ds.Tables[0];
                excelGridView.DataSource = ds.Tables[0].DefaultView;
                excelGridView.ReadOnly = true;
                oledbConn.Close();
                filename.Text = gotdirectory;
                trgfilename.Text = System.IO.Path.GetFileNameWithoutExtension(gotdirectory);
                recordsnumBox.Text = (excelGridView.Rows.Count - 1).ToString();
            }
            catch (Exception displayerror)
            {
                MessageBox.Show(displayerror.ToString());
            }
        }
 
// 2nd part
// Connection to Oracle DB   
 int connectionCounter = 0;   
                bool boolUpdateData = true;   
                while (boolUpdateData && connectionCounter < 3)   
                {   
                    try   
                    {   
                        localConnection = new OracleConnection(localFunction.localConnectionString);   
                        localConnection.Open();   
  
                        boolUpdateData = false;   
                    }   
                    catch (Exception localException)   
                    {   
                        localFunction.WriteEventLog(localException.ToString(), "OPCAlarmConfig");   
                    }   
                    connectionCounter++;   
                }   
  
                localSQL = @"SELECT * FROM SYS_LINE";   
                localFunction.ExecuteOracleNonQuery(localSQL, localConnection, "OPCAlarmConfig");   
  
// Update Oracle DB   
         try {   
            OracleCommandBuilder cb = new OracleCommandBuilder(oleda);   
            userTable = ds.Tables["SYS_LINE"];   
            oleda.Fill(ds,"SYS_LINE");   
            excelGridView.DataSource = ds.Tables[0];   
            oleda.Update(ds, "SYS_LINE");   
              }   
                catch (Exception imptoracle)   
                { MessageBox.Show(imptoracle.ToString()); }   
            }

Open in new window

csharp_learnerAsked:
Who is Participating?
 
Bob LearnedCommented:
I would like to make a suggestion.  You should use an OleDbDataAdapter locally in displayexcel_Load to get the data from the Excel worksheet, and then create the OracleDataAdapter in the imtoracleBtn_Click event handler, instead of using module-level variables.
0
 
csharp_learnerAuthor Commented:
Do let me know which part of my question that I might confuse.
As i'm truely in dire need to slove this.
0
 
Bob LearnedCommented:
It sounds like you don't have any changes to write to the Oracle database.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
csharp_learnerAuthor Commented:
Yes baiscally I just want to Read from the Excel sheet and update to my Oracle table.
The columns and rows are the same it's just that the values are changed inside excel and I want to import into Oracle database.
0
 
Bob LearnedCommented:
What I would do is to create a new DataTable, import the rows from the Excel DataTable, and then call the DataAdapter.Update method, since the row states will be added.
0
 
csharp_learnerAuthor Commented:
TheLearnedOne can you show me how can I accomplish this with codes?
As i'm still new to the programming language and I'm not very good at converting theories into code.
0
 
Bob LearnedCommented:
1) Create a new DataTable

DataTable dtOracle = new DataTable();

2) Import rows

foreach (DataRow dr in dtExcel.Rows)
   dtOracle.ImportRow(dr);

3) Update database:

adapter.Update(dtOracle);
0
 
csharp_learnerAuthor Commented:
Thanks TheLearnedOne for your help thus far...

I took your advice and ran the code with the follwing codes, but this exception error prompt up.
"Unable to cast object of type 'System.Window.Forms.DataGridViewRow' to type 'System.Data.DataRow'."
        private void imtoracleBtn_Click(object sender, EventArgs e)
        {
                int connectionCounter = 0;
                bool boolUpdateData = true;
                while (boolUpdateData && connectionCounter < 3)
                {
                    try
                    {
                        localConnection = new OracleConnection(localFunction.localConnectionString);
                        localConnection.Open();
                        boolUpdateData = false;
                    }
                    catch (Exception localException)
                    {
                        localFunction.WriteEventLog(localException.ToString(), "OPCAlarmConfig");
                    }
                    connectionCounter++;
                }
// Point to the database I want to update.
                localSQL = "SELECT * FROM SYS_LINE";
                localFunction.ExecuteOracleNonQuery(localSQL, localConnection, "OPCAlarmConfig");
 
// Update Oracledb
         try 
         {
             DataTable dtOracle = new DataTable();
             foreach (DataRow dr in excelGridView.Rows)
             dtOracle.ImportRow(dr);
             oleda.Update(dtOracle);
         }
                catch (Exception imptoracle)
                { MessageBox.Show(imptoracle.ToString());}
}

Open in new window

0
 
Bob LearnedCommented:
That was not meant to take data from the DataGridView, but the underlying DataSet/DataTable.

foreach (DataRow dr in ds.Tables[0].Rows)
{
  dtOracle.ImportRow(dr);
}
0
 
csharp_learnerAuthor Commented:
Thanks for the prompt reply.
I got another exception error when i tried the following code.
the error line was at: oleda.Update(dtOracle);
"Object reference not set to an instance of an object."
try 
         {
            DataTable dtOracle = new DataTable();
             foreach (DataRow dr in ds.Tables[0].Rows)
             {
                 dtOracle.ImportRow(dr);
             }
             oleda.Update(dtOracle);
         }

Open in new window

0
 
Bob LearnedCommented:
I would imagine that it is the 'oleda' variable, but I can't see the "bigger picture".
0
 
csharp_learnerAuthor Commented:
Can you advice how can i help to let you see the big picture.
As solving this problem would really remove the torn in me...
0
 
Bob LearnedCommented:
Is this code all in one, big, monster method?
0
 
csharp_learnerAuthor Commented:
This is the entire code in this form.
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Net;
using Oracle.DataAccess.Client;
using SULib;
using System.Drawing.Drawing2D;
using System.Data.OleDb;
using Oracle.DataAccess.Types;
 
namespace OPCAlarmConfig
{
    public partial class displayexcel : Form
    {
        private OracleConnection localConnection = null;
        private GeneralFunction localFunction = new GeneralFunction();
        public DataSet ds = new DataSet();
        public OracleDataAdapter oleda;
        private string localSQL = "";
        public DataTable userTable;
        public string gotdirectory;
        public string gottable;
 
 
        public displayexcel(string initialValue)
        {
            InitializeComponent();
            ValueFromParent = initialValue;
        }
 
        public string ValueFromParent
        {
            set
            {
                gotdirectory = value;
            }
        }
 
        public void displayexcel_Load(object sender, EventArgs e)
        {
            try
            {
                string connString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + gotdirectory + @";Extended Properties=""Excel 8.0;HDR=YES;""";
                OleDbConnection oledbConn = new OleDbConnection(connString);
                oledbConn.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [A2:O36]", oledbConn);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;
                oleda.Fill(ds);
                userTable = ds.Tables[0];
                excelGridView.DataSource = ds.Tables[0].DefaultView;
                excelGridView.ReadOnly = true;
                oledbConn.Close();
                filename.Text = gotdirectory;
                trgfilename.Text = System.IO.Path.GetFileNameWithoutExtension(gotdirectory);
                recordsnumBox.Text = (excelGridView.Rows.Count - 1).ToString();
            }
            catch (Exception displayerror)
            {
                MessageBox.Show(displayerror.ToString());
            }
        }
 
        private void closeBtn_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.Cancel;
            this.Close();
        }
 
        private void imtoracleBtn_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Confirm Import this table to Oracle?", "Import to Oracle", MessageBoxButtons.OKCancel) == DialogResult.OK)
            {
                int connectionCounter = 0;
                bool boolUpdateData = true;
                while (boolUpdateData && connectionCounter < 3)
                {
                    try
                    {
                        localConnection = new OracleConnection(localFunction.localConnectionString);
                        localConnection.Open();
                        boolUpdateData = false;
                    }
                    catch (Exception localException)
                    {
                        localFunction.WriteEventLog(localException.ToString(), "OPCAlarmConfig");
                    }
                    connectionCounter++;
                }
 
                localSQL = "SELECT * FROM SYS_LINE";
                localFunction.ExecuteOracleNonQuery(localSQL, localConnection, "OPCAlarmConfig");
 
         try 
         {
             DataTable dtOracle = new DataTable();
             foreach (DataRow dr in ds.Tables[0].Rows)
             {
                 dtOracle.ImportRow(dr);
             }
             oleda.Update(dtOracle);
            MessageBox.Show("Imported to : " + System.IO.Path.GetFileNameWithoutExtension(gotdirectory), "Imported");
         }
                catch (Exception imptoracle)
                { MessageBox.Show(imptoracle.ToString());}
            }
            else
            { MessageBox.Show("Import Canceled");}
        }
    }
}

Open in new window

0
 
csharp_learnerAuthor Commented:
Finally the code ran smoothly...Thanks again.
But somehow the database did not react to the changed values in displayed DataGridView table
0
 
Bob LearnedCommented:
What went wrong?  Did it not write anything?  
0
 
csharp_learnerAuthor Commented:
the value in the Old Oracle database is
S01 and the value in the DataGridView is S02.
But when i open the Oracle DB after i ran my application the value is still @ S01.
0
 
Bob LearnedCommented:
Huh?  Are you saying that it didn't write anything, or that it wrote data incorrectly?  Is this a bulk insert, or an update?
0
 
csharp_learnerAuthor Commented:
Sorry for my late reply...was away.
It did'n write anything @ all to the database.
0
 
csharp_learnerAuthor Commented:
It seem only fair to you as I've taken so much of u're time and you manage to help me compile the code successfully.
Thanks alot TheLearnedOne.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.