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