示例#1
0
        /// <summary>
        /// Called by the DataProviders to invoke the OnError event handler
        /// </summary>
        /// <param name="packet">The esEntityPacket in error</param>
        /// <param name="ex">The exception that was thrown</param>
        public void FireOnError(tgEntitySavePacket packet, string error)
        {
            OnErrorHandler handler = OnError;

            if (handler != null)
            {
                handler(packet, error);
            }
        }
示例#2
0
        public static SqlCeCommand BuildDynamicInsertCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            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();
            SqlCeParameter p = null;

            Dictionary<string, SqlCeParameter> types = Cache.GetParameters(request);

            SqlCeCommand cmd = new SqlCeCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            tgColumnMetadataCollection cols = request.Columns;
            foreach (tgColumnMetadata col in cols)
            {
                bool isModified = packet.ModifiedColumns == null ? false : packet.ModifiedColumns.Contains(col.Name);

                if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsTiraggoConcurrency))
                {
                    p = cmd.Parameters.Add(CloneParameter(types[col.Name]));

                    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;
                }
                else if (col.IsConcurrency)
                {
                    props["Timestamp"] = col.Name;
                    props["Source"] = request.ProviderMetadata.Source;
                }
                else if (col.IsTiraggoConcurrency)
                {
                    props["EntitySpacesConcurrency"] = col.Name;

                    into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    values += comma + "1";
                    comma = ", ";

                    p = CloneParameter(types[col.Name]);
                    p.Value = 1;
                    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)
                {
                    into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    values += comma + "DEFAULT";
                    comma = ",";

                    defaults += defaultComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    defaultComma = ",";
                }

                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)
            {
                foreach (tgColumnMetadata col in request.Columns)
                {
                    if (!col.IsAutoIncrement && !col.IsComputed && !col.IsConcurrency)
                    {
                        into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                        values += comma + "DEFAULT";
                        comma = ",";
                    }
                }
            }

            if (into.Length != 0)
            {
                sql += "(" + into + ") VALUES (" + values + ")";
            }

            request.Properties = props;

            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd;
        }
示例#3
0
        public static void PopulateStoredProcParameters(OracleCommand cmd, tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, OracleParameter> types = Cache.GetParameters(request);

            OracleParameter p;

            foreach (tgColumnMetadata col in request.Columns)
            {
                p = types[col.Name];
                p = CloneParameter(p);

                p.ParameterName = p.ParameterName.Replace(":", "p");

                if (packet.CurrentValues.ContainsKey(col.Name))
                {
                    p.Value = packet.CurrentValues[col.Name];
                }
                else
                {
                    p.Value = DBNull.Value;
                }

                if (p.OracleType == OracleType.Timestamp)
                {
                    p.Direction = ParameterDirection.InputOutput;
                }
                cmd.Parameters.Add(p);
            }
        }
示例#4
0
        public static OracleCommand BuildStoredProcUpdateCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, OracleParameter> types = Cache.GetParameters(request);

            OracleCommand cmd = new OracleCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spUpdate);

            PopulateStoredProcParameters(cmd, request, packet);

            tgColumnMetadataCollection cols = request.Columns;
            OracleParameter p = null;

            foreach (tgColumnMetadata col in request.Columns)
            {
                if (col.IsComputed || col.IsTiraggoConcurrency)
                {
                    p = cmd.Parameters["p" + (col.Name).Replace(" ", String.Empty)];
                    p.Direction = ParameterDirection.InputOutput;
                }
            }

            if (cols.DateModified != null && cols.DateModified.IsServerSide)
            {
                p = cmd.Parameters[types[cols.DateModified.ColumnName].ParameterName];
                p = cmd.Parameters[p.ParameterName];
                p.Value = null;
                p.Direction = ParameterDirection.Output;
            }

            if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
            {
                p = cmd.Parameters[types[cols.ModifiedBy.ColumnName].ParameterName];
                p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
                p = cmd.Parameters[p.ParameterName];
                p.Value = null;
                p.Direction = ParameterDirection.Output;
            }

            return cmd;
        }
示例#5
0
        /// <summary>
        /// Called by the DataProviders to invoke the OnError event handler
        /// </summary>
        /// <param name="packet">The esEntityPacket in error</param>
        /// <param name="ex">The exception that was thrown</param>
        public void FireOnError(tgEntitySavePacket packet, string error)
        {
            OnErrorHandler handler = OnError;

            if (handler != null)
            {
                handler(packet, error);
            }
        }
示例#6
0
        public static NpgsqlCommand BuildStoredProcUpdateCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request);

            NpgsqlCommand cmd = new NpgsqlCommand();
            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, packet);

            foreach (tgColumnMetadata col in request.Columns)
            {
                if (col.IsComputed)
                {
                    NpgsqlParameter p = types[col.Name];
                    p = cmd.Parameters[p.ParameterName];
                    p.Direction = ParameterDirection.InputOutput;
                }
            }

            return cmd;
        }
示例#7
0
        public static NpgsqlCommand BuildStoredProcDeleteCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request);

            NpgsqlCommand cmd = new NpgsqlCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spDelete + Delimiters.StoredProcNameClose;

            NpgsqlParameter p;

            foreach (tgColumnMetadata col in request.Columns)
            {
                if (col.IsInPrimaryKey)
                {
                    p = types[col.Name];
                    p = CloneParameter(p);
                    p.Value = packet.OriginalValues[col.Name];
                    cmd.Parameters.Add(p);
                }
                else if (col.IsConcurrency || col.IsEntitySpacesConcurrency)
                {
                    p = types[col.Name];
                    p = CloneParameter(p);
                    p.Value = packet.OriginalValues[col.Name];
                    cmd.Parameters.Add(p);
                }
            }

            return cmd;
        }
