Solved

Oracle Update Foreach DataTable Row?

Posted on 2012-03-20
9
355 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)
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
thx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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)
Comment Utility
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
Comment Utility
Thx
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Comment Utility
Thanks for all your hard work.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Log4Net custom Appender not being fired. 5 41
Oracle TEXT search question 9 26
Setting location of a form 4 21
sql query 5 51
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

772 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

12 Experts available now in Live!

Get 1:1 Help Now