Medicare Database Visual Studio Code Example

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
{
Most important section is System.Data to have access to Data Objects.
protected void btnDone_Click(obj
protected void Page_Load(object sender, EventArgs e)
{		page load event is triggered
if (!IsPostBack)
{
Only perform this if not a post back
String cStr = @"Data Source=A30609\EAGLIN;Initial Catalog=PatientSurvey;Integrated Security=True";
This the connection string for the database, the @ symbol means to use it as a literal string.
System.Data.SqlClient.SqlConnection sConn = new System.Data.SqlClient.SqlConnection(cStr);Create a new SqlConnection object using the connection string.
System.Data.SqlClient.SqlDataAdapter sAdpt = new System.Data.SqlClient.SqlDataAdapter("SELECT DISTINCT(State) FROM RawData", sConn);Create a SQL Adapter to run a SQL command and fill DataSet.
DataSet ds = new DataSet();Create a DataSet to hold the results of the query.
sAdpt.Fill(ds);
Put the Query results in the DataSet.
ddlSelectState.DataSource = ds;
ddlSelectState.DataTextField = "State";
ddlSelectState.DataValueField = "State";
ddlSelectState.DataBind();
}
}

Set the DataSource for the drop down list to the DataSet and specify parameters. Bind the drop down to the dataset
protected void btnDone_Click(object sender, EventArgs e)
{

String rStr = ddlSelectState.SelectedValue;
Session.Add("State", rStr);
Response.Redirect("ScoreResults.aspx");
}
}
}		If the Done button is pressed, get the selected value, store it in the Session and pass control of program to new page.