示例#8
0
        public static NpgsqlCommand BuildDynamicInsertCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            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 autoInc = String.Empty;

            NpgsqlParameter p = null;

            Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request);

            NpgsqlCommand cmd = new NpgsqlCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            tgColumnMetadataCollection cols = request.Columns;
            foreach (tgColumnMetadata col in cols)
            {
                bool isModified = packet.ModifiedColumns == null ? false : packet.ModifiedColumns.Contains(col.Name);

                if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsEntitySpacesConcurrency))
                {
                    p = cmd.Parameters.Add(CloneParameter(types[col.Name]));

                    object value = packet.CurrentValues[col.Name];
                    p.Value = value != null ? value : DBNull.Value;

                    into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    values += comma + p.ParameterName;
                    comma = ", ";
                }
                else if (col.IsAutoIncrement && request.ProviderMetadata.ContainsKey("AutoKeyText"))
                {
                    string sequence = request.ProviderMetadata["AutoKeyText"].Replace("nextval", "currval");

                    if (sequence != null && sequence.Length > 0)
                    {
                        // Our identity column ...
                        p = cmd.Parameters.Add(CloneParameter(types[col.Name]));
                        p.Direction = ParameterDirection.Output;

                        autoInc += " SELECT * FROM " + sequence + " as \"" + col.Name + "\"";
                    }

                    p = CloneParameter(types[col.Name]);
                    p.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(p);
                }
                else if (col.IsConcurrency)
                {
                    // These columns have defaults and they weren't supplied with values, so let's
                    // return them
                    p = cmd.Parameters.Add(CloneParameter(types[col.Name]));
                    p.Direction = ParameterDirection.InputOutput;

                    defaults += defaultComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    defaultComma = ", ";

                    if (col.CharacterMaxLength > 0)
                    {
                        p.Size = (int)col.CharacterMaxLength;
                    }
                }
                else if (col.IsEntitySpacesConcurrency)
                {
                    p = cmd.Parameters.Add(CloneParameter(types[col.Name]));
                    p.Direction = ParameterDirection.Output;

                    into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    values += comma + "1";
                    comma = ", ";

                    p.Value = 1; // Seems to work, We'll take it ...

                }
                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)
                {
                    // These columns have defaults and they weren't supplied with values, so let's
                    // return them
                    p = cmd.Parameters.Add(CloneParameter(types[col.Name]));
                    p.Direction = ParameterDirection.InputOutput;

                    defaults += defaultComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    defaultComma = ",";

                    if (col.CharacterMaxLength > 0)
                    {
                        p.Size = (int)col.CharacterMaxLength;
                    }
                }

                if (col.IsInPrimaryKey)
                {
                    p = types[col.Name];

                    if (where.Length > 0) where += " AND ";
                    where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;

                    if (!cmd.Parameters.Contains(p.ParameterName))
                    {
                        p = CloneParameter(p);
                        p.Direction = ParameterDirection.Output;
                        cmd.Parameters.Add(p);
                    }
                }
            }

            #region Special Column Logic
            if (cols.DateAdded != null && cols.DateAdded.IsServerSide)
            {
                p = CloneParameter(types[cols.DateAdded.ColumnName]);
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                into += comma + Delimiters.ColumnOpen + cols.DateAdded.ColumnName + Delimiters.ColumnClose;
                values += comma + request.ProviderMetadata["DateAdded.ServerSideText"];
                comma = ", ";

                defaults += defaultComma + cols.DateAdded.ColumnName;
                defaultComma = ",";
            }

            if (cols.DateModified != null && cols.DateModified.IsServerSide)
            {
                p = CloneParameter(types[cols.DateModified.ColumnName]);
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                into += comma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose;
                values += comma + request.ProviderMetadata["DateModified.ServerSideText"];
                comma = ", ";

                defaults += defaultComma + cols.DateModified.ColumnName;
                defaultComma = ",";
            }

            if (cols.AddedBy != null && cols.AddedBy.IsServerSide)
            {
                p = CloneParameter(types[cols.AddedBy.ColumnName]);
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                into += comma + Delimiters.ColumnOpen + cols.AddedBy.ColumnName + Delimiters.ColumnClose;
                values += comma + request.ProviderMetadata["AddedBy.ServerSideText"];
                comma = ", ";

                defaults += defaultComma + cols.AddedBy.ColumnName;
                defaultComma = ",";

                tgColumnMetadata col = request.Columns[cols.ModifiedBy.ColumnName];

                if (col.CharacterMaxLength > 0)
                {
                    p.Size = (int)col.CharacterMaxLength;
                }
            }

            if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
            {
                p = CloneParameter(types[cols.ModifiedBy.ColumnName]);
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                into += comma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose;
                values += comma + request.ProviderMetadata["ModifiedBy.ServerSideText"];
                comma = ", ";

                defaults += defaultComma + cols.ModifiedBy.ColumnName;
                defaultComma = ",";

                tgColumnMetadata col = request.Columns[cols.ModifiedBy.ColumnName];

                if (col.CharacterMaxLength > 0)
                {
                    p.Size = (int)col.CharacterMaxLength;
                }
            }
            #endregion

            string fullName = CreateFullName(request);

            sql += " INSERT INTO " + fullName;

            if (into.Length != 0)
            {
                sql += " (" + into + ") VALUES (" + values + ");";
            }
            else
            {
                sql += " DEFAULT VALUES;";
            }

            sql += autoInc;

            if (defaults.Length > 0)
            {
                sql += " SELECT " + defaults + " FROM " + fullName + " WHERE (" + where + ")";
            }

            cmd.CommandText = sql + String.Empty;
            cmd.CommandType = CommandType.Text;
            return cmd;
        }
