SPDG v2

Created an improved version of the SPDG

functionality added

• added returning of SqlDataReader
• added support for stored procedure output parameters
• added support for stored procedure "metadata"
• corrected datamappings to .net
• backward compatibility with version 1

The "settings" for the app can also be done within the stored procedures via "metadata" to indicate functions to be exposed. To make use of this functionality, add the following code anywhere within the stored procedures; the app will make use of regular expressions to detect the "metadata"

/*[SPDG]Skip,ExecuteDataSet,ExecuteDataReader,ExecuteXML,ExecuteNonQuery,ExecuteScalar*/
 OR
 /*[SPDG]ExecuteNonQuery*/
 OR
 /*[SPDG]Skip*/ - to be exclude in class generation 

Only include the functionality you would like to expose for the particular stored procedure. if no "metadata" is included, the app will leave the options for you to fill in.

I've also included support for accessing output parameters from stored procedures. The output will be stored in the public properties of the stored procedure classes.

accessing of the return value from the stored procedure output can be retrieved using the stored procedure class properties

sp_getbinary sp = new sp_getbinary(new byte[50]);
sp.ExecuteNonQuery();
foreach (byte b in sp.dataout)
{
    lResult.Text += b.ToString();
}
lResult.Text += "\n";
sp_getntext nt = new sp_getntext(new string(' ', 4000));
nt.ExecuteNonQuery();
lResult.Text += nt.dataout;

you can download the full source code from Code Project

SPDG - Stored Procedure Data Gateway

This is a follow-up on my previous post with regard to the stored procedure gateway. i've created a utility that anyone working on c#.net and MSSQL can use to simplify the creation of the data layer.

head to code project for the full article

The stored procedure data gateway is a utility that generates classes for each stored procedure within an MSSQL database. Each stored procedure
can be configured to expose only supported functionality (ie. ExecuteDataSet, ExecuteXML, ExecuteNonQuery or ExecuteScalar).

The utility also generates a .csproj that encapsulates the data layer in a single assembly, which can then be included into your existing project
for data access.

I wanted something that could be easily implemented with as little coding on the individual stored procedure classes as possible, with
the base class containing most if not all of the data access logic. since the bulk of the work will be on the individual classes and coding
the logic for each stored procedure. here's a sample of the class that has been generated.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SPDataGateway
{
    public class sp_getfavourites: spbase<sp_getfavourites.eFields>, IExecuteDataSet, IExecuteXML
    {
        public enum eFields { userid }
        public sp_getfavourites ( Int64 userid )
        {
            this.userid = userid;

        }
        public Int64 userid { get; set; }
        public System.Data.DataSet ExecuteDataSet()
        {
            return base.ExecuteDataSet(eFields.userid);
        }
        public string ExecuteXML()
        {
            return base.ExecuteXML(eFields.userid);
        }
        protected override string StoredProcedureName
        {
            get { return "sp_getfavourites"; }
        }
    }
}

Each individual class only encapsulates the function exposed, the stored procedure name and the parameters required; the bulk
of the logic is contained in the base class.

The utility is a single form application that contains these controls:

1. Toolbar
• New - Clears the form
• Open - opens an XML file that contains your project settings.
• Save - saves your settings for the project in XML format (saved in .spg)
• Synchronize - synchornizes the current list with any new stored procedures from the database.
• Write Classes - performs an IO writing of the stored procedure classes and supporting classes.
2. Form
• Connection String - connection string to connect to your database
• Namespace - namespace and assembly name for the cs project being created.
3. Left Pane - Contains the full list of stored procedures and the options for each procedure.
4. Right Pane - contains 3 tabs that holds the following info
• Stored Procedure Parameters - List of parameters of the current selected procedure.
• Stored Procedure - the stored procedure content retrieve from the database.
• C# SP Class - the sample of the class begin generated.

To start off a new project, click on new and key in the connection string and desired namespace for the project; when you are done
click on synchronize and the app will retrieve all stored procedures into the list on the left pane.

You can then proceed to configure each stored procedure, to tell the application if the stored procedure returns a dataset, xml or
scalar value or if there are no return values.

Scrolling through the list will update the right pane with details of the selected stored procedure.

When done, remember to save your work; the application will prompt to create a .spg file which contains an XML document with all
settings. Correspondingly, to retrieve your previous work, click on open and select the saved .spg file.

The utility will create these directories and files:

• \ - project root directory will hold only the .csproj file
• \helpers\ - contains the SQLHelper.cs class from here
• \interfaces\ - contains all the interfaces for the project
• \sps\ - contains the stored procedure classes and base classes