LINQ to PostgreSQL Tutorial
原文 LINQ to PostgreSQL Tutorial
This tutorial guides you through the process of creating a simple application powered by LinqConnect technology. In less than 5 minutes you will have a ready-to-use data access layer for your business objects.
In this walkthrough:
- Introducing the LinqConnect (LINQ to PostgreSQL) Technology
- Requirements
- Preparing the Project
- Generating Model from Database
- Querying Data
- Inserting New Data
- Updating Data
- Deleting Data
- Additional Information
Introducing the LinqConnect (LINQ to PostgreSQL) Technology
LinqConnect (formerly known as LINQ to PostgreSQL) is the fast and lightweight ORM solution, which is closely compatible to Microsoft LINQ to SQL and contains its own advanced features, such as complex type support, advanced data fetching options, configurable compiled query caching, and others.
LINQ stands for Language-Integrated Query, which means that data retrieval is no longer a separate language. The LINQ engine allows .NET applications to connect to databases without bothering much about columns and rows. The data you receive is automatically formed as objects ready to use by your business logic.
LINQ to Relational Data may be thought of as an object-relational mapping (ORM) tool. The type-safe LINQ queries get compiled into MSIL on the fly, and the query clauses are translated into SQL and sent to PostgreSQL server for execution. This makes your data access layer safer, faster, and greatly more convenient to design.
Requirements
In order to connect to PostgreSQL server you need the server itself running, dotConnect for PostgreSQL installed and IDE running. LinqConnect requires .NET Framework 3.5, Visual Studio 2008, and PostgreSQL server 8.0 or higher. Note that LinqConnect feature is only available in Professional Edition of dotConnect for PostgreSQL.
In this tutorial it is assumed that you already have the database objects created. You have to execute a script from the following file installed by default to
\Program Files\Devart\dotConnect\PostgreSQL\Samples\crm_demo.sql
Preparing the Project
Create a new console application in Visual Studio. It could be any other project type as well, but for simplicity's sake we'll use console project throughout the tutorial. The rest of the tutorial assumes that the name of the project isConsoleApplication1. If you project is named otherwise, you will have to substitute this name with the actual one in Solution Explorer.
Generating Model from Database
- Add Devart LinqConnect Model to the project. To do this, right-click on the project node in Solution Explorer, point to Add, click New Item.... In the Add New Item dialog select Data category, choose Devart LinqConnect Model template, and click Add. This automatically launches Create New Model wizard, which creates a new empty model or generates it from database.
- Click Next on the welcome screen.
- Fill in connection settings and click Next.
- Choose database objects that will be used in the model. These are all objects from the crm_demo script, including auxiliary tables. Click Next.
- On the next screen you can adjust naming rules for entities and their members. For the CRM Demo database no rules are required, so just click Next.
- Input CrmDemoContext as namespace, and CrmDemoDataContext as the name of DataContext descendant. This will be the name of the main data access class. Click Next.
- Press Finish. The model will be generated and opened in Entity Developer.
- In the main menu, click File | Save. This updates the generated CrmDemoDataContext model code in Visual Studio.
The model you've just generated is ready to use.
Entity Developer creates classes for all selected tables that represent entities. It also creates a descendant ofDevart.Data.Linq.DataContext class, which controls the connection to the database, and the whole data flow. This class includes properties and methods named after your database objects. You will use these members to retrieve and modify data in the context. The generated code is contained in the file DataContext1.Designer.cs (DataContext1.Designer.vb). You may write your own partial classes and methods for it in the file DataContext1.cs (DataContext1.vb).
Querying Data
All LINQ to PostgreSQL operations are executed through the DataContext descendant, which is namedCrmDemoDataContext in this tutorial. To retrieve data you have to first create an instance of the context, then prepare a query with LinqConnect, and then access the object returned by the query, which may be a collection of objects or a single object.
Let's read all the data from the table Company, sort it by CompanyID, and output some columns. Add the following block of code to the method Main:
C#
CrmDemoDataContext context =
new
CrmDemoDataContext();
var query = from it
in
context.Companies
orderby it.CompanyID
select it;
foreach
(Company comp
in
query)
Console.WriteLine(
"{0} | {1} | {2}"
, comp.CompanyID, comp.CompanyName, comp.Country);
Console.ReadLine();
|
Visual Basic
Dim
context
As
CrmDemoDataContext =
New
CrmDemoDataContext
Dim
query = From it
In
context.companies _
Order By it.CompanyID _
Select
it
Dim
comp
As
company
For
Each
comp
In
query
Console.WriteLine(
"{0} | {1} | {2}"
, comp.CompanyID, comp.CompanyName, comp.Country)
Next
Console.ReadLine()
|
As simple as that. You prepare a query and then iterate through it as you would do with a usual collection of objects. The database interaction is performed by LinqConnect in the background. Now let's see who is who in this code sample.
- CrmDemoDataContext is the name of the class that knows all about your model and does everything to retrieve and modify related data in the database. All LinqConnect operations are performed within this class's properties and methods. This class is designed to be lightweight and not expensive to create, thus it is recommended to create a new DataContext instance for any 'unit of work' and dispose it after this unit is completed.
- query, it are arbitrary variable names in the LINQ to SQL statement. The former is used as the collection of data objects, the latter is used to reference single entities in a collection and exists inside the statement only.
- context.Companies refers to a public property of CrmDemoDataContext class. This property represents the collection of all companies in the context.
- Company (in the foreach statement) is the name of an autogenerated class. This class maps to the Companytable in the database and is named after it.
Here is the project's output in the console:
Note that the LINQ query code just describes the query. It does not execute it. This approach is known as deferred execution.
Now let's query data from two tables united with a foreign key. Replace the old code with this:
C#
CrmDemoDataContext context =
new
CrmDemoDataContext();
var query = from it
in
context.Companies
orderby it.CompanyID
select it;
foreach
(Company comp
in
query) {
if
(comp.PersonContacts.Count > 0) {
Console.WriteLine(
"{0} | {1} | {2}"
,
comp.CompanyName, comp.PersonContacts[0].FirstName,
comp.PersonContacts[0].LastName);
}
}
Console.ReadLine();
|
Visual Basic
Dim
context
As
CrmDemoDataContext =
New
CrmDemoDataContext
Dim
query = From it
In
context.companies _
Order By it.CompanyID _
Select
it
Dim
comp
As
company
For
Each
comp
In
query
If
comp.personcontacts.Count > 0
Then
Console.WriteLine(
"{0} | {1} | {2}"
, _
comp.CompanyName, comp.personcontacts(0).FirstName, _
comp.personcontacts(0).LastName)
End
If
|