示例#9
0
            public esTraceArguments(tgDataRequest request, IDbCommand cmd, tgEntitySavePacket packet, string action, string callStack)
            {
                PacketOrder = Interlocked.Increment(ref esTraceArguments.packetOrder);

                this.command = cmd;

                TraceChannel = DataProvider.sTraceChannel;
                Syntax = "SQLITE";
                Request = request;
                ThreadId = Thread.CurrentThread.ManagedThreadId;
                Action = action;
                CallStack = callStack;
                SqlCommand = cmd;
                ApplicationName = System.IO.Path.GetFileName(System.Reflection.Assembly.GetExecutingAssembly().Location);

                IDataParameterCollection parameters = cmd.Parameters;

                if (parameters.Count > 0)
                {
                    Parameters = new List<ITraceParameter>(parameters.Count);

                    for (int i = 0; i < parameters.Count; i++)
                    {
                        SQLiteParameter param = parameters[i] as SQLiteParameter;

                        esTraceParameter p = new esTraceParameter()
                        {
                            Name = param.ParameterName,
                            Direction = param.Direction.ToString(),
                            ParamType = param.DbType.ToString().ToUpper(),
                            BeforeValue = param.Value != null && param.Value != DBNull.Value ? Convert.ToString(param.Value) : "null"
                        };

                        try
                        {
                            // Let's make it look like we're using parameters for the profiler
                            if (param.Value == null || param.Value == DBNull.Value)
                            {
                                if (param.SourceVersion == DataRowVersion.Current)
                                {
                                    object o = packet.CurrentValues[param.SourceColumn];
                                    if (o != null && o != DBNull.Value)
                                    {
                                        p.BeforeValue = Convert.ToString(o);
                                    }
                                }
                                else if (param.SourceVersion == DataRowVersion.Original)
                                {
                                    object o = packet.OriginalValues[param.SourceColumn];
                                    if (o != null && o != DBNull.Value)
                                    {
                                        p.BeforeValue = Convert.ToString(o);
                                    }
                                }
                            }
                        }
                        catch { }

                        this.Parameters.Add(p);
                    }
                }

                stopwatch = Stopwatch.StartNew();
            }
示例#10
0
        private static void SetOriginalValues(tgDataRequest request, tgEntitySavePacket packet, DataRow row, bool primaryKeysAndConcurrencyOnly)
        {
            foreach (tgColumnMetadata col in request.Columns)
            {
                if (primaryKeysAndConcurrencyOnly &&
                    (!col.IsInPrimaryKey && !col.IsConcurrency && !col.IsTiraggoConcurrency)) continue;

                string columnName = col.Name;

                if (packet.OriginalValues.ContainsKey(columnName))
                {
                    row[columnName] = packet.OriginalValues[columnName];
                }
            }
        }
示例#11
0
 private static void SetModifiedValues(tgDataRequest request, tgEntitySavePacket packet, DataRow row)
 {
     foreach (string column in packet.ModifiedColumns)
     {
         if (request.Columns.FindByColumnName(column) != null)
         {
             row[column] = packet.CurrentValues[column];
         }
     }
 }
示例#12
0
        public static SqlCommand BuildStoredProcInsertCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, SqlParameter> types = Cache.GetParameters(request);

            SqlCommand cmd = new SqlCommand();
            if(request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spInsert);

            PopulateStoredProcParameters(cmd, request, packet);

            tgColumnMetadataCollection cols = request.Columns;

            foreach (tgColumnMetadata col in cols)
            {
                if (col.HasDefault &&
                    (col.Default.ToLower().Contains("newid") || col.Default.ToLower().Contains("newsequentialid")))
                {
                    // They could pre-assign this even though it has a default
                    SqlParameter p = types[col.Name];
                    p = cmd.Parameters[p.ParameterName];

                    if (packet.ModifiedColumns.Contains(col.Name))
                    {
                        p.Direction = ParameterDirection.InputOutput;
                    }
                    else
                    {
                        p.Direction = ParameterDirection.Output;
                    }
                }
                else if (col.IsComputed || col.IsAutoIncrement || col.IsTiraggoConcurrency)
                {
                    SqlParameter p = types[col.Name];
                    p = cmd.Parameters[p.ParameterName];
                    p.Direction = ParameterDirection.Output;
                }
            }

            if (cols.DateAdded != null && cols.DateAdded.IsServerSide &&
                cols.FindByColumnName(cols.DateAdded.ColumnName) != null)
            {
                SqlParameter p = cmd.Parameters[types[cols.DateAdded.ColumnName].ParameterName];
                p = cmd.Parameters[p.ParameterName];
                p.Direction = ParameterDirection.Output;
            }

            if (cols.DateModified != null && cols.DateModified.IsServerSide &&
                cols.FindByColumnName(cols.DateModified.ColumnName) != null)
            {
                SqlParameter p = cmd.Parameters[types[cols.DateModified.ColumnName].ParameterName];
                p = cmd.Parameters[p.ParameterName];
                p.Direction = ParameterDirection.Output;
            }

            if (cols.AddedBy != null && cols.AddedBy.IsServerSide &&
                cols.FindByColumnName(cols.AddedBy.ColumnName) != null)
            {
                SqlParameter p = cmd.Parameters[types[cols.AddedBy.ColumnName].ParameterName];
                p.Size = (int)cols.FindByColumnName(cols.AddedBy.ColumnName).CharacterMaxLength;
                p = cmd.Parameters[p.ParameterName];
                p.Direction = ParameterDirection.Output;
            }

            if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide &&
                cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null)
            {
                SqlParameter p = cmd.Parameters[types[cols.ModifiedBy.ColumnName].ParameterName];
                p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
                p = cmd.Parameters[p.ParameterName];
                p.Direction = ParameterDirection.Output;
            }

            return cmd;
        }
