Introduction of LINQ to SQL
LINQ to SQL, previously know as DLINQ, is an API to access databases in .NET 3.5. This article shows how you can connect to a database, get data from a database table, and display it in a DataGridView control. The attached project also shows how to add, update, and delete data through a DataGridView using LINQ to SQL.
Create a Windows Forms application in Visual Studio 2005 and drag a DataGridView control on it, name it authorDataGridView and follow these steps.
Note: To run this sample, you must have .NET 3.0 and LINQ installed on your machine. If you are using Visual Studio 2008, LINQ is already installed on your machine.
Data Fetching and Data Binding
In LINQ to SQL, the DataContext object is responsible for establishing a connection with a data source. The simplest way to create a connection is by passing a database path in the DataContext constructor.
In the following code, you need to change the path of the database to your local path.
// DataContext takes a path of a database for data connection
DataContext db = new DataContext(@"C:\Mahesh\Code\DB2Linq\DB2Linq\Authors.mdf");
The next step is to create a Table object. This is the object that will be used to return the data. The following code creates an Author table. Our database has a table called Author.
// Get a typed table
Table<Author> authors = db.GetTable<Author>();
Now we need to select the data from the Author table using LINQ's from..in..select query. In the following code, we select all records from the Author table in allAuthors type of var.
// Get all authors
var allAuthors = from auth in authors select auth;
Next, I drag and drop a BindingSource on the Form and name it authorBindingSource. After that, I set its DataSource property to allAuthors.
// Bind to a DataGrid
authorBindingSource.DataSource = allAuthors;
Finally, we set DataGridView's DataSource property to BindingSource.
// Bind to a DataGridView control
authorDataGridView.DataSource = authorBindingSource;
The complete source code is listed in Listing 1. You can write it on a button click or on Form's load event handler.
// DataContext takes a path of a database for data connection
DataContext db = new DataContext(@"C:\Mahesh\Code\DB2Linq\DB2Linq\Authors.mdf");
// Get a typed table
Table<Author> authors = db.GetTable<Author>();
// Get all authors
var allAuthors = from auth in authors select auth;
// Bind to a DataGrid
authorBindingSource.DataSource = allAuthors;
// Bind to a DataGridView control
authorDataGridView.DataSource = authorBindingSource;
Listing 1.
Add, Update and Delete Data
Adding, updating, and deleting data is just a matter of adding, updating, and deleting data in the collection and saving changes back to the database.
The following code snippet in Listing 2, creates an Author object and adds it to the authors collection using the Add method and saves changes to the database by calling SubmitChanges method of DataContext.
// Get a typed table
Author auth = new Author();
auth.Name = "Mahesh Chand";
auth.Age = 40;
auth.Male = false;
authors.Add(auth);
db.SubmitChanges();
Listing 2.
The following code snippet in Listing 3, deletes an Author from the authors collection using the Remove method and saves changes to the database by calling SubmitChanges method of DataContext.
authors = db.GetTable<Author>();
int age = 32;
var auth = authors.Single(a => a.Age == age);
authors.Remove(auth);
db.SubmitChanges();
Listing 3.
The Application
Now let's create a full-fledged application. In this application, I will load data from the database, display data in a DataGridView control, and will add and delete data.
The main application looks like Figure 1. This application shows authors data including authors' photo. name, age, male or female, joined date, and bio. So this grid shows how to display and manipulate images, text, boolean, and date types using DLINQ.

Figure 1.
Add a new Author button is used to add a new author to the authors list. Clicking on this button opens a new sub form called AuthorInfo that is used to enter the author data. Listing 4 shows the code looks like on AddNew button click event handler.
private void AddNewButton_Click(object sender, EventArgs e)
{
AuthorInfo ainfo = new AuthorInfo();
ainfo.Show();
}
Listing 4.
As you can see in Figure 2. The Form also provides options to browse and display author photo.

Figure 2.
Clicking Done button adds the author data to the database and displays on the main form. The Done button click event handler looks like Listing 5.
private void DoneButtonClick(object sender, EventArgs e)
{
byte[] imgBytes;
Int32 age;
// Create an Author object
Author newAuth = new Author();
// Generate and set a GUID
newAuth.ID = Guid.NewGuid();
// Make sure age is valid
if (AgeTextBox.Text.Length <= 0 )
{
MessageBox.Show("Enter a valid age!");
AgeTextBox.Select();
return;
}
try
{
age = Int32.Parse(AgeTextBox.Text);
}
catch (Exception exp)
{
MessageBox.Show("Enter a valid age!");
AgeTextBox.Select();
return;
}
// Set Age property


Join the conversation! Your thoughts help the community grow.