private void InitCommandCollection()
{
this._commandCollection = new global::System.Data.IDbCommand[1];
this._commandCollection[0] = new global::Oracle.ManagedDataAccess.Client.OracleCommand();
((global::Oracle.ManagedDataAccess.Client.OracleCommand)(this._commandCollection[0])).Connection = new global::Oracle.ManagedDataAccess.Client.OracleConnection(global::LoginUsuario.Properties.Settings.Default.ConnectionString);
((global::Oracle.ManagedDataAccess.Client.OracleCommand)(this._commandCollection[0])).CommandText = "ADMAURA.ACTUALIZAR_SUCURSAL";
((global::Oracle.ManagedDataAccess.Client.OracleCommand)(this._commandCollection[0])).CommandType = global::System.Data.CommandType.StoredProcedure;
global::Oracle.ManagedDataAccess.Client.OracleParameter param = new global::Oracle.ManagedDataAccess.Client.OracleParameter();
param.ParameterName = "IDSUC";
param.DbType = global::System.Data.DbType.Decimal;
param.OracleDbTypeEx = global::Oracle.ManagedDataAccess.Client.OracleDbType.Decimal;
param.Size = 22;
param.IsNullable = true;
param.SourceColumn = null;
((global::Oracle.ManagedDataAccess.Client.OracleCommand)(this._commandCollection[0])).Parameters.Add(param);
param = new global::Oracle.ManagedDataAccess.Client.OracleParameter();
param.ParameterName = "NOM";
param.OracleDbTypeEx = global::Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2;
param.Size = 32767;
param.IsNullable = true;
param.SourceColumn = null;
((global::Oracle.ManagedDataAccess.Client.OracleCommand)(this._commandCollection[0])).Parameters.Add(param);
param = new global::Oracle.ManagedDataAccess.Client.OracleParameter();
param.ParameterName = "DIR";
param.OracleDbTypeEx = global::Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2;
param.Size = 32767;
param.IsNullable = true;
param.SourceColumn = null;
((global::Oracle.ManagedDataAccess.Client.OracleCommand)(this._commandCollection[0])).Parameters.Add(param);
}
protected override IDbCommand CreateCommand(IDbHelper db, IMappingProvider mappingProvider, object commandType, IDictionary <string, object> values)
{
IStatement statement = mappingProvider.GetStatement(commandType);
StringBuilder sb = new StringBuilder();
IDbCommand dbCommand = db.Connection.CreateCommand();
dbCommand.CommandText = statement.CommandText;
dbCommand.CommandType = statement.CommandType;
foreach (KeyValuePair <string, IParameter> kv in statement.Parameters)
{
object peropertyValue = values[kv.Key];
OracleParameter parameter = (OracleParameter)kv.Value;
global::Oracle.ManagedDataAccess.Client.OracleParameter dataParameter = null;
if (parameter.IsCursor)
{
dataParameter = new global::Oracle.ManagedDataAccess.Client.OracleParameter();
dataParameter.ParameterName = parameter.ParameterName;
dataParameter.OracleDbType = OracleDbType.RefCursor;
CreateParameter(parameter.ParameterName, peropertyValue, parameter.PropertyType, parameter.Size);
}
else
{
dataParameter = (global::Oracle.ManagedDataAccess.Client.OracleParameter)CreateParameter(parameter.ParameterName, peropertyValue, parameter.PropertyType, parameter.Size);
}
dataParameter.Direction = parameter.Direction;
dbCommand.Parameters.Add(dataParameter);
}
return(dbCommand);
}
/// <summary>
/// 根据类型获取存储过程参数类型
/// </summary>
/// <param name="parameterValueType"></param>
/// <returns></returns>
public static OracleDbType GetOracleDbType(Type parameterValueType)
{
if (!CachedOracleDbTypes.ContainsKey(parameterValueType))
{
lock (CachedOracleDbTypes)
{
if (!CachedOracleDbTypes.ContainsKey(parameterValueType))
{
Type innerType = parameterValueType;
if (innerType.IsGenericType && innerType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
innerType = Nullable.GetUnderlyingType(innerType);
}
//nullable中可能还有枚举
if (innerType.IsEnum)
{
innerType = Enum.GetUnderlyingType(innerType);
}
global::Oracle.ManagedDataAccess.Client.OracleParameter param = new global::Oracle.ManagedDataAccess.Client.OracleParameter {
Value = Util.GetDefaultValue(innerType)
};
CachedOracleDbTypes.Add(parameterValueType, param.OracleDbType);
}
}
}
return(CachedOracleDbTypes[parameterValueType]);
}
protected override IDataParameter CreateParameter(string parameterName, object parameterValue, Type propertyType, int length)
{
global::Oracle.ManagedDataAccess.Client.OracleParameter parameter = new global::Oracle.ManagedDataAccess.Client.OracleParameter();
parameter.ParameterName = parameterName;
if (parameterValue == null)
{
parameter.OracleDbType = GetOracleDbType(propertyType);
//parameter.Value = Util.GetDefaultValue(propertyType);
parameter.Value = DBNull.Value;
}
else
{
if (parameterValue is bool)
{
parameter.Value = (bool)parameterValue ? 1 : 0;
}
else if (parameterValue is Enum)
{
parameter.Value = Convert.ChangeType(parameterValue, Enum.GetUnderlyingType(propertyType));
}
else
{
parameter.Value = parameterValue;
}
}
if (length > 0 && (parameter.OracleDbType == OracleDbType.Char || parameter.OracleDbType == OracleDbType.NChar || parameter.OracleDbType == OracleDbType.Varchar2 || parameter.OracleDbType == OracleDbType.NVarchar2))
{
parameter.Size = length;
}
return(parameter);
}
public override int Insert(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, IDictionary <string, object> inserts, IDictionary <string, object> returns)
{
ITable table = mappingProvider.GetTable(entityType);
StringBuilder sb = new StringBuilder();
OracleCommand command = new OracleCommand();
int parameterCounter = 1;
#region 循环每个属性
string tableName = GetTableName(entityType, mappingProvider, tableMapping);
sb.Append("begin insert into ");
sb.Append(tableName);
sb.Append(" (");
//Oracle存储过程参数有顺序之分
//List<OracleParameter> returnParameters = new List<OracleParameter>();//取回的存储过程
Dictionary <string, string> returnColumns = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase); //属性名是键，sql参数名是值
int index = 0;
foreach (KeyValuePair <string, object> kv in inserts)
{
if (index > 0)
{
sb.Append(",");
}
IColumn column = table.Columns[kv.Key];
if (column.InsertBehavior.Generate)
{
sb.Append(column.ColumnName);
index++;
}
}
sb.Append(") values (");
index = 0;
foreach (KeyValuePair <string, object> kv in inserts)
{
if (index > 0)
{
sb.Append(",");
}
IColumn column = table.Columns[kv.Key];
if (column.InsertBehavior.Generate)
{
if (column.InsertBehavior.ValueBehavior == ValueBehavior.UseValueExpression)
{
sb.Append(column.InsertBehavior.ValueExpression);
}
else
{
sb.Append(CreateParameterNameInStatement(parameterCounter));
//object propertyValue = column.PropertyAdapter.GetValue(obj);
//Validate(column, propertyValue);//验证如：检查字符串长度，是否为null
AddParameter(command.Parameters, ref parameterCounter, kv.Value, column.PropertyType, column.Size);
}
index++;
}
}
sb.Append(")");
//"INSERT INTO SequenceTest_Table (ID, OtherColumn)" +
//"VALUES (SequenceTest_Sequence.NEXTVAL, :OtherColumn)" +
//"RETURNING ID INTO :ID";
foreach (string propertyName in table.PrimaryProperties)
{
OracleColumn column = (OracleColumn)table.Columns[propertyName];
//取回主键值 ，本版本只支持主键值的取回
if (column.HasSequence && column.ReturnAfterInsert)
{
string pname = CreateParameterName(parameterCounter);
returnColumns.Add(propertyName, pname);
sb.Append(";select ");
sb.Append(column.Sequence);
sb.Append(".currval into ");
sb.Append(CreateParameterNameInStatement(parameterCounter));
sb.Append(" from dual");
global::Oracle.ManagedDataAccess.Client.OracleParameter parameter = (global::Oracle.ManagedDataAccess.Client.OracleParameter)AddParameter(command.Parameters, ref parameterCounter, null, column.PropertyType, column.Size);
parameter.Direction = ParameterDirection.Output;
}
}
sb.Append("; end;");
#endregion
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
//执行sql
int returnCount = db.ExecuteNonQuery(command);
//read return value
foreach (KeyValuePair <string, string> item in returnColumns)
{
object objValue = command.Parameters[item.Value].Value; //GetCommandValue(cmd, pName);
returns.Add(item.Key, ConvertDbValue(objValue, table.Columns[item.Key].PropertyType));
//item.Value.PropertyAdapter.SetValue(obj, ConvertDbValue(objValue, item.Value.PropertyAdapter.PropertyType.Type));
}
return(returnCount);
}
