コード例 #1
0
        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);
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        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]);
                }
            }
        }
コード例 #4
0
ファイル: db_conn.cs プロジェクト: stonetempledev/root
 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));
 }
コード例 #5
0
ファイル: db_conn.cs プロジェクト: stonetempledev/root
        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);
                                                   }
            }
        }
コード例 #6
0
        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();
            }
        }
コード例 #7
0
ファイル: SqlEarly.cs プロジェクト: yuyixg/EasyFrameWork
 protected override DbCommandBuilder GetDbCommandBuilder(DbDataAdapter adapter)
 {
     return(new SqlCommandBuilder(adapter as SqlDataAdapter));
 }
コード例 #8
0
 /// <summary>
 /// See <see cref="IProvider"> IProvider interface </see>
 /// </summary>
 public override object NewCommandBuilder(DbDataAdapter dataAdapter)
 {
     return(new FbCommandBuilder((FbDataAdapter)dataAdapter));
 }
コード例 #9
0
ファイル: HyOracleCommandBuilder.cs プロジェクト: zj8487/HyDM
 protected override void SetRowUpdatingHandler(DbDataAdapter adapter)
 {
     this.InnerBuilder.DataAdapter = adapter as DDTek.Oracle.OracleDataAdapter;
 }
コード例 #10
0
ファイル: DbSession.cs プロジェクト: tskycc/DataScriptWriter
 public abstract DbCommandBuilder CreateCommandBuilder(DbDataAdapter pDa);
コード例 #11
0
ファイル: DataSetFunctions.cs プロジェクト: khazeley/DataFunc
 public static Task <DataSet> ToDataSetAsync(this DbDataAdapter dbDataAdapter) => new Task <DataSet>(dbDataAdapter.ToDataSet);
コード例 #12
0
        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());
                }
            }
        }
コード例 #13
0
        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);
            }
        }
コード例 #14
0
        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();
            }
        }
コード例 #15
0
 /// <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);
 }
コード例 #16
0
    // <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);
        }
    }
コード例 #17
0
        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();
            }
        }
コード例 #18
0
        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();
            }
        }
コード例 #19
0
 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);
            }
        }
コード例 #21
0
ファイル: db_conn.cs プロジェクト: stonetempledev/root
 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));
 }
コード例 #22
0
 /// <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);
 }
コード例 #23
0
ファイル: db_conn.cs プロジェクト: stonetempledev/root
 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));
 }
コード例 #24
0
        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();
            }
        }
コード例 #25
0
ファイル: db_conn.cs プロジェクト: stonetempledev/root
        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();
                                                                                                                                                                                          }
            }
        }
コード例 #26
0
        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);
        }
コード例 #27
0
        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);
        }
コード例 #28
0
        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();
            }
        }
コード例 #29
0
        private DbDataAdapter CrearAdaptador()
        {
            DbDataAdapter datAdaptador = dpfProxy.CreateDataAdapter();

            return(datAdaptador);
        }
コード例 #30
0
        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);
        }