LINQ With Data Source ( MSSQL To LINQ )

[kat]

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

1. CREATE
   
   TABLE
   
   [
   
   dbo]
   
   .
   
   [
   
   tbl_client]
   
   (
   
2. [
   
   id]
   
   [
   
   INT
   
   ]
   
   IDENTITY
   
   (
   
   1
   
   ,
   
   1
   
   )
   
   NOT
   
   NULL
   
   ,
   
3. [
   
   client_name]
   
   [
   
   VARCHAR
   
   ]
   
   (
   
   150
   
   )
   
   NULL
   
   ,
   
4. [
   
   client_orgenization]
   
   [
   
   VARCHAR
   
   ]
   
   (
   
   150
   
   )
   
   NULL
   
   ,
   
5. [
   
   email]
   
   [
   
   VARCHAR
   
   ]
   
   (
   
   100
   
   )
   
   NULL
   
   ,
   
6. [
   
   telephone]
   
   [
   
   VARCHAR
   
   ]
   
   (
   
   100
   
   )
   
   NULL
   
   ,
   
7. CONSTRAINT
   
   [
   
   PK_tbl_client]
   
   PRIMARY
   
   KEY
   
   CLUSTERED
   
8. (
   
9. [
   
   id]
   
   ASC
   
10. )
    
    WITH
    
    (
    
    PAD_INDEX =
    
    OFF,
    
    STATISTICS_NORECOMPUTE =
    
    OFF,
    
    IGNORE_DUP_KEY =
    
    OFF,
    
    ALLOW_ROW_LOCKS =
    
    ON
    
    ,
    
    ALLOW_PAGE_LOCKS =
    
    ON
    
    )
    
    ON
    
    [
    
    PRIMARY
    
    ]
    
11. )
    
    ON
    
    [
    
    PRIMARY
    
    ]
    
12. 
    
13. 
    
14. CREATE
    
    TABLE
    
    [
    
    dbo]
    
    .
    
    [
    
    tbl_project]
    
    (
    
15. [
    
    id]
    
    [
    
    INT
    
    ]
    
    IDENTITY
    
    (
    
    1
    
    ,
    
    1
    
    )
    
    NOT
    
    NULL
    
    ,
    
16. [
    
    project_name]
    
    [
    
    VARCHAR
    
    ]
    
    (
    
    150
    
    )
    
    NULL
    
    ,
    
17. [
    
    start_date]
    
    [
    
    DATE
    
    ]
    
    NULL
    
    ,
    
18. [
    
    end_date]
    
    [
    
    DATE
    
    ]
    
    NULL
    
    ,
    
19. [
    
    client_id]
    
    [
    
    INT
    
    ]
    
    NULL
    
    ,
    
20. CONSTRAINT
    
    [
    
    PK_tbl_project]
    
    PRIMARY
    
    KEY
    
    CLUSTERED
    
21. (
    
22. [
    
    id]
    
    ASC
    
23. )
    
    WITH
    
    (
    
    PAD_INDEX =
    
    OFF,
    
    STATISTICS_NORECOMPUTE =
    
    OFF,
    
    IGNORE_DUP_KEY =
    
    OFF,
    
    ALLOW_ROW_LOCKS =
    
    ON
    
    ,
    
    ALLOW_PAGE_LOCKS =
    
    ON
    
    )
    
    ON
    
    [
    
    PRIMARY
    
    ]
    
24. )
    
    ON
    
    [
    
    PRIMARY
    
    ]
    
25. 
    
26. 
    
27. 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

1. TestDataContext linqClient =
   
   new
   
   TestDataContext(
   
   )
   
   ;
   
2. 
   
3. var
   
   clients =
   
   from
   
   client in
   
   linqClient.
   
   tbl_clients
   
4. select
   
   new
   
   
   
5. {
   
6. client.
   
   id
   
   ,
   
7. client.
   
   client_name
   
   ,
   
8. client.
   
   client_orgenization
   
   ,
   
9. client.
   
   email
   
   ,
   
10. client.
    
    telephone
    
11. }
    
    ;
    
12. 
    
13. 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.

Download