Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

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

0
csharp_learner
Asked:
csharp_learner
  • 11
  • 9
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now