Execute SQL Query from ASP.NET Code Behind in C#

While working on one of my recent applications, I was tasked with generating serial numbers for a batch of products once their assembly was completed.

To give you all some background, the application I developed is a ‘check-out’ type system that tells employees in a manufacturing environment what they need to assemble next. They log into the application, select the item they need to build, then ‘start’ the job. Once they are finished, they return to the application and mark it ‘complete’. When the user logs into the web application to complete their job they are presented with a summary screen with specifics to that job. At the bottom of the screen there is a ‘Complete’ button.

It seemed pretty obvious that the best time to generate those serials would be on the ‘Complete’ button click.

What wasn’t so obvious was how to create a sequential list of serial numbers based on the quantity for that given batch of assembled products. Luckily for me, I’m a wiz when it comes to this kind of schtuff.

First, I had to model my serial table. It was a pretty basic setup, really. My first column (my Serial # column) is an identity (int) column. This way I could be sure that no serials would be duplicated. Plus, I could rely on my SQL doing the work to generate the numbers, which keeps my SQL Query simple. Following that, I added a column for the work order number, the part number and a smalldatetime field to serve as my time stamp. Below is a screen of my table design.

table identity
my table design settings for the identity

Next, I got to work on my Button click event. As always, make sure you reference the proper assemblies. In this case, you will need the ‘System.Data.SqlClient’ and ‘System.Data assemblies’.

On my summary page, I have some information displayed about the work order which is being populated by a different SQL database table. I am going to use some of that information in my query, so I need to make sure that I locate those controls and take the data from them. Then I put in some if statements to make sure I am not generating serials for a batch that has already been assigned serial numbers.

//if serials have not already been created, create them (durrr)
DetailsView1.ChangeMode(DetailsViewMode.Edit);
DetailsView1.DataBind();
TextBox ss1 = DetailsView1.FindControl("TextBox1") as TextBox;
TextBox ss2 = DetailsView1.FindControl("TextBox2") as TextBox;

if (ss1.Text.Length == 0)
  {
    if (ss2.Text.Length == 0)
      {

Alright! Now we are ready to make some magic! If you aren’t having fun at this point I strongly suggest a career change because this is about as exciting as it will ever get.

Now I will build my query and place it inside my if statement brackets.

//create serial number for the quantity on the order
string updateSerials;
updateSerials = "DECLARE @counter float, @qty float ";
updateSerials += "SELECT @counter = '0', @qty = @QTY ";
updateSerials += "WHILE @counter < @qty ";
updateSerials += "BEGIN INSERT INTO database.dbo.SerialTable ";
updateSerials += "(OrderNum, PartNum, DateAdded) ";
updateSerials += "VALUES (@OrderNum, @PartNum, GETDATE()) ";
updateSerials += "SELECT @counter = @counter + '1' ";
updateSerials += "END";

If you are lost, that’s OK. What I just did was create a query that adds an entry to my serial table then adds +1 to my counter. It keeps adding that same information over and over until my counter is equal to the quantity of the order (You will see in a minute how I populate those parameters). Remember, my table has a ‘int’ column as my serial number, so each time my query adds a new row, a unique serial number is automatically being assigned to that row.

Once our query is complete, we must add our database connection info.

//initialize connection string
SqlConnection con1 = new SqlConnection("connection string...");
SqlCommand cmd1 = new SqlCommand(updateSerials, con1);

Now, I identity my parameter values that are used in the query I created a minute ago.

//identify and name text boxes for parameters
DetailsView2.ChangeMode(DetailsViewMode.Edit);
DetailsView2.DataBind();
TextBox dv1 = DetailsView2.FindControl("TextBox1") as TextBox;
TextBox dv2 = DetailsView2.FindControl("TextBox2") as TextBox;
TextBox dv3 = DetailsView2.FindControl("TextBox3") as TextBox;

//set parameters from text box text
cmd1.Parameters.AddWithValue("@Order", dv1.Text);
cmd1.Parameters.AddWithValue("@Part", dv2.Text);
cmd1.Parameters.AddWithValue("@QTY", dv3.Text);

So, now I have a query and I have the parameters I need for my query. All that’s left to do is run the thing when the time comes.

//run query and close connection
try
  {
    con1.Open();
    cmd1.ExecuteNonQuery();
  }
finally
  {
    con1.Close();
  }

And that will do it! Serials have been created, my good man. Now, it has probably become pretty clear to you by now that I like to show boat, so you know I am not finished there.

For my next trick, I will be writing the first and last of those serials numbers back to my work order table for future reference.

//set first and last serial on work order
string updateWOM;
updateWOM = "MERGE [database].[dbo].[WorkOrderTable] as wot ";
updateWOM += "USING (SELECT OrderNum, MIN(SerialNum) as 'StartSerial', MAX(SerialNum) as 'EndSerial' ";
updateWOM += "FROM [database].[dbo].[SerialTable] ";
updateWOM += "GROUP BY OrderNum) as st ";
updateWOM += "ON wot.OrderNum = st.OrderNum ";
updateWOM += "WHEN MATCHED THEN UPDATE ";
updateWOM += "SET wot.StartSerial = st.StartSerial, wot.EndSerial = st.EndSerial;";

SqlConnection con2 = new SqlConnection("connection string...");
SqlCommand cmd2 = new SqlCommand(updateWOM, con2);

try
  {
    con2.Open();
    cmd2.ExecuteNonQuery();
  }
finally
  {
    con2.Close();
  }

Oh, and don’t forget to close that if statement! You are so forgetful sometimes…

//hey! you might need this
}

Once all that code is wrapped up nice and tight in an event handler, add the OnClick event to your the Button on your application.

<asp:Button> id="Button1" text="Complete" 
onclick="AutomagicSerials" </asp:Button>

And we are done! Wrap it up and put a nice bow on it because you have a gift, my friend.

If you want to take this a step further, use the methods outlined in my previous post titled PRINT A CRYSTAL REPORT FROM AN ASP.NET APPLICATION to automatically generate serial labels for each of those numbers once they have been created in the database.

Until next time, Benster out!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s