Llbl Gen 3.x source code tracing and parsing execution of Stored Procedures

The adventureworks Stored Procedure uspgetemployeemanagers. The call method is as follows:
Declare @ return_value int

Exec @ return_value = [DBO]. [uspgetemployeemanagers]
@ Employeeid = 1
The execution result is as follows:

In the test project, create the following test script

[Testmethod]
Public void teststoredprocedurecall ()
{
Int employeeid = 1;
Datatable TBL = retrievalprocedures. uspgetemployeemanagers (employeeid );
Int rows = TBL. Rows. count;
}

Stored Procedure is specific to the database. Different database creation methods are different, so the generatedCodeFiles are stored in the dbspecific project.
View the generated retrievalprocedures. CSSource codeFirst, analyze its template file. Llbl Gen 3.x integrates the template editor into the orm designer and starts the template editor through the show templates binding viewer in windows.

Click Edit Selected to edit the current template and enter the sd_retrievalprocedurestemplate template, which is similar to the syntax of ASP. NET.

For a stored procedure, it generates three overload C # methods.
Public static datatable uspgetemployeemanagers (system. int32 employeeid );
Public static datatable uspgetemployeemanagers (system. int32 employeeid, idataaccesscore dataaccessprovider );
Public static iretrievalquery getuspgetemployeemanagerscallasquery (system. int32 employeeid );
The first method overload method is defined as follows, which calls the second method

Public static datatable uspgetemployeemanagers (system. int32 employeeid)
{
Using (dataaccessadapter dataaccessprovider = new dataaccessadapter ())
{
Return uspgetemployeemanagers (employeeid, dataaccessprovider );
}
}
The second method is defined as follows. It calls createuspgetemployeemanagerscall to return storedprocedurecall.
Public static datatable uspgetemployeemanagers (system. int32 employeeid, idataaccesscore dataaccessprovider)
{
Using (storedprocedurecall call = createuspgetemployeemanagerscall (dataaccessprovider, employeeid ))
{
Datatable toreturn = call. filldatatable ();
Return toreturn;
}
}
Createuspgetemployeemanagerscall is a private method used to generate a storedprocedurecall object.

Private Static storedprocedurecall createuspgetemployeemanagerscall (idataaccesscore dataaccessprovider, system. int32 employeeid)
{
Return new storedprocedurecall (dataaccessprovider, "[adventureworks]. [DBO]. [uspgetemployeemanagers]", "uspgetemployeemanagers ")
. Addparameter ("@ employeeid", "int", 0, parameterdirection. Input, true, 10, 0, employeeid );
}

Storedprocedurecall is used to call a stored procedure. Here, the stored procedure is divided into two types: Action Procedure execution command type and retrieval Stored Procedure Query type. For a stored procedure of the query type, you can set the result to filldataset, you can also use filldatatable.
The proxy code of filldataset is as follows:

Public dataset filldataset ()
{
Dataset toreturn = new dataset (_ mappedcallname );
_ Dataaccessprovider. callretrievalstoredprocedure (_ storedprocedurename, _ parameters. toarray (), toreturn );
Return toreturn;
}
The code for filldatatable is as follows:

Public datatable filldatatable ()
{
Datatable toreturn = new datatable (_ mappedcallname );
_ Dataaccessprovider. callretrievalstoredprocedure (_ storedprocedurename, _ parameters. toarray (), toreturn );
Return toreturn;
}
Both methods specify the callretrievalstoredprocedure method in dataaccessadapterbase.

Public Virtual bool callretrievalstoredprocedure (string storedproceduretocall, dbparameter [] parameters, datatable tabletofill)
{
Using (dbcommand command = createstoredprocedurecallcommand (storedproceduretocall, parameters ))
{
Using (dbdataadapter adapter = createnewphysicaldataadapter ())
{
Adapter. selectcommand = command;
Adapter. Fill (tabletofill );
}
}
Return true;
}

The generic dbdataadapter and dbcommand are called to execute the stored procedure. It has an overload method to put the result into dataset.

Public Virtual bool callretrievalstoredprocedure (string storedproceduretocall, dbparameter [] parameters, dataset datasettofill)
{
Using (dbcommand command = createstoredprocedurecallcommand (storedproceduretocall, parameters ))
{
Using (dbdataadapter adapter = createnewphysicaldataadapter ())
{
Adapter. selectcommand = command;
Adapter. Fill (datasettofill );
}
}
Return true;
}

Dataaccessadapterbase method the producecorrectstoredprocedurename method is used to generate a call Method for a stored procedure based on the name of the stored procedure.
String idataaccesscore. producecorrectstoredprocedurename (string storedproceduretocall)
{
Return createcorrectstoredprocedurename (storedproceduretocall );
}

Note that this method is public by default, because dataaccessadapterbase implements the idataaccessadapter interface.

continue to the createcorrectstoredprocedurename method of dataaccessadapterbase
protected virtual string encode (string storedproceduretocall)
{< br> dynamicqueryenginebase DQE = createdynamicqueryengine ();
string procname = DQE. getnewpercallstoredprocedurename (storedproceduretocall);
procname = DQE. getnewstoredprocedurename (procname);
return procname;
}

