How to handle errors in SQL Server By Sagar Jaybhay

In this article we will understand How to handle errors in SQL Server By Sagar Jaybhay.

Handle Error

In SQL Server 2005 they introduced try/catch block in SQL server likes C# and Java.

In SQL Server 2000 they have syntax -- @@Error

In SQL Server 2005 they have introduced – try/catch.

In SQL server the variables which are starting with @@ symbols are called global variables but hey are not variables but working like variables but they are similar to a function.

Throw an error in SQL Server we have a function, Raiserror(error_message, severity level, state)

In this Raiserror function, the first parameter is error_message which we want to display. Like throw keyword in C#.

The second parameter is Error Severity level- which is most cases is 16 means the user can resolve this error.

The third Parameter is State: It is a number between 1 to 255 but Raiserror will generate state between 1 to 127.

@@ERROR is a system function that contains non zero value if there is no error else it has 0 value.

@@ERROR is cleared and reset on each statement of execution.

Try/ Catch:

Whatever we can do with @@ERROR we can achieve it by using a try-catch block. You can write any number of statement inside the try block and if any error occurred then control directly moves in the catch block and the rest of the statement in try blocks are the skip. If no error will occur then the control bypass/skip the execution of the catch block.

Errors that are trapped in the catch block are not returned to calling function for that you need to use Raiserror function.

You can use system function which gives more information about the error and this can be called inside catch block only.

To write code in try-catch block use below syntax here

Begin Try
//-- Your code is here
End Try
Begin Catch
//-- Your code is here

End Catch

In SQL server to get more information SQL server provides functions for that which is described below

1. Error_Number() : display how many errors occurred
2. Error_message() : It returns the message of error    
3. Error_Procedure(): returns the name of the stored procedure or trigger where an error occurs
4. Error_State(): returns the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block
5. Error_Severity(): returns the error severity value of an error, regardless of how many times it runs or where it runs within the scope of the CATCH block
6. Error_Line (): returns the line number at which the error occurred. 

Above all of these functions are run inside the context of the catch block. Outside the catch block, it will return null.

GitHub: https://github.com/Sagar-Jaybhay

Route Parameter Constraints In Asp.Net Razor Pages

In this article we will understand Route Parameter Constraints In Asp.Net Razor Pages by Sagar Jaybhay

Route Parameter Constraints

Route Constraints is a
mechanism where it filters out or restricts unwanted route parameters to
reaching out to PageModel methods.

To add route constraints we need to add constraints in route template data. For example, if we want our id only accepts numeric value then for this is below code.

@page "/employee/view/id:int"

Now we want to apply constraints with optional parameters then we need to use below syntax.

@page "/employee/view/id:int?"

Below is a list of the table this kind of constraints we can apply in the route template.

Constraint	Description	Example
alpha	Matches uppercase or lowercase Latin alphabet characters (a-z, A-Z)	title:alpha
bool 1	Matches a Boolean value.	isActive:bool
int 1	Matches a 32-bit integer value.	id:int
datetime 1	Matches a DateTime value.	startdate:datetime
decimal 1	Matches a decimal value.	cost:decimal
double 1	Matches a 64-bit floating-point value.	latitude:double
float 1	Matches a 32-bit floating-point value.	x:float
long 1	Matches a 64-bit integer value.	x:long
guid 1	Matches a GUID value.	id:guid
length	Matches a string with the specified length or within a specified range of lengths.	key:length(8) postcode:length(6,8)
min	Matches an integer with a minimum value.	age:min(18)
max	Matches an integer with a maximum value.	height:max(10)
minlength	Matches a string with a minimum length.	title:minlength(2)
maxlength	Matches a string with a maximum length.	postcode:maxlength(8)
range	Matches an integer within a range of values.	month:range(1,12)
regex	Matches a regular expression.	postcode:regex(^[A-Z]2\d\s?\d[A-Z]2$)

We can apply more than
one constraint at a time for this use below syntax.

For example, we want to Id whose minimum value is 1 and max value 100 can accept

@page "/employee/view/id:min(1):max(100)"

How to create Custom Constraints in Asp.Net Razor pages?

There is a five-step method to create Custom Constraints in Asp.net razor pages.

• First, we required the inbuilt interface for creating a constraint and below is Interface for that.

public interface IRouteConstraint : IParameterPolicy
 
 bool Match(HttpContext httpContext, IRouter route, string routeKey, RouteValueDictionary values, RouteDirection routeDirection);
 

• The second is to create a class and implement this interface in our newly created class and your required logic in match method which returns true or false.

