Medicare Database Visual Studio Code Example

Summary

Topics covered in this video;

-Code Behind
-Populating Dropdown List
-using’s (ie system.data, system.configuration)
-\ (escape sequences)
-SQLConnection
-SQL/Data Adapters
-DataSet/DataTable
-Session Variable
-Strings
-Creating a button
-Variables being passed over
-Debugging for drop down list
-PostBack

Prerequisites

This example uses the Case Study – Medicare Database

It duplicates code from the lecture Getting Started with Database Tools in Visual Studio

Video

Code for Select State with Explanation

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;

namespace CMS_Example2
{
    public partial class SelectState : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                String cStr = @”Data Source=A30609\EAGLIN;Initial Catalog=PatientSurvey;Integrated Security=True”;

System.Data.SqlClient.SqlConnection sConn = new System.Data.SqlClient.SqlConnection(cStr);

System.Data.SqlClient.SqlDataAdapter sAdpt = new System.Data.SqlClient.SqlDataAdapter(“SELECT DISTINCT(State) FROM RawData”, sConn);

                DataSet ds = new DataSet();

               sAdpt.Fill(ds);

                ddlSelectState.DataSource = ds;
                ddlSelectState.DataTextField = “State”;
                ddlSelectState.DataValueField = “State”;
                ddlSelectState.DataBind();
            }
        }

        protected void btnDone_Click(object sender, EventArgs e)
        {

            String rStr = ddlSelectState.SelectedValue;
            Session.Add(“State”, rStr);
            Response.Redirect(“ScoreResults.aspx”);
        }
    }
}

Most important section is System.Data to have access to Data Objects

page load event is triggered

Only perform this if not a post back

This the connection string for the database, the @ symbol means to use it as a literal string.

Create a new SqlConnection object using the connection string.

Create a SQL Adapter to run a SQL command and fill DataSet.

Create a DataSet to hold the results of the query.

Put the Query results in the DataSet.

Set the DataSource for the drop down list to the DataSet and specify parameters. Bind the drop down to the dataset

If the Done button is pressed, get the selected value, store it in the Session and pass control of program to new page.