Create an object class using LINQ to SQL, and use linqsql

Create an object class using LINQ to SQL, and use linqsql

When using LINQ to SQL, you must first create a model for ing database objects, that is, an object class. During running, the SQL statement is generated based on the LINQ expression or query operator and sent to the database for operation. After the database returns data, the result is converted into an object class object by using LINQ to SQL.

There are many ways to create object classes, such as the LINQ to SQL designer, manual coding, XML file ing, and SqlMetal generation using the command line tool. The most convenient one is the LINQ to SQL designer.

1. Use the LINQ to SQL designer to create an object class

Add "service-based database" Database1.mdf to the Demo console program in an example to create a table tb_GuestInfo. The table details are as follows:

This database is used for all the following creation methods.

Add a LINQ to SQL class to the project with the default name DataClasses1.dbml, as shown below:

Drag the tb_GuestInfo table to the interface and save it.

OK, write the relevant code as follows to add, delete, modify, and query:

Using System; using System. collections. generic; using System. linq; using System. text; namespace LINQ_To_ SQL User-Defined database and entity class {// <summary> // entity class creation ___ 1. VS create an object class // </summary> class Program {static void Main (string [] args) {// DataClasses1DataContext dc = new DataClasses1DataContext (); // 1. query IQueryable <tb_GuestInfo> query = from p in dc. tb_GuestInfo where p. name! = "XXX" select p; foreach (var g in query) {Console. writeLine ("{0} {1} {2} {3}", g. id, g. name, g. age, g. tel);} Console. writeLine ("-----------------"); Console. readKey (false); // 2. add a record tb_GuestInfo gInfo = new tb_GuestInfo () {Id = 9, Name = "M & M", Age = 40, Tel = "135 *** 5555 "}; dc. tb_GuestInfo.InsertOnSubmit (gInfo); dc. submitChanges (); foreach (var g in query) {Console. writeLine ("{0} {1} {2} {3}", g. id, g. name, g. age, g. tel);} Console. writeLine ("-----------------"); Console. readKey (false); // 3. delete var query_itemToDelete = from g in dc. tb_GuestInfo where g. name = "M & M" select g; foreach (var g in query_itemToDelete) {dc. tb_GuestInfo.DeleteOnSubmit (g);} dc. submitChanges (); foreach (var g in query) {Console. writeLine ("{0} {1} {2} {3}", g. id, g. name, g. age, g. tel);} Console. writeLine ("-----------------"); Console. readKey (false); // 4. modify var query_itemToUpdate = from g in dc. tb_GuestInfo where g. name. contains ("DebugLZQ") select g; foreach (var g in query_itemToUpdate) {g. name = g. name + "A";} dc. submitChanges (); foreach (var g in query) {Console. writeLine ("{0} {1} {2} {3}", g. id, g. name, g. age, g. tel);} Console. writeLine ("-----------------"); Console. readKey (false );}}}

The program running result is as follows:

2. manually create an object class

In most cases, object classes can be created through the LINQ to SQL class designer. Of course, it is not difficult to create a simple object class. In addition, you can better learn the object model of LINQ to SQL. The database is still the previous example database.

Add a class GuestInfoEntity. cs to the project as follows:

Using System; using System. collections. generic; using System. linq; using System. text; using System. data. linq. mapping; namespace DataContexDemo {// <summary> // manually create an object class /// </summary> [Table (Name = "tb_GuestInfo")] class GuestInfoEntity {[Column (IsPrimaryKey = true, DbType = "Int not null identity", IsDbGenerated = true, Name = "Id")] public int ID {get; set ;} [Column (DbType = "nvarchar (20)", Name = "Name")] public string Name {get; set;} [Column (DbType = "int ", name = "Age")] public int Age {get; set;} [Column (DbType = "nvarchar (20)", Name = "Tel")] public string Tel {get; set ;}}}

Compile the sample code. Note that you need to introduce System. Data. Linq. dll:

Using System; using System. collections. generic; using System. linq; using System. text; using System. data. linq; // follow namespace DataContexDemo {class Program {static void Main (string [] args) {// 2. manually create an object class // connection string constring = @ "Data Source =. \ SQLEXPRESS; AttachDbFilename = E: \ Visual Studio 2010 \ LINQ_to_ SQL \ LINQ_To_ SQL custom database and entity class \ Database1.mdf; Integrated Security = True; User Instance = True "; dataContext dc = new DataContext (constring); Table <GuestInfoEntity> tb = dc. getTable <GuestInfoEntity> (); var query = tb. asEnumerable (); foreach (var q in query) {Console. writeLine ("{0} {1} {2} {3}", q. ID, q. name, q. age, q. tel);} Console. readKey ();}}}

The program runs as follows:

3. Create an object class using an XML ing File

In addition to using inline Attribute, you can also create an XML file containing ing information for object class ing. This file generates System. data. linq. mapping. xmlMappingSource object, used as a parameter of the DataContext object constructor.

This XML file has only one root node-Database element, which is used to map Database information. The Database element contains one or more Table elements, which are used to map data warehouse Table information. The Table element consists of one Type element and multiple Column elements (or Association elements. The Type element is used to specify the object class, the Column element is used to specify the Column information, and the Association element is used to map the database relationship.

Add an XML file to the project with the default name XMLFile1.xml. The content is as follows:

<? Xml version = "1.0" encoding = "UTF-8"?> <Database Name = "Database1" xmlns = "http://schemas.microsoft.com/linqtosql/mapping/2007"> <! -- The database Name can be used at will. You must add the --> <Table Name = "tb_GuestInfo"> <! -- Database table Name --> <Type Name = "LINQtoSQL create object class _ XML. GuestInfoEntity"> <! -- The full name is required because it is too BT; guestInfoEntity doesn't work --> <Column Name = "Id" Member = "ID" DbType = "Int not null identity" IsPrimaryKey = "true"/> <Column Name = "Name" Member = "Name" DbType = "nvarchar (20) "/> <Column Name =" Age "Member =" Age "DbType =" int "/> <Column Name =" Tel "Member =" Tel "DbType =" nvarchar (20) "/> </Type> </Table> </Database>

This XML file contains all the ing information of the class. The following creates the ing Class GuestInfoEntity. cs:

Using System; using System. collections. generic; using System. linq; using System. text; namespace LINQtoSQL create object class _ XML {public class GuestInfoEntity {public int ID {get; set;} public string Name {get; set;} public int Age {get; set ;}public string Tel {get; set ;}}}

To compile the sample code, you also need to introduce System. Data. Linq. dll:

Using System; using System. collections. generic; using System. linq; using System. text; using System. data. linq; using System. data. linq. mapping; using System. IO; // namespace LINQtoSQL create object class _ XML {class Program {static void Main (string [] args) {string constring = @ "Data Source =. \ SQLEXPRESS; AttachDbFilename = E: \ Visual Studio 2010 \ LINQ_to_ SQL \ LINQ_To_ SQL custom database and entity class \ Database1.mdf; Integrated Security = True; User Instance = True "; XmlMappingSource map = XmlMappingSource. fromXml (File. readAllText ("XMLFile1.xml"); DataContext dc = new DataContext (constring, map); Table <GuestInfoEntity> tb = dc. getTable <GuestInfoEntity> (); var query = tb. asEnumerable (); foreach (var g in query) {Console. writeLine ("{0} {1} {2} {3}", g. ID, g. name, g. age, g. tel);} Console. readKey ();}}}

The program runs as follows:

Linq to SQL entity class
. Net: How to Write Data Access layers using linq to SQL?

My personal project I graduated from was also made using linq to SQL. Only the UI Layer and BLL business logic layer (Class Library) were created ), the DateLinq class library (which contains only one file of linq to SQL), the data access layer DAL should not be written any more. The first two references DateLinq and BLL will add, query, modify, and delete data, the entity class directly uses the linq ing table of linq, or you can add features on your own. This is the simplest and useless design mode. I feel that using linq to SQL has more than one option in three layers. Of course, it can still be written like using T-SQL data access, I think it is unnecessary.
Agree with the previous answer. Use the code generator, no matter which layer, it can be done in minutes.