Home

Introduction to the Records of a Database

   

Records Fundamentals

 

Introduction

A table is an object that holds the information of a database. This means that the table must exist (must have been created) in a database. Here is an example of such a table:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnDatabase;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnDatabase = new Button();
        btnDatabase.Text = "Database";
        btnDatabase.Location = new Point(12, 12);
        btnDatabase.Click += new EventHandler(btnDatabaseClick);

        Controls.Add(btnDatabase);
    }

    void CreateDatabase()
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Integrated Security=SSPI;"))
        {
            SqlCommand command =
                new SqlCommand("CREATE DATABASE VideoCollection1 " +
                              "ON PRIMARY " +
                              "( NAME = VideosRepository, FILENAME = 'C:\\VideoCollection\\Video1.mdf') " +
                              "LOG ON " +
                              "( NAME = VideosLog, FILENAME = 'C:\\VideoCollection\\Video1.ldf');",
                              connection);

            connection.Open();
            command.ExecuteNonQuery();
            MessageBox.Show("A database named VideoCollection1 has been created.",
                            "Video Collection",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("CREATE TABLE Videos(" +
                               "Title nvarchar(80), " +
                               "CopyrightYear smallint, " +
                               "Length nvarchar(30), " +
                               "Rating nvarchar(6), " +
                               "WideScreen bit);",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

            MessageBox.Show("A table named \"Videos\" has been created.",
                            "Video Collection",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        CreateDatabase();
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Because a table is the central part of a database, the information it holds must be meticulously organized. To better manage its information, data of a table is arranged in a series of cells. Once a table contains information, you can review it using either the Microsoft SQL Server Management Studio or a Windows application.

Practical LearningPractical Learning: Introducing Database Records

  1. Start Microsoft Visual Studio and create a new Windows Application named CollegeParkAutoRepair3
  2. In the Server Explorer, right-click Data Connections and click Add New SQL Server Database
  3. In the Server Name combo box, select the server or type (local)
  4. Set the name of the database to CPAR2
  5. Click OK
  6. In the Server Explorer, expand server.CPAR2.dbo
  7. Under it, right-click Tables and click Add New Table
  8. Complete the table with following columns:
     
    Column Name Data Type
    ReceiptNumber int
    OrderDate date
    OrderTime date
    CustomerName nvarchar(80)
    CarMake nvarchar(50)
    CarModel nvarchar(50)
    CarYear smallint
    ProblemDescription ntext
    TotalParts money
    TotalLabor money
    TaxRate decimal(6,2)
    TaxAmount money
    TotalOrder money
    Recommendations ntext
  9. To save the table, on the Standard toolbar, click the Save button
  10. Type RepairOrders as the name of the table and click OK
  11. Close the RepairOrders window
  12. In the Solution Explorer, right-click Form1.cs and click Rename
  13. Type OrderProcessing.cs and press Enter twice
  14. Design the form as follows:
     
    College Park Auto Repair
    Control Name Text Other Properties
    GroupBox GroupBox   Order Identification  
    Label     Order Date/Time:  
    DateTimePicker   dtpOrderDate    
    DateTimePicker   dtpOrderTime   Format: Time
    ShowUpDown: True
    Label     Customer Name:  
    TextBox   txtCustomerName    
    Label     Make / Model:  
    TextBox   txtMake    
    TextBox   txtModel    
    Label     Year:  
    TextBox   txtCarYear   TextAlign: Right
    Label     Problem Description:  
    TextBox   txtProblemDescription   Multiline: True
    ScrollBars: Vertical
    GroupBox     Order Summary  
    Label     Total Parts:  
    TextBox   txtTotalParts 0.00 TextAlign: Right
    Label     Total Labor:  
    TextBox   txtTotalLabor 0.00 TextAlign: Right
    Label     Tax Rate:  
    TextBox   txtTaxRate 7.75 TextAlign: Right
    Label     %  
    Label     Tax Amount:  
    TextBox   txtTaxAmount 0.00 TextAlign: Right
    Label     Total Order:  
    TextBox   txtTotalOrder 0.00 TextAlign: Right
    Label     Recommendations  
    TextBox   txtRecommendations   Multiline: True
    ScrollBars: Vertical
    Button   btnSaveRepairOrder Save Repair Order  
    Button   btnNewRepairOrder New Repair Order/Reset  
    Button   btnClose Close  
  15. Double-click the New Repair Order button and implement its event 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 CollegeParkAutoRepair3
    {
        public partial class OrderProcessing : Form
        {
    	public OrderProcessing()
    	{
    	    InitializeComponent();
    	}
    
    	private void btnNewRepairOrder_Click(object sender, EventArgs e)
    	{
    	    dtpOrderDate.Value = DateTime.Today.Date;
    	    dtpOrderTime.Value = DateTime.Now;
    	    txtCustomerName.Text = ""; txtCarMake.Text = "";
    	    txtCarModel.Text = ""; txtCarYear.Text = "";
    	    txtProblemDescription.Text = "";
    
    	    txtTotalParts.Text = "0.00"; txtTotalLabor.Text = "0.00";
    	    txtTaxRate.Text = "7.75"; txtTaxAmount.Text = "0.00";
    	    txtTotalOrder.Text = "0.00"; txtRecommendations.Text = "0.00";
    	}
        }
    }
  16. Return to the form

Table Data Navigation

Data Navigation consists of displaying and viewing data. Because information of a database is stored in tables, your primary means of viewing data consists of opening a table in a view that displays its information.

When a table displays its records, you navigate through its fields using the mouse or the keyboard. With the mouse, to get to any cell, you can just click it. To navigate through records using the keyboard, you can press:

  • The right arrow key to move to the right cell; if the caret is already in the most right cell, it would be moved to the first cell of the next record, up to the last empty cell of the first empty record
  • The left arrow key to move to the previous cell; if the caret is in, or reaches, the most left cell of the first record, nothing would happen when you press the the left arrow key
  • The down arrow key to move to the cell under the current one; if the caret is already in the last cell of the current column, nothing would happen
  • The up arrow key to move to the cell just above the current one; if the caret is already in the first cell of the current column, nothing would happen
  • The Page Down to move to the next group of cell that would correspond to the next page; if the number of records is less than a complete page, the caret would move to the last cell of the current column
  • The Page Up to move to the next group of cell that would correspond to the next page; if the number of records is less than a complete page, the caret would move to the first cell of the current column

Visual Data Entry

 

Introduction

As you are probably aware already, columns are used to organize data by categories. Each column has a series of fields under the column header. One of the actual roles of a table is to display data that is available for each field under a particular column. Data entry consists of providing the necessary values of the fields of a table. Data is entered into a field and every time this is done, the database creates a row of data. This row is called a record. This means that entering data also self-creates rows.

There are various ways you can perform data entry for a Microsoft SQL Server table:

  • You can open the table from the Server Explorer
  • You can import data from another object or another database
  • You can use an external application such as Microsoft SQL Server Management Studio, Microsoft Access, Microsoft Visual Basic, C++Builder, Microsoft Visual C++, Delphi, Microsoft Visual Basic, C#, Visual C#, etc
  • You can create a Windows Forms Application in Microsoft Visual Studio

Using the Server Explorer

Probably the easiest and fastest way to enter data into a table is by using either Microsoft SQL Server Management Studio or Microsoft Visual Studio. Of course, you must first open the desired table from a database connection. In the Server Explorer, after expanding the connection to the database and the Tables nodes, right-click the desired table and click Show Table Data. If the table does not contain data, it would appear with one empty row. If some records were entered already, their rows would show and the table would provide an empty row at the end, expecting a new record.

To perform data entry on a table, you can click in a cell. Each column has a title, called a caption, on top. This gray section on top is called a column header. In Microsoft SQL Server, it displays the actual name of the column. You refer to the column header to know what kind of data should/must go in a field under a particular column. This is why you should design your columns meticulously. After identifying a column, you can type a value. Except for text-based columns, a field can reject a value if the value does not conform to the data type that was set for the column. This means that in some circumstances, you may have to provide some or more explicit information to the user.

Practical LearningPractical Learning: Introducing Data Entry

  1. In the Server Explorer, if necessary, expand the Tables node under Server.CPAR2.dbo.
    Right-click the RepairOrders node and click Show Table Data
  2. Click the empty box under CustomerName, type Jamil Harrah
  3. Click the box under ReceiptNumber, type 1244LPD and press Enter
  4. Notice that you receive an error because the letters are not allowed:
     
    Error
  5. Click OK on the error message box.
  6. Change the value to 1244 and press Tab
  7. Under OrderDate, type 2006/02/16 and press the down arrow key
  8. Notice that the date changes to 2/16/2006
  9. For the second box under OrderDate, type 06/06/06 and press Tab
  10. For the OrderTime of the second record, type 14:48 and press the up arrow key
  11. Notice that the value changes to today's date followed by the time you had entered
  12. For the first record under OrderTime, type 04:25 PM and press Enter
  13. Close the RepairOrders window

Data Entry With SQL

 

Introduction

To perform data entry using SQL, use the INSERT combined with the VALUES keywords. The primary statement uses the following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n);

Alternatively, or to be more precise, you can use the INTO keyword between the INSERT keyword and the TableName factor to specify that you are entering data in the table. This is done with the following syntax:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

The TableName factor must be a valid name of an existing table in the database you are using. If the name is wrong, the SQL interpreter would simply consider that the table you are referring to doesn't exist. Consequently, you would receive an error.

The VALUES keyword indicates that you are ready to list the values of the columns. The values of the columns must be included in parentheses.

If the column is a BIT data type, you must specify one of its values as 0 or 1.

If the column is a numeric type, you should pay attention to the number you type. If the column was configured to receive an integer (int, bigint, smallint), you should provide a valid natural number without the decimal separator.

If the column is for a decimal number (float, real, decimal, numeric), you can type the value with its character separator (the period for US English).

If the column was created for a date or time data type (datetime or smalldatetime), make sure you provide a valid date.

If the data type of a column is a string type, you should include its entry between single quotes. For example, a shelf number can be specified as 'HHR-604' and a middle initial can be given as 'D'.

In your Windows Forms Application, you can pass the INSERT statement to a command object. 

Adjacent Data Entry

The most common technique of performing data entry requires that you know the sequence of fields of the table in which you want to enter data. With this subsequent list in mind, enter the value of each field in its correct position. Here is an example:

void CreateRecord()
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("INSERT INTO Videos " +
                               "VALUES(N'A Few Good Men', 1992, N'138 Minutes', N'R', 0);",
                               connection);
        connection.Open();
        command.ExecuteNonQuery();

        MessageBox.Show("A new record has been created.",
                        "Video Collection",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
}

During data entry on adjacent fields, if you do not have a value for a numeric field, you should type 0 as its value. For a string field whose data you do not have and cannot provide, type two single-quotes '' to specify an empty field.

Practical LearningPractical Learning: Performing Data Entry

  1. On the OrderProcessing form, double-click the Save Repair Order button and implement its event as follows:
    private void btnSaveRepairOrder_Click(object sender, EventArgs e)
    {
        string strConnection =
    	"Data Source=(local);" +
    	"Database='CollegeParkAutoRepair1';" +
    	"Integrated Security=yes;";
        string strRepairOrder = "INSERT INTO RepairOrders(CustomerName, " +
    		"CarMake, CarModel, CarYear, ProblemDescription, " +
    		"TotalParts, TotalLabor, TotalOrder) " +
    		"VALUES ('" + txtCustomerName.Text + "', '" +
    		txtCarMake.Text + "', '" + txtCarModel.Text + "', '" +
    		txtCarYear.Text + "', '" + txtProblemDescription.Text + 
    		"', '" + txtTotalParts.Text + "', '" + txtTotalLabor.Text +
    		"', '" + txtTotalOrder.Text + "');";
    
        using (SqlConnection connection = new SqlConnection(strConnection))
        {
    	SqlCommand command =
    		new SqlCommand(strRepairOrder, connection);
    
    	connection.Open();
    	command.ExecuteNonQuery();
    
    	MessageBox.Show("A new repair order has been created.");
        }
    }
  2. Execute the application
  3. Enter all values in the form
  4. Click the Save Repair Order button. Here is an example:
     
    Repair Order
  5. Close the form and return to your programming environment

Random Data Entry

The adjacent data entry we have performed requires that you know the position of each column. The SQL provides an alternative that allows you to perform data entry using the name of a column instead of its position. This allows you to provide the values of columns in an order of your choice. We have just seen a few examples where the values of some of the fields were not available during data entry. Instead of remembering to type 0 or NULL for such fields or leaving empty quotes for a field, you can use the fields' names to specify the fields whose data you want to provide.

To perform data entry in an order of your choice, you must provide your list of the fields of the table. You can either use all columns or provide a list of the same columns but in your own order. In the same way, you don't have to provide data for all fields, just those you want, in the order you want.

Here are examples:

void RandomlyCreateRecords()
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("INSERT INTO Videos(Title, CopyrightYear, Length) " +
                               "VALUES(N'Silence of the Lambs (The)', 1991, N'118 Minutes'); " +
                               "INSERT INTO Videos(Title, WideScreen, Length) " +
                               "VALUES(N'Distinguished Gentleman (The)', 0, N'112 Minutes'); " +
                               "INSERT INTO Videos(Title, Length, WideScreen) " +
                               "VALUES(N'Lady Killers (The)', N'104 Minutes', 0); " +
                               "INSERT INTO Videos(Title, Length) " +
                               "VALUES(N'Ghosts of Mississippi', N'130 Minutes');",
                               connection);
        connection.Open();
        command.ExecuteNonQuery();
        MessageBox.Show("A few records have been created.",
                        "Video Collection",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    } 
}

Practical LearningPractical Learning: Performing Data Entry

  1. Change the code of the Save Repair Order button as follows:
    private void btnSaveRepairOrder_Click(object sender, EventArgs e)
    {
        string strConnection =
    	"Data Source=(local);" +
    	"Database='CPAR2';" +
    	"Integrated Security=yes;";
        string strRepairOrder = "INSERT INTO RepairOrders(CustomerName, " +
    		"CarMake, CarModel, CarYear, ProblemDescription, " +
    		"TotalParts, TotalLabor, TotalOrder) " +
    		"VALUES ('" + txtCustomerName.Text + "', '" +
    		txtCarMake.Text + "', '" + txtCarModel.Text + "', '" +
    		txtCarYear.Text + "', '" + txtProblemDescription.Text + 
    		"', '" + txtTotalParts.Text + "', '" + txtTotalLabor.Text +
    		"', '" + txtTotalOrder.Text + "');";
    
        using (SqlConnection connection = new SqlConnection(strConnection))
        {
    	SqlCommand command =
    		new SqlCommand(strRepairOrder, connection);
    
    	connection.Open();
    	command.ExecuteNonQuery();
    
    	MessageBox.Show("A new repair order has been created.");
        }
    }
  2. Execute the application
  3. Enter the customer name, the name of the car, its model, its year, a description of a job to be performed, the cost of the parts, the cost of the labor, and the total price paid to repair
  4. Click the Save Repair Order button
  5. Enter the values again and click the Save Repair Order button again
  6. Close the form and return to your programming environment

Creating Multiple Records

In previous sections, we added one record at a time. You can add various records with one call to INSERT. If you are adding a value to each column of the table, after the name of the table, type VALUES, open and close the first parentheses. Inside the parentheses, include the desired values. To add another record, type a comma after the closing parenthesis, open a new parenthesis, list the new values, and close the parenthesis. Do this as many times as you need to add records. Here is an example:

void CreateMultipleRecords()
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                          "Database='VideoCollection1';" +
                          "Integrated Security=yes;"))
    {
        SqlCommand command =
            new SqlCommand("INSERT INTO Videos " +
                           "VALUES(N'Her Alibi', 1998, N'94 Minutes', N'PG-13', 0), " +
                           "      (N'Memoirs of a Geisha', 2006, N'145 Minutes', N'PG-13', 1), " +
                           "      (N'Two for the Money', 2008, N'2 Hrs. 3 Mins.', N'R', 1);",
                           connection);

        connection.Open();
        command.ExecuteNonQuery();

        MessageBox.Show("Records have been added to the Videos table.",
                        "Video Collection",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
}

This is valid for adjacent data entry. If you want to follow your own order of columns, on the right side of the name of the table, include a list of columns in parentheses. Then, when giving the values, for each record, follow the order in which you listed the columns. Here is an example:

void CreateVariousRecords()
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                      "Database='VideoCollection1';" +
                      "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("INSERT INTO Videos(Rating, Title, Length) " +
                               "VALUES(N'R', N'Wall Street', N'126 Minutes')," +
                               "      (N'', N'Michael Jackson Live in Bucharest', N'122 Minutes')," +
                               "      (N'PG-13', N'Sneakers', N'2 Hrs. 6 Mins.')," +
                               "      (N'R', N'Soldier', N'99 Mins.');",
                               connection);

        connection.Open();
        command.ExecuteNonQuery();
        MessageBox.Show("Four records have been added to the Videos table.",
                        "Video Collection",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
}

