General operations of the Linq Database

From: http://oct01.cn/all.aspx? Id = 14

1. Create a project named "DLinq" and add a data source named "Linq To SQL". Here we use the classic Northwind database as an example and name it "NWDB. dbml.

2. Create another Project as the DAL layer and add a Table factory so that we can obtain the Table through entities.

Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;

Namespace DAL
{
Public static class TableFactory
{
Public static System. Data. Linq. Table <T> CreateTable <T> () where T: class
{
Return Database. NWDB. GetTable <T> ();
}
}
}
 

Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;

Namespace DAL
{
Public static class Database
{
Private static DLinq. NWDBDataContext _ NWDB = null;

Public static DLinq. NWDBDataContext NWDB
{
Get
{
If (_ NWDB = null)
_ NWDB = new DLinq. NWDBDataContext ();
Return _ NWDB;
}
}

}
}
3. With the help of the features of Linq, you can now write general database operation classes.

 

Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;

Namespace DAL
{
Public class Utility
{
Public static void Insert <T> (T TEntity) where T: class
{
Var table = TableFactory. CreateTable <T> ();
Table. InsertOnSubmit (TEntity );
}

Public static IEnumerable <T> Where <T> (Func <T, bool> predicate) where T: class
{
Var table = TableFactory. CreateTable <T> ();
Return table. Where (predicate). AsEnumerable ();
}

Public static void SubmitChanges ()
{
Database. NWDB. SubmitChanges ();
}
}
}

4. Now let's write a test method to test whether the test is successful.

 

Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using DAL;
Using DLinq;

Namespace DALTest
{
Class Program
{
Static void Main (string [] args)
{
InsertTest ();
WhereTest ();
Console. WriteLine ("All testings are success! ");
Console. Read ();
}

Private static void InsertTest ()
{
Customer _ customer = new Customer {
CustomerID = "Bruce ",
ContactName = "Lee ",
CompanyName = "CodingSky ",
City = "Shenzhen "};
Utility. Insert (_ customer );
Utility. SubmitChanges ();
}

Private static void WhereTest ()
{
Var _ result = Utility. Where <Customer> (c => c. CustomerID = "Bruce ");
Var _ list = _ result. ToList ();
If (_ list. Count = 0)
{
Console. WriteLine ("No result! ");
Return;
}
Console. WriteLine ("Query result is :");
_ List. ForEach (c => Console. WriteLine (Toolkits. StringExtension. ToString (c )));
}

}
}
5. WhereTest calls the StringExtension class of another Project. This class extends the ToString method and reads all attributes and their values of the instance through Reflection.

 

Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;

Namespace Toolkits
{
Public class StringExtension
{
Public static string ToString <T> (T t) where T: class
{
Var typeInfo = BLReflection. GetProperties (typeof (T ));

Var rType = (from q in typeInfo select new
{
TypeName = q. PropertyType. Name,
PropName = q. Name,
Value = q. GetValue (t, null)
}). ToList ();

StringBuilder sb = new StringBuilder ();
String header = "Class Name: {0} \ n ";
Sb. AppendFormat (header, typeof (T). Name );
RType. forEach (c => sb. append (String. format ("\ t {0 }:{ 1} ({2}), \ n", c. propName, c. value, c. typeName )));
String result = sb. ToString ();
Return (result. Length> header. Length? Result. Substring (0, result. Length-2) + "\ n": header );
}
}
}
6. The output result should be as follows:

 

Query result is:
Class Name: Customer
CustomerID: Bruce (String ),
CompanyName: CodingSky (String ),
ContactName: Lee (String ),
ContactTitle: (String ),
Address: (String ),
City: Shenzhen (String ),
Region: (String ),
PostalCode: (String ),
Country: (String ),
Phone: (String ),
Fax: (String ),
Orders: System. Data. Linq. EntitySet '1 [DLinq. Order] (EntitySet '1)

 

All testings are success!

========================================================== ======================================

Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;

