// Copyright (C) Tim Coleman, 2002
//
-using Mono.Data.TdsClient.Internal;
+//
+// Permission is hereby granted, free of charge, to any person obtaining
+// a copy of this software and associated documentation files (the
+// "Software"), to deal in the Software without restriction, including
+// without limitation the rights to use, copy, modify, merge, publish,
+// distribute, sublicense, and/or sell copies of the Software, and to
+// permit persons to whom the Software is furnished to do so, subject to
+// the following conditions:
+//
+// The above copyright notice and this permission notice shall be
+// included in all copies or substantial portions of the Software.
+//
+// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
+// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
+// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
+// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
+// LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
+// OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
+// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
+//
+
+using Mono.Data.Tds;
+using Mono.Data.Tds.Protocol;
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data.Common;
using System.Runtime.InteropServices;
using System.Text;
-using System.Xml;
namespace Mono.Data.SybaseClient {
public sealed class SybaseCommand : Component, IDbCommand, ICloneable
#region Fields
bool disposed = false;
-
int commandTimeout;
bool designTimeVisible;
string commandText;
-
CommandType commandType;
SybaseConnection connection;
SybaseTransaction transaction;
UpdateRowSource updatedRowSource;
-
CommandBehavior behavior = CommandBehavior.Default;
- NameValueCollection preparedStatements = new NameValueCollection ();
SybaseParameterCollection parameters;
+ string preparedStatement = null;
#endregion // Fields
public string CommandText {
get { return commandText; }
- set { commandText = value; }
+ set {
+ if (value != commandText && preparedStatement != null)
+ Unprepare ();
+ commandText = value;
+ }
}
public int CommandTimeout {
#region Methods
- private string BuildCommand ()
- {
- string statementHandle = preparedStatements [commandText];
- if (statementHandle != null) {
- string proc = String.Format ("sp_execute {0}", statementHandle);
- if (parameters.Count > 0)
- proc += ",";
- return BuildProcedureCall (proc, parameters);
- }
-
- if (commandType == CommandType.StoredProcedure)
- return BuildProcedureCall (commandText, parameters);
-
- string sql = String.Empty;
- if ((behavior & CommandBehavior.KeyInfo) > 0)
- sql += "SET FMTONLY OFF; SET NO_BROWSETABLE ON;";
- if ((behavior & CommandBehavior.SchemaOnly) > 0)
- sql += "SET FMTONLY ON;";
-
- switch (commandType) {
- case CommandType.Text :
- sql += commandText;
- break;
- default:
- throw new InvalidOperationException ("The CommandType was invalid.");
- }
- return BuildExec (sql);
- }
-
- [MonoTODO ("This throws a SybaseException.")]
- private string BuildExec (string sql)
- {
- StringBuilder declare = new StringBuilder ();
- StringBuilder assign = new StringBuilder ();
-
- sql = sql.Replace ("'", "''");
- foreach (SybaseParameter parameter in parameters) {
- declare.Append ("declare ");
- declare.Append (parameter.Prepare (parameter.ParameterName));
- if (parameter.Direction == ParameterDirection.Output)
- declare.Append (" output");
- declare.Append ('\n');
- assign.Append (String.Format ("select {0}={1}\n", parameter.ParameterName, FormatParameter (parameter)));
- sql = sql.Replace (parameter.ParameterName, String.Format ("' + {0} + '", parameter.ParameterName));
- }
-
- return String.Format ("{0}{1}execute ({2})", declare.ToString (), assign.ToString (), sql);
- }
-
- private string BuildPrepare ()
- {
- StringBuilder parms = new StringBuilder ();
- foreach (SybaseParameter parameter in parameters) {
- if (parms.Length > 0)
- parms.Append (", ");
- parms.Append (parameter.Prepare (parameter.ParameterName));
- if (parameter.Direction == ParameterDirection.Output)
- parms.Append (" output");
- }
-
- SybaseParameterCollection localParameters = new SybaseParameterCollection (this);
- SybaseParameter parm;
-
- parm = new SybaseParameter ("@P1", SybaseType.Int);
- parm.Direction = ParameterDirection.Output;
- localParameters.Add (parm);
-
- parm = new SybaseParameter ("@P2", SybaseType.NVarChar);
- parm.Value = parms.ToString ();
- parm.Size = ((string) parm.Value).Length;
- localParameters.Add (parm);
-
- parm = new SybaseParameter ("@P3", SybaseType.NVarChar);
- parm.Value = commandText;
- parm.Size = ((string) parm.Value).Length;
- localParameters.Add (parm);
-
- return BuildProcedureCall ("sp_prepare", localParameters);
- }
-
- private static string BuildProcedureCall (string procedure, SybaseParameterCollection parameters)
- {
- StringBuilder parms = new StringBuilder ();
- StringBuilder declarations = new StringBuilder ();
- StringBuilder outParms = new StringBuilder ();
- StringBuilder set = new StringBuilder ();
-
- int index = 1;
- foreach (SybaseParameter parameter in parameters) {
- string parmName = String.Format ("@P{0}", index);
-
- switch (parameter.Direction) {
- case ParameterDirection.Input :
- if (parms.Length > 0)
- parms.Append (", ");
- parms.Append (FormatParameter (parameter));
- break;
- case ParameterDirection.Output :
- if (parms.Length > 0)
- parms.Append (", ");
- parms.Append (parmName);
- parms.Append (" output");
-
- if (outParms.Length > 0) {
- outParms.Append (", ");
- declarations.Append (", ");
- }
- else {
- outParms.Append ("select ");
- declarations.Append ("declare ");
- }
-
- declarations.Append (parameter.Prepare (parmName));
- set.Append (String.Format ("set {0}=NULL\n", parmName));
- outParms.Append (parmName);
- break;
- default :
- throw new NotImplementedException ("Only support input and output parameters.");
- }
- index += 1;
- }
- if (declarations.Length > 0)
- declarations.Append ('\n');
-
- return String.Format ("{0}{1}{2} {3}\n{4}", declarations.ToString (), set.ToString (), procedure, parms.ToString (), outParms.ToString ());
- }
-
public void Cancel ()
{
if (Connection == null || Connection.Tds == null)
SybaseParameterCollection localParameters = new SybaseParameterCollection (this);
localParameters.Add ("@P1", SybaseType.NVarChar, commandText.Length).Value = commandText;
- Connection.Tds.ExecuteQuery (BuildProcedureCall ("sp_procedure_params_rowset", localParameters));
+ string sql = "sp_procedure_params_rowset";
+
+ Connection.Tds.ExecProc (sql, localParameters.MetaParameters, 0, true);
+
SybaseDataReader reader = new SybaseDataReader (this);
parameters.Clear ();
object[] dbValues = new object[reader.FieldCount];
reader.Close ();
}
+ private void Execute (CommandBehavior behavior, bool wantResults)
+ {
+ Tds.RecordsAffected = -1;
+ TdsMetaParameterCollection parms = Parameters.MetaParameters;
+ if (preparedStatement == null) {
+ bool schemaOnly = ((CommandBehavior & CommandBehavior.SchemaOnly) > 0);
+ bool keyInfo = ((CommandBehavior & CommandBehavior.SchemaOnly) > 0);
+
+ StringBuilder sql1 = new StringBuilder ();
+ StringBuilder sql2 = new StringBuilder ();
+
+ if (schemaOnly || keyInfo)
+ sql1.Append ("SET FMTONLY OFF;");
+ if (keyInfo) {
+ sql1.Append ("SET NO_BROWSETABLE ON;");
+ sql2.Append ("SET NO_BROWSETABLE OFF;");
+ }
+ if (schemaOnly) {
+ sql1.Append ("SET FMTONLY ON;");
+ sql2.Append ("SET FMTONLY OFF;");
+ }
+
+ switch (CommandType) {
+ case CommandType.StoredProcedure:
+ if (keyInfo || schemaOnly)
+ Connection.Tds.Execute (sql1.ToString ());
+ Connection.Tds.ExecProc (CommandText, parms, CommandTimeout, wantResults);
+ if (keyInfo || schemaOnly)
+ Connection.Tds.Execute (sql2.ToString ());
+ break;
+ case CommandType.Text:
+ string sql = String.Format ("{0}{1}{2}", sql1.ToString (), CommandText, sql2.ToString ());
+ Connection.Tds.Execute (sql, parms, CommandTimeout, wantResults);
+ break;
+ }
+ }
+ else
+ Connection.Tds.ExecPrepared (preparedStatement, parms, CommandTimeout, wantResults);
+ }
+
public int ExecuteNonQuery ()
{
ValidateCommand ("ExecuteNonQuery");
- string sql = String.Empty;
int result = 0;
- if (Parameters.Count > 0)
- sql = BuildCommand ();
- else
- sql = CommandText;
-
try {
- result = Connection.Tds.ExecuteNonQuery (sql, CommandTimeout);
+ Execute (CommandBehavior.Default, false);
+ result = Tds.RecordsAffected;
}
catch (TdsTimeoutException e) {
throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
public SybaseDataReader ExecuteReader (CommandBehavior behavior)
{
ValidateCommand ("ExecuteReader");
- this.behavior = behavior;
-
try {
- Connection.Tds.ExecuteQuery (BuildCommand (), CommandTimeout);
+ Execute (behavior, true);
}
catch (TdsTimeoutException e) {
throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
}
-
Connection.DataReader = new SybaseDataReader (this);
return Connection.DataReader;
}
{
ValidateCommand ("ExecuteScalar");
try {
- Connection.Tds.ExecuteQuery (BuildCommand (), CommandTimeout);
+ Execute (CommandBehavior.Default, true);
}
catch (TdsTimeoutException e) {
throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
return result;
}
- [MonoTODO ("Include offset from SybaseParameter for binary/string types.")]
- static string FormatParameter (SybaseParameter parameter)
- {
- if (parameter.Value == null)
- return "NULL";
-
- switch (parameter.SybaseType) {
- case SybaseType.BigInt :
- case SybaseType.Decimal :
- case SybaseType.Float :
- case SybaseType.Int :
- case SybaseType.Money :
- case SybaseType.Real :
- case SybaseType.SmallInt :
- case SybaseType.SmallMoney :
- case SybaseType.TinyInt :
- return parameter.Value.ToString ();
- case SybaseType.NVarChar :
- case SybaseType.NChar :
- return String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
- case SybaseType.UniqueIdentifier :
- return String.Format ("0x{0}", ((Guid) parameter.Value).ToString ("N"));
- case SybaseType.Bit:
- if (parameter.Value.GetType () == typeof (bool))
- return (((bool) parameter.Value) ? "0x1" : "0x0");
- return parameter.Value.ToString ();
- case SybaseType.Image:
- case SybaseType.Binary:
- case SybaseType.VarBinary:
- return String.Format ("0x{0}", BitConverter.ToString ((byte[]) parameter.Value).Replace ("-", "").ToLower ());
- default:
- return String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
- }
- }
-
private void GetOutputParameters ()
{
Connection.Tds.SkipToEnd ();
public void Prepare ()
{
ValidateCommand ("Prepare");
- Connection.Tds.ExecuteNonQuery (BuildPrepare ());
-
- if (Connection.Tds.OutputParameters.Count == 0 || Connection.Tds.OutputParameters[0] == null)
- throw new Exception ("Could not prepare the statement.");
-
- preparedStatements [commandText] = ((int) Connection.Tds.OutputParameters [0]).ToString ();
+ if (CommandType == CommandType.Text)
+ preparedStatement = Connection.Tds.Prepare (CommandText, Parameters.MetaParameters);
}
public void ResetCommandTimeout ()
commandTimeout = 30;
}
+ private void Unprepare ()
+ {
+ Connection.Tds.Unprepare (preparedStatement);
+ preparedStatement = null;
+ }
+
private void ValidateCommand (string method)
{
if (Connection == null)