How to combine multiple stored procedures into a single resulset

Let’s say, you have multiple stored procedures each of which is tied to a partial view or View Component in MVC. Now, while this may seem like a logical thing to do, but what if you want the entire payload to return to a single View.

Well, how about combine all the stored procedures and return a single result set. Here’s how in three simple steps.

First, let’s take a look at the diagram below:

The Employee table has the following records:

Following are the three stored procedures that I have created for the purposes of this demo:

CREATE OR ALTER PROCEDURE [dbo].[spGetMinimumSalary]	
	@MinSalaryRange MONEY,
	@MaxSalaryRange MONEY
AS
BEGIN
	SET NOCOUNT ON;
	 
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
		(Salary BETWEEN @MinSalaryRange AND @MaxSalaryRange)
END
CREATE OR ALTER PROCEDURE [dbo].[spGetMedianSalary]	
	@MinSalaryRange MONEY,
	@MaxSalaryRange MONEY
AS
BEGIN	
	SET NOCOUNT ON;
  
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
((Salary > @MinSalaryRange) AND (Salary <= @MaxSalaryRange))
END
CREATE OR ALTER PROCEDURE [dbo].[spGetMaximumSalary]	
	@Salary MONEY	
AS
BEGIN
	SET NOCOUNT ON;
	   
	SELECT
		FirstName, 
		LastName, 
		Gender
	FROM 
		Employee 
	WHERE
		(Salary > @Salary)
END

Now, comes the interesting part. Let's create a temporary table:

CREATE TABLE #TmpSalary 
(
	FirstName NCHAR(25),
	LastName NCHAR(25),
	Gender NCHAR(10)
)

As you can see, the temporary table only has the structure, but no data:

Select * from #TmpSalary

Now, let's insert the records into the temporary table that are returned from executing the stored procedures like so:

INSERT INTO #TmpSalary
EXEC spGetMinimumSalary 0, 50000

INSERT INTO #TmpSalary
EXEC spGetMedianSalary 50000, 75000

INSERT INTO #TmpSalary
EXEC spGetMaximumSalary 75000

Let's view the records now:

Select * from #TmpSalary

There you have it! 🙂

Posted in SQL Server | Comments Off on How to combine multiple stored procedures into a single resulset

How to pass delimited list to a Stored Procedure

Let’s write a Table-valued function below. This can be created by navigating to Functions – Table-Valued Functions inside SQL Management Studio.

Just an FYI, I am using the AdventureWorks database, but this  sample can be used in any situation.

CREATE FUNCTION [dbo].[fn_SplitStateProvinceCode]
(
   @List VARCHAR(MAX),
   @Delimiter CHAR(1)
)
RETURNS TABLE 
AS 
  RETURN ( SELECT Item = CONVERT(varchar(500), Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, ''
        + REPLACE(@List, @Delimiter, '') + '').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );

For testing, you can declare a variable and test the statement by passing the @List and delimiter like so. As you can see, in the sample below, the delimiter is ‘,’.

Caution: The delimited string is strictly delimited by a comma and has no space in between province codes.

