For a long time I’ve been logging all of my exceptions to a table that looks like:

Now for exceptions that were generated by the database I want to also log the exact database call that caused the exception.  It will make debugging go a lot faster.  For this I have a new table:

This will get the exceptionId from the first table and the a string that caused the exception.  Something like:

saveSomething @id=100, @UserName='Jakey',  @IPAddress='100.100.100.100',  @editBy='JBrooks'

So the first part of logging the Exception is two methods on my base class.

public void LogException(Exception ex)

{

    LogException(ex, null);

}

public void LogExceptionx(Exception ex, SqlCommand objBadCmd)

{

    int id = 0;

    if (this._WritingErrorLog == true)

    {

        // don't want to get in an infinite loop.

        return;

    }

    else

    {

        this._WritingErrorLog = true;

    }

    try

    {

        SqlCommand objCmd = this.GetNewCmd("dbo.insertException");

        string message = "";

        // Get the userId of the user that got the error.

        if (System.Web.HttpContext.Current.Request.Cookies["UID"] != null)

            message = System.Web.HttpContext.Current.Request.Cookies["UID"].Value + "|";

        // Add the command that gave the error.

        if (objBadCmd != null && !string.IsNullOrEmpty(objBadCmd.CommandText))

            message += objBadCmd.CommandText + "|";

        message += ex.ToString();

        if (message.Length > 8000)

            message = message.Substring(0, 7999);

        objCmd.Parameters.Add("@Message", SqlDbType.VarChar, 8000).Value = message;

        objCmd.Parameters.Add("@StackTrace", SqlDbType.VarChar, 8000).Value =

            ((ex.StackTrace == null) ? "no stack trace." : ex.StackTrace);

        //insert the exception and get the new ID.

        id = this.ExecuteScalarInt(objCmd);

        if (id > 0 && objBadCmd != null)

            insertExceptionCmd(id, objBadCmd);

    }

    catch (Exception ExLogException)

    {

        this.LogExceptionToFile(ex);

        this.LogExceptionToFile(ExLogException);

    }

    this._WritingErrorLog = false;

}

Now the part that generates the command string is another method that gets called be the method above:

private string getCmdString(SqlCommand objBadCmd)

{

    if (objBadCmd == null)

        return string.Empty;

    if (objBadCmd.Parameters == null || objBadCmd.Parameters.Count == 0)

        return objBadCmd.CommandText;

    StringBuilder sb = new StringBuilder();

    try

    {

        sb.Append(objBadCmd.CommandText+" ");

        for (int i = 0; i < objBadCmd.Parameters.Count; i++)

        {

            SqlParameter p = objBadCmd.Parameters[i];

            if (i > 0)

                sb.Append(", ");

            sb.Append(p.ParameterName + "=");

            if (p.Value == null)

                sb.Append("null");

            else

            {

                switch (objBadCmd.Parameters[i].SqlDbType)

                {

                    case SqlDbType.BigInt:

                    case SqlDbType.Int:

                    case SqlDbType.Float:

                    case SqlDbType.Decimal:

                    case SqlDbType.SmallInt:

                    case SqlDbType.Money:

                    case SqlDbType.Image:

                    case SqlDbType.Real:

                    case SqlDbType.SmallMoney:

                    case SqlDbType.TinyInt:

                    case SqlDbType.DateTimeOffset:

                        sb.Append(p.Value.ToString());

                        break;

                    default:

                        sb.Append("'" + p.Value.ToString() + "'");

                        break;

                }

            }

        }

    }

    catch (Exception ex)

    {

        return "getCmdString created its own error processing:  " + 

            sb.ToString() + "      +++++" + ex.ToString().Substring(0, 8000);

    }

    return sb.ToString();

}

So now I have a complete logging of exception in my applications and it is a simple matter to make a page where I can view them, or make an RSS feed where they are sent to me.

On one of our projects we have a requirement that we have to change our passwords every 90 day.  So I created a page where I can just click a button and it will generate a password, change the password on the database for the user in the connection string and then change the password in the web.config.

The page just has a button that say “Change Password” and a label called lblMessage to show the results.

