Solved

Oracle Update Foreach DataTable Row?

Posted on 2012-03-20
9
358 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:pointeman
ID: 37745428
thx
0
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

803 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