| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • Work with all your cloud files (Drive, Dropbox, and Slack and Gmail attachments) and documents (Google Docs, Sheets, and Notion) in one place. Try Dokkio (from the makers of PBworks) for free. Now available on the web, Mac, Windows, and as a Chrome extension!

View
 

Lecture - ADO Data Objects

Page history last edited by Dr. Ron Eaglin 9 years, 4 months ago

 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

Comments (0)

You don't have permission to comment on this page.