Namespace DAL
{
Public class Utility
{
Public static void Insert <T> (T TEntity) where T: class
{
Var table = TableFactory. CreateTable <T> ();
Table. InsertOnSubmit (TEntity );
}

Public static void Delete <T> (T TEntity) where T: class
{
Var table = TableFactory. CreateTable <T> ();
Table. DeleteOnSubmit (TEntity );
}

Public static void Delete <T> (Func <T, bool> predicate) where T: class
{
Var table = TableFactory. CreateTable <T> ();
Var dResult = Where <T> (predicate );
Table. DeleteAllOnSubmit (dResult );
}

Public static void Update <T> (T TEntity, Action <T> action)
{
Action (TEntity );
SubmitChanges ();
}

Public static void InsertAll <T> (IEnumerable <T> TEntities) where T: class
{
Var table = TableFactory. CreateTable <T> ();
Table. InsertAllOnSubmit (TEntities );
}

Public static void DeleteAll <T> (IEnumerable <T> TEntities) where T: class
{
Var table = TableFactory. CreateTable <T> ();
Table. DeleteAllOnSubmit (TEntities );
}

Public static IEnumerable <T> SelectAll <T> () where T: class
{
Var table = TableFactory. CreateTable <T> ();
Return table. Select (c => c). AsEnumerable ();
}

Public static IEnumerable <T> Where <T> (Func <T, bool> predicate) where T: class
{
Var table = TableFactory. CreateTable <T> ();
Return table. Where (predicate). AsEnumerable ();
}

Public static void SubmitChanges ()
{
Database. NWDB. SubmitChanges ();
}
}
}

 

Similarly, we also write some Test methods to verify if they are correct.

 

Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using DAL;
Using DLinq;
Using Toolkits;

Namespace DALTest
{
Class Program
{
Static void Main (string [] args)
{
// SelectAllTest ();
InsertTest ();
WhereTest ();
UpdateTest ();
WhereTest ();
DeleteTest1 ();
WhereTest ();
Console. WriteLine ("All testings are success! ");
Console. Read ();
}

Private static void InsertTest ()
{
Customer _ customer = new Customer {
CustomerID = "Bruce ",
ContactName = "Lee ",
CompanyName = "CodingSky ",
City = "Shenzhen "};
Utility. Insert (_ customer );
Utility. SubmitChanges ();
}

Private static void DeleteTest1 ()
{
Utility. Delete <Customer> (c => c. CustomerID = "Bruce ");
Utility. SubmitChanges ();
}

Private static void DeleteTest2 ()
{
Var _ result = Utility. Where <Customer> (c => c. CustomerID = "Bruce ");
If (_ result. Count ()> 0)
{
Utility. Delete (_ result. First ());
Utility. SubmitChanges ();
}
}

Private static void UpdateTest ()
{
Var _ result = Utility. Where <Customer> (c => c. CustomerID = "Bruce ");
If (_ result. Count ()> 0)
{
Var _ customer = _ result. First ();
If (_ customer! = Null)
{
Utility. Update (_ customer, c =>
{
C. ContactName = "Jack ";
C. CompanyName = "Microsoft ";
C. City = "Beijing ";
});
Utility. SubmitChanges ();
}
}
}

Private static void SelectAllTest ()
{
Var _ result = Utility. SelectAll <Customer> ();
Var _ list = _ result. ToList ();
If (_ list. Count = 0)
{
Console. WriteLine ("No result! ");
Return;
}
_ List. ForEach (c => Console. WriteLine (StringExtension. ToString (c )));
}

Private static void WhereTest ()
{
Var _ result = Utility. Where <Customer> (c => c. CustomerID = "Bruce ");
Var _ list = _ result. ToList ();
If (_ list. Count = 0)
{
Console. WriteLine ("No result! ");
Return;
}
Console. WriteLine ("Query result is :");
_ List. ForEach (c => Console. WriteLine (StringExtension. ToString (c )));
}

}
}

For other Code such as TableFactory, StringExtension, see previous: http://blog.csdn.net/fengart/archive/2008/08/19/2798534.aspx

In the preceding Test, Insert a guy named Bruce in the MERs table, change his ContactName to Jack, and Delete him.

(The Customers table contains too much data, so I commented out the SelectAllTest method)

The final output is as follows:

Query result is:
Class Name: Customer
CustomerID: Bruce (String ),
CompanyName: CodingSky (String ),
ContactName: Lee (String ),
ContactTitle: (String ),
Address: (String ),
City: Shenzhen (String ),
Region: (String ),
PostalCode: (String ),
Country: (String ),
Phone: (String ),
Fax: (String ),
Orders: System. Data. Linq. EntitySet '1 [DLinq. Order] (EntitySet '1)

Query result is:
Class Name: Customer
CustomerID: Bruce (String ),
CompanyName: Microsoft (String ),
ContactName: Jack (String ),
ContactTitle: (String ),
Address: (String ),
City: Beijing (String ),
Region: (String ),
PostalCode: (String ),
Country: (String ),
Phone: (String ),
Fax: (String ),
Orders: System. Data. Linq. EntitySet '1 [DLinq. Order] (EntitySet '1)

No result!
All testings are success!