SQL Server – Return a list of parameters from a Stored Procedure

In SQL Server, you can query the database to return the parameters that a Stored Procedure contains. This can be achieved like so:

SELECT
    p.name AS Parameter,
    t.name AS [Type]
FROM <DATABASE NAME>.sys.procedures sp
JOIN <DATABASE NAME>.sys.parameters p
    ON sp.object_id = p.object_id
JOIN sys.types t
    ON p.system_type_id = t.system_type_id
WHERE
    sp.name = '<STORED PROCEDURE NAME>' AND t.name != 'sysname' 

You can change the SELECT to a COUNT(*) if you just want the number of parameters.

 

Credit: JodyThttp://stackoverflow.com/a/15431637/1914145

Calculating the time until a specific time of day

In order to calculate the time remaining before a specific time of day, .NET has a nice class called TimeSpan. With TimeSpan you can perform addition or subtraction on DateTime objects.

I ran into a situation where I needed to calculate the time remaining before it was 14:00. If it was 14:00 or greater, then I needed to calculate it for the following day.

The solution was pretty simple:


            DateTime now = DateTime.Now;
            DateTime target =
                new DateTime( DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 14, 00, 00 );

            //Add 1 day to the target time if we're already at 14:00+ today.
            if ( now.Hour >= 14 )
                target = target.AddDays( 1 );

            TimeSpan difference = target - now;

            var timeRemain = difference.ToString( @"hh\:mm\:ss" );

The result is a nice string that looks similar to this:

02:47:22

Hope this helps someone looking for a similar solution!

Simple C# Wrapper Class for SQL Server

Whilst database technology and the way we create and interact with database has changed significantly with the use of Object-Relational Mapping, however prefer to create the Tables and Queries myself within the database as it is my opinion that this offers greater flexibility and easier maintainability.

Upon finding myself writing the same boilerplate code over and over to interact with a database (A problem that ORMS effectively eliminate), I decided to create my own C# Wrapper Class for SQL Server. In doing so, I have significantly reduced the amount of code needed to run a query – simple or complex!

It is worth noting that this wrapper class is being actively developed, with new features added as and when I find that I need them.

The Class

Since the class is several hundred lines long (I like human readable code) I’ve hosted it externally: View Class on PasteBin

Usage

Once included in your project, usage for this class is fairly simple. An example might be:


    //Usage example for the database wrapper class     
     
     private bool validateOrder( )
        {
            //Create a new Database object
            Database database = new Database();
     
            //Add optional parameters to the query
            database.queryParameters.Add(
                new SqlParameter() { ParameterName = "OrderID", Value = txtOrderID.Text.Trim(), SqlDbType = SqlDbType.NvarChar }
            );
            database.queryParameters.Add(
                new SqlParameter() { ParameterName = "EmailAddress", Value = txtEmail.Text.Trim(), SqlDbType = SqlDbType.NvarChar }
            );
            database.queryParameters.Add(
                new SqlParameter() { ParameterName = "PostCode", Value = txtPostCode.Text.Trim(), SqlDbType = SqlDbType.NvarChar }
            );
           
            //Execute the query - give it the name of the stored procedure and set "hasParams" flag to true (or false if none).
            database.runSelect( "RETURNS_GET_ValidateOrder", true);
     
            //The results table is stored within the object and can be accessed as follows:
            if ( database.resultsTable.Rows.Count == 1 )
                return true;
            else
                return false;
        }

Known Caveats

  • This class doesn’t deal with datasets, only datatables.
  • Could have better Error handling – EG: Store SQLExceptions within the object

Please do let me know what you think, and what features you would like to see added.

Disclaimer:
Use of this code is entirely Open-Source. You are free to use it however you want; Commercially or Non-Commercially, but note: I provide absolutely no warranties for it’s use. This material has been posted as educational material, that may not be suitable for a production environment.