Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

Data Manipulation from SQL Server Source Through Controls and LINQ

Rate me:
Please Sign up or sign in to vote.
4.31/5 (4 votes)
4 Jan 2015CPOL7 min read 14.4K   166   5  
How to access, display, modify SQL Server data (tables, etc.) through Windows Forms controls and LINQ instructions, using DataSets and TableAdapters

Introduction

In this article, we'll see how to make a SQL Server resident table available to our application. We'll use, aside from Visual Studio Wizard to connect to the instance, 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 the 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 occur 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.

VB.NET
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 is 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 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 which will be exposed.

An example may be as follows:

VB.NET
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've 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 characteristics 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 start for "John", we could write a snippet like this:

VB.NET
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 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:

VB.NET
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 DisplayMember property, we could simply write:

VB.NET
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:

VB.NET
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 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":

VB.NET
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, to extract the single record in which Name contains the string "John" to modify its City in "New York".

VB.NET
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

History

  • 2015-01-04: Added source code to article (with sample database backup)
  • 2015-01-04: First release for CodeProject

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Italy Italy
Working in IT since 2003 as Software Developer for Essetre Srl, a company in Northern Italy.
I was awarded in 2014, 2015 and 2016 with Microsoft MVP, for Visual Studio and Development Technologies expertise. My technology interests and main skills are in .NET Framework, Visual Basic, Visual C# and SQL Server, but i'm proficient in PHP and MySQL also.

Comments and Discussions

 
-- There are no messages in this forum --