How to use LINQ to SQL with Stored Procedures in C#

This article demonstrates how to use LINQ to SQL with a stored procedure in a C# Windows Forms application.

Using LINQ to SQL with Stored Procedures in C#

Open Visual Studio, then click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "LinqToSQL" and then click OK

Drag and drop the Label, Combobox and DataGridView controls from the Visual Toolbox onto your form designer, then design your form as shown below.

First, create a stored procedure in your SQL Server database. The following procedure, GetProductByCategoryId, fetches all products from the Products table based on a given CategoryID.

CREATE PROCEDURE [dbo].[GetProductByCategoryId]
(
    @CategoryId INT
)
AS
    SELECT * FROM Products WHERE CategoryID = @CategoryId

In Visual Studio, add a LINQ to SQL Classes file to your project, then drag and drop your Categories table and GetProductByCategoryId stored procedure to the designer surface.

The LINQ to SQL designer will automatically generate methods to invoke the stored procedure and work with the database.

On form load, a LINQ to SQL context (ModelDataContext) retrieves the list of categories from the database.

private void Form1_Load(object sender, EventArgs e)
{
    using (ModelDataContext db = new ModelDataContext())
    {
        // Populate categories from the database
        cboCategory.DataSource = db.Categories.ToList();
        cboCategory.DisplayMember = "CategoryName";
        cboCategory.ValueMember = "CategoryID";
        // Load products for the initially selected category
        Category obj = cboCategory.SelectedItem as Category;
        if (obj != null)
            dataGridView.DataSource = db.GetProductByCategoryId(obj.CategoryID);
    }
}

The cboCategory ComboBox is populated with these categories.

The stored procedure GetProductByCategoryId is called to load products for the initially selected category.

When the user selects a new category from the cboCategory ComboBox, the SelectionChangeCommitted event is triggered.

private void cboCategory_SelectionChangeCommitted(object sender, EventArgs e)
{
    // Handle category change event
    Category obj = cboCategory.SelectedItem as Category;
    if (obj != null)
    {
        using (ModelDataContext db = new ModelDataContext())
        {
            // Fetch and display products for the selected category
            dataGridView.DataSource = db.GetProductByCategoryId(obj.CategoryID);
        }
    }
}

The GetProductByCategoryId stored procedure is called again to fetch products for the new category, and the results are displayed in the dataGridView.

Build and run the application. The ComboBox will display categories, and the DataGridView will show products based on the selected category.

Using LINQ to SQL with stored procedures simplifies database operations while maintaining the benefits of strong typing and query abstraction. This example demonstrates how to integrate stored procedures effectively in a C# application for enhanced database interaction.

VIDEO TUTORIAL