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 if (p.Direction != TdsParameterDirection.ReturnValue) {
82 if (result.Length > 0)
84 if (p.Direction == TdsParameterDirection.InputOutput)
85 result.Append (String.Format("{0}={0} output", p.ParameterName));
87 result.Append (FormatParameter (p));
90 return result.ToString ();
93 private string BuildPreparedParameters ()
95 StringBuilder parms = new StringBuilder ();
96 foreach (TdsMetaParameter p in Parameters) {
99 parms.Append (p.Prepare ());
100 if (p.Direction == TdsParameterDirection.Output)
101 parms.Append (" output");
103 return parms.ToString ();
106 private string BuildPreparedQuery (string id)
108 return BuildProcedureCall (String.Format ("sp_execute {0},", id));
111 private string BuildProcedureCall (string procedure)
113 string exec = String.Empty;
115 StringBuilder declare = new StringBuilder ();
116 StringBuilder select = new StringBuilder ();
117 StringBuilder set = new StringBuilder ();
120 if (Parameters != null) {
121 foreach (TdsMetaParameter p in Parameters) {
122 if (p.Direction != TdsParameterDirection.Input) {
125 select.Append ("select ");
127 select.Append (", ");
128 select.Append (p.ParameterName);
130 declare.Append (String.Format ("declare {0}\n", p.Prepare ()));
132 if (p.Direction != TdsParameterDirection.ReturnValue) {
133 if( p.Direction == TdsParameterDirection.InputOutput )
134 set.Append (String.Format ("set {0}\n", FormatParameter(p)));
136 set.Append (String.Format ("set {0}=NULL\n", p.ParameterName));
142 if (p.Direction == TdsParameterDirection.ReturnValue) {
143 exec = p.ParameterName + "=";
147 exec = "exec " + exec;
149 string sql = String.Format ("{0}{1}{2}{3} {4}\n{5}", declare.ToString (), set.ToString (), exec, procedure, BuildParameters (), select.ToString ());
153 public override bool Connect (TdsConnectionParameters connectionParameters)
156 throw new InvalidOperationException ("The connection is already open.");
158 connectionParms = connectionParameters;
160 SetLanguage (connectionParameters.Language);
161 SetCharset ("utf-8");
163 byte[] empty = new byte[0];
167 byte[] domainMagic = { 6, 0x7d, 0x0f, 0xfd, 0xff, 0x0, 0x0, 0x0,
168 0x0, 0xe0, 0x83, 0x0, 0x0,
169 0x68, 0x01, 0x00, 0x00, 0x09, 0x04, 0x00, 0x00 };
170 byte[] sqlserverMagic = { 6, 0x83, 0xf2, 0xf8,
172 0x0, 0xe0, 0x03, 0x0,
173 0x0, 0x88, 0xff, 0xff, 0xff, 0x36,
177 if (connectionParameters.DomainLogin == true)
180 magic = sqlserverMagic;
182 string username = connectionParameters.User;
184 string domain = Environment.UserDomainName;
185 domain = connectionParameters.DefaultDomain = Environment.UserDomainName;
188 if ((idx = username.IndexOf ("\\")) > -1) {
189 domain = username.Substring (0, idx);
190 username = username.Substring (idx + 1);
192 connectionParameters.DefaultDomain = domain;
193 connectionParameters.User = username;
196 short partialPacketSize = (short) (86 + (
197 connectionParameters.Hostname.Length +
198 connectionParameters.ApplicationName.Length +
200 connectionParameters.LibraryName.Length +
202 connectionParameters.Database.Length +
203 connectionParameters.AttachDBFileName.Length) * 2);
205 if(connectionParameters.DomainLogin == true) {
206 authLen = ((short) (32 + (connectionParameters.Hostname.Length +
208 partialPacketSize += authLen;
211 partialPacketSize += ((short) ((username.Length + connectionParameters.Password.Length) * 2));
213 int totalPacketSize = partialPacketSize;
215 Comm.StartPacket (TdsPacketType.Logon70);
217 Comm.Append (totalPacketSize);
219 Comm.Append (empty, 3, pad);
220 Comm.Append ((byte) 0x70); // TDS Version 7
221 Comm.Append ((int)this.PacketSize); // Set the Block Size
222 Comm.Append (empty, 3, pad);
228 Comm.Append (curPos);
229 Comm.Append ((short) connectionParameters.Hostname.Length);
230 curPos += (short) (connectionParameters.Hostname.Length * 2);
232 if(connectionParameters.DomainLogin.Equals(true))
234 Comm.Append((short)0);
235 Comm.Append((short)0);
236 Comm.Append((short)0);
237 Comm.Append((short)0);
242 Comm.Append (curPos);
243 Comm.Append ((short) username.Length);
244 curPos += ((short) (username.Length * 2));
247 Comm.Append (curPos);
248 Comm.Append ((short) connectionParameters.Password.Length);
249 curPos += (short) (connectionParameters.Password.Length * 2);
253 Comm.Append (curPos);
254 Comm.Append ((short) connectionParameters.ApplicationName.Length);
255 curPos += (short) (connectionParameters.ApplicationName.Length * 2);
258 Comm.Append (curPos);
259 Comm.Append ((short) DataSource.Length);
260 curPos += (short) (DataSource.Length * 2);
263 Comm.Append ((short) 0);
264 Comm.Append ((short) 0);
267 Comm.Append (curPos);
268 Comm.Append ((short) connectionParameters.LibraryName.Length);
269 curPos += (short) (connectionParameters.LibraryName.Length * 2);
272 Comm.Append (curPos);
273 Comm.Append ((short) Language.Length);
274 curPos += (short) (Language.Length * 2);
277 Comm.Append (curPos);
278 Comm.Append ((short) connectionParameters.Database.Length);
279 curPos += (short) (connectionParameters.Database.Length * 2);
282 Comm.Append((byte) 0);
283 Comm.Append((byte) 0);
284 Comm.Append((byte) 0);
285 Comm.Append((byte) 0);
286 Comm.Append((byte) 0);
287 Comm.Append((byte) 0);
289 // Authentication Stuff
290 Comm.Append ((short) curPos);
291 if (connectionParameters.DomainLogin == true)
293 Comm.Append ((short) authLen);
294 curPos += (short) authLen;
297 Comm.Append ((short) 0);
300 Comm.Append (curPos);
301 Comm.Append ((short)( connectionParameters.AttachDBFileName.Length));
302 curPos += (short)(connectionParameters.AttachDBFileName.Length*2);
304 // Connection Parameters
305 Comm.Append (connectionParameters.Hostname);
306 if (connectionParameters.DomainLogin == false)
308 // SQL Server Authentication
309 Comm.Append (connectionParameters.User);
310 string scrambledPwd = EncryptPassword (connectionParameters.Password);
311 Comm.Append (scrambledPwd);
313 Comm.Append (connectionParameters.ApplicationName);
314 Comm.Append (DataSource);
315 Comm.Append (connectionParameters.LibraryName);
316 Comm.Append (Language);
317 Comm.Append (connectionParameters.Database);
319 if (connectionParameters.DomainLogin)
321 // the rest of the packet is NTLMSSP authentication
322 Type1Message msg = new Type1Message ();
324 msg.Host = connectionParameters.Hostname;
325 msg.Flags = NtlmFlags.NegotiateUnicode |
326 NtlmFlags.NegotiateNtlm |
327 NtlmFlags.NegotiateDomainSupplied |
328 NtlmFlags.NegotiateWorkstationSupplied |
329 NtlmFlags.NegotiateAlwaysSign; // 0xb201
330 Comm.Append (msg.GetBytes ());
333 Comm.Append (connectionParameters.AttachDBFileName);
341 private static string EncryptPassword (string pass)
343 int xormask = 0x5a5a;
344 int len = pass.Length;
345 char[] chars = new char[len];
347 for (int i = 0; i < len; ++i) {
348 int c = ((int) (pass[i])) ^ xormask;
349 int m1 = (c >> 4) & 0x0f0f;
350 int m2 = (c << 4) & 0xf0f0;
351 chars[i] = (char) (m1 | m2);
354 return new String (chars);
357 public override bool Reset ()
360 ExecProc ("sp_reset_connection");
362 } catch (Exception e) {
363 System.Reflection.PropertyInfo pinfo = e.GetType ().GetProperty ("Class");
364 if (pinfo != null && pinfo.PropertyType == typeof (byte)) {
365 byte klass = (byte) pinfo.GetValue (e, null);
366 // 11 to 16 indicates error that can be fixed by the user such as 'Invalid object name'
367 if (klass < 11 || klass > 16)
375 public override void ExecPrepared (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
377 Parameters = parameters;
378 ExecuteQuery (BuildPreparedQuery (commandText), timeout, wantResults);
381 public override void ExecProc (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
383 if (parameters != null && parameters.Count > 0) {
384 Parameters = parameters;
385 ExecuteQuery (BuildProcedureCall (commandText), timeout, wantResults);
387 ExecRPC (commandText, parameters, timeout, wantResults);
391 protected override void ExecRPC (string rpcName, TdsMetaParameterCollection parameters,
392 int timeout, bool wantResults)
397 Comm.StartPacket (TdsPacketType.RPC);
399 Comm.Append ( (short) rpcName.Length);
400 Comm.Append (rpcName);
401 Comm.Append ( (short) 0); //no meta data
403 // FIXME : support parameters here
406 CheckForData (timeout);
412 public override void Execute (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
414 Parameters = parameters;
415 string sql = commandText;
416 if (wantResults || (Parameters != null && Parameters.Count > 0))
417 sql = BuildExec (commandText);
418 ExecuteQuery (sql, timeout, wantResults);
421 private bool IsBlobType (TdsColumnType columnType)
423 return (columnType == TdsColumnType.Text || columnType == TdsColumnType.Image || columnType == TdsColumnType.NText);
426 private bool IsLargeType (TdsColumnType columnType)
428 return (columnType == TdsColumnType.NChar || (byte) columnType > 128);
431 private string FormatParameter (TdsMetaParameter parameter)
433 if (parameter.Direction == TdsParameterDirection.Output)
434 return String.Format ("{0}={0} output", parameter.ParameterName);
436 if (parameter.Value == null || parameter.Value == DBNull.Value)
437 return parameter.ParameterName + "=NULL";
440 switch (parameter.TypeName) {
441 case "smalldatetime":
443 DateTime d = Convert.ToDateTime (parameter.Value);
444 value = String.Format(System.Globalization.CultureInfo.InvariantCulture,
445 "'{0:MMM dd yyyy hh:mm:ss tt}'", d );
456 object paramValue = parameter.Value;
457 Type paramType = paramValue.GetType ();
458 if (paramType.IsEnum)
459 paramValue = Convert.ChangeType (paramValue,
460 Type.GetTypeCode (paramType));
461 value = paramValue.ToString ();
465 value = String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
467 case "uniqueidentifier":
468 value = String.Format ("'{0}'", ((Guid) parameter.Value).ToString (""));
471 if (parameter.Value.GetType () == typeof (bool))
472 value = (((bool) parameter.Value) ? "0x1" : "0x0");
474 value = parameter.Value.ToString ();
480 byte[] byteArray = (byte[]) parameter.Value;
481 // In 1.0 profile, BitConverter.ToString() throws ArgumentOutOfRangeException when passed a 0-length
482 // array, so handle that as a special case.
483 if (byteArray.Length == 0)
486 value = String.Format ("0x{0}", BitConverter.ToString (byteArray).Replace ("-", "").ToLower ());
489 value = String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
493 return parameter.ParameterName + "=" + value;
496 public override string Prepare (string commandText, TdsMetaParameterCollection parameters)
498 Parameters = parameters;
500 TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
501 TdsMetaParameter parm = new TdsMetaParameter ("@P1", "int", null);
502 parm.Direction = TdsParameterDirection.Output;
505 parms.Add (new TdsMetaParameter ("@P2", "nvarchar", BuildPreparedParameters ()));
506 parms.Add (new TdsMetaParameter ("@P3", "nvarchar", commandText));
508 ExecProc ("sp_prepare", parms, 0, true);
510 if (ColumnValues [0] == null || ColumnValues [0] == DBNull.Value)
511 throw new TdsInternalException ();
512 return ColumnValues [0].ToString ();
515 protected override TdsDataColumnCollection ProcessColumnInfo ()
517 TdsDataColumnCollection result = new TdsDataColumnCollection ();
518 int numColumns = Comm.GetTdsShort ();
520 for (int i = 0; i < numColumns; i += 1) {
521 byte[] flagData = new byte[4];
522 for (int j = 0; j < 4; j += 1)
523 flagData[j] = Comm.GetByte ();
525 bool nullable = (flagData[2] & 0x01) > 0;
526 bool caseSensitive = (flagData[2] & 0x02) > 0;
527 bool writable = (flagData[2] & 0x0c) > 0;
528 bool autoIncrement = (flagData[2] & 0x10) > 0;
529 bool isIdentity = (flagData[2] & 0x10) > 0;
531 TdsColumnType columnType = (TdsColumnType) (Comm.GetByte () & 0xff);
532 if ((byte) columnType == 0xef)
533 columnType = TdsColumnType.NChar;
535 byte xColumnType = 0;
536 if (IsLargeType (columnType)) {
537 xColumnType = (byte) columnType;
538 if (columnType != TdsColumnType.NChar)
543 string tableName = null;
545 if (IsBlobType (columnType)) {
546 columnSize = Comm.GetTdsInt ();
547 tableName = Comm.GetString (Comm.GetTdsShort ());
550 else if (IsFixedSizeColumn (columnType))
551 columnSize = LookupBufferSize (columnType);
552 else if (IsLargeType ((TdsColumnType) xColumnType))
553 columnSize = Comm.GetTdsShort ();
555 columnSize = Comm.GetByte () & 0xff;
560 switch (columnType) {
561 case TdsColumnType.NText:
562 case TdsColumnType.NChar:
563 case TdsColumnType.NVarChar:
566 case TdsColumnType.Decimal:
567 case TdsColumnType.Numeric:
568 precision = Comm.GetByte ();
569 scale = Comm.GetByte ();
573 string columnName = Comm.GetString (Comm.GetByte ());
575 int index = result.Add (new TdsDataColumn ());
576 result[index]["AllowDBNull"] = nullable;
577 result[index]["ColumnName"] = columnName;
578 result[index]["ColumnSize"] = columnSize;
579 result[index]["ColumnType"] = columnType;
580 result[index]["IsAutoIncrement"] = autoIncrement;
581 result[index]["IsIdentity"] = isIdentity;
582 result[index]["IsReadOnly"] = !writable;
583 result[index]["NumericPrecision"] = precision;
584 result[index]["NumericScale"] = scale;
585 result[index]["BaseTableName"] = tableName;
591 public override void Unprepare (string statementId)
593 TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
594 parms.Add (new TdsMetaParameter ("@P1", "int", Int32.Parse (statementId)));
595 ExecProc ("sp_unprepare", parms, 0, false);
598 protected override bool IsValidRowCount (byte status, byte op)
600 if ((status & (byte)0x10) == 0 || op == (byte)0xc1)
605 #endregion // Methods
608 #region Asynchronous Methods
609 public override IAsyncResult BeginExecuteNonQuery (string cmdText,
610 TdsMetaParameterCollection parameters,
611 AsyncCallback callback,
614 Parameters = parameters;
615 string sql = cmdText;
616 if (Parameters != null && Parameters.Count > 0)
617 sql = BuildExec (cmdText);
619 IAsyncResult ar = BeginExecuteQueryInternal (sql, false, callback, state);
623 public override void EndExecuteNonQuery (IAsyncResult ar)
625 EndExecuteQueryInternal (ar);
628 public override IAsyncResult BeginExecuteQuery (string cmdText,
629 TdsMetaParameterCollection parameters,
630 AsyncCallback callback,
633 Parameters = parameters;
634 string sql = cmdText;
635 if (Parameters != null && Parameters.Count > 0)
636 sql = BuildExec (cmdText);
638 IAsyncResult ar = BeginExecuteQueryInternal (sql, true, callback, state);
642 public override void EndExecuteQuery (IAsyncResult ar)
644 EndExecuteQueryInternal (ar);
648 public override IAsyncResult BeginExecuteProcedure (string prolog,
652 TdsMetaParameterCollection parameters,
653 AsyncCallback callback,
658 Parameters = parameters;
659 string pcall = BuildProcedureCall (cmdText);
660 string sql = String.Format ("{0};{1};{2};", prolog, pcall, epilog);
662 IAsyncResult ar = BeginExecuteQueryInternal (sql, !IsNonQuery, callback, state);
666 public override void EndExecuteProcedure (IAsyncResult ar)
668 EndExecuteQueryInternal (ar);
673 #endregion // Asynchronous Methods