C# ParameterCollection.getKeys方法代码示例

        //
        ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        //
        /// <summary>
        /// Use this when there are records returned whose fields need capturing
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="sPparams"></param>
        /// <returns></returns>
        public System.Xml.XmlReader ExecuteXmlReaderSPWithParams(String spName, ParameterCollection sPparams)
        {
            try
            {
                //////////////////////

                SqlConnection connection = new SqlConnection(connectionString);
                connection.Open();

                SqlCommand cmdProcedure = new SqlCommand(spName, connection);
                cmdProcedure.CommandType = CommandType.StoredProcedure;
                cmdProcedure.CommandTimeout = 180; //change command timeout from default to 3 minutes

                if (sPparams != null)
                {
                    foreach (string param in sPparams.getKeys())
                    {
                        cmdProcedure.Parameters.Add("@" + param, sPparams[param].sqlType);
                        cmdProcedure.Parameters["@" + param].Value = sPparams[param].obj;
                    }
                }
                return (cmdProcedure.ExecuteXmlReader());
            }
            catch (Exception e)
            {
                // get call stack
                StackTrace stackTrace = new StackTrace();

                // get calling method name
                String callingRoutine = stackTrace.GetFrame(1).GetMethod().Name;
                Logger.Instance.WriteToLog("[BCDB2] from [" + callingRoutine + "] Could not execute SQL query: " + spName + "\n\t" + e);
                return (System.Xml.XmlReader)null;
            }
        }

        /// <summary>
        /// Use this when there are no records returned whose fields need capturing
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="sPparams"></param>
        /// <returns></returns>
        public int RunSPWithParams(String spName, ParameterCollection sPparams)
        {
            int affectedRecords = 0;

            try
            {
                //////////////////////
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    SqlCommand cmdProcedure = new SqlCommand(spName, connection);
                    cmdProcedure.CommandType = CommandType.StoredProcedure;
                    cmdProcedure.CommandTimeout = 180; //change command timeout from default to 3 minutes

                    if (sPparams != null)
                    {
                        foreach (string param in sPparams.getKeys())
                        {
                            cmdProcedure.Parameters.Add("@" + param, sPparams[param].sqlType);
                            cmdProcedure.Parameters["@" + param].Value = sPparams[param].obj;
                        }
                    }
                    affectedRecords = cmdProcedure.ExecuteNonQuery();
                } //end of using connection

            }
            catch (Exception e)
            {
                // get call stack
                StackTrace stackTrace = new StackTrace();

                // get calling method name
                String callingRoutine = stackTrace.GetFrame(1).GetMethod().Name;
                Logger.Instance.WriteToLog("[BCDB2] from [" + callingRoutine + "] Could not execute SQL query: " + spName + "\n\t" + e);
            }

            return affectedRecords;
        }

        /**********************************************************************/
        public object ExecuteSpWithRetValAndParams(String stored_procedure, SqlDbType return_type, ParameterCollection pc)
        {
            object result = null;

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand command;
                    command = new SqlCommand(stored_procedure, connection);
                    command.CommandType = CommandType.StoredProcedure;

                    if (pc != null)
                    {
                        foreach (string param in pc.getKeys())
                        {
                            command.Parameters.Add("@" + param, pc[param].sqlType);
                            command.Parameters["@" + param].Value = pc[param].obj;
                        }
                    }
                    SqlParameter retval = command.Parameters.Add("@retval", return_type);
                    retval.Direction = ParameterDirection.ReturnValue;
                    command.ExecuteNonQuery();
                    result = command.Parameters["@retval"].Value;

                    connection.Close();
                } //connection will close here
            }
            catch (Exception ee)
            {
                // get call stack
                StackTrace stackTrace = new StackTrace();

                // get calling method name
                String callingRoutine = stackTrace.GetFrame(1).GetMethod().Name;
                Logger.Instance.WriteToLog("[BCDB2] from [" + callingRoutine + "] Could not execute SP: " + stored_procedure + "\n\t" + ee);
            }

            return result;
        }

        public int ExecuteNonQueryWithParams(String sqlStr, ParameterCollection pc)
        {
            int affectedRecords = 0;

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand command;
                    command = new SqlCommand(sqlStr, connection);
                    command.CommandTimeout = 180; //change command timeout from default to 3 minutes

                    if (pc != null)
                    {
                        foreach (string param in pc.getKeys())
                        {
                            command.Parameters.Add("@" + param, pc[param].sqlType);
                            command.Parameters["@" + param].Value = pc[param].obj;
                        }
                    }
                    affectedRecords = command.ExecuteNonQuery();
                    connection.Close();
                } //connection will close here
            }
            catch (Exception e)
            {
                // get call stack
                StackTrace stackTrace = new StackTrace();

                // get calling method name
                String callingRoutine = stackTrace.GetFrame(1).GetMethod().Name;
                Logger.Instance.WriteToLog("[BCDB2] from [" + callingRoutine + "] Could not execute SQL query: " + sqlStr + "\n\t" + e);
            }

            return affectedRecords;
        }

        /// <summary>
        /// Takes aggregated list of DICOM generated studies, and imports it like any other appointment feed
        /// </summary>
        /// <param name="kvp">Output from DICOM Modality Worklist query.</param>
        /// <returns></returns>
        public static bool parseAndInsertAppointments(List<KeyValuePair<string, string>> kvp, InterfaceInstance iface)
        {
            string sqlStr;
            SqlDataReader reader;

            if ((kvp == null) || (kvp.Count == 0))
            {
                Logger.Instance.WriteToLog("parseAndInsertAppointments in interfaceUtils... NULL or EMPTY arglist in.");
                return false;
            }

            // now process the output.... StudyDate is the first item in each returned study.  List contains zero to infinity appointments.
            List<ParameterCollection> paramCollection = new List<ParameterCollection>();
            ParameterCollection pc = new ParameterCollection();
            string sDate;

            // these two variables used in mapping clinics, see note below.
            int clinicID = 0;   // default to clinic 0... will be changed below
            string defaultAssessmentType = "";

            // emerge from this loop with a collection of parameterCollections, each representing one appointment.
            // "StudyDate" is the delimiting field... when that comes up, cleave off a new param collection.
            foreach (KeyValuePair<string, string> item in kvp)
            {
                switch (item.Key.ToUpper())
                {
                    case "ACCESSIONNUMBER":
                        // this is the delimiting field.  start a new param collection now.
                        if (pc.getKeys().Count > 0)     // first time through?  don't add to collection...
                        {
                            paramCollection.Add(pc);
                            pc = new ParameterCollection();
                        }
                        pc.Add("referral", item.Value);

                        break;
                    case "SCHEDULEDPROCEDURESTEPSTARTDATE":
                        // CONVERT to mm/dd/yyyy
                        sDate = item.Value.Substring(4, 2) + "/" + item.Value.Substring(6, 2) + "/" + item.Value.Substring(0, 4);
                        pc.Add("apptdate", sDate);
                        break;
                    case "SCHEDULEDPROCEDURESTEPSTARTTIME":
                        pc.Add("appttime", item.Value.Substring(0, 2) + ":" + item.Value.Substring(2, 2));
                        break;

                    // use these ids to key into lkpProviders for the localId (?) and set displayName in the appt, upsert into tblApptProviders...
                    // that logic is done in sp_createApptFromSchedService once the new appt id is created.
                    case "REFERRINGPHYSICIANADDRESS":   // note:  at NSMC, this contains referring phys ID!
                        pc.Add("refPhysLocalID", item.Value);
                        break;
                    case "REFERRINGPHYSICIANTELEPHONENUMBERS":  // note: at NSMC, this contains pcp ID!
                        pc.Add("pcpLocalID", item.Value);
                        break;
                    case "PATIENTNAME":
                        // Break into @firstName and @lastName
                        string[] nameParts = item.Value.Split(new char[] { ',', '^' });

                        if (nameParts.Length > 0)
                        {
                            pc.Add("lastname", nameParts[0]);
                            pc.Add("firstname", nameParts[1]);
                        }

                        break;
                    case "PATIENTID":
                        pc.Add("unitnum", item.Value);
                        break;
                    case "PATIENTBIRTHDATE":
                        sDate = item.Value.Substring(4, 2) + "/" + item.Value.Substring(6, 2) + "/" + item.Value.Substring(0, 4);
                        pc.Add("dob", sDate);
                        break;
                    case "PATIENTSEX":
                        pc.Add("gender", item.Value);
                        break;

                    case "SCHEDULEDPERFORMINGPHYSICIANNAME":
                        pc.Add("apptphysname", item.Value);
                        break;

                    default:
                        // turns out that different systems put the clinic name in different fields... it has now been
                        // parameterized in lkpInterfaceDefinitions and InterfaceInstance... deal with it here in the default case.
                        // defaults to SCHEDULEDSTATIONNAME in InterfaceInstance for historical reasons (NSMC, our first MWL customer, put it there.) jdg 1/22/16
                        if (item.Key.ToUpper() == iface.ClinicFieldInMWLQuery.ToUpper())
                        {
                            sqlStr = "select clinicID, defaultAssessmentType from lkpClinics where clinicCode='" + item.Value.Trim() + "'";
                            reader = BCDB2.Instance.ExecuteReader(sqlStr);
                            if (reader != null)
                            {
                                while (reader.Read())
                                {
                                    if (reader.IsDBNull(0) == false)
                                    {
                                        clinicID = reader.GetInt32(0);
                                    }
//.........这里部分代码省略.........

        /*******************************************************************************/
        public void DoPersistWithSpAndParams(HraModelChangedEventArgs e, String spName, ref ParameterCollection sPparams)
        {
            lock (BCDB2.Instance)
            {
                if (e.Persist == false)
                    return;

                try
                {
                    //////////////////////
                    using (SqlConnection connection = new SqlConnection(BCDB2.Instance.getConnectionString()))
                    {
                        connection.Open();

                        SqlCommand cmdProcedure = new SqlCommand(spName, connection)
                        {
                            CommandType = CommandType.StoredProcedure,
                            CommandTimeout = 300
                        };
                        //change command timeout from default to 5 minutes

                        if (e.Delete)
                        {
                            cmdProcedure.Parameters.Add("@delete", SqlDbType.Bit);
                            cmdProcedure.Parameters["@delete"].Value = e.Delete;
                        }

                        cmdProcedure.Parameters.Add("@user", SqlDbType.NVarChar);
                        if (SessionManager.Instance.ActiveUser == null)
                        {
                            cmdProcedure.Parameters["@user"].Value = "Not Available";
                        }
                        else
                        {
                            cmdProcedure.Parameters["@user"].Value = SessionManager.Instance.ActiveUser.userLogin;
                        }

                        if (sPparams != null)
                        {
                            foreach (string param in sPparams.getKeys())
                            {
                                if (sPparams[param].Size > 0)
                                {
                                    cmdProcedure.Parameters.Add("@" + param, sPparams[param].sqlType, sPparams[param].Size);
                                }
                                else
                                {
                                    cmdProcedure.Parameters.Add("@" + param, sPparams[param].sqlType);
                                }
                                cmdProcedure.Parameters["@" + param].Value = sPparams[param].obj;
                                if (sPparams[param].BiDirectional)
                                {
                                    cmdProcedure.Parameters["@" + param].Direction = ParameterDirection.InputOutput;
                                }
                            }
                        }
                        try
                        {
                            if (e.updatedMembers.Count > 0)
                            {
                                foreach (FieldInfo fi in this.GetType().GetFields(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic))
                                {
                                    string name = fi.Name;

                                    foreach (MemberInfo mi in e.updatedMembers)
                                    {
                                        if (name == mi.Name)
                                        {
                                            SqlDbType sType = BCDB2.Instance.GetSqlTypeFromModel(fi.FieldType);
                                            if (cmdProcedure.Parameters.Contains("@" + name))
                                            {
                                                if (cmdProcedure.Parameters["@" + name].Value.Equals(fi.GetValue(this)))
                                                {
                                                    //Logger.Instance.WriteToLog("[DoPersistWithSpAndParams] Executing Stored Procedure - "
                                                    //    + "Added the same parameter and value twice; parameter name = " + name + "; value = " + cmdProcedure.Parameters["@" + name].Value.ToString());
                                                }
                                                else
                                                {
                                                    Logger.Instance.WriteToLog("Attempted to Add the same parameter twice with differing values; first value = " + cmdProcedure.Parameters["@" + name].Value + ", "
                                                        + "Second value = " + fi.GetValue(this));
                                                }
                                                break; //don't add it
                                            }
                                            cmdProcedure.Parameters.Add("@" + name, sType);
                                            cmdProcedure.Parameters["@" + name].Value = fi.GetValue(this);
                                            break;
                                        }
                                    }
                                }
                            }
                            else
                            {
                                foreach (FieldInfo fi in this.GetType().GetFields(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic))
                                {
                                    bool persist = IsPersistable(fi);

                                    if (fi.FieldType == typeof(DateTime))
                                    {
                                        DateTime dt = (DateTime)fi.GetValue(this);
//.........这里部分代码省略.........

        /*******************************************************************************/
        public void DoLoadWithSpAndParams(String spName, ParameterCollection sPparams)
        {
            SetInternationalCulture();
            try
            {
                //////////////////////
                using (SqlConnection connection = new SqlConnection(BCDB2.Instance.getConnectionString()))
                {
                    connection.Open();

                    SqlCommand cmdProcedure = new SqlCommand(spName, connection)
                    {
                        CommandType = CommandType.StoredProcedure,

                        //change command timeout from default to 5 minutes
                        CommandTimeout = 300
                    };

                    if (sPparams != null)
                    {
                        foreach (string param in sPparams.getKeys())
                        {
                            cmdProcedure.Parameters.Add("@" + param, sPparams[param].sqlType);
                            cmdProcedure.Parameters["@" + param].Value = sPparams[param].obj;
                        }
                    }
                    try
                    {
                        SqlDataReader reader = cmdProcedure.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                        {
                            if (_loadThread.CancellationPending)
                            {
                                reader.Close();
                                return;
                            }

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                if (reader.IsDBNull(i) == false)
                                {
                                    foreach (
                                        FieldInfo fi in
                                            this.GetType().GetFields(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic))
                                    {
                                        string name = fi.Name;
                                        if (name == reader.GetName(i))
                                        {
                                            fi.SetValue(this, reader.GetValue(i));
                                            break;
                                        }
                                    }
                                }
                            }
                        }
                        reader.Close();
                    }
                    catch (Exception exception)
                    {
                        Logger.Instance.WriteToLog("**** " + this + "\n" + exception);
                    }
                } //end of using connection
            }
            catch (Exception exc)
            {
                Logger.Instance.WriteToLog("[DoLoadWithSpAndParams] Executing Stored Procedure - " + exc);
            }
        }