Agile.net Component development Platform-data access class library

Agile.DataAccess.dll file is the platform data Access support library, this ORM is based on the fluentdata extension rewrite, provides various database access functions.

Database Support list:

MS SQL Server using the native. NET driver.

MS SQL Azure using the native. NET driver.

MS Access using the native. NET driver.

MS SQL Server Compact 4.0 through the Microsoft SQL Server Compact 4.0 driver.

Oracle through the odp.net driver.

MySQL through the MySQL Connector. NET driver.

SQLite through the SQLite ado Data Provider.

POSTGRESQL through the Npgsql provider.

IBM DB2

Sybase through the Https://github.com/FredoKapo/FLUENT-ORM-ASE-PROVIDER PROVIDER.

API Example

Query returns a list of dynamic objects

List<dynamic> products = Datacontext.script ("SELECT * from Product")

. Querymany<dynamic> ();

query returns a list of strongly typed objects List<product> products = Datacontext.script ("SELECT * from Product")

. Querymany<product> ();

Query returns a single dynamic object

Dynamic Product = Datacontext.script (@ "SELECT * FROM product where ProductId = 1"). Querysingle<dynamic> ();

Query returns a single strongly typed object

Product Product = Datacontext.script (@ "SELECT * FROM product where ProductId = 1"). Querysingle<product> ();

Query return data table

DataTable products = Datacontext.script ("SELECT * from Product")

. Querysingle<datatable> ();

Querying a scalar value

int number = Datacontext.script (@ "SELECT COUNT (*) from Product")

. Querysingle<int> ();

Querying a list of scalar values

list<int> productids = datacontext.script (@ "Select ProductId from Product"). Querymany<int> ();

Custom Match entity classes

