Click here to Skip to main content
11,436,514 members (66,408 online)
Click here to Skip to main content
Articles » Database » Database » ADO.NET » Downloads
Add your own
alternative version

ADO.NET for the Object-Oriented Programmer – Part One

, 19 Jan 2006 CPOL
This article will show how to accomplish these goals—use ADO.NET as a thin data transport layer, while still taking advantage of the data-binding capabilities of .NET user interface controls. As it turns out, it’s pretty easy.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace AdoNetDemo
{
    public partial class Form1 : Form
    {
    	#region Declarations

        // Connection to application database
        string m_ConnectionString = @"Data Source=(local)\sqlexpress;Initial Catalog=AdoNetDemo;Integrated Security=True;";
    
    	#endregion

    	#region Constructor

        public Form1()
        {
            InitializeComponent();
        }

    	#endregion

    	#region Event Handlers

        private void Form1_Load(object sender, EventArgs e)
        {
        }

        private void buttonFlatSelect_Click(object sender, EventArgs e)
        {
            // Get flat list of projects
            ProjectList projects = this.GetProjects();

            // Build results string
            string results = "FLAT SELECT QUERY RESULTS\r\n\r\n";
            foreach (ProjectItem project in projects)
            {
                results += "Project ID = " + project.ID.ToString() + "\r\n";
                results += "Project Name = " + project.Name + "\r\n\r\n";
            }

            // Show results
            textBoxResults.Text = results;
        }

        private void buttonHierarchicalSelect_Click(object sender, EventArgs e)
        {
            // Get hierarchical list of projects
            ProjectList projects = this.GetProjectsAndSteps();

            // Build results string
            string results = "HIERARCHICAL SELECT QUERY RESULTS\r\n\r\n";
            foreach (ProjectItem project in projects)
            {
                results += "Project ID = " + project.ID.ToString() + "\r\n";
                results += "Project Name = " + project.Name + "\r\n\r\n";

                foreach (StepItem step in project.Steps)
                {
                    results += "\tStep ID = " + step.ID.ToString() + "\r\n";
                    results += "\tStep Date = " + step.Date.ToString("MM/dd/yyyy") + "\r\n";
                    results += "\tStep Description = " + step.Description + "\r\n\r\n";
                }
            }

            // Show results
            textBoxResults.Text = results;
        }

        private void buttonInsert_Click(object sender, EventArgs e)
        {
            // Get project list
            ProjectList projects = this.GetProjectsAndSteps();

            // Get ID of first project (we'll add new step to this project)
            int projectID = projects[0].ID;

            // Create new step and add to project list
            StepItem newStep = this.CreateStep(projectID);
            projects[0].Steps.Add(newStep);

            // Build results string
            string results = "INSERT QUERY RESULTS\r\n\r\n";
            results += "Project ID = " + projects[0].ID.ToString() + "\r\n";
            results += "Project Name = " + projects[0].Name + "\r\n\r\n";
            foreach (StepItem step in projects[0].Steps)
            {
                results += "\tStep ID = " + step.ID.ToString() + "\r\n";
                results += "\tStep Date = " + step.Date.ToString("MM/dd/yyyy") + "\r\n";
                results += "\tStep Description = " + step.Description + "\r\n\r\n";
            }

            // Show results
            textBoxResults.Text = results;
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            // Delete new rows
            int numRowsDeleted = this.DeleteNewSteps();

            // Get the project list
            ProjectList projects = this.GetProjectsAndSteps();

            // Build results string
            string results = "HIERARCHICAL SELECT QUERY RESULTS\r\n\r\n";
            results += String.Format("{0} records deleted\r\n\r\n", numRowsDeleted);
            foreach (ProjectItem project in projects)
            {
                results += "Project ID = " + project.ID.ToString() + "\r\n";
                results += "Project Name = " + project.Name + "\r\n\r\n";

                foreach (StepItem step in project.Steps)
                {
                    results += "\tStep ID = " + step.ID.ToString() + "\r\n";
                    results += "\tStep Date = " + step.Date.ToString("MM/dd/yyyy") + "\r\n";
                    results += "\tStep Description = " + step.Description + "\r\n\r\n";
                }
            }

            // Show results
            textBoxResults.Text = results;
        }

        private void buttonUpdate_Click(object sender, EventArgs e)
        {
            // Get project list
            ProjectList projects = this.GetProjectsAndSteps();

            // Change the name of the first project
            projects[0].Name = "Project NameChanged";

            // Update the database
            this.UpdateProjectItem(projects[0]);

            // Build results string
            string results = "UPDATE QUERY RESULTS\r\n\r\n";
            results += "Project ID = " + projects[0].ID.ToString() + "\r\n";
            results += "Project Name = " + projects[0].Name + "\r\n\r\n";
            foreach (StepItem step in projects[0].Steps)
            {
                results += "\tStep ID = " + step.ID.ToString() + "\r\n";
                results += "\tStep Date = " + step.Date.ToString("MM/dd/yyyy") + "\r\n";
                results += "\tStep Description = " + step.Description + "\r\n\r\n";
            }

            // Show results
            textBoxResults.Text = results;
        }

        private void buttonUndo_Click(object sender, EventArgs e)
        {
            // Get project list
            ProjectList projects = this.GetProjectsAndSteps();

            // Change the name of the first project
            projects[0].Name = "Project Southbury";

            // Update the database
            this.UpdateProjectItem(projects[0]);

            // Build results string
            string results = "UNDO UPDATE RESULTS\r\n\r\n";
            results += "Project ID = " + projects[0].ID.ToString() + "\r\n";
            results += "Project Name = " + projects[0].Name + "\r\n\r\n";
            foreach (StepItem step in projects[0].Steps)
            {
                results += "\tStep ID = " + step.ID.ToString() + "\r\n";
                results += "\tStep Date = " + step.Date.ToString("MM/dd/yyyy") + "\r\n";
                results += "\tStep Description = " + step.Description + "\r\n\r\n";
            }

            // Show results
            textBoxResults.Text = results;
        }

        
        #endregion

    	#region Methods

        public ProjectList GetProjects()
        {
            // Create a Projects list
            ProjectList projects = new ProjectList();

            // Set SQL query to fetch projects
            string sqlQuery = "Select * from Projects";

            // Create and open a connection
            SqlConnection connection = new SqlConnection(m_ConnectionString);
            connection.Open();

            // Create a Command object
            SqlCommand command = new SqlCommand(sqlQuery, connection);

            // Use the Command object to create a data reader
            SqlDataReader dataReader = command.ExecuteReader();

            // Read the data reader's rows into the ProjectList
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    // Create a new project
                    ProjectItem project = new ProjectItem();
                    project.ID = dataReader.GetInt32(0);
                    project.Name = dataReader.GetString(1);

                    // Add it to the Projects list
                    projects.Add(project);
                }
            }

            // Close and dispose
            command.Dispose();
            connection.Close();
            connection.Dispose();

            // Set return value
            return projects;
        }

        public ProjectList GetProjectsAndSteps()
        {
            // Create a Projects list
            ProjectList projects = new ProjectList();

            // Set SQL query to fetch projects
            string sqlQuery = "Select * from Projects; Select * from Steps";

            // Create dataset
            DataSet dataSet = new DataSet();

            // Populate dataset
            using (SqlConnection connection = new SqlConnection(m_ConnectionString))
            {
                SqlCommand command = new SqlCommand(sqlQuery, connection);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataSet);
            }

            // Set dataset table names
            dataSet.Tables[0].TableName = "Projects";
            dataSet.Tables[1].TableName = "Steps";
            // Create a data relation between the projects (parents) and steps (children)
            DataColumn parentColumn = dataSet.Tables["Projects"].Columns["ProjectID"];
            DataColumn childColumn = dataSet.Tables["Steps"].Columns["ProjectID"];
            DataRelation projectsToSteps = new DataRelation("ProjectsToSteps", parentColumn, childColumn);
            dataSet.Relations.Add(projectsToSteps);

            // Create a Projects collection from the data set
            ProjectList projectList = new ProjectList();
            ProjectItem nextProject = null;
            StepItem nextStep = null;
            foreach (DataRow parentRow in dataSet.Tables["Projects"].Rows)
            {
                // Create new project 
                nextProject = new ProjectItem();

                // Fill in its properties
                nextProject.ID = Convert.ToInt32(parentRow["ProjectID"]);
                nextProject.Name = parentRow["Name"].ToString();

                /* Read in other fields from the record... */

                // Get its steps
                DataRow[] childRows = parentRow.GetChildRows(dataSet.Relations["ProjectsToSteps"]);

                // Create StepItem objects for each of its steps
                foreach (DataRow childRow in childRows)
                {
                    // Create new step
                    nextStep = new StepItem();

                    // Fill in its properties
                    nextStep.ID = Convert.ToInt32(childRow["StepID"]);
                    nextStep.Date = Convert.ToDateTime(childRow["Date"]);
                    nextStep.Description = childRow["Description"].ToString();

                    // Add new step to the project
                    nextProject.Steps.Add(nextStep);
                }

                // Add new project to the Projects list
                projectList.Add(nextProject);
            }

            // Dispose of the DataSet
            dataSet.Dispose();

            // Set return value
            return projectList;
        }

        public StepItem CreateStep(int projectID)
        {
            // Build 'Insert' query
            string sqlQuery = String.Format("Insert into Steps (ProjectID, "
                + "Description, Date) Values({0}, '[New Step]', '{1}'); Select @@Identity", 
                projectID, DateTime.Today.ToString("yyyy-MM-dd"));

            // Create and open a connection
            SqlConnection connection = new SqlConnection(m_ConnectionString);
            connection.Open();

            // Create a Command object
            SqlCommand command = new SqlCommand(sqlQuery, connection);

            // Execute the command
            int stepID = Convert.ToInt32((decimal)command.ExecuteScalar());

            // Close and dispose
            command.Dispose();
            connection.Close();
            connection.Dispose();

            // Create new step to match the record we just created
            StepItem newStep = new StepItem();
            newStep.ID = stepID;
            newStep.Date = DateTime.Today;
            newStep.Description = "[New Step]";

            // Set return value
            return newStep;
        }

        public int DeleteNewSteps()
        {
            // Set SQL 'Delete' query
            string sqlQuery = "Delete from Steps Where Description = '[New Step]'";

            // Create and open a connection
            SqlConnection connection = new SqlConnection(m_ConnectionString);
            connection.Open();

            // Create a Command object
            SqlCommand command = new SqlCommand(sqlQuery, connection);

            // Execute the command
            int numRowsDeleted = command.ExecuteNonQuery();

            // Close and dispose
            command.Dispose();
            connection.Close();
            connection.Dispose();
            
            // Set return value
            return numRowsDeleted;
        }

        public void UpdateProjectItem(ProjectItem project)
        {
            // Build an 'Update' query
            string sqlQuery = String.Format("Update Projects Set Name = '{0}' "
                + "Where ProjectID = {1}", project.Name, project.ID);

            // Create and open a connection
            SqlConnection connection = new SqlConnection(m_ConnectionString);
            connection.Open();

            // Create a Command object
            SqlCommand command = new SqlCommand(sqlQuery, connection);

            // Execute the command
            command.ExecuteNonQuery();

            // Close and dispose
            command.Dispose();
            connection.Close();
            connection.Dispose();
        }
        
#endregion
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

David Veeneman
Software Developer (Senior) Foresight Systems
United States United States
David Veeneman is a financial planner and software developer. He is the author of "The Fortune in Your Future" (McGraw-Hill 1998). His company, Foresight Systems, develops planning and financial software.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150428.2 | Last Updated 19 Jan 2006
Article Copyright 2006 by David Veeneman
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid