LinQ to SQL simple Insert Update and Delete

Since I was given a task to implement new technologies, I came about selecting LinQ. It was quite hard to find a simple place or tutorial where I could find how to do simple queries without complexities. Therefore I decided to post a blog to describe as simple as can be the process of inserting updating and deleting from LinQ to SQL.

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 😉


(adsbygoogle = window.adsbygoogle || []).push({});

13 thoughts on “LinQ to SQL simple Insert Update and Delete

    • Thanks so much for posting this. I thought I did everything correctly but when I build my project I received this error message: “The type or namespace name ‘myContextDataFile’ could not be found, are you missing….”

      Would you please tell me what I am doing wrong? Thanks –

  1. Error rectified after changing following things worked fine thanks

    myContextDataFile as myContextDataFileDataContext (which is a class name generating automatically in myContextDataFile.cs)

    and if u not get context name while configuring linqdatasource just do it in by typing as

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s