Strongly-Typed Auto-Generated Stored Procedure Calls and Entity/POCO Materialization

1 posts, 0 answers
  1. Richardsonke
    Richardsonke avatar
    93 posts
    Member since:
    Jul 2007

    Posted 04 Oct 2011 Link to this post

    Requirements

    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):

    public List<T> UspCarsWithLastUsedSel<T>(int? categoryID)
    {
        OACommand cmd = this.Connection.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "usp_Cars_with_LastUsed_sel";
        OAParameter parameterCategoryID = new OAParameter() {ParameterName = "CategoryID", Value = categoryID, Direction = ParameterDirection.Input};
        cmd.Parameters.Add(parameterCategoryID);
         
        OADataReader dr = cmd.ExecuteReader();
        return this.Translate<T>(dr).ToList();
    }

    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:
    /// <summary>
    /// Generates the methods that execute stored procedures on the database server.
    /// </summary>
    /// <param name="functions">The list of methods.</param>
    private void GenerateInstanceMethods(System.Collections.Generic.IEnumerable<Telerik.OpenAccess.CodeGeneration.CodeFunction> functions)
    {
        functions.ForEach(
            function =>
            {
                GenerateFunction(function);
                GenerateGenericFunction(function);
            });
    }


    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:

    Usings.Add("Telerik.OpenAccess.Data.Common");
    Usings.Add("Telerik.OpenAccess.SPI.dataobjects");

    VB:

    Changes to Context.ttinclude:

    Replace the GenerateInstanceMethods method with the following:

    /// <summary>
    /// Generates the methods that execute stored procedures on the database server.
    /// </summary>
    /// <param name="functions">The list of methods.</param>
    private void GenerateInstanceMethods(System.Collections.Generic.IEnumerable<Telerik.OpenAccess.CodeGeneration.CodeFunction> functions)
    {
        functions.ForEach
        (
            function =>
            {
                this.GenerateFunction(function);
                this.GenerateGenericFunction(function);
            }
        );
    }

    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:

    Usings.Add("Telerik.OpenAccess.Data.Common");
    Usings.Add("Telerik.OpenAccess.SPI.dataobjects");

    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.
Back to Top