public void Fill(iDB2Parameter db2Parameter)
{
db2Parameter.iDB2DbType = iDB2DbType.iDB2Integer;
db2Parameter.ParameterName = this.Name;
db2Parameter.Value = this.Value;
db2Parameter.Direction = System.Data.ParameterDirection.InputOutput;
}
public void Call(Session session)
{
string program = this.LibraryWhereProgram.Name + "." + this.Name;
command = new iDB2Command(program, session.Connection, session.Transaction);
command.CommandTimeout = 0;
command.CommandType = CommandType.StoredProcedure;
foreach (Parameter parameter in this.Parameters)
{
iDB2Parameter param = new iDB2Parameter();
parameter.Fill(param);
this.command.Parameters.Add(param);
}
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
foreach (Parameter parameter in this.Parameters)
{
iDB2Parameter db2Parameter = command.Parameters[parameter.Name];
if (db2Parameter != null)
{
parameter.UpdateValue(db2Parameter);
}
}
}
public static long GetSequence(iDB2Connection conn, string tableName)
{
long id;
string sql = "SELECT VALUE FROM SEQ_TABLE WHERE KEY=@KEY FOR UPDATE WITH RS";
iDB2Parameter[] parms = new iDB2Parameter[] {
new iDB2Parameter("@KEY", "USER")
};
Object obj = ExecuteScalar(conn, CommandType.Text, sql, parms);
if (obj != null)
{
id = Convert.ToInt32(obj);
string sqlUpdate = "UPDATE SEQ_TABLE SET VALUE=@NEW_ID WHERE VALUE=@OLD_ID AND KEY=@KEY";
iDB2Parameter[] parmsUpdate = new iDB2Parameter[] {
new iDB2Parameter("@NEW_ID", id + 1),
new iDB2Parameter("@OLD_ID", id),
new iDB2Parameter("@KEY", tableName)
};
ExecuteNonQuery(conn, CommandType.Text, sqlUpdate, parmsUpdate);
}
else
{
id = 1;
string sqlInsert = "INSERT INTO SEQ_TABLE(KEY,VALUE) VALUES(@KEY,@VALUE)";
iDB2Parameter[] parmsInsert = new iDB2Parameter[] {
new iDB2Parameter("@KEY", tableName),
new iDB2Parameter("@VALUE", id + 1)
};
ExecuteNonQuery(conn, CommandType.Text, sqlInsert, parmsInsert);
}
return(id);
}
public T FindByid(string sql, string parmName, string parmValue)
{
Debug.Assert(!string.IsNullOrWhiteSpace(parmName));
Debug.Assert(!string.IsNullOrWhiteSpace(parmValue));
T result = default(T);
iDB2Parameter[] db2Parms = new iDB2Parameter[] { new iDB2Parameter('@' + parmName, parmValue) };
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms))
{
if (rdr.Read())
{
T t = Activator.CreateInstance <T>();
PropertyInfo[] propertyInfo = t.GetType().GetProperties();
int i = 0;
foreach (PropertyInfo pi in propertyInfo)
{
try
{
pi.SetValue(t, rdr.GetValue(i++));
}
catch (Exception ex)
{
log.Error("字段类型错误，" + pi.Name + ":" + ex.Message);
}
}
result = t;
}
}
return(result);
}
public iDB2Parameter CreateParameter(string sName, iDB2DbType parameterType, int parameterSize, ParameterDirection paramDirection, string parameterValue)
{
var myParam = new iDB2Parameter(sName, parameterType, parameterSize);
myParam.Value = parameterValue;
myParam.Direction = paramDirection;
return(myParam);
}
public void Fill(iDB2Parameter db2Parameter)
{
db2Parameter.iDB2DbType = iDB2DbType.iDB2VarChar;
db2Parameter.ParameterName = this.Name;
db2Parameter.Size = this.Length;
db2Parameter.Value = this.Value;
db2Parameter.Direction = System.Data.ParameterDirection.InputOutput;
}
/// <summary>
/// <para>Set method for string type parameter</para>
/// <para>Equivalent RPG type: xxA (char)</para>
/// </summary>
/// <param name="name"></param>
/// <param name="length"></param>
/// <param name="aString"></param>
public void setString(string name, int length, string aString)
{
iDB2Parameter parameter = new iDB2Parameter("@" + name, iDB2DbType.iDB2VarChar, length);
parameter.Value = aString;
parameter.Direction = ParameterDirection.InputOutput;
command.Parameters.Add(parameter);
}
/// <summary>
/// <para>Set method for int type parameter</para>
/// <para>Equivalent RPG type: 10i0</para>
/// </summary>
/// <param name="name"></param>
/// <param name="anInteger"></param>
public void setInteger(string name, int anInteger)
{
iDB2Parameter parameter = new iDB2Parameter("@" + name, iDB2DbType.iDB2Integer);
parameter.Value = anInteger;
parameter.Direction = ParameterDirection.InputOutput;
command.Parameters.Add(parameter);
}
/// <summary>
/// <para>Set method for Date type parameter</para>
/// <para>Equivalent RPG type: timestamp (z)</para>
/// </summary>
/// <param name="name"></param>
/// <param name="aDate"></param>
public void setDate(string name, DateTime aDate)
{
iDB2Parameter parameter = new iDB2Parameter("@" + name, iDB2DbType.iDB2TimeStamp);
parameter.Value = aDate;
parameter.Direction = ParameterDirection.InputOutput;
command.Parameters.Add(parameter);
}
/// <inheritdoc />
public virtual iDB2Parameter CreateParameter(QueryPropertyMap map, TModel model)
{
var parm = new iDB2Parameter(GetParameterName(map, model), GetValue(map, model))
{
Direction = map.ParameterDirection,
};
return(parm);
}
public void Fill(iDB2Parameter db2Parameter)
{
db2Parameter.iDB2DbType = iDB2DbType.iDB2Decimal;
db2Parameter.ParameterName = this.Name;
db2Parameter.Precision = Convert.ToByte(this.Precision);
db2Parameter.Scale = Convert.ToByte(this.Scale);
db2Parameter.Value = this.Value;
db2Parameter.Direction = System.Data.ParameterDirection.InputOutput;
}
public static iDB2Parameter CreateParameter(string name, object value, iDB2DbType dbType)
{
iDB2Parameter parm1 = new iDB2Parameter();
parm1.iDB2DbType = dbType;
parm1.ParameterName = name;
parm1.iDB2Value = value;
return(parm1);
}
/// <summary>
/// <para>Set method for decimal type parameter</para>
/// <para>Equivalent RPG type: 15p2 (by Convention)</para>
/// </summary>
/// <param name="name"></param>
/// <param name="precision"></param>
/// <param name="scale"></param>
/// <param name="aDecimal"></param>
public void setDecimal(string name, byte precision, byte scale, Decimal aDecimal)
{
iDB2Parameter parameter = new iDB2Parameter("@" + name, iDB2DbType.iDB2Decimal);
parameter.Value = aDecimal;
parameter.Precision = precision;
parameter.Scale = scale;
parameter.Direction = ParameterDirection.InputOutput;
command.Parameters.Add(parameter);
}
/// <summary>
/// Clones a parameter so that it can be used with another command.
/// </summary>
/// <param name="command">The command to use.</param>
/// <param name="parameter">The parameter to clone.</param>
/// <returns>The clone.</returns>
public override IDataParameter CloneParameter(IDbCommand command, IDataParameter parameter)
{
iDB2Parameter p = (iDB2Parameter)base.CloneParameter(command, parameter);
iDB2Parameter db2p = (iDB2Parameter)parameter;
p.iDB2DbType = db2p.iDB2DbType;
p.iDB2Value = db2p.iDB2Value;
p.Precision = db2p.Precision;
p.Scale = db2p.Scale;
return(p);
}
public void delete(string username)
{
string sql = "DELETE FROM \"USER\" WHERE USERNAME=@USERNAME";
using (iDB2Connection conn = new iDB2Connection(DB2Helper.ConnectionString))
{
conn.Open();
iDB2Parameter[] parms = new iDB2Parameter[] {
new iDB2Parameter("@USERNAME", username)
};
DB2Helper.ExecuteNonQuery(conn, CommandType.Text, sql, parms);
}
}
public void update(User user)
{
string sql = "UPDATE \"USER\" SET PASSWORD=@PASSWORD WHERE USERNAME=@USERNAME";
using (iDB2Connection conn = new iDB2Connection(DB2Helper.ConnectionString))
{
conn.Open();
iDB2Parameter[] parms = new iDB2Parameter[] {
new iDB2Parameter("@PASSWORD", user.password),
new iDB2Parameter("@USERNAME", user.username)
};
DB2Helper.ExecuteNonQuery(conn, CommandType.Text, sql, parms);
}
}
public void CreateInt()
{
IntParameter param = IntParameter.Create(this.AllorsSession, "strParam", 100);
Assert.IsTrue(param != null);
Assert.IsTrue(param.Value == 100);
Assert.IsTrue(param.Name == "strParam");
iDB2Parameter newValue = new iDB2Parameter("strParam", iDB2DbType.iDB2Integer);
newValue.Value = 200;
param.UpdateValue(newValue);
Assert.IsTrue(param.Value == 200);
}
public void CreateDecimal()
{
DecimalParameter param = DecimalParameter.Create(this.AllorsSession, "strParam", 4, 2, 10.23M);
Assert.IsTrue(param != null);
Assert.IsTrue(param.Value == 10.23M);
Assert.IsTrue(param.Name == "strParam");
iDB2Parameter newValue = new iDB2Parameter("strParam", iDB2DbType.iDB2Decimal);
newValue.Value = 19.86M;
param.UpdateValue(newValue);
Assert.IsTrue(param.Value == 19.86M);
}
public void create(User user)
{
string sql = "INSERT INTO \"USER\"(USER_NO,USERNAME,PASSWORD) VALUES(@USER_NO,@USERNAME,@PASSWORD)";
using (iDB2Connection conn = new iDB2Connection(DB2Helper.ConnectionString))
{
conn.Open();
long userNo = DB2Helper.GetSequence(conn, "USER");
iDB2Parameter[] parms = new iDB2Parameter[] {
new iDB2Parameter("@USER_NO", userNo),
new iDB2Parameter("@USERNAME", user.username),
new iDB2Parameter("@PASSWORD", user.password)
};
DB2Helper.ExecuteNonQuery(conn, CommandType.Text, sql, parms);
}
}
public void CreateString()
{
StringParameter param = StringParameter.Create(this.AllorsSession, "strParam", 10, "TestValue");
Assert.IsTrue(param != null);
Assert.IsTrue(param.Value == "TestValue");
Assert.IsTrue(param.Name == "strParam");
Assert.IsTrue(param.Length == 10);
iDB2Parameter newValue = new iDB2Parameter("strParam", iDB2DbType.iDB2VarChar, 10);
newValue.Value = "newValue";
param.UpdateValue(newValue);
Assert.IsTrue(param.Value == "newValue");
}
/// <summary>
/// <para>Adds a new instance of an <see cref="iDB2Parameter"/> object to the command.</para>
/// </summary>
/// <param name="command">The <see cref="iDB2Command"/> to add the parameter.</param>
/// <param name="name"><para>The name of the parameter.</para></param>
/// <param name="idb2Type"><para>One of the <see cref="iDB2DbType"/> values.</para></param>
/// <param name="size"><para>The maximum size of the data within the column.</para></param>
/// <param name="direction"><para>One of the <see cref="ParameterDirection"/> values.</para></param>
/// <param name="nullable"><para>A value indicating whether the parameter accepts <see langword="null"/> (<b>Nothing</b> in Visual Basic) values.</para></param>
/// <param name="precision"><para>The maximum number of digits used to represent the <paramref name="value"/>.</para></param>
/// <param name="scale"><para>The number of decimal places to which <paramref name="value"/> is resolved.</para></param>
/// <param name="sourceColumn"><para>The name of the source column mapped to the DataSet and used for loading or returning the <paramref name="value"/>.</para></param>
/// <param name="sourceVersion"><para>One of the <see cref="DataRowVersion"/> values.</para></param>
/// <param name="value"><para>The value of the parameter.</para></param>
#pragma warning disable 612, 618
public void AddParameter(DbCommand command, string name, iDB2DbType idb2Type, int size,
ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn,
DataRowVersion sourceVersion, object value)
{
if (command == null)
{
throw new ArgumentNullException(nameof(command));
}
iDB2Parameter param = base.CreateParameter(name, DbType.AnsiString, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value) as iDB2Parameter;
if (param != null)
{
param.iDB2DbType = idb2Type;
command.Parameters.Add(param);
}
}
public void CreateDateTime()
{
DateTime tijd = DateTime.Now;
DateTimeParameter param = DateTimeParameter.Create(this.AllorsSession, "strParam", tijd);
Assert.IsTrue(param != null);
Assert.IsTrue(param.Value == tijd);
Assert.IsTrue(param.Name == "strParam");
iDB2Parameter newValue = new iDB2Parameter("strParam", iDB2DbType.iDB2TimeStamp);
DateTime newtijd = DateTime.Now;
newValue.Value = newtijd;
param.UpdateValue(newValue);
Assert.IsTrue(param.Value == newtijd);
}
public static long GetSequenceTrans(iDB2Connection conn, string tableName)
{
//iDB2Transaction trans = conn.BeginTransaction(IsolationLevel.RepeatableRead);
iDB2Transaction trans = conn.BeginTransaction();
long id = 0;
try {
string sql = "SELECT VALUE FROM SEQ_TABLE WHERE KEY=@KEY";
iDB2Parameter[] parms = new iDB2Parameter[] {
new iDB2Parameter("@KEY", "USER")
};
Object obj = ExecuteScalar(trans, CommandType.Text, sql, parms);
if (obj != null)
{
id = Convert.ToInt32(obj);
string sqlUpdate = "UPDATE SEQ_TABLE SET VALUE=@NEW_ID WHERE VALUE=@OLD_ID AND KEY=@KEY";
iDB2Parameter[] parmsUpdate = new iDB2Parameter[] {
new iDB2Parameter("@NEW_ID", id + 1),
new iDB2Parameter("@OLD_ID", id),
new iDB2Parameter("@KEY", tableName)
};
ExecuteNonQuery(trans, CommandType.Text, sqlUpdate, parmsUpdate);
}
else
{
id = 1;
string sqlInsert = "INSERT INTO SEQ_TABLE(KEY,VALUE) VALUES(@KEY,@VALUE)";
iDB2Parameter[] parmsInsert = new iDB2Parameter[] {
new iDB2Parameter("@KEY", tableName),
new iDB2Parameter("@VALUE", id + 1)
};
ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parmsInsert);
}
trans.Commit();
return(id);
}
catch (Exception ex)
{
log.Error(ex.Message);
log.Error(ex.StackTrace);
trans.Rollback();
return(0);
}
}
/// <summary>
/// Fetch parameters from the cache
/// </summary>
/// <param name="cacheKey">Key to look up the parameters</param>
/// <returns></returns>
public static iDB2Parameter[] GetCachedParameters(string cacheKey)
{
iDB2Parameter[] cachedParms = (iDB2Parameter[])parmCache[cacheKey];
if (cachedParms == null)
{
return(null);
}
// If the parameters are in the cache
iDB2Parameter[] clonedParms = new iDB2Parameter[cachedParms.Length];
// return a copy of the parameters
for (int i = 0, j = cachedParms.Length; i < j; i++)
{
clonedParms[i] = (iDB2Parameter)((ICloneable)cachedParms[i]).Clone();
}
return(clonedParms);
}
private static void CloneParameterInCommand(IDbCommand cmd, DbParameter parameter)
{
iDB2Parameter newParameter = new iDB2Parameter();
iDB2Parameter oldParameter = (iDB2Parameter)parameter;
newParameter.DbType = oldParameter.DbType;
newParameter.Direction = oldParameter.Direction;
newParameter.iDB2DbType = oldParameter.iDB2DbType;
newParameter.iDB2Value = oldParameter.iDB2Value;
newParameter.IsNullable = oldParameter.IsNullable;
newParameter.ParameterName = oldParameter.ParameterName;
newParameter.Precision = oldParameter.Precision;
newParameter.Scale = oldParameter.Scale;
newParameter.Size = oldParameter.Size;
newParameter.SourceColumn = oldParameter.SourceColumn;
newParameter.SourceColumnNullMapping = oldParameter.SourceColumnNullMapping;
newParameter.SourceVersion = oldParameter.SourceVersion;
newParameter.Value = oldParameter.Value;
cmd.Parameters.Add(newParameter);
}
public User get(string username)
{
string sql = "SELECT USER_NO,USERNAME,PASSWORD FROM \"USER\" WHERE USERNAME=@USERNAME";
iDB2Parameter[] parms = new iDB2Parameter[] {
new iDB2Parameter("@USERNAME", username)
};
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, parms))
{
if (rdr.Read())
{
User user = new User();
user.userNo = rdr.GetInt32(0);
user.username = rdr.GetString(1);
user.password = rdr.GetString(2);
return(user);
}
else
{
return(null);
}
}
}
public void UpdateValue(iDB2Parameter db2Parameter)
{
this.Value = Convert.ToDateTime(db2Parameter.Value);
}
public int Act_LiqOpe_sp(
int NMRLQO,
int NORSRN,
string FINDSC,
string HINDSC,
string FFNDSC,
string HFNDSC,
string FINEMB,
string HINEMB,
string FFNEMB,
string HFNEMB,
string FCOFF1,
string HCOFF1,
string FCOFF2,
string HCOFF2,
string FPSTAK,
string HPSTAK,
string FATQNA,
string HATQNA,
string ACCION,
string USUARIO
)
{
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Parameter Par = new iDB2Parameter();
iDB2Command Cmd = new iDB2Command();
int dResultado = 0;
try
{
Cmd.CommandText = "SP_INTEGRACION_CARCOOL_ACT_LIQOPE ";
Cmd.Parameters.Clear();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Connection = cn;
Par = Cmd.Parameters.Add("@NMRLQO", iDB2DbType.iDB2Numeric);
Par.Value = NMRLQO;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@NORSRN", iDB2DbType.iDB2Numeric);
Par.Value = NORSRN;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@FINDSC", iDB2DbType.iDB2Numeric);
Par.Value = FINDSC;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@HINDSC", iDB2DbType.iDB2Numeric);
Par.Value = HINDSC;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@FFNDSC", iDB2DbType.iDB2Numeric);
Par.Value = FFNDSC;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@HFNDSC", iDB2DbType.iDB2Numeric);
Par.Value = HFNDSC;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@FINEMB", iDB2DbType.iDB2Numeric);
Par.Value = FINEMB;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@HINEMB", iDB2DbType.iDB2Numeric);
Par.Value = HINEMB;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@FFNEMB", iDB2DbType.iDB2Numeric);
Par.Value = FFNEMB;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@HFNEMB", iDB2DbType.iDB2Numeric);
Par.Value = HFNEMB;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@FCOFF1", iDB2DbType.iDB2Numeric);
Par.Value = FCOFF1;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@HCOFF1", iDB2DbType.iDB2Numeric);
Par.Value = HCOFF1;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@FCOFF2", iDB2DbType.iDB2Numeric);
Par.Value = FCOFF2;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@HCOFF2", iDB2DbType.iDB2Numeric);
Par.Value = HCOFF2;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@FPSTAK", iDB2DbType.iDB2Numeric);
Par.Value = FPSTAK;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@HPSTAK", iDB2DbType.iDB2Numeric);
Par.Value = HPSTAK;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@FATQNA", iDB2DbType.iDB2Numeric);
Par.Value = FATQNA;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@HATQNA", iDB2DbType.iDB2Numeric);
Par.Value = HATQNA;
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@CULUSA", iDB2DbType.iDB2VarChar);
if (USUARIO.Length > 10)
{
Par.Value = USUARIO.Substring(0, 10);
}
else
{
Par.Value = USUARIO;
}
Par.Direction = ParameterDirection.Input;
Par = Cmd.Parameters.Add("@ACCION", iDB2DbType.iDB2VarChar);
Par.Value = ACCION;
Par.Direction = ParameterDirection.Input;
cn.Open();
Cmd.ExecuteNonQuery();
dResultado = 1;
}
catch (Exception ex)
{
dResultado = -1;
}
finally
{
cn.Close();
}
return(dResultado);
}
/// <inheritdoc />
public virtual void SetModelValue(QueryPropertyMap map, iDB2Parameter parameter, TModel model)
{
DefaultParameterRetriever.SetModelValue(map, parameter, model);
}
public void UpdateValue(iDB2Parameter db2Parameter)
{
this.Value = Convert.ToInt32(db2Parameter.Value);
}
