Geeks With Blogs

News

Microsoft MVP


DZone MVB


Moderator at CodeASP.NET


Quiz Master







free counters
Free counters
Added on January 19,2012


Follow Me @vmsdurano

A bit About Me



Disclaimer
The opinions expressed herein are my own personal opinions and does not represent the opinions of my employers. Nor does it represent the opinion of my dog, because I don’t have one.


Vinz' Blog (ProudMonkey) "Code, Beer and Music ~ my way of being a programmer"

Few months ago, I’ve created a simple demo about “Creating a Simple Registration Form using the ADO.NET way”. In this article, I'm going to demonstrate how to create a simple form that would allows users to insert data to the database using L2S.

 

As an overview, LINQ to SQL is a technology that allow you to query sql server. LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes.  You can then query the database using LINQ, as well as update/insert/delete data from it.

 

I will not cover much on details about it in this article so if you need to know more about this technology then you can refer to this link: http://msdn.microsoft.com/en-us/library/bb425822.aspx

 

STEP 1: Creating a new Website in Visual Studio

 

To get started then lets go ahead and fire up Visual Studio 2008 and create a new WebSite by selecting File > New WebSite.

 

STEP 2: Adding a DBML file

 

Since we are going to use L2S then we need to add .dbml file. To do this, just right click on the application root and select Add New Item. On the template select LINQ to SQL Classes file. See below screen shot:

 


 

Now rename your dbml file the way you want it and then click OK. Note that I’m using the Northwind database for this demo and on that case I renamed the dbml file to Northwind to make it friendlier.

 

Now open up server explorer in Visual Studio and browse the database that you wan’t to work on (in this case the Northwind database). Just for the purpose of this example I’m going to use the Customers table from the northwind database and drag it to the Northwind.dbml design surface. See the screen shot below:

 


 

That’s simple! Isn’t it?

 

What happens there is that by time you drag a table in the design surface, L2S will automatically generates the Business object for you within the DataContext and let you query against it.The DataContext is the main gateway by which you retrieve objects from the database and resubmit changes. You use it in the same way that you would use an ADO.NET Connection. In fact, the DataContext is initialized with a connection or connection string you supply. The purpose of the DataContext is to translate your requests for objects into SQL queries made against the database and then assemble objects out of the results. The DataContext enables language-integrated query by implementing the same operator pattern as the standard query operators such as Where and Select

 

STEP 3: Setting up the GUI

 

Now let’s go ahead and create our form for data entry. For the simplicity of this demo, I just set up the form like below:

 

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Untitled Page</title>

    <style type="text/css">

        .style1{width: 400px;}

        .style1 td {width:200px;}

    </style>

</head>

<body>

    <form id="form1" runat="server">

    <asp:Literal ID="LiteralMessage" runat="server"></asp:Literal>

    <table class="style1">

        <tr>

            <td>Company ID</td>

            <td><asp:TextBox ID="TextBoxID" runat="server"/></td>

        </tr>

        <tr>

            <td>Company Name</td>

            <td><asp:TextBox ID="TextBoxCompanyName" runat="server"/></td>

        </tr>

        <tr>

            <td>Contact Name</td>

            <td><asp:TextBox ID="TextBoxContactName" runat="server"/></td>

        </tr>

        <tr>

            <td>Contact Title</td>

            <td><asp:TextBox ID="TextBoxContactTitle" runat="server"/></td>

        </tr>

        <tr>

            <td>Address</td>

            <td><asp:TextBox ID="TextBoxAddress" runat="server"/></td>

        </tr>

        <tr>

            <td>City</td>

            <td><asp:TextBox ID="TextBoxCity" runat="server"/></td>

        </tr>

        <tr>

            <td>Region</td>

            <td><asp:TextBox ID="TextBoxRegion" runat="server"/></td>

        </tr>

        <tr>

            <td>Postal Code</td>

            <td><asp:TextBox ID="TextBoxPostalCode" runat="server"/></td>

        </tr>

        <tr>

            <td>Country</td>

            <td><asp:TextBox ID="TextBoxCountry" runat="server"/></td>

        </tr>

    </table>

    <asp:Button ID="Button1" runat="server" Text="Save" onclick="Button1_Click" />

    </form>