public bool Match(HttpContext httpContext, IRouter route, string routeKey, RouteValueDictionary values, RouteDirection routeDirection)
 
 if(values[routeKey].ToString().Trim().Length!=0)
 
 var regex = new Regex("^[a-zA-Z0-9]4$");
 if (regex.IsMatch(values[routeKey].ToString()))
 return true;
 
 return false;
 

• The now third step to add a custom constraint to our route template for this use below code.

@page "/employee/view/id:custom"

• When we run our application we get an error which is shown below.

---

An unhandled exception occurred while processing the request.

InvalidOperationException: The constraint
reference 'custom' could not be resolved to a type. Register the constraint
type with 'Microsoft.AspNetCore.Routing.RouteOptions.ConstraintMap'.

Microsoft.AspNetCore.Routing.DefaultParameterPolicyFactory.Create(RoutePatternParameterPart parameter, string inlineText)

• To overcome this error we need to add this constraint in configureservice method using ConstraintMap method

public void ConfigureServices(IServiceCollection services)
 
 services.AddRazorPages();
 services.AddSingleton<IEmployeeRepos, DBRepository>();
 services.Configure<RouteOptions>(option =>
 
 option.LowercaseUrls = true;
 option.LowercaseQueryStrings = true;
 option.AppendTrailingSlash = true;
 option.ConstraintMap.Add("custom", typeof(CustomConstraints.custom));
 );
 

By doing this our application work perfectly fine but when we pass more than 4 character id it will give below output.

Using custom constraints it will not throw an error but it shows 404 not found.

Now we need to understand each parameter in the Match method.

bool Match(HttpContext httpContext, IRouter route, string routeKey, RouteValueDictionary values, RouteDirection routeDirection);

Name	Explnation
httpContext	An object that encapsulates information about the HTTP request.
route	he router that this constraint belongs to.
routeKey	The name of the parameter that is being checked.
values	A dictionary that contains the parameters for the URL.
routeDirection	An object that indicates whether the constraint check is being performed when an incoming request is being handled or when a URL is being generated.

---

GitHub:- https://github.com/Sagar-Jaybhay/AspNerRazorPages

---

Route Parameter In Razor Pages Sagar Jaybhay

In this article we will understand Route Parameter In Razor Pages By Sagar Jaybhay.

Route Parameter:

Another way to pass value from is route template. If you see the above image you have seen that the id parameter is passed as a query string parameter. But we want to pass as route template to do this we need to add below code in our Display template of Details .cshtml page.

@page "id"

By doing so our URL will become like below

Here are URL contains id but not as a QueryString parameter. If you want to make the id parameter as optional value then you need to add a Question mark after this like below.

@page " id?"

Now we want to change the route URL you can do so by using the route template right now in our application we go to details view and we want our URL like https://localhost:44316/employee/details/view/a101/  to achieve this modify route template like below and you can achieve this.

@page "view/id"

The output is shown in the below image. By doing so /view and /id is appended to default URL and URL looks like below

But this URL so long we want some custom URL like https://localhost:44316/employee/view/a101/ To achieve this URL we need to modify route template like below

@page "/employee/view/id"

The output of this shown in below image and we can remove details name in URL

If you want to include as many parameters uses below syntax the name present in curly braces are route parameters.

@page "/employee/view/id/name"

Here keep in mind Model binder binds route parameter to id parameter in OnGet() method.

But if you want to access the Id parameter in the Display template you need to create a public property and assign value to it in the OnGet method.

Now this property we use in our Display Template so our output looks like below

So here we need to assign route parameter value to public property in OnGet() method why because by default route parameter support for OnPost()
the method only but we want to access for OnGet() means get call also so we need to use below syntax for that and no need to assign value in OnGet() method. Code for this is written below

 [BindProperty(SupportsGet = true)]
 public string ID get; set; 

The bind property attribute binds route parameter value to ID property in Page template public property. By default, BindProperty Bind value to this public property only for Post request so we pass SupportsGet flag to true to support for Get() call also.

GitHub:- https://github.com/Sagar-Jaybhay/AspNerRazorPages

Query String Parameters In Asp.Net Razor 2020

In this article we will understand Query String Parameters its significance. How to Use Query String Parameters in Asp.Net Razor Pages ?

Query String Parameters

If we want to pass data from the Display template to Code
behind file which is Page Model in our case then we can use the
asp-data-parameter_name tag helper to pass data from the display template to
the Page Model class. This parameter catches in OnGet or OnPost method.

Now we have to achieve functionality like when we click
on View button we can get in detail information about Employee in table format.
For this, we need to create one Details Razor page in our Employee folder which
resides under Page folder.

