public override string Prepare (string commandText, TdsMetaParameterCollection parameters)
{
Parameters = parameters;
TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
TdsMetaParameter parm = new TdsMetaParameter ("@Handle", "int", null);
parm.Direction = TdsParameterDirection.Output;
parms.Add (parm);
parms.Add (new TdsMetaParameter ("@VarDecl", "nvarchar", BuildPreparedParameters ()));
parms.Add (new TdsMetaParameter ("@Query", "nvarchar", commandText));
ExecProc ("sp_prepare", parms, 0, true);
SkipToEnd ();
return OutputParameters[0].ToString () ;
//if (ColumnValues == null || ColumnValues [0] == null || ColumnValues [0] == DBNull.Value)
// throw new TdsInternalException ();
//return string.Empty;
//return ColumnValues [0].ToString ();
}
private void WriteParameterInfo (TdsMetaParameter param)
{
/*
Ms.net send non-nullable datatypes as nullable and allows setting null values
to int/float etc.. So, using Nullable form of type for all data
*/
param.IsNullable = true;
TdsColumnType colType = param.GetMetaType ();
param.IsNullable = false;
bool partLenType = false;
int size = param.Size;
if (size < 1) {
if (size < 0)
partLenType = true;
size = param.GetActualSize ();
}
// Change colType according to the following table
/*
* Original Type Maxlen New Type
*
* NVarChar 4000 UCS2 NText
* BigVarChar 8000 ASCII Text
* BigVarBinary 8000 bytes Image
*
*/
TdsColumnType origColType = colType;
if (colType == TdsColumnType.BigNVarChar) {
// param.GetActualSize() returns len*2
if (size == param.Size)
size <<= 1;
if ((size >> 1) > 4000)
colType = TdsColumnType.NText;
} else if (colType == TdsColumnType.BigVarChar) {
if (size > 8000)
colType = TdsColumnType.Text;
} else if (colType == TdsColumnType.BigVarBinary) {
if (size > 8000)
colType = TdsColumnType.Image;
}
// Calculation of TypeInfo field
/*
* orig size value TypeInfo field
*
* >= 0 <= Maxlen origColType + content len
* > Maxlen NewType as per above table + content len
* -1 origColType + USHORTMAXLEN (0xFFFF) + content len (TDS 9)
*
*/
// Write updated colType, iff partLenType == false
if (TdsVersion > TdsVersion.tds81 && partLenType) {
Comm.Append ((byte)origColType);
Comm.Append ((short)-1);
} else if (ServerTdsVersion > TdsVersion.tds70
&& origColType == TdsColumnType.Decimal) {
Comm.Append ((byte)TdsColumnType.Numeric);
} else {
Comm.Append ((byte)colType);
}
if (IsLargeType (colType))
Comm.Append ((short)size); // Parameter size passed in SqlParameter
else if (IsBlobType (colType))
Comm.Append (size); // Parameter size passed in SqlParameter
else
Comm.Append ((byte)size);
// Precision and Scale are non-zero for only decimal/numeric
if ( param.TypeName == "decimal" || param.TypeName == "numeric") {
Comm.Append ((param.Precision !=0 ) ? param.Precision : (byte) 29);
Comm.Append (param.Scale);
}
/* VARADHAN: TDS 8 Debugging */
/*
if (Collation != null) {
Console.WriteLine ("Collation is not null");
Console.WriteLine ("Column Type: {0}", colType);
Console.WriteLine ("Collation bytes: {0} {1} {2} {3} {4}", Collation[0], Collation[1], Collation[2],
Collation[3], Collation[4]);
} else {
Console.WriteLine ("Collation is null");
}
*/
// Tds > 7.0 uses collation
if (Collation != null &&
(colType == TdsColumnType.BigChar || colType == TdsColumnType.BigNVarChar ||
colType == TdsColumnType.BigVarChar || colType == TdsColumnType.NChar ||
colType == TdsColumnType.NVarChar || colType == TdsColumnType.Text ||
colType == TdsColumnType.NText))
Comm.Append (Collation);
// LAMESPEC: size should be 0xFFFF for any bigvarchar, bignvarchar and bigvarbinary
// types if param value is NULL
if ((colType == TdsColumnType.BigVarChar ||
colType == TdsColumnType.BigNVarChar ||
colType == TdsColumnType.BigVarBinary) &&
(param.Value == null || param.Value == DBNull.Value))
size = -1;
else
size = param.GetActualSize ();
if (IsLargeType (colType))
Comm.Append ((short)size);
else if (IsBlobType (colType))
Comm.Append (size);
else
Comm.Append ((byte)size);
if (size > 0) {
switch (param.TypeName) {
case "money" : {
Decimal val = (decimal) param.Value;
int[] arr = Decimal.GetBits (val);
if (val >= 0) {
Comm.Append (arr[1]);
Comm.Append (arr[0]);
} else {
Comm.Append (~arr[1]);
Comm.Append (~arr[0] + 1);
}
break;
}
case "smallmoney": {
Decimal val = (decimal) param.Value;
if (val < SMALLMONEY_MIN || val > SMALLMONEY_MAX)
throw new OverflowException (string.Format (
CultureInfo.InvariantCulture,
"Value '{0}' is not valid for SmallMoney."
+ " Must be between {1:N4} and {2:N4}.",
#if NET_2_0
val,
#else
val.ToString (CultureInfo.CurrentCulture),
#endif
SMALLMONEY_MIN, SMALLMONEY_MAX));
int[] arr = Decimal.GetBits (val);
int sign = (val>0 ? 1: -1);
Comm.Append (sign * arr[0]);
break;
}
case "datetime":
Comm.Append ((DateTime)param.Value, 8);
break;
case "smalldatetime":
Comm.Append ((DateTime)param.Value, 4);
break;
case "varchar" :
case "nvarchar" :
case "char" :
case "nchar" :
case "text" :
case "ntext" :
byte [] tmp = param.GetBytes ();
Comm.Append (tmp);
break;
case "uniqueidentifier" :
Comm.Append (((Guid)param.Value).ToByteArray());
break;
default :
Comm.Append (param.Value);
break;
}
}
return;
}
private string FormatParameter (TdsMetaParameter parameter)
{
string parameterName = parameter.ParameterName;
if (parameterName [0] == '@') {
parameterName = parameterName.Substring (1);
}
if (parameter.Direction == TdsParameterDirection.Output)
return String.Format ("@{0}=@{0} output", parameterName);
if (parameter.Value == null || parameter.Value == DBNull.Value)
return String.Format ("@{0}=NULL", parameterName);
string value = null;
switch (parameter.TypeName) {
case "smalldatetime":
case "datetime":
DateTime d = Convert.ToDateTime (parameter.Value);
value = String.Format (base.Locale,
"'{0:MMM dd yyyy hh:mm:ss.fff tt}'", d);
break;
case "bigint":
case "decimal":
case "float":
case "int":
case "money":
case "real":
case "smallint":
case "smallmoney":
case "tinyint":
object paramValue = parameter.Value;
Type paramType = paramValue.GetType ();
if (paramType.IsEnum)
paramValue = Convert.ChangeType (paramValue,
Type.GetTypeCode (paramType));
value = paramValue.ToString ();
break;
case "nvarchar":
case "nchar":
value = String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
break;
case "uniqueidentifier":
value = String.Format ("'{0}'", ((Guid) parameter.Value).ToString (string.Empty));
break;
case "bit":
if (parameter.Value.GetType () == typeof (bool))
value = (((bool) parameter.Value) ? "0x1" : "0x0");
else
value = parameter.Value.ToString ();
break;
case "image":
case "binary":
case "varbinary":
byte[] byteArray = (byte[]) parameter.Value;
// In 1.0 profile, BitConverter.ToString() throws ArgumentOutOfRangeException when passed a 0-length
// array, so handle that as a special case.
if (byteArray.Length == 0)
value = "0x";
else
value = String.Format ("0x{0}", BitConverter.ToString (byteArray).Replace ("-", string.Empty).ToLower ());
break;
default:
value = String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
break;
}
return "@" + parameterName + "=" + value;
}
public bool BulkCopyData (object o, bool isNewRow, int size, TdsMetaParameter parameter)
{
// First append a new row byte if needed
if (isNewRow)
tds.Comm.Append ((byte) TdsPacketSubType.Row);
// Push the null value if that is what was supplied
if (o == null || o == DBNull.Value) {
if (parameter.IsAnyVarCharMax) {
// So max varchar and nvarchar needs to contain all F's as a long value. Seems crazy
// but oh well
tds.Comm.Append(System.Convert.ToInt64("0xFFFFFFFFFFFFFFFF", 16));
} else if (parameter.IsTextType) {
tds.Comm.Append((byte)0XFF);
tds.Comm.Append((byte)0XFF);
}
else
tds.Comm.Append ((byte)0);
return true;
}
// Now we must put the size in if it is a VariableType
// The length of the size field varies based on what type it is
parameter.CalculateIsVariableType();
if (parameter.IsVariableSizeType) {
//int size = parameter.GetActualSize();
if (parameter.IsAnyVarCharMax) {
// So max varchar and nvarchar needs to contain the long value as well as size is specified as int
tds.Comm.Append(System.Convert.ToInt64("0xFFFFFFFFFFFFFFFE", 16));
tds.Comm.Append ((int) size);
}
else if (o.GetType() == typeof(string))
tds.Comm.Append ((short) size);
else
tds.Comm.Append ((byte) size);
}
// There are a few special cases for bulk insert that we will handle ourself
// Otherwise we can just pass the value down to the generic Append Object function
if (parameter.IsNonUnicodeText)
tds.Comm.AppendNonUnicode ((string)o);
else if (parameter.IsMoneyType)
tds.Comm.AppendMoney ((decimal)o, size);
else if (parameter.IsDateTimeType)
tds.Comm.Append((DateTime)o, size);
else if (parameter.IsDecimalType)
tds.Comm.AppendDecimal((decimal)o, size, parameter.Scale);
else
tds.Comm.Append (o);
// For some reason max varchar and nvarchar values need to have 4 bytes of 0 appended
if (parameter.IsAnyVarCharMax)
tds.Comm.Append ((int)0);
return true;
}
private void ExecRPC (TdsRpcProcId rpcId, string sql,
TdsMetaParameterCollection parameters,
int timeout, bool wantResults)
{
// clean up
InitExec ();
Comm.StartPacket (TdsPacketType.RPC);
Comm.Append ((ushort) 0xFFFF);
Comm.Append ((ushort) rpcId);
Comm.Append ((short) 0x02); // no meta data
Comm.Append ((byte) 0x00); // no param meta data name
Comm.Append ((byte) 0x00); // no status flags
// Write sql as a parameter value - UCS2
TdsMetaParameter param = new TdsMetaParameter ("sql",
sql.Length > 4000 ? "ntext":"nvarchar",
sql);
WriteParameterInfo (param);
// Write Parameter infos - name and type
WritePreparedParameterInfo (parameters);
// Write parameter/value info
WriteRpcParameterInfo (parameters);
Comm.SendPacket ();
CheckForData (timeout);
if (!wantResults)
SkipToEnd ();
}
public override string Prepare (string commandText, TdsMetaParameterCollection parameters)
{
Parameters = parameters;
TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
// Tested with MS SQL 2008 RC2 Express and MS SQL 2012 Express:
// You may pass either -1 or 0, but not null as initial value of @Handle,
// which is an output parameter.
TdsMetaParameter parm = new TdsMetaParameter ("@Handle", "int", -1);
parm.Direction = TdsParameterDirection.Output;
parms.Add (parm);
parms.Add (new TdsMetaParameter ("@VarDecl", "nvarchar", BuildPreparedParameters ()));
parms.Add (new TdsMetaParameter ("@Query", "nvarchar", commandText));
ExecProc ("sp_prepare", parms, 0, true);
SkipToEnd ();
return OutputParameters[0].ToString () ;
//if (ColumnValues == null || ColumnValues [0] == null || ColumnValues [0] == DBNull.Value)
// throw new TdsInternalException ();
//return string.Empty;
//return ColumnValues [0].ToString ();
}
private void WriteParameterInfo (TdsMetaParameter param)
{
TdsColumnType colType = param.GetMetaType ();
int size = 0;
if (param.Size == 0)
size = param.GetActualSize ();
else
size = param.Size;
/*
* If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
* FIXME: Need to check for other types
*/
if (colType == TdsColumnType.BigNVarChar)
size <<= 1;
// Total hack for varchar(max) and nvarchar(max)
// They are coming back as Text and not the correct values
// based on the size we can determine what the correct type is
// We need the size to come out to 0xFFFF on the wire.
if (param.IsVarNVarCharMax)
colType = TdsColumnType.BigNVarChar;
else if (param.IsVarCharMax)
colType = TdsColumnType.BigVarChar;
tds.Comm.Append ((byte)colType); // type
param.CalculateIsVariableType();
if (param.IsAnyVarCharMax) {
tds.Comm.Append ((byte)0xFF);
tds.Comm.Append ((byte)0xFF);
} else if (tds.IsLargeType (colType))
tds.Comm.Append ((short)size); // Parameter size passed in SqlParameter
else if (tds.IsBlobType (colType))
tds.Comm.Append (size); // Parameter size passed in SqlParameter
else if (param.IsVariableSizeType)
tds.Comm.Append ((byte)size);
// Precision and Scale are non-zero for only decimal/numeric
if ( param.TypeName == "decimal" || param.TypeName == "numeric") {
tds.Comm.Append ((param.Precision!=0)?param.Precision:(byte)29);
tds.Comm.Append (param.Scale);
}
// Documentation is basically 0 on these 5 bytes. But in a nutshell it seems during a bulk insert
// these are required for text types.
if (param.IsTextType) {
tds.Comm.Append ((byte)0x09);
tds.Comm.Append ((byte)0x04);
tds.Comm.Append ((byte)0xd0);
tds.Comm.Append ((byte)0x00);
tds.Comm.Append ((byte)0x34);
}
}
private void WriteParameterInfo (TdsMetaParameter param)
{
/*
Ms.net send non-nullable datatypes as nullable and allows setting null values
to int/float etc.. So, using Nullable form of type for all data
*/
param.IsNullable = true;
TdsColumnType colType = param.GetMetaType ();
param.IsNullable = false;
bool partLenType = false;
int size = param.Size;
if (size < 1) {
if (size < 0)
partLenType = true;
size = param.GetActualSize ();
}
/*
* If the value is null, not setting the size to 0 will cause varchar
* fields to get inserted as an empty string rather than an null.
*/
if (colType != TdsColumnType.IntN && colType != TdsColumnType.DateTimeN) {
if (param.Value == null || param.Value == DBNull.Value)
size = 0;
}
// Change colType according to the following table
/*
* Original Type Maxlen New Type
*
* NVarChar 4000 UCS2 NText
* BigVarChar 8000 ASCII Text
* BigVarBinary 8000 bytes Image
*
*/
TdsColumnType origColType = colType;
if (colType == TdsColumnType.BigNVarChar) {
// param.GetActualSize() returns len*2
if (size == param.Size)
size <<= 1;
if ((size >> 1) > 4000)
colType = TdsColumnType.NText;
} else if (colType == TdsColumnType.BigVarChar) {
if (size > 8000)
colType = TdsColumnType.Text;
} else if (colType == TdsColumnType.BigVarBinary) {
if (size > 8000)
colType = TdsColumnType.Image;
} else if (colType == TdsColumnType.DateTime2 ||
colType == TdsColumnType.DateTimeOffset) {
// HACK: Wire-level DateTime{2,Offset}
// require TDS 7.3, which this driver
// does not implement correctly--so we
// serialize to ASCII instead.
colType = TdsColumnType.Char;
}
// Calculation of TypeInfo field
/*
* orig size value TypeInfo field
*
* >= 0 <= Maxlen origColType + content len
* > Maxlen NewType as per above table + content len
* -1 origColType + USHORTMAXLEN (0xFFFF) + content len (TDS 9)
*
*/
// Write updated colType, iff partLenType == false
if (TdsVersion > TdsVersion.tds81 && partLenType) {
Comm.Append ((byte)origColType);
Comm.Append ((short)-1);
} else if (ServerTdsVersion > TdsVersion.tds70
&& origColType == TdsColumnType.Decimal) {
Comm.Append ((byte)TdsColumnType.Numeric);
} else {
Comm.Append ((byte)colType);
}
if (IsLargeType (colType))
Comm.Append ((short)size); // Parameter size passed in SqlParameter
else if (IsBlobType (colType))
Comm.Append (size); // Parameter size passed in SqlParameter
else
Comm.Append ((byte)size);
// Precision and Scale are non-zero for only decimal/numeric
if ( param.TypeName == "decimal" || param.TypeName == "numeric") {
Comm.Append ((param.Precision !=0 ) ? param.Precision : Precision);
Comm.Append (param.Scale);
// Convert the decimal value according to Scale
if (param.Value != null && param.Value != DBNull.Value &&
((decimal)param.Value) != Decimal.MaxValue &&
((decimal)param.Value) != Decimal.MinValue &&
((decimal)param.Value) != long.MaxValue &&
((decimal)param.Value) != long.MinValue &&
((decimal)param.Value) != ulong.MaxValue &&
((decimal)param.Value) != ulong.MinValue) {
long expo = (long)new Decimal (System.Math.Pow (10, (double)param.Scale));
long pVal = (long)(((decimal)param.Value) * expo);
param.Value = pVal;
}
}
/* VARADHAN: TDS 8 Debugging */
/*
if (Collation != null) {
Console.WriteLine ("Collation is not null");
Console.WriteLine ("Column Type: {0}", colType);
Console.WriteLine ("Collation bytes: {0} {1} {2} {3} {4}", Collation[0], Collation[1], Collation[2],
Collation[3], Collation[4]);
} else {
Console.WriteLine ("Collation is null");
}
*/
// Tds > 7.0 uses collation
if (Collation != null &&
(colType == TdsColumnType.BigChar || colType == TdsColumnType.BigNVarChar ||
colType == TdsColumnType.BigVarChar || colType == TdsColumnType.NChar ||
colType == TdsColumnType.NVarChar || colType == TdsColumnType.Text ||
colType == TdsColumnType.NText))
Comm.Append (Collation);
// LAMESPEC: size should be 0xFFFF for any bigvarchar, bignvarchar and bigvarbinary
// types if param value is NULL
if ((colType == TdsColumnType.BigVarChar ||
colType == TdsColumnType.BigNVarChar ||
colType == TdsColumnType.BigVarBinary ||
colType == TdsColumnType.Image) &&
(param.Value == null || param.Value == DBNull.Value))
size = -1;
else
size = param.GetActualSize ();
if (IsLargeType (colType))
Comm.Append ((short)size);
else if (IsBlobType (colType))
Comm.Append (size);
else
Comm.Append ((byte)size);
if (size > 0) {
switch (param.TypeName) {
case "money" : {
// 4 == SqlMoney::MoneyFormat.NumberDecimalDigits
Decimal val = Decimal.Round ((decimal) param.Value, 4);
int[] arr = Decimal.GetBits (val);
if (val >= 0) {
Comm.Append (arr[1]);
Comm.Append (arr[0]);
} else {
Comm.Append (~arr[1]);
Comm.Append (~arr[0] + 1);
}
break;
}
case "smallmoney": {
// 4 == SqlMoney::MoneyFormat.NumberDecimalDigits
Decimal val = Decimal.Round ((decimal) param.Value, 4);
if (val < SMALLMONEY_MIN || val > SMALLMONEY_MAX)
throw new OverflowException (string.Format (
CultureInfo.InvariantCulture,
"Value '{0}' is not valid for SmallMoney."
+ " Must be between {1:N4} and {2:N4}.",
val,
SMALLMONEY_MIN, SMALLMONEY_MAX));
int[] arr = Decimal.GetBits (val);
int sign = (val>0 ? 1: -1);
Comm.Append (sign * arr[0]);
break;
}
case "datetime":
Comm.Append ((DateTime)param.Value, 8);
break;
case "smalldatetime":
Comm.Append ((DateTime)param.Value, 4);
break;
case "varchar" :
case "nvarchar" :
case "char" :
case "nchar" :
case "text" :
case "ntext" :
case "datetime2":
case "datetimeoffset":
byte [] tmp = param.GetBytes ();
Comm.Append (tmp);
break;
case "uniqueidentifier" :
Comm.Append (((Guid)param.Value).ToByteArray());
break;
default :
Comm.Append (param.Value);
break;
}
}
return;
}
protected void ExecRPC (TdsRpcProcId rpcId, string sql,
TdsMetaParameterCollection parameters,
int timeout, bool wantResults)
{
// clean up
InitExec ();
Comm.StartPacket (TdsPacketType.RPC);
Comm.Append ((ushort) 0xFFFF);
Comm.Append ((ushort) rpcId);
Comm.Append ((short) 0x02); // no meta data
Comm.Append ((byte) 0x00); // no param meta data name
Comm.Append ((byte) 0x00); // no status flags
// Convert BigNVarChar values larger than 4000 chars to nvarchar(max)
// Need to do this here so WritePreparedParameterInfo emit the
// correct data type
foreach (TdsMetaParameter param2 in parameters) {
var colType = param2.GetMetaType ();
if (colType == TdsColumnType.BigNVarChar) {
int size = param2.GetActualSize ();
if ((size >> 1) > 4000)
param2.Size = -1;
}
}
// Write sql as a parameter value - UCS2
TdsMetaParameter param = new TdsMetaParameter ("sql",
sql.Length > 4000 ? "ntext":"nvarchar",
sql);
WriteParameterInfo (param);
// Write Parameter infos - name and type
WritePreparedParameterInfo (parameters);
// Write parameter/value info
WriteRpcParameterInfo (parameters);
Comm.SendPacket ();
CheckForData (timeout);
if (!wantResults)
SkipToEnd ();
}
private string FormatParameter (TdsMetaParameter parameter)
{
if (parameter.Direction == TdsParameterDirection.Output)
return String.Format ("{0}={0} output", parameter.ParameterName);
if (parameter.Value == null || parameter.Value == DBNull.Value)
return parameter.ParameterName + "=NULL";
string value = null;
switch (parameter.TypeName) {
case "smalldatetime":
case "datetime":
DateTime d = (DateTime)parameter.Value;
value = String.Format(System.Globalization.CultureInfo.InvariantCulture,
"'{0:MMM dd yyyy hh:mm:ss tt}'", d );
break;
case "bigint":
case "decimal":
case "float":
case "int":
case "money":
case "real":
case "smallint":
case "smallmoney":
case "tinyint":
object paramValue = parameter.Value;
Type paramType = paramValue.GetType ();
if (paramType.IsEnum)
paramValue = Convert.ChangeType (paramValue,
Type.GetTypeCode (paramType));
value = paramValue.ToString ();
break;
case "nvarchar":
case "nchar":
value = String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
break;
case "uniqueidentifier":
value = String.Format ("0x{0}", ((Guid) parameter.Value).ToString ("N"));
break;
case "bit":
if (parameter.Value.GetType () == typeof (bool))
value = (((bool) parameter.Value) ? "0x1" : "0x0");
else
value = parameter.Value.ToString ();
break;
case "image":
case "binary":
case "varbinary":
value = String.Format ("0x{0}", BitConverter.ToString ((byte[]) parameter.Value).Replace ("-", "").ToLower ());
break;
default:
value = String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
break;
}
return parameter.ParameterName + "=" + value;
}
private string FormatParameter(TdsMetaParameter parameter)
{
string parameterName = parameter.ParameterName;
if (parameterName [0] == '@')
{
parameterName = parameterName.Substring(1);
}
if (parameter.Direction == TdsParameterDirection.Output)
{
return(String.Format("@{0}=@{0} output", parameterName));
}
if (parameter.Value == null || parameter.Value == DBNull.Value)
{
return(String.Format("@{0}=NULL", parameterName));
}
string value = null;
switch (parameter.TypeName)
{
case "smalldatetime":
case "datetime":
DateTime d = Convert.ToDateTime(parameter.Value);
value = String.Format(base.Locale,
"'{0:MMM dd yyyy hh:mm:ss.fff tt}'", d);
break;
case "bigint":
case "decimal":
case "float":
case "int":
case "money":
case "real":
case "smallint":
case "smallmoney":
case "tinyint":
object paramValue = parameter.Value;
Type paramType = paramValue.GetType();
if (paramType.IsEnum)
{
paramValue = Convert.ChangeType(paramValue,
Type.GetTypeCode(paramType));
}
value = paramValue.ToString();
break;
case "nvarchar":
case "nchar":
value = String.Format("N'{0}'", parameter.Value.ToString().Replace("'", "''"));
break;
case "uniqueidentifier":
value = String.Format("'{0}'", ((Guid)parameter.Value).ToString(string.Empty));
break;
case "bit":
if (parameter.Value.GetType() == typeof(bool))
{
value = (((bool)parameter.Value) ? "0x1" : "0x0");
}
else
{
value = parameter.Value.ToString();
}
break;
case "image":
case "binary":
case "varbinary":
byte[] byteArray = (byte[])parameter.Value;
// In 1.0 profile, BitConverter.ToString() throws ArgumentOutOfRangeException when passed a 0-length
// array, so handle that as a special case.
if (byteArray.Length == 0)
{
value = "0x";
}
else
{
value = String.Format("0x{0}", BitConverter.ToString(byteArray).Replace("-", string.Empty).ToLower());
}
break;
default:
value = String.Format("'{0}'", parameter.Value.ToString().Replace("'", "''"));
break;
}
return("@" + parameterName + "=" + value);
}
private void WriteParameterInfo(TdsMetaParameter param)
{
/*
* Ms.net send non-nullable datatypes as nullable and allows setting null values
* to int/float etc.. So, using Nullable form of type for all data
*/
param.IsNullable = true;
TdsColumnType colType = param.GetMetaType();
param.IsNullable = false;
bool partLenType = false;
int size = param.Size;
if (size < 1)
{
if (size < 0)
{
partLenType = true;
}
size = param.GetActualSize();
}
/*
* If the value is null, not setting the size to 0 will cause varchar
* fields to get inserted as an empty string rather than an null.
*/
if (param.Value == null || param.Value == DBNull.Value)
{
size = 0;
}
// Change colType according to the following table
/*
* Original Type Maxlen New Type
*
* NVarChar 4000 UCS2 NText
* BigVarChar 8000 ASCII Text
* BigVarBinary 8000 bytes Image
*
*/
TdsColumnType origColType = colType;
if (colType == TdsColumnType.BigNVarChar)
{
// param.GetActualSize() returns len*2
if (size == param.Size)
{
size <<= 1;
}
if ((size >> 1) > 4000)
{
colType = TdsColumnType.NText;
}
}
else if (colType == TdsColumnType.BigVarChar)
{
if (size > 8000)
{
colType = TdsColumnType.Text;
}
}
else if (colType == TdsColumnType.BigVarBinary)
{
if (size > 8000)
{
colType = TdsColumnType.Image;
}
}
// Calculation of TypeInfo field
/*
* orig size value TypeInfo field
*
* >= 0 <= Maxlen origColType + content len
* > Maxlen NewType as per above table + content len
* -1 origColType + USHORTMAXLEN (0xFFFF) + content len (TDS 9)
*
*/
// Write updated colType, iff partLenType == false
if (TdsVersion > TdsVersion.tds81 && partLenType)
{
Comm.Append((byte)origColType);
Comm.Append((short)-1);
}
else if (ServerTdsVersion > TdsVersion.tds70 &&
origColType == TdsColumnType.Decimal)
{
Comm.Append((byte)TdsColumnType.Numeric);
}
else
{
Comm.Append((byte)colType);
}
if (IsLargeType(colType))
{
Comm.Append((short)size); // Parameter size passed in SqlParameter
}
else if (IsBlobType(colType))
{
Comm.Append(size); // Parameter size passed in SqlParameter
}
else
{
Comm.Append((byte)size);
}
// Precision and Scale are non-zero for only decimal/numeric
if (param.TypeName == "decimal" || param.TypeName == "numeric")
{
Comm.Append((param.Precision != 0) ? param.Precision : Precision);
Comm.Append(param.Scale);
// Convert the decimal value according to Scale
if (param.Value != null && param.Value != DBNull.Value &&
((decimal)param.Value) != Decimal.MaxValue &&
((decimal)param.Value) != Decimal.MinValue &&
((decimal)param.Value) != long.MaxValue &&
((decimal)param.Value) != long.MinValue &&
((decimal)param.Value) != ulong.MaxValue &&
((decimal)param.Value) != ulong.MinValue)
{
long expo = (long)new Decimal(System.Math.Pow(10, (double)param.Scale));
long pVal = (long)(((decimal)param.Value) * expo);
param.Value = pVal;
}
}
/* VARADHAN: TDS 8 Debugging */
/*
* if (Collation != null) {
* Console.WriteLine ("Collation is not null");
* Console.WriteLine ("Column Type: {0}", colType);
* Console.WriteLine ("Collation bytes: {0} {1} {2} {3} {4}", Collation[0], Collation[1], Collation[2],
* Collation[3], Collation[4]);
* } else {
* Console.WriteLine ("Collation is null");
* }
*/
// Tds > 7.0 uses collation
if (Collation != null &&
(colType == TdsColumnType.BigChar || colType == TdsColumnType.BigNVarChar ||
colType == TdsColumnType.BigVarChar || colType == TdsColumnType.NChar ||
colType == TdsColumnType.NVarChar || colType == TdsColumnType.Text ||
colType == TdsColumnType.NText))
{
Comm.Append(Collation);
}
// LAMESPEC: size should be 0xFFFF for any bigvarchar, bignvarchar and bigvarbinary
// types if param value is NULL
if ((colType == TdsColumnType.BigVarChar ||
colType == TdsColumnType.BigNVarChar ||
colType == TdsColumnType.BigVarBinary ||
colType == TdsColumnType.Image) &&
(param.Value == null || param.Value == DBNull.Value))
{
size = -1;
}
else
{
size = param.GetActualSize();
}
if (IsLargeType(colType))
{
Comm.Append((short)size);
}
else if (IsBlobType(colType))
{
Comm.Append(size);
}
else
{
Comm.Append((byte)size);
}
if (size > 0)
{
switch (param.TypeName)
{
case "money": {
// 4 == SqlMoney::MoneyFormat.NumberDecimalDigits
Decimal val = Decimal.Round((decimal)param.Value, 4);
int[] arr = Decimal.GetBits(val);
if (val >= 0)
{
Comm.Append(arr[1]);
Comm.Append(arr[0]);
}
else
{
Comm.Append(~arr[1]);
Comm.Append(~arr[0] + 1);
}
break;
}
case "smallmoney": {
// 4 == SqlMoney::MoneyFormat.NumberDecimalDigits
Decimal val = Decimal.Round((decimal)param.Value, 4);
if (val < SMALLMONEY_MIN || val > SMALLMONEY_MAX)
{
throw new OverflowException(string.Format(
CultureInfo.InvariantCulture,
"Value '{0}' is not valid for SmallMoney."
+ " Must be between {1:N4} and {2:N4}.",
#if NET_2_0
val,
#else
val.ToString(CultureInfo.CurrentCulture),
#endif
SMALLMONEY_MIN, SMALLMONEY_MAX));
}
int[] arr = Decimal.GetBits(val);
int sign = (val > 0 ? 1: -1);
Comm.Append(sign * arr[0]);
break;
}
case "datetime":
Comm.Append((DateTime)param.Value, 8);
break;
case "smalldatetime":
Comm.Append((DateTime)param.Value, 4);
break;
case "varchar":
case "nvarchar":
case "char":
case "nchar":
case "text":
case "ntext":
byte [] tmp = param.GetBytes();
Comm.Append(tmp);
break;
case "uniqueidentifier":
Comm.Append(((Guid)param.Value).ToByteArray());
break;
default:
Comm.Append(param.Value);
break;
}
}
return;
}
public TdsParameter(string parameterName, object value)
{
metaParameter = new TdsMetaParameter(parameterName, value);
this.sourceVersion = DataRowVersion.Current;
InferTdsType(value);
}
private void WriteParameterInfo (TdsMetaParameter param)
{
/*
Ms.net send non-nullable datatypes as nullable and allows setting null values
to int/float etc.. So, using Nullable form of type for all data
*/
param.IsNullable = true;
TdsColumnType colType = param.GetMetaType ();
param.IsNullable = false;
tds.Comm.Append ((byte)colType); // type
int size = 0;
if (param.Size == 0)
size = param.GetActualSize ();
else
size = param.Size;
/*
If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
FIXME: Need to check for other types
*/
if (colType == TdsColumnType.BigNVarChar)
size <<= 1;
if (tds.IsLargeType (colType))
tds.Comm.Append ((short)size); // Parameter size passed in SqlParameter
else if (tds.IsBlobType (colType))
tds.Comm.Append (size); // Parameter size passed in SqlParameter
else
tds.Comm.Append ((byte)size);
// Precision and Scale are non-zero for only decimal/numeric
if ( param.TypeName == "decimal" || param.TypeName == "numeric") {
tds.Comm.Append ((param.Precision!=0)?param.Precision:(byte)29);
tds.Comm.Append (param.Scale);
}
}
