Hello all!
This Blazor stuff is very new to me. Old Desktop app dev here.
I want the real basics of how you connect to a MS SQL DB. I have a SP that takes a parameter and returns a number of columns.
I have a grid on a razor page and it works when I hardcode the data within the page.
I now want to connect to my DB and use the data it returns.
I created the DBContext. Has the connectionstr and the dbSet and the class describing the resultset.
Not sure how to connect to the SP or get that attached to the data grid. I want the result to feed the columns to the grid.
Lost.
Thanks.
1 Answer, 1 is accepted
0
Accepted
Deasun
Top achievements
Rank 3
Bronze
Bronze
Bronze
Rank 3
Bronze
Bronze
Bronze
answered on 09 Sep 2022, 06:33 PM
see my comment above with the answer that worked for me.
Hi Deasun,
Our components don't connect to the database directly, so you can use any generic approach that you are already familiar with. In case you need assistance with that, there are some online resources, for example -
1] Created a class under the Data folder in my project.
clsDataAccessService.cs
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Telerik.Blazor.Components.Editor;
namespace SodaTools.Data
{
public static class clsDataAccessService
{
/// <summary>
public static class DBConnection
{
private static IConfiguration config;
public static IConfiguration Configuration
{
get
{
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json");
config = builder.Build();
return config;
}
}
}
/// </summary>
public class ConnectionString
{
public string connectionStringTest =
(
"Server=" + DBConnection.Configuration.GetSection("DbConfigRAMgrTest:ServerName").Value
+ ";Database=" + DBConnection.Configuration.GetSection("DbConfigRAMgrTest:DatabaseName").Value
+ ";User ID=" + DBConnection.Configuration.GetSection("DbConfigRAMgrTest:UserName").Value
+ ";Password=" + DBConnection.Configuration.GetSection("DbConfigRAMgrTest:Password").Value
+ ";Trusted_connection=false;MultipleActiveResultSets=true;"
);
public string connectionStringPro =
(
"Server=" + DBConnection.Configuration.GetSection("DbConfigRAMgrPro:ServerName").Value
+ ";Database=" + DBConnection.Configuration.GetSection("DbConfigRAMgrPro:DatabaseName").Value
+ ";User ID=" + DBConnection.Configuration.GetSection("DbConfigRAMgrPro:UserName").Value
+ ";Password=" + DBConnection.Configuration.GetSection("DbConfigRAMgrPro:Password").Value
+ ";Trusted_connection=false;MultipleActiveResultSets=true;"
);
public string connectionStringDev =
(
"Server=" + DBConnection.Configuration.GetSection("DbConfigRAMgrDev:ServerName").Value
+ ";Database=" + DBConnection.Configuration.GetSection("DbConfigRAMgrDev:DatabaseName").Value
+ ";User ID=" + DBConnection.Configuration.GetSection("DbConfigRAMgrDev:UserName").Value
+ ";Password=" + DBConnection.Configuration.GetSection("DbConfigRAMgrDev:Password").Value
+ ";Trusted_connection=false;MultipleActiveResultSets=true;"
);
}
public static List<TNSearchResult> doTNSearch(string strTNToSearchFor, string strDBEnvir = "Pro")
{
ConnectionString connectionString = new ConnectionString();
string strConn = "";
switch (strDBEnvir)
{
case "Dev":
strConn = connectionString.connectionStringDev.ToString();
break;
case "Pro":
strConn = connectionString.connectionStringPro.ToString();
break;
default:
strConn = connectionString.connectionStringTest.ToString();
break;
};
List<TNSearchResult> TNSearchResults = new List<TNSearchResult>();
TNSearchResult ResultRow;
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(strConn);
SqlDataAdapter da = new SqlDataAdapter("EXEC [dbo].[SP_Cost_Details_searchForTN]" + strTNToSearchFor, con);
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
ResultRow = new TNSearchResult();
ResultRow.TN = row["TN"] as string;
ResultRow.BAN = row["BAN"] as string;
ResultRow.Platform = row["Platform"] as string;
ResultRow.Provider = row["Provider"] as string;
ResultRow.BillDT = (DateTime)row["BillDT"];
ResultRow.CustID = (Int64)row["CustID"];
ResultRow.LineID = (Int64)row["LineID"];
ResultRow.Customer = row["Customer"] as string;
ResultRow.MRCAmt = (decimal)row["MRC Amt"];
ResultRow.OCCAmt = (decimal)row["OCC Amt"];
ResultRow.UsageAmt = (decimal)row["Usage Amt"];
ResultRow.TotalAmt = (decimal)row["Total Amt"];
TNSearchResults.Add(ResultRow);
};
return TNSearchResults;
}
}
public class TNSearchResult
{
public string? TN { get; set; }
public string? BAN { get; set; }
public string? Platform { get; set; }
public string? Provider { get; set; }
public DateTime BillDT { get; set; }
public Int64 CustID { get; set; }
public Int64 LineID { get; set; }
public string? Customer { get; set; }
public decimal MRCAmt { get; set; }
public decimal OCCAmt { get; set; }
public decimal UsageAmt { get; set; }
public decimal TotalAmt { get; set; }
}
}
2] Json file looks like:
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"DbConfigRAMgrTest": {
"DatabaseName": "MyBDName",
"UserName": "BDUserName",
"Password": "DBUserPW",
"ServerName": "ServerName"
}
"AllowedHosts": "*"
}
3] ON the Razor page
@inject IConfiguration Configuration
@inject IJSRuntime JSRuntime
// codebehind
// for data
using SodaTools.Data;
private async void getData()
{
List<TNSearchResult> TnSearchResults = new List<TNSearchResult>();
TnSearchResults = clsDataAccessService.doTNSearch(stringValue, GlobalStuff.msDBEnvir);
//attaching to my grid
GridData = TnSearchResults;
}