0

Need on the best way to going about programming this in c#. I'm trying to do an if record exists update statement else insert statement:

OracleConnection con = new OracleConnection(strConnection);
        con.Open();
        OracleCommand check_RID = new OracleCommand("SELECT COUNT(*) FROM CONTRACT_INFO WHERE (rid = @rid)", con);
        check_RID.Parameters.Add("@rid", labelRID.Text);
        OracleDataReader reader = check_RID.ExecuteReader();

        if (reader.HasRows)
        {
            OracleConnection conn = new OracleConnection();  // C#
            conn.ConnectionString = strConnection;
            conn.Open();

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "UPDATE...

            cmd.ExecuteNonQuery();

            conn.Close();
            Response.Redirect("primecontractor.aspx?Id=" + labelRID.Text);
        }
        else
        {

            OracleConnection conn = new OracleConnection();  // C#
            conn.ConnectionString = strConnection;
            conn.Open();

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "INSERT....
          ......

            cmd.ExecuteNonQuery();

            conn.Close();

Is there a better way to program this and if so how?

4
Contributors
3
Replies
34
Views
10 Months
Discussion Span
Last Post by lolafuertes
0

First, the if (reader.HasRows) will always return true, because even no record exist for OracleCommand("SELECT COUNT(*) FROM CONTRACT_INFO WHERE (rid = @rid)", con); the Count(*) will return 0;
At this point, if the returned value is 0, then is OK to insert, if the returned value is >0 will be OK to update.
I would suggest, that you write something like (not tested):

int returnedNumber = 0;
if (reader.HasRows)
{
    returnedNumber = reader.GetInt32(0);
}
reader.close();
if (returnedNumber == 0)
{
    // do the insert
}
else
{
    // do the update
}

This way, in first place, you store the response and close the reader, freeing the connection.
On second place, you will not need an additional connection to the database server, so will need less resources in the application and in the server.

Hope this helps.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.