Home

Microsoft SQL/Server Databases: Introduction to Client/Server Applications

   

Introduction

A client/server application is a computer program that allows a user to access something that is stored in a server. Of course, both computers can be workstations and can run the same type of operating system. In most networking environments, the server holds a database and users must access that database from their workstations.

If you want to create a client/server database application, you have many choices among Microsoft Access, XML, file processing, MySQL, Oracle, etc. One more common choice you have is Microsoft SQL Server.

Creating the Database

In a typical client/server database, probably the first thing to do is to create the database. As always, you have many choices. In fact, you can even start with something other than the database.

To launch the server, from the computer where you have installed Microsoft SQL Server or from a workstation where you would have installed the client tools, start Microsoft SQL Server (Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio). On the Standard toolbar, click the New Query button New Query.

Practical LearningPractical Learning: Creating the Database on the Server

  1. On the server, to launch Microsoft SQL Server, click Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio
  2. On the Standard toolbar, click the New Query button New Query
  3. To set code for a simple database, type the following code:
    USE master;
    GO
    CREATE DATABASE Exercise1;
    GO
    USE Exercise1;
    GO
    CREATE SCHEMA Management;
    GO
    CREATE SCHEMA Personnel;
    GO
    CREATE TABLE Management.Departments
    (
    	DeptCode nchar(5) not null,
    	Department nvarchar(40),
    	CONSTRAINT PK_Departments PRIMARY KEY(DeptCode)
    );
    GO
    CREATE TABLE Personnel.Employees
    (
    	EmplNbr nchar(5) not null,
    	FirstName nvarchar(24),
    	LastName nvarchar(24) not null,
    	DeptCode nchar(5)
    		CONSTRAINT FK_Departments
    		FOREIGN KEY REFERENCES Management.Departments(DeptCode),
    	Title nvarchar(50),
    	CONSTRAINT PK_Employees PRIMARY KEY(EmplNbr)
    );
    GO
  4. To create the database, press F5
Microsoft SQL Server Management Studio

Creating the Graphical Application

To allow users to access a database that is stored in a server, you have various options. One is to use a computer language such as C#, Visual Basic, or F#, etc, to create a graphical application. Probably the easiest way to create a graphical application that accesses a Microsoft SQL Server database is to use an integrated development environment (IDE) such as Microsoft Visual Studio (Professional, Ultimate, or one of the free Express editions). Another cheap way is to compile the application from the Command Prompt, in which case you may not have to worry about distributing the application: you can just run the appropriate script on each client workstation.

Let's assume that you are running a Microsoft Windows operating sytem (XP Professional, Vista Professional or Ultimate, or 7 Professional or Ultimate) on the client workstation. First, open Windows Explorer and check the version of the .NET Framework the computer is using. Here is an example:

Microsoft.NET Framework Version
Microsoft.NET Framework Version
  

