2 // Mono.Data.Tds.Protocol.Tds70.cs
5 // Tim Coleman (tim@timcoleman.com)
6 // Diego Caravana (diego@toth.it)
7 // Sebastien Pouliot (sebastien@ximian.com)
8 // Daniel Morgan (danielmorgan@verizon.net)
10 // Copyright (C) 2002 Tim Coleman
11 // Portions (C) 2003 Motus Technologies Inc. (http://www.motus.com)
12 // Portions (C) 2003 Daniel Morgan
16 // Permission is hereby granted, free of charge, to any person obtaining
17 // a copy of this software and associated documentation files (the
18 // "Software"), to deal in the Software without restriction, including
19 // without limitation the rights to use, copy, modify, merge, publish,
20 // distribute, sublicense, and/or sell copies of the Software, and to
21 // permit persons to whom the Software is furnished to do so, subject to
22 // the following conditions:
24 // The above copyright notice and this permission notice shall be
25 // included in all copies or substantial portions of the Software.
27 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
28 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
29 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
30 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
31 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
32 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
33 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
36 using Mono.Security.Protocol.Ntlm;
40 namespace Mono.Data.Tds.Protocol {
41 public class Tds70 : Tds
45 public readonly static TdsVersion Version = TdsVersion.tds70;
51 public Tds70 (string server, int port)
52 : this (server, port, 512, 15)
56 public Tds70 (string server, int port, int packetSize, int timeout)
57 : base (server, port, packetSize, timeout, Version)
61 #endregion // Constructors
65 private string BuildExec (string sql)
67 string esql = sql.Replace ("'", "''"); // escape single quote
68 if (Parameters != null && Parameters.Count > 0)
69 return BuildProcedureCall (String.Format ("sp_executesql N'{0}', N'{1}', ", esql, BuildPreparedParameters ()));
71 return BuildProcedureCall (String.Format ("sp_executesql N'{0}'", esql));
74 private string BuildParameters ()
76 if (Parameters == null || Parameters.Count == 0)
79 StringBuilder result = new StringBuilder ();
80 foreach (TdsMetaParameter p in Parameters) {
81 string includeAt = "@";
82 if (p.ParameterName [0] == '@')
84 if (p.Direction != TdsParameterDirection.ReturnValue) {
85 if (result.Length > 0)
87 if (p.Direction == TdsParameterDirection.InputOutput)
88 result.Append (String.Format("{0}{1}={1} output", includeAt, p.ParameterName));
90 result.Append (FormatParameter (p));
93 return result.ToString ();
96 private string BuildPreparedParameters ()
98 StringBuilder parms = new StringBuilder ();
99 foreach (TdsMetaParameter p in Parameters) {
100 if (parms.Length > 0)
102 parms.Append (p.Prepare ());
103 if (p.Direction == TdsParameterDirection.Output)
104 parms.Append (" output");
106 return parms.ToString ();
109 private string BuildPreparedQuery (string id)
111 return BuildProcedureCall (String.Format ("sp_execute {0},", id));
114 private string BuildProcedureCall (string procedure)
116 string exec = String.Empty;
118 StringBuilder declare = new StringBuilder ();
119 StringBuilder select = new StringBuilder ();
120 StringBuilder set = new StringBuilder ();
123 if (Parameters != null) {
124 foreach (TdsMetaParameter p in Parameters) {
125 if (p.Direction != TdsParameterDirection.Input) {
128 select.Append ("select ");
130 select.Append (", ");
131 select.Append (p.ParameterName);
133 declare.Append (String.Format ("declare {0}\n", p.Prepare ()));
135 if (p.Direction != TdsParameterDirection.ReturnValue) {
136 if( p.Direction == TdsParameterDirection.InputOutput )
137 set.Append (String.Format ("set {0}\n", FormatParameter(p)));
139 set.Append (String.Format ("set {0}=NULL\n", p.ParameterName));
145 if (p.Direction == TdsParameterDirection.ReturnValue) {
146 exec = p.ParameterName + "=";
150 exec = "exec " + exec;
152 string sql = String.Format ("{0}{1}{2}{3} {4}\n{5}", declare.ToString (), set.ToString (), exec, procedure, BuildParameters (), select.ToString ());
156 public override bool Connect (TdsConnectionParameters connectionParameters)
159 throw new InvalidOperationException ("The connection is already open.");
161 connectionParms = connectionParameters;
163 SetLanguage (connectionParameters.Language);
164 SetCharset ("utf-8");
166 byte[] empty = new byte[0];
170 byte[] domainMagic = { 6, 0x7d, 0x0f, 0xfd, 0xff, 0x0, 0x0, 0x0,
171 0x0, 0xe0, 0x83, 0x0, 0x0,
172 0x68, 0x01, 0x00, 0x00, 0x09, 0x04, 0x00, 0x00 };
173 byte[] sqlserverMagic = { 6, 0x0, 0x0, 0x0,
175 0x0, 0xe0, 0x03, 0x0,
176 0x0, 0x0, 0x0, 0x0, 0x0, 0x0,
180 if (connectionParameters.DomainLogin == true)
183 magic = sqlserverMagic;
185 string username = connectionParameters.User;
187 string domain = Environment.UserDomainName;
188 domain = connectionParameters.DefaultDomain = Environment.UserDomainName;
191 if ((idx = username.IndexOf ("\\")) > -1) {
192 domain = username.Substring (0, idx);
193 username = username.Substring (idx + 1);
195 connectionParameters.DefaultDomain = domain;
196 connectionParameters.User = username;
199 short partialPacketSize = (short) (86 + (
200 connectionParameters.Hostname.Length +
201 connectionParameters.ApplicationName.Length +
203 connectionParameters.LibraryName.Length +
205 connectionParameters.Database.Length +
206 connectionParameters.AttachDBFileName.Length) * 2);
208 if(connectionParameters.DomainLogin == true) {
209 authLen = ((short) (32 + (connectionParameters.Hostname.Length +
211 partialPacketSize += authLen;
214 partialPacketSize += ((short) ((username.Length + connectionParameters.Password.Length) * 2));
216 int totalPacketSize = partialPacketSize;
218 Comm.StartPacket (TdsPacketType.Logon70);
220 Comm.Append (totalPacketSize);
222 //Comm.Append (empty, 3, pad);
223 byte[] version = {0x00, 0x0, 0x0, 0x70};
224 Comm.Append (version); // TDS Version 7
225 Comm.Append ((int)this.PacketSize); // Set the Block Size
226 Comm.Append (empty, 3, pad);
232 Comm.Append (curPos);
233 Comm.Append ((short) connectionParameters.Hostname.Length);
234 curPos += (short) (connectionParameters.Hostname.Length * 2);
236 if(connectionParameters.DomainLogin.Equals(true))
238 Comm.Append((short)0);
239 Comm.Append((short)0);
240 Comm.Append((short)0);
241 Comm.Append((short)0);
246 Comm.Append (curPos);
247 Comm.Append ((short) username.Length);
248 curPos += ((short) (username.Length * 2));
251 Comm.Append (curPos);
252 Comm.Append ((short) connectionParameters.Password.Length);
253 curPos += (short) (connectionParameters.Password.Length * 2);
257 Comm.Append (curPos);
258 Comm.Append ((short) connectionParameters.ApplicationName.Length);
259 curPos += (short) (connectionParameters.ApplicationName.Length * 2);
262 Comm.Append (curPos);
263 Comm.Append ((short) DataSource.Length);
264 curPos += (short) (DataSource.Length * 2);
267 Comm.Append ((short) curPos);
268 Comm.Append ((short) 0);
271 Comm.Append (curPos);
272 Comm.Append ((short) connectionParameters.LibraryName.Length);
273 curPos += (short) (connectionParameters.LibraryName.Length * 2);
276 Comm.Append (curPos);
277 Comm.Append ((short) Language.Length);
278 curPos += (short) (Language.Length * 2);
281 Comm.Append (curPos);
282 Comm.Append ((short) connectionParameters.Database.Length);
283 curPos += (short) (connectionParameters.Database.Length * 2);
286 Comm.Append((byte) 0);
287 Comm.Append((byte) 0);
288 Comm.Append((byte) 0);
289 Comm.Append((byte) 0);
290 Comm.Append((byte) 0);
291 Comm.Append((byte) 0);
293 // Authentication Stuff
294 Comm.Append ((short) curPos);
295 if (connectionParameters.DomainLogin == true)
297 Comm.Append ((short) authLen);
298 curPos += (short) authLen;
301 Comm.Append ((short) 0);
304 Comm.Append (curPos);
305 Comm.Append ((short)( connectionParameters.AttachDBFileName.Length));
306 curPos += (short)(connectionParameters.AttachDBFileName.Length*2);
308 // Connection Parameters
309 Comm.Append (connectionParameters.Hostname);
310 if (connectionParameters.DomainLogin == false)
312 // SQL Server Authentication
313 Comm.Append (connectionParameters.User);
314 string scrambledPwd = EncryptPassword (connectionParameters.Password);
315 Comm.Append (scrambledPwd);
317 Comm.Append (connectionParameters.ApplicationName);
318 Comm.Append (DataSource);
319 Comm.Append (connectionParameters.LibraryName);
320 Comm.Append (Language);
321 Comm.Append (connectionParameters.Database);
323 if (connectionParameters.DomainLogin)
325 // the rest of the packet is NTLMSSP authentication
326 Type1Message msg = new Type1Message ();
328 msg.Host = connectionParameters.Hostname;
329 msg.Flags = NtlmFlags.NegotiateUnicode |
330 NtlmFlags.NegotiateNtlm |
331 NtlmFlags.NegotiateDomainSupplied |
332 NtlmFlags.NegotiateWorkstationSupplied |
333 NtlmFlags.NegotiateAlwaysSign; // 0xb201
334 Comm.Append (msg.GetBytes ());
337 Comm.Append (connectionParameters.AttachDBFileName);
345 private static string EncryptPassword (string pass)
347 int xormask = 0x5a5a;
348 int len = pass.Length;
349 char[] chars = new char[len];
351 for (int i = 0; i < len; ++i) {
352 int c = ((int) (pass[i])) ^ xormask;
353 int m1 = (c >> 4) & 0x0f0f;
354 int m2 = (c << 4) & 0xf0f0;
355 chars[i] = (char) (m1 | m2);
358 return new String (chars);
361 public override bool Reset ()
364 ExecProc ("sp_reset_connection");
366 } catch (Exception e) {
367 System.Reflection.PropertyInfo pinfo = e.GetType ().GetProperty ("Class");
368 if (pinfo != null && pinfo.PropertyType == typeof (byte)) {
369 byte klass = (byte) pinfo.GetValue (e, null);
370 // 11 to 16 indicates error that can be fixed by the user such as 'Invalid object name'
371 if (klass < 11 || klass > 16)
379 public override void ExecPrepared (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
381 Parameters = parameters;
382 ExecuteQuery (BuildPreparedQuery (commandText), timeout, wantResults);
385 public override void ExecProc (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
387 Parameters = parameters;
388 ExecRPC (commandText, parameters, timeout, wantResults);
391 protected override void ExecRPC (string rpcName, TdsMetaParameterCollection parameters,
392 int timeout, bool wantResults)
396 Comm.StartPacket (TdsPacketType.RPC);
398 Comm.Append ( (short) rpcName.Length);
399 Comm.Append (rpcName);
400 Comm.Append ( (short) 0); //no meta data
401 if (parameters != null) {
402 foreach (TdsMetaParameter param in parameters) {
403 if (param.Direction == TdsParameterDirection.ReturnValue)
405 Comm.Append ( (byte) param.ParameterName.Length );
406 Comm.Append (param.ParameterName);
407 short status = 0; // unused
408 if (param.Direction != TdsParameterDirection.Input)
409 status |= 0x01; // output
410 Comm.Append ( (byte) status);
411 WriteParameterInfo (param);
415 CheckForData (timeout);
420 private void WriteParameterInfo (TdsMetaParameter param)
423 Ms.net send non-nullable datatypes as nullable and allows setting null values
424 to int/float etc.. So, using Nullable form of type for all data
426 param.IsNullable = true;
427 TdsColumnType colType = param.GetMetaType ();
428 param.IsNullable = false;
430 Comm.Append ((byte)colType); // type
434 size = param.GetActualSize ();
439 If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
440 FIXME: Need to check for other types
442 if (colType == TdsColumnType.BigNVarChar)
444 if (IsLargeType (colType))
445 Comm.Append ((short)size); // Parameter size passed in SqlParameter
446 else if (IsBlobType (colType))
447 Comm.Append (size); // Parameter size passed in SqlParameter
449 Comm.Append ((byte)size);
451 // Precision and Scale are non-zero for only decimal/numeric
452 if ( param.TypeName == "decimal" || param.TypeName == "numeric") {
453 Comm.Append ((param.Precision!=0)?param.Precision:(byte)28);
454 Comm.Append (param.Scale);
457 size = param.GetActualSize ();
458 if (IsLargeType (colType))
459 Comm.Append ((short)size);
460 else if (IsBlobType (colType))
463 Comm.Append ((byte)size);
466 switch (param.TypeName) {
469 Decimal val = (decimal) param.Value;
470 int[] arr = Decimal.GetBits (val);
471 int sign = (val>0 ? 1: -1);
472 Comm.Append (sign * arr[1]);
473 Comm.Append (sign * arr[0]);
478 Decimal val = (decimal) param.Value;
479 int[] arr = Decimal.GetBits (val);
480 int sign = (val>0 ? 1: -1);
481 Comm.Append (sign * arr[0]);
485 Comm.Append ((DateTime)param.Value, 8);
487 case "smalldatetime":
488 Comm.Append ((DateTime)param.Value, 4);
496 byte [] tmp = param.GetBytes ();
499 case "uniqueidentifier" :
500 Comm.Append (((Guid)param.Value).ToByteArray());
503 Comm.Append (param.Value);
510 public override void Execute (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
512 Parameters = parameters;
513 string sql = commandText;
514 if (wantResults || (Parameters != null && Parameters.Count > 0))
515 sql = BuildExec (commandText);
516 ExecuteQuery (sql, timeout, wantResults);
519 private string FormatParameter (TdsMetaParameter parameter)
521 string includeAt = "@";
522 if (parameter.ParameterName [0] == '@')
524 if (parameter.Direction == TdsParameterDirection.Output)
525 return String.Format ("{0}{1}={1} output", includeAt, parameter.ParameterName);
527 if (parameter.Value == null || parameter.Value == DBNull.Value)
528 return parameter.ParameterName + "=NULL";
531 switch (parameter.TypeName) {
532 case "smalldatetime":
534 DateTime d = Convert.ToDateTime (parameter.Value);
535 value = String.Format(System.Globalization.CultureInfo.InvariantCulture,
536 "'{0:MMM dd yyyy hh:mm:ss tt}'", d );
547 object paramValue = parameter.Value;
548 Type paramType = paramValue.GetType ();
549 if (paramType.IsEnum)
550 paramValue = Convert.ChangeType (paramValue,
551 Type.GetTypeCode (paramType));
552 value = paramValue.ToString ();
556 value = String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
558 case "uniqueidentifier":
559 value = String.Format ("'{0}'", ((Guid) parameter.Value).ToString (""));
562 if (parameter.Value.GetType () == typeof (bool))
563 value = (((bool) parameter.Value) ? "0x1" : "0x0");
565 value = parameter.Value.ToString ();
571 byte[] byteArray = (byte[]) parameter.Value;
572 // In 1.0 profile, BitConverter.ToString() throws ArgumentOutOfRangeException when passed a 0-length
573 // array, so handle that as a special case.
574 if (byteArray.Length == 0)
577 value = String.Format ("0x{0}", BitConverter.ToString (byteArray).Replace ("-", "").ToLower ());
580 value = String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
584 return includeAt + parameter.ParameterName + "=" + value;
587 public override string Prepare (string commandText, TdsMetaParameterCollection parameters)
589 Parameters = parameters;
591 TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
592 TdsMetaParameter parm = new TdsMetaParameter ("@Handle", "int", null);
593 parm.Direction = TdsParameterDirection.Output;
596 parms.Add (new TdsMetaParameter ("@VarDecl", "nvarchar", BuildPreparedParameters ()));
597 parms.Add (new TdsMetaParameter ("@Query", "nvarchar", commandText));
599 ExecProc ("sp_prepare", parms, 0, true);
601 return OutputParameters[0].ToString () ;
602 //if (ColumnValues == null || ColumnValues [0] == null || ColumnValues [0] == DBNull.Value)
603 // throw new TdsInternalException ();
605 //return ColumnValues [0].ToString ();
608 protected override TdsDataColumnCollection ProcessColumnInfo ()
610 TdsDataColumnCollection result = new TdsDataColumnCollection ();
611 int numColumns = Comm.GetTdsShort ();
612 for (int i = 0; i < numColumns; i += 1) {
613 byte[] flagData = new byte[4];
614 for (int j = 0; j < 4; j += 1)
615 flagData[j] = Comm.GetByte ();
617 bool nullable = (flagData[2] & 0x01) > 0;
618 //bool caseSensitive = (flagData[2] & 0x02) > 0;
619 bool writable = (flagData[2] & 0x0c) > 0;
620 bool autoIncrement = (flagData[2] & 0x10) > 0;
621 bool isIdentity = (flagData[2] & 0x10) > 0;
623 TdsColumnType columnType = (TdsColumnType) (Comm.GetByte () & 0xff);
624 if ((byte) columnType == 0xef)
625 columnType = TdsColumnType.NChar;
627 byte xColumnType = 0;
628 if (IsLargeType (columnType)) {
629 xColumnType = (byte) columnType;
630 if (columnType != TdsColumnType.NChar)
635 string tableName = null;
637 if (IsBlobType (columnType)) {
638 columnSize = Comm.GetTdsInt ();
639 tableName = Comm.GetString (Comm.GetTdsShort ());
642 else if (IsFixedSizeColumn (columnType))
643 columnSize = LookupBufferSize (columnType);
644 else if (IsLargeType ((TdsColumnType) xColumnType))
645 columnSize = Comm.GetTdsShort ();
647 columnSize = Comm.GetByte () & 0xff;
652 switch (columnType) {
653 case TdsColumnType.NText:
654 case TdsColumnType.NChar:
655 case TdsColumnType.NVarChar:
659 case TdsColumnType.Decimal:
660 case TdsColumnType.Numeric:
664 precision = Comm.GetByte ();
665 scale = Comm.GetByte ();
669 string columnName = Comm.GetString (Comm.GetByte ());
670 int index = result.Add (new TdsDataColumn ());
671 result[index]["AllowDBNull"] = nullable;
672 result[index]["ColumnName"] = columnName;
673 result[index]["ColumnSize"] = columnSize;
674 result[index]["ColumnType"] = columnType;
675 result[index]["IsAutoIncrement"] = autoIncrement;
676 result[index]["IsIdentity"] = isIdentity;
677 result[index]["IsReadOnly"] = !writable;
678 result[index]["NumericPrecision"] = precision;
679 result[index]["NumericScale"] = scale;
680 result[index]["BaseTableName"] = tableName;
685 public override void Unprepare (string statementId)
687 TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
688 parms.Add (new TdsMetaParameter ("@P1", "int", Int32.Parse (statementId)));
689 ExecProc ("sp_unprepare", parms, 0, false);
692 protected override bool IsValidRowCount (byte status, byte op)
694 if ((status & (byte)0x10) == 0 || op == (byte)0xc1)
699 protected override void ProcessReturnStatus ()
701 int result = Comm.GetTdsInt ();
702 if( Parameters != null ) {
703 foreach (TdsMetaParameter param in Parameters) {
704 if (param.Direction == TdsParameterDirection.ReturnValue){
705 param.Value = result;
712 #endregion // Methods
715 #region Asynchronous Methods
716 public override IAsyncResult BeginExecuteNonQuery (string cmdText,
717 TdsMetaParameterCollection parameters,
718 AsyncCallback callback,
721 Parameters = parameters;
722 string sql = cmdText;
723 if (Parameters != null && Parameters.Count > 0)
724 sql = BuildExec (cmdText);
726 IAsyncResult ar = BeginExecuteQueryInternal (sql, false, callback, state);
730 public override void EndExecuteNonQuery (IAsyncResult ar)
732 EndExecuteQueryInternal (ar);
735 public override IAsyncResult BeginExecuteQuery (string cmdText,
736 TdsMetaParameterCollection parameters,
737 AsyncCallback callback,
740 Parameters = parameters;
741 string sql = cmdText;
742 if (Parameters != null && Parameters.Count > 0)
743 sql = BuildExec (cmdText);
745 IAsyncResult ar = BeginExecuteQueryInternal (sql, true, callback, state);
749 public override void EndExecuteQuery (IAsyncResult ar)
751 EndExecuteQueryInternal (ar);
755 public override IAsyncResult BeginExecuteProcedure (string prolog,
759 TdsMetaParameterCollection parameters,
760 AsyncCallback callback,
765 Parameters = parameters;
766 string pcall = BuildProcedureCall (cmdText);
767 string sql = String.Format ("{0};{1};{2};", prolog, pcall, epilog);
769 IAsyncResult ar = BeginExecuteQueryInternal (sql, !IsNonQuery, callback, state);
773 public override void EndExecuteProcedure (IAsyncResult ar)
775 EndExecuteQueryInternal (ar);
780 #endregion // Asynchronous Methods