Exercise 3: Inserting Data

In this exercise you will learn how to add a new product and its associated image to the database using the Transact-SQL INSERT statement. Because of the structure of the AdventureWorks2012 database, this involves inserting a new row into three separate tables. Before we get started, it is important that you understand the concept of exclusive locks, transactions and unique identifiers.

Let us start with exclusive locks. SQL Server places exclusive locks to an exclusive lock on data affected when executing Transact-SQL command that change the database (INSERT, UPDATE and DELETE). Exclusive locks prevent other locks (shared or exclusive) from being placed on the same data until they are released. If the execution of a command is blocked by a lock, SQL Server will automatically wait for it to be released before proceeding. This may result in some latency known as a lock wait, especially in a database that sees frequent changes. In most cases you do not have to do anything special in your application to account for lock waits.

Next, let us discuss transactions. In some cases, it is useful to retain locks to ensure that a set of changes is applied to the database in a consistent way. To support this, SQL Server and ADO.NET support the concept of a transaction. Transactions provide the ability to enforce consistency at the expense of multi-user concurrency by holding locks until a series of changes is complete. While transactions are an integral component of any data-driven application, you should take care to limit their scope and duration. In ADO.NET, transactions are implemented using the System.Transactions class library.

Finally, let us talk about unique identifiers. As we mentioned previously, tables should have a primary key that can be used to uniquely identify every row in the table. This sounds simple enough, but implementing this in a multi-user environment can be tricky. Fortunately SQL Server makes the process of creating unique identifiers a snap. You can generate new unique identifiers using the SQL Server IDENTITY property or using globally unique identifier.

An IDENTITY is nothing but an integer sequence managed by SQL Server. To use the IDENTITY approach, simply define a column in your table using one of the integer data types and declare it as an IDENTITY. When new data is inserted in the table, SQL Server will automatically generate an integer sequence number and store it in the column. You can determine the value that was generated for a newly-inserted row by querying @@IDENTITY immediately after the insert.

A globally unique identifier, or GUID, is a reference number generated by an algorithm that is guaranteed to be unique. GUIDs are typically represented as text, like this: F1F7310E-A04E-441B-B56E-FA957352D9FE. To use this approach, simply define a column in your table using the uniqueidentifier data type and declare it as a ROWGUIDCOL. If you want SQL Server to automatically generate a new GUID during an insert if one is not provided, associate a DEFAULT constraint with the column that uses the NEWSEQUENTIALID() function to generate a new GUID. Otherwise, generate the GUID in your application and specify its value in the insert statement.

Task 1 – Adding the Insert Method to the Data Access Class

1. In the File menu, choose Open | Project/Solution. In the Open Project dialog, browse to Ex3-InsertingData\Begin in the Source folder of this lab, select Begin.sln and click Open. Alternatively, you may continue working with the solution obtained after completing the previous exercise.
2. Open the ProductsDataAccess.cs class by double-clicking the file in the Solution Explorer.
3. Add a reference to the System.Transactions assembly. To do this, right-click the project name in Solution Explorer, and click Add Reference. In the .NET tab, select the System.Transactions assembly, and then click OK.

4. Add the following using statements at the top of the file.

   C#

   using System.Data.SqlTypes; using System.Transactions;

5. Create a new public static method to insert a new product. To do this, paste the following code (shown in bold) inside the ProductDataAccess class.

   (Code Snippet – First SQL ASP.NET Application Lab – Ex03 – InsertProduct method)

   C#

   public static void InsertProduct(string name, decimal listPrice, string size, byte[] photo) { using (TransactionScope scope = new TransactionScope()) { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString)) { connection.Open(); SqlInt32 productId = InsertProductRow((SqlString)name, (SqlMoney)listPrice, (SqlString)size, connection); InsertProductPhoto(productId, photo, connection); scope.Complete(); } } }

   Let us look at this code a little more closely. Notice that we did not use SQL Server native types for the parameters of the InsertProduct method. This is because this method will be called by an ASP.NET Web Control. ASP.NET web controls only support native .NET Framework data types, so we cast these parameter values to SQL Server native types later.

   Adding a new product involves performing three separate insert operations against three different related tables in the AdventureWorks2012 database. All three of these inserts must succeed for the database to be in a consistent state. If there are any errors, all of the changes must be rolled back. What we need is a transaction.

   To implement this behavior, the InsertProduct method wraps all of the individual inserts in a TransactionScope. Any connections that are opened inside the using statement will automatically be enlisted in the transaction. If multiple connections are opened to different SQL Server instances, the transaction is automatically promoted to a distributed transaction. Use of distributed transactions requires that the Distributed Transaction Coordinator service be started and configured by a system administrator in advance.

   After each of the individual insert operations have completed it is time to commit the transaction. This is done by calling the Complete method on the TransactionScope object. Notice that all of the data needed to perform our three inserts is collected from the user outside of the TransactionScope. This is by design. Using this approach the exclusive locks required to perform the three inserts can be placed and released quickly with no user intervention required, maximizing the concurrency of the application.

   Note:

   Best Practice: To improve the multi-user concurrency of your application, limit the scope and duration of transactions to short operations that do not require user intervention to complete. Avoid using distributed transactions except in situations where they are absolutely necessary. If you must use them, strictly limit their scope and duration. Distributed transactions can introduce additional latency and can be complicated to configure, monitor and debug.