DECLARE @List VARCHAR(MAX)
SET @List = 'AB,ON,TX,VIC,WA'
SELECT CODES = ITEM FROM dbo.[fn_SplitStateProvinceCode (@List, ',') 

If you wish to write it in a query using the Where clause and using ‘IN’ you can do the following:

DECLARE @List VARCHAR(MAX)
SET @List = 'AB,ON,TX,VIC,WA'
Select * from Person.StateProvince 
WHERE StateProvinceCode IN (Select CODES = ITEM FROM dbo.fn_SplitStateProvinceCode (@List, ','))	

Cheers,

Obi

Posted in SQL Server | Comments Off on How to pass delimited list to a Stored Procedure

Global Query Filters in EF Core 2.0

Global Query Filters are a new addition to the arsenal of features in EF Core 2.0. This also implies that EF core team spearheaded by Diego Vega who are working diligently to bring EF Core at par with its Entity Framework 6.x classic counterpart. Like it or not, its good news for us developers, right?

Well, GQF in short are LINQ query predicates. In other words they are a boolean expression that are typically passed to the LINQ “Where” query operator and applied to Entity Types in the metadata model (usually in OnModelCreating). Such filters are automatically applied to any LINQ queries involving those Entity Types, including Entity Types referenced indirectly, such as through the use of Include or direct navigation property references.

Below is a Snapshot of the database containing two tables i.e. Department & Employee respectively.

GQFContext.cs class

namespace GlobalQueryFilters 
{
    class GQFContext : DbContext
    {
        public DbSet Department { get; set; }
        public DbSet Employee { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"server=Obi-Oberoi; database=Company; Trusted_Connection=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity().HasQueryFilter(e => !e.IsDeleted);
        }

    }

Department & Employee POCO Classes

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace GlobalQueryFilters
{
    public class Department
    {
        [Key]
        public int DepartmentID { get; set; }
        public string Name { get; set; }
        public List Employee { get; set; }
    }

    public class Employee
    {
        [Key]
        public int ID { get; set; }
        [ForeignKey("DepartmentID")]
        public int DepartmentID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Gender { get; set; }
        public decimal Salary { get; set; }
        public bool IsDeleted { get; set; }
        public Department Department { get; set; }
    }
}

Program.cs

 
static void Main(string[] args)
        {
            using (var ctx =  new GQFContext())
            {
                var emp = ctx.Employee.ToList();

                foreach (var item in emp.Where(e => e.DepartmentID == 3))
                {
                    Console.WriteLine("Name = {0},{1}", item.FirstName, item.LastName);
                }
                Console.ReadLine();
            }
        }

Notice the two records with the DepartmentID of 3 that are being filtered using the LINQ query above.

Now, let’s try and do something different. Let’s query the records with DepartmentID of 2. In the Employee table, we have at least two records with DepartmentID of 2. See below:

Notice the record for the employee Steve Pound has one of his records marked for deletion. That’s precisely what we intended for when using Global Query Filter which as you can recall from the code snippet below. This code is defined in the GQFContext class where we have specifically filtered the record(s) that are marked for soft deletion  like so:

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity().HasQueryFilter(e => !e.IsDeleted);
        }

Notice, the record Steve Pound is filtered as a result of Global Query Filter that is defined as a predicate in the “HasQueryFilter” method.

Posted in ORM | Comments Off on Global Query Filters in EF Core 2.0

Difference between Varchar and Nvarchar. When to use what?

This post was replaced by an erroneous content by an intruder…I will have re-write it.

The inconvenience is regretted my dear lovely readers!

Posted in SQL Server | Leave a comment

EF Core 2.1 Roadmap

EF Core has had a makeover and continues to add new features to its arsenal.
If you have already skipped EF Core 1.0, you might want to take a look at EF Core 2.0 and see what it has to offer.

I personally like EF Core more so because it’s lightweight, sits on top of .NET standard, it’s modular, extensible and most importantly it’s cross-platform. It is also part of the .NET Core family.
If you are wondering what platform to choose when developing an application using EF Core. I’d say, just about any platform you prefer. The reason is that the APIs are no different from one platform to another. That being said, let’s dive in and explore some of the new features that EF Core brings to the table:

• Querying
• Mapping
• Performance

 

Posted in ORM | Comments Off on EF Core 2.1 Roadmap

Write a program that prints an average temperatue that is close to Zero

The idea is to get the lowest number that is close to the value zero (irrespective of the negative or positive value) from an array of average temperatures that are supplied like so:

{ 7, 7.9, 10.5, -4, 3, -6, -2, 1, -15, 11.3, 9, -1 }

Solution:
One way is to iterate through an array of numbers and store the lowest number in a temp variable and print it out to a console.

As a side, an Array class provides methods for creating, manipulating, searching arrays which serves as the base class for all arrays in common language runtime typically referred to as CLR. The array data structure contains a contiguous collection of data items that can be accessed by an ordinal index.

I have assigned an arbitrary value of 50 to the temp variable prior to iterating through the array elements, comparing it and assigning the lowest number to the temp variable until it reaches the last element of the array before printing out the number.

public static void GetTemperatureCloseToZero()
        {
            double temp = 50.0; //some arbitrary value
            double diff;
            int target = 0;

            double[] arr = { 7, 7.9, 10.5, -4, 3, -6, -2, 1, -15, 11.3, 9, -1 };

            for (int i = 0; i < arr.Length; i++)
            {
                diff = Math.Abs(Convert.ToDouble(arr[i])) - Convert.ToDouble(target);
                if (diff <= temp)
                {
                    temp = diff;
                }
            }
            Console.WriteLine("The closest value to 0 is: {0}", temp);
            Console.ReadLine();
        }

Alternative Solution:
If you don't want to iterate through the items in an array, you can also use the Sort() method to sort elements in the entire list using the default comparer like so:

       public static void GetTempCloseToZeroSort()
        {
            double[] arr = { 7, 7.9, 10.5, -4, 3, -6, -2, 1, -15, 11.3, 9, -1 };
            List list = new List();

            foreach (var item in arr)
            {
                list.Add(Math.Abs(item));
            }
            list.Sort();
            
            Console.WriteLine("The closest value to 0 is: {0}", list[0]);
            Console.ReadLine();
        }

In both case the code produces the following output:ArrayOut1

I hope this will help those that are just getting into data structures.

Cheers,

Obi

 

Posted in Algorithms & Data Structures | Comments Off on Write a program that prints an average temperatue that is close to Zero

How to check if nested brackets are in the correct logical order

I am starting a series of blog posts on algorithms and data structures with the first one being the easiest!

Q. Write a method that checks if the nested brackets are in the correct logical order. The input string is the following:

“{ [ () {} ] }”

Please note, there can be many interpretations, but here’s how I am attempting to solve this question which may not necessarily be the most efficient solution. Here it goes…

First off, I create a console app and write a function that removes erroneous spaces like so:

 
        public static String FilterErroneousChars(String input)
        {
            int len = input.Length;
            char[] arr = input.ToCharArray();
            String filter = "";
            for (int i = 0; i < len; i++)
            {
                if (arr[i].Equals('(') || arr[i].Equals(')') || arr[i].Equals('[') || arr[i].Equals(']') || arr[i].Equals('{') || arr[i].Equals('}'))
                {
                    filter += arr[i];
                }
            }
            return filter;
        }

Once the function is executed, it'll return a string with no spaces, i.e. "{[(){}]}".

Next, I call the method from Program Main() and pass in the compressed string as input like so:

Then, I check to see if any of the nested pair(s) is equal. If the condition is met, the program replaces the nested brackets with an empty string until there's nothing left.

In the end, I check to see if the length of the string is 0. Once this condition evaluates to True, we know, that the input string is well-formed, else NOT.

I hope, those of you that are new to data structures, you might find this algorithmic exercise useful.

String s = Algorithms.FilterErroneousChars("{ [ () {} ] }");
            while ((s.Length != 0) && (s.Contains("[]") || s.Contains("()") || s.Contains("{}")))
            {
                s = s.Replace("[]", "");
                s = s.Replace("()", "");
                s = s.Replace("{}", "");
            }

            if (s.Length == 0)
            {
                Console.WriteLine("Well Formed");
            }
            else
            {
                Console.WriteLine("Not Well Formed");
            }

Posted in Algorithms & Data Structures | Comments Off on How to check if nested brackets are in the correct logical order

What package(s) do I need for EF Core 2.x?

First off, it is important to note that there are a couple of ways to download package(s).
1. You can use NuGet right from inside Visual Studio IDE
2. You can also use CLI (Command Line Interface)

Here, I’ll demonstrate both of them.

Even though you could install Microsoft.EntityFrameworkCore, however, you would need a database provider to interact with. Given, that EF Core is modular, and depending on the data-store you want to work with, you would only need to install that specific database provider.

Unlike EF 6.x, where SQL Server is the default database provider; that is not the case with EF Core. EF Core supports a variety of databases that include the following:

  • Microsoft SQL Server
  • SQLite
  • Postgres SQL
  • MySQL
  • SqlServerCompact (By Erik Jensen)
  • In-Memory Testing

When using CLI, you would want to install the following by navigating to Tools – NuGet Package Manager – Package Manager Console.

c:\Install-Package Microsoft.EntityFrameworkCore.SqlServer

If you wish to create a database using Migrations from your POCO classes or what is typically referred as Code-First workflow, you would need to install the following from the command line:

c:\Install-Package Microsoft.EntityFramework.Tools

You might also need Design tools in the event you want to create Controllers and Views using the Scaffolding feature of ASP.NET Core MVC. This can be obtained by installing the following:

c:\Install-Package Microsoft.VisualStudio.Web.CodeGeneration.Design

In the event where you want to use Visual Studio’s IDE, you can simply navigate to Tools – NuGetPackageManager – Manage NuGet Packages for Solution.

Once, the NuGet Window opens up, you can type in the appropriate package in the Search text field which will give you a list of stable packages unless you check the Include Prerelease check box to the right of the search field.

That’s all there is to it!

Posted in ORM | Comments Off on What package(s) do I need for EF Core 2.x?

Mongo DB and .NET

This is my last blog post of the year 2017 where I am going to showcase how you can make the .NET application talk to a Mongo DB.

Just a quick recap…MongoDB is a document database that stores data in flexible, JSON-like documents, meaning fields can vary from document to document and data structures can be changed over time.

If you want to know about Mongo DB, what it is and why you should use it, you can read the article from one of my earlier posts here.

First things first, install the following:

  • Robo 3T from www.robomongo.org

Robo 3T (formerly Robomongo) is the free lightweight GUI embedded shell for MongoDB users.

Launch Visual Studio and create a Console App (.NET Framework).

Now install the following packages from NuGet:

  • MongoDB.Bson
  • MongoDB.Driver
  • MongoDB.Driver.Core

Packages

It’s time to write some code, but before that, add references to the project and use the following namespaces:

using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
using MongoDB.Driver;
using System;
using System.Linq;

Let’s create a POCO class and call it Book. This is done in order for the document model to map to the objects in the application code therefore making data easy to work with.

    public class Book
    {
        public  ObjectId Id { get; set; }
        public string ISBN { get; set; }
        public string Title { get; set; }
        public string Author { get; set; }
        public DateTime Published { get; set; }
        public string Publisher { get; set; }
    }

Let’s insert a record into the book collection like so:

private static void InsertBook()
        {
            var client = new MongoDB.Driver.MongoClient();
            var db = client.GetDatabase("admin");
            var col = db.GetCollection("Books");

            Book objBook = new Book();
            objBook.Id = ObjectId.GenerateNewId();
            objBook.ISBN = "593220";
            objBook.Title = "Debgging Mongo App";
            objBook.Author = "Obi Oberoi, Ken Cenerelli";
            objBook.Published = Convert.ToDateTime("2017-12-31T00:00:00Z");
            objBook.Publisher = "Wrox";

            col.InsertOne(objBook);
        }

To get a book collection, use the code below:

private static void GetAllBooks()
        {
            var client = new MongoDB.Driver.MongoClient();
            var db = client.GetDatabase("admin");
            var col = db.GetCollection("Books");

            foreach(Book doc in col.Find(b=> b.Title.Length > 0)
                    .SortByDescending(b=> b.Title)
                    .ToList())
            {                
                Console.WriteLine("BookID: {0}", doc.Id);
                Console.WriteLine("Title: {0}", doc.Title);
                Console.WriteLine("Author: {0}", doc.Author);
            }
            Console.ReadLine();
        }

Let’s take our code to test by calling the methods from the Main program:

static void Main(string[] args)
        {         
            InsertBook();
            GetAllBooks();            
        }

As you saw in just a few lines of code, one could easily perform CRUD operations against MongoDB with a CLR compliant language such as C#.

Posted in NoSQL | Comments Off on Mongo DB and .NET

What is the difference between Delete and Truncate

To order essay on an author of the essay not only that but safely. Our exchange provides a number of obligations, which guarantee customers that they will receive a high quality and decent work:

each essay is checked by the system Antiplagiat, which ensure that the text was unique;

you made your payment remains “frozen” for the author until the time the student will receive an essay on the order in hand;

real comments and feedback about each expert help to navigate, who to approach and what results to expect.

If you need to order essay on any subject, be sure to contact author of essay on here will execute any order with pleasure and in the shortest time will help you to come to the school with the custom essay uk and best hand.

Other types of work

To the exchange author of essay each day addresses a lot of clients that need professional help. Here  who can do the work of proofreading efficiently and competently. author of essay has such features as:

It employs a lot of specialists who work with different languages. The staff is constantly to big this allows us to assist a lot more often;

The order is carried out only by experienced professionals who have diploma in this field or are the academic staff of the Universities. They thoroughly know their work and can carry out proofreading of articles;

The cost of proofreading text

Exchange author of essay can perform all sorts of work. Proofreading articles, research papers and other assignments is in accordance with all requirements and regulations. As for the cost of proofreading the texts, it may depend on:

Scope of work;

The complexity of the subject;

Deadlines.

For urgent execution, the contractor takes a percentage of the work, as it delays other work and perform an urgent task.

Posted in ORM | Comments Off on What is the difference between Delete and Truncate