Database and Classes

The sample database CourseManagement that is used with the transactions in this chapter is defined by the structure from Figure 21-2. The table Courses contains information about courses: course numbers and titles; for example, the course number 2124 with the title Programming C#. The table CourseDates contains the date of specific courses and is linked to the Courses table. The table Students contains information about persons attending a course. The table CourseAttendees is the link between Students and CourseDates. It defines which student is attending what course.

Figure 21-2

Tip 

You can download the database along the source code with this chapter from the Wrox Web site.

In the sample applications in this chapter, a library with entity and data access classes is used. The class Student contains properties to define a student; for example, Firstname, Lastname, and Company:

  using System; namespace Wrox.ProCSharp.Transactions {    [Serializable]    public class Student    {       public Student() { }       public Student(string firstname, string lastname)       {          this.firstname = firstname;          this.lastname = lastname;       }       private string firstname;       public string Firstname       {          get { return firstname; }          set { firstname = value; }       }       private string lastname;       public string Lastname       {          get { return lastname; }          set { lastname = value; }       }       private string company;       public string Company       {          get { return company; }          set { company = value; }       }       private int id;       public int Id       {          get { return id; }          set { id = value; }       }       public override string ToString()       {          return firstname + " " + lastname;       }    } } 

Adding student information to the database is done in the method AddStudent() of the class StudentData. Here an ADO.NET connection is created to connect to the SQL Server database, the SqlCommand object defines the SQL statement, and the command is executed by invoking ExecuteNonQuery():

  using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Data; using System.Transactions; namespace Wrox.ProCSharp.Transactions {    public class StudentData    {       public void AddStudent(Student s)       {          SqlConnection connection = new SqlConnection(              Properties.Settings.Default.CourseManagementConnectionString);          connection.Open();          try          {             SqlCommand command = connection.CreateCommand();             command.CommandText = "INSERT INTO Students " +                   "(Firstname, Lastname, Company) VALUES " +                   "(@Firstname, @Lastname, @Company)";             command.Parameters.AddWithValue("@Firstname", s.Firstname);             command.Parameters.AddWithValue("@Lastname", s.Lastname);             command.Parameters.AddWithValue("@Company", s.Company);             command.ExecuteNonQuery();          }          finally          {             connection.Close();          }       }    } } 

Tip 

ADO.NET is covered in detail in Chapter 25, “Data Access with .NET.”