CODEWITHSUNDEEP
c#asp.netsql-server
Edd Davis
Edd Davis

Reputation: 59

Best way to record the following data

I am creating a SQL Server database that records vehicle movements, cleans, repairs etc.

I am struggling with how to record the following data.

I need to record each time the vehicle is cleaned. I also need to extract the amount of time that vehicle is cleaned over the past 28 days and make an average

So far I have a master table with the following columns:

Callsign, Registration, Location, Date Last Cleaned.

Then, as a work around I have "vehicle specific" tables which is where I record it each time it is cleaned.

however as I am working the ASP.net, the only way I can find to access the clean dates is by doing a foreach loop through each of the vehicle tables and returning the count of dates in the past 28 days. the problems with this I cannot work out how do this in 1 data connection instead of multiple requests to the server.

Here is a snippet of the code, but you can see that it runs through each of the check box list, and if "true" then will add to the vehicle specific table

 string constr = myconstring;

 SqlConnection cnn = new SqlConnection(constr);
 SqlTransaction transaction;

 for (int i = 0; i < checkboxlistAM.Items.Count; i++)
 {
     string callsignString = checkboxlistAM.Items[i].Text;

     if (checkboxlistAM.Items[i].Selected == true)
     {
         string declare = "declare @morning datetime declare @date datetime declare @counting int ";
         string setting = " set @date = DATEADD(d, DATEDIFF(d, 0, getdate()), 0) set @morning = dateadd(hh, 7, @date) set @counting = (select count([made ready]) from["+callsignString+"] where[Made Ready] = @morning) ";
         string insertmorning = " if @counting <>1 insert into ["+callsignString+"] ([made ready]) values (@morning) ";

         string QueryVehicleSpecificTable = declare + setting + insertmorning;
         string QueryMasterTable = "update Shropshire SET[last made Ready AM] = GETDATE() where Callsign = '"+callsignString+"'";

         cnn.Open();
         transaction = cnn.BeginTransaction();

         SqlCommand cmd1 = new SqlCommand(QueryVehicleSpecificTable, cnn);
         cmd1.CommandType = CommandType.Text;

         SqlCommand cmd2 = new SqlCommand(QueryMasterTable, cnn);
         transaction.Commit();

         cmd1.ExecuteNonQuery();
         cmd2.ExecuteNonQuery();

         cnn.Close();
     }
     else if (checkboxlistAM.Items[i].Selected == false)
     {
         string declare = "declare @morning datetime declare @date datetime declare @counting int ";
         string setting = " set @date = DATEADD(d, DATEDIFF(d, 0, getdate()), 0) set @morning = dateadd(hh, 7, @date) set @counting = (select count([made ready]) from[" + callsignString + "] where[Made Ready] = @morning) ";
         string deletemorning = " delete from ["+callsignString+"] where [Made Ready] = @morning";
         string queryDeleteRecordfromVehicleSpecific = declare + setting + deletemorning;
         string QueryMasterTable = "update Shropshire SET[last made Ready AM] = null where Callsign = '" + callsignString + "'";

         cnn.Open();
         transaction = cnn.BeginTransaction();

         SqlCommand cmd1 = new SqlCommand(QueryMasterTable, cnn);
         SqlCommand cmd2 = new SqlCommand(queryDeleteRecordfromVehicleSpecific, cnn);

         cmd1.CommandType = CommandType.Text;
         cmd2.CommandType = CommandType.Text;

         transaction.Commit();

         cmd1.ExecuteNonQuery();
         cmd2.ExecuteNonQuery();
         cnn.Close();
     }
}

As you can see this loop has approx. 42 iterations in the morning and afternoon (84 altogether) and opening and closing the connection each time is going to slow everything down.

Does anyone have any ideas of how I can make this better.

Either by making a new "cleaning" table, but then how can I keep track over 28 days instead of forever.

Or by copying each of the "vehicle specific tables" to 1 DataTable and then somehow updating them again after altering them?!?

Any ideas or questions are welcomed.

Thanks in advance

Upvotes: 0

Views: 63

Answers (1)

Guillaume CR
Guillaume CR

Reputation: 3016

Before I answer your question, I have to point this out:

where Callsign = '"+callsignString+"'"

This is called SQL concatenation, and it allows for SQL injection, which is the #1 security vulnerability in software today. Never do this. Use parametrized SQL queries all the time.

Now to answer your question.

opening and closing the connection each time is going to slow everything down.

That's probably true, but you should obtain proof that it is a performance problem before you decide to optimize it.

Like @PaulF mentions, you don't need to close the connection every time. Move the code that opens and close the connection outside of your loop.

Another technique is to create a stored procedure in your database that would do all this logic in TSQL. That way you can provide only the callsign to the stored proc and it would execute all 84 operations inside a single SQL command. The disadvantage of this approach is that stored procedures are generally a little more expensive to maintain and refactor.

Upvotes: 1

Related Questions