Data Manipulation From SQL Server Source Through Controls and LINQ

Introduction

In this article we'll see how to make a SQL Server resident table available to our application. We'll use wizards to connect to the instance and create DataSet and TableAdapter classes. We'll see how SQL Server resident data could be exposed using controls, with some examples and how they can be manipulated code-side, to realize updates towards the underlying database. Last, we'll see some LINQ references, to be applied to the present context.

Few steps to Dataset

A maybe simplistic subtitle, but not too far from the truth. We'll see here how to connect to our database, using the DataSource Configuration Wizard that will create the objects we'll use in our examples. This will be a very simple operation. Let's suppose we have a SQL Server instance, on which resides the TECHNET database. It contains a table named People, composed of an autoincremental Id and two Varchar fields, Name and City.

From the Visual Studio menu, click on Project, then Add New Data Source.

In the window that will appear, we must select the type of object from which our data will be read. In our case, Database.

We'll choose now the model type to make read data available using our application. We'll work with a DataSet.

Now we will be asked for connection parameters. Let's click on New Connection, feeding the Wizard with our SQL Server instance parameters. The connection string will be saved in the App.config file, for its later modification in case of migration to a different operative context, or instance changing, or the like.

Click Next. The Wizard will show the objects contained in our database (TECHNET, in our case). Select People table and click Next.

The Wizard will ask for a DataSet name. At the end of the procedure, we'll see our DataSet among the files belonging to the solution.

Double-clicking our DataSet will open the designer, using which we could see how the Wizard had created a DataTable-type object, named People, with the same fields read from the source table and a TableAdapter-type object, with some methods exposed, such as populating, updating and deleting functions, to be executed using T-SQL that has been automatically generated by the table schema. Here we can rename wizard-created objects, columns, column property changes and modify queries.

Binding to DataGridView: data presentation and modification

Let's suppose we are in the Windows Forms environment. We have a form, on the top of which we'll create a DataGridView. In that control we want to present our table's contents, being able to modify it if some changes occurs by the user.

The following code will realize those functionalities. We'll start by declaring two new references, the first at the DataSet viewed above and the second one to its TableAdapter, as in the DataSet schema.