List<product> products = Datacontext.script (@ "Select p.*,

C.categoryid as Category_categoryid,

C.name as Category_name

From Product P

INNER JOIN Category c on P.categoryid = C.categoryid ")

. Querymany<product> ();

Custom Mappings

List<product> products = Datacontext.script (@ "SELECT * FROM Product")

. Querymany<product> (custom_mapper_using_dynamic);

public void custom_mapper_using_dynamic (product product, dynamic Row)

{

Product. ProductId = row. ProductId;

Product. Name = row. Name;

}

Custom Mappings using DataReader

List<product> products = Datacontext.script (@ "SELECT * FROM Product")

. Querymany<product> (Custom_mapper_using_datareader);

public void Custom_mapper_using_datareader (product product, IDataReader Row)

{

Product. ProductId = row. GetInt32 ("ProductId");

Product. Name = row. GetString ("Name");

}

Application of query parameters

Mode 1

Dynamic products = Datacontext.script (@ "SELECT * from Product where ProductId = @0 or ProductId = @1", 1, 2). Querymany<dynamic> ();

Mode 2

Dynamic products = Datacontext.script (@ "SELECT * from Product where ProductId = @0 or ProductId = @1"). Parameters (1, 2). Querymany<dynamic> ();

Mode 3 (named parameter)

Dynamic products = Datacontext.script (@ "SELECT * from Product where ProductId = @ProductId1 or ProductId [email protected] "). Parameter ("ProductId1", 1). Parameter ("ProductId2", 2). Querymany<dynamic> ();

Mode 4 (output parameter):

var command = datacontext.script (@ "Select @ProductName = Name from Product where productid=1"). Parameterout ("ProductName", datatypes.string, 100);

Command. Execute ();

string productName = command. Parametervalue<string> ("ProductName");

Paging query:

List<product> products = datacontext.select<product> ("p.*, c.name as Category_name"). From (@ "Product p inner joins Category c on c.categoryid = P.categoryid"). Where ("P.productid > 0 and p.name are NOT NULL")

. ("P.name"). Paging (1, 10). Querymany ();

Inserting data

Way 1

Datacontext.script (@ "INSERT into Product (Name, CategoryId) VALUES (@0, @1);"). Parameters ("The", 1). Execute ();

Way 2 ( Insert data and return self-increment primary key )

int productId = Datacontext.script (@ "INSERT into Product (Name, CategoryId) VALUES (@0, @1);"). Parameters ("The", 1). Executereturnlastid<int> ();

Mode 3 (Insert data and return self-increment primary key)

int productId = Datacontext.insert ("Product")

. Column ("Name", "the Warren Buffet")

. Column ("CategoryId", 1)

. Executereturnlastid<int> ();

Mode 4 (automatically constructs SQL)

Product Product = new product ();

Product. Name = "The Warren buffet";

Product. CategoryId = 1;

Product. ProductId = datacontext.insert<product> ("Product", product)

. Automap (x = X.productid)

. Executereturnlastid<int> ();

Update data

Way 1

int rowsaffected = Datacontext.script (@ "Update Product set Name = @0 where ProductId = @1"). Parameters ("The Warren Buffet", 1). Execute ();

Way 2

int rowsaffected = datacontext.update ("Product")

. Column ("Name", "the Warren Buffet")

. Where ("ProductId", 1)

. Execute ();

Way 3 ( automatically constructs SQL)

int rowsaffected = datacontext.update<product> ("Product", product)

. Automap (x = X.productid)

. Where (x = X.productid)

. Execute ();

Insert or update a Fill method

var product = new product ();

Product. Name = "The Warren buffet";

Product. CategoryId = 1;

var insertbuilder = datacontext.insert<product> ("Product", product). Fill (Fillbuilder);

var updatebuilder = datacontext.update<product> ("Product", product). Fill (Fillbuilder);

public void Fillbuilder (iinsertupdatebuilder<product> builder)

{

Builder. Column (x = x.name);

Builder. Column (x = X.categoryid);

}

Delete data

Mode 1

int rowsaffected = Datacontext.script (@ "Delete from Product where ProductId = 1"). Execute ();

Way 2

int rowsaffected = Datacontext.delete ("Product")

. Where ("ProductId", 1)

. Execute ();

Way 3

int rowsaffected = datacontext.delete<product> ("Product", product)

. Where (x = X.productid)

. Execute ();

Stored Procedures

Mode 1

var rowsaffected = datacontext.script ("Productupdate")

. CommandType (Dbcommandtypes.storedprocedure)

. Parameter ("ProductId", 1)

. Parameter ("Name", "the Warren Buffet")

. Execute ();

Mode 2

var rowsaffected = datacontext.procedure ("Productupdate")

. Parameter ("Name", "the Warren Buffet")

. Parameter ("ProductId", 1). Execute ();

Mode 3

var rowsaffected = datacontext.procedure<product> ("Productupdate", Product). Automap (x = X.categoryid). Execute ();

Mode 4

var rowsaffected = datacontext.procedure<product> ("Productupdate", Product). Parameter (x = X.productid)

. Parameter (x = x.name). Execute ();

Application of Things

The code inside the wrapper using the transaction uses a using statement to ensure that the database connection is closed. By default, if any exception occurs or does not commit, the thing is automatically rolled back.

using (var context = datacontext.begintransaction ())

{

Context. Script ("update Product set Name = @0 where ProductId = @1")

. Parameters ("The Warren Buffet", 1)

. Execute ();

Context. Update ("Product"). Column ("Name", "the Warren Buffet")

. Where ("ProductId", 1)

. Execute ();

Context. Update<product> ("Product", product)

. Automap (x = X.productid)

. Where (x = X.productid)

. Execute ();

Context.commit ();

}

Platform Specification:

New, updated and deleted data in this platform is done using the above API. Due to the complexity and variability of data queries, the platform specification requires the use of SQL statements, as shown in the following example:

First, define the query script in the script manager, for example "2001" Number Script

Api:

Product Product = Datacontext.scriptbyid (2001). Querysingle<product> ();

Or

List<product> products = Datacontext.scriptbyid (2001). Querymany<product> ();

Or

Product Product = Datacontext.scriptbyid (2001)

. Parameter ("ProductId", 3). Querysingle<product> ();

Agile.net Component development Platform-data access class library