batch updates to the database thru dataset

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
tvenkat9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
If possible, do the data validation in a stored procedure (if no user intervention is needed) directly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tvenkat9Author Commented:
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
deanvanrooyenCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
tvenkat9Author Commented:
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
tvenkat9Author Commented:
Hi,

Any one can suggest me better approach to do this.

Thanks
Venkat
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
tvenkat9Author Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
tvenkat9Author Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
deanvanrooyenCommented:
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
deanvanrooyenCommented:
use a stored procedure in the database rather this is a lot more efficent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.