C# / SqlServer : Dynamic SqlParameter creation

[Kreij]

For any of you who roll you own CRUD parameters for SQL Server database table access, this will save you a ton of time. The code below will query the table you give it and automatically generate the SqlParameters for your commands. It parses the table and gets the column name, type and size.

First, my SqlUtilities class ...

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
// using System.Windows.Forms; // Uncomment this if you want to use a MessageBox

namespace Revelation
{
    public static class SqlUtilities
    {
        private static SqlConnection connection = new SqlConnection(Properties.Settings.Default.DBConnection);

        private static List<KeyValuePair<string, SqlDbType>> dbTypeList = new List<KeyValuePair<string, SqlDbType>> {
            new KeyValuePair<string, SqlDbType>("int", SqlDbType.Int),
            new KeyValuePair<string, SqlDbType>("varchar", SqlDbType.VarChar),
            new KeyValuePair<string, SqlDbType>("bit", SqlDbType.Bit),
            new KeyValuePair<string, SqlDbType>("datetime", SqlDbType.DateTime),
            new KeyValuePair<string, SqlDbType>("decimal", SqlDbType.Decimal)
            // Add any or all of the SqlDbTypes into this list.
        };

        private static SqlDbType FindInDbTypeList(string NativeDbType)
        {
            foreach (KeyValuePair<string, SqlDbType> _DbListItem in dbTypeList)
            {
                if (_DbListItem.Key == NativeDbType) return _DbListItem.Value;
            }

            return SqlDbType.Variant; // If all the possible choices are in the List, this is not good.
        }

        private static DataSet GetColumsAndTypes(string Table)
        {
            DataSet _DataSet;

            SqlCommand _Command = new SqlCommand("SELECT syscolumns.name, syscolumns.length, systypes.name FROM syscolumns " +
                "INNER JOIN systypes ON syscolumns.xtype = systypes.xtype " +
                "WHERE syscolumns.id = object_id('" + Table + "')", connection);

            SqlDataAdapter _Adapter = new SqlDataAdapter(_Command);
            _Adapter.Fill(_DataSet = new DataSet(), Table);

            return _DataSet;
        }

        public static SqlParameter[] GetParameters(string Table)
        {
            SqlParameter[] _Parameters = new SqlParameter[0];
            DataSet _DataSet = GetColumsAndTypes(Table);

            foreach (DataRow _Row in _DataSet.Tables[0].Rows)
            {
                Array.Resize(ref _Parameters, _Parameters.Length + 1);
                _Parameters[_Parameters.Length - 1] = new SqlParameter("@" + _Row[0].ToString(), FindInDbTypeList(_Row[2].ToString()),
                    Convert.ToInt32(_Row[1]), _Row[0].ToString());
            }

            return _Parameters;
        }
    }
}

To utilize this static class from your code (this one uses a stored procedure) ...

SqlCommand _Command = new SqlCommand("my_StoredProcedure", connectionString);
_Command.CommandType = CommandType.StoredProcedure;
SqlParameter _Parameters = SqlUtilities.GetParameters("Your table name");
_Command.Parameters.AddRange(_Parameters);

If you have an autonumbering identity column that you want updated on an insert just add this after the "AddRange" line above ...

_Command.Parameters["@YourColumnName"].Direction = ParameterDirection.Output;

Add the command(s) to the SqlDataAdapter after you create them...
(Remember you must have a new instance of SqlParameters for each command)

_myAdapter.InsertCommand = _Command;

Delete commands are usually only a couple of lines, so I do not user the auto-parameter creation code for them.

Sorry for not color coding everything. It's a pain in the lower posterior region, and I am writing an offline BBCode editor as you read this to make my life easier when posting this stuff.

I have left out a lot of details to keep this from being a novel, so if anyone has any questions feel free to post.

Oh, and by the way, you may have noticed that the namespace (and project name) is "Revelation".
That's the project I use to test new, unproven stuff. Seems a fitting name.

Happy coding !!

 

[Kreij]

Okay. The code above has been working fine, but it still requires me to do to much typing in the classes and methods that use it.

So I added functionality so you can get all of the commands (INSERT, UPDATE, DELETE) in one call.

Here is the updated class ...

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
// using System.Windows.Forms; // Uncomment this if you want to use a MessageBox

namespace Revelation
{
    public static class SqlUtilities
    {
        private static SqlConnection connection = new SqlConnection(Properties.Settings.Default.DBConnection);

