Create Records using ADO.NET in ASP.NET Core Application

Create Records using ADO.NET in ASP.NET Core Application

Last ADO.NET Tutorial introduces ADO.NET and all it’s features and components. You can check this tutorial from this link.

I am now ready to Create Records in my inventory table of my database using ADO.NET. First I need to Create an Inventory Form in my View. From this form I will be able to create a new inventory record in my database.

So add a new View called Create.cshtml inside the ‘Views/Home’ folder and add the below code to it:

@model Inventory

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>ADO - Create Inventory</title>
    <link rel="stylesheet" asp-href-include="lib/bootstrap/dist/css/*.min.css" />
</head>
<body>
    <div class="container-fluid">
        <h1>Create an Inventory</h1>
        <form method="post">
            <div class="form-group">
                <label asp-for="Name"></label>
                <input class="form-control" asp-for="Name" />
            </div>
            <div class="form-group">
                <label asp-for="Price"></label>
                <input class="form-control" asp-for="Price" />
            </div>
            <div class="form-group">
                <label asp-for="Quantity"></label>
                <input class="form-control" asp-for="Quantity" />
            </div>
            <div class="text-center panel-body">
                <button type="submit" class="btn btn-sm btn-primary">Create</button>
            </div>
        </form>
    </div>
</body>
</html>
 

I added bootstrap CSS file link on the ‘head’ section to give proper styling to the form:

<link rel="stylesheet" asp-href-include="lib/bootstrap/dist/css/*.min.css" />

This View takes a model of type Inventory class type, and I am creating the labels and Input controls in the form, by using asp-for tag helper with the model fields.

The ‘asp-for’ tag helper also help me to create a Model Binding feature, so that I can get all the values of the input controls to my create action method.

Next, I need to add the Create action method to my Home Controller. In the Create action I will do the insertion of the Inventory record (which the user filled in the form) in the database.

Since the Create action will need to get the connection string information from the appsettings.json file, therefore I will use the Dependency Injection feature of ASP.NET Core MVC.

So first add to the Home Controller, the Microsoft.Extensions.Configuration; namespace, that contains this interface.

Then add a constructor that takes a parameter of IConfiguration type. Inside this constructor set the value of an IConfiguration type property to the constructor’s parameter value.

This code is given below:

using System;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;

namespace ADO.Controllers
{
    public class HomeController : Controller
    {
        public IConfiguration Configuration { get; }
        public HomeController(IConfiguration configuration)
        {
            Configuration = configuration;
        }	

        public IActionResult Index()
        {
            return View();
        }

    }
}

Now I have the Connection string’s property values in my Controller, I can easily do the database operations through ADO.NET.
So, add 2 Create actions in the Controller, one of type GET and other of type POST. In the POST one I will do the database operation.

The updated controller’s code containing the Create actions is given below:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using ADO.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;

namespace ADO.Controllers
{
    public class HomeController : Controller
    {
        public IConfiguration Configuration { get; }
        public HomeController(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IActionResult Index()
        {
            return View();
        }

        public IActionResult Create()
        {
            return View();
        }

        [HttpPost]
        public IActionResult Create(Inventory inventory)
        {
            string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string sql = $"Insert Into Inventory (Name, Price, Quantity) Values ('{inventory.Name}', '{inventory.Price}','{inventory.Quantity}')";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.CommandType = CommandType.Text;

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

            return View();
        }
    }
}

Explanation

The ASP.NET Core MVC uses dependency injection feature to provide the IConfiguration type property, called ‘Configuration’, with the appsettings.json file’s complete data.

In the first line I am fetching the DefaultConnection node’s value (that resides inside the ConnectionStrings node), and this given me the connection string’s value. This code is:

string connectionString = Configuration["ConnectionStrings:DefaultConnection"];

Next, I create an SqlConnection class object (namespace System.Data.SqlClient), by passing the connection string to it’s constructor. This code is given below:

using (SqlConnection connection = new SqlConnection(connectionString))
{
//...
}

I will be working with SqlCommand class (also from the namespace System.Data.SqlClient), in order to create an Inventory record in the database.

The SqlCommand class has a method called ExecuteNonQuery to execute an SQL statement and SQL Stored procedure.

Here I will execute an SQL statement, so I create the SQL statement in a string variable like:

string sql = $"Insert Into Inventory (Name, Price, Quantity) Values ('{inventory.Name}', '{inventory.Price}','{inventory.Quantity}')";

Note that I add the values of Name, Price and Quantity to this SQL statement string, from the Inventory class parameter given in the action method, like inventory.Name, inventory.Price, inventory.Quantity.

Next I create the SqlCommand class object by passing 2 parameters to it:

