Lecture - ADO Data Objects


 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=9d28de8ff9bb6a3fa41fddfdc28f3bc1 

 

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; 
            }
        }
    }
 

 

 

 

COP 4834 Lectures Page