|
|
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.
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.
Submit an article or tip