Open-source component Smark. Data processes SQL and stored procedures

It is unrealistic to complete all database operations by describing the component object completely. Therefore, the component provides SQL and stored procedure execution support. with the continuous improvement of component versions, it is extremely convenient for components to use SQL and storage after 1.7. its simple operation method is believed to be difficult to experience in other Data Access Components.

SQL Processing

The component provides an SQL object for directly processing SQL statements. Because the operator is overwritten, the object can be initialized directly using string.

 
SQL = "delete from employees where employeeid = @ p1 ";
 

SQL is the object provided by component 1.7. It provides the following basic operations:

• Int execute ()
  Obtain the number of affected rows, which is generally required for executing insert, delete, and update operations.
• T getvalue <t>
  Obtains the value of the first column of the first row of the query result. It is mainly used to execute the summary statistics, Count, sum, and so on.
• Listfirst <t>
  Obtain the first record that matches the query and fill it in the corresponding object
• List <t>
  Retrieve records that match the query and fill them in the corresponding object

Delete data

 
SQL = "delete from employees where employeeid = @ p1"; SQL ["p1", 11]. Execute ();
 

Execute Query

SQL = "select * from orders where employeeid = @ P1 and orderdate> @ p2"; var items = SQL ["p1", 1] ["p2", "1996-1-1"]. list <order> ();
 

SQL merge operations

 
SQL = "select * from orders where 1 = 1"; if (from! = NULL) SQL = "and orderdate >=@ p1" + SQL ["p1", from. value]; If (! = NULL) SQL = "and orderdate <= @ p2" + SQL ["p2", to. value]; items = SQL. List <order> ();
 

Statistics

 
SQL = "select count (*) from orders where 1 = 1"; if (from! = NULL) SQL = "and orderdate >=@ p1" + SQL ["p1", from. value]; If (! = NULL) SQL = "and orderdate <= @ p2" + SQL ["p2", to. value]; int COUNT = SQL. getvalue <int> ();
 

Hybrid Conditions

Components provide object-based description expressions, but for some complex conditions, the string-based description of certain word conditions is relatively simple.

Expression exp = order. orderdate> "1996-1-1"; exp & = "customerid in (select customerid from customers where country = @ P1)"; items = exp ["p1", "USA"]. list <order> ();
 

Storage Processing

The component supports direct execution of stored procedures. The description of stored procedures is basically the same as that of table entities. The following is a simple description of stored procedures:

 
[Proc] public class custorderhist {[procparameter] Public String customerid {Get; Set ;}}
 

The stored procedure name of the preceding object description is custorderhist, while the storage contains an input parameter of customerid. The description storage only has two attributes:

• Proc
  Describes the ing between a class and a stored procedure. If the name parameter is not specified, the class name corresponds to the stored procedure name.
• Procparameter
  This parameter is used to describe a class member as a parameter of the stored procedure. If the name parameter is not specified, the member name is used as the parameter name. The direction attribute is used to specify the parameter type, which is the input type by default, if it is an output type, the return value of the stored procedure will be bound to the relevant members.

Execute the stored procedure:

 
Custorderhist cohist = new custorderhist (); cohist. customerid = "anasd"; cohist. execproc _();
 

You can also return a dataset Based on the execution storage. The results of the following stored procedure are as follows:

You can define an object for the corresponding field:

 
Public class custorderhistdata {Public String productname {Get; set;} public decimal total {Get; Set ;}}
 

After definition, the stored procedure can return the corresponding object set.

 
Custorderhist cohist = new custorderhist (); cohist. customerid = "anrecognition"; ilist <custorderhistdata> datas = cohist. execproctoobjects _ <custorderhistdata> ();
 

Address: http://smark.codeplex.com/

Official Website: http://www.ikende.com/SmarkData.aspx