</body>

</html>

 

STEP 4: Creating the SaveCustomerInfo() method

 

After setting up our GUI then let’s go ahead and create the method for inserting the data to the database using L2S. Here are the code blocks below:

 

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Xml.Linq;

 

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

 

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        SaveCustomerInfo();

    }

 

    private void SaveCustomerInfo()

    {

        using (NorthwindDataContext context = new NorthwindDataContext())

        {

            //Create a new instance of the Customer object

            Customer cust = new Customer();

            //Add new values to each fields

            cust.CustomerID = TextBoxID.Text;

            cust.CompanyName = TextBoxCompanyName.Text;

            cust.ContactName = TextBoxContactName.Text;

            cust.ContactTitle = TextBoxContactTitle.Text;

            cust.Address = TextBoxAddress.Text;

            cust.City = TextBoxCity.Text;

            cust.Region = TextBoxRegion.Text;

            cust.PostalCode = TextBoxPostalCode.Text;

            cust.Country = TextBoxCountry.Text;

 

            //Insert the new Customer object

            context.Customers.InsertOnSubmit(cust);

            //Sumbit changes to the database

            context.SubmitChanges();

 

            //Display Message for successful operation

            LiteralMessage.Text = "<p style='color:Green;'>Information Successfully saved!</p>";

        }

    }

}

 

 

As you can see, the code above was very straight forward. First we have created a new instance of the DataContext which we had created on STEP 2 and wrapped it inside the “using” block; this is to ensure that the DataContext will be disposed after its processing. Second we created a new instance of the Customer object that was defined within the DataContext, this object has properties which will be filled with values that comes from the user inputs. Third we inserted a new Customer object to the Customers set and then call the context.SubmitChanges to update our database. Lastly, L2S will do the rest for you ;).

 

Note: The Customer and Customers set objects are automatically created once you’ve added the Customer table in the .dmbl design surface.

 

STEP 5: Run the code

 

Running the code above will look something like below on the browser:

 

 

From there we can fill in those fields with values we want. Just for this demo, notice that I have filled in those fields with a sample data. Hitting the save button will invoke the method SaveCustomerInfo() which is responsible for doing the insert operation. Now if we look at the database we can see that the data we entered was successfully being saved to the database. See the screen shot below:

 


 

Cool right? ;)

 

Okay, I know that you have few questions that pops in your mind now and these are:

 

·       What happened behind the scene? How does it actually save the data to the database?

·       How does the query being constructed? Does it handle SQL Injection?

·       How does the connection string being set up? What If I want to set the connection string manually?

·       Does L2S always open the connection to the database once we created a new instance of the DataContext?

 

To answer the questions that you have in mind then I would suggest you to give this FAQ a read: http://msdn.microsoft.com/en-us/library/bb386929.aspx


I hope someone find this useful!

Technorati Tags: ,,
Posted on Thursday, March 11, 2010 3:12 PM ASP.NET , C# , LINQ to SQL | Back to top


Comments on this post: Inserting Data to Database using LINQ to SQL

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
Thanks, i find this useful.
Could you please help me with the code to update
Left by ID on Mar 24, 2010 7:53 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
Thanks for taking time out to write this article for LINQ.

The best part of this article it that you have kept this article simple.
Left by Nitin kukreja on Apr 29, 2010 11:12 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
Hi Vinz,

mabuhay, and thank you for your blog! It has been very helpful so far.

Assuming that I'm creating an "insert" form similar to the one at the top, but with one of the values required being a foreign key to the table, it would be better to use a drop-down list for the FK. I would like to know how to code the drop-down list as I keep getting errors. Thanks.