6. Create the InsertProductRow method that is invoked by the InsertProduct method. To do this, add the following method in the ProductDataAccess class.

   (Code Snippet – First SQL ASP.NET Application Lab – Ex03 – InsertProductRow method)

   C#

   private static SqlInt32 InsertProductRow(SqlString name, SqlMoney listPrice, SqlString size, SqlConnection connection) { // at the end of the command we execute a SELECT to get the newly generated product Id var commandText = "INSERT INTO Production.Product " + "(Name, ProductNumber, ListPrice, Size, SafetyStockLevel, " + "ReorderPoint, StandardCost, DaysToManufacture, SellStartDate) " + "VALUES (@Name, @ProductNumber, @ListPrice, @Size, @SafetyStockLevel, " + "@ReorderPoint, @StandardCost, @DaysToManufacture, @SellStartDate); " + "SELECT CAST(@@IDENTITY as integer); "; var command = new SqlCommand(commandText, connection); command.Parameters.AddWithValue("@Name", name); command.Parameters.AddWithValue("@ListPrice", listPrice); command.Parameters.AddWithValue("@Size", size); // set all other required fields that don't have a default value. command.Parameters.AddWithValue("@ProductNumber", "TK-" + DateTime.Now.Millisecond); command.Parameters.AddWithValue("@SafetyStockLevel", 100); command.Parameters.AddWithValue("@ReorderPoint", 100); command.Parameters.AddWithValue("@StandardCost", 0); command.Parameters.AddWithValue("@DaysToManufacture", 0); command.Parameters.AddWithValue("@SellStartDate", DateTime.Today); // return to the program the result of the recently inserted product Id SqlInt32 productId = (Int32)command.ExecuteScalar(); return productId; }

   FakePre-4dbf46d780af40f080fe6ec21f6d47f5-227a03d62d074a9788cba22d1212bed4FakePre-9446e9c5136744918783c93049446aa0-ffb85ea34a6b49149805658de477f9cdFakePre-9140cfeed5914776acc18df1bb37ffe3-5bf4f4152465441f84707cf539c916e8
   

   Let us examine this code in a bit more detail. This code inserts the first of three rows required to add a product. In this case we are inserting a new row into the Production.Product table. Notice that the command text actually includes two commands, an INSERT and a SELECT. The INSERT statement adds a new row to the table. Notice that the ProductId is not specified. This is because it will be automatically generated by SQL Server using an IDENTITY. The SELECT statement returns the newly generated ProductId using @@IDENTITY cast as an integer.

   Note:

   Best Practice: Return a newly generated IDENTITY value to the application using the @@IDENTITY function instead of re-querying the source table to find out the value of an automatically generated unique identifier. This avoids unnecessary overhead and will make your application perform better.

   Next, parameters and values are added to the SqlCommand object for each of the named parameters in the INSERT command. One parameter is created for each column that requires a value. User-provided values are available for name, listPrice and size, and dummy values are used for the other required columns.

   Finally, the command is executed using the ExecuteScalar method, and the newly generated ProductId is returned to the caller. Note that you can only cast the return values of the ExecuteScalar method to native .NET Framework data types.

7. Create the InsertProductPhoto method that is invoked by the InsertProduct method. To do this, add the following method in the ProductDataAccess class.

   (Code Snippet – First SQL ASP.NET Application Lab – Ex03 – InsertProductPhoto method)

   C#

   private static void InsertProductPhoto(SqlInt32 productId, byte[] photo, SqlConnection connection) { // at the end of the command we execute a SELECT to get the newly generated product photo Id var insertPhotoCommandText = "INSERT INTO Production.ProductPhoto " + "(ThumbNailPhoto) VALUES (@ThumbNailPhoto); " + "SELECT CAST(@@IDENTITY AS integer); "; var insertPhotoCommand = new SqlCommand(insertPhotoCommandText, connection); insertPhotoCommand.Parameters.AddWithValue("@ThumbNailPhoto", photo); // store the new product photo Id SqlInt32 productPhotoId = (Int32)insertPhotoCommand.ExecuteScalar(); }

   FakePre-d2fb2e7112734638b97c2597ea961567-aa1d4cf603e34fea8d079b754318379c
   

   The preceding code creates the query to insert a new photo in the ProductPhoto table, and after that it returns the newly generated product photo ID. Notice that the photo binary data is passed as a Command Parameter.

   Note:

   Due to the AdventureWorks2012 database structure, to insert an image for a product you need to insert the image in the ProductPhoto table and then create the relation between the Product and ProductPhoto tables.

8. Add code to the InsertProductPhoto method to add the relation between the Product and the ProductPhoto tables. To do this, add the following bolded code in the InsertProductPhoto method, below the code inserted previously.

   (Code Snippet – First SQL ASP.NET Application Lab – Ex03 – Relation between Product and ProductPhoto)

   C#

   private static void InsertProductPhoto(SqlInt32 productId, byte[] photo, SqlConnection connection)

   FakePre-9c918b7503a3445181c2bede245adab5-49ef12b6a10e4990870ccbaf29e67318FakePre-018245f8fd1b4718add1653e0794b8dc-99bd08e0151540ee9d2eee69da34409aFakePre-1a304c3a5908435e944a4020df94b0d7-3662c0d86e424c9ba980bd536dc11d3f var addRelationCommandText = "INSERT INTO Production.ProductProductPhoto " + "(ProductID, ProductPhotoID, [Primary]) VALUES " + "(@ProductID, @ProductPhotoID, @Primary)"; var addRelationCommand = new SqlCommand(addRelationCommandText, connection); addRelationCommand.Parameters.AddWithValue("@ProductID", productId); addRelationCommand.Parameters.AddWithValue("@ProductPhotoID", productPhotoId); addRelationCommand.Parameters.AddWithValue("@Primary", true); addRelationCommand.ExecuteNonQuery();FakePre-bfc1abfe968e4ebc8750b5af3ed2d921-435db98ae44e418cb62ebde594bf309aFakePre-d0fdf016c41f4ac3b7c181f5cc4199c5-3a5be1bc556145978685bebd82f868fcFakePre-d0601f75f720452988937052e0b458fd-e76b4d7414644734b0e57348a1ebe6aeFakePre-86c96d58bb6b4f15b8d3cc322cd4c800-1108594487ae456a8a706ab839f396b2
   Note:

   The ExecuteNonQuery method of the SqlCommand class is used when the query does not return any value. In the preceding code it is used for executing an Insert query.

9. Press CTRL+SHIFT+B to build the solution.

Task 2 – Binding the Data to the UI

In this task you will learn how to add the insert form and bind it to the method created in the previous task.

1. Open the Default.aspx page in Design view. To do this, double-click the file in the Solution Explorer, and then click Design at the bottom left corner of the designer.
2. Click the ObjectDataSource control in the designer surface to select it.
3. Click the Smart Tag () to expand the ObjectDataSource tasks, and then click Configure Data Source.

4. Click Next in the Choose a Business Object page. Click the Insert tab, choose the InsertProduct method and click Next. Then in the following screen click Finish.

   Figure 14

   Choosing the insert method

5. A dialog asking whether you want to reconfigure ListView may show. Click No.

   Figure 15

   Dialog about reconfiguring ListView

6. Add a DetailsView control to the page. To do this, from the Toolbox drag a DetailsView control at the top of the designer surface, above the Previous and Next buttons.

7. Select the existing ProductsDataSource data source as the Data Source for the control. To do this, click the Choose Data Source drop down list, and then select ProductsDataSource.

   Figure 16

   Choosing the Data Source for the DetailsView control

8. Click the DetailsView control Smart Tag () to display the DetailsView Tasks context menu and then click Add New Field. In the Add Field dialog box, select TemplateField as the field type, type Image as the Header text, and then click OK.

   Figure 17

   Add Field dialog box

9. Click the DetailsView control Smart Tag () to display the DetailsView Tasks context menu and then click Edit Templates. Select Field[7] – Image in the Display combo box.

   Figure 18

   Editing the templates for the DetailsView control

10. From the Standard section of the Toolbox, drag a FileUpload control to the InsertItemTemplate area.

    Figure 19

    Adding a FileUpload control to the InsertItemTemplate area

11. Click over the FileUpload control previously added to select it and press F4 to go to the control properties. Type ProductImageFileUpload as the control (ID) in the Properties pane.

    Figure 20

    Changing the control ID of the FileUpload control

12. Click the DetailsView control Smart Tag () and then click End Template Editing to finish editing the control templates.

13. Click again the DetailsView control Smart Tag () and now click Edit Fields. In the Fields window, delete the ProductID, OriginalName, OriginalListPrice and OriginalSize fields from the Selected Fields list. To do this, select one by one each of the fields and click the delete button (). Finally click OK to close the Fields window.

    Figure 21

    Deleting field from the DetailsView control

14. Select the Enable Inserting checkbox of the DetailsView Tasks context menu.

    Figure 22

    Selecting the Enable Inserting checkbox

15. Select the DetailsView control and press F4 to see its properties. In the Properties pane set the following properties:
    • Set the control (ID) to InsertProductDetailsView.
    • Set the AutoGenerateRows property to False.
    • Set both CellPadding and CellSpacing properties to 1.
    • Set the DefaultMode property to Insert.
    • Expand the FieldHeaderStyle element and set the CssClass property to FieldHeader.
16. Click the events icon () on the Properties pane to see the available event of the control. Double-click the ItemInserting event to generate the event handler for that event.

17. Paste the following code inside the event handler to get the photo from the FileUploader control.

    (Code Snippet – First SQL ASP.NET Application Lab – Ex03 – ItemInserting event code)

    C#

    protected void InsertProductDetailsView_ItemInserting(object sender, System.Web.UI.WebControls.DetailsViewInsertEventArgs e)

    FakePre-7ce7c507782e4677b8087a6d8eb1e349-80d4d7f1edb84c208832cb5e59cb48ff var fileUpload = this.InsertProductDetailsView.FindControl("ProductImageFileUpload") as FileUpload; e.Values["photo"] = fileUpload.FileBytes;FakePre-d6c586d25759460f8e44869f3c16b14d-39f43f51c8e4431a86db03b932a25f01
    Note:

    This handler is used to intercept the insertion before it is made, and update the product with the corresponding bytes of the chosen image.

18. Add the following using statements at the top of the file.

    C#

    using System.Web.UI.WebControls;

19. Open the Default.aspx page in Source view and add the following code after the DetailsView closing tag.

    HTML

    <fieldset style="clear: both"> <legend>Add new product</legend> <br /> <p> * Size field has a max lenght of 5</p> <br style="clear: both" /> <%--Move the DetailsView control here--%> </fieldset>

20. Move the DetailsView control inside the <fieldset> tags replacing the comment “Move the DetailsView control here”. This is shown in the following code.

    HTML

    <fieldset style="clear: both">

    FakePre-a94ab14b6cfd42a7be440e86c0a676ce-0e7046dcb8124acf88720cc7ef8fcc18FakePre-4c5dc2777ce64753896ecca917d89c9d-45665d5596114e5ab301fb726cae560cFakePre-33df3614333c40b9a6e1c3c8b9e80356-6fba6bb532f94eee991389895538f9b6FakePre-4a09137902b549a78ed0fd64d4ebff22-5820f93d61f645cc886286ab157eb9a6FakePre-411e847d77824e6da8738a670f09c003-aafd67089eef457bb555bfa49f9b9869 <asp:DetailsView ID="InsertProductDetailsView" runat="server" DataSourceID="ProductsDataSource" DefaultMode="Insert" AutoGenerateRows="False" OnItemInserting="InsertProductDetailsView_ItemInserting"> ... </asp:DetailsView>FakePre-ae8f5b844fa243bda262e50a3ddfc0ef-850b89826a5f40e59a63775a95032772

21. Save the changes and close the files.

Exercise 3: Verification

In order to verify that you have correctly performed every step of exercise three, proceed as follows.

1. Press CTRL+F5 to run the web application.

   Figure 23

   Listing the products

2. In the Add new product section type the following data in the product fields:
   • Name: Road-180 Mountain Bike, 48
   • ListPrice: 3750
   • Size: 48
3. Click the Browse button and select a photo from your computer. There are a couple of images that you can use, located under the Assets folder in the Source folder of this lab. Finally, click Insert.

4. The listing page will be updated, showing the new product you have just added.

   Figure 24

   Listing the products after the insertion