Linq To sql in Dotnet

Creating a Database and Connection String

To Start with, we will need a new database with a table named Users. If you would like to use a users table which you already have, simply change the table and entity names.
A connection String is definitely a requirement, so what you need to do is to open the Web.Config file and paste the below into the connectionStrings Settings and alter the Source and Catalog according to your database :
<connectionStrings>
<add name=”LINQConnectionString” providerName=”System.Data.SqlClient” connectionString=”Data Source=SERVER-PC\SQLEXPRESS;Initial Catalog=DatabaseNAME;Integrated Security=True”/>
</connectionStrings>

Creating a LINQ to SLQ class to Map Tables

The next thing we need to do is to create a ContextData file using the Object Relational Designer. In order to do so, create a new item, select LINQ to SQL classes (as shown in fig 1 below) and name it myContextDataFile.dbml.

Once the ContextData file has been created we must map all the tables in our database to this file by simply opening the Object Relational Designer and drag all the tables onto the left hand side of the designer and save. This will create all the mappings and settings for each table and their entities.

Creating a Form to Insert Update and Delete

Create a new ASPX page and name as desired. In order to insert the details we will add few text boxes and buttons and if you would like to see your changes add a GridView. If you are in a hurry copy the code below into the html page:
<form id=”form1″ runat=”server”>
<div>

Username: <asp:TextBox ID=”txt_username” runat=”server” /><br />

First Name: <asp:TextBox ID=”txt_firstName” runat=”server” /><br />

Last Name: <asp:TextBox ID=”txt_LastName” runat=”server” /><br />

<asp:Button ID=”btnAdd” runat=”server” Text=”Add” onclick=”btnAdd_Click” />

<asp:Button ID=”btnDelete” runat=”server” Text=”Delete” onclick=”btnDelete_Click” />

<asp:Button ID=”btnUpdate” runat=”server” onclick=”BtnUpdate_Click” Text=”Update” />        <br />

<asp:GridView ID=”GridView1″ runat=”server” DataSourceID=”LinqDataSource1″>    </asp:GridView>

<asp:LinqDataSource ID=”LinqDataSource1″ runat=”server”       ContextTypeName=”LinQ_Tutorial1.myContextDataFile” EnableDelete=”True” EnableInsert=”True” TableName=”Users”>        </asp:LinqDataSource>

</div>
</form>
For future reference the LINQ Data Source is obtained by dragging it from Data Section from the ToolBox.
In order to connect it to the Mapping file, click on the LinqDataSource Box arrow and Configure Data Source. A new window should pop up, from the drop down list select the ContextData file we created name myContextDataFile.

Implementing the LinQ through Code Behind

We should start by checking some required imports we will need such as:
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
1. First of, let us declare a connectionString first thing on top before the page_load:
private string connectionString;
2.
Declare the connectionString in a protected void method:
protected void Connection()
{

connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["LINQConnectionString"].ToString();

}
3. Create the events of the 3 buttons, Add, Update and Delete by double clicking on the buttons in design view.
4. ADD NEW USER:
protected void btnAdd_Click(object sender, EventArgs e)
{

Connection();

// data mapping object to our database.

myContextDataFile db = new myContextDataFile(connectionString);

//Insert New Row

User newUser = new User();  // CREATE AN INSTANCE

newUser.Username = txt_username.Text;

newUser.UserFirstName = txt_firstName.Text;

newUser.UserLastName = txt_LastName.Text;

db.Users.InsertOnSubmit(newUser);

db.SubmitChanges();

//The two lines of code above are the most important since they are the actual command to insert a new row into the table

}
5. UPDATE AN EXISTING USER
protected void btnUpdate_Click(object sender, EventArgs e)
{

Connection();

// data mapping object to our database.

myContextDataFile db = new myContextDataFile(connectionString);

//Update a user

User editUser = db.Users.Single(u => u.Username == txt_username.Text); //To edit user that matches the Username
editUser.UserFirstName = txt_firstName.Text;

editUser.UserLastName = txt_LastName.Text;

db.SubmitChanges();

//The last line of code above is the code that saves changes to the row that matches the Username given

}
6. DELETE AN EXISTING USER
protected void btnDelete_Click(object sender, EventArgs e)

Connection();

// data mapping object to our database.

myContextDataFile db = new myContextDataFile(connectionString);

//Update a user

string UsernameDelete = txt_username.Text;

User toDelete = db.Users.Single(p => p.Username == UsernameDelete);  //Delete user that matches Username

db.Users.DeleteOnSubmit(toDelete);

db.SubmitChanges();

//The last two lines of code above is the code that deletes the row that matches the Username given

7. Verifying and Testing actions performed throught Grid View without using the LinqDataSource
Drag a grid view anywhere on the same page, in this example it had not been renamed so its name is GridView1
In the code behind, add the following namespace:

using System.Data.Common;

Finally, in the page_load copy and paste the following: (edit if table name is different)

myContextDataFile db = new myContextDataFile();

//simple query
var results =
from users in db.Users
select users;

//database command object
DbCommand dc = db.GetCommand(results);

GridView1.DataSource = results;

GridView1.DataBind();

Enjoy