Outputting the Insertion Result

In the techniques we have used so far, when or if the records were added to a table, whether the operation was successful or not, we had no way of finding out. One way you can get this information is to store the inserted records in another table. To support this, Transact-SQL provides the OUTPUT operator. The formula to use it is:

INSERT INTO TableName
OUTPUT INSERTED.Columns
VALUES(Value_1, Value_2, Value_X)

You start with the normal record insertion with the INSERT INTO TableName expression. This is followed by the OUTPUT operator followed by the INSERTED operator and a period. If you are adding a value for each record, follow the period with *. The statement continues with the VALUES operator that is followed by parentheses in which you list the values to be added to the table. Here is an example:

USE Exercise1;
GO

CREATE TABLE Videos
(
	Title nvarchar(50), 
	CopyrightYear smallint, 
        Length nvarchar(30),
        Rating nvarchar(6),
        WideScreen bit
)
GO

INSERT INTO Videos
OUTPUT inserted.*
VALUES(N'Platoon', 1986, N'120 Minutes', N'R', 1), " +
      (N'Armageddon', 1998, N'150 Mins', N'PG-13', 0), " +
      (N'People vs. Larry Flynt (The)', 1996, N'129 Minutes', N'R', 0);",;
GO

When this statement executes, if you are working in the Microsoft SQL Server Management Studio, the lower part would display a list of the records that were added.