First, we need to pass data from View for that use below the line of code.

 <tr><td><a class="btn btn-primary" asp-page="/Employee/Details" asp-route-ID="@emp.ID">View</a></td></tr>

Now we need to cacth this Id in OnGet method for that we pass parameter in OnGet() method.

public void OnGet(string id)
 

 

In this Model binding maps id parameter value to OnGet() method id parameter. And by default id is pass as query string parameter in URL.

We don’t have any method which can get Employee Information by using Id so we need to create this in Interface and then we need to implement this in our DBRepository class as we implement this interface in this class.

The code for Interface and DbRepository have shown below.

 public interface IEmployeeRepos
 
 IEnumerable<Employee> GetAllEmployees();
 Employee GetEmployee(string Id);
 

 public class DBRepository: IEmployeeRepos
 
 private List<Employee> _empList;
 public DBRepository()
 
 _empList = new List<Employee>() 
 new Employee() Dept=Department.HR,Email="sagarjaybhay@gmail.com",ID="A101",Name="Sagar Jaybhay",Photopath=@"sagar jaybhay.png",
 new Employee()Dept=Department.IT,Email="rani@hotmail.com",ID="A201",Name="Rani",Photopath=@"rani.jpg" ,
 new Employee() Dept=Department.Testing,Email="raja@gmail.com",ID="A301",Name="Raja",Photopath=@"ram.png",
 new Employee() Dept=Department.Testing,Email="raghu@gmail.com",ID="A401",Name="Ragu",Photopath=""

 ;
 


 public IEnumerable<Employee> GetAllEmployees()
 
 return this._empList;
 

 public Employee GetEmployee(string Id)
 
 return this._empList.Where(s => s.ID == Id).SingleOrDefault();
 
 

Here is one note: When you use the anchor tag element with an asp-page tag helper then not use default href attribute if you do so you will get an error. Following is the error.

An
unhandled exception occurred while processing the request.

InvalidOperationException: Cannot override the 'href' attribute for <a>. An <a> with a specified 'href' must not have attributes starting with 'asp-route-' or an 'asp-action', 'asp-controller', 'asp-area', 'asp-route', 'asp-protocol', 'asp-host', 'asp-fragment', 'asp-page' or 'asp-page-handler' attribute.

GitHub: https://github.com/Sagar-Jaybhay/AspNerRazorPages

Generic List Routing & Routing Constraints

In this article we will understand How to display Generic List In Asp.Net Razor Pages? How Routing Works in Asp.Net Razor Pages ? How to apply Routing Constraints in Asp.Net Razor Pages by Sagar Jaybhay.

Generic List Routing & Routing Constraints

Now see below image we need to display employee list like this image. We have Employee property present in our Index page model we access this in our view. Now iterating over this list we apply some bootstrap CSS and formatting to display like below.

Code in the Display template

@page
@model RazorApplication.Pages.Employee.IndexModel
@
 ViewData["Title"] = "Employees";


 <h1>Employees</h1>

<h3>No of Employees : @Model.Employees.Count()</h3>


<hr />
<br />
<div class="container-fluid">
 @foreach (var emp in Model.Employees)
 
 var imgSrc = @"images/" + (emp.Photopath.Trim().Length != 0 ? emp.Photopath : "noimage.png");
 <div class="row" style="border:thin 1px black">

 <div class="col-lg-4">
 <img src="@imgSrc" alt="@emp.Name" style="height:150px;width:150px" />
 </div>
 <div class="col-lg-4">
 <table style="border:thin;1px" class="table table-bordered table-active">
 <tbody>
 <tr>
 <td>Name</td>
 <td>@emp.Name</td>
 </tr>
 <tr>
 <td>Email</td>
 <td>@emp.Email</td>
 </tr>
 <tr>
 <td>Department</td>
 <td>@emp.Dept</td>
 </tr>
 </tbody>
 </table>
 </div>
 <div class="col-lg-2">
 
 <table class="table table-bordered">
 <tbody>
 <tr><td><a href="#" class="btn btn-info">Edit</a></td></tr>
 <tr><td><a href="#" class="btn btn-primary">View</a></td></tr>
 <tr><td><a href="#" class="btn btn-danger">Delete</a></td></tr>
 </tbody>

 </table> 
 
 
 </div>
 </div>
 <hr />
 <br />
 

</div>

See the above image in that we use this Employees Property in our Index.cshtml Display template.

Basics Of Routing In Asp.Net Razor Pages:

