Programmatically Configuring the DataAdapter Using Your Own Code

Team-Fly team-fly    

 
ADO.NET Programming in Visual Basic .NET
By Steve  Holzner, Bob  Howell

Table of Contents
Chapter 6.   ADO .NET DataAdapters


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.

graphics/06fig22.gif

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.

graphics/06fig23.gif

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.

graphics/06fig24.jpg

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.


Team-Fly team-fly    
Top


ADO. NET Programming in Visual Basic. NET
ADO.NET Programming in Visual Basic .NET (2nd Edition)
ISBN: 0131018817
EAN: 2147483647
Year: 2005
Pages: 123

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net