示例#13
0
        public static SqlCommand BuildDynamicUpdateCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, SqlParameter> types = Cache.GetParameters(request);

            SqlCommand cmd = new SqlCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            string set = string.Empty;
            string sql = "SET NOCOUNT OFF ";
            sql += "UPDATE " + CreateFullName(request) + " SET ";

            string where = String.Empty;
            string conncur = String.Empty;
            string computed = String.Empty;
            string comma = String.Empty;
            string and = String.Empty;
            string prolog = String.Empty;

            SqlParameter p = null;

            List<string> modifiedColumns = packet.ModifiedColumns;

            foreach (string colName in modifiedColumns)
            {
                tgColumnMetadata col = request.Columns[colName];

                if (col == null) continue;

                if (!col.IsInPrimaryKey && !col.IsComputed)
                {
                    p = CloneParameter(types[colName]);
                    p = cmd.Parameters.Add(p);

                    object value = packet.CurrentValues[colName];
                    p.Value = value != null ? value : DBNull.Value;

                    sql += comma;
                    sql += Delimiters.ColumnOpen + colName + Delimiters.ColumnClose + " = " + p.ParameterName;
                    comma = ", ";
                }
            }

            foreach (tgColumnMetadata col in request.Columns)
            {
                if (col.IsInPrimaryKey)
                {
                    p = CloneParameter(types[col.Name]);
                    p.Value = packet.OriginalValues[col.Name];
                    cmd.Parameters.Add(p);

                    where += and;
                    where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
                    and = " AND ";
                }
                else if (col.IsConcurrency)
                {
                    p = CloneParameter(types[col.Name]);
                    p.Value = packet.OriginalValues[col.Name];
                    p.Direction = ParameterDirection.InputOutput;
                    cmd.Parameters.Add(p);

                    if (request.DatabaseVersion == "2005" || request.DatabaseVersion == "2008")
                        conncur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
                    else
                        conncur += "TSEQUAL(" + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + "," + p.ParameterName + ")";

                    if (computed.Length > 0) computed += ", ";
                    computed += " " + p.ParameterName + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                }
                else if (col.IsComputed && !col.IsAutoIncrement)
                {
                    if (request.SelectedColumns != null && request.SelectedColumns.ContainsKey(col.Name))
                    {
                        p = CloneParameter(types[col.Name]);
                        p.Direction = ParameterDirection.Output;
                        if (col.CharacterMaxLength > 0)
                        {
                            p.Size = (int)col.CharacterMaxLength;
                        }
                        cmd.Parameters.Add(p);

                        if (computed.Length > 0) computed += ", ";
                        computed += " " + p.ParameterName + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    }
                }
                else if (col.IsTiraggoConcurrency)
                {
                    if (packet.OriginalValues != null && packet.OriginalValues.ContainsKey(col.Name))
                    {
                        p = CloneParameter(types[col.Name]);
                        p.Direction = ParameterDirection.InputOutput;
                        p.Value = packet.OriginalValues[col.Name];
                        cmd.Parameters.Add(p);

                        sql += comma.Length > 0 ? ", " : string.Empty;
                        sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " +
                            Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " + 1";

                        conncur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;

                        prolog += " SET " + p.ParameterName + " = " + p.ParameterName + " + 1";
                    }
                }
            }

            tgColumnMetadataCollection cols = request.Columns;

            if (cols.DateModified != null && cols.DateModified.IsServerSide &&
                cols.FindByColumnName(cols.DateModified.ColumnName) != null)
            {
                p = CloneParameter(types[cols.DateModified.ColumnName]);
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                sql += comma;
                sql += Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose + " = " + p.ParameterName;
                comma = ", ";

                set += " SET " + p.ParameterName + " = " + request.ProviderMetadata["DateModified.ServerSideText"] + ";";
            }

            if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide &&
                cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null)
            {
                p = CloneParameter(types[cols.ModifiedBy.ColumnName]);
                p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                sql += comma;
                sql += Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose + " = " + p.ParameterName;
                comma = ", ";

                set += " SET " + p.ParameterName + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"] + ";";
            }

            sql = set + sql + " WHERE (" + where + ")";
            if (conncur.Length > 0)
            {
                sql += " AND " + conncur;
            }

            if (computed.Length > 0)
            {
                sql += " SELECT " + computed + " FROM " + CreateFullName(request) + " WHERE (" + where + ")";
            }

            if (prolog.Length > 0)
            {
                sql += prolog;
            }

            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd;
        }