  • 1. SQL Statement String
  • 2. SQL Connection Object

This code is:

using (SqlCommand command = new SqlCommand(sql, connection))
{
//...
}

I then set the CommandType property to CommandType.Text enum because I am using SQL statement string and not SQL procedure (for which it will be CommandType.StoredProcedure). This code is given below:

command.CommandType = CommandType.Text;

Since the ExecuteNonQuery method works in a connected environment therefore I have to open the connection before execution and also close the connection after execution is done.

The code which does this work is given work

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

Testing the Create Inventory feature

Now it’s time to create a new inventory record in the database. So run your project and go to the URL – http://localhost:61126/Home/Create, in your browser (the port may be different in your case).

In the form fill:

Jeans for ‘Name’
20 for ‘Price’
100 for ‘Quantity’

Then click the Create button.

create inventory view

Now go to your SQL Server Object Explorer and right click the Inventory table and select View Data.

view data in inventory table

This will open the Inventory table and you will see the record has been created in the table.

inventory table data

Parameterized Command Objects

In the above section I used hard-coded string literal for the SQL query, as shown below:

string sql = $"Insert Into Inventory (Name, Price, Quantity) Values ('{inventory.Name}', '{inventory.Price}','{inventory.Quantity}')";

A parameterized query is a query in which placeholders are used for parameters with the ‘@’ sign, like shown below.

string sql = "Insert Into Inventory (Name, Price, Quantity) Values (@Name, @Price, @Quantity)";

Here @Name, @Price and @Quantity are the parameters, and you will have to define and provide them with values.

A Parameterized query serves 2 important things:

