C# IDBManager.CreateParameters方法代码示例

        private void FetchCableList(int id, IDBManager dbmgr)
        {
            string qryString = "SELECT * FROM viewCABROUTE_CHKD c WHERE c.NODE_ID = @node_id ORDER BY c.CABLE";
            CabRoute item = new CabRoute();							                        // create new object type to be able to get property info
            ArrayList list = new ArrayList();										        // create new ArrayList to house objects
            PropertyInfo[] p = item.GetType().GetProperties();					            // get property info for item

            dbmgr.CreateParameters(1);											            // create parameters
            dbmgr.AddParameters(0, "@node_id", id);								            // node id
            dbmgr.ExecuteReader(CommandType.Text, qryString);					            // execute query

            while (dbmgr.DataReader.Read())
            {
                item = new CabRoute();											            // create new item
                item = (CabRoute)FetchObject(item, p, dbmgr);
                list.Add(item);													            // add item to the ArrayList
            }

            dbmgr.CloseReader();
            _route.cablelist = list;										                // update item list
        }

        private void UpdateFireArea(object obj, IDBManager dbmgr)
        {
            FireArea item = (FireArea)obj;
            System.Data.Common.DbParameter param;
            try
            {
                dbmgr.Open();
                dbmgr.BeginTransaction();

                // check to see if new object
                if (item.fa_id == 0)
                {
                    dbmgr.CreateParameters(3);
                    dbmgr.AddParameters(0, "@id", 0, true);
                    dbmgr.AddParameters(1, "@fa", item.fa);
                    dbmgr.AddParameters(2, "@fa_desc", item.fa_desc);
                    dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.FALIST_i");

                    // get item id
                    param = (System.Data.Common.DbParameter)dbmgr.Parameters.GetValue(0);
                    item.fa_id = (int)param.Value;
                }
                // otherwise update existing object
                else
                {
                    dbmgr.CreateParameters(14);
                    dbmgr.AddParameters(0, "@fa_id", item.fa_id);
                    dbmgr.AddParameters(1, "@fa", item.fa);
                    dbmgr.AddParameters(2, "@fa_desc", item.fa_desc);
                    dbmgr.AddParameters(3, "@bldg", item.bldg);
                    dbmgr.AddParameters(4, "@elev", item.elev);
                    dbmgr.AddParameters(5, "@ssd_path", item.ssd_path);
                    dbmgr.AddParameters(6, "@req", item.req);
                    dbmgr.AddParameters(7, "@supp", item.supp);
                    dbmgr.AddParameters(8, "@det", item.det);
                    dbmgr.AddParameters(9, "@comment", item.comment);
                    dbmgr.AddParameters(10, "@dwg_id", item.dwg_id);
                    dbmgr.AddParameters(11, "@dwg_rev", item.dwg_rev);
                    dbmgr.AddParameters(12, "@risk_summary", item.risk_summary);
                    dbmgr.AddParameters(13, "@reg_basis", item.reg_basis);
                    dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.FALIST_u");	        // execute the stored procedure

                    //update status
                    UpdateStatus(dbmgr, item, 1, item.prep_by, item.prep_date);
                    UpdateStatus(dbmgr, item, 5, item.chkd_by, item.chkd_date);
                }

                dbmgr.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbmgr.RollbackTransaction();
                throw ex;
            }
            finally
            {
                dbmgr.Dispose();
            }
        }

 private void UpdateStatus(IDBManager dbmgr, FireArea item, int statustype_id, string user_id, DateTime status_date)
 {
     if ((user_id == "N/A") || (user_id == ""))
     {
         // delete status
         dbmgr.CreateParameters(2);
         dbmgr.AddParameters(0, "@fa_id", item.fa_id);
         dbmgr.AddParameters(1, "@statustype_id", statustype_id);
         dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.FASTATUS_d");
     }
     else
     {
         // update status
         dbmgr.CreateParameters(4);
         dbmgr.AddParameters(0, "@fa_id", item.fa_id);
         dbmgr.AddParameters(1, "@statustype_id", statustype_id);
         dbmgr.AddParameters(2, "@user_id", user_id);
         if (status_date == Convert.ToDateTime(null))
             // set date to null
             dbmgr.AddParameters(3, "@status_date", null);
         else
             // update date
             dbmgr.AddParameters(3, "@status_date", status_date);
         dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.FASTATUS_u");
     }
 }

        private void FetchProtectionList(int id, IDBManager dbmgr)
        {
            string qryString = "SELECT * FROM viewFZPROTECTION WHERE FA_ID = @fa_id ORDER BY FZ, SYS_CATEGORY, SYS_NAME";
            FZProtection item = new FZProtection();
            ArrayList list = new ArrayList();

            PropertyInfo[] p = item.GetType().GetProperties();
            dbmgr.CreateParameters(1);
            dbmgr.AddParameters(0, "@fa_id", id);
            dbmgr.ExecuteReader(CommandType.Text, qryString);

            while (dbmgr.DataReader.Read())
            {
                item = new FZProtection();
                item = (FZProtection)FetchObject(item, p, dbmgr);
                list.Add(item);
            }

            dbmgr.CloseReader();
            _firearea.protectionlist = list;
        }

        private void UpdateCompDisp(object obj, IDBManager dbmgr)
        {
            FACompDisp item = (FACompDisp)obj;										        // cast object to proper item type

            try
            {
                dbmgr.Open();
                dbmgr.CreateParameters(5);												    // create required paramaters
                dbmgr.AddParameters(0, "@fa_id", item.fa_id);
                dbmgr.AddParameters(1, "@comp_id", item.comp_id);
                dbmgr.AddParameters(2, "@old_disp_id", item.old_disp_id);
                dbmgr.AddParameters(3, "@disp_id", item.disp_id);
                dbmgr.AddParameters(4, "@status", item.status);
                dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.FACOMPDISP_u");	    // execute stored procedure
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbmgr.Dispose();
            }
        }

        private void FetchCascPowerList(string analysis, int fa_id, IDBManager dbmgr)
        {
            FACascHit item = new FACascHit();											    // create new object type to be able to get property info
            ArrayList list = new ArrayList();										        // create new ArrayList to house objects

            PropertyInfo[] p = item.GetType().GetProperties();					            // get property info for item
            dbmgr.CreateParameters(2);											            // create parameters
            dbmgr.AddParameters(0, "@analysis", analysis);							            // fire area id
            dbmgr.AddParameters(1, "@fa_id", fa_id);
            dbmgr.ExecuteReader(CommandType.StoredProcedure, "dbo.FACASC_POWER_s"); // execute query

            while (dbmgr.DataReader.Read())
            {
                item = new FACascHit();											            // create new item
                item = (FACascHit)FetchObject(item, p, dbmgr);
                list.Add(item);													            // add item to the ArrayList
            }
            dbmgr.CloseReader();
            _firearea.cascpowerlist = list;											        // update item list
        }

        private void FetchFireArea(int id, IDBManager dbmgr)
        {
            string qryLocal = "SELECT * FROM viewFALIST WHERE [email protected] ORDER BY FA";
            dbmgr.CreateParameters(1);
            dbmgr.AddParameters(0, "@id", id);
            dbmgr.ExecuteReader(CommandType.Text, qryLocal);
            if (dbmgr.DataReader.Read())
            {
                PropertyInfo[] p = _firearea.GetType().GetProperties();
                _firearea = (FireArea)FetchObject(_firearea, p, dbmgr);
            }

            dbmgr.CloseReader();
        }

        private void UpdateDrawing(object obj, IDBManager dbmgr)
        {
            RoutelocaDwg item = (RoutelocaDwg)obj;							                // cast object to proper item type

            try
            {
                dbmgr.Open();															    // open database
                dbmgr.CreateParameters(5);												    // create required paramaters
                dbmgr.AddParameters(0, "@nodeloca_id", item.nodeloca_id);
                dbmgr.AddParameters(1, "@dwg_id", item.dwg_id);
                dbmgr.AddParameters(2, "@dwg_rev", item.dwg_rev);
                dbmgr.AddParameters(3, "@dwgtype_id", 4);                                   // "Other" drawing type
                dbmgr.AddParameters(4, "@col_ref", item.col_ref);
                dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.ROUTELOCADWGS_u");  // execute stored procedure
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbmgr.Dispose();
            }
        }

        private void UpdateRoute(object obj, IDBManager dbmgr)
        {
            Route item = (Route)obj;
            System.Data.Common.DbParameter param;

            try
            {
                dbmgr.Open();
                dbmgr.BeginTransaction();

                // check to see if new object
                if (item.node_id == 0)
                {
                    // if id is null then new object
                    dbmgr.CreateParameters(2);
                    dbmgr.AddParameters(0, "@id", 0, true);
                    dbmgr.AddParameters(1, "@node", item.node);
                    dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.ROUTELIST_i");

                    // get item id
                    param = (System.Data.Common.DbParameter)dbmgr.Parameters.GetValue(0);
                    item.node_id = Convert.ToInt32(param.Value);
                }

                // otherwise update existing object
                else
                {
                    // update ROUTELIST table
                    dbmgr.CreateParameters(2);
                    dbmgr.AddParameters(0, "@node_id", item.node_id);
                    dbmgr.AddParameters(1, "@node", item.node);
                    dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.ROUTELIST_u");
                }

                dbmgr.CommitTransaction();
            }
            catch (Exception ex)
            {
                // if there is problem with transaction roll back to original
                dbmgr.RollbackTransaction();
                throw ex;
            }
            finally
            {
                dbmgr.Dispose();
            }
        }

        private string Get_BD_ENDPT(int id, IDBManager dbmgr)
        {
            string bd_endpt = "";
            string qryString;

            switch (_user.locaType)
            {
                case Constant.type_FR:
                    qryString = "SELECT * FROM viewBDENDPOINTS_FR AS e WHERE e.NODE_ID = @node_id ORDER BY e.BD_ENDPT";
                    break;
                default:
                    qryString = "SELECT * FROM viewBDENDPOINTS AS e WHERE e.NODE_ID = @node_id ORDER BY e.BD_ENDPT";
                    break;
            }
            dbmgr.CreateParameters(1);											            // create parameters
            dbmgr.AddParameters(0, "@node_id", id);								            // node id
            dbmgr.ExecuteReader(CommandType.Text, qryString);					            // execute query

            while (dbmgr.DataReader.Read())
            {
                if (bd_endpt == "")
                    bd_endpt = "'" + dbmgr.DataReader["BD_ENDPT"].ToString() + "'";
                else
                    bd_endpt = bd_endpt + ", " + "'" + dbmgr.DataReader["BD_ENDPT"].ToString() + "'";
            }

            dbmgr.CloseReader();
            return bd_endpt;
        }

 private void UpdateBDEndpoint(object obj, IDBManager dbmgr)
 {
     RouteBDEndpoint item = (RouteBDEndpoint)obj;							        // cast object to proper item type
     try
     {
         dbmgr.Open();															    // open database
         dbmgr.CreateParameters(2);												    // create required paramaters
         dbmgr.AddParameters(0, "@endpt", item.endpt);
         dbmgr.AddParameters(1, "@bdendpt", item.bd_endpt);
         dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.BDENDPOINTS_u");    // execute stored procedure
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         dbmgr.Dispose();
     }
 }

        private void FetchRoutelocaList(int id, IDBManager dbmgr)
        {
            string qryString;
            Routeloca item = new Routeloca();
            ArrayList list = new ArrayList();
            PropertyInfo[] p = item.GetType().GetProperties();

            switch (_user.plant)
            {
                case "BLN":
                case "TFAC":
                    qryString = "SELECT * FROM viewROUTELOCA_FR rl WHERE rl.NODE_ID = @node_id ORDER BY rl.RM, rl.FZ";
                    break;
                default:
                    qryString = "SELECT * FROM viewROUTELOCA rl WHERE rl.NODE_ID = @node_id ORDER BY rl.RM, rl.FZ";
                    break;
            }

            dbmgr.CreateParameters(1);
            dbmgr.AddParameters(0, "@node_id", id);
            dbmgr.ExecuteReader(CommandType.Text, qryString);

            while (dbmgr.DataReader.Read())
            {
                item = new Routeloca();
                item = (Routeloca)FetchObject(item, p, dbmgr);
                list.Add(item);
            }

            dbmgr.CloseReader();
            _route.routelocalist = list;
        }

        private void FetchRoute(int id, IDBManager dbmgr)
        {
            string qryLocal = "SELECT * FROM viewROUTELIST WHERE [email protected] ORDER BY NODE";

            dbmgr.CreateParameters(1);
            dbmgr.AddParameters(0, "@id", id);
            dbmgr.ExecuteReader(CommandType.Text, qryLocal);
            if (dbmgr.DataReader.Read())
            {
                PropertyInfo[] p = _route.GetType().GetProperties();
                _route = (Route)FetchObject(_route, p, dbmgr);
            }

            dbmgr.CloseReader();
        }

        // fetches drawings for every fz_id associated with a gived node_id
        // different from other fetches
        private void FetchDrawingList(ArrayList localist, IDBManager dbmgr)
        {
            if (localist.Count == 0)
                _route.drawinglist = null;
            else
            {
                string qryString = "SELECT * FROM viewROUTELOCADWGS rd WHERE rd.NODE_ID = @node_id AND rd.FZ_ID = @fz_id ORDER BY rd.DWG_REF";
                ArrayList list = new ArrayList();										    // create new ArrayList to house objects

                foreach (Routeloca litem in localist)
                {
                    RoutelocaDwg item = new RoutelocaDwg();							        // create new object type to be able to get property info
                    PropertyInfo[] p = item.GetType().GetProperties();					    // get property info for item

                    dbmgr.CreateParameters(2);											    // create parameters
                    dbmgr.AddParameters(0, "@node_id", litem.node_id);					    // node id
                    dbmgr.AddParameters(1, "@fz_id", litem.fz_id);						    // loca id
                    dbmgr.ExecuteReader(CommandType.Text, qryString);					    // execute query

                    while (dbmgr.DataReader.Read())
                    {
                        item = new RoutelocaDwg();										    // create new item
                        item = (RoutelocaDwg)FetchObject(item, p, dbmgr);
                        list.Add(item);													    // add item to the ArrayList
                    }
                    dbmgr.CloseReader();
                }

                _route.drawinglist = list;										            // update item list
            }
        }

 private void DeleteVFDR(object obj, IDBManager dbmgr)
 {
     FAVFDR item = (FAVFDR)obj;
     try
     {
         dbmgr.Open();
         dbmgr.CreateParameters(2);
         dbmgr.AddParameters(0, "@fa_id", item.fa_id);
         dbmgr.AddParameters(1, "@vfdr_id", item.vfdr_id);
         dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, "dbo.FAVFDR_d");
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         dbmgr.Dispose();
     }
 }