Using Parameterized SQL Queries

I sure am glad that LINQ-to-SQL has simplified my data access scenarios.

Here’s an example of some otherwise useful insert statements.

        /// <summary>
        /// Inserts a row of data 
        /// </summary>
        /// <param name="statement">An SQL insert statement</param>
        /// <returns>True for success, false for failure</returns>
        public static bool InsertStatement(string statement)
        {
            SqlConnection connection = connectToDatabase();
            SqlCommand command;

            command = new SqlCommand( statement, connection );

            // executes command
            if (command.ExecuteNonQuery() > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// Inserts a Patient Encounter into the Database
        /// Specifically a non-simulated encounter
        /// </summary>
        /// <param name="date">The Date of the Encounter</param>
        /// <param name="location">The ID of the location</param>
        /// <param name="gender">The gender of the patient</param>
        /// <param name="comments">Additional comments entered by the student</param>
        /// <param name="site">The ID of the site for the encounter</param>
        /// <param name="level">The ID of the level of participation</param>
        /// <param name="program">The ProgramID for the program of the student</param>
        /// <param name="age">The age of the patient</param>
        /// <param name="entry1">ItemID of Selected Diagnosis</param>
        /// <param name="entry2">ItemID of another diagnosis or null</param>
        /// <param name="entry3">ItemID of a third diagnosis or null</param>
        /// <param name="ageType">The type of age (Years, Months, Days, Weeks)</param>
        private static void InsertPatientEncounter(string date, string location, string gender,
                    string comments, string site, string level, int ProgramID,
                    string age, string ageType, string entry1, string entry2, string entry3, string personID, string courseId)
        {
            SqlConnection connection = connectToDatabase();

            // enter patient encounter except for entries
            string statement = @"INSERT INTO ED2PatientEncounter 

            (DateOfEncounter,LocationID,PatientGender,AdditionalComments,SiteTypeID,LevelOfInvolvementID,ProgramID,PatientAge,AgeType,PersonID,DateOfSubmission,CourseId)
            VALUES (@DateOfEncounter,@LocationID,@PatientGender,@AdditionalComments,@SiteTypeID,@LevelOfInvolvementID,@ProgramID,@PatientAge,@AgeType,@PersonID,@DateOfSubmission,@CourseId)";
            SqlCommand command = new SqlCommand( statement, connection );
            command.Parameters.Add( new SqlParameter( "@DateOfEncounter", date ) );
            command.Parameters.Add( new SqlParameter( "@LocationID", location ) );
            command.Parameters.Add( new SqlParameter( "@PatientGender", gender ) );
            command.Parameters.Add( new SqlParameter( "@AdditionalComments", comments ) );
            command.Parameters.Add( new SqlParameter( "@SiteTypeID", site ) );
            command.Parameters.Add( new SqlParameter( "@LevelOfInvolvementID", level ) );
            command.Parameters.Add( new SqlParameter( "@ProgramID", ProgramID ) );
            command.Parameters.Add( new SqlParameter( "@PatientAge", age ) );
            command.Parameters.Add( new SqlParameter( "@AgeType", ageType ) );
            command.Parameters.Add( new SqlParameter( "@PersonID", personID ) );
            command.Parameters.Add( new SqlParameter( "@DateOfSubmission", DateTime.Now.ToString() ) );
            command.Parameters.Add( new SqlParameter( "@CourseId", courseId ) );

            //Execute Query
            command.ExecuteNonQuery();

            //Pull the ID of the Last Insert
            string statementForID = "SELECT @@Identity";
            SqlCommand commandForID = new SqlCommand( statementForID, connection );
            string id = commandForID.ExecuteScalar().ToString();

            //Insert Entries

            //Entry 1
            InsertPatientEncounterDiagnosis( id, entry1, connection );

            //Entry 2
            if (entry2 != null)
            {
                InsertPatientEncounterDiagnosis( id, entry2, connection );
            }

            //Entry 3
            if (entry3 != null)
            {
                InsertPatientEncounterDiagnosis( id, entry3, connection );
            }

            connection.Close();

        }

        /// <summary>
        /// For each Patient Encounter there are Entries of Items.  We store The EncounterID and the ItemID
        /// </summary>
        /// <param name="PatientEncounterID">The Encounter to document</param>
        /// <param name="ItemID">The Entry (ItemID) added</param>
        /// <param name="connection">The Connection to the Database</param>
        private static void InsertPatientEncounterDiagnosis(string PatientEncounterID, string ItemID, SqlConnection connection)
        {
            SqlCommand commandForEntries = new SqlCommand( "INSERT INTO ED2HasEntry (PatientEncounterID,ItemID) VALUES (@PatientEncounterID,@ItemID)", connection );
            commandForEntries.Parameters.Add( new SqlParameter( "@PatientEncounterID", PatientEncounterID ) );
            commandForEntries.Parameters.Add( new SqlParameter( "@ItemID", ItemID ) );
            commandForEntries.ExecuteNonQuery();
        }

        /// <summary>
        /// Insert a Patient Encounter into the Database
        /// Specifically inserts a Simulated Encounter
        /// </summary>
        /// <param name="date">The date of the encounter</param>
        /// <param name="location">The ID of the location of the encounter</param>
        /// <param name="comments">Additional comments entered by the student</param>
        /// <param name="program">The ProgramID of the student's program</param>
        private static void InsertPatientEncounter(string date, string locationID, string simulationTypeID, string comments, int ProgramID, string entry1, string entry2, string entry3, string personID, string courseId)
        {
            SqlConnection connection = connectToDatabase();

            // enter patient encounter except for entries
            string statement = @"INSERT INTO ED2PatientEncounter 

                    (DateOfEncounter,LocationID,SimulatedEncounterTypeID,AdditionalComments,ProgramID,PersonID,DateOfSubmission,CourseId)
            VALUES (@DateOfEncounter,@LocationID,@SimulatedEncounterTypeID,@AdditionalComments,@ProgramID,@PersonID,@DateOfSubmission,@CourseId)";
            SqlCommand command = new SqlCommand( statement, connection );
            command.Parameters.Add( new SqlParameter( "@DateOfEncounter", date ) );
            command.Parameters.Add( new SqlParameter( "@LocationID", locationID ) );
            command.Parameters.Add( new SqlParameter( "@SimulatedEncounterTypeID", simulationTypeID ) );
            command.Parameters.Add( new SqlParameter( "@AdditionalComments", comments ) );
            command.Parameters.Add( new SqlParameter( "@ProgramID", ProgramID ) );
            command.Parameters.Add( new SqlParameter( "@PersonID", personID ) );
            command.Parameters.Add( new SqlParameter( "@DateOfSubmission", DateTime.Now.ToString() ) );
            command.Parameters.Add( new SqlParameter( "@CourseId", courseId ) );

            //Execute Query
            command.ExecuteNonQuery();

            //Pull the ID of the Last Insert
            string statementForID = "SELECT @@Identity";
            SqlCommand commandForID = new SqlCommand( statementForID, connection );
            string id = commandForID.ExecuteScalar().ToString();

            //Insert Entries

            InsertPatientEncounterDiagnosis( id, entry1, connection );

            if (entry2 != null)
                InsertPatientEncounterDiagnosis( id, entry2, connection );

            if (entry3 != null)
                InsertPatientEncounterDiagnosis( id, entry3, connection );

            connection.Close();

        }