        private static List<KeyValuePair<string, SqlDbType>> dbTypeList = new List<KeyValuePair<string, SqlDbType>> {
            new KeyValuePair<string, SqlDbType>("int", SqlDbType.Int),
            new KeyValuePair<string, SqlDbType>("varchar", SqlDbType.VarChar),
            new KeyValuePair<string, SqlDbType>("bit", SqlDbType.Bit),
            new KeyValuePair<string, SqlDbType>("datetime", SqlDbType.DateTime),
            new KeyValuePair<string, SqlDbType>("decimal", SqlDbType.Decimal),
            new KeyValuePair<string, SqlDbType>("image", SqlDbType.Image)
            // Add any or all of the SqlDbTypes into this list.
        };

        private static SqlDbType FindInDbTypeList(string NativeDbType)
        {
            foreach (KeyValuePair<string, SqlDbType> _DbListItem in dbTypeList)
            {
                if (_DbListItem.Key == NativeDbType) return _DbListItem.Value;
            }

            return SqlDbType.Variant; // If all the possible choices are in the List, this is not good.
        }

        private static DataSet GetColumsAndTypes(string Table)
        {
            DataSet _DataSet;

            SqlCommand _Command = new SqlCommand("SELECT syscolumns.name, syscolumns.length, systypes.name FROM syscolumns " +
                "INNER JOIN systypes ON syscolumns.xtype = systypes.xtype " +
                "WHERE syscolumns.id = object_id('" + Table + "')", connection);

            SqlDataAdapter _Adapter = new SqlDataAdapter(_Command);
            _Adapter.Fill(_DataSet = new DataSet(), Table);

            return _DataSet;
        }

        private static SqlParameter[] GetParameters(string Table)
        {
            SqlParameter[] _Parameters = new SqlParameter[0];
            DataSet _DataSet = GetColumsAndTypes(Table);

            foreach (DataRow _Row in _DataSet.Tables[0].Rows)
            {
                Array.Resize(ref _Parameters, _Parameters.Length + 1);
                _Parameters[_Parameters.Length - 1] = new SqlParameter("@" + _Row[0].ToString(), FindInDbTypeList(_Row[2].ToString()),
                    Convert.ToInt32(_Row[1]), _Row[0].ToString());
            }

            return _Parameters;
        }

        private static SqlCommand CreateInsertCommand(string Table, string CommandString, string IdentityColumn, bool IsStoredProcedure)
        {
            SqlCommand _Command = new SqlCommand(CommandString, connection);
            if (IsStoredProcedure) _Command.CommandType = CommandType.StoredProcedure;
            _Command.Parameters.AddRange(GetParameters(Table));
            _Command.Parameters["@" + IdentityColumn].Direction = ParameterDirection.Output;

            return _Command;
        }

        private static SqlCommand CreateUpdateCommand(string Table, string CommandString, bool IsStoredProcedure)
        {
            SqlCommand _Command = new SqlCommand(CommandString, connection);
            if (IsStoredProcedure) _Command.CommandType = CommandType.StoredProcedure;
            _Command.Parameters.AddRange(GetParameters(Table));

            return _Command;
        }

        private static SqlCommand CreateDeleteCommand(string Table, string IdentityColumn)
        {
            SqlCommand _Command = new SqlCommand("DELETE FROM " + Table + " WHERE " + IdentityColumn + " = @" + IdentityColumn, connection);
            _Command.Parameters.Add(new SqlParameter("@" + IdentityColumn, SqlDbType.Int, sizeof(int), IdentityColumn));

            return _Command;
        }

        public static void CreateAdapterCommands(ref SqlDataAdapter Adapter, string Table, string InsertCommandString, bool IsInsertStoredProcedure, 
            string UpdateCommandString, bool IsUpdateStoredProcedure, string IdentityColumn)
        {
            Adapter.InsertCommand = CreateInsertCommand(Table, InsertCommandString, IdentityColumn, IsInsertStoredProcedure);
            Adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
            Adapter.UpdateCommand = CreateUpdateCommand(Table, UpdateCommandString, IsUpdateStoredProcedure);
            Adapter.DeleteCommand = CreateDeleteCommand(Table, IdentityColumn);
        }
    }
}

All you have to do now is call the "CreateAdapterCommands" method and supply the appropriate arguments.

SqlUtilities.CreateAdapterCommands(ref myAdapter, myTable, (insert command string or stored proc name), (true is stored proc else false), (update command string or stored proc), (true if stored proc else false), myIdentityColumn);

This class is written specifically for what I am doing, but you can see how it is easily adapted to whatever you want to do. I will continue to add functionality to it so that it will become a better replacement for the SqlCommandBuilder class.