示例#14
0
        public static SqlCommand BuildDynamicInsertCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            string into = String.Empty;
            string values = String.Empty;
            string computed = String.Empty;
            string computedComma = String.Empty;
            string autoInc = String.Empty;
            string comma = String.Empty;
            string where = String.Empty;

            // newsequentialid variables
            int seqCount = 0;
            string seqDeclare = " DECLARE @table_ids TABLE (";
            string seqOutput = " OUTPUT ";
            string seqSelect = " SELECT ";

            List<string> modifiedColumns = packet.ModifiedColumns;

            Dictionary<string, SqlParameter> types = Cache.GetParameters(request);

            SqlCommand cmd = new SqlCommand();
            SqlParameter p = null;
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            string sql = "SET NOCOUNT OFF";

            foreach (tgColumnMetadata col in request.Columns)
            {
                string colName = col.Name;

                if (request.SelectedColumns != null && !request.SelectedColumns.ContainsKey(colName)) continue;

                bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name);

                if (isModified && !col.IsComputed && !col.IsConcurrency && !col.IsAutoIncrement)
                {
                    p = types[colName];
                    p = cmd.Parameters.Add(CloneParameter(p));

                    object value = packet.CurrentValues[colName];
                    p.Value = value != null ? value : DBNull.Value;

                    CreateInsertSQLSnippet(colName, p, ref into, ref values, ref comma);
                }
                else
                {
                    bool needOutputParam = false;
                    bool needsFetchedAfterSave = false;
                    SqlParameter clone = null;

                    if (col.HasDefault)
                    {
                        p = types[colName];

                        if (col.esType == tgSystemType.Guid)
                        {
                            if (col.Default.ToLower().Contains("newid"))
                            {
                                // Special logic for newid()'s that weren't supplied with a value, they
                                // go into the SELECT INTO as well
                                sql += " SET " + p.ParameterName + " = NEWID(); ";
                                CreateInsertSQLSnippet(colName, p, ref into, ref values, ref comma);
                                needOutputParam = true;
                            }
                            else if (col.Default.ToLower().Contains("newsequentialid"))
                            {
                                if (seqCount > 0)
                                {
                                    seqDeclare += ", ";
                                    seqOutput += ", ";
                                    seqSelect += ", ";
                                }
                                seqCount++;

                                seqDeclare += col.Name + "  uniqueidentifier";
                                seqOutput += "INSERTED." + col.Name;
                                seqSelect += Delimiters.Param + col.PropertyName + "=" + col.Name;

                                needOutputParam = true;
                            }
                        }
                        else
                        {
                            // 11/15/2009 Let's return all default values
                            needOutputParam = true;
                            needsFetchedAfterSave = true;
                        }
                    }
                    else if (col.IsTiraggoConcurrency)
                    {
                        p = types[colName];
                        sql += " SET " + p.ParameterName + " = 1; ";

                        into += comma;
                        into += Delimiters.ColumnOpen + colName + Delimiters.ColumnClose;
                        values += comma;
                        values += "1";
                        comma = ", ";

                        needOutputParam = true;
                    }
                    else if (col.IsAutoIncrement)
                    {
                        p = types[colName];
                        autoInc += " SELECT " + p.ParameterName + " = SCOPE_IDENTITY() ";
                        needOutputParam = true;
                    }
                    else if (col.IsComputed || col.IsConcurrency)
                    {
                        p = types[colName];
                        needOutputParam = true;
                        needsFetchedAfterSave = true;
                    }

                    if (needOutputParam)
                    {
                        clone = CloneParameter(p);
                        clone.Direction = ParameterDirection.Output;
                        cmd.Parameters.Add(clone);
                    }

                    if (needsFetchedAfterSave)
                    {
                        computed += computedComma;
                        computed += p.ParameterName + " = " + Delimiters.ColumnOpen + colName + Delimiters.ColumnClose;
                        computedComma = ", ";

                        if (col.CharacterMaxLength > 0)
                        {
                            clone.Size = (int)col.CharacterMaxLength;
                        }
                    }
                }
            }

            tgColumnMetadataCollection cols = request.Columns;

            #region Special Column Logic
            if (cols.DateAdded != null && cols.DateAdded.IsServerSide &&
                cols.FindByColumnName(cols.DateAdded.ColumnName) != null)
            {
                p = CloneParameter(types[cols.DateAdded.ColumnName]);
                sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["DateAdded.ServerSideText"] + ";";
                CreateInsertSQLSnippet(cols.DateAdded.ColumnName, p, ref into, ref values, ref comma);

                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);
            }

            if (cols.DateModified != null && cols.DateModified.IsServerSide &&
                cols.FindByColumnName(cols.DateModified.ColumnName) != null)
            {
                p = CloneParameter(types[cols.DateModified.ColumnName]);
                sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["DateModified.ServerSideText"] + ";";
                CreateInsertSQLSnippet(cols.DateModified.ColumnName, p, ref into, ref values, ref comma);

                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);
            }

            if (cols.AddedBy != null && cols.AddedBy.IsServerSide &&
                cols.FindByColumnName(cols.AddedBy.ColumnName) != null)
            {
                p = CloneParameter(types[cols.AddedBy.ColumnName]);
                p.Size = (int)cols.FindByColumnName(cols.AddedBy.ColumnName).CharacterMaxLength;
                sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["AddedBy.ServerSideText"] + ";";

                CreateInsertSQLSnippet(cols.AddedBy.ColumnName, p, ref into, ref values, ref comma);

                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);
            }

            if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide &&
                cols.FindByColumnName(cols.ModifiedBy.ColumnName) != null)
            {
                p = CloneParameter(types[cols.ModifiedBy.ColumnName]);
                p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
                sql += " SET " + p.ParameterName + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"] + ";";

                CreateInsertSQLSnippet(cols.ModifiedBy.ColumnName, p, ref into, ref values, ref comma);

                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);
            }
            #endregion

            seqDeclare += ")";
            seqOutput += " INTO @table_ids";
            seqSelect += " FROM @table_ids";

            if (computed.Length > 0)
            {
                foreach (tgColumnMetadata col in request.Columns)
                {
                    if (col.IsInPrimaryKey)
                    {
                        // We need the were clause if there are defaults to bring back
                        p = types[col.Name];

                        if (where.Length > 0) where += " AND ";
                        where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;

                        if (!cmd.Parameters.Contains(p.ParameterName))
                        {
                            p = CloneParameter(p);
                            p.Direction = ParameterDirection.Output;
                            cmd.Parameters.Add(p);
                        }
                    }
                }
            }

            string fullName = CreateFullName(request);

            if (seqCount > 0)
            {
                sql += seqDeclare;
            }

            sql += " INSERT INTO " + fullName;

            if (into.Length != 0 && seqCount > 0)
            {
                sql += "(" + into + ") " + seqOutput + " VALUES (" + values + ")";
            }
            else if (into.Length != 0)
            {
                sql += "(" + into + ") VALUES (" + values + ")";
            }
            else
            {
                sql += "DEFAULT VALUES";
            }

            sql += autoInc;

            if (seqCount > 0)
            {
                sql += seqSelect;
            }

            if (computed.Length > 0)
            {
                sql += " SELECT " + computed + " FROM " + fullName + " WHERE (" + where + ")";
            }

            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd;
        }
示例#15
0
        public static SqlCommand BuildDynamicDeleteCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, SqlParameter> types = Cache.GetParameters(request);

            SqlCommand cmd = new SqlCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            string sql = "SET NOCOUNT OFF; ";
            sql += "DELETE FROM " + CreateFullName(request) + " ";

            string comma = String.Empty;
            string concur = String.Empty;
            comma = String.Empty;
            sql += " WHERE ";
            foreach (tgColumnMetadata col in request.Columns)
            {
                if (col.IsInPrimaryKey)
                {
                    SqlParameter p = CloneParameter(types[col.Name]);
                    p.Value = packet.OriginalValues[col.Name];
                    cmd.Parameters.Add(p);

                    sql += comma;
                    sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
                    comma = " AND ";
                }
                else if (col.IsConcurrency || col.IsTiraggoConcurrency)
                {
                    SqlParameter p = CloneParameter(types[col.Name]);
                    p.Value = packet.OriginalValues[col.Name];
                    cmd.Parameters.Add(p);

                    if (request.DatabaseVersion == "2005" || request.DatabaseVersion == "2008" || col.IsTiraggoConcurrency)
                        concur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
                    else
                        concur += "TSEQUAL(" + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + "," + p.ParameterName + ")";
                }
            }

            if (concur.Length > 0)
            {
                sql += " AND " + concur;
            }

            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd;
        }
