/// <summary>
 /// Clear all of the parameters from this SQLServerDBProvider's Command objects in the data-adapter.  Useful for re-using same provider
 /// for multiple SQL calls.
 /// </summary>
 /// <param name="commandType"></param>
 public void ClearParameters(DBCommandTypes commandType)
 {
     if (this.dataAdapter != null)
     {
         if ((commandType & DBCommandTypes.Select) != 0)
         {
             this.dataAdapter.SelectCommand.Parameters.Clear();
         }
         if ((commandType & DBCommandTypes.Delete) != 0)
         {
             this.dataAdapter.DeleteCommand.Parameters.Clear();
         }
         if ((commandType & DBCommandTypes.Insert) != 0)
         {
             this.dataAdapter.InsertCommand.Parameters.Clear();
         }
         if ((commandType & DBCommandTypes.Update) != 0)
         {
             this.dataAdapter.UpdateCommand.Parameters.Clear();
         }
     }
 }
 /// <summary>
 /// Adds a parameter to the relevant command(s)' parameter list.
 /// </summary>
 /// <param name="commandTypeToAddTo"></param>
 /// <param name="storedProcedureParameterName"></param>
 /// <param name="paramValue"></param>
 public void Add(DBCommandTypes commandTypeToAddTo, string storedProcedureParameterName, object paramValue)
 {
     if (this.dataAdapter != null)
     {
         if ((commandTypeToAddTo & DBCommandTypes.Select) != 0)
         {
             this.dataAdapter.SelectCommand.Parameters.AddWithValue(storedProcedureParameterName, paramValue);
         }
         if ((commandTypeToAddTo & DBCommandTypes.Delete) != 0)
         {
             this.dataAdapter.DeleteCommand.Parameters.AddWithValue(storedProcedureParameterName, paramValue);
         }
         if ((commandTypeToAddTo & DBCommandTypes.Insert) != 0)
         {
             this.dataAdapter.InsertCommand.Parameters.AddWithValue(storedProcedureParameterName, paramValue);
         }
         if ((commandTypeToAddTo & DBCommandTypes.Update) != 0)
         {
             this.dataAdapter.UpdateCommand.Parameters.AddWithValue(storedProcedureParameterName, paramValue);
         }
     }
 }
        public void AddColumnToDataSetCommands(DataColumn dataColumn,
                                               string storedProcedureParameterName,
                                               DBCommandTypes dBCommandTypesToBeAddedTo)
        {
            if (dataColumn == null)
            {
                // The data column must be specified
                throw new Exception("DataColumn can not be null");
            }

            if (string.IsNullOrEmpty(storedProcedureParameterName))
            {
                // The stored procedure paramater name must be specified
                throw new Exception("storedProcedureParameterName must be specified");
            }

            if (dBCommandTypesToBeAddedTo == DBCommandTypes.None)
            {
                // There are no command types specified to add to so there is no
                // work to be done, just return
                return;
            }

            SqlDbType dbType = SqlDbType.Int;
            int dbSize = 0;
            if (dataColumn.DataType == typeof(bool))
            {
                dbType = SqlDbType.Bit;
                dbSize = 0;
            }
            else if (dataColumn.DataType == typeof(UInt32))
            {
                dbType = SqlDbType.Int;
                dbSize = 0;
            }
            else if (dataColumn.DataType == typeof(decimal))
            {
                dbType = SqlDbType.Decimal;
                dbSize = 0;
            }
            else if (dataColumn.DataType == typeof(string))
            {
                dbType = SqlDbType.NVarChar;
                dbSize = dataColumn.MaxLength;
            }

            foreach (DBCommandTypes dataSetCommandType in Enum.GetValues(typeof(DBCommandTypes)))
            {
                if ((dataSetCommandType & dBCommandTypesToBeAddedTo) > 0)
                {
                    SqlParameter dbParamater;
                    if (dbSize == 0)
                    {
                        dbParamater = new SqlParameter(storedProcedureParameterName, dbType);
                    }
                    else
                    {
                        dbParamater = new SqlParameter(storedProcedureParameterName, dbType, dbSize);
                    }
                    dbParamater.SourceColumn = dataColumn.ColumnName;

                    switch (dataSetCommandType)
                    {
                        case DBCommandTypes.Delete:
                            this.DataAdapater.DeleteCommand.Parameters.Add(dbParamater);
                            break;
                        case DBCommandTypes.Insert:
                            this.DataAdapater.InsertCommand.Parameters.Add(dbParamater);
                            break;
                        case DBCommandTypes.Select:
                            this.DataAdapater.SelectCommand.Parameters.Add(dbParamater);
                            break;
                        case DBCommandTypes.Update:
                            this.DataAdapater.UpdateCommand.Parameters.Add(dbParamater);
                            break;
                    }
                }
            }
        }
Ejemplo n.º 4
0
        public static DBCommand Build(DBTable table, string procName, DBCommandTypes type, IEnumerable <DBColumn> columns = null)
        {
            if (table.PrimaryKey == null && (type == DBCommandTypes.Delete || type == DBCommandTypes.Update))
            {
                return(null);
            }
            if (columns == null)
            {
                columns = table.Columns;
            }
            string commandText = procName;
            string prefix      = table.Schema.System.ParameterPrefix;

            CommandType ctype = CommandType.StoredProcedure;

            if (string.IsNullOrEmpty(procName))
            {
                ctype       = CommandType.Text;
                commandText = table.Schema.System.FormatCommand(table, type, null, columns);
            }
            var command = new DBCommand(commandText, ctype);

            if (type == DBCommandTypes.Delete)
            {
                command.Parameters.Add(new DBCommandParameter(table.PrimaryKey, prefix)
                {
                    Direction = ParameterDirection.Input
                });
            }
            else
            {
                DBCommandParameter paramId = null;
                if (table.PrimaryKey != null)
                {
                    paramId = new DBCommandParameter(table.PrimaryKey, prefix)
                    {
                        Direction = ParameterDirection.Input
                    };

                    if (type == DBCommandTypes.Insert || type == DBCommandTypes.InsertSequence)
                    {
                        command.Parameters.Add(paramId);
                    }
                    //else if (type == DBCommandTypes.InsertSequence)
                    //{
                    //command.Parameters.Add(paramId);
                    //paramId.Direction = ParameterDirection.InputOutput;
                    //}
                }
                foreach (var column in columns)
                {
                    if (column.ColumnType == DBColumnTypes.Default && column != table.PrimaryKey)
                    {
                        var prm = new DBCommandParameter(column, prefix);
                        if (ctype == CommandType.Text)
                        {
                            prm.Direction = ParameterDirection.Input;
                        }
                        command.Parameters.Add(prm);
                    }
                }
                if (paramId != null && type == DBCommandTypes.Update)
                {
                    command.Parameters.Add(paramId);
                }
            }
            return(command);
        }