  • 1. Avoid SQL injection attacks, which is a very common attack.
  • 2. Faster execution than literal SQL string because they are pre-compiled SQL statement. So all you need to supply are the “parameters” (think “variables”) that need to be inserted into the statement for it to be executed.

To use Parameterized Command Object you have to specify parameters for the SqlCommand. This is done by using the SqlParameter class.

Important members of the SqlParameter class are shown in the below table:

Name Description
ParameterName Gets or sets the name of the parameter.
Value Gets or sets the value of the parameter.
SqlDbType Gets or sets the SQL Server’s data type of the parameter.
Size Gets or sets the maximum parameter size of the data in bytes.
IsNullable Gets or sets whether the parameter accepts null values.
Direction Gets or sets whether the parameter is input-only, output-only, bidirectional, or a return
value parameter.

Now it’s time to change the Create View code to a parameterized one. So replace your Create View code with this updated code given below:

[HttpPost]
public IActionResult Create(Inventory inventory)
{
    string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string sql = "Insert Into Inventory (Name, Price, Quantity) Values (@Name, @Price, @Quantity)";

        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            command.CommandType = CommandType.Text;

            // adding parameters
            SqlParameter parameter = new SqlParameter
            {
                ParameterName = "@Name",
                Value = inventory.Name,
                SqlDbType = SqlDbType.VarChar,
                Size = 50
            };
            command.Parameters.Add(parameter);

            parameter = new SqlParameter
            {
                ParameterName = "@Price",
                Value = inventory.Price,
                SqlDbType = SqlDbType.Money
            };
            command.Parameters.Add(parameter);

            parameter = new SqlParameter
            {
                ParameterName = "@Quantity",
                Value = inventory.Quantity,
                SqlDbType = SqlDbType.Int
            };
            command.Parameters.Add(parameter);

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

    return View();
}
Explanation

There are just very few minute changes done in the code. First I changed the SQL query string to contain parameters like:

string sql = "Insert Into Inventory (Name, Price, Quantity) Values (@Name, @Price, @Quantity)";

Then I defined and gave these parameters the values filled in the form like:

SqlParameter parameter = new SqlParameter
{
    ParameterName = "@Name",
    Value = inventory.Name,
    SqlDbType = SqlDbType.VarChar,
    Size = 50
};

I also have to add these parameters to the SqlCommand object like:

command.Parameters.Add(parameter);

Also notice this for the @Name parameter that since I defined the Name column in the Inventory table as VARCHAR(50) therefore here I set the SqlDbType as SqlDbType.VarChar and Size as 50.

Similarly look how I defined the @Price parameter:

parameter = new SqlParameter
{
    ParameterName = "@Price",
    Value = inventory.Price,
    SqlDbType = SqlDbType.Money
};

I gave the SqlDbType value as SqlDbType.Money (as Price column is Money type). I also did not provide the Size field as Money type columns in SQL Server does not have a size value.

Now coming to the @Quantity parameter to which I provided the SqlDbType value as SqlDbType.Int

parameter = new SqlParameter
{
    ParameterName = "@Quantity",
    Value = inventory.Quantity,
    SqlDbType = SqlDbType.Int
};

Testing Parameterized Command Objects

Run your application and go to the URL – ‘http://localhost:61126/Home/Create’. Put ‘Skirts’ for Name, 19.99 for ‘Price’ and 50 for ‘Quantity’. Then click the ‘Create’ button, see below image:

adding inventory record

Now check the Inventory table data, where you will find this record inserted. This shown the Parameterized SqlCommand Object worked perfectly, see below image:

parameterized query data insert

Executing a Stored Procedure

A Stored Procedure (short form ‘SP’) is an SQL Code block stored in the database. They can do any type of work like read, insert, update, and delete records from the database table. Stored Procedures can also have parameters.

I will now create a stored procedure that will insert records to my inventory table. This SP will have 4 parameters in total, out of these 4, 3 will be of input direction and 1 output direction.

In your SQL Server Object Explorer window, reach out the Stored Procedures node. You can do this by opening the Databases ► Company ►Programmability node.

Then right click on the Stored Procedures node and select Add New Stored Procedures. See the below image which illustrates this.

add new stored procedure

A new window will open in Visual Studio, and provides you a template for creating a stored procedure, see below image:

stored procedure template

Replace all the code from this window with the below SP code:

CREATE PROCEDURE [dbo].[CreateInventory]
	@Name		VARCHAR(50),
	@Price        MONEY,
	@Quantity	INT,
	@Result	VARCHAR(50)	OUTPUT
AS
BEGIN
	Insert Into Inventory (Name, Price, Quantity) Values (@Name, @Price, @Quantity)
	SET @Result='Success'
END

This Stored Procedure called CreateInventory has 3 input parameter – @Name, @Price and @Quantity. I have to provide the values for these parameters from my code.

The procedure also has an output parameter called @Result, whose work is to return the ‘Success’ message to my ADO.NET code. This success message will signify that the record inserted successfully.

Now replace the Create action of HttpPost type with the new updated version. This version executes the CreateInventory stored procedure.

[HttpPost]
public IActionResult Create(Inventory inventory)
{
    string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string sql = "CreateInventory";

        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            // adding parameters
            SqlParameter parameter = new SqlParameter
            {
                ParameterName = "@Name",
                Value = inventory.Name,
                SqlDbType = SqlDbType.VarChar,
                Size = 50
            };
            command.Parameters.Add(parameter);

            parameter = new SqlParameter
            {
                ParameterName = "@Price",
                Value = inventory.Price,
                SqlDbType = SqlDbType.Money
            };
            command.Parameters.Add(parameter);

            parameter = new SqlParameter
            {
                ParameterName = "@Quantity",
                Value = inventory.Quantity,
                SqlDbType = SqlDbType.Int
            };
            command.Parameters.Add(parameter);

            parameter = new SqlParameter
            {
                ParameterName = "@Result",
                SqlDbType = SqlDbType.VarChar,
                Size = 50,
                Direction = ParameterDirection.Output
            };
            command.Parameters.Add(parameter);

            connection.Open();

            // Execute the stored procedure
            command.ExecuteNonQuery();

            // Output parameter value
            string result= Convert.ToString(command.Parameters["@Result"].Value);
            ViewBag.Result = result;

            connection.Close();
        }
    }

    return View();
}

Explanation

There are just a few changes which are done, in order to execute the SP. The first one is that instead of the text query I have assigned the stored procedure’s name to the string variable.

string sql = "CreateInventory";

So now the SqlCommand object will receive the SP name in it’s first parameter instead of the string query.

I have changed the CommandType property to CommandType.StoredProcedure, this will tell ADO.NET that I need to execute a Stored Procedure this time.

