When we use SqlCommand with a set of parameters, they are being executed as a parametrized query. You may or may not pass the desired type with each SqlParameter. If you do not specify desired database type it will be determined by the ADO provider, but you will not know what the actual SQL type is. Below I provide a code that will give you the actual parameters. This code can also help you find out what the actual SQL query executed in the database is.

protected string AsString(IEnumerable<SqlParameterInfo> parameters)
{
    var r = new StringBuilder("DECLARE ");
    var first = true;
    foreach (var e in parameters)
    {
        if (!first) r.AppendLine(",");
        else first = false;
        r.Append(e.ToString());
    }
    return r.ToString();
}
protected IEnumerable<SqlParameterInfo> GetSqlParameterInfo(
             IEnumerable<IDataParameter> parameters, string connectionString)
{
    var sb = new StringBuilder(@"SELECT ");
    sb.AppendLine();
    var first = true;
    foreach (var p in parameters)
    {
        if (!first) sb.AppendLine(", ");
        else first = false;
        sb.Append(p.ParameterName + " as [" + p.ParameterName.Replace("@", "") + "]");
    }
    sb.AppendLine();
    sb.Append("INTO #tempGetSqlParameterInfo");
    sb.AppendLine();
    sb.Append(@" 
    SELECT 
        COLUMN_NAME, 
        DATA_TYPE, 
        '('+CAST(CHARACTER_MAXIMUM_LENGTH as varchar(100))+')' as CHARACTER_MAXIMUM_LENGTH 
    FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME like '#tempGetSqlParameterInfo%'");

    var list = new List<SqlParameterInfo>();
    using (var conn = new SqlConnection(connectionString))
    {

        var command = new SqlCommand
        {
            CommandText = sb.ToString(),
            CommandType = CommandType.Text,
            Connection = conn
        };
        command.Parameters.AddRange(parameters.ToArray());
        conn.Open();


        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                var spi = new SqlParameterInfo();
                spi.Value = parameters.Where(p => p.ParameterName == spi.Name)
                                      .Select(p => p.Value)
                                      .FirstOrDefault();
                spi.Name = "@" + reader["COLUMN_NAME"] as string;
                spi.Type = reader["DATA_TYPE"] as string;
                var cml = reader["CHARACTER_MAXIMUM_LENGTH"];
                spi.Precision = cml == DBNull.Value ? "" : cml.ToString();

                list.Add(spi);
            }
        }

        conn.Close();
    }
    return list;
}

public class SqlParameterInfo
{
    public string Name;
    public string Type;
    public string Precision;
    public object Value;
    public override string ToString()
    {
        return Name + " " + Type + Precision+ 
                " = "+(Value == null || Value == DBNull.Value ? "NULL" : "'"+Value+"'")+"";
    }
}

As you can see I save the list of parameters in a temp database table and then read the column definitions from the schema information. You can use this code as:

Console.WriteLine(AsString(parameters));

Share this post:   digg     Stumble Upon     del.icio.us     E-mail

Please post your comments:

Name:  
Email (optional): Your email address will not be posted.
Comments: HTML will be ignored, URLs will be converted to hyperlinks  
Enter the text you see in the box: