|
RadControls version
|
2011.2.908.1
|
| .NET version |
3.5+
|
| Visual Studio version |
2010
|
| programming language |
VB/C#
|
PROJECT DESCRIPTION
While it is easy to wire up stored procedures to handle the insert, update, and delete events on entities, in OpenAccess Q2 2011 querying a database using a stored procedure that returns a result set is not as straight forward. The
documentation on calling stored procedures and materializing the result set(s) works fine, but can be a lot of code if you have a significant number of stored procedures. In order to streamline the use of stored procedures, I added some additional functions to the standard code generation templates. After adding your stored procedures to the model, it will automatically methods like this (added to the [rlinqfilename].cs file):
As you probably noticed, this is a generic function that you can pass in the return type. If you pass in an Entity type, it will wire it up just like any other entity and you will be able to use the properties of the object(s) to access other entities through associations. If you pass in any other CLR object, it will wire up the properties on that object where the name of the property is equal to the name of a column in the result set. All the parameters of the stored procedure are also strongly typed into the function call. This means that if you update your stored procedure parameters, you will know at compile time if any of your stored procedure calls are using the old parameters instead.
These are the modifications you need to make to your code generation templates to add this functionality:
C#:
Changes in Context.ttinclude:
Replace the GenerateInstanceMethods function with the following:
Add these two functions:
private void GenerateGenericFunction(Telerik.OpenAccess.CodeGeneration.CodeFunction function)
{
string clrParametersDeclaration = GetClrParametersDeclarationForFunction(function);
string sqlParameterVariablesNames = GetSqlParameterVariablesNames(function);
string questionMarks = GetQuestionMarkTokensForFunction(function);
string executionParameters = GetExecutionParametersForFunction(function);
string sqlParameterDefinition = string.Empty;
if(string.IsNullOrEmpty(executionParameters))
{
sqlParameterDefinition = "null";
}
else
{
sqlParameterDefinition ="sqlParameters,";
}
string sqlName = string.Empty;
if (function.UserData.Contains("SqlName"))
{
sqlName = function.UserData["SqlName"].ToString().Replace("'", "");
}
this.PushIndent("\t");
GenerateCustomAttributes(function.Attributes);
this.PopIndent();
#>
public List<T> <#= function.Name #><T>(<#= clrParametersDeclaration #>)
{
OACommand cmd = this.Connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "<#= sqlName #>";
<#+
string dictionaryName= string.Empty;
if(function.Parameters.Count > 0)
{
for (int i = 0; i < function.Parameters.Count; i++)
{
GenerateOAParameterDeclaration(function.Parameters[i]);
//string variableName = string.Concat("parameter", char.ToUpper(function.Parameters[i].Name[0]), function.Parameters[i].Name.Substring(1));
//this.WriteLine("cmd.Parameters.Add(" + variableName + ")");
}
}
#>
OADataReader dr = cmd.ExecuteReader();
return this.Translate<T>(dr).ToList();
}
<#+
}
private void GenerateOAParameterDeclaration(Telerik.OpenAccess.CodeGeneration.CodeParameter parameter)
{
string name = parameter.UserData["SqlName"] as string;
string type = parameter.UserData["OpenAccessType"] as string;
string mode = "Input";
string variableName = string.Concat("parameter", char.ToUpper(parameter.Name[0]), parameter.Name.Substring(1));
if (IsParameterOut(parameter))
{
mode = "Output";
}
#>
OAParameter <#=variableName#> = new OAParameter() {ParameterName = "<#= name #>", Value = <#= parameter.Name #>, Direction = ParameterDirection.<#= mode #>};
cmd.Parameters.Add(<#=variableName#>);
<#+
}
Changes to General.ttinclude:
Add the two lines below to the InitilizeDefaultUsings() method right below the other Usings.Add lines:
VB:
Changes to Context.ttinclude:
Replace the GenerateInstanceMethods method with the following:
Add the following two functions:
private void GenerateGenericFunction(Telerik.OpenAccess.CodeGeneration.CodeFunction function)
{
string clrParametersDeclaration = this.GetClrParametersDeclarationForFunction(function);
string sqlParameterVariablesNames = this.GetSqlParameterVariablesNames(function);
string questionMarks = this.GetQuestionMarkTokensForFunction(function);
string executionParameters = this.GetExecutionParametersForFunction(function);
string sqlParameterDefinition = string.Empty;
if(string.IsNullOrEmpty(executionParameters))
{
sqlParameterDefinition = "Nothing";
}
else
{
sqlParameterDefinition ="sqlParameters,";
}
string sqlName = string.Empty;
if (function.UserData.Contains("SqlName"))
{
sqlName = function.UserData["SqlName"].ToString().Replace("'", "");
}
this.PushIndent("\t");
this.GenerateCustomAttributes(function.Attributes);
this.PopIndent();
#>
Public Function <#= function.Name #>(of T)(<#= clrParametersDeclaration #>) As List(of T)
Dim cmd as OACommand = Me.Connection.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "<#= sqlName #>"
<#+
string dictionaryName= string.Empty;
if(function.Parameters.Count > 0)
{
for (int i = 0; i < function.Parameters.Count; i++)
{
this.GenerateOAParameterDeclaration(function.Parameters[i]);
//string variableName = string.Concat("parameter", char.ToUpper(function.Parameters[i].Name[0]), function.Parameters[i].Name.Substring(1));
//this.WriteLine("cmd.Parameters.Add(" + variableName + ")");
}
}
#>
Dim dr as OADataReader = cmd.ExecuteReader()
return Me.Translate(of T)(dr).ToList()
End Function
<#+
}
private void GenerateOAParameterDeclaration(Telerik.OpenAccess.CodeGeneration.CodeParameter parameter)
{
string name = parameter.UserData["SqlName"] as string;
string type = parameter.UserData["OpenAccessType"] as string;
string mode = "Input";
string variableName = string.Concat("parameter", char.ToUpper(parameter.Name[0]), parameter.Name.Substring(1));
if (IsParameterOut(parameter))
{
mode = "Output";
}
#>
Dim <#=variableName#> As New OAParameter With {.ParameterName = "<#= name #>", .Value = <#= parameter.Name #>, .Direction = ParameterDirection.<#= mode #>}
cmd.Parameters.Add(<#=variableName#>)
<#+
}
Changes to General.ttinclude:
Add the two lines below to the InitilizeDefaultUsings() method right below the other Usings.Add lines:
You can create other versions of the GenerateGenericFunction method to return multiple result sets (use multiple generic parameters), a single object, or a scalar value.
Attached is a demo showing the stored procedures in action. There are C# and VB libraries with a console application that demonstrates calling the stored procedures. There is a SQL script in a solution folder that you can run on your SofiaCarRental database to add the required stored procedures.
Please note that the attached solution is based on my common base class example. Be aware that you will need to update the location of the code generation template and the RLINQ file before using the sample. To do this, open the domain model with an XML editor, find the following XML paths and update the node values (representing directories and files) to match your local ones:
DomainModel \ ModelSettings \ CodeGenerationSettings \ OutputPath - the project folder
DomainModel \ ModelSettings \ CodeGenerationSettings \ CustomTemplateFileName - location of the DefaultTemplate[Language].tt file
DomainModel \ ModelSettings \ SchemaUpdateSetting \ DeploymentDirectory - the project folder
Hope this helps.