public void AseAdapter_WithAseCommandBuilder_CanInsertUpdateAndDelete()
{
using (var connnection = new AseConnection(ConnectionStrings.Default))
{
connnection.Open();
using (var adapter = new AseDataAdapter("SELECT ColumnId, ColumnDescription, ColumnNullable, COALESCE(ColumnNullable, 'Foo') AS ColumnCalculated FROM AseDataAdapterTests_Table1", connnection))
{
using (new AseCommandBuilder(adapter))
{
var original = new DataTable("AseDataAdapterTests_Table1");
adapter.FillSchema(original, SchemaType.Mapped);
adapter.Fill(original);
Assert.AreEqual(5, original.Rows.Count); // SELECT
var updateRow = original.Rows.Find(1);
Assert.IsNotNull(updateRow, "Did not find a row in AseDataAdapterTests_Table1 for update with ColumnId=1");
updateRow["ColumnDescription"] = "an updated value"; // UPDATE
var deleteRow = original.Rows.Find(3);
Assert.IsNotNull(deleteRow, "Did not find a row in AseDataAdapterTests_Table1 for delete with ColumnId=3");
deleteRow.Delete(); // DELETE
original.Rows.Add(-1, "an inserted value"); // INSERT
// Commit the changes to the database.
adapter.Update(original);
original.AcceptChanges();
var fresh = new DataTable("AseDataAdapterTests_Table1");
adapter.FillSchema(fresh, SchemaType.Mapped);
adapter.Fill(fresh);
Assert.AreEqual(5, fresh.Rows.Count); // SELECT
updateRow = fresh.Rows.Find(1);
Assert.IsNotNull(updateRow, "Did not find a row in AseDataAdapterTests_Table1 for update with ColumnId=1");
Assert.AreEqual(updateRow["ColumnDescription"], "an updated value");
deleteRow = fresh.Rows.Find(3);
Assert.IsNull(deleteRow);
var insertRow = fresh.Rows.Find(6); // Next identity value.
Assert.IsNotNull(insertRow);
}
}
}
}
public DataSet Query(string SQLString, params AseParameter[] cmdParms)
{
DataSet set2;
using (AseConnection connection = new AseConnection(this.connectionString))
{
AseCommand cmd = new AseCommand();
this.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (AseDataAdapter adapter = new AseDataAdapter(cmd))
{
DataSet dataSet = new DataSet();
try
{
adapter.Fill(dataSet, "ds");
cmd.Parameters.Clear();
}
catch (Exception exception1)
{
throw new Exception(exception1.Message);
}
set2 = dataSet;
}
}
return(set2);
}
private DataTable RetornarDataTableQuery(string query, string strConn, List <AseParameter> parameters = null)
{
_log.TraceMethodStart();
_log.Trace($"Query gerada: {query}");
DataTable data = new DataTable();
AseConnection connection = new AseConnection(strConn);
AseDataAdapter da = new AseDataAdapter(query, connection);
if (parameters != null)
{
da.SelectCommand.Parameters.AddRange(parameters.ToArray());
}
try
{
connection.Open();
da.Fill(data);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
_log.TraceMethodEnd();
return(data);
}
public override DataTable ExecuteQuery(string cmdText, DBHelperParmCollection parameters)
{
DataTable dtRet = new DataTable();
AseCommand _AseCommand = (AseCommand)CreateCommand(cmdText, CommandType.Text);
_AseCommand.Parameters.Clear();
if (parameters != null)
{
foreach (DBHelperParm para in parameters)
{
_AseCommand.Parameters.Add(new AseParameter(para.Key, EncodingHelper.Default2DB(para.Value, _DBEncodeing)));
}
}
AseDataAdapter _OdbcDataAdapter = new AseDataAdapter(_AseCommand);
try
{
ProcessDataTable(dtRet);
_OdbcDataAdapter.Fill(dtRet);
}
catch (Exception ex)
{
throw ex;
}
return(dtRet);
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <param name="commandType"></param>
/// <param name="closeConnection"></param>
/// <returns></returns>
public List <RespObj> ExecuteReader(string query, CommandType commandType = CommandType.Text, bool closeConnection = true)
{
comm.CommandText = query.ToString();
comm.CommandType = commandType;
DataTable resultTable = new DataTable();
try
{
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
AseDataAdapter Adapter = new AseDataAdapter(comm);
Adapter.Fill(resultTable);
List <RespObj> ReturnObject = _util.convertDataTable <RespObj>(resultTable);
return(ReturnObject);
}
catch (Exception ex)
{
throw;
}
finally
{
comm = new AseCommand();
if (closeConnection)
{
comm.Dispose();
if (_conn.State != ConnectionState.Closed)
{
_conn.Close();
}
}
}
}
public DataTable GetJobDetail(string sqlCommand, string tableName)
{
DataTable dt = new DataTable(tableName);
var cmd = new AseCommand(sqlCommand, _aseConnection);
var ada = new AseDataAdapter(cmd);
ada.Fill(dt);
return(dt);
}
private static int GenerateTableBinary(string tableName, string fileName, string connectionString)
{
int iRet = 0;
string sConn = "";
if (connectionString.Length < 3)
{
sConn = "Data Source = hkgdcussyb010; Port = 4105; Database = db_policy; Uid = huat059; Pwd = K5hlYMI%; charset = cp850";
}
else
{
sConn = connectionString;
}
try
{
AseConnection cn = new AseConnection(sConn);
AseCommand cmd = new AseCommand();
cmd.Connection = cn;
AseDataAdapter da = new AseDataAdapter(cmd);
string sPureTableName = "";
if (tableName.ToLower().StartsWith("select"))
{
cmd.CommandText = "select count(1) from (" + tableName + ") a";
sPureTableName = "return_data";
}
else
{
cmd.CommandText = "select * from " + tableName;
sPureTableName = getTableName(tableName);
}
DataTable dt = new DataTable(sPureTableName);
da.Fill(dt);
iRet = dt.Rows.Count;
//string sFullTableName = getFullTableName(tableName);
//Boolean isOK = SerializeDataTable(dt, fileName);
DataSetSerializerCompression(dt, fileName);
//dt.WriteXml(System.IO.Path.Combine(dir, sPureTableName + ".table"), XmlWriteMode.WriteSchema);
//Console.WriteLine("Save to: " + tableName + ".table,RowCount = " + iRet.ToString());
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
return(iRet);
}
protected void ExecuteQuery(string sql, GridView users)
{
var connStr = ConfigurationManager.ConnectionStrings["SybaseLocalConnection"].ToString();
using (AseConnection connnection = new AseConnection(connStr))
{
using (AseCommand cmd = connnection.CreateCommand())
{
cmd.CommandText = sql;
AseDataAdapter da = new AseDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
users.DataSource = ds;
users.DataBind();
}
}
}
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 DataTable GetReaderInfo(DataModel.M_Config config)
{
AseConnection conn = new AseConnection(config.SybaseSourceSetting.ToConnectionString());
string cmdstr = config.SQLString;
DataSet ds = new DataSet();
try
{
AseDataAdapter adapter = new AseDataAdapter(cmdstr, conn);
adapter.Fill(ds);
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
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
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;
}
/// <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;
}
private System.Data.DataTable GetCompareRecords()
{
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
using (AseConnection oCon = new AseConnection(strCon))
{
using (AseCommand oCom = new AseCommand("select * from Confirms_DifferenceTable", oCon) { CommandType = CommandType.Text })
{
oCon.Open();
AseDataAdapter adapter = new AseDataAdapter(oCom);
adapter.Fill(dtCompare);
}
}
return dtCompare;
}
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;
}
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;
}
public override DataTable ExecuteQuery(string cmdText, DBHelperParmCollection parameters)
{
DataTable dtRet = new DataTable();
AseCommand _AseCommand = (AseCommand)CreateCommand(cmdText, CommandType.Text);
_AseCommand.Parameters.Clear();
if (parameters != null)
{
foreach (DBHelperParm para in parameters)
{
_AseCommand.Parameters.Add(new AseParameter(para.Key, EncodingHelper.Default2DB(para.Value, _DBEncodeing)));
}
}
AseDataAdapter _OdbcDataAdapter = new AseDataAdapter(_AseCommand);
try
{
ProcessDataTable(dtRet);
_OdbcDataAdapter.Fill(dtRet);
}
catch (Exception ex)
{
throw ex;
}
return dtRet;
}