Public Class Form1
Dim myDS As New TECHNETDataSet
Dim myTB As New TECHNETDataSetTableAdapters.PeopleTableAdapter
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
myTB.Fill(myDS.People)
DataGridView1.DataSource = myDS.People
End Sub
Private Sub DataGridView1_CellValidated(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValidated
myTB.Update(myDS.People)
End Sub
End Class

On the Form's Load Event, we want to populate our DataSet. To this end, we'll use the TableAdapter Fill method, passing to it the People DataTable as argument. The DataSource will then be set as the DataGridView DataSource. This will generate in the grid the columns that reference the table's fields, showing their contents. We can now modify the presented data.

For the data to be saved, though, this will not suffice. We must tell the TableAdapter to execute an update on the underlying data. We'll use the method Update for this. In the snippet above, the update procedure will take place after the cell contents are validated, in other words when the data the cell contains has been flagged has correct. If we want to execute this operation when the entire row will be validated, we can use the RowValidated event.

Running our example and doing some tests inserting data, modifying them and deleting rows, we'll see that every modification will automatically take place in the original table also.

Binding to ComboBox on single column

Some controls, while possessing the same properties that allows data binding, aren't made for showing the entire range of results. A ComboBox, for example, cannot show all the columns from a table, but only one of them. On a ComboBox, we can set the DataSource in the same way we've done for the DataGridView, but specifying, in the DisplayMember property, the data member that will be exposed.

An example may be as follows:

Public Class Form1
Dim myDS As New TECHNETDataSet
Dim myTB As New TECHNETDataSetTableAdapters.PeopleTableAdapter
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
myTB.Fill(myDS.People)
ComboBox1.DataSource = myDS.People
ComboBox1.DisplayMember = myDS.People.NameColumn.ColumnName
End Sub
End Class

We'll execute the same initialization on DataSet and TableAdapter, proceeding in populating our DataSet as in the previous example. The Datasource property of the ComboBox control is set the same way we've done for the DataGridView and then we set the DisplayMember property using the string that defines the column name (ColumnName), belonging to the column that exposed the Name field (NameColumn) from the People table, resident in our DataSet. This way, running our program, we'll see that the ComboBox elements will be represented by the column Name.

LINQ references

A major characteric of Language-Integrated Queries is the universal availability of its methods, independent of the referenced data source and the powerful filter functions, to select data in a very concise and efficient way. After initializing the references to our DataSet, the LINQ syntax could be used with profit in cases as those we saw.

Stepping back to the DataGridView example and supposing we want to extract and view only those records in which the Name fields starts for "John", we could write a snippet like this:

Public Class Form1
Dim myDS As New TECHNETDataSet
Dim myTB As New TECHNETDataSetTableAdapters.PeopleTableAdapter
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
myTB.Fill(myDS.People)
DataGridView1.DataSource = (From pr As TECHNETDataSet.PeopleRow
In myDS.People
Where pr.Name Like "John*").ToList
End Sub
Private Sub DataGridView1_CellValidated(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValidated
myTB.Update(myDS.People)
End Sub
End Class

Please note that the DataGridView DataSource isn't directly set to a DataTable, but to an extraction of PeopleRow elements, read on the base of a Where clause that search for the string "John*" (being the asterisk the wildcard character) in the Name field. Working on a filtered list doesn't allow us, in this case, to add new items. We can though modify those shown and with the call at the Update method, our changes will be saved.

In the same way, speaking about our ComboBox and wishing to recreate the same conditions as above, an example could be:

Public Class Form1
Dim myDS As New TECHNETDataSet
Dim myTB As New TECHNETDataSetTableAdapters.PeopleTableAdapter
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
myTB.Fill(myDS.People)
ComboBox1.DataSource = (From pr As TECHNETDataSet.PeopleRow
In myDS.People
Select pr.Name).ToList
End Sub
End Class

In this case, knowing we can bind a simple list of strings, we could bypass the matters inherent to DisplayMember adding a further selection in our query. In the last example, we extract all the rows from the table People, further extracting from them the element Name. Obviously, to continue using the DisplayMember property, we could simply write:

ComboBox1.DataSource = (From pr As TECHNETDataSet.PeopleRow
In myDS.People).ToList
ComboBox1.DisplayMember = "Name"

Or, avoiding the extractions of subset of the first selection, demanding to the DisplayMember property the task of showing a specific column.

Data Modifications using LINQ

Let's suppose we wish to do an update without any user interaction. Think about a field that, with predetermined conditions satisfied, must be automatically modified. In our example, we want to change in "Turin" every city for any record in our table. Using LINQ and the methods we saw until now, we can do it without worrying about the connection layer:

Public Class Form1
Dim myDS As New TECHNETDataSet
Dim myTB As New TECHNETDataSetTableAdapters.PeopleTableAdapter
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
myTB.Fill(myDS.People)
Dim results As IEnumerable(Of TECHNETDataSet.PeopleRow) = From p As TECHNETDataSet.PeopleRow In myDS.People
For Each result In results
result.City = "Turin"
Next
myTB.Update(myDS.People)
End Sub
End Class

We have populated our DataSet, extracting from our table an enumeration of PeopleRows. Then, with a For/Each loop, we have changed the field value and, calling on the TableAdapter's Update, we have consolidated our data. Running the example and checking the corresponding property on SQL server-side, we can see the data was successfully modified.

A more LINQ-like method to write the preceding example could be the following, in which we modify the cities in "Milan":

Public Class Form1
Dim myDS As New TECHNETDataSet
Dim myTB As New TECHNETDataSetTableAdapters.PeopleTableAdapter
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
myTB.Fill(myDS.People)
Dim results As IEnumerable(Of TECHNETDataSet.PeopleRow) = (From p As TECHNETDataSet.PeopleRow In myDS.People)
results.ToList.ForEach(Sub(x As TECHNETDataSet.PeopleRow) x.City = "Milan")
myTB.Update(myDS.People)
End Sub
End Class

Finally, let's assume we desire to modify the City field for a specific record. We want, for example, to extract the single record in which the Name contains the string "John" to modify its City in "New York".

Public Class Form1
Dim myDS As New TECHNETDataSet
Dim myTB As New TECHNETDataSetTableAdapters.PeopleTableAdapter
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
myTB.Fill(myDS.People)
Dim p As TECHNETDataSet.PeopleRow = myDS.People.Single(Function(x As TECHNETDataSet.PeopleRow) x.Name.Contains("John") <> 0)
p.City = "New York"
myTB.Update(myDS.People)
End Sub
End Class

In this case, using the Single function, we've extracted a record referred to the condition imposed in the internal function, namely the string "John" to be present in the column Name. Then, referencing the result variable, it will be sufficient to modify the desired property, calling the Table Adapter's Update function.

Bibliography