public static int SaveClientLog(ClientLogEntity log)
{
OracleParameter[] paras = new OracleParameter[11];
paras[0] = new OracleParameter("p_main_id", OracleDbType.Int32);
paras[0].Value =log.MainId;//int类型
paras[1] = new OracleParameter("p_file_name", OracleDbType.Varchar2, 150);
paras[1].Value = log.FileName;//string类型
paras[2] = new OracleParameter("p_file_size", OracleDbType.Int32);
paras[2].Value = log.FileSize;//int类型
paras[3] = new OracleParameter("p_time_start", OracleDbType.Date);
paras[3].Value = log.TimeStart;//DateTime类型
paras[4] = new OracleParameter("p_time_end", OracleDbType.Date);
paras[4].Value = log.TimeEnd;//DateTime类型
paras[5] = new OracleParameter("p_md5_before", OracleDbType.Varchar2, 200);
paras[5].Value = log.MD5Before;//string 类型
paras[6] = new OracleParameter("p_md5_after", OracleDbType.Varchar2, 200);
paras[6].Value = log.MD5After;//string 类型
paras[7] = new OracleParameter("p_status", OracleDbType.Int32);
paras[7].Value = log.Status;//int类型
paras[8] = new OracleParameter("p_isactive", OracleDbType.Int32);
paras[8].Value = log.IsActive;//bool类型
paras[9] = new OracleParameter("p_user", OracleDbType.Varchar2, 150);
paras[9].Value = log.User;
paras[10] = new OracleParameter("out_main_id", OracleDbType.Int32);
paras[10].Direction = ParameterDirection.Output;
OracleHelper.ExecuteNonQuery(OracleHelper.MSMGRConnectionString, CommandType.StoredProcedure, "MSMGR_MANAGE.savemsmgr_client_log", paras);
int mainid = int.Parse(paras[10].Value.ToString());
return mainid;
}
private void set_erp_interface_sale(out string sqlstr_line, out OracleParameter[] paras_line)
{
sqlstr_line = "insert into cux.cux_order_interface_temp(CUSTOMER_NUMBER,SHIPPED_NUMBER,INVENTORY_ITEM,";
sqlstr_line += "ORDERED_QUANTITY,ORDER_QUANTITY_UOM,address_id,customer_id,shipped_header_id,SHIPPED_LINE_ID,";
sqlstr_line += "mfg_organization_id,SUB_INVENTORY_NAME,LOAD_FLAG,CREATE_DATE,COMEFROM,UNIT_PRICE,PRIV,";
sqlstr_line += "UNIT_PRICE1,SAP_PO_NO,PROCESS_FLAG,attribute1,attribute9)";
sqlstr_line += "values(:CUSTOMER_NUMBER,:SHIPPED_NUMBER,:INVENTORY_ITEM,";
sqlstr_line += ":ORDERED_QUANTITY,:ORDER_QUANTITY_UOM,:address_id,:customer_id,:shipped_header_id,:SHIPPED_LINE_ID,";
sqlstr_line += ":mfg_organization_id,:SUB_INVENTORY_NAME,:LOAD_FLAG,sysdate,:COMEFROM,:UNIT_PRICE,:PRIV,";
sqlstr_line += ":UNIT_PRICE1,:SAP_PO_NO,:PROCESS_FLAG,:attribute1,:attribute9)";
paras_line = new OracleParameter[20];
paras_line[0] = new OracleParameter(":CUSTOMER_NUMBER", OracleType.VarChar); //客户NUMBER
paras_line[1] = new OracleParameter(":SHIPPED_NUMBER", OracleType.VarChar); //来源的订单号码
paras_line[2] = new OracleParameter(":INVENTORY_ITEM", OracleType.VarChar); //物料
paras_line[3] = new OracleParameter(":ORDERED_QUANTITY", OracleType.Number); //数量,正数为销售,负数为退货
paras_line[4] = new OracleParameter(":ORDER_QUANTITY_UOM", OracleType.VarChar); //单位,均为'PCS'
paras_line[5] = new OracleParameter(":address_id", OracleType.Number); //客户地址ID
paras_line[6] = new OracleParameter(":customer_id", OracleType.Number); //客户id
paras_line[7] = new OracleParameter(":shipped_header_id", OracleType.Number); //来源的订单头ID
paras_line[8] = new OracleParameter(":SHIPPED_LINE_ID", OracleType.Number); //来源的订单行ID
paras_line[9] = new OracleParameter(":mfg_organization_id", OracleType.Number); //组织ID
paras_line[10] = new OracleParameter(":SUB_INVENTORY_NAME", OracleType.VarChar); //子库存名
paras_line[11] = new OracleParameter(":LOAD_FLAG", OracleType.VarChar); //'N'
paras_line[12] = new OracleParameter(":COMEFROM", OracleType.VarChar); //传入的第三方系统名称,'CMS'
paras_line[13] = new OracleParameter(":UNIT_PRICE", OracleType.Number); //单价
paras_line[14] = new OracleParameter(":PRIV", OracleType.VarChar); //是否由系统决定单价
paras_line[15] = new OracleParameter(":UNIT_PRICE1", OracleType.Number); //0
paras_line[16] = new OracleParameter(":SAP_PO_NO", OracleType.VarChar); //SAP采购单号
paras_line[17] = new OracleParameter(":PROCESS_FLAG", OracleType.VarChar); //'N'
paras_line[18] = new OracleParameter(":attribute1", OracleType.VarChar); //货位ID
paras_line[19] = new OracleParameter(":attribute9", OracleType.VarChar); //贸易属性
}
参数的数据类型特定于.NET Framework数据提供程序。 指定参数类型会将参数的值转换为.NET Framework数据提供程序类型,然后再将值传递给数据源。 您还可以通过将Parameter对象的DbType属性设置为特定的DbType来以通用方式指定Parameter的类型。
从Parameter对象的Value的.NET Framework类型或Parameter对象的DbType推断出Parameter对象的.NET Framework数据提供程序类型。 下表显示了基于作为参数值或指定的DbType传递的对象的推断参数类型( https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types)。
注:
1.当指定DbType时Value值的数据类型需要与之匹配。如指定参数类型为OracleDbType.Date时,Value需要设置为DateTime或TimeSpan或DateTimeOffset,具体可参考以上链接
不指定DbType时则会通过Value的.NET Framework数据类型推断出.NET Framework数据提供程序类型。
2.
当参数值为null时,必须指定DBNull.Value而不是null
3. 创建时OracleParameter时如果调用的是存储过程则参数不用加“:”如代码片段1,如果执行的sql语句,则需要加":"起占位符的作用.
4. 调用存储过程时如果参数是可变长度类型如varchar2,则new OracleParameter 时必须要指定长度.
5. 特别注意,参数名称必须与占位符或存储过程参数完全一致,不能多空格之类的;在使用oracle自带的访问dll时对OracleParameter参数数组元素的顺序还需要与存储过程或sql占位符的顺序匹配(最好不要打乱顺序)