ASP.Net – SQL Server Database Connection using LINQ method.

ASP.Net Database Connection using LINQ

LINQ in ASP.Net – Language-Integrated Query
LINQ is a microsoft language-integrated Query tool used in connectivity with database server. The LINQ is available in version .Net Framework 3.5 or higher version.
we can use LINQ in visual studio 2008 or higher version of vs2008.

LINQ is a managed language that use query and table as an object. LINQ provide drag and drop system to use sql table and stored procedures to use in web application.

In this asp.net article we will learn LINQ with an example to integrate LINQ to SQL database (dbml classes) in ASP.Net using C# language.
We learn in this tutorial create dbml classes, connecting to sql server database, bind Table and Stored Procedures.

Here, In this asp.net connectivity example we will learn asp.net with sql server connection using LINQ method. In this asp.net connectivity example we will use Visual Studio 2010 and SQL Server Management Studio 2008.
ASP.Net LINQ Example Video tutorial

The step for connection ASP.Net and SQL Server Database.

Step 1 – Open Visual Studio 2010 Create web application

Step 2 – Open SQL Server Management Studio 2008 Create new database

Step 3 – Create new Table and Stored Procedure in Database

Step 4 – Add LINQ to SQL Classes (DataClasses.dbml) in App_Code folder.

Step 5 – Open Server Explorer and Connect Database

Step 6 – Drag and drop Sql Table and  Stored  Procedures to DataClasses.dbml

Step 7 – Declare DataClassesDataContext for connectivity

 Step 1 – Open Visual Studio 2010 Create web application

Create New web application in visual studio 2010 and design a web form with three Textbox Control, one Button control and a GridView control for display data.

Here, we first Insert Data to SQL-Server and then retrieve data and display data in to GridView control using LINQ.

The asp.net web page LINQ example design layout is:

 Step 2 – Open SQL Server Management Studio 2008 Create new database

In our previous sql tutorials we have already learned to CREATE DATABASE in sql server management studio 2008.
In this ASP.Net LINQ example we have created new database in sql server 2008 with table and stored procedure.

Database Name = MyExample

Step 3 – Create new Table and Stored Procedure in Database

After creating database create table and create stored procedure for insert data and select data.
learn about CREATE STORED PROCEDURE in sql server management studio 2008.

Database Name = MyExample

Table Name = UserMst

Select Stored Procedure = USERMST_SELECT

Insert Stored Procedure = USERMST_INSERT

The sql USERMST_SELECT stored procedure :

CREATE PROCEDURE [dbo].[USERMST_SELECT]
AS
BEGIN

SELECT * FROM UserMst
END

The sql USERMST_INSERT stored procedure :

CREATE PROCEDURE [dbo].[USERMST_INSERT]

@NAME AS NVARCHAR(256),
@CITY AS NVARCHAR(256),
@EMAIL AS NVARCHAR(256)
AS
BEGIN

INSERT INTO UserMst VALUES(@NAME,@CITY,@EMAIL)
END

Step 4 – Add LINQ to SQL Classes (DataClasses.dbml) in App_Code folder.

Now, After design web form Right Click on Solution Explorer —> Add New Item —> LINQ to SQL Classes

Add LINQ to SQL Classes with name DataClasses1.dbml in to App_Code folder in Solution Explorer.

 Step 5 – Open Server Explorer and Connect Database

Now, Open Server Explorer for SQL-Database connection.

Here, we have Database named MyExample and two stored procedure for Insert data and Select data.

Data Connection —> Add Connection select Microsoft SQL Server.

Select database server name from below screen. we have use here SQL  Server, Select Microsoft SQL Server from below screen.

Write sql server name and select database for connection. In this asp.net example write “.\SQLExpress” in server name option and select database “MyExample” in database select option.

After successfully connect sql server we have below screen shows sql table and stored procedure which is already created in sq l server management studio 2008.

The SQL Table = UserMst
Select Stored Procedure = USERMST_SELECT
Insert Stored Procedure = USERMST_INSERT

 

Step 6 – Drag and drop Sql Table and  Stored  Procedures to DataClasses.dbml

After doing this Open the LINQ to SQL Classes DataClasses.dbml then Drag and Drop SQL Table and SQL strode procedure in it like shows below screen.

 

Step 7 – Declare DataClassesDataContext for connectitivty

Now, write the C# server side code for Insert data to sql server and then Select data from SQL Server.

write below code on Button Click event at code behind page:

DataClassesDataContext ctx = new DataClassesDataContext();
ctx.USERMST_INSERT(txtname.Text, txtcity.Text, txtemail.Text);

GridView1.DataSource = ctx.USERMST_SELECT();
GridView1.DataBind();

The output of ASP.Net LINQ Connection Example.

I hope this LINQ connectivity with ASP.Net and SQL Server will help you..