/// <summary>
/// 获取 SQL 语句常量表示串
/// </summary>
/// <param name="sdt">{Char,DateTime,NChar,NText,NVarChar,SmallDateTime,Text,UniqueIdentifier,VarChar}</param>
/// <param name="str"></param>
/// <returns></returns>
public static string ConvertToSqlON(System.Data.SqlDbType sdt, string str)
{
if (sdt == System.Data.SqlDbType.Char ||
sdt == System.Data.SqlDbType.DateTime ||
sdt == System.Data.SqlDbType.NChar ||
sdt == System.Data.SqlDbType.NText ||
sdt == System.Data.SqlDbType.NVarChar ||
sdt == System.Data.SqlDbType.SmallDateTime ||
sdt == System.Data.SqlDbType.Text ||
sdt == System.Data.SqlDbType.UniqueIdentifier ||
sdt == System.Data.SqlDbType.VarChar
)
{ // 需要在前后添加单引号(')
string rtn = "'" + str.Replace("'", "''") + "'";
if (sdt == System.Data.SqlDbType.NChar ||
sdt == System.Data.SqlDbType.NText ||
sdt == System.Data.SqlDbType.NVarChar
)
{
rtn = "N" + rtn;
}
return(rtn);
}
return(NonSupportedOutType);
}
private SqlParameter getParameter(string paramName, System.Data.SqlDbType sqlDBType, string paramValue, int paramSize)
{
SqlParameter parameter = new System.Data.SqlClient.SqlParameter(paramName, sqlDBType, paramSize);
parameter.Value = paramValue;
return(parameter);
}
public static SqlParameter CreateParameter(System.String name, System.Data.SqlDbType dbType, System.Object value)
{
SqlParameter prm = new SqlParameter(name, dbType);
prm.Value = value == null ? DBNull.Value : value;
return(prm);
}
private static DbTypeMapEntry Find(System.Data.SqlDbType sqlDbType)
{
object retObj = null;
for (int i = 0; i < _DbTypeList.Count; i++)
{
DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
if (entry.SqlDbType == sqlDbType)
{
retObj = entry;
break;
}
}
if (retObj == null)
{
throw
new ApplicationException("Referenced an unsupported SqlDbType");
}
return((DbTypeMapEntry)retObj);
}
/// <summary>
/// Parameter Builder
/// </summary>
/// <param name="name">Name, please include (at)</param>
/// <param name="dbType">Type</param>
/// <param name="value">Values</param>
/// <returns>SqlParameter</returns>
public static SqlParameter ParameterBuilder(string name, System.Data.SqlDbType dbType, object value)
{
var p = new SqlParameter(name, dbType);
p.Value = value;
return(p);
}
public MetaType(byte precision, byte scale, int fixedLength, bool isFixed, bool isLong, bool isPlp, byte tdsType, byte nullableTdsType, string typeName, Type classType, Type sqlType, System.Data.SqlDbType sqldbType, System.Data.DbType dbType, byte propBytes)
{
this.Precision = precision;
this.Scale = scale;
this.FixedLength = fixedLength;
this.IsFixed = isFixed;
this.IsLong = isLong;
this.IsPlp = isPlp;
this.TDSType = tdsType;
this.NullableType = nullableTdsType;
this.TypeName = typeName;
this.SqlDbType = sqldbType;
this.DbType = dbType;
this.ClassType = classType;
this.SqlType = sqlType;
this.PropBytes = propBytes;
this.IsAnsiType = _IsAnsiType(sqldbType);
this.IsBinType = _IsBinType(sqldbType);
this.IsCharType = _IsCharType(sqldbType);
this.IsNCharType = _IsNCharType(sqldbType);
this.IsSizeInCharacters = _IsSizeInCharacters(this.SqlDbType);
this.Is70Supported = _Is70Supported(this.SqlDbType);
this.Is80Supported = _Is80Supported(this.SqlDbType);
this.Is90Supported = _Is90Supported(this.SqlDbType);
this.Is100Supported = _Is100Supported(this.SqlDbType);
}
public QueryParameter(string name, System.Data.SqlDbType dataType, ParameterDirection direction, int size)
{
this._parameterName = name;
this._parameterDataType = dataType;
this._direction = direction;
this._size = size;
}
public MetaType(byte precision, byte scale, int fixedLength, bool isFixed, bool isLong, bool isPlp, byte tdsType, byte nullableTdsType, string typeName, Type classType, Type sqlType, System.Data.SqlDbType sqldbType, System.Data.DbType dbType, byte propBytes)
{
this.Precision = precision;
this.Scale = scale;
this.FixedLength = fixedLength;
this.IsFixed = isFixed;
this.IsLong = isLong;
this.IsPlp = isPlp;
this.TDSType = tdsType;
this.NullableType = nullableTdsType;
this.TypeName = typeName;
this.SqlDbType = sqldbType;
this.DbType = dbType;
this.ClassType = classType;
this.SqlType = sqlType;
this.PropBytes = propBytes;
this.IsAnsiType = _IsAnsiType(sqldbType);
this.IsBinType = _IsBinType(sqldbType);
this.IsCharType = _IsCharType(sqldbType);
this.IsNCharType = _IsNCharType(sqldbType);
this.IsSizeInCharacters = _IsSizeInCharacters(this.SqlDbType);
this.Is70Supported = _Is70Supported(this.SqlDbType);
this.Is80Supported = _Is80Supported(this.SqlDbType);
this.Is90Supported = _Is90Supported(this.SqlDbType);
this.Is100Supported = _Is100Supported(this.SqlDbType);
}
public static Type GetManagedType(System.Data.SqlDbType nativeType)
{
switch (nativeType)
{
case System.Data.SqlDbType.BigInt:
return(typeof(Int64));
case System.Data.SqlDbType.Binary:
case System.Data.SqlDbType.Image:
case System.Data.SqlDbType.Timestamp:
case System.Data.SqlDbType.VarBinary:
return(typeof(byte[]));
case System.Data.SqlDbType.Bit:
return(typeof(bool));
case System.Data.SqlDbType.Char:
case System.Data.SqlDbType.NChar:
case System.Data.SqlDbType.NText:
case System.Data.SqlDbType.NVarChar:
case System.Data.SqlDbType.Text:
case System.Data.SqlDbType.VarChar:
case System.Data.SqlDbType.Xml:
return(typeof(string));
case System.Data.SqlDbType.DateTime:
return(typeof(DateTime));
case System.Data.SqlDbType.Decimal:
case System.Data.SqlDbType.Money:
case System.Data.SqlDbType.SmallMoney:
return(typeof(decimal));
case System.Data.SqlDbType.Float:
return(typeof(double));
case System.Data.SqlDbType.Int:
return(typeof(int));
case System.Data.SqlDbType.Real:
return(typeof(Single));
case System.Data.SqlDbType.UniqueIdentifier:
return(typeof(Guid));
case System.Data.SqlDbType.SmallDateTime:
return(typeof(DateTime));
case System.Data.SqlDbType.SmallInt:
return(typeof(Int16));
case System.Data.SqlDbType.TinyInt:
return(typeof(byte));
case System.Data.SqlDbType.Variant:
case System.Data.SqlDbType.Udt:
default:
return(typeof(object));
}
}
public static System.Data.DbType GetDbType(System.Data.SqlDbType nativeType)
{
switch (nativeType)
{
case System.Data.SqlDbType.BigInt:
return(DbType.Int64);
case System.Data.SqlDbType.Binary:
case System.Data.SqlDbType.Image:
case System.Data.SqlDbType.Timestamp:
case System.Data.SqlDbType.VarBinary:
return(DbType.Binary);
case System.Data.SqlDbType.Bit:
return(DbType.Boolean);
case System.Data.SqlDbType.Char:
case System.Data.SqlDbType.NChar:
return(DbType.StringFixedLength);
case System.Data.SqlDbType.NText:
case System.Data.SqlDbType.NVarChar:
case System.Data.SqlDbType.Text:
case System.Data.SqlDbType.VarChar:
case System.Data.SqlDbType.Xml:
return(DbType.String);
case System.Data.SqlDbType.DateTime:
case System.Data.SqlDbType.SmallDateTime:
return(DbType.DateTime);
case System.Data.SqlDbType.Decimal:
case System.Data.SqlDbType.Money:
case System.Data.SqlDbType.SmallMoney:
return(DbType.Decimal);
case System.Data.SqlDbType.Float:
return(DbType.Double);
case System.Data.SqlDbType.Int:
return(DbType.Int32);
case System.Data.SqlDbType.Real:
return(DbType.Single);
case System.Data.SqlDbType.UniqueIdentifier:
return(DbType.Guid);
case System.Data.SqlDbType.SmallInt:
return(DbType.Int16);
case System.Data.SqlDbType.TinyInt:
return(DbType.Byte);
case System.Data.SqlDbType.Variant:
case System.Data.SqlDbType.Udt:
default:
return(DbType.Object);
}
}
private int SetParameter(ref SqlDataAdapter cm, string ParameterName, Object Value
, System.Data.SqlDbType ParameterType, int FieldSize = -1
, System.Data.ParameterDirection Direction = System.Data.ParameterDirection.Input
, Byte Precision = 0, Byte Scale = 0)
{
try
{
cm.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
if (FieldSize == -1)
{
cm.SelectCommand.Parameters.Add(ParameterName, ParameterType);
}
else
{
cm.SelectCommand.Parameters.Add(ParameterName, ParameterType, FieldSize);
}
if (Precision > 0)
{
cm.SelectCommand.Parameters[cm.SelectCommand.Parameters.Count - 1].Precision = Precision;
}
if (Scale > 0)
{
cm.SelectCommand.Parameters[cm.SelectCommand.Parameters.Count - 1].Scale = Scale;
}
cm.SelectCommand.Parameters[cm.SelectCommand.Parameters.Count - 1].Value = Value;
cm.SelectCommand.Parameters[cm.SelectCommand.Parameters.Count - 1].Direction = Direction;
return(0);
}
catch (Exception ex) { throw new Exception(ex.Message); }
}
/// <summary>
/// Obtiene un parámetro SqlServer inicializado con los parámetros proporcionados
/// </summary>
/// <param name="Name">Nombre del parámetro Sql</param>
/// <param name="dbType">Tipo de dato</param>
/// <param name="value">Valor del parámetro</param>
/// <returns></returns>
public static SqlParameter Direct(string Name, System.Data.SqlDbType dbType, object value)
{
SqlParameter sPar = new SqlParameter(Name, dbType);
sPar.Value = value == null ? DBNull.Value : value;
return(sPar);
}
public static int ValidateDataTypeMax(this System.Data.SqlDbType type, int length)
{
switch (type)
{
case System.Data.SqlDbType.Char:
return((length > 8000) ? 8000 : length);
case System.Data.SqlDbType.VarChar:
return((length > 8000) ? 8000 : length);
case System.Data.SqlDbType.NChar:
return((length > 4000) ? 4000 : length);
case System.Data.SqlDbType.NVarChar:
return((length > 4000) ? 4000 : length);
case System.Data.SqlDbType.Decimal:
return((length > 38) ? 38 : length);
case System.Data.SqlDbType.Binary:
case System.Data.SqlDbType.VarBinary:
return((length > 8000) ? 8000 : length);
}
return(length);
}
public static string ConvertToExtControl(System.Data.SqlDbType sqltype)
{
string datatype = string.Empty;
switch (sqltype)
{
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
datatype = "datetime";
break;
case SqlDbType.Float:
case SqlDbType.Decimal:
datatype = "float";
break;
case SqlDbType.Int:
case SqlDbType.SmallInt:
datatype = "int";
break;
default:
datatype = "string";
break;
}
return(datatype);
}
public static bool VariableLengthType(System.Data.SqlDbType type)
{
switch (type)
{
case SqlDbType.BigInt: return(false);
case SqlDbType.Binary: return(true);
case SqlDbType.Bit: return(false);
case SqlDbType.Char: return(true);
case SqlDbType.DateTime: return(false);
case SqlDbType.Decimal: return(true);
case SqlDbType.Float: return(false);
case SqlDbType.Image: return(false);
case SqlDbType.Int: return(false);
case SqlDbType.Money: return(false);
case SqlDbType.NChar: return(true);
case SqlDbType.NText: return(false);
case SqlDbType.NVarChar: return(true);
case SqlDbType.Real: return(false);
case SqlDbType.SmallDateTime: return(false);
case SqlDbType.SmallInt: return(false);
case SqlDbType.SmallMoney: return(false);
case SqlDbType.Text: return(false);
case SqlDbType.Timestamp: return(false);
case SqlDbType.TinyInt: return(false);
case SqlDbType.Udt: return(false);
case SqlDbType.UniqueIdentifier: return(false);
case SqlDbType.VarBinary: return(true);
case SqlDbType.VarChar: return(true);
case SqlDbType.Variant: return(false);
case SqlDbType.Xml: return(false);
default: return(false);
}
}
public static string DefaultValueCodeString(System.Data.SqlDbType type)
{
switch (type)
{
case SqlDbType.BigInt: return("long.MinValue");
case SqlDbType.Binary: return("new System.Byte[]{}");
case SqlDbType.Bit: return("false");
case SqlDbType.Char: return("string.Empty");
case SqlDbType.DateTime: return("System.DateTime.MinValue");
case SqlDbType.Decimal: return("0.0m");
case SqlDbType.Float: return("0.0f");
case SqlDbType.Image: return("new System.Byte[]{}");
case SqlDbType.Int: return("int.MinValue");
case SqlDbType.Money: return("0.00m");
case SqlDbType.NChar: return("string.Empty");
case SqlDbType.NText: return("string.Empty");
case SqlDbType.NVarChar: return("string.Empty");
case SqlDbType.Real: return("System.Single.MinValue");
case SqlDbType.SmallDateTime: return("System.DateTime.MinValue");
case SqlDbType.SmallInt: return("0");
case SqlDbType.SmallMoney: return("0.00m");
case SqlDbType.Text: return("string.Empty");
case SqlDbType.Timestamp: return("new System.Byte[]{}");
case SqlDbType.TinyInt: return("0");
case SqlDbType.Udt: return("string.Empty");
case SqlDbType.UniqueIdentifier: return("System.Guid.NewGuid()");
case SqlDbType.VarBinary: return("new System.Byte[]{}");
case SqlDbType.VarChar: return("string.Empty");
case SqlDbType.Variant: return("string.Empty");
case SqlDbType.Xml: return("string.Empty");
default: return("string.Empty");
}
}
public static void addparameter(SqlCommand cmd, String paramname, System.Data.SqlDbType paramtype, int paramsize, ParameterDirection paramdir, Object paramval)
{
SqlParameter objParameter;
objParameter = cmd.Parameters.Add(paramname, paramtype, paramsize);
objParameter.Direction = paramdir;
objParameter.Value = paramval;
}
private static bool _IsAnsiType(System.Data.SqlDbType type)
{
if ((type != System.Data.SqlDbType.Char) && (type != System.Data.SqlDbType.VarChar))
{
return(type == System.Data.SqlDbType.Text);
}
return(true);
}
public DBParameter(string _ParameterName, System.Data.SqlDbType _DbType, int _Size, System.Data.ParameterDirection _ParameterDirection, object _ParamValue)
{
ParameterName = _ParameterName;
DbType = _DbType;
Size = _Size;
ParameterDirection = _ParameterDirection;
ParamValue = _ParamValue;
}
public DbQueryType(System.Data.SqlDbType dbType, bool notNull, int length, short precision, short scale)
{
this.dbType = dbType;
this.notNull = notNull;
this.length = length;
this.precision = precision;
this.scale = scale;
}
private static bool _Is90Supported(System.Data.SqlDbType type)
{
if (!_Is80Supported(type) && (System.Data.SqlDbType.Xml != type))
{
return(System.Data.SqlDbType.Udt == type);
}
return(true);
}
internal static bool _IsVarTime(System.Data.SqlDbType type)
{
if ((type != System.Data.SqlDbType.Time) && (type != System.Data.SqlDbType.DateTime2))
{
return(type == System.Data.SqlDbType.DateTimeOffset);
}
return(true);
}
public virtual SqlParameter AddParameter(string parameterName, System.Data.SqlDbType dbType, int size, object value, System.Data.ParameterDirection direction)
{
SqlParameter param = new SqlParameter(parameterName, dbType);
param.Direction = direction;
param.Value = value;
return(param);
}
private static bool _IsNCharType(System.Data.SqlDbType type)
{
if (((type != System.Data.SqlDbType.NChar) && (type != System.Data.SqlDbType.NVarChar)) && (type != System.Data.SqlDbType.NText))
{
return(type == System.Data.SqlDbType.Xml);
}
return(true);
}
private static bool _IsBinType(System.Data.SqlDbType type)
{
if ((((type != System.Data.SqlDbType.Image) && (type != System.Data.SqlDbType.Binary)) && ((type != System.Data.SqlDbType.VarBinary) && (type != System.Data.SqlDbType.Timestamp))) && (type != System.Data.SqlDbType.Udt))
{
return(type == (System.Data.SqlDbType.SmallInt | System.Data.SqlDbType.Int));
}
return(true);
}
/// <summary>
/// 生成Sql参数
/// </summary>
/// <param name="ParamName">字段名</param>
/// <param name="otype">数据类型</param>
/// <param name="size">数据大小</param>
/// <param name="Value">值</param>
/// <returns></returns>
public static SqlParameter MakeParam(string ParamName, System.Data.SqlDbType otype, int size, Object Value)
{
SqlParameter para = new SqlParameter(ParamName, Value);
para.SqlDbType = otype;
para.Size = size;
return(para);
}
private static bool _Is100Supported(System.Data.SqlDbType type)
{
if ((!_Is90Supported(type) && (System.Data.SqlDbType.Date != type)) && ((System.Data.SqlDbType.Time != type) && (System.Data.SqlDbType.DateTime2 != type)))
{
return(System.Data.SqlDbType.DateTimeOffset == type);
}
return(true);
}
private static bool _IsSizeInCharacters(System.Data.SqlDbType type)
{
if (((type != System.Data.SqlDbType.NChar) && (type != System.Data.SqlDbType.NVarChar)) && (type != System.Data.SqlDbType.Xml))
{
return(type == System.Data.SqlDbType.NText);
}
return(true);
}
public SqlParameter Add(string parameterName, System.Data.SqlDbType sqlDbType, int size, System.Data.ParameterDirection direction, bool isNullable)
{
SqlParameter sParam = new SqlParameter(parameterName, sqlDbType, size);
sParam.Direction = direction;
sParam.IsNullable = isNullable;
return(this.Add(sParam));
}
public static int BytesPerChar(System.Data.SqlDbType sqlDbType)
{
if (sqlDbType == System.Data.SqlDbType.NChar || sqlDbType == System.Data.SqlDbType.NText || sqlDbType == System.Data.SqlDbType.NVarChar)
{
return(2);
}
return(1);
}
public sComParameter(string _ParamName, System.Data.SqlDbType _dbType, int _Length, object _Value, string _TypeName)
{
ParamName = _ParamName;
dbType = _dbType;
Length = _Length;
Value = _Value;
parDir = ParameterDirection.Input;
TypeName = _TypeName;
}
public CellStruct(string columnName, System.Data.SqlDbType sqlType, bool isReadOnly, bool isCanNull, int maxSize, ParameterDirection paraDirection)
{
ColumnName = columnName;
SqlType = sqlType;
IsReadOnly = isReadOnly;
IsCanNull = isCanNull;
MaxSize = maxSize;
ParaDirection = paraDirection;
ValueType = DataType.GetType(sqlType);
}
private SmiMetaData(System.Data.SqlDbType sqlDbType, long maxLength, byte precision, byte scale, SqlCompareOptions compareOptions)
{
this._databaseType = sqlDbType;
this._maxLength = maxLength;
this._precision = precision;
this._scale = scale;
this._compareOptions = compareOptions;
this._localeId = 0L;
this._clrType = null;
this._isMultiValued = false;
this._fieldMetaData = __emptyFieldList;
this._extendedProperties = SmiMetaDataPropertyCollection.EmptyInstance;
}
private void SetDefaultsForType(System.Data.SqlDbType dbType)
{
SmiMetaData defaultForType = GetDefaultForType(dbType);
this._databaseType = dbType;
this._maxLength = defaultForType.MaxLength;
this._precision = defaultForType.Precision;
this._scale = defaultForType.Scale;
this._localeId = defaultForType.LocaleId;
this._compareOptions = defaultForType.CompareOptions;
this._clrType = null;
this._isMultiValued = defaultForType._isMultiValued;
this._fieldMetaData = defaultForType._fieldMetaData;
this._extendedProperties = defaultForType._extendedProperties;
}
/// <summary>
/// Parses a result page for a UNION SELECT error to refine the data type
/// </summary>
/// <param name="HTMLCode">The HTML text from the resulting error page</param>
/// <param name="Plugin">The Plugin to use to identify the data type</param>
/// <returns>The refined data type of the field that caused the error</returns>
public static System.Data.SqlDbType ParseUnionSelectForIntegerRefinement(string HTMLCode, IErrorPlugin Plugin)
{
System.Data.SqlDbType retVal = new System.Data.SqlDbType();
//retVal = TSQL.DataType.Undefined;
int StartError, EndError;
// Check for the first half of the error
StartError = HTMLCode.IndexOf(Plugin.UnionSelectErrorPreVarchar);
UserStatus(HTMLCode);
// Now check for the second half of the error
EndError = HTMLCode.IndexOf(Plugin.UnionSelectErrorPostInt);
if (EndError > StartError || HTMLCode.IndexOf(Plugin.UnionSelectErrorStandaloneInt) > StartError)
{
retVal = SqlDbType.Int;
}
else if(HTMLCode.IndexOf(Plugin.UnionSelectErrorPostFloat) > StartError || HTMLCode.IndexOf(Plugin.UnionSelectErrorStandaloneFloat) > StartError)
{
retVal = SqlDbType.Float;
}
else if(HTMLCode.IndexOf(Plugin.UnionSelectErrorPostSmallint) > StartError)
{
retVal = SqlDbType.SmallInt;
}
else if(HTMLCode.IndexOf(Plugin.UnionSelectErrorStandaloneNumeric) > StartError)
{
retVal = SqlDbType.Int;
}
else if(HTMLCode.IndexOf(Plugin.UnionSelectErrorStandaloneDateTime) > StartError)
{
retVal = SqlDbType.DateTime;
}
else if(HTMLCode.IndexOf(Plugin.UnionSelectErrorStandaloneMoney) > StartError)
{
retVal = SqlDbType.Money;
}
else
{
UserStatus(HTMLCode);
//throw new ApplicationException("Unknown Int");
retVal = SqlDbType.Variant;
}
return retVal;
}
/// <summary>Parses a result page for a UNION SUM error to determine the data type</summary>
/// <param name="HTMLCode">The HTML text from the resulting error page</param>
/// <param name="Plugin">The Plugin to use to identify the data type</param>
/// <returns>The data type of the field generating the error</returns>
public static System.Data.SqlDbType ParseUnionSumError(string HTMLCode, IErrorPlugin Plugin)
{
SqlDbType retVal = new System.Data.SqlDbType();
retVal = SqlDbType.Variant;
int StartError, StartData, EndError, StartSize;
string ErrorData;
// Check for the first half of the error
StartError = HTMLCode.IndexOf(Plugin.UnionSumErrorPre);
StartSize = Plugin.UnionSumErrorPre.Length;
if (StartError >= 0)
{
// Now check for the second half of the error
EndError = HTMLCode.IndexOf(Plugin.UnionSumErrorPost);
if (EndError > StartError)
{
StartData = StartError + StartSize;
ErrorData = HTMLCode.Substring(StartData, EndError - StartData);
switch (ErrorData)
{
case "datetime":
retVal = SqlDbType.DateTime;
break;
case "varchar":
retVal = SqlDbType.VarChar;
break;
case "nvarchar":
retVal = SqlDbType.NVarChar;
break;
case "bit":
retVal = SqlDbType.Bit;
break;
case "char":
retVal = SqlDbType.Char;
break;
case "smallint":
retVal = SqlDbType.SmallInt;
break;
default:
UserStatus(String.Format("Unknown Data Type of type: {0}", ErrorData));
break;
}
}
}
else if (HTMLCode.IndexOf(Plugin.UnionSumErrorOnIntPre) >= 0)
{
retVal = SqlDbType.Int;
}
else if(HTMLCode.IndexOf(Plugin.UnionSumErrorOnText) >= 0)
{
retVal = SqlDbType.Text;
}
else
{
UserStatus(String.Format("Error: {0}", HTMLCode));
}
return retVal;
}