Nikolaos Kantzelis ASP.Net Blog

This blog will focus on ASP.NET Framework

Sponsors

About Me

Great Blogs

Select,Insert,Update and Delete data with LINQ to SQL in an ASP.Net application

As you might have guessed I am continuing my LINQ to SQL posts. I am teaching a course right now on ADO.Net 3.5 (LINQ & EF) and I know a lot of people who have learned through my blog and my style of writing.

I am going to use a step by step example to demonstrate how to select,update,insert,delete data through LINQ to SQL into the database.

If you want to have a look on how to return data from a database with LINQ to SQL and stored procedures click here. If you want to have a look on how to insert,update and delete data with LINQ to SQL and stored procedures click here.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

3) From the Server Explorer / Data Explorer window drag the Employees table and drop it on the Northwind.dbml.

4) Have a look at the generated code from the Linq engine in Northwind.designer.cs

5) Drag a Gridview web server control on the Default.aspx page.

6) In the Page_Load event handling routine of the Default.aspx page type

        NorthwindDataContext ctx = new NorthwindDataContext();

                   var emps = from myemp in ctx.Employees
                                     select myemp;
                       

        GridView1.DataSource = emps;
        GridView1.DataBind();


 7) Run your application and see the results printed in the screen.

 8) But what if wanted only a few columns of the Employees table back from the database?

we need to rewrite the code above, like that (comment out everything you have so far inside the )

 NorthwindDataContext ctx = new NorthwindDataContext();


        var emps = from myemp in ctx.Employees
                   select new
                              {
                              myemp.LastName,
                              myemp.FirstName,
                              myemp.BirthDate,
                              myemp.HireDate,
                              myemp.HomePhone,
                              myemp.City,
                              myemp.Country
                               

                              };
     


        GridView1.DataSource = emps;
        GridView1.DataBind();

 9) Run your application and see only the selected columns printed out on the screen.Now I am going to show you how to update the data.

Before we do that let's change the code a bit. Create a new method called Load_Data. Inside this method place all the code above.

So the whole method looks like this

private void Load_Data()
    {
        NorthwindDataContext ctx = new NorthwindDataContext();


        var emps = from myemp in ctx.Employees
                   select new
                              {
                                  myemp.LastName,
                                  myemp.FirstName,
                                  myemp.BirthDate,
                                  myemp.HireDate,
                                  myemp.HomePhone,
                                  myemp.City,
                                  myemp.Country
                               

                              };
     


        GridView1.DataSource = emps;
        GridView1.DataBind();
    }

 

The Page_Load() event handling routine becomes

 protected void Page_Load(object sender, EventArgs e)
    {
        Load_Data();
    }

10) Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Update".

11) In the Button1_Click() event handling routine type

 NorthwindDataContext ctx = new NorthwindDataContext();

        var myEmployee = ctx.Employees.Single(myemp => myemp.EmployeeID == 4);

        myEmployee.FirstName = "Julia";

        try
        {
            ctx.SubmitChanges();
            Response.Write("Updated");
        }
        catch (Exception ex)
        {

           Response.Write(ex.Message);
        }
        Load_Data();

    }

12) Run your application and you will see the LastName value for the particular record changing to the new value.Note that the change will not be persisted to the database until the SubmitChanges() method executes.

13) Now let's try and insert a new record in our Employees table. Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Insert".

 14) In the Button2_Click() event handling routine type

   NorthwindDataContext ctx = new NorthwindDataContext();

        Employee myemp = new Employee();
        myemp.LastName = "Kantzelis";
        myemp.FirstName = "Nikos";
        myemp.BirthDate = DateTime.Now.AddDays(-10000);
        myemp.HireDate = DateTime.Now.AddDays(-2000);
        myemp.HomePhone = "55555555";
        myemp.City = "Athens";
        myemp.Country = "Greece";

        ctx.Employees.InsertOnSubmit(myemp);

        try
        {
            ctx.SubmitChanges();
            Response.Write("Inserted");
        }
        catch (Exception ex)
        {

            Response.Write(ex.Message);
        }

        Load_Data();

Run your application and see the new record inserted into the table.

Note that the change will not be persisted to the database until the SubmitChanges() method executes.

15) Now let's try to delete the newly added record from our Employees table. Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Delete".

16) In the Button3_Click() event handling routine type

        NorthwindDataContext ctx = new NorthwindDataContext();

        var myEmployee = ctx.Employees.Single(myemp => myemp.EmployeeID == 10);

        ctx.Employees.DeleteOnSubmit(myEmployee);
       
        try
        {
            ctx.SubmitChanges();
            Response.Write("Deleted");
        }
        catch (Exception ex)
        {

            Response.Write(ex.Message);
        }

        Load_Data();

We get an instance of the object  that I want to delete. Then we pass it as a parameter in the DeleteOnSubmit() method.

Run your application and see the newly added record deleted from the table.

Note that the changes will not be persisted to the database until the SubmitChanges() method executes.

That is all for now. Stay tuned for many posts on EF and LINQ.

Email me if you need the source code.

Hope it helps.

Comments

Chaman Lal said:

Thanks a Lot.

It's a milestone for beginner to the linq

# May 17, 2011 6:14 AM

hussein39 said:

Thank a million for your Nice Post

I just have a question that i have only 1 day to answer it. i dont know why when i set CopytoOutputDirectory in my database to donotCopy. i get this error when i'm connected to the database:

An attempt to attach an auto-named database for file F:\Schoo_Backup\3\Visual Studio 2008\Projects\School_Project1\School_Project1\bin\Debug\SchoolDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

on my AppConfig i set the connection like this:

 <add name="SchoolDB"

           connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\School.mdf;Integrated Security=True;User Instance=True"

           providerName="System.Data.SqlClient" />

       <add name="School_Project.Properties.Settings.SchoolDBConnectionString"

           connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SchoolDB.mdf;Integrated Security=True;User Instance=True"

           providerName="System.Data.SqlClient" />

   </connectionStrings>    

I get the StackSource property in exception class:

  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

  at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

  at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

  at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)

  at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)

  at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

  at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

  at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

  at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

  at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

  at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

  at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

  at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

  at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

  at System.Data.SqlClient.SqlConnection.Open()

  at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)

  at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe()

  at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode()

  at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)

  at System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)

  at System.Linq.Queryable.Count[TSource](IQueryable`1 source)

  at School_Project.Login.Check_If_Valid() in F:\Schoo_Backup\3\Visual Studio 2008\Projects\School_Project1\School_Project1\Logint.cs:line 27

# August 14, 2011 4:02 AM

Arif said:

Its good example...

Thanks

# October 31, 2011 8:35 AM

a said:

Thumbs Up PAL :)

-From Pakistan

# September 6, 2012 1:22 AM