CRUD operations with LINQ To SQL(ASP.NET 3.5)

Hi Readers,

Thank you for you emails.I appreciate it.
Here are some tips if you want to use LINK To SQL to select, insert,
delete and update known by CRUD.
I used ASP.NET AJAX web Template for this demo and VS 2008
Here are steps:

1-Add a new table called Users to Northwind database :

CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL,
[Email] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



2-Create ASP.NET AJAX web app and make sure ScriptMaster is in place(without it
ajax functionality=null).Make also sure that Ajax ToolKit controls are in place.
Drop Tab Container on the design surface with 4 tabs:
-Query Task tab: to query and show result on GridView control
-Insert Task tab : to insert new records .
-Update Task tab: to update records
-Delete Task tab: to delete record.



Copy the source code Demo.Zip



Here is how it works:



A-For Query Task:



The goal of this task is to load data into GridView control via a button control:



' This code will query all users where UserName is not null

Dim users=From usr In db.Users _

Where usr.UserName IsNot Nothing _

Select usr

'This code bind users data to GridView control



Me.GridView1.DataSource=users

Me.GridView1.DataBind()



B-For Insert Task:



'I defined a new User class and i used the column names

'you need With which allows you select column names

'after you type .(dot) inside {} column names will appear automatically



Dim u as New User With {.UserName=me.txtUserName,.PassWord=me.txtPassWord,.Email=me.txtEmail}



'Now submit result on Insert and Update table

db.Users.InsertOnSubmit(u)

db.SubmitChanges()



C-For Update Task:



'This section is little loosy

'I select users where UserID=upUserID.txt

'You need these parentheses to make it work

'You need to specifiy single meaning you are

'updating only a single row

'match columns again fields

'finally update user table



Dim u=(From usr In db.Users _

Where usr.UserID=me.upUserID.txt _

Select usr).Single

u.UserName=me.upUserName.text

u.PassWord=me.upPassWord.text

u.Email=me.upEmail.text



db.SubmitOnChanges()



D-Delete Task:



'Tricky too

Dim p1=db.Users.First(Function(p) p.UserID=me.delUserID.text

db.Users.DeleteOnSubmit(p1)

db.SubmitChanges()



Run app and if everything is ok you should see something like this: