CommandAsSql/ExtensionMethods.cs
/// <summary>
/// Turns a parameter object to string
/// </summary>
/// <param name="param">SqlParameter</param>
/// <returns></returns>
Turns a parameter object to string
Turns a parameter object to string
public static string ParameterValueForSQL(this SqlParameter param)
        {
            object paramValue = param.Value; 
            if (paramValue == null) 
                return "NULL"; 
            switch (param.SqlDbType)
            {
                case SqlDbType.Char:
                case SqlDbType.NChar:
                case SqlDbType.NText:
                case SqlDbType.NVarChar:
                case SqlDbType.Text:
                case SqlDbType.Time:
                case SqlDbType.VarChar:
                case SqlDbType.Xml:
                case SqlDbType.Date:
                case SqlDbType.DateTime:
                case SqlDbType.DateTime2:
                case SqlDbType.DateTimeOffset:
                    return $"'{paramValue.ToString().Replace("'", "''")}'";
                case SqlDbType.Bit:
                    return (paramValue.ToBooleanOrDefault(false)) ? "1" : "0";
                case SqlDbType.Structured:
                    var sb = new System.Text.StringBuilder();
                    var dt = (DataTable)paramValue;
                    sb.Append("declare ").Append(param.ParameterName).Append(" ").AppendLine(param.TypeName);
                    foreach (DataRow dr in dt.Rows)
                    {
                        sb.Append("insert ").Append(param.ParameterName).Append(" values (");
                        for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                        {
                            switch (Type.GetTypeCode(dr[colIndex].GetType()))
                            {
                                case TypeCode.Boolean:
                                    sb.Append(Convert.ToInt32(dr[colIndex]));
                                    break;
                                case TypeCode.String:
                                    sb.Append("'").Append(dr[colIndex]).Append("'");
                                    break;
                                case TypeCode.DateTime:
                                    sb.Append("'").Append(Convert.ToDateTime(dr[colIndex]).ToString("yyyy-MM-dd HH:mm")).Append("'");
                                    break;
                                default:
                                    sb.Append(dr[colIndex]); break;
                            }
                            sb.Append(", ");
                        }
                        sb.Length -= 2; 
                        sb.AppendLine(")");
                    }
                    return sb.ToString();
                case SqlDbType.Decimal:
                case SqlDbType.Float:
                    return ((double)paramValue).ToString(System.Globalization.CultureInfo.InvariantCulture).Replace("'", "''");
                default:
                    return paramValue.ToString().Replace("'", "''");
            }
        }
public static string ParameterValueForSQL ( this SqlParameter param ) { object paramValue = param . Value ; if ( paramValue == null ) return " " ; switch ( param . SqlDbType ) { case SqlDbType . Char : case SqlDbType . NChar : case SqlDbType . NText : case SqlDbType . NVarChar : case SqlDbType . Text : case SqlDbType . Time : case SqlDbType . VarChar : case SqlDbType . Xml : case SqlDbType . Date : case SqlDbType . DateTime : case SqlDbType . DateTime2 : case SqlDbType . DateTimeOffset : return $" ' { paramValue . ToString ( ) . Replace ( " " , " " ) } ' " ; case SqlDbType . Bit : return ( paramValue . ToBooleanOrDefault ( false ) ) ? " " : " " ; case SqlDbType . Structured : var sb = new System . Text . StringBuilder ( ) ; var dt = ( DataTable ) paramValue ; sb . Append ( " " ) . Append ( param . ParameterName ) . Append ( " " ) . AppendLine ( param . TypeName ) ; foreach ( DataRow dr in dt . Rows ) { sb . Append ( " " ) . Append ( param . ParameterName ) . Append ( " " ) ; for ( int colIndex = 0 ; colIndex < dt . Columns . Count ; colIndex ++ ) { switch ( Type . GetTypeCode ( dr [ colIndex ] . GetType ( ) ) ) { case TypeCode . Boolean : sb . Append ( Convert . ToInt32 ( dr [ colIndex ] ) ) ; break ; case TypeCode . String : sb . Append ( " " ) . Append ( dr [ colIndex ] ) . Append ( " " ) ; break ; case TypeCode . DateTime : sb . Append ( " " ) . Append ( Convert . ToDateTime ( dr [ colIndex ] ) . ToString ( " " ) ) . Append ( " " ) ; break ; default : sb . Append ( dr [ colIndex ] ) ; break ; } sb . Append ( " " ) ; } sb . Length -= 2 ; sb . AppendLine ( " " ) ; } return sb . ToString ( ) ; case SqlDbType . Decimal : case SqlDbType . Float : return ( ( double ) paramValue ) . ToString ( System . Globalization . CultureInfo . InvariantCulture ) . Replace ( " " , " " ) ; default : return paramValue . ToString ( ) . Replace ( " " , " " ) ; } }
Turns a parameter object to string
Turns a parameter object to string
//assuming param isn't null //TODO: should probably use DBNull.Value instead or in combination with this //TODO: naive code, won't work as is, need to replace later on = NULL with IS NULL at non-Update queries // trailing ', '
None
SqlParameter
this
param
None