The code behind just calls the class that does all of the work and then shows a message:

const string CONNSTRINGNAME = "SampleAppConnString";

const string WEBCONFIGFILE = "~/Web.Config";

protected void btnChangePassword_Click(object sender, EventArgs e)

{

if (cConnStringPasswordChanger.ChangePassword(HttpContext.Current.Server.MapPath(WEBCONFIGFILE), 

        CONNSTRINGNAME))

    this.lblMessage.Text = "Password has been changed";

else

    this.lblMessage.Text = "Password has NOT been changed";

}

Now for the class that does all of the work:

public static class cConnStringPasswordChanger

{

    public static bool ChangePassword(string webConfig, string ConnStringName)

    {

        return ChangePassword(webConfig, ConnStringName, 

                Guid.NewGuid().ToString().Replace("-", "").Substring(0, 9));

    }

    // 1. Open the web.config.

    // 2. Change the connString to have the new password.

    // 3. Change the password on the database.

    // 4. Commit the web.config change.

    public static bool ChangePassword(string webConfig, string ConnStringName, 

        string newPassword)

    {

        bool changed = false;

        string oldConnString;

        FileInfo fi = new FileInfo(webConfig);

        if (fi.IsReadOnly)

        {

            File.SetAttributes(webConfig, FileAttributes.Normal);

        }

        fi = null;

        XmlDocument cfgDoc = new XmlDocument();

        cfgDoc.Load(webConfig);

        XmlNode connNode = cfgDoc.SelectSingleNode("//connectionStrings");

        XmlNode myNode = connNode.SelectSingleNode("//add[@name='" + ConnStringName + "']");

        oldConnString = myNode.Attributes["connectionString"].Value;

        string oldPassword = getValue(oldConnString, "Password");

        string newConnString = oldConnString.Replace(oldPassword, newPassword);

        myNode.Attributes["connectionString"].Value = newConnString;

        string userId = getValue(newConnString, "User Id");

        XmlTextWriter writer = new XmlTextWriter(webConfig, null);

        writer.Formatting = Formatting.Indented;

        // last possible second change it on the database.

        changePassword(oldConnString, userId, oldPassword, newPassword);

        try

        {

            cfgDoc.WriteTo(writer);

            writer.Flush();

            writer.Close();

            changed = true;

        }

        catch (Exception ex)

        {

            // error saving web.config change, so change it back on the database.

            changePassword(newConnString, userId, newPassword, oldPassword);

            throw;

        }

        writer = null;

        cfgDoc = null;

        return changed;

    }

    // This function is passed a connection string like:

    // "data source=.\SQLEXPRESS;Initial Catalog=MyDb;User Id=McUser;Password=c99c0472e;"

    // and a partName like "User Id" or "Password".

    // it returns the value for that partName.

    private static string getValue(string connString, string partName)

    {

        int partStart = connString.ToLower().IndexOf(partName.ToLower());

        int partEndSemi; 

        int partEndQuote;

        if (partStart > -1)

            partStart += partName.Length + 1;

        else

            throw new Exception(partName + " not found in connection string");

        partEndSemi = connString.Substring(partStart).IndexOf(";");

        partEndQuote = connString.Substring(partStart).IndexOf("\"");

        if (partEndQuote == -1)

            partEndQuote = connString.Length - partStart - 1;

        if (partEndSemi == -1)

            partEndSemi = connString.Length - partStart - 1;

        return connString.Substring(partStart, Math.Min(partEndQuote, partEndSemi));

    }

    // Call the database to change the password.

    private static bool changePassword(string connString, string loginName, 

        string oldPassword, string newPassword)

    {

        bool changed = false;

        using (SqlConnection connection = new SqlConnection(connString))

        {

            SqlCommand command = new SqlCommand("dbo.sp_password", connection);

            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("@loginame", SqlDbType.NVarChar, 128).Value = loginName;

            command.Parameters.Add("@old", SqlDbType.NVarChar, 128).Value = oldPassword;

            command.Parameters.Add("@new", SqlDbType.NVarChar, 128).Value = newPassword;

            command.Connection.Open();

            command.ExecuteNonQuery();

            changed = true;

        }

        return changed;

    }

}

I had a large number of XML files that each contained hourly prices for a lot of different locations.  I needed to get all of the prices for a single location.  I did this by loading all of the files into a table and then querying the table.

First, I needed to change the rights on my database to allow me to run the xp_cmdshell store procedure.

USE MASTER

GO

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE WITH OVERRIDE

GO

USE MyTestDB

GO

Then I needed to make the temporary table that would hold my file names and load the file names from the directory.

CREATE TABLE #DIR (fileName varchar(100))

INSERT INTO #DIR

EXEC master..xp_CmdShell 'DIR C:\RTHourly\*.xml /B'

A second table is needed hold the content of my XML files,  it is one row for each file. Note the XML data type for the last column called priceData.

create table xmls

(id int identity,

fileName varchar(250),

priceData xml)

Now we are ready to loop thru each file name found in the  #DIR table and load it’s contents into the xmls table.

DECLARE @fileName AS varchar(250)

DECLARE @fullPathName AS varchar(250)

DECLARE file_cursor CURSOR FOR

SELECT fileName

FROM #DIR

WHERE fileName like '%.xml'

OPEN file_cursor

FETCH NEXT FROM file_cursor INTO @fileName;

WHILE @@FETCH_STATUS = 0

BEGIN

    PRINT @fileName

    set @fullPathName = 'C:\RTHourly\'+ @fileName

    -- this must be dynamic sql b/c file name has to be a literal string for OPENROWSET

    exec (

    'INSERT INTO xmls(fileName, priceData)

    SELECT '''+@fileName+''' AS fileName, A.*

    FROM OPENROWSET( BULK '''+@fullPathName+''',SINGLE_CLOB)

    AS A')

    FETCH NEXT FROM file_cursor INTO @fileName;

END

CLOSE file_cursor;

DEALLOCATE file_cursor;

At this point all of the files are loaded so I just need to get the data out for my location “AZ”. 

select priceData.value('(//PriceGroup/Prices/Price[@location = "AZ"]/Detail/@utcTimepoint)[1]',

        'varchar(150)') as time,

priceData.value('(//PriceGroup/Prices/Price[@location = "AZ"]/Detail/@lmp)[1]',

'real') as price,

fileName

from xmls

order by 1 

Now let’s turn off the security hole that we created when we started this little project.

USE MASTER

GO

EXEC sp_configure 'xp_cmdshell', 0

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'show advanced options', 0;

GO

RECONFIGURE;

GO

In the past we would use a Varchar to pass a comma delimited list of IDs to a stored proc. We would then build a big varchar string of the full SQL and run the dynamic SQL.  Starting with SQL Server 2005 you can pass the stored proc XML and do your select without using dynamic SQL.

The stored proc would look something like:

create proc [dbo].[getLocationTypes](@locationIds XML,

@typeIds XML=null)

as  

begin  

set nocount on  

SELECT locationId, typeId

FROM xrefLocationTypes 

WHERE locationId 

IN (SELECT Item.value('.', 'int' )

FROM @locationIDs.nodes('IDList/ID') AS x(Item))

AND (typeId IN

 (SELECT Item.value('.', 'int' )

FROM @typeIds.nodes('IDList/ID') AS x(Item)))

ORDER BY 1, 2

end  

EXECUTE dbo.getLocationTypes '<IDList><ID>1</ID><ID>3</ID></IDList>', 

'<IDList><ID>200</ID><ID>300</ID><ID>400</ID></IDList>'    

Real quick I wanted to know what tables and stored procs were in my ReleaseManagerTest database and not in my ReleaseManager database.  So here is the SQL for that.

select min(DB) as DB, name, type

from

(

select 'ReleaseManagerTest' as DB, name, type 

from ReleaseManagerTest.sys.objects 

union all

select 'ReleaseManager' as DB, name, type

from ReleaseManager.sys.objects

) A

group by name, type

having count(*) <> 2

I kept writing the same SQL over and over while developing to find the names of stored procs or to see what objects I had recently added.  So I created a quick stored proc that I now have in my tool box.

create proc obj(@name varchar(50)=null, @type char(1)=null)

as

begin

set nocount on

select o.name, 

o.type, 

convert(varchar(10),o.create_date,1) created, 

convert(varchar(10),o.modify_date,1) modified

from sys.objects o

where (@name is null or o.name like @name)

and (@type is null or o.type = @type)

and o.is_ms_shipped = 0

order by o.modify_date desc

end

go

So if I just do:
obj ‘get%’, ‘P’

I’ll get

and if I call obj with no parameters I will get a list of all my objects with the most recently modified objects first. This works with SQL 2005 and above.

We were changing our linked server and needed to test every page in our website that used that link server.  None of the pages used it directly, but they did call stored procs that would use it or stored procs that would in turn call other stored procs that us it.    I need to find all of the pages that eventually would use it so that we could test them all.  The fact that it could be nested within stored proc calls made it a little more difficult.   Here is how I did it:

Step 1:  A simple stored proc used for searching for text within other stored procs. 

This work for SQL Server 2005

create proc dbo.Find(@search VARCHAR(100) = '') AS  
begin  
set nocount on  
  
SET @search = '%' + @search + '%'  
  
select object_name(o.object_id) Object, o.type
from sys.sql_modules m  
join sys.objects o on m.object_id = o.object_id  
where m.definition like @search  
order by 1  
  
end  

Step 2: Make a working table to store you work as you process the stored procs.

create table spDoc
(parentObj varchar(500),
obj varchar(500),
[type] varchar(3),
level int)

You will be storing the object name (stored proc, function, or trigger) in the column “obj”.  You will be storing the calling stored proc in the “parentObj” column. 

Step 3: Create a stored proc to process your search.

create proc doc(@obj varchar(256))
as
begin
set nocount on
declare @level int


if exists (select 1 from spDoc where parentObj = @obj)
    return  -- already done.

select @level = isnull(level,0)+1
from spDoc
where obj = @obj

if @level is null
    set @level = 1


--Find all of the objects that have the text of the current object.
insert into spDoc
(obj, [type])
exec Find @obj

-- The @obj is the parent of the rows just created.
update spDoc
set parentObj = @obj,
level = @level
where parentObj is null

--Delete where they are both the same.
delete from spDoc
where parentObj = @obj
and obj = @obj


--Loop thru each of the rows just created
--and recursively call this function.
DECLARE @currentObj varchar(256)

DECLARE @getObjs CURSOR

SET @getObjs = CURSOR FOR
select obj
from spDoc
where parentObj = @obj


OPEN @getObjs

FETCH NEXT
FROM @getObjs INTO @currentObj

WHILE @@FETCH_STATUS = 0
BEGIN
    exec doc @currentObj  -- recursive call

    FETCH NEXT
    FROM @getObjs INTO @currentObj
END

CLOSE @getObjs
DEALLOCATE @getObjs

end

If you were trying to document the object called “MyLink” you would call it like:

   doc MyLink

Step 4: Get your results.

Once the processing is done you only need a listing of the parent objects and this is the list that you would use to check your front end code.  

select distinct parentObj
from spDoc
order by 1

And that is all there is to it.

Part of my pattern during development is to:
    1. create a new table
    2. create the stored procs to get data and save data

 This second step is tedious and lends itself to automation.  So I created the stored proc createSPFromTable that takes the name of a table and creates a simple save store proc.  So the example:

createSPFromTable SourceTypes

create proc saveSourceTypes(@id int, @Name varchar(50), @DisplayOrder float, @Status char(1))

as

begin

set nocount on

if @id > 0

    update SourceTypes

    set Name = @Name,

    DisplayOrder = @DisplayOrder,

    Status = @Status

    where id = @id

else

begin

    insert into SourceTypes

    (Name, DisplayOrder, Status)

    values (@Name, @DisplayOrder, @Status)

 set @id = scope_identity()

end

select @id

return @id

end

Below is the stored proc that generates the basic save proc.   Not that you have to pass in the primary key column if it is not named "id".

create proc [dbo].[createSPFromTable](@TableName varchar(250), @idColumn varchar(25)=null)
as
set nocount on

declare @crlf char(2)
set @crlf = char(13)+char(10)

declare @parameterList varchar(8000)
set @parameterList='create proc save'+@TableName+'('

-- This converts sql type to c# type.
SELECT @parameterList=  @parameterList + case when lower(c.COLUMN_NAME) = isnull(@idColumn,'id') then '' else '@'+c.COLUMN_NAME+' ' + c.DATA_TYPE+
CASE c.DATA_TYPE
WHEN 'binary' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'char' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'decimal' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'nchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'numeric' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'nvarchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'varbinary' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'varchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
ELSE '' END+', ' end,
@idColumn = case when @idColumn is null and lower(c.COLUMN_NAME) = 'id' then c.COLUMN_NAME else @idColumn end
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf+'as'+@crlf+'begin'+@crlf+'set nocount on'+@crlf+@crlf+@crlf

set @parameterList=@parameterList+'if @'+@idColumn +' > 0 ' + @crlf

set @parameterList=@parameterList+' update '+@TableName+ @crlf +' set'

SELECT @parameterList=@parameterList+' '+ c.COLUMN_NAME+' = @'+c.COLUMN_NAME+', '+ @crlf
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-4)+ @crlf+ ' where '+@idColumn +' = @'+@idColumn+ @crlf

set @parameterList=@parameterList+'else '+ @crlf +'begin'+@crlf+ ' insert into '+@TableName+@crlf +' ('

SELECT @parameterList=@parameterList+ c.COLUMN_NAME+', '
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf

set @parameterList=@parameterList+' values ('

SELECT @parameterList=@parameterList+'@'+ c.COLUMN_NAME+', '
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf+@crlf

set @parameterList=@parameterList+' set @'+@idColumn+' = scope_identity()'+@crlf

set @parameterList=@parameterList+'end'+@crlf

set @parameterList=@parameterList+'select @'+@idColumn+@crlf

set @parameterList=@parameterList+'return @'+@idColumn+@crlf

set @parameterList=@parameterList+'end'+@crlf
print @parameterList

 

Part of my pattern during development is to:
    1. create a new table
    2. create the stored procs to get data and save data
    3. create the data access layer methods that call the stored procs.

This is a little tedious so I created SQL stored proc called createDALFromSP to generate a data access method from a stored proc.  

To create a DAL method for a stored proc called SaveRequest I would run this from SQL Analyzer:

      createDALFromSP 'SaveRequest'

An what it generates looks like the following:

public void SaveRequest(Int32 ID, String Name, Guid UserID, String Comments, Boolean IsSpecial, Double BuildOrder, String status, Boolean clear)
{
    SqlCommand cmd = GetNewCmd("dbo.SaveRequest");

    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
    cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = Name;
    cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = UserID;
    cmd.Parameters.Add("@Comments", SqlDbType.VarChar, 8000).Value = Comments;
    cmd.Parameters.Add("@IsSpecial", SqlDbType.Bit).Value = IsSpecial;
    cmd.Parameters.Add("@BuildOrder", SqlDbType.Float).Value = BuildOrder;
    cmd.Parameters.Add("@status", SqlDbType.Char, 1).Value = status;
    cmd.Parameters.Add("@clear", SqlDbType.Bit).Value = clear;

    cmd.Parameters["@ID"].Direction = ParameterDirection.InputOutput;
    ExecuteNonQuery(cmd);
}
 

Below is the stored proc createDALFromSP, it looks like a mess but it is basic stuff.  This works on SQL Server 2000 and 2005.

create  proc dbo.createDALFromSP(@procName varchar(250))
as
begin

declare @parameterList varchar(8000)
declare @isGet bit

select @isGet = case when lower(@procName) like 'get%' then 1 else 0 end

if @isGet = 1
    set @parameterList='public DataTable '+@procName+'('   
else
    set @parameterList='public void '+@procName+'('

-- get the signiture of the method itelf - so convert SQL Server data types to C# datatypes
SELECT @parameterList=@parameterList+(SELECT CASE DATA_TYPE
        WHEN 'bigint' THEN 'Int64'
        WHEN 'binary' THEN 'Binary'
        WHEN 'bit' THEN 'Boolean'
        WHEN 'char' THEN 'String'
        WHEN 'datetime' THEN 'DateTime'
        WHEN 'decimal' THEN 'Decimal'
        WHEN 'float' THEN 'Double'
        WHEN 'image' THEN 'Binary'
        WHEN 'int' THEN 'Int32'
        WHEN 'money' THEN 'Decimal'
        WHEN 'nchar' THEN 'String'
        WHEN 'ntext' THEN 'String'
        WHEN 'numeric' THEN 'Decimal'
        WHEN 'nvarchar' THEN 'String'
        WHEN 'real' THEN 'Single'
        WHEN 'smalldatetime' THEN 'DateTime'
        WHEN 'smallint' THEN 'Int16'
        WHEN 'smallmoney' THEN 'Currency'
        WHEN 'sql_variant' THEN 'String'
        WHEN 'sysname' THEN 'String'
        WHEN 'text' THEN 'String'
        WHEN 'timestamp' THEN 'Binary'
        WHEN 'tinyint' THEN 'Byte'
        WHEN 'uniqueidentifier' THEN 'Guid'
        WHEN 'varbinary' THEN 'Binary'
        WHEN 'varchar' THEN 'String'
                ELSE '!MISSING' END)+' '+substring(PARAMETER_NAME,2,300)+', ' 
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
order by ORDINAL_POSITION

