LINQ With Data Source ( MSSQL To LINQ )

Submitted by: 
Language: 
Visitors have accessed this post 2124 times.

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.

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.