示例#16
0
        public static SqlCeCommand BuildDynamicUpdateCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            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();
            SqlCeParameter p = null;

            Dictionary<string, SqlCeParameter> types = Cache.GetParameters(request);

            SqlCeCommand cmd = new SqlCeCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            tgColumnMetadataCollection cols = request.Columns;
            foreach (tgColumnMetadata col in cols)
            {
                bool isModified = packet.ModifiedColumns == null ? false : packet.ModifiedColumns.Contains(col.Name);

                if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsTiraggoConcurrency))
                {
                    p = cmd.Parameters.Add(CloneParameter(types[col.Name]));

                    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)
                {
                    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 ";
                }
                else if (col.IsTiraggoConcurrency)
                {
                    props["EntitySpacesConcurrency"] = col.Name;

                    p = CloneParameter(types[col.Name]);
                    p.SourceVersion = DataRowVersion.Original;
                    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;
        }
示例#17
0
        public static NpgsqlCommand BuildDynamicDeleteCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request);

            NpgsqlCommand cmd = new NpgsqlCommand();
            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 (tgColumnMetadata col in request.Columns)
            {
                if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency)
                {
                    NpgsqlParameter p = types[col.Name];
                    p = cmd.Parameters.Add(CloneParameter(p));
                    p.Value = packet.OriginalValues[col.Name];

                    sql += comma;
                    sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
                    comma = " AND ";
                }
            }

            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd;
        }
示例#18
0
        public static OracleCommand BuildDynamicDeleteCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, OracleParameter> types = Cache.GetParameters(request);

            OracleCommand cmd = new OracleCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            string sql = String.Empty;
            bool hasConcurrency = false;

            sql += "BEGIN ";

            sql += "DELETE FROM " + CreateFullName(request) + " ";

            string where = String.Empty;
            string comma = String.Empty;

            comma = String.Empty;
            foreach (tgColumnMetadata col in request.Columns)
            {
                if (col.IsInPrimaryKey || col.IsTiraggoConcurrency)
                {
                    OracleParameter p = CloneParameter(types[col.Name]);
                    p.Value = packet.OriginalValues[col.Name];
                    cmd.Parameters.Add(p);

                    where += comma;
                    where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
                    comma = " AND ";

                    if (col.IsTiraggoConcurrency) hasConcurrency = true;
                }
            }

            sql += " WHERE (" + where + "); ";

            if (hasConcurrency)
            {
                sql += "IF SQL%ROWCOUNT = 0 THEN ";
                sql += "Raise_application_error(-20101, 'NO RECORDS WERE DELETED'); END IF;";
            }

            sql += " END;";

            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd;
        }
示例#19
0
        public static NpgsqlCommand BuildDynamicUpdateCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            string where = String.Empty;
            string conncur = String.Empty;
            string scomma = String.Empty;
            string defaults = String.Empty;
            string defaultsComma = String.Empty;
            string and = String.Empty;

            string sql = "UPDATE " + CreateFullName(request) + " SET ";

            PropertyCollection props = new PropertyCollection();
            NpgsqlParameter p = null;

            Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request);

            NpgsqlCommand cmd = new NpgsqlCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            tgColumnMetadataCollection cols = request.Columns;
            foreach (tgColumnMetadata col in cols)
            {
                bool isModified = packet.ModifiedColumns == null ? false : packet.ModifiedColumns.Contains(col.Name);

                if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsEntitySpacesConcurrency))
                {
                    p = cmd.Parameters.Add(CloneParameter(types[col.Name]));

                    object value = packet.CurrentValues[col.Name];
                    p.Value = value != null ? value : DBNull.Value;

                    sql += scomma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
                    scomma = ", ";
                }
                else if (col.IsAutoIncrement)
                {
                    // Nothing to do but leave this here
                }
                else if (col.IsConcurrency)
                {
                    p = CloneParameter(types[col.Name]);
                    p.SourceVersion = DataRowVersion.Original;
                    p.Direction = ParameterDirection.InputOutput;
                    cmd.Parameters.Add(p);

                    conncur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
                }
                else if (col.IsEntitySpacesConcurrency)
                {
                    p = CloneParameter(types[col.Name]);
                    p.Value = packet.OriginalValues[col.Name];
                    p.Direction = ParameterDirection.InputOutput;
                    cmd.Parameters.Add(p);

                    sql += scomma;
                    sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName + " + 1";

                    conncur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;

                    defaults += defaultsComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    defaultsComma = ",";
                }
                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.Value = packet.OriginalValues[col.Name];
                    cmd.Parameters.Add(p);

                    where += and + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
                    and = " AND ";
                }
            }

            #region Special Column Logic
            if (cols.DateModified != null && cols.DateModified.IsServerSide)
            {
                p = CloneParameter(types[cols.DateModified.ColumnName]);
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                sql += scomma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["DateModified.ServerSideText"];
                scomma = ", ";

                defaults += defaultsComma + cols.DateModified.ColumnName;
                defaultsComma = ",";
            }

            if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
            {
                p = CloneParameter(types[cols.ModifiedBy.ColumnName]);
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                sql += scomma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"];
                scomma = ", ";

                defaults += defaultsComma + cols.ModifiedBy.ColumnName;
                defaultsComma = ",";

                tgColumnMetadata col = request.Columns[cols.ModifiedBy.ColumnName];

                if (col.CharacterMaxLength > 0)
                {
                    p.Size = (int)col.CharacterMaxLength;
                }
            }
            #endregion

            sql += " WHERE " + where + "";
            if (conncur.Length > 0)
            {
                sql += " AND " + conncur;
            }

            if (defaults.Length > 0)
            {
                sql += "; SELECT " + defaults + " FROM " + CreateFullName(request) + " WHERE (" + where + ")";
            }

            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd;
        }
示例#20
0
        public static OracleCommand BuildDynamicInsertCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, OracleParameter> types = Cache.GetParameters(request);

            OracleCommand cmd = new OracleCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            string comma = String.Empty;
            string into = String.Empty;
            string values = String.Empty;
            string computedColumns = String.Empty;
            string computedParameters = String.Empty;
            string computedComma = String.Empty;

            List<string> modifiedColumns = packet.ModifiedColumns;

            string sql = "BEGIN ";

            OracleParameter p = null;

            foreach (tgColumnMetadata col in request.Columns)
            {
                if (col.IsAutoIncrement)
                {
                    p = CloneParameter(types[col.Name]);
                    p.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(p);

                    string AutoKeyText = request.ProviderMetadata["AutoKeyText"];

                    // SELECT EMPLOYEE_ID.NextVal INTO p_EMPLOYEE_ID FROM DUAL;
                    sql += "SELECT \"" + AutoKeyText + "\".NextVal INTO " + p.ParameterName + " FROM DUAL; ";

                    into += comma;
                    into += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    values += comma;
                    values += p.ParameterName;
                    comma = ", ";
                }
                else if (col.IsTiraggoConcurrency)
                {
                    p = CloneParameter(types[col.Name]);
                    p.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(p);

                    sql += p.ParameterName + " := 1; ";

                    into += comma;
                    into += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    values += comma;
                    values += p.ParameterName;
                    comma = ", ";
                }
                else if (col.HasDefault && (modifiedColumns != null && !modifiedColumns.Contains(col.Name)))
                {
                    p = CloneParameter(types[col.Name]);
                    p.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(p);

                    computedColumns += computedComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    computedParameters += computedComma + p.ParameterName;
                    computedComma = ", ";

                    if (col.CharacterMaxLength > 0)
                    {
                        p.Size = (int)col.CharacterMaxLength;
                    }
                }
            }

            tgColumnMetadataCollection cols = request.Columns;

            #region Special Column Logic
            if (cols.DateAdded != null && cols.DateAdded.IsServerSide)
            {
                p = CloneParameter(types[cols.DateAdded.ColumnName]);
                sql += p.ParameterName + " := " + request.ProviderMetadata["DateAdded.ServerSideText"] + ";";

                CreateInsertSQLSnippet(cols.DateAdded.ColumnName, p, ref into, ref values, ref comma);

                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);
            }

            if (cols.DateModified != null && cols.DateModified.IsServerSide)
            {
                p = CloneParameter(types[cols.DateModified.ColumnName]);
                sql += p.ParameterName + " := " + request.ProviderMetadata["DateModified.ServerSideText"] + ";";

                CreateInsertSQLSnippet(cols.DateModified.ColumnName, p, ref into, ref values, ref comma);

                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);
            }

            if (cols.AddedBy != null && cols.AddedBy.IsServerSide)
            {
                p = CloneParameter(types[cols.AddedBy.ColumnName]);
                p.Size = (int)cols.FindByColumnName(cols.AddedBy.ColumnName).CharacterMaxLength;
                sql += p.ParameterName + " := " + request.ProviderMetadata["AddedBy.ServerSideText"] + ";";

                CreateInsertSQLSnippet(cols.AddedBy.ColumnName, p, ref into, ref values, ref comma);

                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);
            }

            if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
            {
                p = CloneParameter(types[cols.ModifiedBy.ColumnName]);
                p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
                sql += p.ParameterName + " := " + request.ProviderMetadata["ModifiedBy.ServerSideText"] + ";";

                CreateInsertSQLSnippet(cols.ModifiedBy.ColumnName, p, ref into, ref values, ref comma);

                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);
            }
            #endregion

            sql += "INSERT INTO " + CreateFullName(request) + " ";

            if (modifiedColumns != null)
            {
                foreach (string colName in modifiedColumns)
                {
                    tgColumnMetadata col = request.Columns[colName];
                    if (col != null && !col.IsAutoIncrement)
                    {
                        p = types[colName];
                        p = cmd.Parameters.Add(CloneParameter(p));

                        object value = packet.CurrentValues[colName];
                        p.Value = value != null ? value : DBNull.Value;

                        into += comma;
                        into += Delimiters.ColumnOpen + colName + Delimiters.ColumnClose;
                        values += comma;
                        values += p.ParameterName;
                        comma = ", ";
                    }
                }
            }

            if (into.Length != 0)
            {
                sql += "(" + into + ") VALUES (" + values + ");";
            }

            if (computedColumns.Length > 0)
            {
                string where = String.Empty;

                foreach (tgColumnMetadata col in request.Columns)
                {
                    if (col.IsInPrimaryKey)
                    {
                        // We need the were clause if there are defaults to bring back
                        p = types[col.Name];

                        if (where.Length > 0) where += " AND ";
                        where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;

                        if (!cmd.Parameters.Contains(p.ParameterName))
                        {
                            p = CloneParameter(p);
                            p.Direction = ParameterDirection.Output;
                            cmd.Parameters.Add(p);
                        }
                    }
                }

                sql += " SELECT " + computedColumns + " INTO " + computedParameters + " FROM " + CreateFullName(request) + " WHERE (" + where + ");";
            }

            sql += " END;";

            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd;
        }
示例#21
0
        public static NpgsqlCommand BuildStoredProcInsertCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request);

            NpgsqlCommand cmd = new NpgsqlCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spInsert + Delimiters.StoredProcNameClose;

            PopulateStoredProcParameters(cmd, request, packet);

            foreach (tgColumnMetadata col in request.Columns)
            {
                if (col.HasDefault && col.Default.ToLower().Contains("newid"))
                {
                    NpgsqlParameter p = types[col.Name];
                    p = cmd.Parameters[p.ParameterName];
                    p.Direction = ParameterDirection.InputOutput;
                }
                else if (col.IsComputed || col.IsAutoIncrement)
                {
                    NpgsqlParameter p = types[col.Name];
                    p = cmd.Parameters[p.ParameterName];
                    p.Direction = ParameterDirection.Output;
                }
            }

            return cmd;
        }
示例#22
0
        //CREATE PROCEDURE "MYGENERATION"."proc_SeqTestUpdate" (
        //    pID IN "SeqTest"."ID"%type,
        //    pTimeStamp IN OUT "SeqTest"."TimeStamp"%type,
        //    pData IN "SeqTest"."Data"%type
        //)
        //IS
        //    pConncurrency "SeqTest"."TimeStamp"%type := pTimeStamp;
        //BEGIN
        //    UPDATE "SeqTest"
        //    SET
        //        "TimeStamp" = "TimeStamp" + 1,
        //        "Data"  = pData
        //    WHERE
        //        "ID" = pID
        //    AND "TimeStamp" = pConncurrency
        //;
        //    IF SQL%ROWCOUNT = 1 THEN
        //     pTimeStamp := (pConncurrency + 1);
        //    ELSE
        //     Raise_application_error(01403, 'NO RECORDS WERE UPDATED');
        //    END IF;
        //END ;
        public static OracleCommand BuildDynamicUpdateCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, OracleParameter> types = Cache.GetParameters(request);

            OracleCommand cmd = new OracleCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            string sql = string.Empty;

            bool hasConcurrency = false;
            string concurrencyColumn = String.Empty;

            List<string> modifiedColumns = packet.ModifiedColumns;

            foreach (tgColumnMetadata col in request.Columns)
            {
                if (col.IsTiraggoConcurrency)
                {
                    hasConcurrency = true;
                    concurrencyColumn = col.Name;

                    sql += "DECLARE pConncurrency " + request.ProviderMetadata.GetTypeMap(col.PropertyName).NativeType + "; ";
                    break;
                }
            }

            OracleParameter p = null;
            sql += " BEGIN ";

            tgColumnMetadataCollection cols = request.Columns;

            if (cols.DateModified != null && cols.DateModified.IsServerSide)
            {
                p = CloneParameter(types[cols.DateModified.ColumnName]);
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                sql += p.ParameterName + " := " + request.ProviderMetadata["DateModified.ServerSideText"] + "; ";
            }

            if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
            {
                p = CloneParameter(types[cols.ModifiedBy.ColumnName]);
                p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                sql += p.ParameterName + " := " + request.ProviderMetadata["ModifiedBy.ServerSideText"] + "; ";
            }

            if (hasConcurrency)
            {
                sql += "pConncurrency := " + Delimiters.Param + concurrencyColumn + "; ";
            }

            sql += "UPDATE " + CreateFullName(request) + " SET ";

            string computed = String.Empty;
            string comma = String.Empty;
            string and = String.Empty;
            string where = string.Empty;

            foreach (string colName in modifiedColumns)
            {
                tgColumnMetadata col = request.Columns[colName];

                if (col != null && !col.IsInPrimaryKey && !col.IsTiraggoConcurrency)
                {
                    p = cmd.Parameters.Add(CloneParameter(types[colName]));

                    object value = packet.CurrentValues[colName];
                    p.Value = value != null ? value : DBNull.Value;

                    sql += comma;
                    sql += Delimiters.ColumnOpen + colName + Delimiters.ColumnClose + " = " + p.ParameterName;
                    comma = ", ";
                }
            }

            foreach (tgColumnMetadata col in request.Columns)
            {
                if (col.IsInPrimaryKey)
                {
                    p = CloneParameter(types[col.Name]);
                    p.Value = packet.OriginalValues[col.Name];
                    cmd.Parameters.Add(p);

                    where += and;
                    where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
                    and = " AND ";
                }
                else if (col.IsComputed && !col.IsAutoIncrement)
                {
                    if (request.SelectedColumns != null && request.SelectedColumns.ContainsKey(col.Name))
                    {
                        p = CloneParameter(types[col.Name]);
                        p.Direction = ParameterDirection.Output;
                        if (col.CharacterMaxLength > 0)
                        {
                            p.Size = (int)col.CharacterMaxLength;
                        }
                        cmd.Parameters.Add(p);

                        if (computed.Length > 0) computed += ", ";
                        computed += " " + p.ParameterName + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose;
                    }
                }
                if (col.IsTiraggoConcurrency)
                {
                    sql += ", ";
                    sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " +
                      Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " + 1";

                    p = CloneParameter(types[col.Name]);
                    p.Direction = ParameterDirection.InputOutput;
                    p.Value = packet.OriginalValues[col.Name];
                    cmd.Parameters.Add(p);
                    break;
                }
            }

            sql += " WHERE (" + where + ") ";
            if (hasConcurrency)
            {
                sql += " AND \"" + concurrencyColumn + "\" = pConncurrency; ";

                sql += "IF SQL%ROWCOUNT = 1 THEN ";
                sql += Delimiters.Param + concurrencyColumn + " := pConncurrency + 1; ELSE ";
                sql += "Raise_application_error(-20101, 'NO RECORDS WERE UPDATED'); END IF;";
            }
            else
            {
                sql += ";";
            }

            if (computed.Length > 0)
            {
                sql += " SELECT " + computed + " FROM " + CreateFullName(request) + " WHERE (" + where + ")";
            }

            sql += " END;";

            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd;
        }
示例#23
0
        public static void PopulateStoredProcParameters(NpgsqlCommand cmd, tgDataRequest request, tgEntitySavePacket packet)
        {
            Dictionary<string, NpgsqlParameter> types = Cache.GetParameters(request);

            NpgsqlParameter p;

            foreach (tgColumnMetadata col in request.Columns)
            {
                p = types[col.Name];
                p = CloneParameter(p);

                if (packet.CurrentValues.ContainsKey(col.Name))
                {
                    p.Value = packet.CurrentValues[col.Name];
                }

                if (p.NpgsqlDbType == NpgsqlDbType.Timestamp)
                {
                    p.Direction = ParameterDirection.InputOutput;
                }

                if (col.IsComputed && col.CharacterMaxLength > 0)
                {
                    p.Size = (int)col.CharacterMaxLength;
                }

                cmd.Parameters.Add(p);
            }
        }
示例#24
0
        public static OracleCommand BuildStoredProcDeleteCommand(tgDataRequest request, tgEntitySavePacket packet)
        {
            OracleCommand cmd = new OracleCommand();
            if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spDelete);

            Dictionary<string, OracleParameter> types = Cache.GetParameters(request);

            OracleParameter p;

            foreach (tgColumnMetadata col in request.Columns)
            {
                if (col.IsInPrimaryKey || col.IsTiraggoConcurrency)
                {
                    p = types[col.Name];
                    p = CloneParameter(p);
                    p.ParameterName = p.ParameterName.Replace(":", "p");
                    p.Value = packet.OriginalValues[col.Name];
                    cmd.Parameters.Add(p);
                }
            }

            return cmd;
        }