| To prepare for this project, create a Windows Forms project and call it ADOBook06-03. You can copy the form file from the previous project into this project. We will then modify the code in the new project. You can also download the code from the book's web site if you don't want to type it in. In our next exercise we will look into how to manually set up the Data Adapter so that the Update statement will work automatically and we will not have to assign our parameters manually. In order to do this in a less confusing way we will use a simpler Select statement that does not select all of the fields in the table. Use the following Select statement: Select CustomerID, CompanyName, ContactName, ContactTitle from customers Where CompanyName Like @CoName + '%' This statement will allow us to test our Update statements and also use a parameter to retrieve data. Using Dynamic Properties Before we continue, let's look at an interesting feature of the .NET framework. The feature is called dynamic properties. It is similar to the use of initialization files in previous versions of Visual Basic. Instead of the initialization file format it uses an XML format. The data is stored in this file when you set the property in the Property window. There are also programmatic ways to retrieve the data. The method is similar to the GetPrivateProfileString Windows API calls but much simpler to use. The name of the file, app.config, is stored in the same folder as the program by default. We can set any property of any control to be a dynamic property. You can do this by using the dynamic properties entry in the Property window. To make a property dynamic, first select the Control on the form designer. Then scroll the dynamic properties entry into view in the Property window and click the plus box. An ellipsis button will appear next to Advanced property. Clicking the ellipsis button opens the Dynamic Properties dialog box. It appears as in Figure 6.22. Figure 6.22. The Dynamic Properties dialog.  When we start entering code for the program we will see how to retrieve the property values from the app.config file. This will save us a lot of time reentering SQL statements. Now let's get back to the task at hand. We'll have to make changes to the Refresh button's click event. We will show you the changes now and explain them line by line later. Here is the new Refresh button's click event: Private Sub btnRefresh_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRefresh.Click Dim cmdSelect As New SqlClient.SqlCommand() Dim cmdInsert As New SqlClient.SqlCommand() Dim cmdUpdate As New SqlClient.SqlCommand() Dim cmdDelete As New SqlClient.SqlCommand() Dim prm As New SqlClient.SqlParameter() Try If TextBox1.Text = "" Then Exit Sub If mDS.Tables.Count > 0 Then mDS.Clear() End If cmdSelect.Connection = mCN mDA.SelectCommand = cmdSelect mDA.SelectCommand.CommandText = TextBox1.Text prm.ParameterName = "@CoName" prm.DbType = DbType.String mDA.SelectCommand.Parameters.Add(prm) mDA.SelectCommand.Parameters("@CoName").Value = txtSearch.Text mDA.InsertCommand = cmdInsert mDA.UpdateCommand = cmdUpdate mDA.DeleteCommand = cmdDelete mDA.SelectCommand.CommandText = TextBox1.Text mDA.InsertCommand.CommandText = TextBox2.Text mDA.UpdateCommand.CommandText = TextBox3.Text mDA.DeleteCommand.CommandText = TextBox4.Text mDA.Fill(mDS) DataGrid1.DataSource = mDS.Tables(0) Catch errobj As Exception MsgBox(errobj.Message) End Try End Sub The code is not yet complete. We still have to add the code to create the Insert, Update, and Delete statements. The code as it stands will just get the data from the database and load it into the grid. We did this with a couple of lines of code when we configured the DataAdapter using the wizard. Now it will take considerably more lines because we have to do everything ourselves . The first thing we have to do is manually create the Command objects to load into the four Command properties of the DataAdapter. So we create and call them cmdSelect, cmdInsert, cmdUpdate, and cmdDelete. Further down in the code we assign these objects to the Command properties of the DataAdapter. Reading Data Let's first look at the code to read the data from the database. The first thing we have to do is assign the Select command a Connection object. Next we assign the Select command CommandText property to the value of TextBox1. Next we assign the Command object to the DataAdapter's SelectCommand property. Next we must manually create a parameter to pass into the SQL statement. We declared a Parameter object in the declaration section of the procedure. Now we must assign the name of the parameter to the Parameter Name property of the Parameter object. Then we must add the parameter to the Select command's Parameters collection. The last thing we have to do with the parameter is assign it the value of the text box that contains the search field. The next seven lines will be made clear when we add the code to create the Update statements. Right now the project is ready to run. So let's run it and see the results. Your screen should look like Figure 6.23. Figure 6.23. The demo screen with a custom select statement.  Notice that we searched for H which means to give me all records that have a company name starting with H . By default searches are case insensitive. Creating the Select statement is the easy part. The other three statements are a little more difficult. Creating the Update Statement The most difficult statement is Update. In order for the DataAdapter to work properly, the Parameter collection must be created carefully . We must be sure to create two parameters for each column in the Select statement so that we can employ optimistic concurrency. We can create the Update statements manually and create our own parameter list, or we can create a procedure to parse the Select statement and create the parameters for us. First let's do it by manually creating our own parameter collection. Before we can create a parameter list we must create the update SQL statements: Update Customers Set CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle Where CustomerID = @Original_CustomerID And CompanyName = @Original_CompanyName And ContactName = @Original_ContactName And ContactTitle = @Original_ContactTitle The statement itself is pretty simple. The Set clause uses the first set of parameters to assign the new values to the columns. In the where clause we use the other set of parameters (prefixed by Original_). These parameters will be used to pass the original unchanged values of the columns into the Update statement. If you look back at the Update statement created by the CommandBuilder object, you will notice that it does a lot of checking for nulls as well as the value in parameters in the where clause. In order to simplify the statement we will not check for the nulls, we will assume the parameters are not Null. This makes it a little easier to see what we're doing. By setting properties in the Parameter objects we tell the DataAdapter which are the original values and which are the current values. Note that just by declaring the parameters in the SQL statement does not automatically create the Parameter collection. We still must do this manually in code. For now we will create the parameters using hard-coded statements. In a bit, we will create a procedure to parse the update SQL statement and create a parameter list for it. Let's look at the code to create the Parameter collection for our SQL statement. Since we are creating the Command object ourselves, we will need a variable to hold the Command object while we configure it. We will also need a variable to hold a Parameter object while we configure it. Unlike in the last demonstration, we will not declare the variables as New. This is because we will need to create multiple instances of each object. Therefore we need to create the objects as we need them. Also, since the code to initialize the DataAdapter is now considerably more complicated, let's move the code into its own separate subprocedure. The next section contains the new procedure. We will go through it line by line so you understand what it is doing. The procedure appears to be quite long but it repeats itself for each Parameter object, so the code is really not as complicated as it looks. To backtrack, here is the SQL statement for the Insert. Insert Into Customers (CustomerID, CompanyName, ContactName, ContactTitle) Values (@CustomerID, @CompanyName, @ContactName, @ContactTitle) And here is the code for the Delete statement. Delete From Customers Where CustomerID = @Original_CustomerID And CompanyName = @Original_CompanyName And ContactName = @Original_ContactName And ContactTitle = @Original_ContactTitle The Code to Create the Commands These SQL statements should be placed in TextBox3 and TextBox4 respectively. Since we have created dynamic properties from the Text property, the SQL statements will be saved in the app.config file. Private Sub InitDA() Dim cmd As SqlClient.SqlCommand Dim prm As SqlClient.SqlParameter Try mCN.ConnectionString = "Server=em_server\vsdotnet;Database=Northwind;Integrated Security=SSPI" cmd = New SqlClient.SqlCommand() cmd.Connection = mCN cmd.CommandText = TextBox1.Text mDA.SelectCommand = cmd prm = New SqlClient.SqlParameter() prm.ParameterName = "@CoName" prm.DbType = DbType.String mDA.SelectCommand.Parameters.Add(prm) ' UPDATE COMMAND ' First assign the SQL statement cmd = New SqlClient.SqlCommand() cmd.CommandText = TextBox2.Text cmd.Connection = mCN ' Customer ID Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@CustomerID" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Current prm.SourceColumn = "CustomerID" cmd.Parameters.Add(prm) ' Company Name Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@CompanyName" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Current prm.SourceColumn = "CompanyName" cmd.Parameters.Add(prm) ' Contact Name Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@ContactName" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Current prm.SourceColumn = "ContactName" cmd.Parameters.Add(prm) ' Contact Title Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@ContactTitle" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Current prm.SourceColumn = "ContactTitle" cmd.Parameters.Add(prm) ' Customer ID Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@Original_CustomerID" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Original prm.SourceColumn = "CustomerID" cmd.Parameters.Add(prm) ' Company Name Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@Original_CompanyName" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Original prm.SourceColumn = "CompanyName" cmd.Parameters.Add(prm) ' Contact Name Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@Original_ContactName" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Original prm.SourceColumn = "ContactName" cmd.Parameters.Add(prm) ' Contact Title Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@Original_ContactTitle" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Original prm.SourceColumn = "ContactTitle" cmd.Parameters.Add(prm) mDA.UpdateCommand = cmd ' INSERT COMMAND ' First assign the SQL statement cmd = New SqlClient.SqlCommand() cmd.CommandText = TextBox3.Text cmd.Connection = mCN ' Customer ID Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@CustomerID" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Current prm.SourceColumn = "CustomerID" cmd.Parameters.Add(prm) ' Company Name Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@CompanyName" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Current prm.SourceColumn = "CompanyName" cmd.Parameters.Add(prm) ' Contact Name Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@ContactName" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Current prm.SourceColumn = "ContactName" cmd.Parameters.Add(prm) ' Contact Title Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@ContactTitle" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Current prm.SourceColumn = "ContactTitle" cmd.Parameters.Add(prm) mDA.InsertCommand = cmd ' DELETE COMMAND ' First assign the SQL statement cmd = New SqlClient.SqlCommand() cmd.CommandText = TextBox4.Text cmd.Connection = mCN ' Customer ID Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@Original_CustomerID" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Original prm.SourceColumn = "CustomerID" cmd.Parameters.Add(prm) ' Company Name Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@Original_CompanyName" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Original prm.SourceColumn = "CompanyName" cmd.Parameters.Add(prm) ' Contact Name Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@Original_ContactName" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Original prm.SourceColumn = "ContactName" cmd.Parameters.Add(prm) ' Contact Title Parameter prm = New SqlClient.SqlParameter() prm.ParameterName = "@Original_ContactTitle" prm.DbType = DbType.String prm.SourceVersion = DataRowVersion.Original prm.SourceColumn = "ContactTitle" cmd.Parameters.Add(prm) mDA.DeleteCommand = cmd Catch errobj As Exception MsgBox(errobj.Message & vbCrLf & errobj.StackTrace) End Try End Sub As you can see, there are basically two snippets of code in this procedure. One creates the Command object and the other creates each Parameter object. The Command object snippet is self-explanatory. A new instance of an SQL Command class is assigned to the cmd variable. Then we assign the Connection object to the Command object's Connection property. Lastly, we assign the Text property of the appropriate text box containing the SQL statement to the CommandText property of the Command object. The parameter creation snippet is a little more involved but not that difficult to understand. First we create an instance of a Parameter object and assign it to the prm variable. Then we assign the name of the parameter to the Parameter object's ParameterName property. It is important to get the name of the parameter correct because this is how it associates the parameter to the parameter placeholder in the SQL statement. Then we assign the data type of parameter to the Parameter objects DBType property. All of our properties happen to be strings, but they could be any type in the DBType enumeration. Some types require additional properties to be set, such as the size , scale, and so forth. The DBType property returns a data type that is compatible across all the CLR languages (Visual Basic, C#). There is another enumeration, SQLDBType, which returns native SQL Server types. If you are an SQL guru, you may want to use these instead as they may be more familiar. The caveat is that now your code will be SQL Server specific. Next we must tell the Property object whether our property is an original value or a current value. This property is there primarily to support interaction with the DataSet. Original values are used in the where clauses. Current values are used to pass the new values in the data set that the user has changed. Otherwise there's no real difference between the two types of parameters. The SourceColumn property is used to associate the parameter with a column in the table. Again this is to support interaction with the DataSet. The Update SQL statement uses both parameter types to support updating and optimistic concurrency. The Insert statement does not need original values since there are none. Therefore it only uses current values. The converse is true of the Delete statement. There are no current values, just original values. Now that we've manually created our parameters the long way let's run the project to verify that it works. When we run the project, the screen should look like Figure 6.24 after we click the Refresh button. Figure 6.24. The demo with manually entered SQL statements.  To test the application, try changing one of the fields. Then click the Update button. Change the search for text box to another letter and click Refresh. Then change it back to b and click Refresh. Your change should have persisted . Now add a new row to the grid. Make sure the customer ID's unique and begins with B. Type anything else you want in the other four columns. Click the Update button. Now repeat the exercise, changing the search for text box to another letter. Click Refresh, then change it back, and click Refresh again. You should still see your new row. Now delete the row you just added by selecting the row and hitting the delete key. Click the Update button again. If you refresh the grid again your row should remain deleted.  | 
