static public EfzCommand BuildStoredProcDeleteCommand(esDataRequest request)
{
EfzCommand cmd = new EfzCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spDelete + Delimiters.StoredProcNameClose;
Dictionary <string, EfzParameter> types = Cache.GetParameters(request);
EfzParameter p;
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency)
{
p = types[col.Name];
p = CloneParameter(p);
p.ParameterName = p.ParameterName.Replace("?", "?p");
p.SourceVersion = DataRowVersion.Current;
cmd.Parameters.Add(p);
}
}
return(cmd);
}
private void GetCategories()
{
try
{
// Get the categories via a DataReader
EfzCommand oCmd = new EfzCommand("SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName", this.m_oCn);
this.m_oCn.Open();
EfzDataReader oDR = oCmd.ExecuteReader();
// Clear the category list and fill it
cboCategory.Items.Clear();
while (oDR.Read())
{
cboCategory.Items.Add(new Category((string)oDR["CategoryName"], (int)oDR["CategoryID"]));
}
oDR.Close();
this.m_oCn.Close();
// Select the first category
cboCategory.SelectedIndex = 0;
}
catch (EfzException exSql)
{
throw (new Exception(exSql.Message, exSql));
}
catch (Exception ex)
{
throw (new Exception(ex.Message, ex));
}
}
static public EfzCommand BuildDynamicDeleteCommand(esDataRequest request, List <string> modifiedColumns)
{
Dictionary <string, EfzParameter> types = Cache.GetParameters(request);
EfzCommand cmd = new EfzCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
string sql = "DELETE FROM " + CreateFullName(request) + " ";
string comma = String.Empty;
comma = String.Empty;
sql += " WHERE ";
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency || col.IsConcurrency)
{
EfzParameter p = types[col.Name];
cmd.Parameters.Add(CloneParameter(p));
sql += comma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = " AND ";
}
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return(cmd);
}
private void btnMem_Click(object sender, RoutedEventArgs e)
{
string connectionString = "connection type=MEMORY; initial catalog=TestDb; user=SA; password="******"CREATE TABLE Test(ID int, Name varchar(100));";
EfzCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "INSERT INTO Test(ID , Name ) VALUES(1,'Car');";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "SELECT * FROM TEST;";
cmd.CommandText = sql;
EfzDataReader reader = cmd.ExecuteReader();
reader.Read();
tbkText.Text = String.Format("ID = {0}, Name = {1} ", reader.GetInt32(0), reader.GetString(1));
}
}
public string GetDatabaseName()
{
string db = "PUBLIC";
try
{
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT DISTINCT SCHEMA FROM INFORMATION_SCHEMA.SYSTEM_SESSIONS";
cmd.Connection = conn;
using (EfzDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
db = reader["SCHEMA"].ToString();
}
reader.Close();
}
}
finally
{
//EfzConnection.ClearAllPools();
}
return(db);
}
public DataTable GetProcedures(string database)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateProceduresDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText =
@"SELECT p.*
FROM INFORMATION_SCHEMA.SYSTEM_PROCEDURES p
WHERE PROCEDURE_CAT = '" + database + "'";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["PROCEDURE_CATALOG"] = r["PROCEDURE_CAT"];
row["PROCEDURE_SCHEMA"] = r["PROCEDURE_SCHEM"];
row["PROCEDURE_NAME"] = r["PROCEDURE_NAME"];
row["PROCEDURE_TYPE"] = r["PROCEDURE_TYPE"];
row["DESCRIPTION"] = r["REMARKS"];
}
}
}
finally { }
return(metaData);
}
public DataTable GetTables(string database)
{
DataTable metaData = new DataTable();
/*SYSTEM_TABLES*/
try
{
metaData = context.CreateTablesDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES WHERE TABLE_TYPE='TABLE' AND TABLE_CAT='" + database + "'";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = r["TABLE_CAT"];
row["TABLE_SCHEMA"] = r["TABLE_SCHEM"];
row["TABLE_NAME"] = r["TABLE_NAME"];
row["TABLE_TYPE"] = (r["TABLE_TYPE"].ToString().IndexOf("SYSTEM ") > -1) ? "SYSTEM TABLE" : "TABLE";
row["DESCRIPTION"] = r["REMARKS"] + " : " + r["EFFIPROZ_TYPE"] + (r["COMMIT_ACTION"] == DBNull.Value ? string.Empty : (" " + r["COMMIT_ACTION"]));
}
}
}
finally { }
return(metaData);
}
protected override List <PersistentQueueItem <T> > MultiLoadImpl(int batch)
{
List <PersistentQueueItem <T> > result = new List <PersistentQueueItem <T> >(batch);
using (EfzConnection connection = new EfzConnection(this.connString))
{
EfzCommand command = connection.CreateCommand();
command.CommandText = "select top " + batch + " Id, Payload, EnqueueTime, Priority, Try from QueueItems where State = 0 order by Priority";
connection.Open();
using (EfzDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Add(PersistentQueueItem <T> .FromDataReader(reader));
}
}
if (result.Count > 0)
{
StringBuilder ids = new StringBuilder(result[0].Id.ToString());
for (int i = 1; i < result.Count; i++)
{
ids.Append(",");
ids.Append(result[i].Id);
}
command.CommandText = "update QueueItems set State = 1 where Id in (" + ids.ToString() + ");";
command.ExecuteNonQuery();
}
}
return(result);
}
static public EfzCommand BuildStoredProcUpdateCommand(esDataRequest request)
{
EfzCommand cmd = new EfzCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spUpdate + Delimiters.StoredProcNameClose;
PopulateStoredProcParameters(cmd, request);
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsComputed || col.IsEntitySpacesConcurrency)
{
EfzParameter p = cmd.Parameters["?p" + (col.Name).Replace(" ", String.Empty)];
p.SourceVersion = DataRowVersion.Original;
p.Direction = ParameterDirection.InputOutput;
}
}
return(cmd);
}
static public void AddParameters(EfzCommand cmd, esDataRequest request)
{
EfzParameter parameter;
if (request.QueryType == esQueryType.Text && request.QueryText != null && request.QueryText.Contains("{0}"))
{
int i = 0;
string token = String.Empty;
string sIndex = String.Empty;
string param = String.Empty;
foreach (esParameter esParam in request.Parameters)
{
sIndex = i.ToString();
token = '{' + sIndex + '}';
param = Delimiters.Param + "p" + sIndex;
request.QueryText = request.QueryText.Replace(token, param);
i++;
int pos = cmd.Parameters.Add(Delimiters.Param + esParam.Name);
parameter = cmd.Parameters[pos];
parameter.Value = esParam.Value;
}
}
else
{
string paramPrefix = request.ProviderMetadata.spLoadByPrimaryKey == cmd.CommandText ? Delimiters.Param + "p" : Delimiters.Param;
foreach (esParameter esParam in request.Parameters)
{
int pos = cmd.Parameters.Add(paramPrefix + esParam.Name);
parameter = cmd.Parameters[pos];
parameter.Value = esParam.Value;
// The default is ParameterDirection.Input
switch (esParam.Direction)
{
case esParameterDirection.InputOutput:
parameter.Direction = ParameterDirection.InputOutput;
break;
case esParameterDirection.Output:
parameter.Direction = ParameterDirection.Output;
parameter.DbType = esParam.DbType;
parameter.Size = esParam.Size;
//parameter.Scale = esParam.Scale;
//parameter.Precision = esParam.Precision;
break;
case esParameterDirection.ReturnValue:
parameter.Direction = ParameterDirection.ReturnValue;
break;
}
}
}
}
protected override void PurgeImpl()
{
using (EfzConnection connection = new EfzConnection(this.connString))
{
EfzCommand command = connection.CreateCommand();
command.CommandText = EfzQueuePersistence <T> .purgeSql;
connection.Open();
command.ExecuteNonQuery();
}
}
public static void DeleteFriends(string strOwnerID)
{
EnsureDBConnection();
if(m_cmdDeleteFriends == null) {
m_cmdDeleteFriends = new EfzCommand("DELETE FROM Friends WHERE OwnerID=@OwnerID", m_conDB);
m_cmdDeleteFriends.Parameters.Add(new EfzParameter() { DbType = EfzType.VarChar, ParameterName = "@OwnerID" });
}
m_cmdDeleteFriends.Parameters["@OwnerID"].Value = strOwnerID;
m_cmdDeleteFriends.ExecuteNonQuery();
}
protected override void FailImpl(PersistentQueueItem <T> item)
{
using (EfzConnection connection = new EfzConnection(this.connString))
{
EfzCommand command = connection.CreateCommand();
command.CommandText = string.Format("update QueueItems set Priority = {0}, Try = {1}, State = 0 where Id = {2}", item.Priority, item.Try, item.Id);
connection.Open();
command.ExecuteNonQuery();
}
}
protected override void RemoveImpl(PersistentQueueItem <T> item)
{
using (EfzConnection connection = new EfzConnection(this.connString))
{
EfzCommand command = connection.CreateCommand();
command.CommandText = "delete from QueueItems where Id = " + item.Id;
connection.Open();
command.ExecuteNonQuery();
}
}
protected override void DiscardImpl(PersistentQueueItem <T> item)
{
using (EfzConnection connection = new EfzConnection(this.connString))
{
string sql = string.Format("delete from QueueItems where Id = {0}; insert into DiscardQueueItems(Id, Payload, EnqueueTime) values({0}, '{1}', '{2}');", item.Id, item.PayloadToJson(), item.EnqueueTime.ToString("yyyy-MM-dd HH:mm:ss"));
EfzCommand command = connection.CreateCommand();
command.CommandText = sql;
connection.Open();
command.ExecuteNonQuery();
}
}
//doesn't fire exceptions
public static void DeleteDBUser()
{
EnsureDBConnection();
if(m_cmdDeleteDBUser == null) {
m_cmdDeleteDBUser = new EfzCommand("DELETE FROM DB_USER", m_conDB);
}
try {
m_cmdDeleteDBUser.ExecuteNonQuery();
}
catch { }
}
public sealed override DbDataAdapter GetDataAdapter(DbCommand dbCmd)
{
EfzCommand myCmd = dbCmd as EfzCommand;
if (myCmd == null)
{
throw new ArgumentException();
}
EfzDataAdapter sa = new EfzDataAdapter(myCmd);
return(sa);
}
public static EfzCommand PrepareCommand(esDataRequest request)
{
esDynamicQuerySerializable query = request.DynamicQuery;
EfzCommand cmd = new EfzCommand();
int pindex = NextParamIndex(cmd);
string sql = BuildQuery(request, query, cmd, ref pindex);
cmd.CommandText = sql;
return(cmd);
}
public static EfzCommand PrepareCommand(esDataRequest request)
{
esDynamicQuerySerializable query = request.DynamicQuery;
EfzCommand cmd = new EfzCommand();
int pindex = NextParamIndex(cmd);
string sql = BuildQuery(request, query, cmd, ref pindex);
cmd.CommandText = sql;
return cmd;
}
protected override void MultiSaveImpl(params PersistentQueueItem <T>[] items)
{
using (EfzConnection connection = new EfzConnection(this.connString))
{
StringBuilder sql = new StringBuilder();
for (int i = 0; i < items.Length; i++)
{
PersistentQueueItem <T> item = items[i];
sql.Append(string.Format("insert into QueueItems(Payload, EnqueueTime, Priority, Try, State) values('{0}', '{1}', {2}, 0, 0);", item.PayloadToJson().Replace("'", "''"), item.EnqueueTime.ToString("yyyy-MM-dd HH:mm:ss"), item.Priority));
}
EfzCommand command = connection.CreateCommand();
command.CommandText = sql.ToString();
connection.Open();
command.ExecuteNonQuery();
}
}
public DataTable GetForeignKeys(string database, string table)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateForeignKeysDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE WHERE (PKTABLE_NAME='" +
table + "' AND PKTABLE_CAT='" + database + "') OR (FKTABLE_NAME='" +
table + "' AND FKTABLE_CAT='" + database + "')";
cmd.Connection = conn;
DataTable dt = new DataTable();
EfzDataAdapter adapter = new EfzDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dt);
foreach (DataRow r in dt.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
// The main Information ...
row["PK_TABLE_CATALOG"] = r["PKTABLE_CAT"];
row["PK_TABLE_SCHEMA"] = r["PKTABLE_SCHEM"];
row["PK_TABLE_NAME"] = r["PKTABLE_NAME"];
row["FK_TABLE_CATALOG"] = r["FKTABLE_CAT"];
row["FK_TABLE_SCHEMA"] = r["FKTABLE_SCHEM"];
row["FK_TABLE_NAME"] = r["FKTABLE_NAME"];
row["ORDINAL"] = r["KEY_SEQ"];
row["FK_NAME"] = r["FK_NAME"];
row["PK_NAME"] = r["PK_NAME"];
row["UPDATE_RULE"] = r["UPDATE_RULE"];
row["DELETE_RULE"] = r["DELETE_RULE"];
row["DEFERRABILITY"] = r["DEFERRABILITY"];
row["PK_COLUMN_NAME"] = r["PKCOLUMN_NAME"];
row["FK_COLUMN_NAME"] = r["FKCOLUMN_NAME"];
}
}
finally { }
return(metaData);
}
protected override int Init(string persistPath)
{
this.connString = EfzQueuePersistence <T> .GetConnectionString(persistPath);
int result;
using (EfzConnection connection = new EfzConnection(this.connString))
{
EfzCommand command = connection.CreateCommand();
command.CommandText = EfzQueuePersistence <T> .databaseSql;
connection.Open();
command.ExecuteNonQuery();
command.CommandText = EfzQueuePersistence <T> .restoreAndCountSql;
result = (int)command.ExecuteScalar();
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="databaseName">
/// In access ,this para is not used ever
/// Like SSCE ,Recommend use this base method with( no parameter)</param>
/// <returns></returns>
public override List <string> GetTableListInDatabase(string databaseName)
{
List <string> jieguo = new List <string>();
EfzDataAdapter reader = null;
string cmdStr = string.Empty;
EfzConnection myConn = (EfzConnection)baseConn;
using (EfzCommand cmd = new EfzCommand(cmdStr, myConn))
{
cmd.CommandTimeout = 10;
try
{
DataTable userTables;
userTables = myConn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
Debug.WriteLine("Table count " + userTables.Rows.Count);
for (int i = 0; i < userTables.Rows.Count; i++)
{
jieguo.Add(userTables.Rows[i]["Table_Name"].ToString());
Debug.WriteLine(userTables.Rows[i]["Table_Name"].ToString());
}
}
catch (Exception ee)
{
GlobalDefine.SP.LastErrorMsg = ee.Message;
#if DEBUG
Debug.WriteLine(ee.Message);
Debug.WriteLine(ee.StackTrace);
throw ee;
#else
GlobalDefine.SP.LastErrorMsg = ee.Message;
#endif
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
}
return(jieguo);
}
public DataTable GetTableIndexes(string database, string table)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateIndexesDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_INDEXINFO WHERE TABLE_NAME='" +
table + "' AND TABLE_CAT='" + database + "'";
cmd.Connection = conn;
DataTable dt = new DataTable();
EfzDataAdapter adapter = new EfzDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dt);
foreach (DataRow r in dt.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = r["TABLE_CAT"];
row["TABLE_SCHEMA"] = r["TABLE_SCHEM"];
row["TABLE_NAME"] = r["TABLE_NAME"];
row["INDEX_NAME"] = r["INDEX_NAME"];
row["UNIQUE"] = r["UNIQUE_INDEX"];
row["PRIMARY_KEY"] = r["PRIMARY_INDEX"];
row["CARDINALITY"] = r["ROW_CARDINALITY"] == DBNull.Value ? (object)DBNull.Value : (object)Convert.ToDecimal(r["ROW_CARDINALITY"]);
row["COLUMN_NAME"] = r["COLUMN_NAME"];
row["FILTER_CONDITION"] = r["FILTER_CONDITION"];
row["TYPE"] = r["TYPE"];
row["PAGES"] = r["PAGES"];
row["ORDINAL_POSITION"] = r["ORDINAL_POSITION"];
}
}
finally { }
return(metaData);
}
protected static string BuildQuery(esDataRequest request, esDynamicQuerySerializable query, EfzCommand cmd, ref int pindex)
{
bool paging = false;
if (query.es.PageNumber.HasValue && query.es.PageSize.HasValue)
paging = true;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string select = GetSelectStatement(request, query, cmd, ref pindex);
string from = GetFromStatement(request, query, cmd, ref pindex);
string join = GetJoinStatement(request, query, cmd, ref pindex);
string where = GetComparisonStatement(request, query, iQuery.InternalWhereItems, " WHERE ", cmd, ref pindex);
string groupBy = GetGroupByStatement(request, query, cmd, ref pindex);
string having = GetComparisonStatement(request, query, iQuery.InternalHavingItems, " HAVING ", cmd, ref pindex);
string orderBy = GetOrderByStatement(request, query, cmd, ref pindex);
string setOperation = GetSetOperationStatement(request, query, cmd, ref pindex);
string sql = String.Empty;
if (paging)
{
int begRow = ((query.es.PageNumber.Value - 1) * query.es.PageSize.Value) + 1;
int endRow = begRow + (query.es.PageSize.Value - 1);
// The WITH statement
sql += "WITH [withStatement] AS (";
sql += "SELECT " + select + ", ROW_NUMBER() OVER(" + orderBy + ") AS ESRN ";
sql += "FROM " + from + join + where + groupBy + ") ";
sql += "SELECT * FROM [withStatement] ";
sql += "WHERE ESRN BETWEEN " + begRow + " AND " + endRow;
sql += " ORDER BY ESRN ASC";
}
else
{
sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
}
return sql;
}
//doesn't fire exceptions
public static DBUser ReadDBUser()
{
EnsureDBConnection();
if(m_cmdReadDBUser == null) {
m_cmdReadDBUser = new EfzCommand("SELECT TOKEN, ID, NAME, PICTURE_LINK, GENDER, LINK, HOMETOWN, STORE_TIME FROM DB_USER WHERE STORE_TIME>@AllowedStoreTime", m_conDB);
m_cmdReadDBUser.Parameters.Add(new EfzParameter() { DbType = EfzType.Date, ParameterName = "@AllowedStoreTime" });
}
m_cmdReadDBUser.Parameters["@AllowedStoreTime"].Value = DateTime.Now.AddHours(-1); //allow access for 1 hour
try {
EfzDataReader eRead = m_cmdReadDBUser.ExecuteReader();
DBUser dbRet = null;
if(eRead.Read()) {
dbRet = new DBUser(eRead.GetString(0), eRead.GetString(1), eRead.GetString(2), eRead.GetString(3), eRead.GetString(4), eRead.GetString(5), eRead.GetString(6));
}
eRead.Close();
eRead.Dispose();
return (dbRet);
}
catch { return (null); }
}
static public void PopulateStoredProcParameters(EfzCommand cmd, esDataRequest request)
{
Dictionary <string, EfzParameter> types = Cache.GetParameters(request);
EfzParameter p;
foreach (esColumnMetadata col in request.Columns)
{
p = types[col.Name];
p = CloneParameter(p);
p.ParameterName = p.ParameterName.Replace("?", "?p");
p.SourceVersion = DataRowVersion.Current;
if (p.EfzType == EfzType.TimeStamp)
{
p.Direction = ParameterDirection.InputOutput;
}
cmd.Parameters.Add(p);
}
}
public override bool ExecuteProcedureWithNoQuery(string procedureName, object[] varList, OleDbType[] dbTypeList, int[] objectLengthList, object[] objectList, object[] objectValueList)
{
bool jieguo = false;
if (invalidator.IsInvalidArguments(procedureName))
{
return(false);
}
if (!IsOpened)
{
throw new ConnectErrorException();
}
try
{
using (EfzCommand myCmd = new EfzCommand())
{
myCmd.Connection = (EfzConnection)baseConn;
myCmd.CommandType = CommandType.StoredProcedure;
myCmd.CommandText = procedureName;
for (int i = 0; i < varList.Length; i++)
{
myCmd.Parameters.Add("@" + varList[i], (EfzType)(dbTypeList[i]), objectLengthList[i], objectList[i].ToString());
myCmd.Parameters["@" + varList[i]].Value = objectValueList[i];
Debug.WriteLine(i + " " + varList[i] + " " + dbTypeList[i] + " " + objectValueList[i]);
}
myCmd.ExecuteNonQuery();
}
jieguo = true;
}
catch (Exception ee)
{
GlobalDefine.SP.LastErrorMsg = ee.Message;
#if DEBUG
throw ee;
#else
return(false);
#endif
}
return(jieguo);
}
static public void GatherReturnParameters(EfzCommand cmd, esDataRequest request, esDataResponse response)
{
if (cmd.Parameters.Count > 0)
{
if (request.Parameters != null && request.Parameters.Count > 0)
{
string paramPrefix = request.ProviderMetadata.spLoadByPrimaryKey == cmd.CommandText ? Delimiters.Param + "p" : Delimiters.Param;
response.Parameters = new esParameters();
foreach (esParameter esParam in request.Parameters)
{
if (esParam.Direction != esParameterDirection.Input)
{
response.Parameters.Add(esParam);
EfzParameter p = cmd.Parameters[paramPrefix + esParam.Name];
esParam.Value = p.Value;
}
}
}
}
}
public DataTable GetViews(string database)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateViewsDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText =
@"SELECT t.TABLE_CAT, t.TABLE_SCHEM, v.TABLE_NAME, v.VIEW_DEFINITION, v.CHECK_OPTION, t.REMARKS, t.EFFIPROZ_TYPE, t.TABLE_TYPE, v.IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS v
left join INFORMATION_SCHEMA.SYSTEM_TABLES t on t.TABLE_NAME = v.TABLE_NAME";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = r["TABLE_CAT"];
row["TABLE_SCHEMA"] = r["TABLE_SCHEM"];
row["TABLE_NAME"] = r["TABLE_NAME"];
row["TABLE_TYPE"] = (r["TABLE_TYPE"].ToString().IndexOf("SYSTEM ") > -1) ? "SYSTEM VIEW" : "VIEW";
row["DESCRIPTION"] = r["REMARKS"] + " : " + r["EFFIPROZ_TYPE"] + (r["CHECK_OPTION"] == DBNull.Value ? string.Empty : (" " + r["CHECK_OPTION"]));
row["IS_UPDATABLE"] = r["IS_UPDATABLE"].ToString().Equals("YES", StringComparison.CurrentCultureIgnoreCase);
//row["INSERTABLE_INTO"] = r["INSERTABLE_INTO"]; // NOT SUPPORTED YET
row["VIEW_TEXT"] = r["VIEW_DEFINITION"];
}
}
}
finally { }
return(metaData);
}
public List <string> GetPrimaryKeyColumns(string database, string table)
{
List <string> primaryKeys = new List <string>();
try
{
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_PRIMARYKEYS WHERE TABLE_NAME='" + table + "' AND TABLE_CAT='" + database + "'";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
primaryKeys.Add(r["COLUMN_NAME"].ToString());
}
}
}
finally { }
return(primaryKeys);
}
static private esDataResponse LoadManyToMany(esDataRequest request)
{
esDataResponse response = new esDataResponse();
EfzCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new EfzCommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
string mmQuery = request.QueryText;
string[] sections = mmQuery.Split('|');
string[] tables = sections[0].Split(',');
string[] columns = sections[1].Split(',');
// We build the query, we don't use Delimiters to avoid tons of extra concatentation
string sql = "SELECT * FROM `" + tables[0];
sql += "` JOIN `" + tables[1] + "` ON `" + tables[0] + "`.`" + columns[0] + "` = `";
sql += tables[1] + "`.`" + columns[1];
sql += "` WHERE `" + tables[1] + "`.`" + sections[2] + "` = ?";
if (request.Parameters != null)
{
foreach (esParameter esParam in request.Parameters)
{
sql += esParam.Name;
}
Shared.AddParameters(cmd, request);
}
EfzDataAdapter da = new EfzDataAdapter();
cmd.CommandText = sql;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadManyToMany", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
private void GetCategories()
{
try
{
// Get the categories via a DataReader
EfzCommand oCmd = new EfzCommand("SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName", this.m_oCn);
this.m_oCn.Open();
EfzDataReader oDR = oCmd.ExecuteReader();
// Clear the category list and fill it
cboCategory.Items.Clear();
while (oDR.Read())
{
cboCategory.Items.Add(new Category((string)oDR["CategoryName"], (int)oDR["CategoryID"]));
}
oDR.Close();
this.m_oCn.Close();
// Select the first category
cboCategory.SelectedIndex = 0;
}
catch (EfzException exSql)
{
throw (new Exception(exSql.Message, exSql));
}
catch (Exception ex)
{
throw (new Exception(ex.Message, ex));
}
}
esDataResponse IDataProvider.ExecuteScalar(esDataRequest request)
{
esDataResponse response = new esDataResponse();
EfzCommand cmd = null;
try
{
cmd = new EfzCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = request.QueryText;
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
case esQueryType.DynamicQuery:
cmd = QueryBuilder.PrepareCommand(request);
break;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteScalar", System.Environment.StackTrace))
{
try
{
response.Scalar = cmd.ExecuteScalar();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
response.Scalar = cmd.ExecuteScalar();
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
static private esDataResponse LoadDataTableFromText(esDataRequest request)
{
esDataResponse response = new esDataResponse();
EfzCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new EfzCommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
EfzDataAdapter da = new EfzDataAdapter();
cmd.CommandText = request.QueryText;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromText", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
public DataTable GetTableColumns(string database, string table)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateColumnsDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS WHERE TABLE_NAME='" + table + "' AND TABLE_CAT='" + database + "'";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = r["TABLE_CAT"];
row["TABLE_SCHEMA"] = r["TABLE_SCHEM"];
row["TABLE_NAME"] = r["TABLE_NAME"];
row["COLUMN_NAME"] = r["COLUMN_NAME"];
row["ORDINAL_POSITION"] = r["ORDINAL_POSITION"];
row["DESCRIPTION"] = r["REMARKS"];
row["COLUMN_HASDEFAULT"] = false;
if (r["IS_NULLABLE"] != DBNull.Value)
{
row["IS_NULLABLE"] = r["IS_NULLABLE"];
}
if (r["COLUMN_DEF"] != DBNull.Value)
{
row["COLUMN_HASDEFAULT"] = true;
row["COLUMN_DEFAULT"] = r["COLUMN_DEF"];
}
if (r["IS_GENERATED"] != DBNull.Value && r["IDENTITY_INCREMENT"] != DBNull.Value)
{
row["IS_AUTO_KEY"] = true;
row["AUTO_KEY_SEED"] = Convert.ToInt32(r["IDENTITY_START"]);
row["AUTO_KEY_INCREMENT"] = Convert.ToInt32(r["IDENTITY_INCREMENT"]);
}
int type = Convert.ToInt32(r["DATA_TYPE"]); // dbType enum code
string typeName = (string)r["TYPE_NAME"]; // dbType enum code
int charMax = 0;
int precision = 0;
int scale = 0;
if (r["COLUMN_SIZE"] != DBNull.Value)
{
charMax = Convert.ToInt32(r["COLUMN_SIZE"]);
}
if (r["COLUMN_SIZE"] != DBNull.Value)
{
precision = Convert.ToInt32(r["COLUMN_SIZE"]);
}
if (r["DECIMAL_DIGITS"] != DBNull.Value)
{
scale = Convert.ToInt32(r["DECIMAL_DIGITS"]);
}
row["DATA_TYPE"] = type;
row["TYPE_NAME"] = typeName;
row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(typeName, charMax, precision, scale);
row["NUMERIC_PRECISION"] = precision;
row["NUMERIC_SCALE"] = scale;
row["CHARACTER_MAXIMUM_LENGTH"] = charMax;
//TODO: we will have to find the best way to implement this later?
//row["IS_COMPUTED"] = (type == "timestamp") ? true : false;
}
}
}
finally { }
return(metaData);
}
static public EfzCommand BuildStoredProcUpdateCommand(esDataRequest request)
{
EfzCommand cmd = new EfzCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spUpdate + Delimiters.StoredProcNameClose;
PopulateStoredProcParameters(cmd, request);
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsComputed || col.IsEntitySpacesConcurrency)
{
EfzParameter p = cmd.Parameters["?p" + (col.Name).Replace(" ", String.Empty)];
p.SourceVersion = DataRowVersion.Original;
p.Direction = ParameterDirection.InputOutput;
}
}
return cmd;
}
protected static string GetSetOperationStatement(esDataRequest request, esDynamicQuerySerializable query, EfzCommand cmd, ref int pindex)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalSetOperations != null)
{
foreach (esSetOperation setOperation in iQuery.InternalSetOperations)
{
switch (setOperation.SetOperationType)
{
case esSetOperationType.Union: sql += " UNION "; break;
case esSetOperationType.UnionAll: sql += " UNION ALL "; break;
case esSetOperationType.Intersect: sql += " INTERSECT "; break;
case esSetOperationType.Except: sql += " EXCEPT "; break;
}
sql += BuildQuery(request, setOperation.Query, cmd, ref pindex);
}
}
return(sql);
}
public EfzCommandWrapper()
{
m_command = new EfzCommand();
}
static public void AddParameters(EfzCommand cmd, esDataRequest request)
{
EfzParameter parameter;
if (request.QueryType == esQueryType.Text && request.QueryText != null && request.QueryText.Contains("{0}"))
{
int i = 0;
string token = String.Empty;
string sIndex = String.Empty;
string param = String.Empty;
foreach (esParameter esParam in request.Parameters)
{
sIndex = i.ToString();
token = '{' + sIndex + '}';
param = Delimiters.Param + "p" + sIndex;
request.QueryText = request.QueryText.Replace(token, param);
i++;
int pos = cmd.Parameters.Add(Delimiters.Param + esParam.Name);
parameter = cmd.Parameters[pos];
parameter.Value = esParam.Value;
}
}
else
{
string paramPrefix = request.ProviderMetadata.spLoadByPrimaryKey == cmd.CommandText ? Delimiters.Param + "p" : Delimiters.Param;
foreach (esParameter esParam in request.Parameters)
{
int pos = cmd.Parameters.Add(paramPrefix + esParam.Name);
parameter = cmd.Parameters[pos];
parameter.Value = esParam.Value;
// The default is ParameterDirection.Input
switch (esParam.Direction)
{
case esParameterDirection.InputOutput:
parameter.Direction = ParameterDirection.InputOutput;
break;
case esParameterDirection.Output:
parameter.Direction = ParameterDirection.Output;
parameter.DbType = esParam.DbType;
parameter.Size = esParam.Size;
//parameter.Scale = esParam.Scale;
//parameter.Precision = esParam.Precision;
break;
case esParameterDirection.ReturnValue:
parameter.Direction = ParameterDirection.ReturnValue;
break;
}
}
}
}
static public void GatherReturnParameters(EfzCommand cmd, esDataRequest request, esDataResponse response)
{
if (cmd.Parameters.Count > 0)
{
if (request.Parameters != null && request.Parameters.Count > 0)
{
string paramPrefix = request.ProviderMetadata.spLoadByPrimaryKey == cmd.CommandText ? Delimiters.Param + "p" : Delimiters.Param;
response.Parameters = new esParameters();
foreach (esParameter esParam in request.Parameters)
{
if (esParam.Direction != esParameterDirection.Input)
{
response.Parameters.Add(esParam);
EfzParameter p = cmd.Parameters[paramPrefix + esParam.Name];
esParam.Value = p.Value;
}
}
}
}
}
static public void PopulateStoredProcParameters(EfzCommand cmd, esDataRequest request)
{
Dictionary<string, EfzParameter> types = Cache.GetParameters(request);
EfzParameter p;
foreach (esColumnMetadata col in request.Columns)
{
p = types[col.Name];
p = CloneParameter(p);
p.ParameterName = p.ParameterName.Replace("?", "?p");
p.SourceVersion = DataRowVersion.Current;
if (p.EfzType == EfzType.TimeStamp)
{
p.Direction = ParameterDirection.InputOutput;
}
cmd.Parameters.Add(p);
}
}
static public EfzCommand BuildStoredProcDeleteCommand(esDataRequest request)
{
EfzCommand cmd = new EfzCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spDelete + Delimiters.StoredProcNameClose;
Dictionary<string, EfzParameter> types = Cache.GetParameters(request);
EfzParameter p;
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency)
{
p = types[col.Name];
p = CloneParameter(p);
p.ParameterName = p.ParameterName.Replace("?", "?p");
p.SourceVersion = DataRowVersion.Current;
cmd.Parameters.Add(p);
}
}
return cmd;
}
static public EfzCommand BuildDynamicInsertCommand(esDataRequest request, List<string> modifiedColumns)
{
string sql = String.Empty;
string defaults = String.Empty;
string into = String.Empty;
string values = String.Empty;
string comma = String.Empty;
string defaultComma = String.Empty;
string where = String.Empty;
string whereComma = String.Empty;
PropertyCollection props = new PropertyCollection();
EfzParameter p = null;
Dictionary<string, EfzParameter> types = Cache.GetParameters(request);
EfzCommand cmd = new EfzCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
esColumnMetadataCollection cols = request.Columns;
foreach (esColumnMetadata col in cols)
{
bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name);
if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsEntitySpacesConcurrency))
{
p = types[col.Name];
cmd.Parameters.Add(CloneParameter(p));
into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
values += comma + p.ParameterName;
comma = ", ";
}
else if (col.IsAutoIncrement)
{
props["AutoInc"] = col.Name;
props["Source"] = request.ProviderMetadata.Source;
p = CloneParameter(types[col.Name]);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
}
else if (col.IsConcurrency)
{
props["Timestamp"] = col.Name;
props["Source"] = request.ProviderMetadata.Source;
p = CloneParameter(types[col.Name]);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
}
else if (col.IsEntitySpacesConcurrency)
{
into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
values += comma + "1";
comma = ", ";
p = CloneParameter(types[col.Name]);
p.Direction = ParameterDirection.Output;
p.Value = 1; // Seems to work, We'll take it ...
cmd.Parameters.Add(p);
}
else if (col.IsComputed)
{
// Do nothing but leave this here
}
else if (cols.IsSpecialColumn(col))
{
// Do nothing but leave this here
}
else if (col.HasDefault)
{
defaults += defaultComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
defaultComma = ",";
string def = col.Default.ToLower();
if (def.Contains("guid") || def.Contains("newid"))
{
p = CloneParameter(types[col.Name]);
p.Direction = ParameterDirection.Output;
p.SourceVersion = DataRowVersion.Current;
cmd.Parameters.Add(p);
sql += " IF " + Delimiters.Param + col.Name + " IS NULL SET " +
Delimiters.Param + col.Name + " = NEWID();";
into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
values += comma + p.ParameterName;
comma = ", ";
}
}
if (col.IsInPrimaryKey)
{
where += whereComma + col.Name;
whereComma = ",";
}
}
#region Special Columns
if (cols.DateAdded != null && cols.DateAdded.IsServerSide)
{
into += comma + Delimiters.ColumnOpen + cols.DateAdded.ColumnName + Delimiters.ColumnClose;
values += comma + request.ProviderMetadata["DateAdded.ServerSideText"];
comma = ", ";
defaults += defaultComma + Delimiters.ColumnOpen + cols.DateAdded.ColumnName + Delimiters.ColumnClose;
defaultComma = ",";
}
if (cols.DateModified != null && cols.DateModified.IsServerSide)
{
into += comma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose;
values += comma + request.ProviderMetadata["DateModified.ServerSideText"];
comma = ", ";
defaults += defaultComma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose;
defaultComma = ",";
}
if (cols.AddedBy != null && cols.AddedBy.IsServerSide)
{
into += comma + Delimiters.ColumnOpen + cols.AddedBy.ColumnName + Delimiters.ColumnClose;
values += comma + request.ProviderMetadata["AddedBy.ServerSideText"];
comma = ", ";
defaults += defaultComma + Delimiters.ColumnOpen + cols.AddedBy.ColumnName + Delimiters.ColumnClose;
defaultComma = ",";
}
if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
{
into += comma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose;
values += comma + request.ProviderMetadata["ModifiedBy.ServerSideText"];
comma = ", ";
defaults += defaultComma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose;
defaultComma = ",";
}
#endregion
if (defaults.Length > 0)
{
comma = String.Empty;
props["Defaults"] = defaults;
props["Where"] = where;
}
sql += " INSERT INTO " + CreateFullName(request);
if (into.Length != 0)
{
sql += "(" + into + ") VALUES (" + values + ")";
}
else
{
sql += "DEFAULT VALUES";
}
request.Properties = props;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return cmd;
}
private static int NextParamIndex(EfzCommand cmd)
{
return cmd.Parameters.Count;
}
protected static string GetOrderByStatement(esDataRequest request, esDynamicQuerySerializable query, EfzCommand cmd, ref int pindex)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalOrderByItems != null)
{
sql += " ORDER BY ";
foreach (esOrderByItem orderByItem in iQuery.InternalOrderByItems)
{
bool literal = false;
sql += comma;
string columnName = orderByItem.Expression.Column.Name;
if (columnName != null && columnName[0] == '<')
{
sql += columnName.Substring(1, columnName.Length - 2);
if (orderByItem.Direction == esOrderByDirection.Unassigned)
{
literal = true; // They must provide the DESC/ASC in the literal string
}
}
else
{
sql += GetExpressionColumn(orderByItem.Expression, false, false);
}
if (!literal)
{
if (orderByItem.Direction == esOrderByDirection.Ascending)
sql += " ASC";
else
sql += " DESC";
}
comma = ",";
}
}
return sql;
}
protected static string GetFromStatement(esDataRequest request, esDynamicQuerySerializable query, EfzCommand cmd, ref int pindex)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string sql = String.Empty;
if (iQuery.InternalFromQuery == null)
{
sql = Shared.CreateFullName(request, query);
if (iQuery.JoinAlias != " ")
{
sql += " " + iQuery.JoinAlias;
}
if (query.es.WithNoLock == true)
{
sql += " WITH (NOLOCK)";
}
}
else
{
IDynamicQuerySerializableInternal iSubQuery = iQuery.InternalFromQuery as IDynamicQuerySerializableInternal;
iSubQuery.IsInSubQuery = true;
sql += "(";
sql += BuildQuery(request, iQuery.InternalFromQuery, cmd, ref pindex);
sql += ")";
if (iSubQuery.SubQueryAlias != " ")
{
sql += " AS " + iSubQuery.SubQueryAlias;
}
iSubQuery.IsInSubQuery = false;
}
return sql;
}
protected static string GetSetOperationStatement(esDataRequest request, esDynamicQuerySerializable query, EfzCommand cmd, ref int pindex)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalSetOperations != null)
{
foreach (esSetOperation setOperation in iQuery.InternalSetOperations)
{
switch (setOperation.SetOperationType)
{
case esSetOperationType.Union: sql += " UNION "; break;
case esSetOperationType.UnionAll: sql += " UNION ALL "; break;
case esSetOperationType.Intersect: sql += " INTERSECT "; break;
case esSetOperationType.Except: sql += " EXCEPT "; break;
}
sql += BuildQuery(request, setOperation.Query, cmd, ref pindex);
}
}
return sql;
}
public DataTable GetProcedureParameters(string database, string procedure)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateParametersDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText =
@"SELECT *
FROM INFORMATION_SCHEMA.SYSTEM_PROCEDURECOLUMNS
WHERE PROCEDURE_NAME='" + procedure + "' and PROCEDURE_CAT='" + database + "'";
cmd.Connection = conn;
using (EfzDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
if (r["IS_NULLABLE"] != DBNull.Value)
{
row["IS_NULLABLE"] = r["NULLABLE"];
}
int type = Convert.ToInt32(r["DATA_TYPE"]); // dbType enum code
string typeName = (string)r["TYPE_NAME"]; // dbType enum code
int charMax = 0;
int charOctetMax = 0;
int precision = 0;
int scale = 0;
if (r["CHAR_OCTET_LENGTH"] != DBNull.Value)
{
charOctetMax = Convert.ToInt32(r["CHAR_OCTET_LENGTH"]);
}
if (r["LENGTH"] != DBNull.Value)
{
charMax = Convert.ToInt32(r["LENGTH"]);
}
if (r["PRECISION"] != DBNull.Value)
{
precision = Convert.ToInt32(r["PRECISION"]);
}
if (r["SCALE"] != DBNull.Value)
{
scale = Convert.ToInt32(r["SCALE"]);
}
row["DATA_TYPE"] = type;
row["TYPE_NAME"] = typeName;
//row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(typeName, charMax, precision, scale);
row["CHARACTER_MAXIMUM_LENGTH"] = charMax;
row["CHARACTER_OCTET_LENGTH"] = charOctetMax;
row["NUMERIC_PRECISION"] = precision;
row["NUMERIC_SCALE"] = scale;
row["PROCEDURE_CATALOG"] = r["PROCEDURE_CAT"];
row["PROCEDURE_SCHEMA"] = r["PROCEDURE_SCHEM"];
row["PROCEDURE_NAME"] = r["PROCEDURE_NAME"];
row["PARAMETER_NAME"] = r["COLUMN_NAME"];
row["ORDINAL_POSITION"] = r["ORDINAL_POSITION"];
row["PARAMETER_TYPE"] = r["COLUMN_TYPE"];
row["PARAMETER_HASDEFAULT"] = r["COLUMN_DEF"] != DBNull.Value && r["COLUMN_DEF"] != string.Empty;
row["PARAMETER_DEFAULT"] = r["COLUMN_DEF"];
//row["IS_NULLABLE"] = r["NULLABLE"];
//row["DATA_TYPE"] = r["DATA_TYPE"];
//row["CHARACTER_MAXIMUM_LENGTH"] = r["LENGTH"];
//row["CHARACTER_OCTET_LENGTH"] = r["CHAR_OCTET_LENGTH"];
row["NUMERIC_PRECISION"] = r["PRECISION"];
row["NUMERIC_SCALE"] = r["SCALE"];
row["DESCRIPTION"] = r["REMARKS"];
//row["TYPE_NAME"] = r["TYPE_NAME"];
//row["LOCAL_TYPE_NAME"] = r[""];
}
}
}
finally { }
return(metaData);
}
esDataResponse IDataProvider.ExecuteReader(esDataRequest request)
{
esDataResponse response = new esDataResponse();
EfzCommand cmd = null;
try
{
cmd = new EfzCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = request.QueryText;
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
case esQueryType.DynamicQuery:
cmd = QueryBuilder.PrepareCommand(request);
break;
}
cmd.Connection = new EfzConnection(request.ConnectionString);
cmd.Connection.Open();
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteReader", System.Environment.StackTrace))
{
try
{
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
protected static string GetJoinStatement(esDataRequest request, esDynamicQuerySerializable query, EfzCommand cmd, ref int pindex)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalJoinItems != null)
{
foreach (esJoinItem joinItem in iQuery.InternalJoinItems)
{
esJoinItem.esJoinItemData joinData = (esJoinItem.esJoinItemData)joinItem;
switch (joinData.JoinType)
{
case esJoinType.InnerJoin:
sql += " INNER JOIN ";
break;
case esJoinType.LeftJoin:
sql += " LEFT JOIN ";
break;
case esJoinType.RightJoin:
sql += " RIGHT JOIN ";
break;
case esJoinType.FullJoin:
sql += " FULL JOIN ";
break;
}
IDynamicQuerySerializableInternal iSubQuery = joinData.Query as IDynamicQuerySerializableInternal;
sql += Shared.CreateFullName(request, joinData.Query);
sql += " " + iSubQuery.JoinAlias;
if (query.es.WithNoLock == true)
{
sql += " WITH (NOLOCK)";
}
sql += " ON ";
sql += GetComparisonStatement(request, query, joinData.WhereItems, String.Empty, cmd, ref pindex);
}
}
return sql;
}
protected static string GetSelectStatement(esDataRequest request, esDynamicQuerySerializable query, EfzCommand cmd, ref int pindex)
{
string sql = String.Empty;
string comma = String.Empty;
bool selectAll = true;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (query.es.Distinct) sql += " DISTINCT ";
if (query.es.Top >= 0) sql += " TOP " + query.es.Top.ToString() + " ";
if (iQuery.InternalSelectColumns != null)
{
selectAll = false;
foreach (esExpression expressionItem in iQuery.InternalSelectColumns)
{
if (expressionItem.Query != null)
{
IDynamicQuerySerializableInternal iSubQuery = expressionItem.Query as IDynamicQuerySerializableInternal;
sql += comma;
if (iSubQuery.SubQueryAlias == string.Empty)
{
sql += iSubQuery.JoinAlias + ".*";
}
else
{
iSubQuery.IsInSubQuery = true;
sql += " (" + BuildQuery(request, expressionItem.Query as esDynamicQuerySerializable, cmd, ref pindex) + ") AS " + iSubQuery.SubQueryAlias;
iSubQuery.IsInSubQuery = false;
}
comma = ",";
}
else
{
sql += comma;
string columnName = expressionItem.Column.Name;
if (columnName != null && columnName[0] == '<')
sql += columnName.Substring(1, columnName.Length - 2);
else
sql += GetExpressionColumn(expressionItem, false, true);
comma = ",";
}
}
sql += " ";
}
if (query.es.CountAll)
{
selectAll = false;
sql += comma;
sql += "COUNT(*)";
if (query.es.CountAllAlias != null)
{
// Need DBMS string delimiter here
sql += " AS " + Delimiters.StringOpen + query.es.CountAllAlias + Delimiters.StringClose;
}
}
if (selectAll)
{
sql += "*";
}
return sql;
}
// This is used only to execute the Dynamic Query API
static private void LoadDataTableFromDynamicQuery(esDataRequest request, esDataResponse response, EfzCommand cmd)
{
try
{
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
EfzDataAdapter da = new EfzDataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromDynamicQuery", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
protected static string GetGroupByStatement(esDataRequest request, esDynamicQuerySerializable query, EfzCommand cmd, ref int pindex)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalGroupByItems != null)
{
sql += " GROUP BY ";
foreach (esGroupByItem groupBy in iQuery.InternalGroupByItems)
{
sql += comma;
string columnName = groupBy.Expression.Column.Name;
if (columnName != null && columnName[0] == '<')
sql += columnName.Substring(1, columnName.Length - 2);
else
sql += GetExpressionColumn(groupBy.Expression, false, false);
comma = ",";
}
if (query.es.WithRollup)
{
sql += " WITH ROLLUP";
}
}
return sql;
}
public EfzCommandWrapper()
{
m_command = new EfzCommand();
}
protected static void OnRowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs 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"];
string source = props["Source"] as string;
if (e.StatementType == StatementType.Insert)
{
if (props.Contains("AutoInc"))
{
string autoInc = props["AutoInc"] as string;
EfzCommand cmd = new EfzCommand();
cmd.Connection = e.Command.Connection as EfzConnection;
cmd.Transaction = e.Command.Transaction as EfzTransaction;
cmd.CommandText = "SELECT LastIdentity([" + autoInc + "]) FROM [" + source + "]";
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
{
o = cmd.ExecuteScalar();
}
if (o != null)
{
e.Row[autoInc] = o;
((EfzParameter)e.Command.Parameters["@" + autoInc]).Value = o;
}
}
if (props.Contains("EntitySpacesConcurrency"))
{
string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string;
packet.CurrentValues[esConcurrencyColumn] = 1;
}
}
if (props.Contains("Timestamp"))
{
string column = props["Timestamp"] as string;
EfzCommand cmd = new EfzCommand();
cmd.Connection = e.Command.Connection as EfzConnection;
cmd.Transaction = e.Command.Transaction as EfzTransaction;
cmd.CommandText = "SELECT LastTimestamp('" + source + "');";
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
{
o = cmd.ExecuteScalar();
}
if (o != null)
{
((EfzParameter)e.Command.Parameters["@" + column]).Value = o;
}
}
//-------------------------------------------------------------------------------------------------
// 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
EfzCommand cmd = new EfzCommand();
cmd.Connection = e.Command.Connection as EfzConnection;
cmd.Transaction = e.Command.Transaction as EfzTransaction;
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)
{
EfzParameter p = new EfzParameter("@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 + ";";
EfzDataReader 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
{
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];
EfzParameter p = e.Command.Parameters["@" + colName] as EfzParameter;
object v = null;
switch (Type.GetTypeCode(o.GetType()))
{
case TypeCode.Int16: v = ((System.Int16)o) + 1; break;
case TypeCode.Int32: v = ((System.Int32)o) + 1; break;
case TypeCode.Int64: v = ((System.Int64)o) + 1; break;
case TypeCode.UInt16: v = ((System.UInt16)o) + 1; break;
case TypeCode.UInt32: v = ((System.UInt32)o) + 1; break;
case TypeCode.UInt64: v = ((System.UInt64)o) + 1; break;
}
p.Value = v;
}
}
}
catch { }
}
static private void CleanupCommand(EfzCommand cmd)
{
if (cmd != null && cmd.Connection != null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
}
protected static string GetComparisonStatement(esDataRequest request, esDynamicQuerySerializable query, List<esComparison> items, string prefix, EfzCommand cmd, ref int pindex)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
//=======================================
// WHERE
//=======================================
if (items != null)
{
sql += prefix;
DbType paramType = DbType.String;
string compareTo = String.Empty;
foreach (esComparison comparisonItem in items)
{
paramType = DbType.String;
esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem;
esDynamicQuerySerializable subQuery = null;
bool requiresParam = true;
bool needsStringParameter = false;
std.needsIntegerParameter = false;
if (comparisonData.IsParenthesis)
{
if (comparisonData.Parenthesis == esParenthesis.Open)
sql += "(";
else
sql += ")";
continue;
}
if (comparisonData.IsConjunction)
{
switch (comparisonData.Conjunction)
{
case esConjunction.And: sql += " AND "; break;
case esConjunction.Or: sql += " OR "; break;
case esConjunction.AndNot: sql += " AND NOT "; break;
case esConjunction.OrNot: sql += " OR NOT "; break;
}
continue;
}
Dictionary<string, EfzParameter> types = null;
if (comparisonData.Column.Query != null)
{
IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)iLocalQuery.Columns);
}
if (comparisonData.IsLiteral)
{
sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2);
continue;
}
if (comparisonData.ComparisonColumn.Name == null)
{
subQuery = comparisonData.Value as esDynamicQuerySerializable;
if (subQuery == null)
{
if (comparisonData.Column.Name != null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++pindex).ToString();
}
else
{
compareTo = Delimiters.Param + "Expr" + (++pindex).ToString();
}
}
else
{
// It's a sub query
compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(request, subQuery, cmd, ref pindex) + ") ";
requiresParam = false;
}
}
else
{
compareTo = GetColumnName(comparisonData.ComparisonColumn);
requiresParam = false;
}
switch (comparisonData.Operand)
{
case esComparisonOperand.Exists:
sql += " EXISTS" + compareTo;
break;
case esComparisonOperand.NotExists:
sql += " NOT EXISTS" + compareTo;
break;
//-----------------------------------------------------------
// Comparison operators, left side vs right side
//-----------------------------------------------------------
case esComparisonOperand.Equal:
if(comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " = " + compareTo;
else
sql += compareTo + " = " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.NotEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " <> " + compareTo;
else
sql += compareTo + " <> " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.GreaterThan:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " > " + compareTo;
else
sql += compareTo + " > " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.LessThan:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " < " + compareTo;
else
sql += compareTo + " < " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.LessThanOrEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " <= " + compareTo;
else
sql += compareTo + " <= " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.GreaterThanOrEqual:
if (comparisonData.ItemFirst)
sql += ApplyWhereSubOperations(comparisonData) + " >= " + compareTo;
else
sql += compareTo + " >= " + ApplyWhereSubOperations(comparisonData);
break;
case esComparisonOperand.Like:
string esc = comparisonData.LikeEscape.ToString();
if(String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(comparisonData) + " LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(comparisonData) + " LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case esComparisonOperand.NotLike:
esc = comparisonData.LikeEscape.ToString();
if (String.IsNullOrEmpty(esc) || esc == "\0")
{
sql += ApplyWhereSubOperations(comparisonData) + " NOT LIKE " + compareTo;
needsStringParameter = true;
}
else
{
sql += ApplyWhereSubOperations(comparisonData) + " NOT LIKE " + compareTo;
sql += " ESCAPE '" + esc + "'";
needsStringParameter = true;
}
break;
case esComparisonOperand.Contains:
sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", " + compareTo + ")";
paramType = DbType.AnsiStringFixedLength;
needsStringParameter = true;
break;
case esComparisonOperand.IsNull:
sql += ApplyWhereSubOperations(comparisonData) + " IS NULL";
requiresParam = false;
break;
case esComparisonOperand.IsNotNull:
sql += ApplyWhereSubOperations(comparisonData) + " IS NOT NULL";
requiresParam = false;
break;
case esComparisonOperand.In:
case esComparisonOperand.NotIn:
{
if (subQuery != null)
{
// They used a subquery for In or Not
sql += ApplyWhereSubOperations(comparisonData);
sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN";
sql += compareTo;
}
else
{
comma = String.Empty;
if (comparisonData.Operand == esComparisonOperand.In)
{
sql += ApplyWhereSubOperations(comparisonData) + " IN (";
}
else
{
sql += ApplyWhereSubOperations(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 esComparisonOperand.Between:
sql += ApplyWhereSubOperations(comparisonData) + " BETWEEN ";
sql += compareTo;
if (comparisonData.ComparisonColumn.Name == null)
{
cmd.Parameters.AddWithValue(compareTo, comparisonData.BetweenBegin);
}
if (comparisonData.ComparisonColumn2.Name == null)
{
IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal;
esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns;
compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++pindex).ToString();
sql += " AND " + compareTo;
cmd.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd);
}
else
{
sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose;
}
requiresParam = false;
break;
}
if (requiresParam)
{
EfzParameter p;
if (comparisonData.Column.Name != null)
{
p = types[comparisonData.Column.Name];
p = Cache.CloneParameter(p);
p.ParameterName = compareTo;
p.Value = comparisonData.Value;
if (needsStringParameter)
{
p.DbType = paramType;
}
else if (std.needsIntegerParameter)
{
p.DbType = DbType.Int32;
}
}
else
{
p = new EfzParameter(compareTo, comparisonData.Value);
}
cmd.Parameters.Add(p);
}
}
}
return sql;
}
static public EfzCommand BuildDynamicUpdateCommand(esDataRequest request, List<string> modifiedColumns)
{
string where = String.Empty;
string scomma = String.Empty;
string wcomma = String.Empty;
string defaults = String.Empty;
string defaultsWhere = String.Empty;
string defaultsComma = String.Empty;
string defaultsWhereComma = String.Empty;
string sql = "UPDATE " + CreateFullName(request) + " SET ";
PropertyCollection props = new PropertyCollection();
EfzParameter p = null;
Dictionary<string, EfzParameter> types = Cache.GetParameters(request);
EfzCommand cmd = new EfzCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
esColumnMetadataCollection cols = request.Columns;
foreach (esColumnMetadata col in cols)
{
bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name);
if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsEntitySpacesConcurrency))
{
p = CloneParameter(types[col.Name]);
cmd.Parameters.Add(p);
sql += scomma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
scomma = ", ";
}
else if (col.IsAutoIncrement)
{
// Nothing to do but leave this here
}
else if (col.IsConcurrency)
{
props["Timestamp"] = col.Name;
props["Source"] = request.ProviderMetadata.Source;
p = CloneParameter(types[col.Name]);
p.SourceVersion = DataRowVersion.Original;
p.Direction = ParameterDirection.InputOutput;
cmd.Parameters.Add(p);
where += wcomma;
where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
wcomma = " AND ";
}
else if (col.IsEntitySpacesConcurrency)
{
props["EntitySpacesConcurrency"] = col.Name;
p = CloneParameter(types[col.Name]);
p.SourceVersion = DataRowVersion.Original;
p.Direction = ParameterDirection.InputOutput;
cmd.Parameters.Add(p);
sql += scomma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " + 1";
scomma = ", ";
where += wcomma;
where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
wcomma = " AND ";
}
else if (col.IsComputed)
{
// Do nothing but leave this here
}
else if (cols.IsSpecialColumn(col))
{
// Do nothing but leave this here
}
else if (col.HasDefault)
{
// defaults += defaultsComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
// defaultsComma = ",";
}
if (col.IsInPrimaryKey)
{
p = CloneParameter(types[col.Name]);
p.SourceVersion = DataRowVersion.Original;
cmd.Parameters.Add(p);
where += wcomma;
where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
wcomma = " AND ";
defaultsWhere += defaultsWhereComma + col.Name;
defaultsWhereComma = ",";
}
}
#region Special Columns
if (cols.DateModified != null && cols.DateModified.IsServerSide)
{
sql += scomma;
sql += Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["DateModified.ServerSideText"];
scomma = ", ";
defaults += defaultsComma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose;
defaultsComma = ",";
}
if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
{
sql += scomma;
sql += Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"];
scomma = ", ";
defaults += defaultsComma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose;
defaultsComma = ",";
}
#endregion
if (defaults.Length > 0)
{
props["Defaults"] = defaults;
props["Where"] = defaultsWhere;
}
sql += " WHERE " + where + ";";
request.Properties = props;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return cmd;
}
static public EfzCommand BuildDynamicDeleteCommand(esDataRequest request, List<string> modifiedColumns)
{
Dictionary<string, EfzParameter> types = Cache.GetParameters(request);
EfzCommand cmd = new EfzCommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
string sql = "DELETE FROM " + CreateFullName(request) + " ";
string comma = String.Empty;
comma = String.Empty;
sql += " WHERE ";
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency || col.IsConcurrency)
{
EfzParameter p = types[col.Name];
cmd.Parameters.Add(CloneParameter(p));
sql += comma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = " AND ";
}
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return cmd;
}
