object ExecuteScalar()
{
try {
object result = null;
ValidateCommand("ExecuteScalar", false);
behavior = CommandBehavior.Default;
Execute(true);
try {
if (Connection.Tds.NextResult() && Connection.Tds.NextRow())
{
result = Connection.Tds.ColumnValues[0];
}
if (commandType == CommandType.StoredProcedure)
{
Connection.Tds.SkipToEnd();
GetOutputParameters();
}
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
return(result);
} finally {
CloseDataReader();
}
}
public SqlDataReader EndExecuteReader(IAsyncResult ar)
{
ValidateAsyncResult(ar, "EndExecuteReader");
EndExecuteInternal(ar);
SqlDataReader reader = null;
try {
reader = new SqlDataReader(this);
} catch (TdsTimeoutException e) {
// if behavior is closeconnection, even if it throws exception
// the connection has to be closed.
if ((behavior & CommandBehavior.CloseConnection) != 0)
{
Connection.Close();
}
throw SqlException.FromTdsInternalException((TdsInternalException)e);
} catch (SqlException) {
// if behavior is closeconnection, even if it throws exception
// the connection has to be closed.
if ((behavior & CommandBehavior.CloseConnection) != 0)
{
Connection.Close();
}
throw;
}
((SqlAsyncResult)ar).Ended = true;
return(reader);
}
bool NextResult()
{
ValidateState();
if ((command.CommandBehavior & CommandBehavior.SingleResult) != 0 && resultsRead > 0)
{
return(false);
}
try {
moreResults = command.Tds.NextResult();
} catch (TdsInternalException ex) {
command.Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
if (!moreResults)
{
command.GetOutputParameters();
}
else
{
//new schema
schemaTable = ConstructSchemaTable();
GetSchemaTable();
}
rowsRead = 0;
resultsRead += 1;
return(moreResults);
}
public new SqlDataReader ExecuteReader(CommandBehavior behavior)
{
ValidateCommand("ExecuteReader");
try {
this.behavior = behavior;
if ((behavior & CommandBehavior.SequentialAccess) != 0)
{
Tds.SequentialAccess = true;
}
Execute(behavior, true);
Connection.DataReader = new SqlDataReader(this);
} catch (TdsTimeoutException e) {
// if behavior is closeconnection, even if it throws exception
// the connection has to be closed.
if ((behavior & CommandBehavior.CloseConnection) != 0)
{
Connection.Close();
}
throw SqlException.FromTdsInternalException((TdsInternalException)e);
} catch (SqlException) {
// if behavior is closeconnection, even if it throws exception
// the connection has to be closed.
if ((behavior & CommandBehavior.CloseConnection) != 0)
{
Connection.Close();
}
throw;
}
return(Connection.DataReader);
}
internal void DeriveParameters()
{
if (commandType != CommandType.StoredProcedure)
{
throw new InvalidOperationException(String.Format("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
}
ValidateCommand("DeriveParameters");
SqlParameterCollection localParameters = new SqlParameterCollection(this);
localParameters.Add("@procedure_name", SqlDbType.NVarChar, CommandText.Length).Value = CommandText;
string sql = "sp_procedure_params_rowset";
try {
Connection.Tds.ExecProc(sql, localParameters.MetaParameters, 0, true);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
SqlDataReader reader = new SqlDataReader(this);
parameters.Clear();
object[] dbValues = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(dbValues);
parameters.Add(new SqlParameter(dbValues));
}
reader.Close();
}
internal void DeriveParameters()
{
if (commandType != CommandType.StoredProcedure)
{
throw new InvalidOperationException(String.Format("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
}
ValidateCommand("DeriveParameters", false);
string procName = CommandText;
string schemaName = String.Empty;
int dotPosition = procName.IndexOf('.');
if (dotPosition >= 0)
{
schemaName = procName.Substring(0, dotPosition);
procName = procName.Substring(dotPosition + 1);
}
procName = EscapeProcName(procName, false);
schemaName = EscapeProcName(schemaName, true);
SqlParameterCollection localParameters = new SqlParameterCollection(this);
localParameters.Add("@procedure_name", SqlDbType.NVarChar, procName.Length).Value = procName;
if (schemaName.Length > 0)
{
localParameters.Add("@procedure_schema", SqlDbType.NVarChar, schemaName.Length).Value = schemaName;
}
string sql = "sp_procedure_params_rowset";
try {
Connection.Tds.ExecProc(sql, localParameters.MetaParameters, 0, true);
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
SqlDataReader reader = new SqlDataReader(this);
parameters.Clear();
object[] dbValues = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(dbValues);
parameters.Add(new SqlParameter(dbValues));
}
reader.Close();
if (parameters.Count == 0)
{
throw new InvalidOperationException("Stored procedure '" + procName + "' does not exist.");
}
}
internal bool ReadRecord()
{
try {
bool result = command.Tds.NextRow();
rowsRead += 1;
return(result);
} catch (TdsInternalException ex) {
command.Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
}
public XmlReader ExecuteXmlReader()
{
ValidateCommand("ExecuteXmlReader");
behavior = CommandBehavior.Default;
try {
Execute(CommandBehavior.Default, true);
} catch (TdsTimeoutException e) {
throw SqlException.FromTdsInternalException((TdsInternalException)e);
}
SqlDataReader dataReader = new SqlDataReader(this);
SqlXmlTextReader textReader = new SqlXmlTextReader(dataReader);
XmlReader xmlReader = new XmlTextReader(textReader);
return(xmlReader);
}
long GetBytes(int i, long dataIndex, byte[] buffer, int bufferIndex, int length)
{
if ((command.CommandBehavior & CommandBehavior.SequentialAccess) != 0)
{
try {
long len = ((Tds)command.Tds).GetSequentialColumnValue(i, dataIndex, buffer, bufferIndex, length);
if (len == -1)
{
throw new InvalidCastException("Invalid attempt to GetBytes on column "
+ "'" + command.Tds.Columns[i]["ColumnName"] +
"'." + "The GetBytes function"
+ " can only be used on columns of type Text, NText, or Image");
}
return(len);
} catch (TdsInternalException ex) {
command.Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
}
object value = GetValue(i);
if (!(value is byte []))
{
if (value is DBNull)
{
throw new SqlNullValueException();
}
throw new InvalidCastException("Type is " + value.GetType().ToString());
}
if (buffer == null)
{
return(((byte [])value).Length); // Return length of data
}
// Copy data into buffer
int availLen = (int)(((byte [])value).Length - dataIndex);
if (availLen < length)
{
length = availLen;
}
Array.Copy((byte [])value, (int)dataIndex, buffer, bufferIndex, length);
return(length); // return actual read count
}
int ExecuteNonQuery()
{
ValidateCommand("ExecuteNonQuery", false);
int result = 0;
behavior = CommandBehavior.Default;
try {
Execute(false);
result = Connection.Tds.RecordsAffected;
} catch (TdsTimeoutException e) {
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)e);
}
GetOutputParameters();
return(result);
}
object GetValue(int i)
{
if (i < 0 || i >= command.Tds.Columns.Count)
{
throw new IndexOutOfRangeException();
}
try {
if ((command.CommandBehavior & CommandBehavior.SequentialAccess) != 0)
{
return(((Tds)command.Tds).GetSequentialColumnValue(i));
}
} catch (TdsInternalException ex) {
command.Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
return(command.Tds.ColumnValues [i]);
}
int GetValues(object[] values)
{
int len = values.Length;
int bigDecimalIndex = command.Tds.ColumnValues.BigDecimalIndex;
// If a four-byte decimal is stored, then we can't convert to
// a native type. Throw an OverflowException.
if (bigDecimalIndex >= 0 && bigDecimalIndex < len)
{
throw new OverflowException();
}
try {
command.Tds.ColumnValues.CopyTo(0, values, 0,
len > command.Tds.ColumnValues.Count ? command.Tds.ColumnValues.Count : len);
} catch (TdsInternalException ex) {
command.Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
return(len < FieldCount ? len : FieldCount);
}
internal IAsyncResult BeginExecuteInternal(CommandBehavior behavior,
bool wantResults,
AsyncCallback callback,
object state)
{
IAsyncResult ar = null;
Connection.Tds.RecordsAffected = -1;
TdsMetaParameterCollection parms = Parameters.MetaParameters;
if (preparedStatement == null)
{
bool schemaOnly = ((behavior & CommandBehavior.SchemaOnly) > 0);
bool keyInfo = ((behavior & CommandBehavior.KeyInfo) > 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:
string prolog = "";
string epilog = "";
if (keyInfo || schemaOnly)
{
prolog = sql1.ToString();
}
if (keyInfo || schemaOnly)
{
epilog = sql2.ToString();
}
try {
Connection.Tds.BeginExecuteProcedure(prolog,
epilog,
CommandText,
!wantResults,
parms,
callback,
state);
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
break;
case CommandType.Text:
string sql = String.Format("{0}{1};{2}", sql1.ToString(), CommandText, sql2.ToString());
try {
if (wantResults)
{
ar = Connection.Tds.BeginExecuteQuery(sql, parms, callback, state);
}
else
{
ar = Connection.Tds.BeginExecuteNonQuery(sql, parms, callback, state);
}
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
break;
}
}
else
{
try {
Connection.Tds.ExecPrepared(preparedStatement, parms, CommandTimeout, wantResults);
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
}
return(ar);
}
private void Execute(bool wantResults)
{
int index = 0;
Connection.Tds.RecordsAffected = -1;
TdsMetaParameterCollection parms = Parameters.MetaParameters;
foreach (TdsMetaParameter param in parms)
{
param.Validate(index++);
}
if (preparedStatement == null)
{
bool schemaOnly = ((behavior & CommandBehavior.SchemaOnly) > 0);
bool keyInfo = ((behavior & CommandBehavior.KeyInfo) > 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:
try {
if (keyInfo || schemaOnly)
{
Connection.Tds.Execute(sql1.ToString());
}
Connection.Tds.ExecProc(CommandText, parms, CommandTimeout, wantResults);
if (keyInfo || schemaOnly)
{
Connection.Tds.Execute(sql2.ToString());
}
} catch (TdsTimeoutException ex) {
// If it is a timeout exception there can be many reasons:
// 1) Network is down/server is down/not reachable
// 2) Somebody has an exclusive lock on Table/DB
// In any of these cases, don't close the connection. Let the user do it
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
break;
case CommandType.Text:
string sql;
if (sql2.Length > 0)
{
sql = String.Format("{0}{1};{2}", sql1.ToString(), CommandText, sql2.ToString());
}
else
{
sql = String.Format("{0}{1}", sql1.ToString(), CommandText);
}
try {
Connection.Tds.Execute(sql, parms, CommandTimeout, wantResults);
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
break;
}
}
else
{
try {
Connection.Tds.ExecPrepared(preparedStatement, parms, CommandTimeout, wantResults);
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
}
}
private void Execute(CommandBehavior behavior, bool wantResults)
{
int index = 0;
Connection.Tds.RecordsAffected = -1;
TdsMetaParameterCollection parms = Parameters.MetaParameters;
foreach (TdsMetaParameter param in parms)
{
param.Validate(index++);
}
if (preparedStatement == null)
{
bool schemaOnly = ((behavior & CommandBehavior.SchemaOnly) > 0);
bool keyInfo = ((behavior & CommandBehavior.KeyInfo) > 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:
try {
if (keyInfo || schemaOnly)
{
Connection.Tds.Execute(sql1.ToString());
}
Connection.Tds.ExecProc(CommandText, parms, CommandTimeout, wantResults);
if (keyInfo || schemaOnly)
{
Connection.Tds.Execute(sql2.ToString());
}
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
break;
case CommandType.Text:
string sql;
if (sql2.Length > 0)
{
sql = String.Format("{0}{1};{2}", sql1.ToString(), CommandText, sql2.ToString());
}
else
{
sql = String.Format("{0}{1}", sql1.ToString(), CommandText);
}
try {
Connection.Tds.Execute(sql, parms, CommandTimeout, wantResults);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
break;
}
}
else
{
try {
Connection.Tds.ExecPrepared(preparedStatement, parms, CommandTimeout, wantResults);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
}
}