Practical LearningPractical Learning: Creating a GUI Application Using an IDE

  1. If you have a programming environment such as Microsoft Visual Studio (Professional, Ultimate, or Express), launch it
  2. On the main menu, click File -> New Project...
  3. In the left list, select your language and select Windows Forms Application
  4. Set the Name to Exercise 1
  5. Click OK
  6. On the main menu, click Project -> Add Windows Form...
  7. Set the Name to Departments
  8. Click Add
  9. Design the form as follows:
     
    Departments
    Control (Name) Text
    DataGridView DataGridView dgvDepartments  
    GroupBox GroupBox   New Department
    Label Label   Department Code:
    TextBox TextBox txtDepartmentCode  
    Label Label   Department:
    TextBox TextBox txtDepartment  
    Button Button btnAdd Add
    Button Button Close btnClose
  10. Double-click an unoccupied area of the form, such as on the left side of Close
  11. Return to the form and double-click the Add button
  12. Return to the form and double-click the Close button
  13. Change the file as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace Exercise1
    {
        public partial class Departments : Form
        {
            string serverName;
    
            public Departments()
            {
                InitializeComponent();
            }
    
            private void ShowDepartments()
            {
                using (SqlConnection cntExercise =
                    new SqlConnection("Data Source=" + serverName + 
                        ";Database='Exercise1';Integrated Security=yes;"))
                {
                    SqlCommand cmdDepartments =
                    new SqlCommand("SELECT ALL * FROM Management.Departments;",
                    	       cntExercise);
    
                    SqlDataAdapter odaDepartments =
                    	new SqlDataAdapter(cmdDepartments);
                    DataSet dsDepartments = new DataSet("DepartmentsSet");
    
                    cntExercise.Open();
    
                    odaDepartments.Fill(dsDepartments);
                    dgvDepartments.DataSource = dsDepartments.Tables[0];
                }
            }
    
            private void Departments_Load(object sender, EventArgs e)
            {
                // Replace EXPRESSION with the name of the computer
                // on which Microsoft SQL Server is installed
                serverName = "EXPRESSION";
                ShowDepartments();
            }
    
            private void btnAdd_Click(object sender, EventArgs e)
            {
                if (string.IsNullOrEmpty(txtDepartmentCode.Text))
                {
                    MessageBox.Show("You must enter a department code.",
                                    "Exercise", MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                    return;
                }
    
                if (string.IsNullOrEmpty(txtDepartment.Text))
                {
                    MessageBox.Show("You must enter the name of a department.",
                                    "Exercise", MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                    return;
                }
    
                using (SqlConnection cntExercise =
                   new SqlConnection("Data Source=" + serverName +
                       ";Database='Exercise1';Integrated Security=yes;"))
                {
                    SqlCommand cmdDepartments =
                    new SqlCommand("INSERT INTO Management.Departments VALUES(" +
                                   "'" + txtDepartmentCode.Text + "', '" +
                                   txtDepartment.Text + "');",
                                   cntExercise);
    
                    cntExercise.Open();
                    cmdDepartments.ExecuteNonQuery();
    
                    txtDepartmentCode.Text = "";
                    txtDepartment.Text = "";
                }
    
                ShowDepartments();
            }
    
            private void btnClose_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
  14. On the main menu, click Project -> Add Windows Form...
  15. Set the Name to Employees
  16. Click Add
  17. Design the form as follows:
     
    Employees
    Control (Name) Text
    DataGridView DataGridView dgvEmployees
    GroupBox GroupBox   New Employee
    Label Label   Employee #:
    MaskedTextBox MaskedTextBox txtEmployeeNumber  
    Label Label   First Name:
    TextBox TextBox txtFirstName  
    Label Label   Last Name:
    TextBox TextBox txtLastName  
    Label Label   Department:
    ComboBox ComboBox cbxDepartments  
    Label Label   Title:
    TextBox TextBox txtTitle  
    Button Button btnAdd Add
    Button Button Close btnClose
  18. Double-click an unoccupied area of the form, such as on the left side of Close
  19. Return to the form and double-click the Add button
  20. Return to the form and double-click the Close button
  21. Change the file as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace Exercise1
    {
        public partial class Employees : Form
        {
            string serverName;
    
            public Employees()
            {
                InitializeComponent();
            }
    
            private void ShowEmployees()
            {
                using (SqlConnection cntExercise =
                    new SqlConnection("Data Source=" + serverName +
                        ";Database='Exercise1';Integrated Security=yes;"))
                {
                    SqlCommand cmdEmployees =
                    new SqlCommand("SELECT ALL * FROM Personnel.Employees;",
                    	       cntExercise);
    
                    SqlDataAdapter odaEmployees = new SqlDataAdapter(cmdEmployees);
                    DataSet dsEmployees = new DataSet("EmployeesSet");
    
                    cntExercise.Open();
    
                    odaEmployees.Fill(dsEmployees);
                    dgvEmployees.DataSource = dsEmployees.Tables[0];
                }
            }
    
            private void Employees_Load(object sender, EventArgs e)
            {
                // Replace EXPRESSION with the name of the computer
                // on which Microsoft SQL Server is installed
                serverName = "EXPRESSION";
                ShowEmployees();
    
                using (SqlConnection cntExercise =
                    new SqlConnection("Data Source=" + serverName +
                        ";Database='Exercise1';Integrated Security=yes;"))
                {
                    SqlCommand cmdDepartments =
                    new SqlCommand("SELECT ALL * FROM Management.Departments;",
                    	       cntExercise);
    
                    cntExercise.Open();
                    SqlDataReader rdrDepartment = cmdDepartments.ExecuteReader();
    
                    while(rdrDepartment.Read())
                        cbxDepartments.Items.Add(rdrDepartment.GetString(0) +
                        			     " - " + rdrDepartment.GetString(1));
                }
            }
    
            private void btnAdd_Click(object sender, EventArgs e)
            {
                if (string.IsNullOrEmpty( txtEmployeeNumber.Text))
                {
                    MessageBox.Show("You must enter an employee number.",
                                    "Exercise", MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                    return;
                }
    
                if (string.IsNullOrEmpty(txtLastName.Text))
                {
                    MessageBox.Show("You must enter thelast name of the employee.",
                                    "Exercise", MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                    return;
                }
    
                using (SqlConnection cntExercise =
                   new SqlConnection("Data Source=" + serverName +
                       ";Database='Exercise1';Integrated Security=yes;"))
                {
                    SqlCommand cmdDepartments =
                    new SqlCommand("INSERT INTO Personnel.Employees VALUES(" +
                                   "'" + txtEmployeeNumber.Text + "', '" +
                                   txtFirstName.Text + "', '" +
                                   txtLastName.Text + "', '" +
                                   cbxDepartments.Text.Substring(0, 5) + "', '" +
                                   txtTitle.Text + "');",
                                   cntExercise);
    
                    cntExercise.Open();
                    cmdDepartments.ExecuteNonQuery();
    
                    txtEmployeeNumber.Text = "";
                    txtFirstName.Text = "";
                    txtLastName.Text = "";
                    cbxDepartments.Text = "";
                    txtTitle.Text = "";
                }
    
                ShowEmployees();
            }
    
            private void btnClose_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
  22. In the Solution Explorer, right-click Form1.cs and click Rename
  23. Type Exercise.cs and press Enter twice
  24. Design the form as follows:
     
    Exercise
    Control (Name) Text
    Button Button btnDepartments Departments...
    Button Button btnEmployees Employees...
    Button Button Close btnClose
  25. Double-click the Departments button
  26. Return to the form and double-click Departments
  27. Return to the form and double-click Employees
  28. Implement the events as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    
    namespace Exercise1
    {
        public partial class Exercise : Form
        {
            public Exercise()
            {
                InitializeComponent();
            }
    
            private void btnDepartments_Click(object sender, EventArgs e)
            {
                Departments depts = new Departments();
                depts.Show();
            }
    
            private void btnEmployees_Click(object sender, EventArgs e)
            {
                Employees empls = new Employees();
                empls.Show();
            }
    
            private void btnClose_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
  29. To execute the application, on the main menu, click Debug -> Start Without Debugging
 
 
 

Practical LearningPractical Learning: Creating a GUI Application Using a Text Editor

  1. Start Notepad
  2. Type the following code:
    using System;
    using System.Data;
    using System.Drawing;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    public class Departments : Form
    {
        private string serverName;
    
        private DataGridView dgvDepartments;
        private Label        lblDepartmentCode;
        private TextBox      txtDepartmentCode;
        private Label        lblDepartment;
        private TextBox      txtDepartment;
        private Button       btnAdd;
        private GroupBox     gbxNewDepartment;
        private Button       btnClose;
    
        public Departments()
        {
            InitializeComponent();
        }
    
        private void InitializeComponent()
        {
            dgvDepartments = new DataGridView();
            dgvDepartments.TabIndex = 1;
            dgvDepartments.Size = new System.Drawing.Size(268, 145);
            dgvDepartments.Location = new System.Drawing.Point(12, 12);
            dgvDepartments.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize;
    
            lblDepartmentCode = new Label();
            lblDepartmentCode.TabIndex = 1;
            lblDepartmentCode.AutoSize = true;
            lblDepartmentCode.Text = "Department Code:";
            lblDepartmentCode.Size = new System.Drawing.Size(93, 13);
            lblDepartmentCode.Location = new System.Drawing.Point(15, 27);
    
            txtDepartmentCode = new TextBox();
            txtDepartmentCode.TabIndex = 2;
            txtDepartmentCode.Size = new System.Drawing.Size(68, 20);
            txtDepartmentCode.Location = new System.Drawing.Point(114, 24);
    
            lblDepartment = new Label();
            lblDepartment.TabIndex = 3;
            lblDepartment.AutoSize = true;
            lblDepartment.Text = "Department:";
            lblDepartment.Size = new System.Drawing.Size(65, 13);
            lblDepartment.Location = new System.Drawing.Point(15, 53);
    
            txtDepartment = new TextBox();
            txtDepartment.TabIndex = 4;
            txtDepartment.Size = new System.Drawing.Size(141, 20);
            txtDepartment.Location = new System.Drawing.Point(114, 50);
    
            btnAdd = new Button();
            btnAdd.Text = "Add";
            btnAdd.TabIndex = 5;
            btnAdd.Location = new System.Drawing.Point(180, 85);
            btnAdd.Size = new System.Drawing.Size(75, 23);
            btnAdd.UseVisualStyleBackColor = true;
            btnAdd.Click += new System.EventHandler(this.btnAddClick);
    
            gbxNewDepartment = new GroupBox();
            gbxNewDepartment.Text = "New Department";
            gbxNewDepartment.Size = new System.Drawing.Size(268, 124);
            gbxNewDepartment.Location = new System.Drawing.Point(12, 168);
    
            gbxNewDepartment.Controls.Add(this.btnAdd);
            gbxNewDepartment.Controls.Add(this.txtDepartment);
            gbxNewDepartment.Controls.Add(this.lblDepartment);
            gbxNewDepartment.Controls.Add(this.txtDepartmentCode);
            gbxNewDepartment.Controls.Add(this.lblDepartmentCode);
    
            btnClose = new Button();
            btnClose.TabIndex = 3;
            btnClose.Text = "Close";
            btnClose.Size = new System.Drawing.Size(75, 23);
            btnClose.Location = new System.Drawing.Point(192, 304);
            btnClose.UseVisualStyleBackColor = true;
            btnClose.Click += new System.EventHandler(this.btnCloseClick);
    
            AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            AutoScaleMode = AutoScaleMode.Font;
            ClientSize = new System.Drawing.Size(292, 337);
    
            Controls.Add(this.dgvDepartments);
            Controls.Add(this.gbxNewDepartment);
            Controls.Add(this.btnClose);
            StartPosition = FormStartPosition.CenterScreen;
            Text = "Departments";
            Load += new System.EventHandler(this.DepartmentsLoad);
        }
    
        private void ShowDepartments()
        {
            using (SqlConnection cntExercise =
                    new SqlConnection("Data Source=" + serverName + 
                        ";Database='Exercise1';Integrated Security=yes;"))
            {
                SqlCommand cmdDepartments =
                    new SqlCommand("SELECT ALL * FROM Management.Departments;", cntExercise);
    
                SqlDataAdapter odaDepartments = new SqlDataAdapter(cmdDepartments);
                DataSet dsDepartments = new DataSet("DepartmentsSet");
    
                cntExercise.Open();
    
                odaDepartments.Fill(dsDepartments);
                dgvDepartments.DataSource = dsDepartments.Tables[0];
            }
        }
    
        private void DepartmentsLoad(object sender, EventArgs e)
        {
            // Replace EXPRESSION with the name of the computer
            // on which Microsoft SQL Server is installed
            serverName = "EXPRESSION";
            ShowDepartments();
        }
    
        private void btnAddClick(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtDepartmentCode.Text))
            {
                MessageBox.Show("You must enter a department code.",
                                "Exercise", MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
                return;
            }
    
            if (string.IsNullOrEmpty(txtDepartment.Text))
            {
                MessageBox.Show("You must enter the name of a department.",
                                "Exercise", MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
                return;
            }
    
            using (SqlConnection cntExercise =
                   new SqlConnection("Data Source=" + serverName +
                       ";Database='Exercise1';Integrated Security=yes;"))
            {
                SqlCommand cmdDepartments =
                    new SqlCommand("INSERT INTO Management.Departments VALUES(" +
                                   "'" + txtDepartmentCode.Text + "', '" +
                                   txtDepartment.Text + "');",
                                   cntExercise);
    
                cntExercise.Open();
                cmdDepartments.ExecuteNonQuery();
    
                txtDepartmentCode.Text = "";
                txtDepartment.Text = "";
            }
    
            ShowDepartments();
        }
    
        private void btnCloseClick(object sender, EventArgs e)
        {
            Close();
        }
    }
  3. To start a new file, on the main menu, click File -> New
  4. When asked whether you want to save, click Save
  5. In the top combo box, select the C: drive
  6. On the Toolbar, click New Folder
  7. Type ClientServer and press Enter twice (to display the folder in the top combo box)
  8. Change the name of the file to "Departments.cs"
  9. Click Save
  10. In the empty document, type the following:
    using System;
    using System.Data;
    using System.Drawing;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    public class Employees : Form
    {
        private string serverName;
    
        private DataGridView  dgvEmployees;
        private Label         lblEmployeeNumber;
        private MaskedTextBox txtEmployeeNumber;
        private Label 	  lblFirstName;
        private TextBox 	  txtFirstName;
        private Label 	  lblLastName;
        private TextBox 	  txtLastName;
        private Label 	  lblDepartment;
        private ComboBox 	  cbxDepartments;
        private Label 	  lblTitle;
        private TextBox 	  txtTitle;
        private Button 	  btnAdd;
        private GroupBox 	  gbxNewEmployee;
        private Button 	  btnClose;
    
        public Employees()
        {
            InitializeComponent();
        }
    
        private void InitializeComponent()
        {
            dgvEmployees = new DataGridView();
            dgvEmployees.TabIndex = 0;
            dgvEmployees.Size = new System.Drawing.Size(373, 145);
            dgvEmployees.Location = new System.Drawing.Point(12, 12);
            dgvEmployees.Anchor = AnchorStyles.Top  | AnchorStyles.Bottom |
    			      AnchorStyles.Left | AnchorStyles.Right;
             dgvEmployees.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize;
    
            lblEmployeeNumber = new Label();
            lblEmployeeNumber.TabIndex = 0;
            lblEmployeeNumber.AutoSize = true;
            lblEmployeeNumber.Text = "Employee #:";
            lblEmployeeNumber.Size = new System.Drawing.Size(66, 13);
            lblEmployeeNumber.Location = new System.Drawing.Point(15, 22);
    
            txtEmployeeNumber = new MaskedTextBox();
            txtEmployeeNumber.TabIndex = 1;
            txtEmployeeNumber.Mask = "000-000";
            txtEmployeeNumber.Size = new System.Drawing.Size(53, 20);
            txtEmployeeNumber.Location = new System.Drawing.Point(114, 22);
    
            lblFirstName = new Label();
            lblFirstName.TabIndex = 2;
            lblFirstName.AutoSize = true;
            lblFirstName.Text = "First Name:";
            lblFirstName.Size = new System.Drawing.Size(60, 13);
            lblFirstName.Location = new System.Drawing.Point(15, 53);
    
            txtFirstName = new TextBox();
            txtFirstName.TabIndex = 3;
            txtFirstName.Size = new System.Drawing.Size(141, 20);
            txtFirstName.Location = new System.Drawing.Point(114, 50);
    
            lblLastName = new Label();
            lblLastName.TabIndex = 4;
            lblLastName.AutoSize = true;
            lblLastName.Text = "Last Name:";
            lblLastName.Size = new System.Drawing.Size(61, 13);
            lblLastName.Location = new System.Drawing.Point(15, 79);
    
            txtLastName = new TextBox();
            txtLastName.TabIndex = 5;
            txtLastName.Size = new System.Drawing.Size(141, 20);
            txtLastName.Location = new System.Drawing.Point(114, 76);
    
            lblDepartment = new Label();
            lblDepartment.TabIndex = 6;
            lblDepartment.AutoSize = true;
            lblDepartment.Text = "Department:";
            lblDepartment.Size = new System.Drawing.Size(65, 13);
            lblDepartment.Location = new System.Drawing.Point(15, 105);
    
            cbxDepartments = new ComboBox();
            cbxDepartments.TabIndex = 7;
            cbxDepartments.FormattingEnabled = true;
            cbxDepartments.Size = new System.Drawing.Size(141, 21);
            cbxDepartments.Location = new System.Drawing.Point(114, 101);
    
            btnAdd = new Button();
            btnAdd.Text = "Add";
            btnAdd.TabIndex = 10;
            btnAdd.UseVisualStyleBackColor = true;
            btnAdd.Size = new System.Drawing.Size(75, 23);
            btnAdd.Location = new System.Drawing.Point(281, 154);
            btnAdd.Click += new System.EventHandler(btnAdd_Click);
    
            lblTitle = new Label();
            lblTitle.TabIndex = 8;
            lblTitle.Text = "Title:";
            lblTitle.AutoSize = true;
            lblTitle.Size = new System.Drawing.Size(30, 13);
            lblTitle.Location = new System.Drawing.Point(15, 131);
    
            txtTitle = new TextBox();
            txtTitle.TabIndex = 9;
            txtTitle.Size = new System.Drawing.Size(242, 20);
            txtTitle.Location = new System.Drawing.Point(114, 128);
    
            gbxNewEmployee = new GroupBox();
            gbxNewEmployee.Text = "New Employee";
            gbxNewEmployee.Size = new System.Drawing.Size(373, 193);
            gbxNewEmployee.Location = new System.Drawing.Point(12, 168);
            gbxNewEmployee.Anchor = AnchorStyles.Bottom | AnchorStyles.Left | AnchorStyles.Right;
    
            gbxNewEmployee.Controls.Add(lblEmployeeNumber);
            gbxNewEmployee.Controls.Add(txtEmployeeNumber);
            gbxNewEmployee.Controls.Add(lblFirstName);
            gbxNewEmployee.Controls.Add(txtFirstName);
            gbxNewEmployee.Controls.Add(lblLastName);
            gbxNewEmployee.Controls.Add(txtLastName);
            gbxNewEmployee.Controls.Add(lblDepartment);
            gbxNewEmployee.Controls.Add(cbxDepartments);
            gbxNewEmployee.Controls.Add(lblTitle);
            gbxNewEmployee.Controls.Add(txtTitle);
            gbxNewEmployee.Controls.Add(btnAdd);
    
            btnClose = new Button();
            btnClose.TabIndex = 2;
            btnClose.Text = "Close";
            btnClose.UseVisualStyleBackColor = true;
            btnClose.Size = new System.Drawing.Size(75, 23);
            btnClose.Location = new System.Drawing.Point(293, 376);
            btnClose.Anchor = AnchorStyles.Bottom | AnchorStyles.Right;
            btnClose.Click += new System.EventHandler(btnClose_Click);
     
             Text = "Employees";
            ClientSize = new System.Drawing.Size(401, 412);
        
    	Controls.Add(dgvEmployees);
            Controls.Add(gbxNewEmployee);
            Controls.Add(btnClose);
             
            StartPosition = FormStartPosition.CenterScreen;
            Load += new System.EventHandler(Employees_Load);
        }
    
        private void ShowEmployees()
        {
            using (SqlConnection cntExercise =
                    new SqlConnection("Data Source=" + serverName +
                        ";Database='Exercise1';Integrated Security=yes;"))
            {
                SqlCommand cmdEmployees =
                    new SqlCommand("SELECT ALL * FROM Personnel.Employees;", cntExercise);
    
                SqlDataAdapter odaEmployees = new SqlDataAdapter(cmdEmployees);
                DataSet dsEmployees = new DataSet("EmployeesSet");
    
                cntExercise.Open();
    
                odaEmployees.Fill(dsEmployees);
                dgvEmployees.DataSource = dsEmployees.Tables[0];
            }
        }
    
        private void Employees_Load(object sender, EventArgs e)
        {
            // Replace EXPRESSION with the name of the computer
            // on which Microsoft SQL Server is installed
            serverName = "EXPRESSION";
            ShowEmployees();
    
            using (SqlConnection cntExercise =
                    new SqlConnection("Data Source=" + serverName +
                        ";Database='Exercise1';Integrated Security=yes;"))
            {
                SqlCommand cmdDepartments =
                    new SqlCommand("SELECT ALL * FROM Management.Departments;", cntExercise);
    
                cntExercise.Open();
                SqlDataReader rdrDepartment = cmdDepartments.ExecuteReader();
    
                while(rdrDepartment.Read())
                    cbxDepartments.Items.Add(rdrDepartment.GetString(0) + " - " + rdrDepartment.GetString(1));
            }
        }
    
        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty( txtEmployeeNumber.Text))
            {
                MessageBox.Show("You must enter an employee number.",
                                "Exercise", MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
                return;
            }
    
            if (string.IsNullOrEmpty(txtLastName.Text))
            {
                MessageBox.Show("You must enter thelast name of the employee.",
                                "Exercise", MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
                return;
            }
    
            using (SqlConnection cntExercise =
                   new SqlConnection("Data Source=" + serverName +
                       ";Database='Exercise1';Integrated Security=yes;"))
            {
                SqlCommand cmdDepartments =
                    new SqlCommand("INSERT INTO Personnel.Employees VALUES(" +
                                   "'" + txtEmployeeNumber.Text + "', '" +
                                   txtFirstName.Text + "', '" +
                                   txtLastName.Text + "', '" +
                                   cbxDepartments.Text.Substring(0, 5) + "', '" +
                                   txtTitle.Text + "');",
                                   cntExercise);
    
                cntExercise.Open();
                cmdDepartments.ExecuteNonQuery();
    
                txtEmployeeNumber.Text = "";
                txtFirstName.Text = "";
                txtLastName.Text = "";
                cbxDepartments.Text = "";
                txtTitle.Text = "";
            }
    
            ShowEmployees();
        }
    
        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
  11. To create a new file, on the main menu, click File -> New
  12. When asked whether you want to save, click Save
  13. Make sure the top combo box displays ClientServer.
    Set the name of the file to "Employees.cs"
  14. Click Save
  15. In the empty document, type the following:
    using System;
    using System.Data;
    using System.Drawing;
    using System.Windows.Forms;
    
    public class Exercise : Form
    {
        private Button btnDepartments;
        private Button btnEmployees;
        private Button btnClose;
    
        public Exercise()
        {
            InitializeComponent();
        }
    
        private void InitializeComponent()
        {
            btnDepartments = new Button();
            btnDepartments.TabIndex = 0;
            btnDepartments.Text = "Departments...";
            btnDepartments.UseVisualStyleBackColor = true;
            btnDepartments.Size = new System.Drawing.Size(259, 65);
            btnDepartments.Location = new System.Drawing.Point(21, 12);
            btnDepartments.Font = new System.Drawing.Font("Century Schoolbook",
    						      21.75F,
    				  		      System.Drawing.FontStyle.Bold,
    						      System.Drawing.GraphicsUnit.Point,
    						      0);
            btnDepartments.Click += new System.EventHandler(this.btnDepartments_Click);
    
            btnEmployees = new Button();
            btnEmployees.TabIndex = 1;
            btnEmployees.Text = "Employees...";
            btnEmployees.UseVisualStyleBackColor = true;
            btnEmployees.Size = new System.Drawing.Size(259, 65);
            btnEmployees.Location = new System.Drawing.Point(21, 94);
            btnEmployees.Font = new System.Drawing.Font("Century Schoolbook",
    						    21.75F,
    						    System.Drawing.FontStyle.Bold,
    						    System.Drawing.GraphicsUnit.Point,
    						    0);
            btnEmployees.Click += new System.EventHandler(this.btnEmployees_Click);
    
            btnClose = new Button();
            btnClose.TabIndex = 2;
            btnClose.Text = "Close";
            btnClose.UseVisualStyleBackColor = true;
            btnClose.Size = new System.Drawing.Size(259, 65);
            btnClose.Location = new System.Drawing.Point(21, 176);
            btnClose.Font = new System.Drawing.Font("Century Schoolbook",
    						21.75F,
    						System.Drawing.FontStyle.Bold,
    						System.Drawing.GraphicsUnit.Point,
    						0);
            btnClose.Click += new System.EventHandler(this.btnClose_Click);
    
            ClientSize = new System.Drawing.Size(301, 263);
            Controls.Add(this.btnClose);
            Controls.Add(this.btnEmployees);
            Controls.Add(this.btnDepartments);
            StartPosition = FormStartPosition.CenterScreen;
            Text = "Client/Server Exercise";
        }
    
        private void btnDepartments_Click(object sender, EventArgs e)
        {
            Departments depts = new Departments();
            depts.Show();
        }
    
        private void btnEmployees_Click(object sender, EventArgs e)
        {
            Employees empls = new Employees();
            empls.Show();
        }
    
        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
    
    public class Program
    {
        [STAThread]
        static int Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Exercise());
    
    	return 0;
        }
    }
  16. To close Notepad, on the main menu, click File -> Exit
  17. When asked whether you want to save, click Save
  18. Set the file name to "Exercise.cs"
  19. Click Save
  20. To open the Command Prompt (Start -> All Programs -> Accessories -> Command Prompt)
  21. To switch to the root, type CD\ and press Enter
  22. To switch to the folder that contains the files of this project, type CD ClientServer and press Enter
  23. To build and compile the project, type the following:
    C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc /r:System.dll
    /r:System.Drawing.dll /r:System.Data.dll /r:System.Xml.dll /r:System.Windows.For
    ms.dll /target:winexe Departments.cs Employees.cs Exercise.cs
  24. Press Enter
  25. To execute, type Exercise and press Enter

Exercise

You can copy the executable that was created, named Exercise.exe, and paste it in the computers that need to access the database.

Practical LearningPractical Learning: Using the Application

  1. Click Departments
  2. Create a few deparments as follows:
     
    Department Code Department
    HMNRS Human Resources
    ITECM Information Technology and Management
    ACCNT Accounting
  3. Close the Departments form
  4. Click Employees
  5. Create a few employees as follows:
     
    Employee # First Name Last Name Department Title
    270-284 Paul Motto HMNRS General Manager
    579-485 Joan Stohl ITECM Webmaster
    852-662 Thomas Pratt HMNRS Intern
    279-484 Martine Schlamme ACCNT Accountant
  6. Close the forms
 
 
   
 

Home Copyright © 2010-2012 FunctionX