command.CommandType = CommandType.StoredProcedure;

I also added a new @Result parameter and specified it’s direction as Output:

parameter = new SqlParameter
{
    ParameterName = "@Result",
    SqlDbType = SqlDbType.VarChar,
    Size = 50,
    Direction = ParameterDirection.Output
};
command.Parameters.Add(parameter);

Then after the ExecuteNonQuery statement, I can get the value returned by my output parameter like this:

string result= Convert.ToString(command.Parameters["@Result"].Value);

I can easily use this return value to tell the user about the outcome of this operation.

So I have added the @Result parameter’s value to a ViewBag variable like ViewBag.Result = result;.

And showing the ViewBag’s value inside an H2 tag in the Create View:

…
<div class="container-fluid">
    <h1>Create an Inventory</h1>
    <h2 class="alert alert-danger">@ViewBag.Result</h2>
    <form method="post">
        …
    </form>
</div>
…

Test this feature by inserting a new inventory record. You will find the message Success get displayed on the View.

success message on view

Understanding SqlDataReader Object

You might want to read data stored in the Inventory table of the Company database. To do this in ADO.NET you have to use SqlDataReader class which derives from the DbDataReader class.

SqlDataReader are useful when you need to iterate over large amounts of data quickly and you do not need to maintain an in-memory representation. Be aware, that SqlDataReader objects maintain an open connection to their data source until you explicitly close the connection.

You obtain SqlDataReader objects from the SqlCommand object using a call to ExecuteReader(). I show a snipped code of SqlDataReader in action below:

string connection = "Server=(localdb)\\MSSQLLocalDB;Database=Company;Trusted_Connection=True;MultipleActiveResultSets=true";
string sql = "Select * From Inventory";
SqlCommand myCommand = new SqlCommand(sql, connection);

connection.Open();

// Obtain a data reader via ExecuteReader().
using (SqlDataReader dataReader = command.ExecuteReader())
{
    // Loop over the results
    while (dataReader.Read())
    {
        int inventoryId= Convert.ToInt32(dataReader["Id"]);
        string inventoryName = Convert.ToString(dataReader["Name"]);
        string inventoryPrice = Convert.ToDecimal(dataReader["Price"]);
        int inventoryQuantity = Convert.ToInt32(dataReader["Quantity"]);
        DateTime inventoryAddedOn = Convert.ToDateTime(dataReader["AddedOn"]);
    }
}	

connection.Close();

The .Read() method advances the SqlDataReader to the next record.

Remove Hard Coded Column Names

In the above code I have hard coded the column names in order to get their values, like –

dataReader["Name"]
dataReader["Price"]
dataReader["Quanity"] 

This can be avoided by using the ‘FieldCount’ property along with ‘GetName() and GetValue()’ methods of the SqlDataReader class. These are explained below:

  • FieldCount – returns the total number of columns in the current records of the SqlDataReader object.
  • GetName() – gets the name of the specified column.
  • GetValue() – gets the value of the specified column.
The below code shows how to remove the hard-coded columns names:

using (SqlDataReader dataReader = command.ExecuteReader())
{
    while (dataReader.Read())
    {
        for (int i = 0; i < dataReader.FieldCount; i++)
        {
            string currentColName = dataReader.GetName(i);
            string currentColValue = Convert.ToString(dataReader.GetValue(i));
        }
    }
}	

Obtaining Multiple Result Sets from SqlDataReader

If you are obtaining results from 2 tables by running 2 SQL queries like –

string sql = "Select * From Inventory; Select * from Report";

Then these form Multiple Result Sets. You can iterate over each of these results using the SqlDataReader class NextResult method.

In the below code I iterate all the rows of each Result Sets:

do
{
    while (dataReader.Read())
    {
        for (int i = 0; i < dataReader.FieldCount; i++)
        {
            string currentColName = dataReader.GetName(i);
            string currentColValue = Convert.ToString(dataReader.GetValue(i));

        }
    }
} while (dataReader.NextResult());

You can download the source code using the below link:

Download

Conclusion

In this tutorial you learned to create records in a database table using ADO.NET. In the next tutorial I will tell how to Read records from Database using ADO.NET and display them to the users in the application.

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "ASP.NET Core, jQuery, EF Core, SEO, jQuery, HTML" and more.