Creating Records

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        
    }

    protected void btnCreateTable_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
		new SqlConnection("Data Source=(local);" +
				  "Database='Exercise';" +
				  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("CREATE TABLE Employees(" +
            "EmployeeNumber nchar(10), EmployeeName nvarchar(50)," +
            "DateHired date, HourlySalary money);", connection);
        
        connection.Open();
        command.ExecuteNonQuery();
        connection.Close();
    }
}

Because a table is the central part of a database, the information it holds must be organized. To better manage its information, data of a table is arranged in a series of fields.

Columns are used to organize data by categories. One of the actual purposes of a table is to display data that is available for each field. 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, visually or programmatically. 

In the SQL, data entry is performed using the INSERT combined with the VALUES keywords. The primary statement uses the following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n);

You can use the INTO keyword between the INSERT keyword and the TableName:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

The TableName must be a valid name of an existing table in the database you are using. 

The VALUES keyword is followed by parentheses in which you include the values of the columns:

• If the column is a BIT data type, you must specify one of its values as 0 or 1.
• If the column is a number-based (int, bigint, smallint), write it appropriately
• 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 is a date or time data type (datetime2), make sure you provide a valid date.
• If the data type of a column is a string type, include its value between single quotes. Example are 'HHR-604' or 'D'

In your web application, you can pass the INSERT statement to a command object. 

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:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        
    }

    protected void btnCreateTable_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
		new SqlConnection("Data Source=(local);" +
				  "Database='Exercise';" +
				  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("CREATE TABLE Employees(" +
            "EmployeeNumber nchar(10), EmployeeName nvarchar(50)," +
            "DateHired date, HourlySalary money);", connection);
        
        connection.Open();
        command.ExecuteNonQuery();
        connection.Close();
    }
    protected void btnCreateRecord_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
        new SqlConnection("Data Source=(local);" +
                  "Database='Exercise';" +
                  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("INSERT INTO Employees " +
            "VALUES('65-804', 'Jeannette Childs'," +
            "'20081022', 24.85);", connection);

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

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.

Adjacent data entry requires that you know the position of each column. 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. Here are examples:

void btnCreateRecords_Click(object sender, EventArgs e)
{
    SqlConnection connection =
        new SqlConnection("Data Source=(local);" +
                  "Database='Exercise';" +
                  "Integrated Security=yes;");
    SqlCommand command = new SqlCommand("INSERT INTO Employees(" +
            "EmployeeName, DateHired, HourlySalary)" +
            "VALUES('Saul Tandeng', '20060616', 22.60);" +
            "INSERT INTO Employees(EmployeeName, EmployeeNumber," +
            "HourlySalary) VALUES('Hank Helms','72-644', 16.20);" +
            "INSERT INTO Employees(EmployeeName," +
            "HourlySalary, EmployeeNumber) VALUES(" +
            "'Ernestine Mongo', 18.20, '28-604');" +
            "INSERT INTO Employees(DateHired, HourlySalary," +
            "EmployeeName, EmployeeNumber)" +
            "VALUES('20040820', 32.05, 'April Laurenthall', '52-260');" +
            "INSERT INTO Employees(DateHired, HourlySalary," +
            "EmployeeNumber) VALUES('20020928', 24.60," +
            "'38-263');", connection);

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