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, create DataSet and TableAdapter classes. We'll see how SQL Server resident data could be exposed through 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 truth: we'll see here how to connect to our database, using DataSource Configuration Wizard, which 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 by an autoincremental Id, and two Varchar fields, Name and City.
From the Visual Studio menù, 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 through 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 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, through 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 through T-SQL, which 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 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 which 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, i.e. 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 which allows data binding, aren't made for show 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 which 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 executed the same initialization on DataSet and TableAdapter, proceeding in populating our DataSet as in the previous example. The Datasource property of ComboBox control is set the same way we've done for the DataGridView, and then we set the DisplayMember property using the string which defines the column name (ColumnName), belonging to the column which 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
The major characterics of Language-Integrated Queries are the univocity of their instructions set, independent from 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, 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 DataGridView DataSource isn't directly set to a DataTable, but to an extraction of PeopleRow elements, read on the base of a Where clause which search for the string "John*" (being the asterisk the wildcard character) in the Name field. Working on a filtered list don'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 inherents 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 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 DisplayMember property the task of showing a specific column.
Data Modifications through LINQ
Let's suppose we wish to perform an update without any user interaction. Think about a field which, 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. Through 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 out DataSet, extracting from our table an enumeration of PeopleRows. Then, with a For/Each loop, we have changed the field value, and - calling on TableAdapter's Update - we have consolidated our data. Running the example, and checking the corresponding property on SQL Server side, we can notice the data were successfully modified.
A more LINQ-like method to write the above 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
Last, let's suppose we desire to modify the City field for a particular record. We want, for example, extract the single record in which 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. After that, referencing the result variable, it will be sufficient to modify the desired property, calling the Table Adapter's Update function.
Bibliography
Other Languages
The present article is available in the following localizations: