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