In this article I will explain with an example, how to do a Database Connection without using Entity Framework in ASP.Net Core Razor Pages.
This article will illustrate how to connect to Database using ADO.Net and fetch data from Database using SqlDataReader in ASP.Net Core Razor Pages.
Database
This article makes use of a table named Fruits whose schema is defined as follows.
![Database Connection without Entity Framework in ASP.Net Core Razor Pages]()
The Fruits table has the following records.
![Database Connection without Entity Framework in ASP.Net Core Razor Pages]()
Note: You can download the database table SQL by clicking the download link below.
Namespaces
You will need to import the following namespaces.
using System.Data.SqlClient;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc.Rendering;
Model
Following is a Model class named FruitModel with two properties i.e. FruitId and FruitName.
public class FruitModel
{
[BindProperty]
public int FruitId { get; set; }
[BindProperty]
public string FruitName { get; set; }
}
Razor PageModel (Code-Behind)
The PageModel consists of two Action Handler methods.
Handler method for handling GET operation
Inside this Handler method, the PopulateFruits method is called.
Inside the PopulateFruits method, the records from the Fruits table are fetched from Database Table through a DataReader and generic list collection of FruitModel class is populated.
Then generic list collection of FruitModel class objects is copied to a public property Fruits which is a SelectList class object and assigned to the public property Fruits, which is used for populating DropDownList in .Net Core Razor Pages.
Handler method for handling Button Click and POST operation
This Handler method handles the POST call when the Submit Button is clicked and the Form is submitted.
When the Form is submitted, the Text and Value of the selected DropDownList Item are captured through the FruitModel class object received as parameter.
The values of FruitId and FruitName are fetched and are set into a ViewData object which will be later displayed in Razor Page using JavaScript Alert Message Box.
public class IndexModel : PageModel
{
public SelectList Fruits { get; set; }
publicvoid OnGet()
{
this.Fruits = new SelectList(PopulateFruits(), "FruitId", "FruitName");
}
public void OnPostSubmit(FruitModel fruit)
{
string message = "Fruit Name: " + fruit.FruitName;
message += "\\nFruit Id: " + fruit.FruitId;
ViewData["Message"] = message;
}
private static List<FruitModel> PopulateFruits()
{
string constr = @"Data Source=.\SQL2017;Initial Catalog=AjaxSamples;Integrated Security=true";
List<FruitModel> fruits = new List<FruitModel>();
using (SqlConnection con = new SqlConnection(constr))
{
string query = "SELECT FruitName, FruitId FROM Fruits";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
fruits.Add(new FruitModel
{
FruitName = sdr["FruitName"].ToString(),
FruitId = Convert.ToInt32(sdr["FruitId"])
});
}
}
con.Close();
}
}
return fruits;
}
}
Razor Page (HTML)
The HTML of Razor Page consists of an HTML Form with a DropDownList, a Hidden Field and a Submit Button.
The Submit Button has been set with the POST Handler method using the asp-page-handler attribute.
Note: In the Razor PageModel, the Handler method name is OnPostSubmit but here it will be specified as Submit when calling from the Razor HTML Page.
The Model data has been assigned to the DropDownList using the asp-items Tag Helpers attribute.
The DropDownList has been assigned a jQuery OnChange event handler, when an item is selected in the DropDownList, the Text of the selected item is copied in the Hidden Field.
When the Submit Button is clicked, the Form gets submitted and the FruitId and FruitName values are sent to the Razor PageModel.
Finally, the ViewData object returned from the PageModel is checked for NULL and if it is not NULL then the FruitId and FruitName values of the selected Fruit is displayed using JavaScript Alert MessageBox.
@page
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@model Razor_DropDownList_ADO.Net.Pages.IndexModel
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width"/>
<title>Index</title>
</head>
<body>
<form method="post">
<select id="ddlFruits" name="FruitId" asp-items="@Model.Fruits">
<option value="0">Please select</option>
</select>
<input type="hidden" name="FruitName"/>
<input type="submit" value="Submit" asp-page-handler="Submit"/>
</form>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
$("body").on("change", "#ddlFruits", function () {
$("input[name=FruitName]").val($(this).find("option:selected").text());
});
</script>
@if (ViewData["Message"] != null)
{
<script type="text/javascript">
$(function () {
alert("@ViewData["Message"]");
});
</script>
}
</body>
</html>
Screenshot
![Database Connection without Entity Framework in ASP.Net Core Razor Pages]()
Downloads
