ADO Data Objects

Prerequisites

Lecture – How To – Create Forms that Update

Part of Case Study – Trauma Flow Database

Summary

This video demonstrates the basic use of the ADO.NET objects in a class wrapper. SQLConnection, SqlCommand, SQLAdapter, DataTable objects are all discussed and used. A discussion of refactoring code is included.

Video 

http://online1.daytonastate.edu/player2.php?id=6403675579f6114559c90de0014cd3d6

Reference Materials

SqlClient Object Library – http://msdn.microsoft.com/en-us/library/8t72t3k4 

SqlConnection Class – http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx 

SqlCommand Class – http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand 

SqlDataAdapter Class – http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter 

Generic Classes for Data (System.Data)

DataTable – http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx 

DataSet – http://msdn.microsoft.com/en-us/library/system.data.dataset 

Additional Information

public class DatabaseHelper    {        public string ConnectionString {get; set;}        public DatabaseHelper()        {        }        public DatabaseHelper(string connName)        {            ConnectionString = getConnectionString(connName);        }        public string getConnectionString(string connName)        {            System.Configuration.Configuration rootWeb = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(“/TraumaFlow”);            System.Configuration.ConnectionStringSettings connstr;            if (rootWeb.ConnectionStrings.ConnectionStrings.Count > 0)            {                connstr = rootWeb.ConnectionStrings.ConnectionStrings[connName];                return connstr.ConnectionString;            }            return String.Empty;        }        public string executeSqlReturnString(string sql)        {            // Should be the first element in the DataTable, row and column            DataTable dt = executeSqlReturnDataTable(sql);            if (dt.Rows.Count >= 1)                return Convert.ToString(dt.Rows[0][0]);            else                return String.Empty;        }        public DataTable executeSqlReturnDataTable(string sql)        {                       // Create a Connection Object            SqlConnection connection = new SqlConnection(ConnectionString);            // Create the Command            SqlCommand command = new SqlCommand(sql, connection);            // Create an Adapter to execute the command            SqlDataAdapter a = new SqlDataAdapter(command);            // Create a DataTable to get the results            DataTable dt = new DataTable();            // The Adapter will then fill the DataTable            a.Fill(dt);            return dt;        }        public DataSet executeStoredProcedureReturnDataSet(SqlParameterCollection spc, string sp_name)        {            // Create a Connection Object            SqlConnection connection = new SqlConnection(ConnectionString);            SqlCommand command = new SqlCommand();            command.CommandType = CommandType.StoredProcedure;            command.CommandText = sp_name;            foreach (SqlParameter sp in spc)                            command.Parameters.Add(sp);            SqlDataAdapter a = new SqlDataAdapter(command);            // Create a DataTable to get the results            DataSet ds = new DataSet();            // The Adapter will then fill the DataTable            a.Fill(ds);            return ds;        }        }

public class TraumaFlowHelper    {        public static string connectionName = “TraumaFlowConnectionString”;        public static string getConnectionString()        {            DatabaseHelper dh = new DatabaseHelper(connectionName);            return dh.getConnectionString(connectionName);        }        public static void fillTextBox(TextBox tb, string XMLName, int TraumaFormID)        {            tb.Text = getValue(XMLName, TraumaFormID);        }        public static string getValue(string XMLName, int TraumaFormID)        {            if (XMLName == “PatientReportedName”) return TraumaFlowHelper.getPatientName(TraumaFormID);            string sql = “SELECT Value FROM view_TextMedicalData “;            sql += “WHERE XMLName = ‘” + XMLName + “‘”;            sql += ” AND id = ” + Convert.ToString(TraumaFormID);            DatabaseHelper dh = new DatabaseHelper(connectionName);            return dh.executeSqlReturnString(sql);        }        public static string getPatientName(int TraumaFormID)        {            string sql = “SELECT name FROM TraumaForm WHERE id = ‘” + TraumaFormID.ToString() + “‘”;                        DatabaseHelper dh = new DatabaseHelper(connectionName);            return dh.executeSqlReturnString(sql);        }        public static int getCodeID(string XMLName, int TraumaFormID)        {            string sql = “SELECT CodeID FROM view_CodedMedicalData “;            sql += “WHERE XMLName = ‘” + XMLName + “‘”;            sql += ” AND id = ” + Convert.ToString(TraumaFormID);            try            {                DatabaseHelper dh = new DatabaseHelper(connectionName);                return Convert.ToInt32(dh.executeSqlReturnString(sql));             }            catch            {                 return 0;             }        }    }