Web-App-Code-Review with DataSet and DataAdapter

  • Thread starter Thread starter Wolfgang Schmidt
  • Start date Start date
W

Wolfgang Schmidt

Hello NG,

I've written a .NET web-application which is doing updates in several
tables of a SQL Server 2000 with the help of the following method:


public static int UpdateDB(string table, DataSet dataset, bool insert,
DataRow dr, TraceContext Trace) {

int returnValue = 0;
SqlConnection con = new
SqlConnection(ConfigurationSettings.AppSettings["Connection"]);
SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT * FROM
"+table,con);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(myAdapter);
if (insert) { // This code handles database inserts
SqlCommand ins = new
SqlCommand(cmdBuilder.GetInsertCommand().CommandText,
myAdapter.SelectCommand.Connection);
ins.CommandText += ";SELECT IDENT_CURRENT('"+table+"') AS
pk_ID";
ins.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
Array aParams = Array.CreateInstance((new
SqlParameter()).GetType(),
(cmdBuilder.GetInsertCommand()).Parameters.Count);
cmdBuilder.GetInsertCommand().Parameters.CopyTo(aParams,0);
vcmdBuilder.GetInsertCommand().Parameters.Clear();
foreach (SqlParameter param in aParams) {
ins.Parameters.Add(param);
}
myAdapter.InsertCommand = ins;
cmdBuilder.Dispose();
try {
myAdapter.Update(dataset, table);
// new ID's are returned in case of inserts:
returnValue = (int)dr["pk_ID"];
}
catch (System.Data.SqlClient.SqlException ex){
Trace.Warn("INSERT failed: " + ex.ToString());
}
}
else { // This code handles database deletes and updates
try {
// number of changed rows are returned in case of deletes and
updates:
returnValue = myAdapter.Update(dataset, table);
}
catch (System.Data.SqlClient.SqlException ex){
Trace.Warn("DELETE or UPDATE failed: " + ex.ToString());
}
return returnValue;
}


Prerequesites:
* DataSet is stored as an Application Variable
* All tables of the dataset have one primary key "pk_ID"
(AutoIncrementSeed = -1, AutoIncrementStep = -1)
* The primary keys of the tables in the database have
AutoIncrementSeed and AutoIncrementStep set to 1
* DBUpdater.UpdateDB is called once per INSERT/UPDATE/DELETE row.


Questions:
* Is that code thread-safe if multiple users make transactions at the
same time? (on the level of the application; on the database-level
there is no need to do things like locking)
* How would you assess the performance? How could it be improved?

Thanks in advance,

Wolfgang
www.epsit.de
 
Back
Top