Routing is the URL pattern matching
techniques and in this, it matches URLs with razor Pages. It is like most of
page centric frameworks where URLs match with physical file paths. Keypoint to
remember it start with the Root folder and in this Pages is the root folder.

Like MVC Razor pages also use
Conventions and Configuration for routing. Razor pages use the same
infrastructure as MVC for routing.

The standard Razor Pages 3.x site
template includes 3 pages in the root folder.

1. Error.cshtml
2. Index.cshtml
3. Privacy.cshtml

By default, route templates are generated by taking the
root path of each Content Page and then it removes root folder name from the
start of path and extension from the end of the path.

In Asp.Net Razor pages Index.cshtml is the default
document present in any folder so it has 2 different routes one with “blank and
other with https://sagarjaybhay.com/index

So in our application, we created the Employee folder and
in that we Index.cshtml so our route becomes

1. “blank which is
   an empty string path is “https://yourdomain.com/Employee”
2. “Employee/Index”
   this is the second path https://yourdomain.com/Employee/index

But if you create Employee.cshtml in your root domain and
Employee folder is present in your Pages folder then when you run your
application it will throw an exception.

An
unhandled exception occurred while processing the request.

AmbiguousMatchException: The request
matched multiple endpoints. Matches:



/Employee

/Employee/Index

Microsoft.AspNetCore.Routing.Matching.DefaultEndpointSelector.ReportAmbiguity(CandidateState[]
candidateState)

How to handle this AmbigousMatch Exception in Asp.Net Razor pages?

1. One to overcome
   this error by renaming one of the file or folder names in our Asp.net razor
   page application.
2. The second way
   is to Overwrite default routes. We know that if Routes in Asp.Net razor pages
   are mapped to a physical file location.

@page "EmployeeList"

We are giving above custom route names to our page in Employee
folders Index.cshtml file and error is gone see below image

So by giving this custom route, you can’t use the default
route in our application means  https://yourdomain.com/Employee/Index
this route won't work.

1. The third way to
   overcome this error is used Route parameter in our application

@page "name"

In the above we give name is our route parameter and when
we invoke the URL

https://yourdomain.com/Employee/abc here ABC is our
route parameter.

Constraint on Route Parameter

If we see above URL we pass ABC to name parameter in Index view which can accept any value like character, number any value to add a constraint. Now we want our URL parameter to accept the only character then we have name:constraint_name syntax.

@page "name:alpha"

By doing this our URL only accepts the character and if we pass number it will throw an error.

GitHub:- https://github.com/Sagar-Jaybhay/AspNerRazorPages

What Is DataBase Normalization By Sagar Jaybhay

In this article we will understand What Is DataBase Normalization ? Different Types of Normalization forms By Sagar Jaybhay.

DataBase Normalization

Database
normalization is a process of organizing data and minimizing data redundancy
which in turn ensures data consistency.

The problem of data redundancies:

1. Data is duplicated
2. Disk space required more which is wastage
3. Data inconsistency
4. DML queries become slow(Insert, update, delete)

create table emps(empname nvarchar(20),gender nvarchar(20),salary float,deptname nvarchar(20),deptheaad nvarchar(20),deptlocation nvarchar(20))
insert into emps values('sagar','male',1000,'Hr','Raju','London'),
('A','female',2000,'IT','X','UK'),
('B','male',3000,'Account','Y','USA'),
('C','female',4000,'Support','Z','India');

Database normalization
is a step by step process. There is 6 normal forms that start from 1^st
normal form to the 6^th normal form.

But most
of the databases support up to 3^rd normal form.

In
general, normalization means broken down the table into multiple tables where
we can avoid data redundancies in which repeating columns or rows move to
another table.

Below is unnormalize table and we want to normalize this table.

In the
above case, you can see we are repeating the depthead and department name
column. Suppose this table has millions of records and in the future, our
department head will change then Raju to xyz then we need to update millions of
records and this is time-consuming and performance will degrade.

So if we remove this repeating rows into another table which decreases space requirement and time required for this is minimum.

1st Normal Form

• It means data in the column should be atomic and no column contains multiple data by comma-separated.

DeptName	EmpName
Hr	Sagar, Suresh, Ramesh
IT	X, y, Z

This not good.

• The table does not contain any repeating column groups.

DeptName	EmpName1	Empname2	Empname3
Hr	Sagar	Suresh	Ramesh
IT	X	Y	Z

• You can identify each record by the primary key.

Deptid	DeptName
1	HR
2	IT

Deptid	Empname
1	Sagar
1	Suresh
1	Ramesh
2	X
2	Y
2	Z

2^nd Normal Form(2NF)

