Here are the steps,
- Create an Empty Web Form Application with C#.
- Switch ON server explorer: By default Its ON and Visible at toolbox side on, if not then CTRL+W, L.
- Select Add connection to database or create a new SQL Server database from Server Explorer.
- Create a DBML file that is LINQ to SQL File.
- Double click on DBML. I have given the following file name: MemberCDAC_DataClasses.DBML
- Drag and Drop tblMembers on MemberCDAC_Database.DBML from Server Explorer
- Click on MemberID column of tblMember and check property window for detail.
Access: Public and Auto Generated Value : True.
You can set setting in property window. If any changes in table, then again drag n drop or update manually. Personally, I suggest delete table link from DBML and again drag and drop.
- When you configure server connection, drag and drop table into DBML canvas then system will automatically add connection string in WEB.CONFIG file.
- Web.Config file contains the following: I have used WINDOWS authentication connectionstrings.
- <configuration>
- <connectionStrings>
- <add name="MemberCDACConnectionString" connectionString="Data Source=SAIBABA-PC\SAIBABA;Initial Catalog=MemberCDAC;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
- <system.web>
- <compilation debug="true" targetFramework="4.5" />
- <httpRuntime targetFramework="4.5" /> </system.web>
- </configuration>
- For explaining LINQ 2 SQL, I have used a webform with the following controls as in the following:
Here's the aspx page.
Controls I have used:
Control Type | Control Used | Description |
Label | lblMemberID | Member ID display from table. |
TextBox | txtName | Get and Set Member Name. |
TextBox | txtAddress | Get and Set Member Address. Change Following Property: TextMode : MultiLine |
TextBox | txtPlace | Get and Set Member Place. |
TextBox | txtJoinDate | Get and Set Member Join Date. |
Button | btnSave | Dynamically used and rename this button for Save : An new entry saved in table. Update : For update existing entry. |
GridView | GridView1 | GridView to display records and functional button for Select, New and Delete activities. |
- The following LINQ 2 SQL queries used:
- var db = new MemberCDAC_DataClassesDataContext();
db is instance created of MemberCDAC_DataClassesDataContext.
db take care of Select, Insert and Update and Delete activities.
TYPE OF QUERY | Query Text | USED IN EVENT | DESCRIPTION |
Select Query – Style 1 | from a in db.tblMembers select a).ToList(); | ( Created a BindGridView() method, which used for loand and refresh data from table. | GridView1.DataSource = (from a in db.tblMembers select a).ToList(); GridView1.DataBind(); GridView used this query directly as datasource. |
Select Query – Style 2 | db.tblMembers.ToList(); | Created a BindGridView() method, which used for loand and refresh data from table. | GridView1.DataSource = db.tblMembers.ToList(); GridView1.DataBind(); |
Save Query | //Insert new record in tblmembers db.tblMembers.InsertOnSubmit(NewMember); //Update table db.SubmitChanges(); | btnSave_Click | Syntax: db.TableName.InsertOnSubmit(Class Object) Example: db.tblMembers.InsertOnSubmit(NewMember); above command insert a new row in table. db.SubmitChanges(); This will save and update table. |
Update Query | int SelectMemberID = Convert.ToInt16(lblMemberID.Text); var NewMember = (from a in db.tblMembers where a.MemberID == Convert.ToInt16(lblMemberID.Text) select a ).FirstOrDefault();
NewMember.Name = txtName.Text; NewMember.address =txtAddress.Text; NewMember.place = txtPlace.Text; NewMember.joindate = DateTime.ParseExact(txtJoinDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture); //Update table
db.SubmitChanges(); | btnSave_Click | Syntax: For update command , first we have to create filled instance of Member object , then update current value from control like TextBox etc...
var NewMember = (from a in db.tblMembers where a.MemberID == Convert.ToInt16(lblMemberID.Text) select a ).FirstOrDefault();
NewMember.Name = txtName.Text; db.SubmitChanges(); This will delete record from table. |
Delete Query | db.tblMembers.DeleteOnSubmit(MemberDetail); db.SubmitChanges(); | GridView1_RowCommand | Syntax: db.TableName.DeleteOnSubmit(Class Object)
Example: db.tblMembers.DeleteOnSubmit(NewMember); above command will mark a delete row in table. db.SubmitChanges(); This will delete record from table. |
db.SubmitChanges(): This command required to execute on after activity like Insert, Update, Delete.
Recap Linq To SQL queries:
- Fetching All Members Records
- (from a in db.tblMembers select a).ToList();
- Fetching a Particular Member Record
- (from a in db.tblMembers where a.MemberID == TMemberID select a).FirstOrDefault();
- Insert Query
-
- db.tblMembers.InsertOnSubmit(NewMember);
-
-
- db.SubmitChanges();
Note: NewMember : Filled Class object with Datas.
- Update Query
Firstly, you have to create object of member.
- Var NewMember =(from a in db.tblMembers where a.MemberID == TMemberID select a).FirstOrDefault();
- NewMember.Name = txtName.Text;
- NewMember.address = txtAddress.Text;
- NewMember.place = txtPlace.Text;
- NewMember.joindate = DateTime.ParseExact(txtJoinDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);
-
-
- db.SubmitChanges();
- Delete Query
- db.tblMembers.DeleteOnSubmit(MemberDetail);
- db.SubmitChanges();
Code Behind File for Your Reference
SQL Table Script for Your Reference
- USE [MemberCDAC]
- GO
- /****** Object: Table [dbo].[tblMembers] Script Date: 11/29/2015 20:38:53 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[tblMembers](
- [MemberID] [int] IDENTITY(1,1) NOT NULL,
- [Name] [nvarchar](50) NULL,
- [address] [nvarchar](500) NULL,
- [place] [nvarchar](50) NULL,
- [joindate] [datetime] NULL,
- CONSTRAINT [PK_tblMembers] PRIMARY KEY CLUSTERED
- (
- [MemberID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
WebApplication View