Sincerely,
Fr. Collin Nunis
Left by Collin Michael Nunis on May 30, 2010 9:50 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
Hello
This blog is so much helpful for me but one thing that i want to know is that if i take "postalcode" datatype to "int" in database then how can i convert this textbox value to int???
I tried something like this but it is not working:
cust.PostalCode = Convert.ToInt32(TextBoxPostalCode.Text);

Also i want to know the datetime conversion!
Thanks!
Left by mohsin on Jun 10, 2010 9:48 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
thanks for tutorial, it really help me, so what about editing data with linq to sql??and if there a null value, have a message that the field of...is null, please insert,,and if the data is duplicate ex custumer id, have a message could not a duplicate data,,i need your help..thank you...^___^
Left by rhiena on Aug 07, 2010 9:46 AM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
@mohsin,

When converting string to int or datetime type then make sure that the value to be converted has a vaild datetime or int value or else it will fail. To avoid exceptions you may try using TryParse instead.
Left by Vinz on Dec 21, 2010 8:07 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
@rhiena,

Take a look at this post:
http://geekswithblogs.net/dotNETvinz/archive/2010/05/05/editing-updating-and-deleting-data-in-the-form-using-linq.aspx
Left by Vinz on Dec 21, 2010 8:07 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
i m having this error so can u plz help me...


error CS0411: The type arguments for method 'System.Data.Linq.Table<USRPASS>.InsertAllOnSubmit<TSubEntity>(System.Collections.Generic.IEnumerable<TSubEntity>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.
Left by mickel on Feb 24, 2011 5:33 AM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
//Insert the new Customer object

context.Customers.InsertOnSubmit(cust);

In the above line of code
from where do u get the InsertOnSubmit(); function????

is it a stored procedure or added automatically by the entity framework?
Left by Ashutosh Singh on Jun 10, 2011 10:00 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
@Ashutosh Singh,

Did you used L2S or EF? InsertOnSubmit() Is a built-in method for Linq 2 SQL.
Left by Vinz on Jun 10, 2011 11:52 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
Thankyou very much for this blog.

I want to required gridview after complition of submitting data and extra functionality like edit update & delete with help of GridView_RowCommand event,GridView_edit event,GridView_delete event,GridView_paging event,and more field like based on two Drop down list
one of base on first E.G. state and City,and second one split function (three dropdown)

please reply me as soon as possible.
Left by ikhradi on Aug 18, 2011 2:48 AM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
Compiler Error Message: CS0246: The type or namespace name 'DataClassesDataContext' could not be found (are you missing a using directive or an assembly reference?)
Left by deepak on Sep 09, 2011 1:04 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
your tutorial is fine
Left by anil gupta on Nov 04, 2011 5:42 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
Was a good help in solving the problem
Left by vini on Dec 20, 2011 6:19 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
nice article helpfull in problem solving
Left by Satish Kumar on Dec 31, 2011 3:01 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
Can i get this same code in visual basic?
Left by ted on Mar 18, 2012 6:37 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
Provide with Good Examples
Left by Saakshi on Mar 26, 2012 3:03 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
I use C# to program and i am finding it hard to save , retrieve and update data from SQL to my webform.we do not use stored procedures for our programs but we have to create lists using LINQ......
Left by zukisa on Apr 17, 2012 5:46 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
Realy simple and cool artical
Left by Ashish on Apr 25, 2012 7:50 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
this is not proper.where is the NorthwindDataContext class file. or remove it from site. not good. this is bad.
Left by renuka on May 16, 2012 7:22 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
how to add database name e.g northwinddatacontex
Left by chandu on Jul 12, 2012 2:32 PM

# re: Inserting Data to Database using LINQ to SQL
Requesting Gravatar...
Please can I get vb code for the example?
Also suppose I want to use a drop down list for say the city column which will come from a table city . how do I go about it?
Thanks.
Left by Johnson on Oct 01, 2012 11:00 PM

comments powered by Disqus

Copyright © Vincent Maverick Durano | Powered by: GeeksWithBlogs.net