Solved

Oracle Update Foreach DataTable Row?

Posted on 2012-03-20
9
356 Views
Last Modified: 2012-03-23
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());
            }
        }
0
Comment
Question by:pointeman
  • 5
  • 4
9 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37745240
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.
0
 

Author Comment

by:pointeman
ID: 37745405
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37745414
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.
0
 

Author Comment

by:pointeman
ID: 37745428
thx
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37745433
If you can post a complete test case with tables, data and code, it would help a lot.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37750015
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.
0
 

Author Comment

by:pointeman
ID: 37751728
Thx
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37752096
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

0
 

Author Closing Comment

by:pointeman
ID: 37759357
Thanks for all your hard work.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now