Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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());
            }
        }
0
pointeman
Asked:
pointeman
  • 5
  • 4
1 Solution
 
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.
0
 
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.
0
 
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.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
pointemanAuthor Commented:
thx
0
 
slightwv (䄆 Netminder) Commented:
If you can post a complete test case with tables, data and code, it would help a lot.
0
 
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.
0
 
pointemanAuthor Commented:
Thx
0
 
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

0
 
pointemanAuthor Commented:
Thanks for all your hard work.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now