public List <T> ExecuteList <T>(DbCommandWrapper cmdWrapper, bool isStoredProc,
string sql, IDataMapper <T> dataMapper, int index, int size)
{
if (cmdWrapper == null)
{
throw new Exception("DbCommandWrapper is null");
}
if (string.IsNullOrEmpty(sql))
{
throw new Exception("Sql or stored procedure is empty");
}
if (dataMapper == null)
{
throw new Exception("Mapper is null");
}
List <T> list = new List <T>();
IDataReader rdr = null;
DataSet dataSet = null;
DbCommand cmd = null;
DbDataAdapter da = null;
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory(dataSource.Provider);
da = factory.CreateDataAdapter();
cmd = conn.CreateCommand();
cmdWrapper.Command = cmd;
cmd.Connection = conn;
if (isStoredProc)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
cmd.CommandText = sql;
dataSet = new DataSet();
da.SelectCommand = cmd;
da.Fill(dataSet, index, size, typeof(T).ToString());
AddParameters(cmd, cmdWrapper);
rdr = dataSet.Tables[typeof(T).ToString()].CreateDataReader();
while (rdr.Read())
{
list.Add(dataMapper.Map(rdr));
}
cmdWrapper.PopulateOutputParameters();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (da != null)
{
da.Dispose();
}
if (dataSet != null)
{
dataSet.Dispose();
}
if (rdr != null)
{
rdr.Close();
}
}
return(list);
}
private void TestForDominatedObjects()
{
var common = new SQLCommon
{
SkylineType =
new SkylineBNL()
{
Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString
},
ShowInternalAttributes = true
};
DataTable entireSkylineDataTable =
common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
_entireSkylineSql);
int[] skylineAttributeColumns =
SkylineSamplingHelper.GetSkylineAttributeColumns(entireSkylineDataTable);
DbProviderFactory factory = DbProviderFactories.GetFactory(Helper.ProviderName);
// use the factory object to create Data access objects.
DbConnection connection = factory.CreateConnection();
// will return the connection object (i.e. SqlConnection ...)
connection.ConnectionString = Helper.ConnectionString;
var dtEntire = new DataTable();
connection.Open();
DbDataAdapter dap = factory.CreateDataAdapter();
DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandTimeout = 0; //infinite timeout
string strQueryEntire;
string operatorsEntire;
int numberOfRecordsEntire;
string[] parameterEntire;
string ansiSqlEntire =
common.GetAnsiSqlFromPrefSqlModel(
common.GetPrefSqlModelFromPreferenceSql(_entireSkylineSql));
prefSQL.SQLParser.Helper.DetermineParameters(ansiSqlEntire, out parameterEntire,
out strQueryEntire, out operatorsEntire,
out numberOfRecordsEntire);
selectCommand.CommandText = strQueryEntire;
dap.SelectCommand = selectCommand;
dtEntire = new DataTable();
dap.Fill(dtEntire);
for (var ii = 0; ii < skylineAttributeColumns.Length; ii++)
{
dtEntire.Columns.RemoveAt(0);
}
DataTable sampleSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName,
_skylineSampleSql);
DataTable entireSkylineDataTableBestRank = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName,
_entireSkylineSqlBestRank.Replace("XXX", sampleSkylineDataTable.Rows.Count.ToString()));
DataTable entireSkylineDataTableSumRank = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName,
_entireSkylineSqlSumRank.Replace("XXX", sampleSkylineDataTable.Rows.Count.ToString()));
IReadOnlyDictionary <long, object[]> sampleSkylineDatabase =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(sampleSkylineDataTable, 0);
IReadOnlyDictionary <long, object[]> entireDatabase =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(dtEntire, 0);
IReadOnlyDictionary <long, object[]> entireSkylineDatabase =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTable, 0);
IReadOnlyDictionary <long, object[]> entireSkylineDatabaseBestRank =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTableBestRank, 0);
IReadOnlyDictionary <long, object[]> entireSkylineDatabaseSumRank =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTableSumRank, 0);
IReadOnlyDictionary <long, object[]> randomSample =
SkylineSamplingHelper.GetRandomSample(entireSkylineDatabase, sampleSkylineDataTable.Rows.Count);
var dominatedObjectsEntireSkyline = new DominatedObjects(entireDatabase, entireSkylineDatabase,
skylineAttributeColumns);
var dominatedObjectsSampleSkyline = new DominatedObjects(entireDatabase, sampleSkylineDatabase,
skylineAttributeColumns);
var dominatedObjectsEntireSkylineBestRank = new DominatedObjects(entireDatabase,
entireSkylineDatabaseBestRank, skylineAttributeColumns);
var dominatedObjectsEntireSkylineSumRank = new DominatedObjects(entireDatabase, entireSkylineDatabaseSumRank,
skylineAttributeColumns);
var dominatedObjectsRandomSample = new DominatedObjects(entireDatabase, randomSample,
skylineAttributeColumns);
Debug.WriteLine("entire database size: {0}", entireDatabase.Keys.ToList().Count);
Debug.WriteLine("entire skyline size: {0}", entireSkylineDataTable.Rows.Count);
Debug.WriteLine("sample skyline size: {0}", sampleSkylineDatabase.Keys.ToList().Count);
Debug.WriteLine("random skyline size: {0}", randomSample.Keys.ToList().Count);
Debug.WriteLine("best skyline size: {0}", entireSkylineDatabaseBestRank.Keys.ToList().Count);
Debug.WriteLine("sum skyline size: {0}", entireSkylineDatabaseSumRank.Keys.ToList().Count);
Debug.WriteLine("");
Debug.WriteLine("");
WriteSummary("entire", dominatedObjectsEntireSkyline);
WriteSummary("sample", dominatedObjectsSampleSkyline);
WriteSummary("random", dominatedObjectsRandomSample);
WriteSummary("best", dominatedObjectsEntireSkylineBestRank);
WriteSummary("sum", dominatedObjectsEntireSkylineSumRank);
WriteDominatingObjects("entire", dominatedObjectsEntireSkyline);
WriteDominatingObjects("sample", dominatedObjectsSampleSkyline);
WriteDominatingObjects("random", dominatedObjectsRandomSample);
WriteDominatingObjects("best", dominatedObjectsEntireSkylineBestRank);
WriteDominatingObjects("sum", dominatedObjectsEntireSkylineSumRank);
}
private void TestCompareAlgorithms()
{
var common = new SQLCommon
{
SkylineType = new SkylineBNL()
};
var sql =
"SELECT cs.*, colors.name, fuels.name, bodies.name, makes.name, conditions.name FROM cars_large cs LEFT OUTER JOIN colors ON cs.color_id = colors.ID LEFT OUTER JOIN fuels ON cs.fuel_id = fuels.ID LEFT OUTER JOIN bodies ON cs.body_id = bodies.ID LEFT OUTER JOIN makes ON cs.make_id = makes.ID LEFT OUTER JOIN conditions ON cs.condition_id = conditions.ID SKYLINE OF cs.price LOW, cs.mileage LOW, cs.horsepower HIGH, cs.enginesize HIGH, cs.consumption LOW, cs.cylinders HIGH, cs.seats HIGH, cs.doors HIGH, cs.gears HIGH, colors.name ('red' >> OTHERS INCOMPARABLE), fuels.name ('diesel' >> 'petrol' >> OTHERS EQUAL), bodies.name ('limousine' >> 'coupé' >> 'suv' >> 'minivan' >> OTHERS EQUAL), makes.name ('BMW' >> 'MERCEDES-BENZ' >> 'HUMMER' >> OTHERS EQUAL), conditions.name ('new' >> 'occasion' >> OTHERS EQUAL)";
PrefSQLModel model = common.GetPrefSqlModelFromPreferenceSql(sql);
string sqlBNL = common.ParsePreferenceSQL(sql);
DataTable entireSkylineDataTable =
common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
sql);
DbProviderFactory factory = DbProviderFactories.GetFactory(Helper.ProviderName);
// use the factory object to create Data access objects.
DbConnection connection = factory.CreateConnection();
// will return the connection object (i.e. SqlConnection ...)
connection.ConnectionString = Helper.ConnectionString;
var dtEntire = new DataTable();
DbDataAdapter dap = factory.CreateDataAdapter();
DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandText = sqlBNL;
dap.SelectCommand = selectCommand;
dap.Fill(dtEntire);
var common2 = new SQLCommon
{
SkylineType = new SkylineSQL()
};
string sqlNative = common2.ParsePreferenceSQL(sql);
var dtEntire2 = new DataTable();
DbDataAdapter dap2 = factory.CreateDataAdapter();
DbCommand selectCommand2 = connection.CreateCommand();
selectCommand2.CommandText = sqlNative;
dap2.SelectCommand = selectCommand2;
dap2.Fill(dtEntire2);
connection.Close();
foreach (DataRow i in dtEntire2.Rows)
{
var has = false;
foreach (DataRow j in entireSkylineDataTable.Rows)
{
if ((int)i[0] == (int)j[0])
{
has = true;
break;
}
}
if (!has)
{
Debug.WriteLine(i[0]);
}
}
}
protected DataTable data_table(string sql, out DbDataAdapter ad, string table_name = "", bool throwerr = true, bool with_schema = false, bool no_log = false)
{
return((DataTable)open_set_ad(sql, out ad, throwerr, false, table_name, with_schema, no_log));
}
protected object open_set_ad(string sql, out DbDataAdapter ad, bool throwerr = true, bool dataset = true, string table_name = "", bool with_schema = false, bool no_log = false, int?timeout = null)
{
bool opened = false;
try {
opened = open_conn(no_log);
if (!no_log)
{
log_sql(sql);
}
DbCommand cmd = _conn.CreateCommand();
if (_trans != null)
{
cmd.Transaction = _trans;
}
cmd.CommandText = sql;
if (timeout.HasValue)
{
cmd.CommandTimeout = timeout.Value;
}
else if (_timeout > 0)
{
cmd.CommandTimeout = _timeout;
}
cmd.CommandType = CommandType.Text;
ad = DbProviderFactories.GetFactory(_prov_name).CreateDataAdapter();
ad.SelectCommand = cmd;
object ds = dataset ? (object)new DataSet() :
(table_name != "" ? (object)new DataTable(table_name) : (object)new DataTable());
if (dataset)
{
if (with_schema)
{
throw new Exception("funzionalità non supportata!");
}
ad.Fill((DataSet)ds, table_name != "" ? table_name : "table1");
}
else
{
if (with_schema)
{
ad.FillSchema((DataTable)ds, SchemaType.Source);
}
ad.Fill((DataTable)ds);
}
return(ds);
} catch (Exception ex) { log_err(sql); log_err(ex); ad = null; if (throwerr)
{
throw ex;
}
else
{
return(null);
} } finally { if (opened)
{
close_conn(no_log);
}
}
}
private void Page_Load(object sender, System.EventArgs e)
{
gID = Sql.ToGuid(Request["ID"]);
Guid gBUG_ID = Sql.ToGuid(txtBUG_ID.Value);
if (!Sql.IsEmptyGuid(gBUG_ID))
{
try
{
SqlProcs.spEMAILS_BUGS_Update(gID, gBUG_ID);
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwEMAILS_BUGS " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 11/27/2006 Paul. Make sure to filter relationship data based on team access rights.
Security.Filter(cmd, m_sMODULE, "list");
cmd.CommandText += " and EMAIL_ID = @EMAIL_ID" + ControlChars.CrLf;
cmd.CommandText += " order by BUG_NUMBER " + ControlChars.CrLf;
Sql.AddParameter(cmd, "@EMAIL_ID", gID);
if (bDebug)
{
RegisterClientScriptBlock("vwEMAILS_BUGS", Sql.ClientScriptBlock(cmd));
}
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
// 09/05/2005 Paul. LinkButton controls will not fire an event unless the the grid is bound.
//if ( !IsPostBack )
{
grdMain.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
protected override DbCommandBuilder GetDbCommandBuilder(DbDataAdapter adapter)
{
return(new SqlCommandBuilder(adapter as SqlDataAdapter));
}
/// <summary>
/// See <see cref="IProvider"> IProvider interface </see>
/// </summary>
public override object NewCommandBuilder(DbDataAdapter dataAdapter)
{
return(new FbCommandBuilder((FbDataAdapter)dataAdapter));
}
protected override void SetRowUpdatingHandler(DbDataAdapter adapter)
{
this.InnerBuilder.DataAdapter = adapter as DDTek.Oracle.OracleDataAdapter;
}
public abstract DbCommandBuilder CreateCommandBuilder(DbDataAdapter pDa);
public static Task <DataSet> ToDataSetAsync(this DbDataAdapter dbDataAdapter) => new Task <DataSet>(dbDataAdapter.ToDataSet);
protected void Page_Command(Object sender, CommandEventArgs e)
{
if (e.CommandName == "Save")
{
Guid gDuplicateID = Sql.ToGuid(Request["DuplicateID"]);
if (!Sql.IsEmptyGuid(gID) || !Sql.IsEmptyGuid(gDuplicateID))
{
// 03/04/2006 Paul. The Enable flag cannot be modified prior to ValidateEditViewFields because the change will get over-ridden.
//RequiredFieldValidator reqCONTENT = FindControl("CONTENT_REQUIRED" ) as RequiredFieldValidator;
//RequiredFieldValidator reqREVISION = FindControl("REVISION_REQUIRED") as RequiredFieldValidator;
//if ( reqCONTENT != null )
// reqCONTENT.Enabled = false;
//if ( reqREVISION != null )
// reqREVISION.Enabled = false;
}
// 01/16/2006 Paul. Enable validator before validating page.
this.ValidateEditViewFields(m_sMODULE + ".EditView");
if (Page.IsValid)
{
string sCUSTOM_MODULE = "DOCUMENTS";
DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sCUSTOM_MODULE);
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
// 11/18/2007 Paul. Use the current values for any that are not defined in the edit view.
DataRow rowCurrent = null;
DataTable dtCurrent = new DataTable();
if (!Sql.IsEmptyGuid(gID))
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwDOCUMENTS_Edit" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Security.Filter(cmd, m_sMODULE, "edit");
Sql.AppendParameter(cmd, gID, "ID", false);
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
da.Fill(dtCurrent);
if (dtCurrent.Rows.Count > 0)
{
rowCurrent = dtCurrent.Rows[0];
}
else
{
// 11/19/2007 Paul. If the record is not found, clear the ID so that the record cannot be updated.
// It is possible that the record exists, but that ACL rules prevent it from being selected.
gID = Guid.Empty;
}
}
}
}
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
HtmlInputFile fileCONTENT = FindControl("CONTENT") as HtmlInputFile;
HttpPostedFile pstCONTENT = null;
if (fileCONTENT != null)
{
pstCONTENT = fileCONTENT.PostedFile;
}
// 03/04/2006 Paul. This is a new document if gID and gDuplicateID are both empty.
bool bNewDocument = Sql.IsEmptyGuid(gID) && Sql.IsEmptyGuid(gDuplicateID);
if (bNewDocument)
{
//die("ERROR: uploaded file was too big: max filesize: {$sugar_config['upload_maxsize']}");
if (pstCONTENT != null)
{
long lFileSize = pstCONTENT.ContentLength;
long lUploadMaxSize = Sql.ToLong(Application["CONFIG.upload_maxsize"]);
if ((lUploadMaxSize > 0) && (lFileSize > lUploadMaxSize))
{
throw(new Exception("ERROR: uploaded file was too big: max filesize: " + lUploadMaxSize.ToString()));
}
}
}
// 04/24/2006 Paul. Upgrade to SugarCRM 4.2 Schema.
// 11/18/2007 Paul. Use the current values for any that are not defined in the edit view.
// 12/29/2007 Paul. TEAM_ID is now in the stored procedure.
SqlProcs.spDOCUMENTS_Update
(ref gID
, new DynamicControl(this, rowCurrent, "DOCUMENT_NAME").Text
, new DynamicControl(this, rowCurrent, "ACTIVE_DATE").DateValue
, new DynamicControl(this, rowCurrent, "EXP_DATE").DateValue
, new DynamicControl(this, rowCurrent, "CATEGORY_ID").SelectedValue
, new DynamicControl(this, rowCurrent, "SUBCATEGORY_ID").SelectedValue
, new DynamicControl(this, rowCurrent, "STATUS_ID").SelectedValue
, new DynamicControl(this, rowCurrent, "DESCRIPTION").Text
, new DynamicControl(this, rowCurrent, "MAIL_MERGE_DOCUMENT").Checked
, new DynamicControl(this, rowCurrent, "RELATED_DOC_ID").ID
, new DynamicControl(this, rowCurrent, "RELATED_DOC_REV_ID").ID
, new DynamicControl(this, rowCurrent, "IS_TEMPLATE").Checked
, new DynamicControl(this, rowCurrent, "TEMPLATE_TYPE").Text
, new DynamicControl(this, rowCurrent, "TEAM_ID").ID
, trn
);
if (bNewDocument)
{
if (pstCONTENT != null)
{
// 08/20/2005 Paul. File may not have been provided.
if (pstCONTENT.FileName.Length > 0)
{
string sFILENAME = Path.GetFileName(pstCONTENT.FileName);
string sFILE_EXT = Path.GetExtension(sFILENAME);
string sFILE_MIME_TYPE = pstCONTENT.ContentType;
Guid gRevisionID = Guid.Empty;
// 01/16/2006 Paul. spDOCUMENT_REVISIONS_Insert needs to be in the transaction,
// otherwise the entire transaction will timeout. This is because the transaction has
// locked the tables that are needed by spDOCUMENT_REVISIONS_Insert.
SqlProcs.spDOCUMENT_REVISIONS_Insert
(ref gRevisionID
, gID
, new DynamicControl(this, "REVISION").Text
, "Document Created"
, sFILENAME
, sFILE_EXT
, sFILE_MIME_TYPE
, trn
);
LoadFile(gRevisionID, pstCONTENT.InputStream, trn);
}
}
}
else if (Sql.IsEmptyGuid(Request["ID"]) && !Sql.IsEmptyGuid(gDuplicateID))
{
Guid gRevisionID = Guid.Empty;
// 03/04/2006 Paul. We need a separate procedure to copy the document stored in the revisions table.
SqlProcs.spDOCUMENT_REVISIONS_Duplicate
(ref gRevisionID
, gID
, gDuplicateID
, new DynamicControl(this, "REVISION").Text
, "Document Created"
, trn
);
}
SplendidDynamic.UpdateCustomFields(this, trn, gID, sCUSTOM_MODULE, dtCustomFields);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
ctlEditButtons.ErrorText = ex.Message;
return;
}
}
}
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
else if (e.CommandName == "Cancel")
{
if (Sql.IsEmptyGuid(gID))
{
Response.Redirect("default.aspx");
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
public void select(DbConnection conn)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
DataTable tb = new DataTable();
DbCommand dbCommand = conn.CreateCommand();
string selectQuery = KOM_PSIT_INF_Table_DAOManager.SelectQuery_;
//selectQuery.Replace("@KeyColumnValue@", this.KeyColumn_);
dbCommand.CommandText = selectQuery;
//DbDataAdapter dataAdapter = new DbDataAdapter(dbCommand.CommandText, conn);
DbDataAdapter dataAdapter = DataBaseConnectManager.CreateDataAdapter(dbCommand, conn);
dataAdapter.Fill(tb);
DataRow[] dr = tb.Select();
int rowCount = dr.Length;
foreach (DataRow item in dr)
{
KOM_PSIT_INF_Table_DAO dao = new KOM_PSIT_INF_Table_DAO();
dao.BASC_DT_ = item[0].ToString();
dao.OFFC_CD_ = item[1].ToString();
dao.SEQ_NO_ = item[2].ToString();
dao.POSITION_ID_ = item[3].ToString();
dao.KR_CD_ = item[4].ToString();
dao.BCOF_ITM_NM_ = item[5].ToString();
dao.ISD_FUND_CD_ = item[6].ToString();
dao.CRPN_ID_ = item[7].ToString();
dao.CRPN_NM_ = item[8].ToString();
dao.DEAL_TYP_ = item[9].ToString();
dao.HOLD_QNT_ = item[10].ToString();
dao.BCOF_EXHG_RT_CD_ = item[11].ToString();
dao.PURT_AMT_ = item[12].ToString();
dao.ACBK_UPRC_ = item[13].ToString();
dao.FRC_ACBK_UPRC_ = item[14].ToString();
dao.MKT_UPRC_ = item[15].ToString();
dao.FRC_MKT_UPRC_ = item[16].ToString();
dao.FCVL_UNIT_ = item[17].ToString();
dao.FRC_FCVL_UNIT_ = item[18].ToString();
dao.FCVL_AMT_ = item[19].ToString();
dao.FRC_FCVL_AMT_ = item[20].ToString();
dao.ACBK_AMT_ = item[21].ToString();
dao.FRC_ACBK_AMT_ = item[22].ToString();
dao.MKT_AMT_ = item[23].ToString();
dao.FRC_MKT_AMT_ = item[24].ToString();
dao.WRNT_AMT_ = item[25].ToString();
dao.HOLD_MAX_QNT_ = item[26].ToString();
dao.FST_BUY_DT_ = item[27].ToString();
dao.BUY_UPRC_ = item[28].ToString();
dao.FST_BUY_AMT_ = item[29].ToString();
dao.FST_FRC_ACBK_AMT_ = item[30].ToString();
dao.DD_BY_ASES_LNG_ = item[31].ToString();
dao.DD_BY_INT_LNG_ = item[32].ToString();
dao.DD_BY_DEAL_LNG_ = item[33].ToString();
dao.DD_BY_DIVD_LNG_ = item[34].ToString();
dao.DD_EXP_ = item[35].ToString();
dao.ASES_LNG_ = item[36].ToString();
dao.ETC_PL_AMT_1_ = item[37].ToString();
dao.ETC_PL_AMT_2_ = item[38].ToString();
dao.ETC_PL_AMT_3_ = item[39].ToString();
dao.ETC_PL_AMT_4_ = item[40].ToString();
dao.ETC_PL_AMT_5_ = item[41].ToString();
dao.ETC_PL_AMT_6_ = item[42].ToString();
dao.ETC_PL_AMT_7_ = item[43].ToString();
dao.ETC_PL_AMT_8_ = item[44].ToString();
dao.ETC_PL_AMT_9_ = item[45].ToString();
dao.ETC_PL_AMT_10_ = item[46].ToString();
dao.ETC_PL_AMT_11_ = item[47].ToString();
dao.ETC_PL_AMT_12_ = item[48].ToString();
dao.ETC_PL_AMT_13_ = item[49].ToString();
dao.ETC_PL_AMT_14_ = item[50].ToString();
dao.ETC_PL_AMT_15_ = item[51].ToString();
dao.ETC_PL_AMT_16_ = item[52].ToString();
dao.ETC_PL_AMT_17_ = item[53].ToString();
dao.ETC_PL_AMT_18_ = item[54].ToString();
dao.ETC_PL_AMT_19_ = item[55].ToString();
dao.ETC_PL_AMT_20_ = item[56].ToString();
dao.ETC_PL_AMT_21_ = item[57].ToString();
dao.ETC_PL_AMT_22_ = item[58].ToString();
dao.ETC_PL_AMT_23_ = item[59].ToString();
dao.ETC_PL_AMT_24_ = item[60].ToString();
dao.ETC_PL_AMT_25_ = item[61].ToString();
dao.BCOF_AC_CD_1_ = item[62].ToString();
dao.BCOF_PRD_CD_1_ = item[63].ToString();
dao.BCOF_PRD_CD_2_ = item[64].ToString();
dao.BCOF_PRD_CD_3_ = item[65].ToString();
dao.CLTR_OFFR_AMT_ = item[66].ToString();
dao.CLTR_OFFR_YN_ = item[67].ToString();
dao.MKT_PRC_ASES_CD_ = item[68].ToString();
dao.OPT_EVET_YN_ = item[69].ToString();
dao.HDG_TRSC_YN_ = item[70].ToString();
dao.TST_ISS_TYP_ = item[71].ToString();
dao.USER_SET_YN_ = item[72].ToString();
dao.NOTE_ = item[73].ToString();
dao.FILE_DT_ = item[74].ToString();
dao.DB_INSERT_DT_ = item[75].ToString();
this.DAOList_.Add(dao);
}
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term("Config.LBL_LIST_FORM_TITLE"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = SplendidCRM.Security.IS_ADMIN;
if (!this.Visible)
{
return;
}
try
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwCONFIG_List" + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
ctlSearch.SqlSearchClause(cmd);
if (bDebug)
{
RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
}
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
// 12/14/2007 Paul. Only set the default sort if it is not already set. It may have been set by SearchView.
if (String.IsNullOrEmpty(grdMain.SortColumn))
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
}
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
/// <summary>
/// Sets the RowUpdated event for the data adapter.
/// </summary>
/// <param name="adapter">The <see cref="DbDataAdapter"/> to set the event.</param>
/// <remarks>The <see cref="DbDataAdapter"/> must be an <see cref="OracleDataAdapter"/>.</remarks>
protected override void SetUpRowUpdatedEvent(DbDataAdapter adapter)
{
((OracleDataAdapter)adapter).RowUpdated += new OracleRowUpdatedEventHandler(OnOracleRowUpdated);
}
// <Snippet1>
static void CreateDataAdapter(string providerName, string connectionString)
{
try
{
// Create the DbProviderFactory and DbConnection.
DbProviderFactory factory =
DbProviderFactories.GetFactory(providerName);
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
using (connection)
{
// Define the query.
string queryString =
"SELECT CustomerID, CompanyName FROM Customers";
// Create the select command.
DbCommand command = factory.CreateCommand();
command.CommandText = queryString;
command.Connection = connection;
// Create the DbDataAdapter.
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
// Create the DbCommandBuilder.
DbCommandBuilder builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
// Get the insert, update and delete commands.
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
// Display the CommandText for each command.
Console.WriteLine("InsertCommand: {0}",
adapter.InsertCommand.CommandText);
Console.WriteLine("UpdateCommand: {0}",
adapter.UpdateCommand.CommandText);
Console.WriteLine("DeleteCommand: {0}",
adapter.DeleteCommand.CommandText);
// Fill the DataTable.
DataTable table = new DataTable();
adapter.Fill(table);
// Insert a new row.
DataRow newRow = table.NewRow();
newRow["CustomerID"] = "XYZZZ";
newRow["CompanyName"] = "XYZ Company";
table.Rows.Add(newRow);
adapter.Update(table);
// Display rows after insert.
Console.WriteLine();
Console.WriteLine("----List All Rows-----");
foreach (DataRow row in table.Rows)
{
Console.WriteLine("{0} {1}", row[0], row[1]);
}
Console.WriteLine("----After Insert-----");
// Edit an existing row.
DataRow[] editRow = table.Select("CustomerID = 'XYZZZ'");
editRow[0]["CompanyName"] = "XYZ Corporation";
adapter.Update(table);
// Display rows after update.
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
Console.WriteLine("{0} {1}", row[0], row[1]);
}
Console.WriteLine("----After Update-----");
// Delete a row.
DataRow[] deleteRow = table.Select("CustomerID = 'XYZZZ'");
foreach (DataRow row in deleteRow)
{
row.Delete();
}
adapter.Update(table);
// Display rows after delete.
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
Console.WriteLine("{0} {1}", row[0], row[1]);
}
Console.WriteLine("----After Delete-----");
Console.WriteLine("Customer XYZZZ was deleted.");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term("Contacts.LBL_LIST_FORM_TITLE"));
if (!IsPostBack)
{
// 07/02/2006 Paul. Quote needs to select only Contacts for a specified Account.
new DynamicControl(ctlSearchView, "ACCOUNT_NAME").Text = Sql.ToString(Request["ACCOUNT_NAME"]);
}
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwCONTACTS_List" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 11/24/2006 Paul. Use new Security.Filter() function to apply Team and ACL security rules.
Security.Filter(cmd, "Contacts", "list");
ctlSearchView.SqlSearchClause(cmd);
if (bDebug)
{
Page.ClientScript.RegisterClientScriptBlock(System.Type.GetType("System.String"), "SQLCode", Sql.ClientScriptBlock(cmd));
}
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
// 12/14/2007 Paul. Only set the default sort if it is not already set. It may have been set by SearchView.
if (String.IsNullOrEmpty(grdMain.SortColumn))
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
}
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
Page.DataBind();
}
}
private void Page_Load(object sender, System.EventArgs e)
{
Utils.SetPageTitle(Page, L10n.Term(m_sMODULE + ".LBL_LIST_FORM_TITLE"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "list") >= 0);
if (!this.Visible)
{
return;
}
try
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwTASKS_List" + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
int nACLACCESS = Security.GetUserAccess(m_sMODULE, "list");
if (nACLACCESS == ACL_ACCESS.OWNER)
{
Sql.AppendParameter(cmd, Security.USER_ID, "ASSIGNED_USER_ID", false);
}
ctlSearch.SqlSearchClause(cmd);
#if DEBUG
Page.RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
#endif
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
grdMain.SortColumn = "DATE_DUE";
grdMain.SortOrder = "desc";
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
public iSqlCmdFormGrid(DbDataAdapter pAdapter) : this(pAdapter, null, null, null, null)
{
}
private void InitAdapterCommands(string tableName, DbDataAdapter adapter, IDbConnection connection)
{
DataTableMapping tableMapping = new DataTableMapping();
tableMapping.SourceTable = "Table";
if (tableName.Equals("orders", StringComparison.InvariantCultureIgnoreCase))
{
tableMapping.DataSetTable = "orders";
tableMapping.ColumnMappings.Add("order_id", "order_id");
tableMapping.ColumnMappings.Add("order_date", "order_date");
adapter.TableMappings.Add(tableMapping);
adapter.DeleteCommand = CastAndSetConnection(connection);
adapter.DeleteCommand.CommandText = "DELETE FROM [orders] WHERE [order_id] = @original_order_id";
adapter.DeleteCommand.CommandType = CommandType.Text;
SqlParameter originalOrderIdParam = new SqlParameter("@original_order_id", SqlDbType.Int, 4, "order_id");
originalOrderIdParam.SourceVersion = DataRowVersion.Original;
adapter.DeleteCommand.Parameters.Add(originalOrderIdParam);
adapter.InsertCommand = CastAndSetConnection(connection);
adapter.InsertCommand.CommandText = "INSERT INTO [orders] ([order_id], [order_date]) VALUES (@order_id, @order_date)";
adapter.InsertCommand.CommandType = CommandType.Text;
adapter.InsertCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int, 4, "order_id"));
adapter.InsertCommand.Parameters.Add(new SqlParameter("@order_date", SqlDbType.DateTime, 8, "order_date"));
adapter.UpdateCommand = CastAndSetConnection(connection);
adapter.UpdateCommand.CommandText = @"UPDATE [orders] SET [order_id] = @order_id, [order_date] = @order_date " +
"WHERE [order_id] = @original_order_id";
adapter.UpdateCommand.CommandType = CommandType.Text;
adapter.UpdateCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int, 4, "order_id"));
adapter.UpdateCommand.Parameters.Add(new SqlParameter("@order_date", SqlDbType.DateTime, 8, "order_date"));
originalOrderIdParam = new SqlParameter("@original_order_id", SqlDbType.Int, 4, "order_id");
originalOrderIdParam.SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand.Parameters.Add(originalOrderIdParam);
}
else
{
tableMapping.DataSetTable = "order_details";
tableMapping.ColumnMappings.Add("order_id", "order_id");
tableMapping.ColumnMappings.Add("order_details_id", "order_details_id");
tableMapping.ColumnMappings.Add("product", "product");
tableMapping.ColumnMappings.Add("quantity", "quantity");
adapter.TableMappings.Add(tableMapping);
adapter.DeleteCommand = CastAndSetConnection(connection);
adapter.DeleteCommand.CommandText = @"DELETE FROM [order_details] WHERE [order_details_id] = @original_order_details_id";
adapter.DeleteCommand.CommandType = CommandType.Text;
SqlParameter originalOrderDetailsIdParam = new SqlParameter("@original_order_details_id", SqlDbType.Int, 4, "order_details_id");
originalOrderDetailsIdParam.SourceVersion = DataRowVersion.Original;
adapter.DeleteCommand.Parameters.Add(originalOrderDetailsIdParam);
adapter.InsertCommand = CastAndSetConnection(connection);
adapter.InsertCommand.CommandText = @"INSERT INTO [order_details] ([order_id], [order_details_id], [product], [quantity]) VALUES " +
"(@order_id, @order_details_id, @product, @quantity)";
adapter.InsertCommand.CommandType = CommandType.Text;
adapter.InsertCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int, 4, "order_id"));
adapter.InsertCommand.Parameters.Add(new SqlParameter("@order_details_id", SqlDbType.Int, 4, "order_details_id"));
adapter.InsertCommand.Parameters.Add(new SqlParameter("@product", SqlDbType.NVarChar, 100, "product"));
adapter.InsertCommand.Parameters.Add(new SqlParameter("@quantity", SqlDbType.Int, 4, "quantity"));
adapter.UpdateCommand = CastAndSetConnection(connection);
adapter.UpdateCommand.CommandText = @"UPDATE [order_details] SET [order_id] = @order_id, [order_details_id] = @order_details_id, " +
"[product] = @product, [quantity] = @quantity WHERE [order_details_id] = @original_order_details_id";
adapter.UpdateCommand.CommandType = CommandType.Text;
adapter.UpdateCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int, 4, "order_id"));
adapter.UpdateCommand.Parameters.Add(new SqlParameter("@order_details_id", SqlDbType.Int, 4, "order_details_id"));
adapter.UpdateCommand.Parameters.Add(new SqlParameter("@product", SqlDbType.NVarChar, 100, "product"));
adapter.UpdateCommand.Parameters.Add(new SqlParameter("@quantity", SqlDbType.Int, 4, "quantity"));
originalOrderDetailsIdParam = new SqlParameter("@original_order_details_id", SqlDbType.Int, 4, "order_details_id");
originalOrderDetailsIdParam.SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand.Parameters.Add(originalOrderDetailsIdParam);
}
}
public virtual DataTable dt_table(string sql, string table_name, out DbDataAdapter ad, bool throwerr = true, bool no_log = false)
{
return(data_table(sql, out ad, table_name, throwerr, no_log: no_log));
}
/// <summary>
/// Initializes the ODBC db data adapter
/// </summary>
/// <param name="adapter">DbDataAdapter to be initialized</param>
/// <param name="command">DbCommand to be initialized</param>
private void InitializeODBCDbDataAdapter(ref DbDataAdapter adapter, DbCommand command)
{
adapter = new OdbcDataAdapter((OdbcCommand)command);
}
protected object open_set(string sql, bool throwerr = true, bool dataset = true, string table_name = "", bool with_schema = false, bool no_log = false, int?timeout = null)
{
DbDataAdapter ad = null; return(open_set_ad(sql, out ad, throwerr, dataset, table_name, with_schema, no_log, timeout));
}
private void Page_Load(object sender, System.EventArgs e)
{
gID = Sql.ToGuid(Request["ID"]);
// 03/08/2007 Paul. We need to disable the buttons unless the user is an administrator.
if (bMyAccount)
{
btnSelectRole.Visible = Security.IS_ADMIN;
gID = Security.USER_ID;
}
if (!Sql.IsEmptyString(txtROLE_ID.Value))
{
try
{
SqlProcs.spUSERS_ACL_ROLES_MassUpdate(gID, txtROLE_ID.Value);
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwUSERS_ACL_ROLES " + ControlChars.CrLf
+ " where USER_ID = @USER_ID" + ControlChars.CrLf
+ " order by ROLE_NAME asc " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@USER_ID", gID);
if (bDebug)
{
RegisterClientScriptBlock("vwUSER_ACL_ROLES", Sql.ClientScriptBlock(cmd));
}
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
// 09/05/2005 Paul. LinkButton controls will not fire an event unless the the grid is bound.
//if ( !IsPostBack )
{
grdMain.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
protected object call_sp(string sp_name, List <db_par> pars = null, bool ret_int = false, bool ret_dt = false, int timeout = -1)
{
bool opened = false;
try {
if (open_conn())
{
opened = true;
}
DbCommand cmd = _conn.CreateCommand();
if (timeout > 0)
{
cmd.CommandTimeout = timeout;
}
else if (_timeout > 0)
{
cmd.CommandTimeout = _timeout;
}
if (_trans != null)
{
cmd.Transaction = _trans;
}
cmd.Connection = _conn;
cmd.CommandText = sp_name;
cmd.CommandType = CommandType.StoredProcedure;
if (pars != null)
{
foreach (db_par par in pars)
{
DbParameter param = cmd.CreateParameter();
param.ParameterName = par.Name;
param.Value = par.IsNull ? DBNull.Value : par.Value;
cmd.Parameters.Add(param);
}
}
DbParameter p_ret = null;
if (ret_int)
{
p_ret = cmd.CreateParameter();
p_ret.ParameterName = "@return";
p_ret.DbType = DbType.Int32;
p_ret.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p_ret);
}
log_sql(string.Format("exec {0}({1})", sp_name, string.Join(", ", pars.Select(x => "@" + x.Name + " = " + (x.IsNull ? "NULL" : x.Value.ToString())))));
if (ret_dt)
{
DataTable dt = new DataTable();
DbDataAdapter ad = DbProviderFactories.GetFactory(_prov_name).CreateDataAdapter();
ad.SelectCommand = cmd;
ad.Fill(dt);
return(dt);
}
else
{
cmd.ExecuteNonQuery();
return(ret_int ? Convert.ToInt32(p_ret.Value) : 0);
}
} catch (Exception ex) { log_err(ex, string.Format("s.p. {0}({1})", sp_name, string.Join(", ", pars.Select(x => x.IsNull ? "" : x.Value.ToString())))); throw ex; } finally { if (opened)
{
close_conn();
}
}
}
public DataTable LoadDataTable(ConnectionType connectionType, string connectionString, string sql)
{
DataTable table = new DataTable();
try
{
if (MyLoadDataTable != null)
{
return(MyLoadDataTable(connectionType, connectionString, sql));
}
var connection = Helper.DbConnectionFromConnectionString(connectionType, connectionString);
try
{
connection.Open();
if (UseDbDataAdapter)
{
DbDataAdapter adapter = null;
if (connection is OdbcConnection)
{
adapter = new OdbcDataAdapter(sql, (OdbcConnection)connection);
}
else if (connection is SqlConnection)
{
adapter = new SqlDataAdapter(sql, (SqlConnection)connection);
}
else
{
adapter = new OleDbDataAdapter(sql, (OleDbConnection)connection);
}
adapter.SelectCommand.CommandTimeout = SelectTimeout;
adapter.Fill(table);
}
else
{
DbCommand cmd = null;
if (connection is OdbcConnection)
{
cmd = new OdbcCommand(sql, (OdbcConnection)connection);
}
else if (connection is SqlConnection)
{
cmd = new SqlCommand(sql, (SqlConnection)connection);
}
else
{
cmd = new OleDbCommand(sql, (OleDbConnection)connection);
}
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
DbDataReader dr = cmd.ExecuteReader();
DataTable schemaTable = dr.GetSchemaTable();
foreach (DataRow dataRow in schemaTable.Rows)
{
DataColumn dataColumn = new DataColumn();
dataColumn.ColumnName = dataRow["ColumnName"].ToString();
dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString());
dataColumn.ReadOnly = (bool)dataRow["IsReadOnly"];
dataColumn.AutoIncrement = (bool)dataRow["IsAutoIncrement"];
dataColumn.Unique = (bool)dataRow["IsUnique"];
for (int i = 0; i < table.Columns.Count; i++)
{
if (dataColumn.ColumnName == table.Columns[i].ColumnName)
{
dataColumn.ColumnName += "_" + table.Columns.Count.ToString();
}
}
table.Columns.Add(dataColumn);
}
while (dr.Read())
{
DataRow dataRow = table.NewRow();
for (int i = 0; i < table.Columns.Count; i++)
{
dataRow[i] = dr[i];
}
table.Rows.Add(dataRow);
}
dr.Close();
}
}
finally
{
connection.Close();
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Error got when executing '{0}':\r\n{1}\r\n", sql, ex.Message));
}
return(table);
}
private void TestForClusterAnalysis()
{
var common = new SQLCommon
{
SkylineType =
new SkylineBNL()
{
Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString
},
ShowInternalAttributes = true
};
DbProviderFactory factory = DbProviderFactories.GetFactory(Helper.ProviderName);
// use the factory object to create Data access objects.
DbConnection connection = factory.CreateConnection();
// will return the connection object (i.e. SqlConnection ...)
connection.ConnectionString = Helper.ConnectionString;
var dt = new DataTable();
connection.Open();
DbDataAdapter dap = factory.CreateDataAdapter();
DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandTimeout = 0; //infinite timeout
string strQuery;
string operators;
int numberOfRecords;
string[] parameter;
string ansiSql =
common.GetAnsiSqlFromPrefSqlModel(common.GetPrefSqlModelFromPreferenceSql(_entireSkylineSql));
prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out strQuery, out operators,
out numberOfRecords);
selectCommand.CommandText = strQuery;
dap.SelectCommand = selectCommand;
dt = new DataTable();
dap.Fill(dt);
DataTable entireSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName,
_entireSkylineSql);
int[] skylineAttributeColumns = SkylineSamplingHelper.GetSkylineAttributeColumns(entireSkylineDataTable);
IReadOnlyDictionary <long, object[]> entireSkylineNormalized =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTable, 0);
SkylineSamplingHelper.NormalizeColumns(entireSkylineNormalized, skylineAttributeColumns);
DataTable sampleSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
Helper.ProviderName,
_skylineSampleSql);
IReadOnlyDictionary <long, object[]> sampleSkylineNormalized =
prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(sampleSkylineDataTable, 0);
SkylineSamplingHelper.NormalizeColumns(sampleSkylineNormalized, skylineAttributeColumns);
for (var i = 0; i < skylineAttributeColumns.Length; i++)
{
dt.Columns.RemoveAt(0);
}
IReadOnlyDictionary <long, object[]> full = prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(dt, 0);
SkylineSamplingHelper.NormalizeColumns(full, skylineAttributeColumns);
ClusterAnalysis.CalcMedians(full, skylineAttributeColumns);
IReadOnlyDictionary <BigInteger, List <IReadOnlyDictionary <long, object[]> > > entireBuckets =
ClusterAnalysis.GetBuckets(entireSkylineNormalized, skylineAttributeColumns);
IReadOnlyDictionary <BigInteger, List <IReadOnlyDictionary <long, object[]> > > sampleBuckets =
ClusterAnalysis.GetBuckets(sampleSkylineNormalized, skylineAttributeColumns);
//IReadOnlyDictionary<int, List<IReadOnlyDictionary<long, object[]>>> aggregatedEntireBuckets =
// ClusterAnalysis.GetAggregatedBuckets(entireBuckets);
//IReadOnlyDictionary<int, List<IReadOnlyDictionary<long, object[]>>> aggregatedSampleBuckets =
// ClusterAnalysis.GetAggregatedBuckets(sampleBuckets);
IReadOnlyDictionary <BigInteger, List <IReadOnlyDictionary <long, object[]> > > fullB =
ClusterAnalysis.GetBuckets(full, skylineAttributeColumns);
// IReadOnlyDictionary<int, List<IReadOnlyDictionary<long, object[]>>> aFullB =
// ClusterAnalysis.GetAggregatedBuckets(fullB);
IOrderedEnumerable <KeyValuePair <BigInteger, List <IReadOnlyDictionary <long, object[]> > > > sorted = fullB.OrderBy(l => l.Value.Count)
.ThenBy(l => l.Key);
int len = Convert.ToInt32(Math.Pow(2, skylineAttributeColumns.Length));
//for (var i = 0; i < len; i++)
foreach (KeyValuePair <BigInteger, List <IReadOnlyDictionary <long, object[]> > > s in sorted)
{
BigInteger i = s.Key;
int entire = entireBuckets.ContainsKey(i) ? entireBuckets[i].Count : 0;
int sample = sampleBuckets.ContainsKey(i) ? sampleBuckets[i].Count : 0;
double entirePercent = (double)entire / entireSkylineNormalized.Count;
double samplePercent = (double)sample / sampleSkylineNormalized.Count;
int fullX = fullB.ContainsKey(i) ? fullB[i].Count : 0;
double fullP = (double)fullX / full.Count;
Console.WriteLine("-- {0,5} -- {5,6} ({6,7:P2} %) -- {1,6} ({3,7:P2} %) -- {2,6} ({4,7:P2} %)", i,
entire, sample, entirePercent,
samplePercent, fullX, fullP);
}
Console.WriteLine();
Console.WriteLine("{0} - {1} - {2}", entireSkylineNormalized.Count, sampleSkylineNormalized.Count,
full.Count);
}
private void Page_Load(object sender, System.EventArgs e)
{
gID = Sql.ToGuid(Request["ID"]);
Guid gPROSPECT_LIST_ID = Sql.ToGuid(txtPROSPECT_LIST_ID.Value);
if (!Sql.IsEmptyGuid(gPROSPECT_LIST_ID))
{
try
{
SqlProcs.spPROSPECT_LIST_CAMPAIGNS_Update(gPROSPECT_LIST_ID, gID);
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwCAMPAIGNS_PROSPECT_LISTS" + ControlChars.CrLf
+ " where CAMPAIGN_ID = @CAMPAIGN_ID" + ControlChars.CrLf
+ " order by DATE_ENTERED asc " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@CAMPAIGN_ID", gID);
if (bDebug)
{
RegisterClientScriptBlock("vwCAMPAIGNS_PROSPECT_LISTS", Sql.ClientScriptBlock(cmd));
}
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
// 09/05/2005 Paul. LinkButton controls will not fire an event unless the the grid is bound.
//if ( !IsPostBack )
{
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
private DbDataAdapter CrearAdaptador()
{
DbDataAdapter datAdaptador = dpfProxy.CreateDataAdapter();
return(datAdaptador);
}
public List <T> ExecuteList <T>(string sql, IDataMapper <T> rowMapper,
int index, int size)
{
if (string.IsNullOrEmpty(sql))
{
throw new Exception("Sql is empty");
}
if (rowMapper == null)
{
throw new Exception("Mapper is null");
}
List <T> list = new List <T>();
IDataReader rdr = null;
DataSet dataSet = null;
DbCommand cmd = null;
DbDataAdapter da = null;
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory(dataSource.Provider);
da = factory.CreateDataAdapter();
cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
dataSet = new DataSet();
da.SelectCommand = cmd;
da.Fill(dataSet, index, size, typeof(T).ToString());
rdr = dataSet.Tables[typeof(T).ToString()].CreateDataReader();
while (rdr.Read())
{
list.Add(rowMapper.Map(rdr));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (da != null)
{
da.Dispose();
}
if (dataSet != null)
{
dataSet.Dispose();
}
if (rdr != null)
{
rdr.Close();
}
}
return(list);
}