if @@rowcount > 0
       set @parameterList=substring(@parameterList,1,len(@parameterList)-1)
set @parameterList=@parameterList+')'

-- This is the body of the method
-- Most of the work is to convert sql data types to SqlDbType while listing out the parameters.
select Line
from (
select -99 SortOrder, @parameterList Line
union all
select -98, '{'
union all
select -97, '    SqlCommand cmd = GetNewCmd("dbo.'+@procName+'");'
union all
select -96,''
union all
select ORDINAL_POSITION, '    cmd.Parameters.Add("'+PARAMETER_NAME+'", SqlDbType.'+
    (SELECT CASE DATA_TYPE
    WHEN 'bigint' THEN 'BigInt'
    WHEN 'binary' THEN 'Binary, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'bit' THEN 'Bit'
    WHEN 'char' THEN 'Char, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'datetime' THEN 'DateTime'
    WHEN 'decimal' THEN 'Decimal, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'float' THEN 'Float'
    WHEN 'image' THEN 'Image'
    WHEN 'int' THEN 'Int'
    WHEN 'money' THEN 'Money'
    WHEN 'nchar' THEN 'NChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'ntext' THEN 'NText'
    WHEN 'numeric' THEN 'Decimal, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'nvarchar' THEN 'NVarChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'real' THEN 'Real'
    WHEN 'smalldatetime' THEN 'SmallDateTime'
    WHEN 'smallint' THEN 'SmallInt'
    WHEN 'smallmoney' THEN 'SmallMoney'
    WHEN 'sql_variant' THEN 'Variant'
    WHEN 'text' THEN 'Text'
    WHEN 'timestamp' THEN 'Timestamp'
    WHEN 'tinyint' THEN 'TinyInt'
    WHEN 'uniqueidentifier' THEN 'UniqueIdentifier'
    WHEN 'varbinary' THEN 'VarBinary, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'varchar' THEN 'VarChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    ELSE '!MISSING'
                         END)+').Value = '+substring(PARAMETER_NAME,2,300)+';'
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
union all
select 901,''
union all
SELECT 902,'    cmd.Parameters["'+PARAMETER_NAME+'"].Direction = ParameterDirection.InputOutput;'
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
and PARAMETER_MODE = 'INOUT'
union all
SELECT 903,'    cmd.Parameters["'+PARAMETER_NAME+'"].Direction = ParameterDirection.Output;'
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
and PARAMETER_MODE = 'OUT'
union all
select 904,case when @isGet = 1 then '    return GetTable(cmd);' else '    ExecuteNonQuery(cmd);' end
union all
select 905, '}'
 ) x
order by SortOrder


end