CODEWITHSUNDEEP
c#oracle-databasedatagridview
Sailormoon
Sailormoon

Reputation: 267

DategridView insert into another oracle Table C#

I have a Datagridview of 46 columns consists of Date, Int and String Type. I want to insert the values to another oracle table. 

private void loopGrid()
    {
        foreach (DataGridViewRow datarow in this.dataGridView1.Rows)
        {
            writeRecord(datarow);
        }
    }

    private void writeRecord(DataGridViewRow datarow)
    {

        string sqlqry = "insert into Table  (Date,Number) values(:Date,:Number)";

        OracleCommand cmd = new OracleCommand(sqlqry, conn);
        cmd.Parameters.Add(new OracleParameter(":Date", datarow["DATE"]))
        cmd.Parameters.Add(new OracleParameter(":Number", datarow["Number"]));
        cmd.CommandText = sqlqry;
        cmd.ExecuteNonQuery();
    }

I don't quite understand what I did wrong about passing the parameters. I'm pretty new to C#, what's the right way to pass the Datatype and value to the other oracle table?

Upvotes: 1

Views: 839

Answers (2)

Hambone
Hambone

Reputation: 16377

Depending on the Oracle driver you are using, there may be an overload for the .Add method that accepts the value as the second parameter, or there may be an .AddWithValue method that does it explicitly. I think for ODP.net you have it right as just .Add.

Either way, these will derive the datatype from the datatype of the value.

Regarding your parameters, you need to omit the colon : character when invoking the parameters. I know in SQL Server, Sybase, SQLite and others you include them (a @ in those cases), but in Oracle you leave them out during the parameter declaration. Interestingly, I think PostgreSQL accepts either methodology.

As such, I think this will work:

private void writeRecord(DataGridViewRow datarow)
{

    string sqlqry = "insert into Table (Date,Number) values(:Date,:Number)";

    OracleCommand cmd = new OracleCommand(sqlqry, conn);
    cmd.Parameters.Add(new OracleParameter("Date", datarow["DATE"]))
    cmd.Parameters.Add(new OracleParameter("Number", datarow["Number"]));
    // cmd.CommandText = sqlqry;  -- not necessary, handled in constructor
    cmd.ExecuteNonQuery();
}

If not, maybe try explicit conversions with either the type or value:

Yuck:

cmd.Parameters.Add(new OracleParameter("Date", Convert.ToDateTime(datarow["DATE"])))
cmd.Parameters.Add(new OracleParameter("Number", Convert.ToDecimal(datarow["Number"])));

Better:

cmd.Parameters.Add(new OracleParameter("DATE", OracleDbType.Date,
    datarow["DATE"], ParameterDirection.Input);
cmd.Parameters.Add(new OracleParameter("Number", OracleDbType.Decimal,
    datarow["Number"], ParameterDirection.Input));

And if worse comes to worse, the bulletproof (although verbose) way to do it would be:

cmd.Parameters.Add(new OracleParameter("DATE", OracleDbType.Date));
cmd.Parameters.Add(new OracleParameter("Number", OracleDbType.Decimal));
cmd.Parameters[0].Value = datarow["DATE"];
cmd.Parameters[1].Value = datarow["Number"];

As a footnote, this last way is actually preferred if you have multiple rows to insert, as you create the parameters once and modify the values and execute the insert multiple times:

cmd.Parameters.Add(new OracleParameter("DATE", OracleDbType.Date));
cmd.Parameters.Add(new OracleParameter("Number", OracleDbType.Decimal));

foreach (DataRow datarow in datarows)
{
    cmd.Parameters[0].Value = datarow["DATE"];
    cmd.Parameters[1].Value = datarow["Number"];

    cmd.ExecuteNonQuery();
}

-- EDIT --

Based on your feedback and my improved proofreading skills, this is what I'd recommend. I added a transaction in there for good measure:

string sqlqry = "insert into Table  (Date,Number) values(:Date,:Number)";

OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
OracleCommand cmd = new OracleCommand(sqlqry, conn, trans);
cmd.Parameters.Add(new OracleParameter("DATE", OracleDbType.Date));
cmd.Parameters.Add(new OracleParameter("Number", OracleDbType.Decimal));

foreach (DataGridViewRow datarow in this.dataGridView1.Rows)
{
    cmd.Parameters[0].Value = datarow.Cells["DATE"].Value;
    cmd.Parameters[1].Value = datarow.Cells["Number"].Value;

    cmd.ExecuteNonQuery();
}

trans.Commit();

For what it's worth, you also need some exception handling.

Upvotes: 1

user8128167
user8128167

Reputation: 7676

You would probably want to use:

    cmd.Parameters.Add(new OracleParameter(DbType.Date, datarow["DATE"]))
    cmd.Parameters.Add(new OracleParameter(DbType.Double, datarow["Number"]));

Please see https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.dbtype(v=vs.110).aspx

Upvotes: 0

Related Questions