Oracle Update Foreach DataTable Row?

This query takes a very long time to execute and I'm new to Oracle, Help!

Note: the data is: ID = 1-9, Active = T or F

        public void Update(DataTable dt)
        {
            try
            {
                OracleConnection cn = DataAccess.OpenConnection();
                OracleCommand cmd = newConn.CreateCommand();
                newConn.Open();

                foreach (DataRow dr in dt.Rows)
                {                    
                    cmd.CommandText = "update Table1 set Active = '" + dr[1] + "' where id = " + dr[0];
                    cmd.ExecuteNonQuery();                
                }
                DataAccess.CloseConnection(cn);
            }
            catch (Exception ex)
            {
                System.Web.HttpContext.Current.Response.Write(ex.ToString());
            }
        }
pointemanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You can save a little time using bind variables.  It will also save you from possible sql-injection.

I also suggest if you aren't already using it, use Oracle's ODP.Net data provider.


What is the source for the datatable, a gridview, datagrid, ???
How many rows?
How much is a 'long time'?
Where do you want to get?  for example if it takes 10 minutes now and you want 1 second, probably not possible.
pointemanAuthor Commented:
This post is the beginning of my task to save gridview checkbox values back to the database. Here is the first post: http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_27638379.html

The Gridview has four colums: ID, FirstName, LastName, checkbox with approx 50 rows.

Unfortunately the datbase table accepts only T or F instead of the typical bool. So I convert and save the checkboxes to a dataset converted to T or F.

for(int i=0;i<GridView1.Rows.Count;i++)
            {
                GridViewRow row = GridView1.Rows[i];
                CheckBox ckbx = (CheckBox)row.FindControl("checkbox1");

                if (cbSelectedHeader.Checked == true)
                    ckbx.Checked = true;
                else
                    ckbx.Checked = false;

                dr = dt.NewRow();
                dr[0] = GridView1.DataKeys[i].Value.ToString();
                dr[1] = checkBox1.Checked ? "T" : "F";
                dt.Rows.Add(dr);
            }

Open in new window


So I'm not simply using a dataAdapter.Fill then dataAdapter.Update, that would be too easy. Thus the problems of saving a different datatable back to the database. I'm thinking there must be a better way than the way I'm trying to do it with a foreach like shown which actually times-out.
slightwv (䄆 Netminder) Commented:
50 rows in a loop times out?  Likely not a loop issue.  The problem would likely be elsewhere.

I have not done much with grid views but have done a lot with data grids.  I'll take a look at this when I get back to work tomorrow.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pointemanAuthor Commented:
thx
slightwv (䄆 Netminder) Commented:
If you can post a complete test case with tables, data and code, it would help a lot.
slightwv (䄆 Netminder) Commented:
I apologize for not posting today.  I worked on this this morning and have it 97% complete.  I got tied up this afternoon.  I'll finish it first thing tomorrow morning and post it.
pointemanAuthor Commented:
Thx
slightwv (䄆 Netminder) Commented:
Based on your other question you want to call OnCheckedChanged to update the underlying table on every check.

Here is what I came up with.

Table setup:
drop table tab1 purge;
create table tab1( id number, first_name char(1), last_name char(1), ischecked char(1));

insert into tab1 values(1,'a','b','T');
insert into tab1 values(2,'c','d','F');
insert into tab1 values(3,'e','f','T');
commit;

Open in new window



Web page using ODP.Net:
<%@ import namespace = "System" %>
<%@ import namespace = "System.Data" %>
<%@ import namespace = "Oracle.DataAccess.Client" %>
<%@ import namespace = "Oracle.DataAccess.Types" %>

<html>
<title>Gridview Sample</title>

<body>

<script language="c#" runat="server">

	public void Page_Load(object sender, EventArgs e)
	{

		if(!IsPostBack)
		{

			OracleConnection con = new OracleConnection("User Id=username;Password=somepassword;Data Source=somedatabase;");
			OracleCommand cmd = new OracleCommand();
			cmd.Connection = con;
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = "select id, first_name, last_name, ischecked from tab1 ";

			try {
				con.Open();
				GenericGridView.DataSource = cmd.ExecuteReader();
				GenericGridView.DataBind();

			} catch (Exception ex) {
				Response.Write("Error: " + ex.Message);

			} finally {
				con.Close();
				cmd.Dispose();
			}
		}

	}


	public void HasBeenChanged(Object sender, EventArgs e) 
	{ 

		CheckBox chkStatus = (CheckBox)sender;
    	GridViewRow row = (GridViewRow)chkStatus.NamingContainer;

   
		OracleConnection con = new OracleConnection("User Id=username;Password=somepassword;Data Source=somedatabase;");

		OracleCommand cmd = new OracleCommand();
		cmd.Connection = con;
		cmd.CommandType = CommandType.Text;
		cmd.CommandText = " update tab1 set ischecked = :ischecked where id = :id ";

		OracleParameter param1 = cmd.Parameters.Add("ischecked", OracleDbType.Varchar2, 1, chkStatus.Checked ? "T" : "F" , ParameterDirection.Input);
		OracleParameter param2 = cmd.Parameters.Add("id", OracleDbType.Decimal, 1, row.Cells[0].Text, ParameterDirection.Input);

		try {
			con.Open();
			cmd.ExecuteNonQuery();

		} catch (Exception ex) {
			Response.Write("Error: " + ex.Message);

		} finally {
			cmd.Dispose();
			con.Close();
			con.Dispose();
		}






	}

</script>

<form runat="server">

<asp:GridView id="GenericGridView" AutoGenerateColumns="False" runat="server">
	<Columns>
		<asp:BoundField DataField="id" />
		<asp:BoundField DataField="first_name" />
		<asp:BoundField DataField="last_name" />
		<asp:TemplateField>
			<ItemTemplate>
				<asp:checkbox AutoPostBack="True" OnCheckedChanged="HasBeenChanged" checked=<%# "T" == (String)Eval("ischecked") %> runat="server"/>
			</ItemTemplate>
		</asp:TemplateField>
	</Columns>
</asp:GridView>

</form>
</body>
</html>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pointemanAuthor Commented:
Thanks for all your hard work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.