LINQ With Data Source ( MSSQL To LINQ )

Submitted by thusitcp on Saturday, October 18, 2014 - 10:22.

Language

My previous tutorial I have described basic of LINQ and how to dealing with XML files. In this tutorial I am going to explain you how to deal with rational data sources. In this tutorial you will be understand how does LINQ help us dealing with non-object oriented data structures like dealing with rational data. Here we are going to connect SQL Server database and going to create SQL to LINQ data source. To do that at first we have to create DBML file which contains c# code which is helping write LINQ queries. We are creating a databse called TEST and it has following two tables

CREATE TABLE [dbo].[tbl_client](
        [id] [INT] IDENTITY(1,1) NOT NULL,
        [client_name] [VARCHAR](150) NULL,
        [client_orgenization] [VARCHAR](150) NULL,
        [email] [VARCHAR](100) NULL,
        [telephone] [VARCHAR](100) NULL,
 CONSTRAINT [PK_tbl_client] PRIMARY KEY CLUSTERED 
(
        [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
 
CREATE TABLE [dbo].[tbl_project](
        [id] [INT] IDENTITY(1,1) NOT NULL,
        [project_name] [VARCHAR](150) NULL,
        [start_date] [DATE] NULL,
        [end_date] [DATE] NULL,
        [client_id] [INT] NULL,
 CONSTRAINT [PK_tbl_project] PRIMARY KEY CLUSTERED 
(
        [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
 
THEN GO TO the visual studio AND CREATE NEW windows project called Test

Then we need to add out database into our test project Go to Tools - > connect to database Add required database connection parameters. After successfully added database in to our project, we need to DBML file. There are few object relations mapping available but they have their own pros and cons. LINQ is one of the best object relation mapping technologies available in computer world. Now we are going to create DBML file. Create a new folder called TESTSQL in our project. Then right click on it and select Add New Item on opening menu Select LINQ To SQL Class on new item menu give Name as Test and click add Then double click on DBML file Then Right click on server explorer and Drag the tables in to DBML Left pane. Then you can see the table with their relation. After that drag the stores procedures to right pane, now you can see the methods. When you are create Test.dbml file it will add related context class that can be used to access tables and soared procedures

  TestDataContext linqClient = new TestDataContext();
 
            var clients = from client in linqClient.tbl_clients
                          select new
                          {
                              client.id,
                              client.client_name,
                              client.client_orgenization,
                              client.email,
                              client.telephone
                          };
 
            grdclient.DataSource = clients;

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

121 views