Home

ADO.NET How To: Add/Create a Record

     

Introduction

Here is an example of creating a new record (or adding a record) to a table of a Microsoft SQL Server database:

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);
    }
}

Here is an example of creating many records one at a time:

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);
    }
}

Here is an example of adding many records using one statement:

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);
    }
}

Here is an example that sets the identity column ON (SET IDENTITY_INSERT):

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection = new SqlConnection("Data Source=(local);" +
                                                        "Database='Exercise';" +
                                                        "Integrated Security=yes;"))
    {
        SqlCommand command =
            new SqlCommand("SET IDENTITY_INSERT StoreItems ON;" +
                           "INSERT INTO StoreItems(ItemID, Category, [Item Name], Size, [Unit Price])" +
                           "VALUES(10, N'Girls', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95);",
                           connection);
        connection.Open();
        command.ExecuteNonQuery();

        MessageBox.Show("A new record has been added to the StoreItems table.");
    }
}


 
 

Home Copyright © 2010-2014, FunctionX