Пример #1
0
        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);
        }
Пример #2
0
 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));
     }
 }
Пример #3
0
        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);
        }
Пример #4
0
        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));
            }
        }
Пример #5
0
        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);
        }
Пример #6
0
        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);
        }
Пример #7
0
        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);
        }
Пример #8
0
        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);
        }
Пример #9
0
        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);
        }
Пример #10
0
        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;
                    }
                }
            }
        }
Пример #11
0
 protected override void PurgeImpl()
 {
     using (EfzConnection connection = new EfzConnection(this.connString))
     {
         EfzCommand command = connection.CreateCommand();
         command.CommandText = EfzQueuePersistence <T> .purgeSql;
         connection.Open();
         command.ExecuteNonQuery();
     }
 }
Пример #12
0
 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();
 }
Пример #13
0
 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();
     }
 }
Пример #14
0
 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();
     }
 }
Пример #15
0
 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();
     }
 }
Пример #16
0
 //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 { }
 }
Пример #17
0
        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;
        }
Пример #20
0
 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();
     }
 }
Пример #21
0
        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);
        }
Пример #22
0
        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);
        }
Пример #23
0
        /// <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);
        }
Пример #24
0
        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;
        }
Пример #26
0
 //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); }
 }
Пример #27
0
        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);
            }
        }
Пример #28
0
        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);
        }
Пример #29
0
        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;
                        }
                    }
                }
            }
        }
Пример #30
0
        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);
        }
Пример #31
0
        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;
        }
Пример #33
0
 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;
        }
Пример #36
0
        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);
        }
Пример #39
0
 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;
        }
Пример #49
0
        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;
        }
Пример #55
0
 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;
        }