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; }
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 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; }
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; }