You can create a library project and add this class. You can then compile it (as a Dll) and reference it in you project(s) if you want.

More when I update it or get ideas from all of you for more functionality.

 

[Kreij]

Well, wouldn't you know it. Just when things seems all fat and happy ....

In the above code, there are problems with some of the SqlDbTypes.
They are text, ntext and image. (the following applies also to varchar(MAX), nvarchar(MAX) and varbinary(MAX) )

All of these types are a type of varchar, nvarchar or varbinary type. When you declare a column in a table of these types, Sql server does not store the data "in row", but basically stores a pointer to the actual data. Sql server then know that when you want to retrieve the data, where to find the entire thing.

The issue is that the systypes table stores the length of these three types as 16 (or -1 if they are (MAX) types), not the actual size of the text/image that is contained in the field. As you can imagine, when you try to SELECT, INSERT or UPDATE a field of greater that 16 characters, with my code above, it truncates the field to 16 bytes (or just pukes on a size of -1).

Not good.

These sql server types hold between 0 and their maximum number of character/bytes.
Here are the limits ...
text and varchar(MAX) ... 2,147,483,647 bytes
ntext and nvarchar(MAX) ... 1,073,741,823 bytes
image and varbinary(MAX) ... 2,147,483,647 bytes

My current workaround for this is to detect if the paramter is one of these types and instead of pulling the size from the syscolums, substitute either the maximum value or an arbitrary value that you know will be at least as big as the largest entry into this database column.

This is not a great workaround, but it does work. I will post more when I feel I have a best-method answer.

Oh ... and by the way ... the text, ntext and image sql server datatypes are going to be phased out (to be replaced with varchar(MAX), nvarchar(MAX) and varbinary(MAX)) in upcoming versions, so use the varchar, nvarchar or varbinary in your new developments and plan on modifying these existing types if you are planning on updating you Sql Server version in the future.

 

[Kreij]

Okay, while I am not sure this is the best way to do this, it seems to work well for what I have tested.

Instead of just feeding the returned parameter size from the system tables into the parameter array, I check for the incidence of the type size (by type name).

So the new GetParameters method is ...

private static SqlParameter[] GetParameters(string Table)
{
   SqlParameter[] _Parameters = new SqlParameter[0];
   DataSet _DataSet = GetColumsAndTypes(Table);

    foreach (DataRow _Row in _DataSet.Tables[0].Rows)
    {
        Array.Resize(ref _Parameters, _Parameters.Length + 1);
        _Parameters[_Parameters.Length - 1] = new SqlParameter("@" + _Row[0].ToString(), FindInDbTypeList(_Row[2].ToString()),
        [color=Purple]GetVariableParameterSize(Convert.ToInt32(_Row[1]),_Row[2].ToString()), _Row[0].ToString())[/color];
        }

    return _Parameters;
}

... and the method it calls to get the "real" size is ...

private static int GetVariableParameterSize(int returnedSize, string varType)
{
    int _RealSize = returnedSize;

    if (returnedSize == 16 || returnedSize == -1)
    {
        switch (varType)
        {
            case "varchar":
            case "varbinary":
            case "text":
            case "image":
                    _RealSize = 2147483647;
                    break;
            case "ntext":
            case "nvartchar":
                    _RealSize = 1073741823;
                    break;
        }
    }

    return _RealSize;
}

This still leaves a bit to be desired.
For instance, what if you create a varchar, nvarchar or varbinary column in your database of a length 16? I don't want to force a size of over 1 or 2 Billion bytes in that case (although it would not matter as it is only the parameter value and would not really effect the DB column itself).

More reasearch is forthcoming, along with copious amounts of beer. Beer is always good when coding.

 

[adbroke]

Hi Kreij,
My self Ad Broke.
i am working in IT company i am also working in C#.I am facing problem in compare of arraylist menbers.
do you have any suggestion for that.
Both Arraylist are base on string format..
just provide me syntax for that..
Thanks in Advance..

 

[FordGT90Concept]

That's off-topic. Still, you want to use List<> not, ArrayList.

using System.Collections.Generic;

class MyClass
{
  private void MySub()
  {
    List<string> collection = new List<string>();
    collection.Add("MyString1");
    collection.Add("MyString2");
    collection.Add("MyString3");

    foreach (string item in collection)
      Console.WriteLine(item);

    collection.RemoveAt("MyString3");
    collection.RemoveAt(1);
    collection.RemoveAt(0);
  }
}