private void SendParamFormat()
{
Comm.Append((byte)TdsPacketSubType.ParamFormat);
int len = 2 + (8 * Parameters.Count);
TdsColumnType metaType;
foreach (TdsMetaParameter p in Parameters)
{
metaType = p.GetMetaType();
if (!IsFixedSizeColumn(metaType))
{
len += 1;
}
if (metaType == TdsColumnType.Numeric || metaType == TdsColumnType.Decimal)
{
len += 2;
}
}
Comm.Append((short)len);
Comm.Append((short)Parameters.Count);
foreach (TdsMetaParameter p in Parameters)
{
string locale = String.Empty;
string parameterName = String.Empty;
int userType = 0;
byte status = 0x00;
if (p.IsNullable)
{
status |= 0x20;
}
if (p.Direction == TdsParameterDirection.Output)
{
status |= 0x01;
}
metaType = p.GetMetaType();
Comm.Append((byte)parameterName.Length);
Comm.Append(parameterName);
Comm.Append(status);
Comm.Append(userType);
Comm.Append((byte)metaType);
if (!IsFixedSizeColumn(metaType))
{
Comm.Append((byte)p.Size); // MAXIMUM SIZE
}
if (metaType == TdsColumnType.Numeric || metaType == TdsColumnType.Decimal)
{
Comm.Append(p.Precision);
Comm.Append(p.Scale);
}
Comm.Append((byte)locale.Length);
Comm.Append(locale);
}
}
private void WriteRpcParameterInfo(TdsMetaParameterCollection parameters)
{
if (parameters != null)
{
foreach (TdsMetaParameter param in parameters)
{
if (param.Direction == TdsParameterDirection.ReturnValue)
{
continue;
}
string pname = param.ParameterName;
if (pname != null && pname.Length > 0 && pname [0] == '@')
{
Comm.Append((byte)pname.Length);
Comm.Append(pname);
}
else
{
Comm.Append((byte)(pname.Length + 1));
Comm.Append("@" + pname);
}
short status = 0; // unused
if (param.Direction != TdsParameterDirection.Input)
{
status |= 0x01; // output
}
Comm.Append((byte)status);
WriteParameterInfo(param);
}
}
}
public override string Prepare(string sql, TdsMetaParameterCollection parameters)
{
Parameters = parameters;
Random rand = new Random();
StringBuilder idBuilder = new StringBuilder();
for (int i = 0; i < 25; i += 1)
{
idBuilder.Append((char)(rand.Next(26) + 65));
}
string id = idBuilder.ToString();
//StringBuilder declare = new StringBuilder ();
sql = String.Format("create proc {0} as\n{1}", id, sql);
short len = (short)((id.Length) + sql.Length + 5);
Comm.StartPacket(TdsPacketType.Normal);
Comm.Append((byte)TdsPacketSubType.Dynamic);
Comm.Append(len);
Comm.Append((byte)0x1); // PREPARE
Comm.Append((byte)0x0); // UNUSED
Comm.Append((byte)id.Length);
Comm.Append(id);
Comm.Append((short)sql.Length);
Comm.Append(sql);
Comm.SendPacket();
MoreResults = true;
SkipToEnd();
return(id);
}
public override void ExecPrepared(string id, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
{
Parameters = parameters;
bool hasParameters = (Parameters != null && Parameters.Count > 0);
Comm.StartPacket(TdsPacketType.Normal);
Comm.Append((byte)TdsPacketSubType.Dynamic);
Comm.Append((short)(id.Length + 5));
Comm.Append((byte)0x02); // TDS_DYN_EXEC
Comm.Append((byte)(hasParameters ? 0x01 : 0x00));
Comm.Append((byte)id.Length);
Comm.Append(id);
Comm.Append((short)0);
if (hasParameters)
{
SendParamFormat();
SendParams();
}
MoreResults = true;
Comm.SendPacket();
CheckForData(timeout);
if (!wantResults)
{
SkipToEnd();
}
}
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
// 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();
}
}
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();
}
}
public override void Unprepare(string statementId)
{
Comm.StartPacket(TdsPacketType.Normal);
Comm.Append((byte)TdsPacketSubType.Dynamic);
Comm.Append((short)(3 + statementId.Length));
Comm.Append((byte)0x04);
Comm.Append((byte)0x00);
Comm.Append((byte)statementId.Length);
Comm.Append(statementId);
//Comm.Append ((short) 0);
MoreResults = true;
Comm.SendPacket();
SkipToEnd();
}
private void WritePreparedParameterInfo(TdsMetaParameterCollection parameters)
{
if (parameters == null)
{
return;
}
string param = BuildPreparedParameters();
Comm.Append((byte)0x00); // no param meta data name
Comm.Append((byte)0x00); // no status flags
// Type_info - parameter info
WriteParameterInfo(new TdsMetaParameter("prep_params",
param.Length > 4000 ? "ntext" : "nvarchar",
param));
}
protected override void ExecRPC(string rpcName, TdsMetaParameterCollection parameters,
int timeout, bool wantResults)
{
// clean up
InitExec();
Comm.StartPacket(TdsPacketType.RPC);
Comm.Append((short)rpcName.Length);
Comm.Append(rpcName);
Comm.Append((short)0); //no meta data
WriteRpcParameterInfo(parameters);
Comm.SendPacket();
CheckForData(timeout);
if (!wantResults)
{
SkipToEnd();
}
}
private void SendParams()
{
Comm.Append((byte)TdsPacketSubType.Parameters);
TdsColumnType metaType;
foreach (TdsMetaParameter p in Parameters)
{
metaType = p.GetMetaType();
bool isNull = (p.Value == DBNull.Value || p.Value == null);
if (!IsFixedSizeColumn(metaType))
{
Comm.Append((byte)p.GetActualSize());
}
if (!isNull)
{
Comm.Append(p.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.IsTextType && (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;
}
public override bool Connect(TdsConnectionParameters connectionParameters)
{
if (IsConnected)
{
throw new InvalidOperationException("The connection is already open.");
}
connectionParms = connectionParameters;
SetLanguage(connectionParameters.Language);
SetCharset("utf-8");
byte[] empty = new byte[0];
short authLen = 0;
byte pad = (byte)0;
byte[] domainMagic = { 6, 0x7d, 0x0f, 0xfd, 0xff, 0x0, 0x0, 0x0,
0x0, 0xe0, 0x83, 0x0, 0x0,
0x68, 0x01, 0x00, 0x00, 0x09, 0x04, 0x00, 0x00 };
byte[] sqlserverMagic = { 6, 0x0, 0x0, 0x0,
0x0, 0x0, 0x0, 0x0,
0x0, 0xe0, 0x03, 0x0,
0x0, 0x0, 0x0, 0x0,0x0, 0x0,
0x0, 0x0, 0x0 };
byte[] magic = null;
if (connectionParameters.DomainLogin)
{
magic = domainMagic;
}
else
{
magic = sqlserverMagic;
}
string username = connectionParameters.User;
string domain = null;
int idx = username.IndexOf("\\");
if (idx != -1)
{
domain = username.Substring(0, idx);
username = username.Substring(idx + 1);
connectionParameters.DefaultDomain = domain;
connectionParameters.User = username;
}
else
{
domain = Environment.UserDomainName;
connectionParameters.DefaultDomain = domain;
}
short partialPacketSize = (short)(86 + (
connectionParameters.Hostname.Length +
connectionParameters.ApplicationName.Length +
DataSource.Length +
connectionParameters.LibraryName.Length +
Language.Length +
connectionParameters.Database.Length +
connectionParameters.AttachDBFileName.Length) * 2);
if (connectionParameters.DomainLogin)
{
authLen = ((short)(32 + (connectionParameters.Hostname.Length +
domain.Length)));
partialPacketSize += authLen;
}
else
{
partialPacketSize += ((short)((username.Length + connectionParameters.Password.Length) * 2));
}
int totalPacketSize = partialPacketSize;
Comm.StartPacket(TdsPacketType.Logon70);
Comm.Append(totalPacketSize);
//Comm.Append (empty, 3, pad);
//byte[] version = {0x00, 0x0, 0x0, 0x71};
//Console.WriteLine ("Version: {0}", ClientVersion[3]);
Comm.Append(ClientVersion); // TDS Version 7
Comm.Append((int)this.PacketSize); // Set the Block Size
Comm.Append(empty, 3, pad);
Comm.Append(magic);
short curPos = 86;
// Hostname
Comm.Append(curPos);
Comm.Append((short)connectionParameters.Hostname.Length);
curPos += (short)(connectionParameters.Hostname.Length * 2);
if (connectionParameters.DomainLogin)
{
Comm.Append((short)0);
Comm.Append((short)0);
Comm.Append((short)0);
Comm.Append((short)0);
}
else
{
// Username
Comm.Append(curPos);
Comm.Append((short)username.Length);
curPos += ((short)(username.Length * 2));
// Password
Comm.Append(curPos);
Comm.Append((short)connectionParameters.Password.Length);
curPos += (short)(connectionParameters.Password.Length * 2);
}
// AppName
Comm.Append(curPos);
Comm.Append((short)connectionParameters.ApplicationName.Length);
curPos += (short)(connectionParameters.ApplicationName.Length * 2);
// Server Name
Comm.Append(curPos);
Comm.Append((short)DataSource.Length);
curPos += (short)(DataSource.Length * 2);
// Unknown
Comm.Append((short)curPos);
Comm.Append((short)0);
// Library Name
Comm.Append(curPos);
Comm.Append((short)connectionParameters.LibraryName.Length);
curPos += (short)(connectionParameters.LibraryName.Length * 2);
// Language
Comm.Append(curPos);
Comm.Append((short)Language.Length);
curPos += (short)(Language.Length * 2);
// Database
Comm.Append(curPos);
Comm.Append((short)connectionParameters.Database.Length);
curPos += (short)(connectionParameters.Database.Length * 2);
// MAC Address
Comm.Append((byte)0);
Comm.Append((byte)0);
Comm.Append((byte)0);
Comm.Append((byte)0);
Comm.Append((byte)0);
Comm.Append((byte)0);
// Authentication Stuff
Comm.Append((short)curPos);
if (connectionParameters.DomainLogin)
{
Comm.Append((short)authLen);
curPos += (short)authLen;
}
else
{
Comm.Append((short)0);
}
// Unknown
Comm.Append(curPos);
Comm.Append((short)(connectionParameters.AttachDBFileName.Length));
curPos += (short)(connectionParameters.AttachDBFileName.Length * 2);
// Connection Parameters
Comm.Append(connectionParameters.Hostname);
if (!connectionParameters.DomainLogin)
{
// SQL Server Authentication
Comm.Append(connectionParameters.User);
string scrambledPwd = EncryptPassword(connectionParameters.Password);
Comm.Append(scrambledPwd);
}
Comm.Append(connectionParameters.ApplicationName);
Comm.Append(DataSource);
Comm.Append(connectionParameters.LibraryName);
Comm.Append(Language);
Comm.Append(connectionParameters.Database);
if (connectionParameters.DomainLogin)
{
// the rest of the packet is NTLMSSP authentication
Type1Message msg = new Type1Message();
msg.Domain = domain;
msg.Host = connectionParameters.Hostname;
msg.Flags = NtlmFlags.NegotiateUnicode |
NtlmFlags.NegotiateNtlm |
NtlmFlags.NegotiateDomainSupplied |
NtlmFlags.NegotiateWorkstationSupplied |
NtlmFlags.NegotiateAlwaysSign; // 0xb201
Comm.Append(msg.GetBytes());
}
Comm.Append(connectionParameters.AttachDBFileName);
Comm.SendPacket();
MoreResults = true;
SkipToEnd();
return(IsConnected);
}
public override bool Connect(TdsConnectionParameters connectionParameters)
{
if (IsConnected)
{
throw new InvalidOperationException("The connection is already open.");
}
SetCharset(connectionParameters.Charset);
SetLanguage(connectionParameters.Language);
byte pad = (byte)0;
byte[] empty = new byte[0];
Comm.StartPacket(TdsPacketType.Logon);
// hostname (offset 0)
byte[] tmp = Comm.Append(connectionParameters.Hostname, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// username (offset 31 0x1f)
tmp = Comm.Append(connectionParameters.User, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// password (offset 62 0x3e)
tmp = Comm.Append(GetPlainPassword(connectionParameters.Password), 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// hostproc (offset 93 0x5d)
Comm.Append("00000116", 8, pad);
// unused (offset 109 0x6d)
Comm.Append(empty, (30 - 14), pad);
// apptype
Comm.Append((byte)0x0);
Comm.Append((byte)0xa0);
Comm.Append((byte)0x24);
Comm.Append((byte)0xcc);
Comm.Append((byte)0x50);
Comm.Append((byte)0x12);
// hostproc length
Comm.Append((byte)8);
// Byte order of 2 byte ints
// 2 = <MSB, LSB>, 3 = <LSB, MSB>
Comm.Append((byte)3);
// Byte order of 4 byte ints
// 0 = <MSB, LSB>, 1 = <LSB, MSB>
Comm.Append((byte)1);
// Character representation
// (6 = ASCII, 7 = EBCDIC)
Comm.Append((byte)6);
// Eight byte floating point representation
// 4 = IEEE <MSB, ..., LSB>
// 5 = VAX 'D'
// 10 = IEEE <LSB, ..., MSB>
// 11 = ND5000
Comm.Append((byte)10);
// Eight byte date format
// 8 = <MSB, ..., LSB>
Comm.Append((byte)9);
// notify of use db
Comm.Append((byte)1);
// disallow dump/load and bulk insert
Comm.Append((byte)1);
// sql interface type
Comm.Append((byte)0);
// type of network connection
Comm.Append((byte)0);
// spare [7]
Comm.Append(empty, 7, pad);
// appname
tmp = Comm.Append(connectionParameters.ApplicationName, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// server name
tmp = Comm.Append(DataSource, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// remote passwords
Comm.Append(empty, 2, pad);
tmp = Comm.Append(GetPlainPassword(connectionParameters.Password), 253, pad);
Comm.Append((byte)(tmp.Length < 253 ? tmp.Length + 2 : 253 + 2));
// tds version
Comm.Append((byte)(((byte)Version) / 10));
Comm.Append((byte)(((byte)Version) % 10));
Comm.Append((byte)0);
Comm.Append((byte)0);
// prog name
tmp = Comm.Append(connectionParameters.ProgName, 10, pad);
Comm.Append((byte)(tmp.Length < 10 ? tmp.Length : 10));
// prog version
Comm.Append((byte)6);
// Tell the server we can handle SQLServer version 6
Comm.Append((byte)0);
// Send zero to tell the server we can't handle any other version
Comm.Append((byte)0);
Comm.Append((byte)0);
// auto convert short
Comm.Append((byte)0);
// type of flt4
Comm.Append((byte)0x0d);
// type of date4
Comm.Append((byte)0x11);
// language
tmp = Comm.Append(Language, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// notify on lang change
Comm.Append((byte)1);
// security label hierarchy
Comm.Append((short)0);
// security components
Comm.Append(empty, 8, pad);
// security spare
Comm.Append((short)0);
// security login role
Comm.Append((byte)0);
// charset
tmp = Comm.Append(Charset, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// notify on charset change
Comm.Append((byte)1);
// length of tds packets
tmp = Comm.Append(PacketSize.ToString(), 6, pad);
Comm.Append((byte)3);
// pad out to a longword
Comm.Append(empty, 8, pad);
Comm.SendPacket();
MoreResults = true;
SkipToEnd();
return(IsConnected);
}
public override bool Connect(TdsConnectionParameters connectionParameters)
{
if (IsConnected)
{
throw new InvalidOperationException("The connection is already open.");
}
byte[] capabilityRequest = { 0x03, 0xef, 0x65, 0x41, 0xff, 0xff, 0xff, 0xd6 };
byte[] capabilityResponse = { 0x00, 0x00, 0x00, 0x06, 0x48, 0x00, 0x00, 0x08 };
SetCharset(connectionParameters.Charset);
SetLanguage(connectionParameters.Language);
byte pad = (byte)0;
byte[] empty = new byte[0];
Comm.StartPacket(TdsPacketType.Logon);
// hostname (offset 0)
// 0-30
byte[] tmp = Comm.Append(connectionParameters.Hostname, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// username (offset 31 0x1f)
// 31-61
tmp = Comm.Append(connectionParameters.User, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// password (offset 62 0x3e)
// 62-92
tmp = Comm.Append(GetPlainPassword(connectionParameters.Password), 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// hostproc (offset 93 0x5d)
// 93-123
tmp = Comm.Append("37876", 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// Byte order of 2 byte ints
// 2 = <MSB, LSB>, 3 = <LSB, MSB>
// 124
Comm.Append((byte)3);
// Byte order of 4 byte ints
// 0 = <MSB, LSB>, 1 = <LSB, MSB>
// 125
Comm.Append((byte)1);
// Character representation
// (6 = ASCII, 7 = EBCDIC)
// 126
Comm.Append((byte)6);
// Eight byte floating point representation
// 4 = IEEE <MSB, ..., LSB>
// 5 = VAX 'D'
// 10 = IEEE <LSB, ..., MSB>
// 11 = ND5000
// 127
Comm.Append((byte)10);
// Eight byte date format
// 8 = <MSB, ..., LSB>
// 128
Comm.Append((byte)9);
// notify of use db
// 129
Comm.Append((byte)1);
// disallow dump/load and bulk insert
// 130
Comm.Append((byte)1);
// sql interface type
// 131
Comm.Append((byte)0);
// type of network connection
// 132
Comm.Append((byte)0);
// spare [7]
// 133-139
Comm.Append(empty, 7, pad);
// appname
// 140-170
tmp = Comm.Append(connectionParameters.ApplicationName, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// server name
// 171-201
tmp = Comm.Append(DataSource, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// remote passwords
// 202-457
Comm.Append(empty, 2, pad);
tmp = Comm.Append(GetPlainPassword(connectionParameters.Password), 253, pad);
Comm.Append((byte)(tmp.Length < 253 ? tmp.Length + 2 : 253 + 2));
// tds version
// 458-461
Comm.Append((byte)5);
Comm.Append((byte)0);
Comm.Append((byte)0);
Comm.Append((byte)0);
// prog name
// 462-472
tmp = Comm.Append(connectionParameters.ProgName, 10, pad);
Comm.Append((byte)(tmp.Length < 10 ? tmp.Length : 10));
// prog version
// 473-476
Comm.Append((byte)6);
Comm.Append((byte)0);
Comm.Append((byte)0);
Comm.Append((byte)0);
// auto convert short
// 477
Comm.Append((byte)0);
// type of flt4
// 478
Comm.Append((byte)0x0d);
// type of date4
// 479
Comm.Append((byte)0x11);
// language
// 480-510
tmp = Comm.Append(Language, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// notify on lang change
// 511
Comm.Append((byte)1);
// security label hierarchy
// 512-513
Comm.Append((short)0);
// security components
// 514-521
Comm.Append(empty, 8, pad);
// security spare
// 522-523
Comm.Append((short)0);
// security login role
// 524
Comm.Append((byte)0);
// charset
// 525-555
tmp = Comm.Append(Charset, 30, pad);
Comm.Append((byte)(tmp.Length < 30 ? tmp.Length : 30));
// notify on charset change
// 556
Comm.Append((byte)1);
// length of tds packets
// 557-563
tmp = Comm.Append(this.packetSize.ToString(), 6, pad);
Comm.Append((byte)(tmp.Length < 6 ? tmp.Length : 6));
Comm.Append(empty, 8, pad);
// Padding...
// 564-567
//Comm.Append (empty, 4, pad);
// Capabilities
Comm.Append((byte)TdsPacketSubType.Capability);
Comm.Append((short)20);
Comm.Append((byte)0x01); // TDS_CAP_REQUEST
Comm.Append(capabilityRequest);
Comm.Append((byte)0x02);
Comm.Append(capabilityResponse);
Comm.SendPacket();
MoreResults = true;
SkipToEnd();
return(IsConnected);
}
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;
}