Tuesday, 31 December 2019
Wednesday, 26 June 2019
Thursday, 30 May 2019
Monday, 8 April 2019
SingleOrDefault() Vs. FirstOrDefault() in LINQ Query
Single() / SingleOrDefault()
First () / FirstOrDefault()
Single() - There is exactly 1 result, an exception is thrown if no result is returned or more than one result.
SingleOrDefault() – Same as Single(), but it can handle the null value.
First() - There is at least one result, an exception is thrown if no result is returned.
FirstOrDefault() - Same as First(), but not thrown any exception or return null when there is no result.
Single() asserts that one and only one element exists in the sequence.
First() simply gives you the first one.
When to use
Use Single / SingleOrDefault() when you sure there is only one record present in database or you can say if you querying on database with help of primary key of table.
When to use
Developer may use First () / FirstOrDefault() anywhere, when they required single value from collection or database.
Single() or SingleOrDefault() will generate a regular TSQL like "SELECT ...".
The First() or FirstOrDefault() method will generate the TSQL statment like "SELECT TOP 1..."
In the case of Fist / FirstOrDefault, only one row is retrieved from the database so it performs slightly better than single / SingleOrDefault. such a small difference is hardly noticeable but when table contain large number of column and row, at this time performance is noticeable.
Thursday, 4 April 2019
IQS
- Difference between CROSS APPLY and OUTER APPLY in SQL Server
- Understanding a SQL Server execution plan
- difference between function and stored procedure
- how to apply indexes in sql server
- how to apply cursors in sql server
- how to pass authentication token in header
- select sum(CustomerID) from Customer
- how to set outproc session in asp.net
- How to improve SQL Server database performance
- Improve SQL Query Performance.
- Avoid Multiple Joins in a Single Query. ...
- Eliminate Cursors from the Query. ...
- Avoid Use of Non-correlated Scalar Sub Query. ...
- Avoid Multi-statement Table Valued Functions (TVFs) ...
- Creation and Use of Indexes. ...
- Understand the Data. ...
- Create a Highly Selective Index.
Ø Stored
procedure can have input and output parameters.
Ø Function allow only input parameters,
doesn’t support output parameters.
Ø Stored
procedures cannot be called from Function.
Ø Function
can be called from Procedure.
Ø Stored
procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statements.
Ø Function
allows only SELECT statement, we cannot use DML statements in Function.
Ø Stored
procedure cannot be called from Select/Where/Having and so on statements. Execute
or Exec statement can be used to call or execute Stored Procedure.
Ø Function
can be called from select statement.
Ø Exception
can be handled by try-catch block in a stored procedure.
Ø try-catch
block cannot be used in a Function.
Ø We
can use transactions within stored procedure.
Ø Transactions
are not allowed within Function.
Ø We
can use table variables as well as temporary tables in Procedure.
Ø We
can use only table variables in Function, temporary tables are not allowed.
Thursday, 28 March 2019
Multiple Models to one View in ASP.Net MVC
Namespaces
You will need to import the following namespaces.
using System.Dynamic;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
Model
Following are the two Model classes.
CustomerModel
The following Model class will be used to populate data from Customers Table.
public class CustomerModel
{
public string CustomerId { get; set; }
public string CustomerName { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
EmployeeModel
The following Model class will be used to populate data from Employees Table.
public class EmployeeModel
{
public string EmployeeId { get; set; }
public string EmployeeName { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
Controller
The Controller consists of an Index Action method. Inside this Action method, first an object of ExpandoObject class is created and it's instance is assigned to a variable of type Dynamic.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
dynamic model = new ExpandoObject();
model.Customers = GetCustomers();
model.Employees = GetEmployees();
return View(model);
}
private static List<CustomerModel> GetCustomers()
{
List<CustomerModel> customers = new List<CustomerModel>();
string query = "SELECT TOP 10 CustomerID, ContactName, City, Country FROM Customers";
string constr = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(new CustomerModel
{
CustomerId = sdr["CustomerID"].ToString(),
CustomerName = sdr["ContactName"].ToString(),
City = sdr["City"].ToString(),
Country = sdr["Country"].ToString()
});
}
}
con.Close();
return customers;
}
}
}
private static List<EmployeeModel> GetEmployees()
{
List<EmployeeModel> employees = new List<EmployeeModel>();
string query = "SELECT EmployeeID, (FirstName + ' ' + LastName) [Name], City, Country FROM Employees";
string constr = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
employees.Add(new EmployeeModel
{
EmployeeId = sdr["EmployeeID"].ToString(),
EmployeeName = sdr["Name"].ToString(),
City = sdr["City"].ToString(),
Country = sdr["Country"].ToString()
});
}
con.Close();
return employees;
}
}
}
}
}
View
Inside the View, first you will need to import the namespace for accessing the Model classes.
Then you will need to declare the Model for the View as dynamic.
For displaying the records, two HTML Tables are used and by iterating over the Generic List Collection of Model objects, rows are added to the HTML Tables.
@using Multiple_Model_MVC.Models
@model dynamic
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width"/>
<title>Index</title>
</head>
<body>
<table cellpadding="0" cellspacing="0">
<tr>
<th>CustomerID</th>
<th>Contact Name</th>
<th>City</th>
<th>Country</th>
</tr>
@foreach (CustomerModel customer in Model.Customers)
{
<tr>
<td>@customer.CustomerId</td>
<td>@customer.CustomerName</td>
<td>@customer.City</td>
<td>@customer.Country</td>
</tr>
}
</table>
<hr/>
<table cellpadding="0" cellspacing="0">
<tr>
<th>EmployeeID</th>
<th>Employee Name</th>
<th>City</th>
<th>Country</th>
</tr>
@foreach (EmployeeModel employee in Model.Employees)
{
<tr>
<td>@employee.EmployeeId</td>
<td>@employee.EmployeeName</td>
<td>@employee.City</td>
<td>@employee.Country</td>
</tr>
}
</table>
</body>
</html>
Screenshot
Subscribe to:
Posts (Atom)