Database interaction in ASP.NET

Introduction

This article is for beginners who are very new to ASP.NET and want to learn how to interact with database. While going through this article, you will learn how to insert, update, delete and load records from database.

Database Design

Before I proceed further, let me tell you that for this article, I have taken example of a simple database table named "SampleTable" that has following fields.

As we all know that stored procedure has its own advantage to use to avoid some extent of sql injection and to increase the peformance of the application so I have created stored procedure for all the action we generally perform in the database.

To insert record into database, I have created a stored procedure named "InsertRecord" that has following code.

CREATE PROCEDURE InsertRecord
(
 @firstName varchar(50),
 @lastName varchar(50),
 @active bit
)
AS
INSERT INTO SampleTable (FirstName, LastName, Active) VALUES
(@firstName, @lastName, @active)
GO

To Update the record, I have created a stored procedure named "UpdateRecord" that has following code.

create  PROCEDURE [UpdateRecord] 
(
 @autoid int,
 @firstName varchar(50),
 @lastName varchar(50),
 @active bit
)
AS
UPDATE SampleTable SET FirstName = @firstName, LastName = @lastName, Active = @active
WHERE Autoid = @autoid
GO

To Delete a record, I have created a stored procedure named "DeleteRecord" that has following code.

CREATE PROCEDURE [DeleteRecord] 
(
 @AutoID int
)
AS
DELETE SampleTable WHERE AutoID = @autoid
GO

To Load records, I have created a stored procedure named "LoadRecords" that has following code.

CREATE PROCEDURE LoadRecords
AS
SELECT * FROM SampleTable ORDER BY AutoID DESC
GO

To Search records, I have created a stored procedure named "SearchRecords" that has following code.

CREATE PROCEDURE SearchRecords
(
 @firstName varchar(50)
)
AS
SELECT * FROM SampleTable WHERE FirstName LIKE @firstName ORDER BY FirstName ASC
GO

Get solutions of the .NET problems with video explanations, .pdf and source code in .NET How to's.

Database connection string

I have written my connection string in web.config file under connectionStrings tag. My connection string to connect to the database looks like following.

<connectionStrings>
<add name="ConnStr" connectionString="server=localhost;database=SampleDatabase;user id=SampleUser;password=SampleUser;"/>
</connectionStrings>

Insert Record

To show how to insert record into database, I have designed a simple form that looks something like image below.

When user hits Submit button, onclick event of the button will be raised and "AddRecords" method will be called. Following is the code of this method.

/// <summary>
/// Add record into database
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void AddRecords(object sender, EventArgs e)
{
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
 
string firstName = txtFName.Text.Trim();
string lastName = txtLName.Text.Trim();
bool active = bool.Parse(dropActive.SelectedValue);
int result = 0;
 
SqlConnection conn = new SqlConnection(connStr);
SqlCommand dCmd = new SqlCommand();
try
{
dCmd.CommandText = "InsertRecord"; // name of the sp
dCmd.CommandType = CommandType.StoredProcedure;
dCmd.Connection = conn;

 
SqlParameter[] prms = new SqlParameter[3];
prms[0] = new SqlParameter("@firstName", SqlDbType.VarChar, 50);
prms[0].Value = firstName;
prms[1] = new SqlParameter("@lastName", SqlDbType.VarChar, 50);
prms[1].Value = lastName;
prms[2] = new SqlParameter("@active", SqlDbType.Bit);
prms[2].Value = active;
// add the parameters into command
dCmd.Parameters.AddRange(prms);
// now open the connection
conn.Open();
result = dCmd.ExecuteNonQuery();
conn.Close(); // close the connection
// write message
if (result.Equals(0))
lblMessage.Text = "Couldn't insert record";
else
lblMessage.Text = "Record inserted successfully.";
}
catch (Exception ee)
{
lblMessage.Text = ee.Message.ToString();
}
finally
{
dCmd.Dispose();
conn.Dispose();
}
}

Here, first I have got the connection string from the web.config file and also assigned local variables corresponding to the textboxes in the form. Then I have instantiated SqlConnection object that is accepting connection string as a parameter, then I have instantiated SqlCommand object. Later on inside the try block, I have specified the name of the stored procedure, command type and attached my connection to the command object.

I have created an array of the SqlParameter, assigned proper value and added to the command object. Finally I have opened the connection, executed my command and closed the connection immediately. As the database connection is very percious and we should use it very judiciously so it is always suggested to open the connection as late as possible and close it as soon as possible.

In the catch block, I have caught the parent exception and displayed in a Label.

In the finally block, I have disposed the command object and connection object.

Search Records

To show how to search records, I have created a simple form with a textbox that accepts FirstName as keyword and searches the database and show the results in GridView. My search form looks similar to image below.

When user hits Search button, onclick event of the button control raises and fire SearchData method. Following is the code for this method.

/// <summary>
/// Fires when Search button is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void SearchData(object sender, EventArgs e)
{
string keyword = txtFName.Text.Trim() + "%";
string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
 
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter dAd = new SqlDataAdapter();
SqlCommand dCmd = new SqlCommand();
DataTable dTable = new DataTable();
try
{
dCmd.CommandText = "SearchRecords";
dCmd.CommandType = CommandType.StoredProcedure;
dCmd.Connection = conn;
SqlParameter prm = new SqlParameter("@firstName", SqlDbType.VarChar, 50);
prm.Value = keyword;
// add the parameters into command
dCmd.Parameters.Add(prm);
// assign the select command to the Adapter object
dAd.SelectCommand = dCmd;
// now open the connection
conn.Open();
dAd.Fill(dTable);
conn.Close(); // close the connection
}
catch (Exception ee)
{
lblMessage.Text = ee.Message.ToString();
}
finally
{
dCmd.Clone();
dAd.Dispose();
conn.Dispose();
}
 
// Bind the record now
GridView1.DataSource = dTable;
GridView1.DataBind();
}

In the above method, I have retrieved the connection string from web.config file. I have also created a local variable named keyword and assigned its value as textbox value suffixed with "%" (as we are going to search all records from database whose FirstName starts with characters entered into the textbox. Notie that I am using LIKE keyword in my SearchRecords stored proceudre).

Later on I have instantiated SqlConnection, SqlDataAdapter and SqlCommand object. I have also instantiated a DataTable object to hold the search results from the database. Later on I have assigned the name of the stored proceudre I am using to the command object, specified the command type and connection to use.

I have also created a SqlParameter with value as the keyword and added into the SqlCommand object. Later on I have specified the SqlCommand to be used into the SqlDataAdapter object as SelectCommand.

Again, as written earlier, once I have everything ready to fireup the command, I opened the connection, executed the Fill method of the SqlDataAdapter and immediately closed the connection. Generally people use DataSet with the Fill method of the SqlDataAdapter but it is not suggested as the DataSet object is much more heavier in size than DataTable object. Unless your query is not returning more than one results, you should not use DataSet with the Fill method of the SqlDataAdapter object.

To update, delete records please refer to my another article http://www.dotnetfunda.com/articles/article29.aspx that has explained this in great details, you will also learn how to manipulate data using GridView in this article.

Please feel free to download the attachment that has source code of this article. Hope this article was useful. Thanks for reading and please let me know if you have any questions or comments. Cheers and happy coding !!!