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); }