DQE is displayed again. The call statement is generated using the getnewpercallstoredprocedurename method of DQE.
taking SQL Server as an example, enter the getnewpercallstoredprocedurename method of dynamicqueryengine
Public override string encode (string currentname)
{< br> RegEx procnamepartfinder = _ procmatchingmatcher;
matchcollection matchesfound = procnamepartfinder. matches (currentname);

If (matchesfound. count <= 0)
{< br> // just the proc name, or some weird format we don't support, return the proc name
return currentname;
}

// There's just 1 match:
String catalogname = matchesfound [0]. Groups ["catalogname"]. value;
String schemaname = matchesfound [0]. Groups ["schemaname"]. value;
String procname = matchesfound [0]. Groups ["procname"]. value;
The regular expression procmatchingmatcher is defined as follows:
Private Static readonly RegEx _ procmatchingmatcher = new RegEx (@"((? <Catalogname> \ [[\ W \. \ $ @ #] + \] | \ W + (? = \.)).)? (? <Schemaname> \ [[\ W \. \ $ @ #] + \] | \ W + ).(? <Procname> \ [\ W \. \ $ @ #] + \]) ", regexoptions. Compiled | regexoptions. cultureinvariant | regexoptions. ignorecase );

At the beginning of this method, the regular expression will be applied to the calling syntax of the stored procedure for verification. The format meets the requirements of such adventureworks. DBO. uspgetemployeemanagers

Continue the code in the getnewpercallstoredprocedurename Method

String toreturn;
If (catalogname. Length <= 0)
{
// No catalog specified
Toreturn = (dbspecificcreatorbase) This. Creator). getnewpercallschemaname (schemaname) + "." + procname;
}
Else
{
// Catalog and schema specified
Toreturn = (dbspecificcreatorbase) This. Creator). encode (catalogname) + "." + (dbspecificcreatorbase) This. Creator). Merge (schemaname) + "." + procname;
}
The generated code sqlserverspecificcreator is derived from dbspecificcreatorbase and enters its getnewpercallcatalogname
To determine the catalogname and schemaname of the call. Catalogname is the database name, And schemaname is the name of all stored procedures.

For example, adventureworks. Sales. uspgetemployeemanagers and adventureworks. DBO. uspgetemployeemanagers
In SQL Server, they represent different stored procedures.

For example, there are two versions of uspgetemployeemanagers, which are DBO and sales respectively.
Finally, return to the retrievalprocedures method.

Public static datatable uspgetemployeemanagers (system. int32 employeeid, idataaccesscore dataaccessprovider)
{< br> using (storedprocedurecall call = callback (dataaccessprovider, employeeid)
{< br> datatable toreturn = call. filldatatable ();
return toreturn;
}< BR >}

Let's take a look at the third method of the three methods in the generated code. getuspgetemployeemanagerscallasquery
Public static iretrievalquery getuspgetemployeemanagerscallasquery (system. int32 employeeid)
{
Using (dataaccessadapter dataaccessprovider = new dataaccessadapter ())
{
Return createuspgetemployeemanagerscall (dataaccessprovider, employeeid). toretrievalquery ();
}
}

The second method above calls createuspgetemployeemanagerscall. Different from the second method, the toretrievalquery method is called and the iretrievalquery query is returned.
The toretrievalquery code of storedprocedurecall is as follows:
Public iretrievalquery toretrievalquery ()
{
Dbcommand cmd = _ creator. createcommand ();
Cmd. commandtext = _ dataaccessprovider. producecorrectstoredprocedurename (_ storedprocedurename );
Cmd. commandtype = commandtype. storedprocedure;
Iretrievalquery toreturn = new retrievalquery (CMD );
Foreach (dbparameter parameter in _ parameters)
{
Toreturn. Parameters. Add (parameter );
}
Return toreturn;
}
It also generates commands through common dbcommand and dbconnection and sends them to the server.

Conclusion: The dynamicqueryengine of SQL Server is responsible for parsing the calling method of SQL Server-type stored procedure. As shown in the beginning, the execution of the stored procedure still follows the generic dbcommand, dbdataadapter, it has been built into the ORM Support Type Library.

We recommend that you use the database dialects of SQL Server and MySQL respectively
Select * From salesorderheader where salesorderid = @ salesorderid
. This gives you a clearer picture of which code is the work of DQE and which are fixed to the Code in the ORM support.

here, I think of a llbl Gen design idea. For example, to support MySQL and SQL Server, first write the basic types and put them in the ORM supporto Type Library, for example, dynamicqueryenginebase is used to query the basic type of data. dbspecificcreatorbase is used to generate the database dialect method, and then the part that needs to change according to the data type is placed in the generated code, that is, database-specific. Use the ORM design tool to generate a changeable Method Based on the template. These three functions work together to produce powerful and easy-to-use development results.
if you have a set of ORM theories, don't forget to work with an automated code generation tool to achieve rapid development.