If you use the above formula, when you close the database, the reference is lost. If you want to store the list of newly created records in a table, on the right side of the INSERTED operator and its period, type INTO followed by the name of the table that will receive the values. The table must have been created; that is, it must exist at the time this insertion operation is taking place. Here is an example:

void KeepAnArchiveOfRecordsCreated()
{
    using (SqlConnection connection =
               new SqlConnection("Data Source=(local);" +
                         "Database='VideoCollection1';" +
                         "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("CREATE TABLE Archives(" +
                               "Title nvarchar(50)," +
                               "CopyrightYear smallint," +
                               "Length nvarchar(30)," +
                               "Rating nvarchar(6)," +
                               "WideScreen bit);",
                               connection);

        connection.Open();
        command.ExecuteNonQuery();
        MessageBox.Show("To keep an archive of records added to the Videos, " +
                        "a table named Archives has been created.",
                        "Video Collection",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
        
    using (SqlConnection connection =
               new SqlConnection("Data Source=(local);" +
                         "Database='VideoCollection1';" +
                         "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("INSERT INTO Videos " +
                               "OUTPUT inserted.* INTO Archives " +
                       "VALUES(N'Platoon', 1986, N'120 Minutes', N'R', 1), " +
                       "      (N'Armageddon', 1998, N'150 Mins', N'PG-13', 0), " +
          "      (N'People vs. Larry Flynt (The)', 1996, N'129 Minutes', N'R', 0);",
                               connection);

        connection.Open();
        command.ExecuteNonQuery();
        MessageBox.Show("A few records have been created.",
                        "Video Collection",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
}

In this case, a copy of the newly created records would be stored in the indication table.

The above techniques assume that you are adding a complete record; that is, you are providing a value for each column of the table. We already saw that if you want to provide values for only some columns, after the name of the table, provide the list of columns in parentheses. To get the list of newly inserted records, after the OUTPUT keyword, type INSERTED followed by a period and followed by the name of the first column. Do this for each column. The formula to use is:

INSERT INTO TableName(Column_1, Column_2, Column_X)
OUTPUT INSERTED.Column_1, INSERTED.Column_2, INSERTED.Column_X
VALUES(Value_1, Value_2, Value_X)

Of course, you can list the columns in an order of your choice, as long as both the TableName and the OUTPUT section use the exact same order. Here is an example:

SqlCommand command =
        new SqlCommand("INSERT INTO Videos(CopyrightYear, Rating, Title)" +
                       "OUTPUT inserted.CopyrightYear, inserted.Rating, inserted.Title " +
                       "VALUES(1995, N'R', N'Bad Boys')," +
                       "      (2007, N'PG-13', N'Transformers');", . . .

In this case, when the statement has executed, the result would display in the lower portion of the Microsoft SQL Server Management Studio. If you want to store the result in a table, use the following formula

INSERT INTO TableName(Column_1, Column_2, Column_X)
OUTPUT INSERTED.Column_1, INSERTED.Column_2, INSERTED.Column_X INTO TargetTable
VALUES(Value_1, Value_2, Value_X)

Here is an example:

void CreateAndArchive()
{
    using (SqlConnection connection =
               new SqlConnection("Data Source=(local);" +
                         "Database='VideoCollection1';" +
                         "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("INSERT INTO Videos(CopyrightYear, Rating, Title)" +
                               "OUTPUT inserted.CopyrightYear, inserted.Rating, inserted.Title " +
                               "INTO Archives (CopyrightYear, Rating, Title)" +
                               "VALUES(1995, N'R', N'Bad Boys')," +
                               "      (2007, N'PG-13', N'Transformers');",
                               connection);

        connection.Open();
        command.ExecuteNonQuery();
        MessageBox.Show("New records have been created and archived.",
                        "Video Collection",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
}
 

Previous Copyright © 2010-2011 C#Key Next