public void BasicAdoCode()
{
var connection = ConnectionFactory.GetConnection();
using (var cmd = new SACommand("WithOutParams", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
SAParameter param1 = new SAParameter("out1", SADbType.Integer);
param1.Direction = ParameterDirection.InputOutput;
param1.Value = 9;
cmd.Parameters.Add(param1);
SAParameter param2 = new SAParameter("out2", SADbType.Integer);
param1.Direction = ParameterDirection.InputOutput;
param2.Value = 9;
cmd.Parameters.Add(param2);
cmd.ExecuteNonQuery();
Console.WriteLine(param1.Value);
Console.WriteLine(param2.Value);
}
}
// If it's an Insert we fetch the @@Identity value and stuff it in the proper column
protected static void OnRowUpdated(object sender, SARowUpdatedEventArgs e)
{
try
{
PropertyCollection props = e.Row.Table.ExtendedProperties;
if (props.ContainsKey("props"))
{
props = (PropertyCollection)props["props"];
}
if (e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update))
{
esDataRequest request = props["esDataRequest"] as esDataRequest;
esEntitySavePacket packet = (esEntitySavePacket)props["esEntityData"];
if (e.StatementType == StatementType.Insert)
{
if (props.Contains("AutoInc"))
{
string autoInc = props["AutoInc"] as string;
SACommand cmd = new SACommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
cmd.CommandText = "SELECT @@IDENTITY;";
cmd.CommandType = CommandType.Text;
object o = null;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace))
{
try
{
o = cmd.ExecuteScalar();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
o = cmd.ExecuteScalar();
}
if (o != null)
{
packet.CurrentValues[autoInc] = o;
e.Row[autoInc] = o;
}
}
if (props.Contains("EntitySpacesConcurrency"))
{
string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string;
packet.CurrentValues[esConcurrencyColumn] = 1;
}
}
//-------------------------------------------------------------------------------------------------
// Fetch any defaults, SQLite doesn't support output parameters so we gotta do this the hard way
//-------------------------------------------------------------------------------------------------
if (props.Contains("Defaults"))
{
// Build the Where parameter and parameters
SACommand cmd = new SACommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
string select = (string)props["Defaults"];
string[] whereParameters = ((string)props["Where"]).Split(',');
string comma = String.Empty;
string where = String.Empty;
int i = 1;
foreach (string parameter in whereParameters)
{
SAParameter p = new SAParameter(":p" + i++.ToString(), e.Row[parameter]);
cmd.Parameters.Add(p);
where += comma + "[" + parameter + "]=" + p.ParameterName;
comma = " AND ";
}
// Okay, now we can execute the sql and get any values that have defaults that were
// null at the time of the insert and/or our timestamp
cmd.CommandText = "SELECT " + select + " FROM [" + request.ProviderMetadata.Source + "] WHERE " + where + ";";
SADataReader rdr = null;
try
{
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace))
{
try
{
rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
}
if (rdr.Read())
{
select = select.Replace("[", String.Empty).Replace("]", String.Empty);
string[] selectCols = select.Split(',');
for (int k = 0; k < selectCols.Length; k++)
{
packet.CurrentValues[selectCols[k]] = rdr.GetValue(k);
}
}
}
finally
{
// Make sure we close the reader no matter what
if (rdr != null) rdr.Close();
}
}
if (e.StatementType == StatementType.Update)
{
string colName = props["EntitySpacesConcurrency"] as string;
object o = e.Row[colName];
switch (Type.GetTypeCode(o.GetType()))
{
case TypeCode.Int16: packet.CurrentValues[colName] = ((System.Int16)o) + 1; break;
case TypeCode.Int32: packet.CurrentValues[colName] = ((System.Int32)o) + 1; break;
case TypeCode.Int64: packet.CurrentValues[colName] = ((System.Int64)o) + 1; break;
case TypeCode.UInt16: packet.CurrentValues[colName] = ((System.UInt16)o) + 1; break;
case TypeCode.UInt32: packet.CurrentValues[colName] = ((System.UInt32)o) + 1; break;
case TypeCode.UInt64: packet.CurrentValues[colName] = ((System.UInt64)o) + 1; break;
}
}
}
}
catch { }
}
private static SAParameter CloneParameter(SAParameter p)
{
ICloneable param = p as ICloneable;
return param.Clone() as SAParameter;
}
public static Dictionary<string, SAParameter> GetParameters(Guid dataID,
tgProviderSpecificMetadata providerMetadata, tgColumnMetadataCollection columns)
{
lock (parameterCache)
{
if (!parameterCache.ContainsKey(dataID))
{
// The Parameters for this Table haven't been cached yet, this is a one time operation
Dictionary<string, SAParameter> types = new Dictionary<string, SAParameter>();
SAParameter param1;
foreach (tgColumnMetadata col in columns)
{
tgTypeMap typeMap = providerMetadata.GetTypeMap(col.PropertyName);
if (typeMap != null)
{
string nativeType = typeMap.NativeType;
SADbType dbType = Cache.NativeTypeToDbType(nativeType);
param1 = new SAParameter(Delimiters.Param + col.PropertyName, dbType, 0, col.Name);
param1.SourceColumn = col.Name;
switch (dbType)
{
case SADbType.BigInt:
case SADbType.Decimal:
case SADbType.Float:
case SADbType.Integer:
case SADbType.Money:
case SADbType.Real:
case SADbType.SmallMoney:
case SADbType.TinyInt:
case SADbType.SmallInt:
param1.Size = (int)col.CharacterMaxLength;
param1.Precision = (byte)col.NumericPrecision;
param1.Scale = (byte)col.NumericScale;
break;
case SADbType.VarChar:
param1.Size = (int)col.CharacterMaxLength;
break;
//case SADbType.Integer:
// param1.Size = 4;
// break;
case SADbType.Date:
case SADbType.DateTime:
param1.Precision = 23;
param1.Scale = 3;
break;
case SADbType.SmallDateTime:
param1.Precision = 16;
break;
}
types[col.Name] = param1;
}
}
parameterCache[dataID] = types;
}
}
return parameterCache[dataID];
}
protected static string GetComparisonStatement(StandardProviderParameters std, tgDynamicQuerySerializable query, List<tgComparison> items, string prefix)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
//=======================================
// WHERE
//=======================================
if (items != null)
{
sql += prefix;
string compareTo = String.Empty;
foreach (tgComparison comparisonItem in items)
{
tgComparison.tgComparisonData comparisonData = (tgComparison.tgComparisonData)comparisonItem;
tgDynamicQuerySerializable subQuery = null;
bool requiresParam = true;
bool needsStringParameter = false;
std.needsIntegerParameter = false;
if (comparisonData.IsParenthesis)
{
if (comparisonData.Parenthesis == tgParenthesis.Open)
sql += "(";
else
sql += ")";
continue;
}
if (comparisonData.IsConjunction)
{
switch (comparisonData.Conjunction)
{
case tgConjunction.And: sql += " AND "; break;
case tgConjunction.Or: sql += " OR "; break;
case tgConjunction.AndNot: sql += " AND NOT "; break;
case tgConjunction.OrNot: sql += " OR NOT "; break;
}
continue;
}
Dictionary<string, SAParameter> types = null;
if (comparisonData.Column.Query != null)
{
IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
types = Cache.GetParameters(iLocalQuery.DataID, (tgProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (tgColumnMetadataCollection)iLocalQuery.Columns);
}
if (comparisonData.IsLiteral)
{
if (comparisonData.Column.Name[0] == '<')
{
sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2);
}
else
{
sql += comparisonData.Column.Name;
}
continue;
}
if (comparisonData.ComparisonColumn.Name == null)
{
subQuery = comparisonData.Value as tgDynamicQuerySerializable;
if (subQuery == null)
{
if (comparisonData.Column.Name != null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
}
else
{
compareTo = Delimiters.Param + "Expr" + (++std.pindex).ToString();
}
}
else
{
// It's a sub query
compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ") ";
requiresParam = false;
}
}
else
{
compareTo = GetColumnName(comparisonData.ComparisonColumn);
requiresParam = false;
}
switch (comparisonData.Operand)
{
case tgComparisonOperand.Exists:
sql += " EXISTS" + compareTo;
break;
case tgComparisonOperand.NotExists:
sql += " NOT EXISTS" + compareTo;
break;
//-----------------------------------------------------------
// Comparison operators, left side vs right side
//-----------------------------------------------------------
case tgComparisonOperand.Equal:
if(comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo;
else
sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.NotEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo;
else
sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.GreaterThan:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo;
else
sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.LessThan:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo;
else
sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.LessThanOrEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo;
else
sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.GreaterThanOrEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo;
else
sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData);
break;
case tgComparisonOperand.Like:
string esc = comparisonData.LikeEscape.ToString();
if(String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case tgComparisonOperand.NotLike:
esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case tgComparisonOperand.Contains:
sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", '" + comparisonData.Value + "')"; // + compareTo + ")";
//needsStringParameter = true;
requiresParam = false;
break;
case tgComparisonOperand.IsNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL";
requiresParam = false;
break;
case tgComparisonOperand.IsNotNull:
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL";
requiresParam = false;
break;
case tgComparisonOperand.In:
case tgComparisonOperand.NotIn:
{
if (subQuery != null)
{
// They used a subquery for In or Not
sql += ApplyWhereSubOperations(std, query, comparisonData);
sql += (comparisonData.Operand == tgComparisonOperand.In) ? " IN" : " NOT IN";
sql += compareTo;
}
else
{
comma = String.Empty;
if (comparisonData.Operand == tgComparisonOperand.In)
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " IN (";
}
else
{
sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT IN (";
}
foreach (object oin in comparisonData.Values)
{
string str = oin as string;
if (str != null)
{
// STRING
sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose;
comma = ",";
}
else if (null != oin as System.Collections.IEnumerable)
{
// LIST OR COLLECTION OF SOME SORT
System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable;
if (enumer != null)
{
System.Collections.IEnumerator iter = enumer.GetEnumerator();
while (iter.MoveNext())
{
object o = iter.Current;
string soin = o as string;
if (soin != null)
sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose;
else
sql += comma + Convert.ToString(o);
comma = ",";
}
}
}
else
{
// NON STRING OR LIST
sql += comma + Convert.ToString(oin);
comma = ",";
}
}
sql += ")";
requiresParam = false;
}
}
break;
case tgComparisonOperand.Between:
SACommand sqlCommand = std.cmd as SACommand;
sql += ApplyWhereSubOperations(std, query, comparisonData) + " BETWEEN ";
sql += compareTo;
if (comparisonData.ComparisonColumn.Name == null)
{
sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenBegin);
}
if (comparisonData.ComparisonColumn2.Name == null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString();
sql += " AND " + compareTo;
sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd);
}
else
{
sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose;
}
requiresParam = false;
break;
}
if (requiresParam)
{
SAParameter p;
if (comparisonData.Column.Name != null)
{
p = types[comparisonData.Column.Name];
p = Cache.CloneParameter(p);
p.Value = comparisonData.Value;
if (needsStringParameter)
{
p.DbType = DbType.String;
}
else if (std.needsIntegerParameter)
{
p.DbType = DbType.Int32;
}
}
else
{
p = new SAParameter(compareTo, comparisonData.Value);
}
std.cmd.Parameters.Add(p);
}
}
}
return sql;
}