Using LINQ to SQL in place of SQL Statements in C#

demo · 发表于 2011-5-27 08:30:07

本帖最后由 demo 于 2011-5-28 00:46 编辑

ASP.NET Database Tutorial

This tutorial will show you how to use LINQ to SQL in ASP.NET 3.5 to manipulate database data without using SQL Statements. C# version.

With ASP.NET 2.0 we were limited in our ways of connecting to a database, with SQL and Access databases the most popular to use. Not many developers enjoy working with SQL statements - having to translate between that and the scripting language. But now, with ASP.NET 3.5, we have been introduced to LINQ. LINQ provides us with a way of communicating directly with a database in the code - no need to translate into SQL. LINQ also comes in a variety of flavors, allowing us to connect to numerous data sources, not only databases.

In this tutorial, we will be using LINQ to SQL to interact with a SQL Server Database. We will add the ability to add new records to the database, as well as edit and delete existing records - all without using any SQL statements. To do this, we will need to create a LINQ to SQL Class to represent our database, but luckily for us, Visual Studio does the hard work for us.

Let's start by creating a new C# web application project in Visual Studio .NET 2008, and then add a new database by right-clicking the App_Data folder in Solution Explorer and choosing Add New Item.. SQL Server Database. We will add one table, tblEmployees, with three columns - id, name, and position. We will make the id the Primary Key and also the Identity Specification (in the Properties Window).

Once we have done this, save the table and make sure the Connection String is in the Web.config:

Next, right-click the project in Solution Explorer and choose Add ASP.NET Folder > App_Code. Then right-click the App_Code folder and choose Add New Item.. LINQ to SQL Classes. Give the name Employees. This is the class that will represent our database and that we will use LINQ to interact with.

We should be provided with a design view of the Object Relation Designer. Drag onto the designer the table from Server Explorer, then save.

Once saved, we can close the class - VS will create the methods based on our graphical structure. Next, we will extend this class with our own methods, which we will use to add, edit and delete database data. Right-click the App_Code folder once more and choose Add New Item.. Class. Give it the name Employees, and we should be provided with a new, empty C# class. Let's start with the Select statement. You will notice that LINQ statements are similar to SQL, but they interact with the class we created earlier, and then commit changes back to the database.

public static IEnumerable Select()
{
EmployeesDataContext db = new EmployeesDataContext();
return db.tblEmployees.OrderBy(e=>e.id);
}

Instead of interacting directly with the database, we instantiate the DataContext class and make a selection of all the data, ordered by the id column using a Lambda Expression.

Now we can move onto the Insert method:

public static void Insert(tblEmployee employeeToAdd)
{
EmployeesDataContext db = new EmployeesDataContext();
db.tblEmployees.InsertOnSubmit(employeeToAdd);
db.SubmitChanges();
}

This method will be used by the FormView control to add a new record to the database. Using LINQ makes it so simple to do this, as it manages our database connection and data types, etc. All we need to tell it to do is to add, and what to add.
Now we can add the method that will allow us to update records using the built-in GridView update feature:

public static void Update(tblEmployee oldEmployee, tblEmployee newEmployee)
{
EmployeesDataContext db = new EmployeesDataContext();
db.tblEmployees.Attach(newEmployee,oldEmployee);
db.SubmitChanges();
}

Using the Attach method of the table class, we are able to supply the data necessary to update the database, then SubmitChanges commits changes back to the database.

Finally, we can write the method to delete records from the database:

public static void Delete(tblEmployee employeeToDelete)
{
EmployeesDataContext db = new EmployeesDataContext();
db.tblEmployees.Attach(employeeToDelete);
db.tblEmployees.DeleteOnSubmit(employeeToDelete);
db.SubmitChanges();
}

The entire code is as follows:

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;
using System.Data.Linq;

/// <summary>
/// Extension of Employees.dbml
/// </summary>
public partial class Employees
{
      public static void Insert(tblEmployee employeeToAdd)
      {
            EmployeesDataContext db = new EmployeesDataContext();
            db.tblEmployees.InsertOnSubmit(employeeToAdd);
            db.SubmitChanges();
      }

public static IEnumerable<tblEmployee> Select()
{
      EmployeesDataContext db = new EmployeesDataContext();
      return db.tblEmployees.OrderBy(e=>e.id);
}

public static void Update(tblEmployee oldEmployee, tblEmployee newEmployee)
{
      EmployeesDataContext db = new EmployeesDataContext();
      db.tblEmployees.Attach(newEmployee,oldEmployee);
      db.SubmitChanges();
}

public static void Delete(tblEmployee employeeToDelete)
{
      EmployeesDataContext db = new EmployeesDataContext();
      db.tblEmployees.Attach(employeeToDelete);
      db.tblEmployees.DeleteOnSubmit(employeeToDelete);
      db.SubmitChanges();
}
}

Now that we are now done extending the LINQ to SQL Class, we can work on implementing the functionality into our ASPX page. Firstly, let's add the controls we will be using:

We set both controls DataSourceID to that of the ObjectDataSource, but we also need to set the method attributes to the ones we just created in the class. We do this by assigning the TypeName of the DataSource to our partial class, and then the methods:

<asp:ObjectDataSource ID="ODS1" runat="server" TypeName="Employees" DataObjectTypeName="tblEmployee"
SelectMethod="Select" InsertMethod="Insert" UpdateMethod="Update" DeleteMethod="Delete"
ConflictDetection="CompareAllValues" OldValuesParameterFormatString="oldEmployee" />

Finally, we build the template of our FormView and also enable the Edit and Delete buttons on our GridView:

Now when we run this web application, we will be presented with a table of data from the database, and the ability to add new records, edit existing records and also delete records from the database. All this functionality without dealing directly with SQL.

http://www.aspnettutorials.com/tutorials/database/linq-to-sql-add-edit-delete-cs.aspx

juseliao · 发表于 2011-6-1 22:58:51

楼主好帖不回不行啊哈！

为了好帖我沉闷一天的手终于控制不了要去回帖了，好了，我回了....

三仙矛草,三仙矛草,三仙矛草,三仙矛草

低调的爱 · 发表于 2012-1-16 10:04:23

顶你一下，好贴要顶！

策一知产

账号		自动登录	找回密码
密码			注册

Using LINQ to SQL in place of SQL Statements in C#

浏览过的版块