public IDbDataParameter CreateDbParameter(string parameterName, object value)
{
IDbDataParameter dbParameter = null;
if (value == null)
{
dbParameter = new AseParameter(parameterName, DBNull.Value);
}
else if (value is byte[])
{
dbParameter = new AseParameter(parameterName, AseDbType.Image);
dbParameter.Value = value;
}
else
{
dbParameter = new AseParameter(parameterName, value);
}
if (dbParameter.Value == System.DBNull.Value)
{
dbParameter.DbType = DbType.String;
}
return dbParameter;
}
private void GetComaprisonReport()
{
using (AseConnection oCon = new AseConnection(sybConnectionString))
{
using (AseCommand oCom = new AseCommand("portia_load_dtc_comparison", oCon) { CommandType = CommandType.StoredProcedure })
{
AseParameter oParam1 = new AseParameter("@trade_dt", AseDbType.DateTime);
oParam1.Value = new DateTime(2014, 03, 26);
oCom.Parameters.Add(oParam1);
oCon.Open();
try
{
oCom.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
/// <summary>
/// Sobrescreve o metodo de GetParametroRetorno, adiciona o parametro de "@RETURN_VALUE".
/// </summary>
/// <returns>Retorna uma colecao de parametros com o parametro "@RETURN_VALUE" </returns>
internal override IDataParameter GetParametroRetorno()
{
AseParameter p = new AseParameter("@retValue",AseDbType.Integer);
p.Direction = ParameterDirection.ReturnValue;
return p;
}
public override void AttachParameter(IDbCommand command, IDbDataParameter parameter)
{
if (parameter.Value is string && parameter.DbType == DbType.Guid)
{
parameter.DbType = DbType.AnsiString;
}
base.AttachParameter(command, parameter);
AseParameter p = (AseParameter)parameter;
if (p.AseDbType == AseDbType.Unsupported && p.Value is DBNull)
{
parameter.DbType = DbType.AnsiString;
}
}
public static AseDbType InferType(AseParameter parameter)
{
if (parameter.AseDbType != AseDbType.Unsupported || parameter.Value == null || parameter.Value == DBNull.Value)
{
return(parameter.AseDbType);
}
var netType = parameter.Value.GetType();
if (NetTypeToAseDbTypeMap.ContainsKey(netType))
{
return(NetTypeToAseDbTypeMap[netType]);
}
throw new NotSupportedException($"Unsupported .net type {parameter.Value.GetType()} for parameter '{parameter.ParameterName}'.");
}
public static int?GetFormatLength(DbType dbType, AseParameter parameter, Encoding enc)
{
if (FixedFormatLengthMap.ContainsKey(dbType))
{
return(FixedFormatLengthMap[dbType]);
}
var value = parameter.SendableValue;
if (VariableFormatLengthMap.ContainsKey(dbType))
{
return(VariableFormatLengthMap[dbType](parameter, enc, value));
}
return(null);
}
public void ConstructParameter_WithStringValue_InfersType()
{
const string parameterName = "@a_parameter";
const string value = "a value";
var parameter = new AseParameter(parameterName, value);
Assert.AreEqual(parameterName, parameter.ParameterName);
Assert.AreEqual(AseDbType.Unsupported, parameter.AseDbType);
Assert.AreEqual(0, parameter.Size);
Assert.AreEqual(ParameterDirection.Input, parameter.Direction);
Assert.AreEqual(false, parameter.IsNullable);
Assert.AreEqual(0, parameter.Precision);
Assert.AreEqual(0, parameter.Scale);
Assert.IsNull(parameter.SourceColumn);
Assert.AreEqual(DataRowVersion.Default, parameter.SourceVersion);
Assert.AreEqual(value, parameter.Value);
}
private static int GetAnsiStringFormatLength(AseParameter parameter, Encoding enc, object value)
{
if (parameter.IsOutput)
{
return(Math.Max(VarLongBoundary, parameter.Size));
}
switch (value)
{
case string s:
return(enc.GetByteCount(s));
case char c:
return(enc.GetByteCount(new[] { c }));
default:
return(0);
}
}
private static int GetBinaryFormatLength(AseParameter parameter, Encoding enc, object value)
{
if (parameter.IsOutput)
{
return(Math.Max(VarLongBoundary, parameter.Size));
}
switch (value)
{
case byte[] ba:
return(ba.Length);
case byte _:
return(1);
default:
return(0);
}
}
private void GetComparison(DateTime tradeDate)
{
using (AseConnection oCon = new AseConnection("Data Source=mcmqavip;port=4105;Database=MCM;Uid=idsi;Pwd=3idsi3;"))
{
using (AseCommand oCom = new AseCommand("portia_load_dtc_comparison", oCon) { CommandType = CommandType.StoredProcedure })
{
AseParameter oParam = new AseParameter("@trade_dt", AseDbType.DateTime) { Value = tradeDate };
oCom.Parameters.Add(oParam);
oCon.Open();
try
{
oCom.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
private List<DtcConfirms> GetConfirmsFromSybase(DateTime tradeDate)
{
DataTable data = new DataTable();
List<DtcConfirms> lstConfirms = new List<DtcConfirms>();
using (AseConnection oCon = new AseConnection("Data Source=mcmqavip;port=4105;Database=MCM;Uid=idsi;Pwd=3idsi3;"))
{
using (AseCommand oCom = new AseCommand(Constants.InsertConfirms, oCon) { CommandType = CommandType.Text })
{
AseParameter paramDate = new AseParameter(Constants.TradeDateParamPortia, AseDbType.DateTime);
paramDate.Value = tradeDate;
oCom.Parameters.Add(paramDate);
oCon.Open();
try
{
AseDataAdapter adapter = new AseDataAdapter(oCom);
adapter.Fill(data);
foreach (DataRow row in data.Rows)
{
var values = row.ItemArray;
lstConfirms.Add(new DtcConfirms(
values[0].ToString(), values[1].ToString(), values[2].ToString(), values[3].ToString(), values[4].ToString(), values[5].ToString(), values[6].ToString(), values[7].ToString(),
values[8].ToString(), values[9].ToString(), values[10].ToString(), values[11].ToString(), values[12].ToString(), values[13].ToString(), values[14].ToString(),
values[15].ToString(), values[16].ToString(), values[17].ToString(), values[18].ToString(), values[19].ToString(), values[20].ToString()
));
}
}
catch (Exception ex)
{
}
oCon.Close();
}
}
return lstConfirms;
}
public void Simple_Procedure_ShouldExecute()
{
using (var connection = new AseConnection(ConnectionStrings.Pooled))
{
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = "sp_test_173";
cmd.CommandType = CommandType.StoredProcedure;
var total = new AseParameter()
{
ParameterName = "@TotalValue",
AseDbType = AseDbType.Decimal,
Direction = ParameterDirection.Output,
Precision = 18,
Scale = 4
};
cmd.Parameters.Add(total);
var result = cmd.ExecuteNonQuery();
Assert.AreEqual(2819.0444m, Convert.ToDecimal(total.SendableValue));
}
}
/// <summary>
/// 获取参数
/// </summary>
/// <param name="parameterName">参数名称</param>
/// <param name="parameterValue">值</param>
/// <param name="dbType">数据类型</param>
/// <param name="parameterSize">大小</param>
/// <param name="parameterDirection">输出方向</param>
/// <returns>参数集</returns>
public IDbDataParameter MakeParameter(string parameterName, object parameterValue, DbType dbType, Int32 parameterSize, ParameterDirection parameterDirection)
{
AseParameter parameter;
if (parameterSize > 0)
{
parameter = new AseParameter(parameterName, (AseDbType)dbType, parameterSize);
}
else
{
parameter = new AseParameter(parameterName, (AseDbType)dbType);
}
parameter.Direction = parameterDirection;
if (!(parameterDirection == ParameterDirection.Output && parameterValue == null))
{
parameter.Value = parameterValue;
}
return(parameter);
}
public void ConstructParameter_ViaProperties_Success()
{
const string parameterName = "@a_parameter";
const AseDbType type = AseDbType.VarChar;
const int size = 256;
const string sourceColumn = "a_column";
const ParameterDirection parameterDirection = ParameterDirection.Output;
const bool isNullable = true;
const byte precision = 16;
const byte scale = 24;
const DataRowVersion sourceVersion = DataRowVersion.Default;
const string value = "a value";
var parameter = new AseParameter
{
ParameterName = parameterName,
AseDbType = type,
Size = size,
Direction = parameterDirection,
IsNullable = isNullable,
Precision = precision,
Scale = scale,
SourceColumn = sourceColumn,
SourceVersion = sourceVersion,
Value = value
};
Assert.AreEqual(parameterName, parameter.ParameterName);
Assert.AreEqual(type, parameter.AseDbType);
Assert.AreEqual(size, parameter.Size);
Assert.AreEqual(parameterDirection, parameter.Direction);
Assert.AreEqual(isNullable, parameter.IsNullable);
Assert.AreEqual(precision, parameter.Precision);
Assert.AreEqual(scale, parameter.Scale);
Assert.AreEqual(sourceColumn, parameter.SourceColumn);
Assert.AreEqual(sourceVersion, parameter.SourceVersion);
Assert.AreEqual(value, parameter.Value);
}
public void ExecuteData(procedure type, string flashAcctNum, string dtcAcctNum)
{
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd=null;
if (type.Equals(procedure.ADD_FLSH))
{
oCmd = new AseCommand("ADD_FLSH", oCon);
AseParameter oParam0 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam0.Value = strHandler;
oCmd.Parameters.Add(oParam0);
}
if (type.Equals(procedure.RemoveMapping))
{
oCmd = new AseCommand("RemoveMapping", oCon);
AseParameter oParam0 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam0.Value = strHandler;
oCmd.Parameters.Add(oParam0);
}
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam1 = new AseParameter("@flash_number", AseDbType.VarChar, 25);
oParam1.Value = flashAcctNum;
oCmd.Parameters.Add(oParam1);
AseParameter oParam2 = new AseParameter("@dtc_number", AseDbType.VarChar, 25);
oParam2.Value = dtcAcctNum;
oCmd.Parameters.Add(oParam2);
oCon.Open();
oCmd.ExecuteNonQuery();
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-ExeuteData():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
throw;
}
}
public int CheckMappingData(string dtcTicket, string flashTicket)
{
int noOfRowReturned;
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd;
oCmd = new AseCommand("CheckMapping", oCon);
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam0 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam0.Value = strHandler;
oCmd.Parameters.Add(oParam0);
AseParameter oParam2 = new AseParameter("@dtc_number", AseDbType.VarChar, 25);
oParam2.Value = dtcTicket;
oCmd.Parameters.Add(oParam2);
AseParameter oParam3 = new AseParameter("@flash_number", AseDbType.VarChar, 25);
oParam3.Value = flashTicket;
oCmd.Parameters.Add(oParam3);
oCon.Open();
noOfRowReturned = Convert.ToInt32(oCmd.ExecuteScalar());
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-CekMapping():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
if (ex.Message == "No Flash Record found")
{
return 0;
}
throw;
}
return noOfRowReturned;
}
public void ExecuteData(procedure type, string forFlashValue, string dtcTicket, string flashTicket, string updatedDtcValue)
{
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd = null;
if (type.Equals(procedure.UpdateMappingBoth))
{
oCmd = new AseCommand("UpdateMappingForBoth", oCon);
AseParameter oParam3 = new AseParameter("@newFlash_number", AseDbType.VarChar, 25);
oParam3.Value = flashTicket;
oCmd.Parameters.Add(oParam3);
AseParameter oParam4 = new AseParameter("@newDtc_number", AseDbType.VarChar, 25);
oParam4.Value = updatedDtcValue;
oCmd.Parameters.Add(oParam4);
}
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam1 = new AseParameter("@flash_number", AseDbType.VarChar, 25);
oParam1.Value = forFlashValue;
oCmd.Parameters.Add(oParam1);
AseParameter oParam2 = new AseParameter("@dtc_number", AseDbType.VarChar, 25);
oParam2.Value = dtcTicket;
oCmd.Parameters.Add(oParam2);
oCon.Open();
oCmd.ExecuteNonQuery();
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-Exeute Data():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
throw;
}
}
public DataSet GetData(procedure type)
{
DataSet dsReport = new DataSet();
string strOut = string.Empty;
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd = null;
if (type.Equals(procedure.FLSH_RPT))
{
oCmd = new AseCommand("FLSH_RPT", oCon);
}
if (type.Equals(procedure.DISTINCT_DTC))
{
oCmd = new AseCommand("DISTINCT_DTC", oCon);
}
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam1 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam1.Value = strHandler;
oCmd.Parameters.Add(oParam1);
AseDataAdapter oAseAdp = new AseDataAdapter(oCmd);
oAseAdp.Fill(dsReport);
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-GetData():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
if (ex.Message == "No Flash Record found")
{
dsReport = null;
return dsReport;
}
throw;
}
return dsReport;
}
public List <Loan> getApplicantLoans(string acct_no)
{
List <Loan> loans = new List <Loan>();
Loan loan = null;
AseConnection conn = null;
AseCommand command = null;
AseDataReader reader = null;
StringBuilder s = new StringBuilder();
StringBuilder output = new StringBuilder();
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
string sqltext = "zsp_cust_exist_loans";
//string sqltext = "select (select description from phoenix..ad_ln_cls where class_code = a.class_code) as 'FacilityType',a.acct_no";
//sqltext += ",a.amt as 'FacilityAmount'";
//sqltext += ",a.period as 'RepaymentFreq'";
//sqltext += ",a.last_pmt_amt as 'LastRepaymentAmount'";
//sqltext += ",a.nxt_pmt_amt as 'NextRepaymentAmount'";
//sqltext += ",a.col_bal as 'CurrentBalance'";
//sqltext += ",a.mat_dt as 'FacilityMaturityDate'";
//sqltext += " from phoenix..ln_display a, phoenix..dp_acct b ";
//sqltext += "where b.acct_no = @applicantacct_no ";// -----'1020041488'
//sqltext += "and a.rim_no = b.rim_no ";
//sqltext += "and a.class_code != 585 ";
//sqltext += "and a.col_bal != 0 ";
//sqltext += "and a.status != 'Closed' ";
try
{
using (conn = new AseConnection(cnstring))
{
conn.Open();
using (command = new AseCommand(sqltext, conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
//command.Parameters.AddWithValue("@applicantacct_no", acct_no);
//AseParameter param = new AseParameter();
//param.ParameterName = "@applicantacct_no";
//param.Value = acct_no;
AseParameter param = new AseParameter();
param.ParameterName = "@psApplicantacct_no";
param.Value = acct_no;
command.Parameters.Add(param);
reader = command.ExecuteReader();
while (reader.Read())
{
loan = new Loan();
loan.FacilityType = reader["FacilityType"].ToString();
loan.acct_no = reader["acct_no"].ToString();
loan.FacilityAmount = reader["FacilityAmount"].ToString();
loan.RepaymentFreq = reader["RepaymentFreq"].ToString();
loan.LastRepaymentAmount = reader["LastRepaymentAmount"].ToString();
loan.NextRepaymentAmount = reader["NextRepaymentAmount"].ToString();
loan.FacilityMaturityDate = reader["FacilityMaturityDate"].ToString();
loan.CurrentBalance = reader["CurrentBalance"].ToString();
loans.Add(loan);
}
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
command.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(loans);
}
public static IDbDataParameter CreateDataParameter(ClientType ct)
{
IDbDataParameter iParam = null;
switch (ct)
{
case ClientType.ctMsSql:
iParam = new SqlParameter();
break;
case ClientType.ctOleDB:
iParam = new OleDbParameter();
break;
case ClientType.ctOracle:
iParam = new OracleParameter();
break;
case ClientType.ctODBC:
iParam = new OdbcParameter();
break;
#if MySql
case ClientType.ctMySql:
iParam = new MySqlParameter();
break;
#endif
#if Informix
case ClientType.ctInformix:
iParam = new IfxParameter();
break;
#endif
#if Sybase
case ClientType.ctSybase:
iParam = new AseParameter();
break;
#endif
}
return iParam;
}
public static int?GetFormatLength(DbType dbType, AseParameter parameter, Encoding enc)
{
var value = parameter.SendableValue;
switch (dbType)
{
case DbType.String:
case DbType.StringFixedLength:
if (parameter.IsOutput)
{
return(Math.Max(VarLongBoundary * 2, parameter.Size));
}
switch (value)
{
case string s:
return(Encoding.Unicode.GetByteCount(s));
case char c:
return(Encoding.Unicode.GetByteCount(new[] { c }));
default:
return(0);
}
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
if (parameter.IsOutput)
{
return(Math.Max(VarLongBoundary, parameter.Size));
}
switch (value)
{
case string s:
return(enc.GetByteCount(s));
case char c:
return(enc.GetByteCount(new[] { c }));
default:
return(0);
}
case DbType.Binary:
if (parameter.IsOutput)
{
return(Math.Max(VarLongBoundary, parameter.Size));
}
switch (value)
{
case byte[] ba:
return(ba.Length);
case byte _:
return(1);
default:
return(0);
}
case DbType.Decimal:
case DbType.VarNumeric:
return(17); //1 byte pos/neg, 16 bytes data
case DbType.Boolean:
case DbType.Byte:
return(1);
case DbType.Int16:
case DbType.UInt16:
case DbType.SByte: //can't seem to write an sbyte as a single byte, so it'll get encoded in a short
return(2);
case DbType.Int32:
case DbType.UInt32:
return(4);
case DbType.Int64:
case DbType.UInt64:
return(8);
case DbType.Single:
return(4);
case DbType.Double:
return(8);
case DbType.DateTime:
return(8);
case DbType.Date:
return(4);
case DbType.Time:
return(4);
case DbType.Guid:
return(16);
case DbType.Currency:
return(8);
default:
return(null);
}
}
/// <summary>
/// calling the stored procedure MCM_STAGE..FLSH_DTC_RCN_RPT
/// prepare the comparison report and returns the data with exception and success
/// </summary>
public DataSet dsExceptionReport(string strTradeDate, int PageSize, string strType)
{
DataSet dsReport = new DataSet();
string strOut = string.Empty;
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd;
if (strType == "1")//Equity trades
oCmd = new AseCommand("FLSH_DTC_RCN_RPT", oCon);
else//FI trades
oCmd = new AseCommand("FLSH_DTC_RCN_RPT_FI", oCon);
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam = new AseParameter("@trade_dt", AseDbType.DateTime);
oParam.Value = Convert.ToDateTime(strTradeDate).ToShortDateString();
oCmd.Parameters.Add(oParam);
AseParameter oParam1 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam1.Value = strHandler;
oCmd.Parameters.Add(oParam1);
AseDataAdapter oAseAdp = new AseDataAdapter(oCmd);
oAseAdp.Fill(dsReport);
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-dsExceptionReport():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
if (ex.Message == "No Broker Record for that date")
{
dsReport = null;
return dsReport;
}
throw;
}
return dsReport;
}
public DataSet GetData(procedure type, string flashAcctNum,string dtcAcctNum)
{
DataSet dsReport = new DataSet();
string strOut = string.Empty;
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd = null;
if (type.Equals(procedure.SEARCH_FLSH_SINGLE))//Equity trades
{
oCmd = new AseCommand("SEARCH_FLSH_SINGLE", oCon);
AseParameter oParam2 = new AseParameter("@flash_number", AseDbType.VarChar, 25);
oParam2.Value = flashAcctNum;
oCmd.Parameters.Add(oParam2);
AseParameter oParam1 = new AseParameter("@dtc_number", AseDbType.VarChar, 25);
oParam1.Value = dtcAcctNum;
oCmd.Parameters.Add(oParam1);
}
if (type.Equals(procedure.SEARCH_NONACTIVE))//Equity trades
{
oCmd = new AseCommand("SEARCH_NotActive", oCon);
AseParameter oParam2 = new AseParameter("@flash_number", AseDbType.VarChar, 25);
oParam2.Value = flashAcctNum;
oCmd.Parameters.Add(oParam2);
AseParameter oParam1 = new AseParameter("@dtc_number", AseDbType.VarChar, 25);
oParam1.Value = dtcAcctNum;
oCmd.Parameters.Add(oParam1);
}
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam0 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam0.Value = strHandler;
oCmd.Parameters.Add(oParam0);
AseDataAdapter oAseAdp = new AseDataAdapter(oCmd);
oAseAdp.Fill(dsReport);
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-GetData():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
if (ex.Message == "No Flash Record found")
{
dsReport = null;
return dsReport;
}
throw;
}
return dsReport;
}
protected override void SetCmdParameters(ref IDbCommand Cmd, string SQL, params ODAParameter[] ParamList)
{
string dbSql = SQL;
if (ParamList != null)
{
foreach (ODAParameter pr in ParamList)
{
dbSql = dbSql.Replace(pr.ParamsName, pr.ParamsName.Replace(ODAParameter.ODAParamsMark, DbASybase.DBParamsMark));
AseParameter param = new AseParameter();
param.ParameterName = pr.ParamsName.Replace(ODAParameter.ODAParamsMark, DbASybase.DBParamsMark);
if (pr.Size < 0)
{
param.Size = 1;
}
else
{
param.Size = pr.Size;
}
param.Direction = pr.Direction;
switch (pr.DBDataType)
{
case ODAdbType.ODatetime:
param.AseDbType = AseDbType.DateTime;
if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
{
param.Value = System.DBNull.Value;
}
else
{
if (pr.ParamsValue is DateTime || pr.ParamsValue is DateTime?)
{
param.Value = pr.ParamsValue;
}
else if (string.IsNullOrWhiteSpace(pr.ParamsValue.ToString().Trim()))
{
param.Value = System.DBNull.Value;
}
else
{
param.Value = Convert.ToDateTime(pr.ParamsValue);
}
}
break;
case ODAdbType.ODecimal:
param.AseDbType = AseDbType.Decimal;
if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
{
param.Value = System.DBNull.Value;
}
else
{
if (pr.ParamsValue is decimal || pr.ParamsValue is decimal?)
{
param.Value = pr.ParamsValue;
}
else if (string.IsNullOrWhiteSpace(pr.ParamsValue.ToString().Trim()))
{
param.Value = System.DBNull.Value;
}
else
{
param.Value = Convert.ToDecimal(pr.ParamsValue);
}
}
break;
case ODAdbType.OBinary:
param.AseDbType = AseDbType.Image;
if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
{
param.Value = System.DBNull.Value;
}
else
{
param.Value = pr.ParamsValue;
if (pr.ParamsValue is byte[])
{
param.Size = ((byte[])pr.ParamsValue).Length;
}
else
{
throw new ODAException(201, "Params :" + pr.ParamsName + " Type must be byte[]");
}
}
break;
case ODAdbType.OInt:
param.AseDbType = AseDbType.Integer;
if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
{
param.Value = System.DBNull.Value;
}
else
{
if (pr.ParamsValue is int || pr.ParamsValue is int?)
{
param.Value = pr.ParamsValue;
}
else if (string.IsNullOrWhiteSpace(pr.ParamsValue.ToString().Trim()))
{
param.Value = System.DBNull.Value;
}
else
{
param.Value = Convert.ToInt32(pr.ParamsValue);
}
}
break;
case ODAdbType.OChar:
param.AseDbType = AseDbType.UniChar;
if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
{
param.Value = System.DBNull.Value;
}
else
{
if (pr.ParamsValue.ToString().Trim() == "")
{
param.Value = System.DBNull.Value;
}
else
{
param.Value = pr.ParamsValue.ToString().Trim();
}
}
break;
case ODAdbType.OVarchar:
param.AseDbType = AseDbType.UniVarChar;
if (pr.ParamsValue == null || pr.ParamsValue is DBNull)
{
param.Value = System.DBNull.Value;
}
else
{
if (pr.ParamsValue.ToString().Trim() == "")
{
param.Value = System.DBNull.Value;
}
else
{
param.Value = pr.ParamsValue.ToString().Trim();
}
}
break;
default:
param.AseDbType = AseDbType.VarChar;
param.Value = pr.ParamsValue;
break;
}
((AseParameterCollection)Cmd.Parameters).Add(param);
}
}
Cmd.CommandText = dbSql;
FireExecutingCommand(Cmd);
}
private Dictionary <string, DbParameterAccessor> ReadIntoParams(String objname, String query)
{
if (objname.Contains("."))
{
String[] schemaAndName = objname.Split(new char[] { '.' }, 2);
objname = schemaAndName[0] + ".." + schemaAndName[1];
}
else
{
objname = NameNormaliser.NormaliseName(objname);
}
var cnx = CurrentConnection;
var dc = cnx.CreateCommand();
dc.Transaction = (DbTransaction)CurrentTransaction;
dc.CommandText = query;
dc.CommandType = CommandType.Text;
AddInput(dc, "@objname", objname);
var reader = dc.ExecuteReader();
Dictionary <String, DbParameterAccessor> allParams = new Dictionary <string, DbParameterAccessor>();
int position = 0;
while (reader.Read())
{
String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0);
String dataType = reader.GetString(1);
int length = (reader.IsDBNull(2)) ? 0 : System.Convert.ToInt32(reader[2]);
int isOutput = (reader.IsDBNull(3)) ? 0 : System.Convert.ToInt32(reader[3]);
byte precision = (reader.IsDBNull(4)) ? Convert.ToByte(0) : System.Convert.ToByte(reader[4]);
byte scale = (reader.IsDBNull(5)) ? Convert.ToByte(0) : System.Convert.ToByte(reader[5]);
AseParameter dp = new AseParameter();
dp.Direction = GetParameterDirection(isOutput);
if (!String.IsNullOrEmpty(paramName))
{
if (!paramName.StartsWith(ParameterPrefix))
{
dp.ParameterName = ParameterPrefix + paramName;
}
else
{
dp.ParameterName = paramName;
}
dp.SourceColumn = paramName;
}
else
{
dp.Direction = ParameterDirection.ReturnValue;
}
dp.AseDbType = GetDBType(dataType);
String typeName = NormaliseTypeName(dataType);
if (precision > 0)
{
dp.Precision = precision;
}
if (scale > 0)
{
dp.Scale = scale;
}
if ("NTEXT".Equals(typeName) || ("TEXT".Equals(typeName)))
{
dp.Size = MAX_STRING_SIZE;
}
else if (length > 0)
{
dp.Size = System.Convert.ToInt32(length);
}
else
{
if (!ParameterDirection.Input.Equals(dp.Direction) ||
typeof(String).Equals(GetDotNetType(dataType)))
{
dp.Size = MAX_STRING_SIZE;
}
}
allParams[NameNormaliser.NormaliseName(paramName)] = new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType);
}
reader.Close();
return(allParams);
}
private object AddAccount(object param)
{
string dtcAccount = ((Tuple<string, string>)param).Item1;
string portiaAccount = ((Tuple<string, string>)param).Item2;
if (!string.IsNullOrEmpty(dtcAccount) && !string.IsNullOrEmpty(portiaAccount))
{
using (AseConnection con = new AseConnection(model.Constants.SybaseConnection))
{
using (AseCommand com = new AseCommand(model.Constants.InsertAccount, con) { CommandType = CommandType.Text })
{
AseParameter oParam = new AseParameter("@dtcAccountNumber", AseDbType.VarChar);
oParam.Value = dtcAccount;
com.Parameters.Add(oParam);
AseParameter oParam1 = new AseParameter("@portiaAccountNumber", AseDbType.VarChar);
oParam1.Value = portiaAccount;
com.Parameters.Add(oParam1);
con.Open();
try
{
com.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
//DtcAccountNum = string.Empty;
//PortiaAccountNum = string.Empty;
return null;
}
static int Main(string[] args)
{
int
Result = -1,
tmpInt;
decimal
tmpDecimal;
object
tmpObject;
StreamWriter
fstr_out = null;
string
tmpString,
OutputFileName = "log.log";
#if WITH_TRACE
string
TraceFileName = "trace.log";
#endif
AseConnection
conn = null;
AseCommand
cmd = null;
AseTransaction
Transaction = null;
AseDataReader
reader = null;
AseParameter
Parameter = null;
AseDataAdapter
da = null;
DataTable
tmpDataTable = null;
try
{
try
{
fstr_out = new StreamWriter(OutputFileName, false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
#if WITH_REFLECTION
Assembly[]
asms = AppDomain.CurrentDomain.GetAssemblies();
Assembly
asm = null;
foreach (Assembly a in asms)
{
if (a.FullName.ToLower().IndexOf("sybase.data.aseclient") > -1)
{
asm = a;
}
fstr_out.WriteLine("Assembly.CodeBase: " + a.CodeBase);
fstr_out.WriteLine("Assembly.EscapedCodeBase: " + a.EscapedCodeBase);
fstr_out.WriteLine("Assembly.FullName: " + a.FullName);
fstr_out.WriteLine("Assembly.GlobalAssemblyCache: " + a.GlobalAssemblyCache.ToString().ToLower());
fstr_out.WriteLine("Assembly.ImageRuntimeVersion: " + a.ImageRuntimeVersion);
fstr_out.WriteLine("Assembly.Location: " + a.Location);
fstr_out.WriteLine();
}
if (asm != null)
{
Type[]
alltypes = asm.GetTypes();
for (tmpInt = 0; tmpInt < alltypes.Length; ++tmpInt)
{
fstr_out.WriteLine("Обнаружено: " + alltypes[tmpInt].Name);
if (alltypes[tmpInt].Name.CompareTo("AseConnection") == 0)
{
Type
t = alltypes[tmpInt];
FieldInfo[]
fi = t.GetFields(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
fstr_out.WriteLine("Анализ Field, определенных в " + t.Name);
foreach (FieldInfo f in fi)
{
fstr_out.Write(" " + f.Name);
fstr_out.WriteLine();
}
fstr_out.WriteLine();
PropertyInfo[]
//pi_=t.GetProperties(BindingFlags.Instance|BindingFlags.NonPublic|BindingFlags.Static); // AseConnection.Language
pi_ = t.GetProperties(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
fstr_out.WriteLine("Анализ Property, определенных в " + t.Name);
foreach (PropertyInfo p_ in pi_)
{
fstr_out.Write(" " + p_.Name);
fstr_out.WriteLine();
}
fstr_out.WriteLine();
fstr_out.WriteLine("Поддерживаемые методы:");
ParameterInfo[]
pi;
MethodInfo[]
mi;
//mi=t.GetMethods();
mi = t.GetMethods(BindingFlags.DeclaredOnly
| BindingFlags.Instance
| BindingFlags.Public);
foreach (MethodInfo m in mi)
{
fstr_out.Write(" " + m.ReturnType.Name + " " + m.Name + "(");
pi = m.GetParameters();
for (int i = 0; i < pi.Length; ++i)
{
fstr_out.Write(pi[i].ParameterType.Name + " " + pi[i].Name);
if (i + 1 < pi.Length)
{
fstr_out.Write(", ");
}
}
fstr_out.WriteLine(")");
}
fstr_out.WriteLine();
}
}
fstr_out.WriteLine();
}
#endif
#if WITH_TRACE
_strmWriter = new StreamWriter(TraceFileName, false, System.Text.Encoding.GetEncoding(1251));
_strmWriter.AutoFlush = true;
#endif
if ((tmpString = ConfigurationSettings.AppSettings["connectionString"]) == null ||
tmpString == string.Empty)
{
fstr_out.WriteLine("ConfigurationSettings.AppSettings[\"connectionString\"] is empty!!!");
return(Result);
}
conn = new AseConnection(tmpString);
conn.InfoMessage += new AseInfoMessageEventHandler(conn_InfoMessage);
conn.StateChange += new System.Data.StateChangeEventHandler(conn_StateChange);
#if WITH_TRACE
conn.TraceEnter += new TraceEnterEventHandler(conn_TraceEnter);
conn.TraceExit += new TraceExitEventHandler(conn_TraceExit);
#endif
conn.Open();
fstr_out.WriteLine("AseConnection.ConnectionString: " + conn.ConnectionString);
fstr_out.WriteLine("AseConnection.ConnectionTimeout: " + conn.ConnectionTimeout);
fstr_out.WriteLine("AseConnection.Database: " + conn.Database);
fstr_out.WriteLine("AseConnection.NamedParameters: " + conn.NamedParameters.ToString().ToLower());
fstr_out.WriteLine("AseConnection.State: " + conn.State);
fstr_out.WriteLine("AseConnection.DriverVersion: " + AseConnection.DriverVersion);
//fstr_out.WriteLine("AseConnection.Language: "+AseConnection.Language);
fstr_out.WriteLine();
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select @@spid";
if ((tmpObject = cmd.ExecuteScalar()) != null)
{
tmpString = Convert.ToString(tmpObject);
}
#if TEST_CHARSET
cmd.CommandText = "select cast(val as date) from T4 where GroupId=4 and Id=1";
if (da == null)
{
da = new AseDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
da.Fill(tmpDataTable);
#endif
#if TEST_EXECUTE_NON_QUERY
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.NamedParameters = false;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
cmd.CommandText = "update Victim set Val = ? where Id = ?";
cmd.Parameters.Add("Val", AseDbType.Integer);
cmd.Parameters.Add("Id", AseDbType.Integer);
for (int Id = 1; Id <= 5; Id += 2)
{
cmd.Parameters["Val"].Value = Id;
cmd.Parameters["Id"].Value = Id;
tmpInt = cmd.ExecuteNonQuery();
}
cmd.NamedParameters = true;
#endif
#if TEST_BLOB
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
FileStream
fs;
byte[]
Blob;
#if TEST_BLOB_SAVE
tmpString = "@FImage";
cmd.CommandText = "update TestTypes set FImage = " + tmpString;
cmd.Parameters.Clear();
cmd.Parameters.Add(tmpString, AseDbType.Image);
fs = new FileStream("welcome.bmp", FileMode.Open, FileAccess.Read);
Blob = new byte[fs.Length];
fs.Read(Blob, 0, Blob.Length);
cmd.Parameters[tmpString].Value = Blob;
tmpInt = cmd.ExecuteNonQuery();
#endif
cmd.Parameters.Clear();
cmd.CommandText = "select * from TestTypes";
reader = cmd.ExecuteReader();
do
{
if (reader.HasRows)
{
for (int i = 0; i < reader.FieldCount; ++i)
{
fstr_out.WriteLine(reader.GetName(i) + " GetDataTypeName(): \"" + reader.GetDataTypeName(i) + "\" GetFieldType(): \"" + reader.GetFieldType(i) + "\"");
}
tmpInt = reader.GetOrdinal("FImage");
while (reader.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = (byte[])reader["FImage"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = new byte[reader.GetBytes(tmpInt, 0, null, 0, int.MaxValue)];
reader.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
}while(reader.NextResult());
reader.Close();
#endif
#if TEST_SMTH
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from TestDate order by FDate";
cmd.Parameters.Clear();
if (da == null)
{
da = new AseDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable != null)
{
tmpDataTable.Reset();
}
else
{
tmpDataTable = new DataTable();
}
da.Fill(tmpDataTable);
tmpString = "";
for (int i = 0; i < tmpDataTable.Rows.Count; ++i)
{
if (tmpString != string.Empty)
{
tmpString += " ";
}
tmpString += Convert.ToDateTime(tmpDataTable.Rows[i]["FDate"]).ToString("yyyy-MM-dd");
}
cmd.NamedParameters = false;
cmd.CommandText = "update TestTypes set FDatetime = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("FDate", AseDbType.DateTime).Value = DateTime.Now;
tmpInt = cmd.ExecuteNonQuery();
cmd.NamedParameters = true;
cmd.CommandText = "update TestTypes set FDatetime = @FDatetime";
cmd.Parameters.Clear();
cmd.Parameters.Add("@FDatetime", AseDbType.DateTime).Value = DateTime.Now;
tmpInt = cmd.ExecuteNonQuery();
#endif
#if TEST_DATA_ADAPTER_FILL_SCHEMA
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Staff";
if (da == null)
{
da = new AseDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable != null)
{
tmpDataTable.Reset();
}
else
{
tmpDataTable = new DataTable();
}
da.FillSchema(tmpDataTable, SchemaType.Source);
#endif
#if TEST_STORED_PROCEDURES
if (cmd == null)
{
cmd = conn.CreateCommand();
}
object[]
tmpObjects = new object[] { 5, 6 };
cmd.CommandType = CommandType.StoredProcedure;
cmd.NamedParameters = false;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "mathtutor";
for (int i = 0; i < tmpObjects.Length; ++i)
{
Parameter = new AseParameter();
Parameter.ParameterName = "Param" + i;
switch (Type.GetTypeCode(tmpObjects[i].GetType()))
{
case System.TypeCode.Int32:
{
Parameter.DbType = DbType.Int32;
break;
}
}
Parameter.Value = tmpObjects[i];
cmd.Parameters.Add(Parameter);
}
Parameter = new AseParameter();
Parameter.ParameterName = "Param3";
Parameter.DbType = DbType.Int32;
Parameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Parameter);
cmd.ExecuteNonQuery();
tmpInt = !Convert.IsDBNull(cmd.Parameters["Param3"].Value) ? Convert.ToInt32(cmd.Parameters["Param3"].Value) : Int32.MinValue;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_TestTypes_Decimal_10_6";
AseCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["@FDecimal_10_6"].Value = 123.45m;
cmd.ExecuteNonQuery();
tmpDecimal = !Convert.IsDBNull(cmd.Parameters["@FDecimal_10_6_out"].Value) ? Convert.ToDecimal(cmd.Parameters["@FDecimal_10_6_out"].Value) : decimal.MinValue;
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "{? = call sp_TestTypes_Decimal_10_6(? ,?)}";
cmd.NamedParameters = false;
Parameter = new AseParameter("@RETURN_VALUE", AseDbType.Integer);
Parameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(Parameter);
Parameter = new AseParameter("@FDecimal_10_6", AseDbType.Decimal); //AseDbType.Numeric
//Parameter=new AseParameter();
//Parameter.ParameterName="@FDecimal_10_6";
//Parameter.DbType=DbType.Decimal;
//Parameter.AseDbType=AseDbType.Decimal; //AseDbType.Numeric
Parameter.Direction = ParameterDirection.Input;
//Parameter.Precision=10;
//Parameter.Scale=6;
Parameter.Value = 123.45m;
cmd.Parameters.Add(Parameter);
Parameter = new AseParameter("@FDecimal_10_6_out", AseDbType.Decimal); //AseDbType.Numeric
Parameter.Direction = ParameterDirection.Output;
Parameter.Precision = 10;
Parameter.Scale = 6;
cmd.Parameters.Add(Parameter);
cmd.ExecuteNonQuery();
tmpDecimal = !Convert.IsDBNull(cmd.Parameters["@FDecimal_10_6_out"].Value) ? Convert.ToDecimal(cmd.Parameters["@FDecimal_10_6_out"].Value) : decimal.MinValue;
tmpDecimal = 2;
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "{call UpdateTestTypes(?)}";
cmd.NamedParameters = false;
Parameter = new AseParameter("@rate", AseDbType.Decimal);
Parameter.Direction = ParameterDirection.Input;
Parameter.Value = tmpDecimal;
cmd.Parameters.Add(Parameter);
/*
* if(cmd==null)
* cmd=conn.CreateCommand();
*
* cmd.CommandType=CommandType.StoredProcedure;
* cmd.CommandText="UpdateTestTypes";
* AseCommandBuilder.DeriveParameters(cmd);
* cmd.Parameters["@Decimal_18_4"].Value=tmpDecimal;
*/
tmpInt = cmd.ExecuteNonQuery();
cmd.CommandType = CommandType.StoredProcedure;
for (int size = 255; size <= 258; ++size)
{
cmd.CommandText = "sp_ReturnAndOutputVarChar" + size;
AseCommandBuilder.DeriveParameters(cmd);
cmd.ExecuteNonQuery();
tmpString = !Convert.IsDBNull(cmd.Parameters["@OutParam"].Value) ? Convert.ToString(cmd.Parameters["@OutParam"].Value) : "NULL";
}
conn.ChangeDatabase("master");
cmd.CommandText = "testdb..sp_ReturnAndOutput";
AseCommandBuilder.DeriveParameters(cmd);
conn.ChangeDatabase("testdb");
#endif
Transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.Transaction = Transaction;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_Staff_Save";
//cmd.CommandText="sp_TestTypes";
fstr_out.WriteLine("AseCommandBuilder.DeriveParameters(\"" + cmd.CommandText + "\")");
AseCommandBuilder.DeriveParameters(cmd);
foreach (AseParameter parameter in cmd.Parameters)
{
fstr_out.WriteLine("\tParameterIndex: " + parameter.ParameterIndex + Environment.NewLine +
"\tParameterName: " + parameter.ParameterName + Environment.NewLine +
"\tDirection: " + parameter.Direction + Environment.NewLine +
"\tDbType: " + parameter.DbType + Environment.NewLine +
"\tAseDbType: " + parameter.AseDbType + Environment.NewLine +
"\tSize: " + parameter.Size + Environment.NewLine +
"\tPrecision: " + parameter.Precision + Environment.NewLine +
"\tScale: " + parameter.Scale + Environment.NewLine +
"\tIsNullable: " + parameter.IsNullable.ToString().ToLower() + Environment.NewLine +
"\tSourceColumn: " + parameter.SourceColumn + Environment.NewLine +
"\tSourceVersion: " + parameter.SourceVersion + Environment.NewLine);
}
fstr_out.WriteLine();
conn.ChangeDatabase("veksel");
cmd.CommandText = "sp_CONTRACT_SAVE";
fstr_out.WriteLine("AseCommandBuilder.DeriveParameters(\"" + cmd.CommandText + "\")");
AseCommandBuilder.DeriveParameters(cmd);
foreach (AseParameter parameter in cmd.Parameters)
{
fstr_out.WriteLine("\tParameterIndex: " + parameter.ParameterIndex + Environment.NewLine +
"\tParameterName: " + parameter.ParameterName + Environment.NewLine +
"\tDirection: " + parameter.Direction + Environment.NewLine +
"\tDbType: " + parameter.DbType + Environment.NewLine +
"\tAseDbType: " + parameter.AseDbType + Environment.NewLine +
"\tSize: " + parameter.Size + Environment.NewLine +
"\tPrecision: " + parameter.Precision + Environment.NewLine +
"\tScale: " + parameter.Scale + Environment.NewLine +
"\tIsNullable: " + parameter.IsNullable.ToString().ToLower() + Environment.NewLine +
"\tSourceColumn: " + parameter.SourceColumn + Environment.NewLine +
"\tSourceVersion: " + parameter.SourceVersion + Environment.NewLine);
}
fstr_out.WriteLine();
Transaction.Rollback();
Transaction = null;
Result = 0;
}
catch (AseException eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine +
"Errors:" + Environment.NewLine + ErrorsToString(eException.Errors) + Environment.NewLine +
"Message: " + eException.Message + Environment.NewLine +
"Source: " + eException.Source + Environment.NewLine +
"StackTrace:" + Environment.NewLine + eException.StackTrace + Environment.NewLine +
"TargetSite: " + eException.TargetSite + Environment.NewLine);
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine +
"Message: " + eException.Message + Environment.NewLine +
"Source: " + eException.Source + Environment.NewLine +
"StackTrace:" + Environment.NewLine + eException.StackTrace + Environment.NewLine +
"TargetSite: " + eException.TargetSite + Environment.NewLine +
"InnerException:" + Environment.NewLine + eException.InnerException.GetType().FullName + Environment.NewLine +
"InnerException.Message: " + eException.InnerException.Message + Environment.NewLine +
"InnerException.Source: " + eException.InnerException.Source + Environment.NewLine +
"InnerException.StackTrace:" + Environment.NewLine + eException.InnerException.StackTrace + Environment.NewLine +
"InnerException.TargetSite: " + eException.InnerException.TargetSite);
}
}
finally
{
if (Transaction != null)
{
try
{
Transaction.Rollback();
}
catch
{
;
}
}
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
if (fstr_out != null)
{
fstr_out.Close();
}
#if WITH_TRACE
if (_strmWriter != null)
{
_strmWriter.Close();
}
#endif
}
return(Result);
}
private object AddBroker(object param)
{
string portiaName = ((Tuple<string, string,string>)param).Item1;
string dtcName = ((Tuple<string, string, string>)param).Item2;
string execBrokerId = ((Tuple<string, string, string>)param).Item3;
if (!string.IsNullOrEmpty(dtcName) && !string.IsNullOrEmpty(portiaName))
{
using (AseConnection con = new AseConnection(model.Constants.SybaseConnection))
{
using (AseCommand com = new AseCommand(model.Constants.InsertBroker, con) { CommandType = CommandType.Text })
{
AseParameter oParam = new AseParameter("@portiaBrokerName", AseDbType.VarChar);
oParam.Value = portiaName;
com.Parameters.Add(oParam);
AseParameter oParam1 = new AseParameter("@dtcBrokerName", AseDbType.VarChar);
oParam1.Value = dtcName;
com.Parameters.Add(oParam1);
AseParameter oParam2 = new AseParameter("@executingBrokerId", AseDbType.VarChar);
oParam2.Value = execBrokerId;
com.Parameters.Add(oParam2);
con.Open();
try
{
com.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
DtcBrokerName = string.Empty;
PortiaBrokerName = string.Empty;
ExecBrokerId = string.Empty;
return null;
}
public void ConstructParameter_WithNoArgs_NoErrors()
{
var unused = new AseParameter();
Assert.Pass();
}
private Dictionary<string, DbParameterAccessor> ReadIntoParams(String objname, String query)
{
if (objname.Contains("."))
{
String[] schemaAndName = objname.Split(new char[] { '.' }, 2);
objname = schemaAndName[0] + ".." + schemaAndName[1] ;
}
else
{
objname = NameNormaliser.NormaliseName(objname) ;
}
DbCommand dc = CurrentConnection.CreateCommand();
dc.Transaction = CurrentTransaction;
dc.CommandText = query;
dc.CommandType = CommandType.Text;
AddInput(dc, "@objname", objname);
DbDataReader reader = dc.ExecuteReader();
Dictionary<String, DbParameterAccessor> allParams = new Dictionary<string, DbParameterAccessor>();
int position = 0;
while (reader.Read())
{
String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0);
String dataType = reader.GetString(1);
int length = (reader.IsDBNull(2)) ? 0 : System.Convert.ToInt32(reader[2]);
int isOutput = (reader.IsDBNull(3)) ? 0 : System.Convert.ToInt32(reader[3]);
byte precision = (reader.IsDBNull(4)) ? Convert.ToByte(0) : System.Convert.ToByte(reader[4]);
byte scale = (reader.IsDBNull(5)) ? Convert.ToByte(0) : System.Convert.ToByte(reader[5]);
AseParameter dp = new AseParameter();
dp.Direction = GetParameterDirection(isOutput);
if (!String.IsNullOrEmpty(paramName))
{
if (!paramName.StartsWith(ParameterPrefix))
dp.ParameterName = ParameterPrefix + paramName;
else
dp.ParameterName = paramName;
dp.SourceColumn = paramName;
}
else
{
dp.Direction = ParameterDirection.ReturnValue;
}
dp.AseDbType = GetDBType(dataType);
String typeName = NormaliseTypeName(dataType);
if (precision > 0) dp.Precision = precision;
if (scale > 0) dp.Scale = scale;
if ("NTEXT".Equals(typeName) || ("TEXT".Equals(typeName)))
dp.Size = MAX_STRING_SIZE;
else if (length > 0)
{
dp.Size = System.Convert.ToInt32(length);
}
else
{
if (!ParameterDirection.Input.Equals(dp.Direction) ||
typeof(String).Equals(GetDotNetType(dataType)))
dp.Size = MAX_STRING_SIZE;
}
allParams[NameNormaliser.NormaliseName(paramName)] = new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType);
}
reader.Close();
return allParams;
}
public DataSet GetConsolidateReport(string strType)
{
DataSet dsReport = new DataSet();
//string strOut = string.Empty;
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd;
if (strType == "1")//Equity trades
oCmd = new AseCommand("FLSH_DTC_RCN_ECEPTION_ALL", oCon);
else//FI trades
oCmd = new AseCommand("RCN_EXCEPTION_DETAILS_FI_ALL", oCon);
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam.Value = strHandler;
oCmd.Parameters.Add(oParam);
AseDataAdapter oAseAdp = new AseDataAdapter(oCmd);
oAseAdp.Fill(dsReport);
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-GetConsolidateReport():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
dsReport = null;
throw;
}
return dsReport;
}
private void GetComaprisonReport(DateTime tradeDate)
{
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
using (AseConnection oCon = new AseConnection(strCon))
{
using (AseCommand oCom = new AseCommand("portia_load_dtc_comparison", oCon) { CommandType = CommandType.StoredProcedure })
{
AseParameter oParam1 = new AseParameter("@trade_dt", AseDbType.DateTime);
oParam1.Value = tradeDate;
oCom.Parameters.Add(oParam1);
oCon.Open();
try
{
oCom.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
dtCompare = GetCompareRecords();
ExportDataTableTOExcel(dtCompare,tradeDate);
}
private void FillList()
{
AseConnection oCon = new AseConnection(strCon);
SuggestionListFlash = "";
SuggestionListDtc = "";
try
{
string strHandler = Convert.ToString(Utility.GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd;
oCmd = new AseCommand("FLSH_RPT_ALL", oCon);
oCmd.CommandType = CommandType.StoredProcedure;
AseParameter oParam1 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam1.Value = strHandler;
oCmd.Parameters.Add(oParam1);
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
oCon.Open();
AseDataReader reader = oCmd.ExecuteReader();
while (reader.Read())
{
if (string.IsNullOrEmpty(SuggestionListFlash))
{
flashList.Insert(0, reader["flashAccountNumber"].ToString());
SuggestionListFlash += "\"" + reader["flashAccountNumber"].ToString() + "\"";
}
else
{
flashList.Add(reader["flashAccountNumber"].ToString());
SuggestionListFlash += ", \"" + reader["flashAccountNumber"].ToString() + "\"";
}
}
hdnFlash.Value = SuggestionListFlash;
oCmd.CommandTimeout = intTimeout;
oCmd = new AseCommand("DISTINCT_DTC_ALL", oCon);
oCmd.CommandType = CommandType.StoredProcedure;
AseParameter oParam2 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam2.Value = strHandler;
oCmd.Parameters.Add(oParam2);
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
oCon.Open();
reader = oCmd.ExecuteReader();
while (reader.Read())
{
if (string.IsNullOrEmpty(SuggestionListDtc))
{
dtcList.Insert(0, reader["dtcAccountNumber"].ToString());
SuggestionListDtc += "\"" + reader["dtcAccountNumber"].ToString() + "\"";
}
else
{
dtcList.Add(reader["dtcAccountNumber"].ToString());
SuggestionListDtc += ", \"" + reader["dtcAccountNumber"].ToString() + "\"";
}
}
hdnDtc.Value = SuggestionListDtc;
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-while filling autoomplete data:", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
throw;
}
}
private void DisplayDataForQuery()
{
dataGridView1.DataSource = null;
if (queryBuilder.MetadataProvider != null && queryBuilder.MetadataProvider.Connected)
{
if (queryBuilder.MetadataProvider is OLEDBMetadataProvider)
{
OleDbCommand command = (OleDbCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder.SQL;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
OleDbParameter parameter = new OleDbParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}//end ole schemaRoot
if (queryBuilder.MetadataProvider is ODBCMetadataProvider)
{
OdbcCommand command = (OdbcCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder.SQL;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
OdbcParameter parameter = new OdbcParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
OdbcDataAdapter adapter = new OdbcDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}//end odbc
if (queryBuilder.MetadataProvider is UniversalMetadataProvider)
{
if (this.DatabasePlatform == QueryBuilderDatabasePlatform.Sybase)
{
AseCommand command = (AseCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder.SQL;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
AseParameter parameter = new AseParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
AseDataAdapter adapter = new AseDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
} //end sybase
} //end universal metadata provider
} //end check if connected
} //end method