1. The Table needs to meet the requirement of 1^st normal
   form.
2. Need to move redundant data to separate table
3. Create a relationship between these tables using primary key and
   foreign key.

EmpID	EmpName	Gender	Salary	DeptName	DeptHead	DeptLocation
1	Sagar	Male	10000	HR	X	India
2	Seeta	Female	20000	IT	Y	USA
3	Suresh	male	30000	Sales	Z	UK
4	Raju	Male	40000	Account	K	London

Now we are splitting the above table into 2 different tables which look like below here deptid is a foreign key by which relationship is achieved.

DeptID	DeptName	DeptHead	DeptLocation
1	HR	X	India
2	IT	Y	USA
3	Sales	Z	UK
4	Account	K	London

EmpID	EmpName	Gender	Salary	DeptID
1	Sagar	Male	10000	1
2	Seeta	Female	20000	2
3	Suresh	male	30000	3
4	Raju	Male	40000	4

3^rd Normal Form

1. The table needs to meet all the conditions in the first normal form
   and second normal form.
2. The table does not contain any column that not fully depend on the
   primary key of that table.

EmpID	EmpName	Gender	Salary	Annual Salary	DeptID
1	Sagar	Male	10000	120000	1
2	Seeta	Female	20000	240000	2
3	Suresh	male	30000	360000	3
4	Raju	Male	40000	480000	4

In the above table, the Annual salary table does not fully depend on empid. So there is no need for the annual salary you can compute this query so you can remove this column.

GitHub Profile:- https://github.com/Sagar-Jaybhay

How to create Models In Asp.Net Blazor

In this article you will understand How to create Models In Asp.Net Blazor By Sagar Jaybhay.

In Asp.Net Razor pages we don’t have models folder now to perform CRUD operation we will create the Model Class Library Project in our application. By creating a class library project it is easily used in any other project like web API, asp.net mvc.

Models In Asp.Net Razor Pages:

public class Employee
 
 public string ID get; set; 
 public string Name get; set; 
 public string Email get; set; 
 public string Photopath get; set; 
 public Department? Dept get; set; 

 

 public enum Department
 
 IT, HR, Support, Testing, Account
 

Models In Razor

In our, we added this.NetStandard class library projects with Employee as class and Department as an enum.

Now we will create a DataAccess Layer in
our application to do this we again create .Net standard Class library project.
In this, we create one Interface which has the GetAllEmployees method and which
returns a list of employees. After that, we create the DbRepository class which
implements the Interface. We create this for use of Dependency injection and
Inversion of control and to know more about this pattern use the below link.
https://sagarjaybhay.com/repository-pattern-asp-net-core-by-sagar-jaybhay/

Now we refer these projects in our main application and to check everything working fine we create the Employee field in our Index page and constructor we inject the IEmployeeRepos object by using dependency injection. Now we have to check the count of an employee. So in the Page Display template, we use the below code.

<h3>No of Employees : @Model.Employees.Count()</h3>

And our page model class looks like below

 public class IndexModel : PageModel
 
 public List<RazorPages.Models.Employee> Employees;
 public IndexModel(IEmployeeRepos employeeRepos)
 
 EmployeeRepos = employeeRepos;
 

 public IEmployeeRepos EmployeeRepos get; 

 public void OnGet()
 
 Employees = EmployeeRepos.GetAllEmployees().ToList(); 
 
 

After running this application we get below error output.

An unhandled exception
occurred while processing the request.

InvalidOperationException:
Unable to resolve service for type 'DAL.IEmployeeRepos' while attempting to
activate 'RazorApplication.Pages.Employee.IndexModel'.

Microsoft.Extensions.DependencyInjection.ActivatorUtilities.GetService(IServiceProvider sp, Type type, Type requiredBy, bool isDefaultParameterRequired)

To resolve this error we need to do the
following changes in our application. We
forget to register our service that’s why this error occurred.  The error occurred because we never initialize
Interface object with concrete type in our startup class configure service
method. It means that below is code for registering the service in the Configure
method.

To initialize this use the below code of configure service method

public void ConfigureServices(IServiceCollection services)
 
 services.AddRazorPages();
 services.AddSingleton<IEmployeeRepos, DBRepository>();
 

In the above code, we use the
AddSingleton method what is this? To learn about this visit this link to
understand better https://sagarjaybhay.com/what-is-the-difference-between-addtransient-vs-addsingleton-vs-addscoped-in-asp-net-core-mvc-by-sagar-jaybhay/

The output looks like below

GitHub Link:- https://github.com/Sagar-Jaybhay/AspNetRazor