www.binaryintellect.net
Open in
urlscan Pro
199.233.254.89
Public Scan
URL:
https://www.binaryintellect.net/articles/1a7ab1ca-979c-4177-abcb-1445fcc66f60.aspx
Submission: On July 23 via manual from PL — Scanned from PL
Submission: On July 23 via manual from PL — Scanned from PL
Form analysis 1 forms found in the DOM
POST ./1a7ab1ca-979c-4177-abcb-1445fcc66f60.aspx
<form method="post" action="./1a7ab1ca-979c-4177-abcb-1445fcc66f60.aspx" id="aspnetForm">
<div class="aspNetHidden">
<input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE"
value="/wEPDwUKMTY4Nzg3ODA2NGQYBAU7Y3RsMDAkQ29udGVudFBsYWNlSG9sZGVyMSRncmlkUG9wdWxhckZyb21DYXRlZ29yeSRHcmlkVmlldzMPZ2QFO2N0bDAwJENvbnRlbnRQbGFjZUhvbGRlcjEkZ3JpZFBvcHVsYXJGcm9tQ2F0ZWdvcnkkR3JpZFZpZXcyDzwrAAwBCAIBZAU7Y3RsMDAkQ29udGVudFBsYWNlSG9sZGVyMSRncmlkUG9wdWxhckZyb21DYXRlZ29yeSRHcmlkVmlldzEPZ2QFI2N0bDAwJENvbnRlbnRQbGFjZUhvbGRlcjEkZ3JpZExpbmtzDzwrAAwBCGZkkhn9SU1OFqoimLlst9TrPVEzAZHRP7LWvwAczGeeScA=">
</div>
<div class="aspNetHidden">
<input type="hidden" name="__VIEWSTATEGENERATOR" id="__VIEWSTATEGENERATOR" value="14C6E5A8">
</div>
<div id="container">
<div id="header">
<div id="slogan" title="BinaryIntellect Knowledge Base | .NET & Web Development KB By Bipin Joshi"><a href="/default.aspx"><img src="/images/logos/SloganBlue.png"></a></div>
<div id="logo" title="BinaryIntellect Knowledge Base | .NET & Web Development KB By Bipin Joshi"><a href="/default.aspx"><img src="/images/logos/techbloglogo.png"></a></div>
</div>
<div id="menubar">
<table id="ctl00_topmenubar_DataList1" cellspacing="0" cellpadding="0" style="color:#333333;width:100%;border-collapse:collapse;">
<tbody>
<tr>
<td class="MenuBarItem" align="center" valign="middle" style="background-color:#EFF3FB;width:25%;white-space:nowrap;">
<a id="ctl00_topmenubar_DataList1_ctl00_HyperLink1" href="../default.aspx">Home</a>
</td>
<td class="MenuBarItem" align="center" valign="middle" style="background-color:White;width:25%;white-space:nowrap;">
<a id="ctl00_topmenubar_DataList1_ctl01_HyperLink1" href="../about.aspx">About</a>
</td>
<td class="MenuBarItem" align="center" valign="middle" style="background-color:#EFF3FB;width:25%;white-space:nowrap;">
<a id="ctl00_topmenubar_DataList1_ctl02_HyperLink1" href="http://www.binaryintellect.com">ASP.NET Online Course</a>
</td>
<td class="MenuBarItem" align="center" valign="middle" style="background-color:White;width:25%;white-space:nowrap;">
<a id="ctl00_topmenubar_DataList1_ctl03_HyperLink1" href="http://www.bipinjoshi.org">Shambhavi Mudra</a>
</td>
</tr>
</tbody>
</table>
</div>
<div id="main">
<div id="leftcolumn">
<img id="ctl00_imgSpacer" src="../images/misc/leftcolumnspacer.png">
<br>
<table id="ctl00_coursesMenu_DataList1" class="MenuPad" cellspacing="0" style="border-collapse:collapse;">
<tbody>
<tr>
<td class="MenuHeading" align="center" style="white-space:nowrap;">
<span id="ctl00_coursesMenu_DataList1_ctl00_Label1" style="font-weight:bold;">Online Courses</span>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_coursesMenu_DataList1_ctl01_HyperLink1" href="http://www.binaryintellect.com/courses/fa959470-2e71-4eb5-9fdc-fceb7971c71b">ASP.NET Core</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_coursesMenu_DataList1_ctl02_HyperLink1" href="http://www.binaryintellect.com/courses/63e78acd-8c7a-4da6-9015-dddd184f3068">Design Patterns</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_coursesMenu_DataList1_ctl03_HyperLink1" href="http://www.bipinjoshi.org">Shambhavi Mudra</a>
</td>
</tr>
</tbody>
</table>
<br>
<div class="AdContainer">
<center>
<a href="http://www.discountasp.net" target="_blank">
<img src="/images/discountasp.gif" border="0">
</a>
</center>
</div>
<br>
<table id="ctl00_categoriesMenu_DataList1" class="MenuPad" cellspacing="0" style="border-collapse:collapse;">
<tbody>
<tr>
<td class="MenuHeading" align="center" style="white-space:nowrap;">
<span id="ctl00_categoriesMenu_DataList1_ctl00_Label1" style="font-weight:bold;">Categories</span>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl01_HyperLink1" href="../categories/be3270c7-740a-4ce4-aded-95d6d601d651.aspx">ASP.NET Core</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl02_HyperLink1" href="../categories/dac01a23-ffc5-4584-aedf-617d92339767.aspx">Blazor</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl03_HyperLink1" href="../categories/5970c420-64b5-4d71-a4db-36a7f7d8b810.aspx">Web API</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl04_HyperLink1" href="../categories/023b938c-1583-4a06-8d86-31e4a55d7500.aspx">ASP.NET MVC</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl05_HyperLink1" href="../categories/22d7f3e3-a683-4510-8930-d37e38c671f1.aspx">ASP.NET</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl06_HyperLink1" href="../categories/751a6da8-ef7b-4b69-9bf6-a1ad03755b6b.aspx">Data Access</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl07_HyperLink1" href="../categories/5a11fcea-7177-4bde-9b0f-8a7884cb7880.aspx">.NET / Visual Studio</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl08_HyperLink1" href="../categories/25543196-65d4-46fc-93e2-c7144805882a.aspx">JavaScript / jQuery</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl09_HyperLink1" href="../categories/fc193202-2f03-4aee-8106-ec182acd3e97.aspx">HTML5</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl10_HyperLink1" href="../categories/ca35eae7-3ef7-4ffa-af08-cc06d0943fb3.aspx">Web Services</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl11_HyperLink1" href="../categories/503191ae-787e-4109-a3c4-75ebcd078541.aspx">Windows Forms</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl12_HyperLink1" href="../categories/332d650f-5313-4a91-9ee7-4d6815f861fc.aspx">Projects</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl13_HyperLink1" href="../categories/646e6280-f859-40f4-bc1c-27db77030f2b.aspx">Book Reviews</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl14_HyperLink1" href="../categories/5f4986ff-9b56-4529-bbcd-c4753aa0951b.aspx">Videos</a>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_categoriesMenu_DataList1_ctl15_HyperLink1" href="searcharticles.aspx">Search Articles</a>
</td>
</tr>
</tbody>
</table>
<br>
<table id="ctl00_followMenu_DataList1" class="MenuPad" cellspacing="0" style="border-collapse:collapse;">
<tbody>
<tr>
<td class="MenuHeading" align="center" style="white-space:nowrap;">
<span id="ctl00_followMenu_DataList1_ctl00_Label1" style="font-weight:bold;">Article Updates</span>
</td>
</tr>
<tr>
<td class="MenuItem" align="center" style="white-space:nowrap;">
<a id="ctl00_followMenu_DataList1_ctl01_HyperLink1" href="rssfeed.aspx">RSS Feed</a>
</td>
</tr>
</tbody>
</table>
<br>
<span>
<center>
<!-- <a href="/about.aspx" target="_blank">
<img src="/images/books/html5.png" border="0"/>
</a> -->
<a href="http://www.apress.com/9781484218471"><img src="/images/books/XmlCsSmall.jpg" border="0"></a>
</center>
<center>
<a href="/about.aspx" target="_blank">
<img src="/images/books/jquery.png" border="0">
</a>
</center>
<center>
<a href="/about.aspx" target="_blank">
<img src="/images/books/dpsmall.jpg" border="0">
</a>
</center>
<center>
<a href="http://www.bipinjoshi.org" target="_blank">
<img src="/images/books/nsdsmall.jpg" border="0">
</a>
<br>
<a href="http://www.bipinjoshi.org" target="_blank">
<img src="/images/books/ddh.png" border="0">
</a>
</center>
</span><span>
<div data-type="ad" data-publisher="lqm.bipinjoshinet.site" data-format="160x600" data-zone="ros2"></div>
<br>
</span><span>
<div data-type="ad" data-publisher="lqm.bipinjoshinet.site" data-zone="ron" data-format="160x600"></div>
</span>
</div>
<div id="middlecolumn">
<span><!-- <div class="Note" style="font-weight:600;text-align:center">
ASP.NET Core 5.0 : MVC, Razor Pages, Web API, EF Core, Blazor, Design Patterns, and more. Private online coaching for software developers. Click <a href="http://www.binaryintellect.com">here</a> for more details.
</div> -->
<!--
<div data-type="ad" data-publisher="lqm.bipinjoshinet.site" data-format="728x90" data-zone="ros11"></div>
-->
<center>
<div class="Note">
<strong>
<a href="http://www.bipinjoshi.org">अजपा जप आणि शांभवी मुद्रा ऑनलाईन कोर्स</a> : श्वास, मंत्र, मुद्रा आणि ध्यान यांच्या सहाय्याने मनःशांती, एकाग्रता, चक्र संतुलन आणि कुंडलिनी जागृती. अधिक माहिती आणि आगामी तारखांसाठी
<a href="http://www.bipinjoshi.org">येथे जा</a>. </strong>
</div>
</center>
<br>
<br>
</span>
<div>
<h1>Use SqlDataReader to Read Data in ASP.NET Core</h1>
<p align="center"><img src="content/Images/T_AdoNetAspNetCore_01.jpg" style="object-fit: cover;width:100%;height:100%"></p>
<p>In the <a href="0c8f13fd-ef6d-4346-a9d3-0f6f810924a2.aspx">previous article</a> we learned the basics of the ADO.NET data provider for SQL Server. We also took a glance at the SQL Server data provider's object model. Now it's time to put
those objects to use in a simple CRUD web application. To that end this article kicks off the development by creating a simple repository for performing the CRUD operations.</p>
<p>In this article we create a simple repository with five methods namely SelectAll(), SelectByID(), Insert(), Update(), and Delete(). These methods will be asynchronous and will do the respective task. </p>
<p>To get started, create a new ASP.NET Core web application using the empty project template. If you created a project as a pert of the <a href="0c8f13fd-ef6d-4346-a9d3-0f6f810924a2.aspx">previous article,</a> you can open the same project
to develop this example.</p>
<p>Then open NuGet package manager dialog and install the Microsoft.Data.SqlClient package as shown below:</p>
<p><img src="content/Images/T_AdoNetAspNetCore_02_01.png"></p>
<p>And</p>
<p><img src="content/Images/T_AdoNetAspNetCore_02_02.png"></p>
<p>Then add a new C# class file named GlobalUsings.cs in the project root and place the following code in it.</p>
<pre><strong>global using System.Data;
global using Microsoft.Data.SqlClient;
</strong>global using System.ComponentModel.DataAnnotations;
global using AdoNetIDemo.DataAccess;</pre>
<p>We imported a few namespaces globally. Especially System.Data and Microsoft.Data.SqlClient are important because they provide the main data access classes.</p>
<p>Next, open appsettings.json file and place the following database connection string.</p>
<pre>"ConnectionStrings": {
"AppDb": <strong>"data source=.;initial catalog=Northwind;
integrated security=true"</strong>
}</pre>
<p>Here, I am going to use the Northwind database and Customers table for this example. Make sure to change the database connection string as per your setup.</p>
<p>Next, add a folder named DataAccess in the project root and then add a new C# class file called Customer.cs in it.</p>
<p>Once we fetch data from the Customers table we need to send it to the UI. We will create a <a href="https://docs.microsoft.com/en-us/dotnet/csharp/whats-new/tutorials/records?WT.mc_id=DT-MVP-7776" target="_blank">
record type</a> called Customer for this purpose. You could have also used traditional C# class instead of a record.</p>
<p>Open the Customer.cs file and add the following code to it:</p>
<pre>namespace AdoNetIDemo.DataAccess;
public record Customer(
[Required] string CustomerID,
[Required] string CompanyName,
[Required] string ContactName,
[Required] string Country
);</pre>
<p>As you can see, we declared a record called Customer that has four properties namely CustomerID, CompanyName, ContactName, and Country. All the properties are decorated with [Required] attribute for the sake of data validation.</p>
<p>I have used a shortcut way of declaring a record (positional record). If you want you can also use a more elaborate class-like syntax involving init only properties :</p>
<pre>public record Customer
{
[Required]
public string CustomerID { get; <strong>init;</strong> }
[Required]
public string CompanyName { get; <strong>init;</strong> }
[Required]
public string ContactName { get; <strong>init;</strong> }
[Required]
public string Country { get; <strong>init;</strong> }
}</pre>
<p>You can read more about record type in the official documentation <a href="https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/builtin-types/record?WT.mc_id=DT-MVP-7776" target="_blank">
here</a> and <a href="https://docs.microsoft.com/en-us/dotnet/csharp/fundamentals/types/records?WT.mc_id=DT-MVP-7776" target="_blank">
here</a>.</p>
<p>Add the AdoNetIDemo.DataAccess namespace to GlobalUsings.cs file before moving ahead.</p>
<p>Now add a new interface named ICustomerRepository to the DataAccess folder and write the following code in it.</p>
<pre>namespace AdoNetIDemo.DataAccess;
public interface <strong>ICustomerRepository</strong>
{
Task<List<Customer>> <strong>SelectAll</strong><strong>()</strong>;
Task<Customer> <strong>SelectByID</strong><strong>(string id)</strong>;
Task<int> <strong>Insert(Customer customer)</strong>;
Task<int> <strong>Update(Customer customer)</strong>;
Task<int> <strong>Delete(int id)</strong>;
}</pre>
<p>The ICustomerRepository interface contains the skeleton of our repository class and has five methods - SelectAll(), SelectByID(), Insert(), Update(), Delete().</p>
<p>Since we want our repository to be async, we return Task from the interface methods.</p>
<p>Now add CustomerRepository class into the DataAccess folder and implement ICustomerRepository interface in it.</p>
<pre>namespace AdoNetIDemo.DataAccess;
public class <strong>CustomerRepository : ICustomerRepository</strong>
{
// your code here
}</pre>
<p>The CustomerRepository class will need the database connection string stored in the appsettings.json file. So, we inject IConfiguration object into the constructor and read the connection string as shown below:</p>
<pre>private readonly string connectionString;
public CustomerRepository(IConfiguration config)
{
connectionString = config.GetConnectionString("AppDb");
}</pre>
<p>The GetConnectionString() method of IConfiguration accepts the connection string name from the ConnectionStrings section and returns that connecting string value.</p>
<p>We will now implement the SelectAll() method of the repository that reads the Customers table. Take a look :</p>
<pre>public async Task<List<Customer>> SelectAll()
{
<strong>using var connection = new SqlConnection(connectionString);</strong>
var query = "Select CustomerID, CompanyName, ContactName,
Country From Customers Order By CustomerID";
<strong>using var command = new SqlCommand(query, connection);</strong>
try
{
await <strong>connection.OpenAsync()</strong>;
using var reader = await <strong>command.ExecuteReaderAsync()</strong>;
List<Customer> items = null;
if (<strong>reader.HasRows</strong>)
{
items = new List<Customer>();
while (await <strong>reader.ReadAsync()</strong>)
{
var item = new Customer(
CustomerID: reader.GetString(0),
CompanyName: reader.GetString(1),
ContactName: reader.GetString(2),
Country: reader.GetString(3));
items.Add(item);
}
}
await <strong>reader.CloseAsync()</strong>;
await <strong>connection.CloseAsync()</strong>;
return items;
}
catch
{
if (<strong>connection.State</strong> != ConnectionState.Closed)
{
await <strong>connection.CloseAsync()</strong>;
}
return null;
}
}</pre>
<p>This code begins by creating a new SqlConnection object. Notice the use of <a href="https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement?WT.mc_id=DT-MVP-7776" target="_blank">
using declaration</a> while creating the SqlConnection. That will ensure that the resources consumed by the connection are reclaimed properly when the connection is closed. </p>
<p>We are interested to retrieve all the Customers from the database (We need CustomerID, CompanyName, ContactName, and Country columns). So, we store a SELECT query for doing that in a string variable. </p>
<p>Then we create a SqlCommand object by specifying this query and the SqlConnection. A SqlCommand object represents a select or action query (or stored procedure) that you intend to execute on the database. A SqlCommand requires an open
database connection to execute any queries. So, we open the database connection by calling OpenAsync() method of SqlConnection.</p>
<p>To execute this SELECT query against the database we use ExecuteReaderAsync() method of SqlCommand. The ExecuteReaderAsync() method executes the query and returns a SqlDataReader. A SqlDataReader is read-only, forward-only cursor that
allows you to iterate through the result set and read one record at a time.</p>
<p>To read and store the customer data we create a List of Customer records (C# record). It makes sense to iterate through the result set only if there are any rows in it. So, we check the HasRows property of SqlDataReader that tells us
just that. If HasRows returns true we go ahead and run a while loop. Initially, SqlDataReader's current row pointer is placed before the first row. Calling ReadAsync() method does two things - it advances the row pointer to the next row
and reads data for that row.</p>
<p>Inside the while loop we create a Customer item by specifying its positional properties - CustomerID, CompanyName, ContactName, and Country. To set values to these positional properties we use GetString() method of SqlDataReader. The
GetString() method accepts a column index (index starts from 0 and sequence is same as they appear in the query) and returns the value from that column. Just like GetString() there are different methods for different data types such as
GetInt32(), GetBoolean(), and GetDateTime().</p>
<p>Once a new Customer item is created we add it to the List<Customer>. The ReadAsync() method returns false when it reaches past the end of the result set. We then close the SqlDataReader and SqlConnection by calling their
CloseAsync() methods respectively. Strictly speaking closing a connection also closes the data reader. Here, I am explicitly closing both of them just to highlight that SqlDataReader has CloseAsync() method (you will learn more about it
in a later part of this article series).</p>
<p>Finally, List<Customer> is returned to the caller. </p>
<p>The whole while loop and data reading logic is placed inside a try-catch block to trap any unexpected errors. In case of any error, we close the SqlConnection if it's open and return null to the caller. The State property is an
enumeration of type ConnectionState and contains various possible values such as Closed, Open, and Broken.</p>
<p>This completes the SelectAll() method.</p>
<p>Now let's add the SelectByID() method. </p>
<pre>public async Task<Customer> SelectByID(string id)
{
using var connection = new SqlConnection(connectionString);
var query = "Select CustomerID, CompanyName, ContactName,
Country From Customers <strong>Where CustomerID = @CustomerID</strong>";
using var command = new SqlCommand(query, connection);
<strong>command.Parameters.AddWithValue("CustomerID", id);</strong>
try
{
await connection.OpenAsync();
using var reader = await command.ExecuteReaderAsync();
Customer item = null;
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
item = new Customer(
CustomerID: reader.GetString
(<strong>reader.GetOrdinal("CustomerID")</strong>),
CompanyName: reader.GetString
(<strong>reader.GetOrdinal("CompanyName")</strong>),
ContactName: reader.GetString
(<strong>reader.GetOrdinal("ContactName")</strong>),
Country: reader.GetString
(<strong>reader.GetOrdinal("Country")</strong>));
}
}
await reader.CloseAsync();
await connection.CloseAsync();
return item;
}
catch
{
if (connection.State != ConnectionState.Closed)
{
await connection.CloseAsync();
}
return null;
}
}</pre>
<p>The SelectByID() method accepts a CustomerID and returns the matching Customer to the caller. The overall working of this method is similar to SelectAll() because here also we need to read Customer data from the database. However, this
time we fetch only a single Customer row. Notice the code marked in bold letters. </p>
<p>The SELECT query now has WHERE with a @CustomerID parameter. To specify this query parameter's value we use AddWithValue() method of the SqlCommand's Parameter collection. The AddWithValue() method takes the parameter name and its value.
Internally, it adds a SqlParameter object to the Parameters collection.</p>
<p>This time instead of creating a List<Customer> we create just a single Customer record. The item gets filled inside the while loop and is then returned to the caller.</p>
<p>Previously we used GetString() calls and specified the column index. Many a times knowing and hard-coding a column index can be problematic. So, this time we use GetOrdinal() method of SqlDataReader. The GetOrdinal() method accepts a
column name and returns its index.</p>
<p>This completes the SelectByID() method.</p>
<p>In the next part of this article series we will add Insert(), Update(), and Delete() methods to the CustomerRepository. </p>
<p>That's it for now! Keep coding!!</p>
<center>
</center>
</div>
<br>
<div>
<div class="AuthorProfile" style="font-weight:500">
<b>Bipin Joshi</b> is an independent software consultant and trainer by profession specializing in Microsoft web development technologies. Having embraced the Yoga way of life he is also a meditation teacher and spiritual guide to his
students. He is a prolific author and writes regularly about software development and yoga on his websites. He is programming, meditating, writing, and teaching for over 27 years. To know more about his ASP.NET online courses go
<a href="http://www.binaryintellect.com">here</a>. More details about his Kriya and Meditation online course are available <a href="http://www.bipinjoshi.org">here</a>.
</div>
</div>
<br>
<center>
<span id="ctl00_ContentPlaceHolder1_lblPostDate" style="font-weight:bold;">Posted On : 08 August 2022</span>
</center>
<br>
<center>
<script type="text/javascript" src="https://platform-api.sharethis.com/js/sharethis.js#property=6451d137f204d5001a5bdaca&product=inline-share-buttons&source=platform" async="async"></script>
<div class="sharethis-inline-share-buttons st-center st-has-labels st-inline-share-buttons st-animated" id="st-1">
<div class="st-btn st-first" data-network="facebook" style="display: inline-block;">
<img alt="facebook sharing button" src="https://platform-cdn.sharethis.com/img/facebook.svg">
<span class="st-label">Share</span>
</div>
<div class="st-btn" data-network="twitter" style="display: inline-block;">
<img alt="twitter sharing button" src="https://platform-cdn.sharethis.com/img/twitter.svg">
<span class="st-label">Tweet</span>
</div>
<div class="st-btn" data-network="whatsapp" style="display: inline-block;">
<img alt="whatsapp sharing button" src="https://platform-cdn.sharethis.com/img/whatsapp.svg">
<span class="st-label">Share</span>
</div>
<div class="st-btn" data-network="linkedin" style="display: inline-block;">
<img alt="linkedin sharing button" src="https://platform-cdn.sharethis.com/img/linkedin.svg">
<span class="st-label">Share</span>
</div>
<div class="st-btn st-last" data-network="sharethis" style="display: inline-block;">
<img alt="sharethis sharing button" src="https://platform-cdn.sharethis.com/img/sharethis.svg">
<span class="st-label">Share</span>
</div>
</div>
</center>
<br>
<div>
</div>
<br><br><br>
<div id="ctl00_ContentPlaceHolder1_gridPopularFromCategory_pnlShort">
<div>
<table cellspacing="0" cellpadding="6" id="ctl00_ContentPlaceHolder1_gridPopularFromCategory_GridView2" style="color:#333333;border-color:#5D7B9D;border-width:1px;border-style:Solid;width:100%;border-collapse:collapse;">
<tbody>
<tr align="center" style="color:White;background-color:#5D7B9D;font-weight:bold;">
<th scope="col">Popular from this category</th>
</tr>
<tr style="color:#333333;background-color:#F7F6F3;">
<td><a href="fbc96859-8a31-4735-baeb-7adcbc521b30.aspx">Executing Raw SQL Queries using Entity Framework</a></td>
</tr>
<tr style="color:#284775;background-color:White;">
<td><a href="30738a7c-5176-4333-aa83-98eab8548da5.aspx">Working with Stored Procedures in Entity Framework - Part 2</a></td>
</tr>
<tr style="color:#333333;background-color:#F7F6F3;">
<td><a href="c1bff938-1789-4501-8161-3f38bc465a8b.aspx">What to do when SaveChanges() fails?</a></td>
</tr>
<tr style="color:#284775;background-color:White;">
<td><a href="3babaf92-64ee-491e-8401-b7f52befada3.aspx">Uploading and Retrieving Images From SQL Server</a></td>
</tr>
<tr style="color:#333333;background-color:#F7F6F3;">
<td><a href="98ab41a4-3a69-42c6-96d9-b639ce68a0f4.aspx">Working with Stored Procedures in Entity Framework - Part 1</a></td>
</tr>
<tr style="color:#284775;background-color:White;">
<td><a href="fbea3517-b0c8-4e79-bc52-cc75e0b1b86f.aspx">Developing generic data access layer using ADO.NET 2.0</a></td>
</tr>
<tr style="color:#333333;background-color:#F7F6F3;">
<td><a href="c7f854b4-b213-4216-ba30-67c6584dc376.aspx">Working with Binary Large Objects (BLOBs) Using SQL Server and ADO.NET</a></td>
</tr>
<tr style="color:#284775;background-color:White;">
<td><a href="165bb877-27ee-4efa-9fa3-40cd0cf69e49.aspx">Wrapping multiple calls to SaveChanges() in a single transaction</a></td>
</tr>
<tr style="color:#333333;background-color:#F7F6F3;">
<td><a href="432e4904-754c-4425-ae93-c6142c2b943f.aspx">Use DataReader Cleverly</a></td>
</tr>
<tr style="color:#284775;background-color:White;">
<td><a href="d07c7f36-5d4c-4195-817a-bdddbbc11041.aspx">Generic Data Access Application Block for .NET</a></td>
</tr>
</tbody>
</table>
</div>
</div>
<br>
<center>
<span>
<div data-type="ad" data-publisher="lqm.bipinjoshinet.site" data-zone="ron" data-format="300x250"></div>
</span>
</center>
</div>
<div id="rightcolumn"></div>
</div>
<div id="footer">
<div style="padding-top:11px"> Copyright © binaryintellect.net. All rights reserved. </div>
<div>
<a href="/legal.aspx">Read Copyright and Legal Terms of Use</a>
</div>
<div>
<a href="http://www.copyscape.com/" target="_blank">
<img src="http://banners.copyscape.com/img/copyscape-banner-blue-200x25.png" width="200" height="25" border="0" alt="Protected by Copyscape" title="Protected by Copyscape Plagiarism Checker - Do not copy content from this page.">
</a>
</div>
<div>
<a href="http://www.discountasp.net">Hosted by DiscountASP.net</a>
</div>
<div class="CenteredNormalText" style="padding:3px">
<a href="https://www.facebook.com/joshibipin">
<img src="/images/social/facebook.jpg">
</a>
<a href="https://twitter.com/joshibipin">
<img src="/images/social/twitter.png">
</a>
<a href="https://www.linkedin.com/in/joshibipin">
<img src="/images/social/linkedin.png">
</a>
<a href="/contact.aspx">
<img src="/images/social/email.jpg">
</a>
<a href="/articles/rssfeed.aspx">
<img src="/images/social/rss.png">
</a>
</div>
</div>
</div>
<span>
<script type="text/javascript">
function _dmBootstrap(file) {
var _dma = document.createElement('script');
_dma.type = 'text/javascript';
_dma.async = true;
_dma.src = ('https:' == document.location.protocol ? 'https://' : 'http://') + file;
(document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(_dma);
}
function _dmFollowup(file) {
if (typeof DMAds === 'undefined') _dmBootstrap('cdn2.DeveloperMedia.com/a.min.js');
}
(function() {
_dmBootstrap('cdn1.DeveloperMedia.com/a.min.js');
setTimeout(_dmFollowup, 2000);
})();
</script>
</span>
</form>Text Content
Home About ASP.NET Online Course Shambhavi Mudra
Online Courses ASP.NET Core Design Patterns Shambhavi Mudra
Categories ASP.NET Core Blazor Web API ASP.NET MVC ASP.NET Data Access .NET /
Visual Studio JavaScript / jQuery HTML5 Web Services Windows Forms Projects Book
Reviews Videos Search Articles
Article Updates RSS Feed
अजपा जप आणि शांभवी मुद्रा ऑनलाईन कोर्स : श्वास, मंत्र, मुद्रा आणि ध्यान यांच्या
सहाय्याने मनःशांती, एकाग्रता, चक्र संतुलन आणि कुंडलिनी जागृती. अधिक माहिती आणि
आगामी तारखांसाठी येथे जा.
USE SQLDATAREADER TO READ DATA IN ASP.NET CORE
In the previous article we learned the basics of the ADO.NET data provider for
SQL Server. We also took a glance at the SQL Server data provider's object
model. Now it's time to put those objects to use in a simple CRUD web
application. To that end this article kicks off the development by creating a
simple repository for performing the CRUD operations.
In this article we create a simple repository with five methods namely
SelectAll(), SelectByID(), Insert(), Update(), and Delete(). These methods will
be asynchronous and will do the respective task.
To get started, create a new ASP.NET Core web application using the empty
project template. If you created a project as a pert of the previous article,
you can open the same project to develop this example.
Then open NuGet package manager dialog and install the Microsoft.Data.SqlClient
package as shown below:
And
Then add a new C# class file named GlobalUsings.cs in the project root and place
the following code in it.
global using System.Data;
global using Microsoft.Data.SqlClient;
global using System.ComponentModel.DataAnnotations;
global using AdoNetIDemo.DataAccess;
We imported a few namespaces globally. Especially System.Data and
Microsoft.Data.SqlClient are important because they provide the main data access
classes.
Next, open appsettings.json file and place the following database connection
string.
"ConnectionStrings": {
"AppDb": "data source=.;initial catalog=Northwind;
integrated security=true"
}
Here, I am going to use the Northwind database and Customers table for this
example. Make sure to change the database connection string as per your setup.
Next, add a folder named DataAccess in the project root and then add a new C#
class file called Customer.cs in it.
Once we fetch data from the Customers table we need to send it to the UI. We
will create a record type called Customer for this purpose. You could have also
used traditional C# class instead of a record.
Open the Customer.cs file and add the following code to it:
namespace AdoNetIDemo.DataAccess;
public record Customer(
[Required] string CustomerID,
[Required] string CompanyName,
[Required] string ContactName,
[Required] string Country
);
As you can see, we declared a record called Customer that has four properties
namely CustomerID, CompanyName, ContactName, and Country. All the properties are
decorated with [Required] attribute for the sake of data validation.
I have used a shortcut way of declaring a record (positional record). If you
want you can also use a more elaborate class-like syntax involving init only
properties :
public record Customer
{
[Required]
public string CustomerID { get; init; }
[Required]
public string CompanyName { get; init; }
[Required]
public string ContactName { get; init; }
[Required]
public string Country { get; init; }
}
You can read more about record type in the official documentation here and here.
Add the AdoNetIDemo.DataAccess namespace to GlobalUsings.cs file before moving
ahead.
Now add a new interface named ICustomerRepository to the DataAccess folder and
write the following code in it.
namespace AdoNetIDemo.DataAccess;
public interface ICustomerRepository
{
Task<List<Customer>> SelectAll();
Task<Customer> SelectByID(string id);
Task<int> Insert(Customer customer);
Task<int> Update(Customer customer);
Task<int> Delete(int id);
}
The ICustomerRepository interface contains the skeleton of our repository class
and has five methods - SelectAll(), SelectByID(), Insert(), Update(), Delete().
Since we want our repository to be async, we return Task from the interface
methods.
Now add CustomerRepository class into the DataAccess folder and implement
ICustomerRepository interface in it.
namespace AdoNetIDemo.DataAccess;
public class CustomerRepository : ICustomerRepository
{
// your code here
}
The CustomerRepository class will need the database connection string stored in
the appsettings.json file. So, we inject IConfiguration object into the
constructor and read the connection string as shown below:
private readonly string connectionString;
public CustomerRepository(IConfiguration config)
{
connectionString = config.GetConnectionString("AppDb");
}
The GetConnectionString() method of IConfiguration accepts the connection string
name from the ConnectionStrings section and returns that connecting string
value.
We will now implement the SelectAll() method of the repository that reads the
Customers table. Take a look :
public async Task<List<Customer>> SelectAll()
{
using var connection = new SqlConnection(connectionString);
var query = "Select CustomerID, CompanyName, ContactName,
Country From Customers Order By CustomerID";
using var command = new SqlCommand(query, connection);
try
{
await connection.OpenAsync();
using var reader = await command.ExecuteReaderAsync();
List<Customer> items = null;
if (reader.HasRows)
{
items = new List<Customer>();
while (await reader.ReadAsync())
{
var item = new Customer(
CustomerID: reader.GetString(0),
CompanyName: reader.GetString(1),
ContactName: reader.GetString(2),
Country: reader.GetString(3));
items.Add(item);
}
}
await reader.CloseAsync();
await connection.CloseAsync();
return items;
}
catch
{
if (connection.State != ConnectionState.Closed)
{
await connection.CloseAsync();
}
return null;
}
}
This code begins by creating a new SqlConnection object. Notice the use of using
declaration while creating the SqlConnection. That will ensure that the
resources consumed by the connection are reclaimed properly when the connection
is closed.
We are interested to retrieve all the Customers from the database (We need
CustomerID, CompanyName, ContactName, and Country columns). So, we store a
SELECT query for doing that in a string variable.
Then we create a SqlCommand object by specifying this query and the
SqlConnection. A SqlCommand object represents a select or action query (or
stored procedure) that you intend to execute on the database. A SqlCommand
requires an open database connection to execute any queries. So, we open the
database connection by calling OpenAsync() method of SqlConnection.
To execute this SELECT query against the database we use ExecuteReaderAsync()
method of SqlCommand. The ExecuteReaderAsync() method executes the query and
returns a SqlDataReader. A SqlDataReader is read-only, forward-only cursor that
allows you to iterate through the result set and read one record at a time.
To read and store the customer data we create a List of Customer records (C#
record). It makes sense to iterate through the result set only if there are any
rows in it. So, we check the HasRows property of SqlDataReader that tells us
just that. If HasRows returns true we go ahead and run a while loop. Initially,
SqlDataReader's current row pointer is placed before the first row. Calling
ReadAsync() method does two things - it advances the row pointer to the next row
and reads data for that row.
Inside the while loop we create a Customer item by specifying its positional
properties - CustomerID, CompanyName, ContactName, and Country. To set values to
these positional properties we use GetString() method of SqlDataReader. The
GetString() method accepts a column index (index starts from 0 and sequence is
same as they appear in the query) and returns the value from that column. Just
like GetString() there are different methods for different data types such as
GetInt32(), GetBoolean(), and GetDateTime().
Once a new Customer item is created we add it to the List<Customer>. The
ReadAsync() method returns false when it reaches past the end of the result set.
We then close the SqlDataReader and SqlConnection by calling their CloseAsync()
methods respectively. Strictly speaking closing a connection also closes the
data reader. Here, I am explicitly closing both of them just to highlight that
SqlDataReader has CloseAsync() method (you will learn more about it in a later
part of this article series).
Finally, List<Customer> is returned to the caller.
The whole while loop and data reading logic is placed inside a try-catch block
to trap any unexpected errors. In case of any error, we close the SqlConnection
if it's open and return null to the caller. The State property is an enumeration
of type ConnectionState and contains various possible values such as Closed,
Open, and Broken.
This completes the SelectAll() method.
Now let's add the SelectByID() method.
public async Task<Customer> SelectByID(string id)
{
using var connection = new SqlConnection(connectionString);
var query = "Select CustomerID, CompanyName, ContactName,
Country From Customers Where CustomerID = @CustomerID";
using var command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("CustomerID", id);
try
{
await connection.OpenAsync();
using var reader = await command.ExecuteReaderAsync();
Customer item = null;
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
item = new Customer(
CustomerID: reader.GetString
(reader.GetOrdinal("CustomerID")),
CompanyName: reader.GetString
(reader.GetOrdinal("CompanyName")),
ContactName: reader.GetString
(reader.GetOrdinal("ContactName")),
Country: reader.GetString
(reader.GetOrdinal("Country")));
}
}
await reader.CloseAsync();
await connection.CloseAsync();
return item;
}
catch
{
if (connection.State != ConnectionState.Closed)
{
await connection.CloseAsync();
}
return null;
}
}
The SelectByID() method accepts a CustomerID and returns the matching Customer
to the caller. The overall working of this method is similar to SelectAll()
because here also we need to read Customer data from the database. However, this
time we fetch only a single Customer row. Notice the code marked in bold
letters.
The SELECT query now has WHERE with a @CustomerID parameter. To specify this
query parameter's value we use AddWithValue() method of the SqlCommand's
Parameter collection. The AddWithValue() method takes the parameter name and its
value. Internally, it adds a SqlParameter object to the Parameters collection.
This time instead of creating a List<Customer> we create just a single Customer
record. The item gets filled inside the while loop and is then returned to the
caller.
Previously we used GetString() calls and specified the column index. Many a
times knowing and hard-coding a column index can be problematic. So, this time
we use GetOrdinal() method of SqlDataReader. The GetOrdinal() method accepts a
column name and returns its index.
This completes the SelectByID() method.
In the next part of this article series we will add Insert(), Update(), and
Delete() methods to the CustomerRepository.
That's it for now! Keep coding!!
Bipin Joshi is an independent software consultant and trainer by profession
specializing in Microsoft web development technologies. Having embraced the Yoga
way of life he is also a meditation teacher and spiritual guide to his students.
He is a prolific author and writes regularly about software development and yoga
on his websites. He is programming, meditating, writing, and teaching for over
27 years. To know more about his ASP.NET online courses go here. More details
about his Kriya and Meditation online course are available here.
Posted On : 08 August 2022
Share
Tweet
Share
Share
Share
Popular from this category Executing Raw SQL Queries using Entity Framework
Working with Stored Procedures in Entity Framework - Part 2 What to do when
SaveChanges() fails? Uploading and Retrieving Images From SQL Server Working
with Stored Procedures in Entity Framework - Part 1 Developing generic data
access layer using ADO.NET 2.0 Working with Binary Large Objects (BLOBs) Using
SQL Server and ADO.NET Wrapping multiple calls to SaveChanges() in a single
transaction Use DataReader Cleverly Generic Data Access Application Block for
.NET
Copyright © binaryintellect.net. All rights reserved.
Read Copyright and Legal Terms of Use
Hosted by DiscountASP.net