Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

batch updates to the database thru dataset

Posted on 2006-04-06
16
Medium Priority
?
520 Views
Last Modified: 2010-05-18
Hi,

My requirement is I am fetching around 4000 records from the database table into dataset in a ASP.net application( lang:vb.net), I want to iterate thru each record do some validations checks (manipulate the data) and update the data back(using stored proc) to the same table. Please suggest me a better way of doing this .

Can this be done using dataset batch update method, if so how ?
Otherwise what is the better approah.

Thanks
Venkat
0
Comment
Question by:tvenkat9
  • 5
  • 5
  • 3
13 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16397973
If possible, do the data validation in a stored procedure (if no user intervention is needed) directly.
0
 

Author Comment

by:tvenkat9
ID: 16397983
Hi,

User intervention is not needed,on click of a button this has to be done,
but if i need to do it in stored procedure for iterating thru records i have to write a cursor which are not advisable for large amount of data?

Thanks
Venkat
0
 
LVL 12

Expert Comment

by:deanvanrooyen
ID: 16398228
hi,

if you load data into a dataset using sqladapter and then make changes to data in a table of the dataset or add rows etc you can update the datasource by using the sqladapter.update mehod - this updates your datasource for you eg table in a db

search msdn or google on how to use the sqladapter update method, this should help - this will stop you from having to iterate through all the records and firing a stored procedure on each update....
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16398415
depending on the data validation, no cursor is needed.
you might want to tell us what kind of validation is needed, possibly we can suggest some single query for this
0
 

Author Comment

by:tvenkat9
ID: 16401709
Hi,

I don't think it is possible to write a single query , because i need to iterate thru each record and  for each field I need to check the values against a look up table and if there is no value then I need to insert a value and in addition to that for each record  I need to get the matching records from the child table and check the totals and some more validations and then I have write a exeception record into the another table.I thought writing a single query for this will be hard.

I am working code this way
-----
Dim ds As DataSet = DGS.OTR.FAS.Business.WorkOrdersFactory.SelectTempWorkOrders

                    Dim row As DataRow
                    For Each row In ds.Tables(0).Rows
                        If row("WO_dept_equip_dept") = "30101" Then
                            Call ValidateCmlWorkOrder(row)--I will handle all validations here
                            WO_exp_technicn_vendor = "VENDOR"
                            WO_acct_acct_code = row("WO_acct_acct_code")
                            WO_error_flag = row("WO_error_flag")

                            DGS.OTR.FAS.Business.WorkOrdersFactory.UpdateTest(WO_acct_acct_code, WO_error_flag, WO_exp_technicn_vendor)

                        Else
                            Call ValidateInHouseWorkOrder(row)
                            WO_exp_technicn_vendor = "INHOUSE"
                            WO_acct_acct_code = row("WO_acct_acct_code")
                            WO_error_flag = row("WO_error_flag")
                            DGS.OTR.FAS.Business.WorkOrdersFactory.UpdateTest(WO_acct_acct_code, WO_error_flag, WO_exp_technicn_vendor)

                        End If
------------------------
But this way i need to make a trip databse for each record.
If there is a better way please suggest me.

Thanks
Venkat

0
 

Author Comment

by:tvenkat9
ID: 16403878
Hi,

Any one can suggest me better approach to do this.

Thanks
Venkat
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16404247
This looks very neat so far, but as i processes row by row, depending on what the validation functions are doing, you might be able to do in 1 or 2 single updates...
0
 

Author Comment

by:tvenkat9
ID: 16404526
Hi,

Right now the way I have written code will call the update method once for each record,
wether it is valid or not still i need to update the table.
So for 5000 records it has to call the update method(I am updating the table by calling stored proc) 5000 times, it is a intranet application,
my concern is regarding the performance.

As you suggested if I have to do it in one stored procedure then I have to write cursor ,
I can't write sql query .

Can we use datset batch update method for updating 5000 records at a time?
Or else which is better method.

Thanks
Venkat
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16404595
the .net code you have to update the table would look like this in SQL:

UPDATE WorkOrdersFactory
  SET WO_exp_technicn_vendor = CASE WHEN s.WO_dept_equip_dept = 30101 THEN 'VENDOR' ELSE 'INHOUSE' END
FROM WorkOrdersFactory s
WHERE <<condition for temp work_orders>>

I guess that the rows are coming from a table called WorkOrdersFactory, and that you update the same table actually.
I guess the column names based on the variable names.
If the fields  WO_acct_acct_code and WO_error_flag are also updated to a potentially different value (coming from the validation function(s), this will make it a bit more complicated.

NOTE: if you are already using SQL Server 2005, you can write .NET code as stored functions...
         
0
 

Author Comment

by:tvenkat9
ID: 16404702
Hi,

I have nearly 15 fields in the record,depending upon the WO_dept_equip_dept field value the validations are different
I get the values into dataset , I iterate thru each record.

here is the code
--------------------------------------------
Dim row As DataRow
   
For Each row In ds.Tables(0).Rows

if row(" WO_dept_equip_dept ")= 30101 then
  --common validations for both types
  --set of validations specific to this type(i need check the value against a couple of look up tables)
  --and then assign the values to the fields
  --update the table
  --if there are errors then write a exception record to exception table
 
 
else
  --common validations for both types
  --set of validations specific to this type(i need check the value against a couple of look up tables)
  --and then assign the values to the fields
  --if there are errors then write a exception record to exception table
 ----update the table

end if

----------------------------
Can this be done in a single query ?

Thanks
Venkat
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16404743
yes, this is possible in general. as you have seen in the UPDATE statement above, you can set field values depending on others using the CASE WHEN ... THEN ...  { WHEN ... THEN ... } ELSE ... END statement.
0
 
LVL 12

Expert Comment

by:deanvanrooyen
ID: 16405254
hi,

this is an example not of mine but from code project on how to run a batch update using a transaction and mysql, you can at least see hoe to do this, jsut modify the iteration in the  foreach (string rolename in rolenames) to do what you want

            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand("INSERT INTO `" + usersInRolesTable + "`" +
                    " (Username, Rolename, ApplicationName) " +
                    " Values(?Username, ?Rolename, ?ApplicationName)", conn);

            MySqlParameter userParm = cmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255);
            MySqlParameter roleParm = cmd.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255);
            cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName;

            MySqlTransaction tran = null;

            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                cmd.Transaction = tran;

                foreach (string username in usernames)
                {
                    foreach (string rolename in rolenames)
                    {
                        userParm.Value = username;
                        roleParm.Value = rolename;
                        cmd.ExecuteNonQuery();
                    }
                }

                tran.Commit();
            }
            catch (MySqlException e)
            {
                try
                {
                    tran.Rollback();
                }
                catch { }


                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "AddUsersToRoles");
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }
0
 
LVL 12

Expert Comment

by:deanvanrooyen
ID: 16593182
use a stored procedure in the database rather this is a lot more efficent
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month10 days, 14 hours left to enroll

572 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