Jaden  Haag

Jaden Haag

1635739200

Using SQL Server HierarchyId with Entity Framework Core

In this tutorial, we will learn how to generate HierarchyId with EF Core and SQL Server. ASP.NET Core Example.

#efcore  #sqlserver  

What is GEEK

Buddha Community

Using SQL Server HierarchyId with Entity Framework Core
Cayla  Erdman

Cayla Erdman

1594369800

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

Ray  Patel

Ray Patel

1625843760

Python Packages in SQL Server – Get Started with SQL Server Machine Learning Services

Introduction

When installing Machine Learning Services in SQL Server by default few Python Packages are installed. In this article, we will have a look on how to get those installed python package information.

Python Packages

When we choose Python as Machine Learning Service during installation, the following packages are installed in SQL Server,

  • revoscalepy – This Microsoft Python package is used for remote compute contexts, streaming, parallel execution of rx functions for data import and transformation, modeling, visualization, and analysis.
  • microsoftml – This is another Microsoft Python package which adds machine learning algorithms in Python.
  • Anaconda 4.2 – Anaconda is an opensource Python package

#machine learning #sql server #executing python in sql server #machine learning using python #machine learning with sql server #ml in sql server using python #python in sql server ml #python packages #python packages for machine learning services #sql server machine learning services

Brain  Crist

Brain Crist

1600347600

SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master

Introduction

This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below,

A glimpse of previous articles
Part 1

In part one, we learned the basics of data, database, database management system, and types of DBMS and SQL.

Part 2
  • We learned to create a database and maintain it using SQL statements.
  • Best practice methods were also mentioned.

#sql server #benefits of schemas #create schema in sql #database schemas #how to create schema in sql server #schemas #schemas in sql server #sql server schemas #what is schema in sql server

Einar  Hintz

Einar Hintz

1602560783

jQuery Ajax CRUD in ASP.NET Core MVC with Modal Popup

In this article, we’ll discuss how to use jQuery Ajax for ASP.NET Core MVC CRUD Operations using Bootstrap Modal. With jQuery Ajax, we can make HTTP request to controller action methods without reloading the entire page, like a single page application.

To demonstrate CRUD operations – insert, update, delete and retrieve, the project will be dealing with details of a normal bank transaction. GitHub repository for this demo project : https://bit.ly/33KTJAu.

Sub-topics discussed :

  • Form design for insert and update operation.
  • Display forms in modal popup dialog.
  • Form post using jQuery Ajax.
  • Implement MVC CRUD operations with jQuery Ajax.
  • Loading spinner in .NET Core MVC.
  • Prevent direct access to MVC action method.

Create ASP.NET Core MVC Project

In Visual Studio 2019, Go to File > New > Project (Ctrl + Shift + N).

From new project window, Select Asp.Net Core Web Application_._

Image showing how to create ASP.NET Core Web API project in Visual Studio.

Once you provide the project name and location. Select Web Application(Model-View-Controller) and uncheck HTTPS Configuration. Above steps will create a brand new ASP.NET Core MVC project.

Showing project template selection for .NET Core MVC.

Setup a Database

Let’s create a database for this application using Entity Framework Core. For that we’ve to install corresponding NuGet Packages. Right click on project from solution explorer, select Manage NuGet Packages_,_ From browse tab, install following 3 packages.

Showing list of NuGet Packages for Entity Framework Core

Now let’s define DB model class file – /Models/TransactionModel.cs.

public class TransactionModel
{
    [Key]
    public int TransactionId { get; set; }

    [Column(TypeName ="nvarchar(12)")]
    [DisplayName("Account Number")]
    [Required(ErrorMessage ="This Field is required.")]
    [MaxLength(12,ErrorMessage ="Maximum 12 characters only")]
    public string AccountNumber { get; set; }

    [Column(TypeName ="nvarchar(100)")]
    [DisplayName("Beneficiary Name")]
    [Required(ErrorMessage = "This Field is required.")]
    public string BeneficiaryName { get; set; }

    [Column(TypeName ="nvarchar(100)")]
    [DisplayName("Bank Name")]
    [Required(ErrorMessage = "This Field is required.")]
    public string BankName { get; set; }

    [Column(TypeName ="nvarchar(11)")]
    [DisplayName("SWIFT Code")]
    [Required(ErrorMessage = "This Field is required.")]
    [MaxLength(11)]
    public string SWIFTCode { get; set; }

    [DisplayName("Amount")]
    [Required(ErrorMessage = "This Field is required.")]
    public int Amount { get; set; }

    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
    public DateTime Date { get; set; }
}

C#Copy

Here we’ve defined model properties for the transaction with proper validation. Now let’s define  DbContextclass for EF Core.

#asp.net core article #asp.net core #add loading spinner in asp.net core #asp.net core crud without reloading #asp.net core jquery ajax form #asp.net core modal dialog #asp.net core mvc crud using jquery ajax #asp.net core mvc with jquery and ajax #asp.net core popup window #bootstrap modal popup in asp.net core mvc. bootstrap modal popup in asp.net core #delete and viewall in asp.net core #jquery ajax - insert #jquery ajax form post #modal popup dialog in asp.net core #no direct access action method #update #validation in modal popup

Madyson  Reilly

Madyson Reilly

1597006740

How to Use SQL Server HierarchyID Through Easy Examples

Do you still hold on to the parent/child design, or would like to try something new, like SQL Server hierarchyID? Well, it is really new because hierarchyID has been a part of SQL Server since 2008. Of course, the novelty itself is not a persuasive argument. But note that Microsoft added this feature to represent one-to-many relationships with multiple levels in a better way.

You may wonder what difference it makes and which benefits you get from using hierarchyID instead of the usual parent/child relationships. If you never explored this option, it might be surprising for you.

The truth is, I didn’t explore this option since it was released. However, when I finally did it, I found it a great innovation. It is a better-looking code, but it has much more in it. In this article, we are going to find out about all those excellent opportunities.

However, before we dive into the peculiarities of using SQL Server hierarchyID, let’s clarify its meaning and scope.

What is SQL Server HierarchyID?

SQL Server hierarchyID is a built-in data type designed to represent trees, which are the most common type of hierarchical data. Each item in a tree is called a node. In a table format, it is a row with a column of hierarchyID data type.

CodingSight - SQL Server HierarchyID

Usually, we demonstrate hierarchies using a table design. An ID column represents a node, and another column stands for the parent. With the SQL Server HierarchyID, we only need one column with a data type of hierarchyID.

When you query a table with a hierarchyID column, you see hexadecimal values. It is one of the visual images of a node. Another way is a string:

‘/’ stands for the root node;

‘/1/’, ‘/2/’, ‘/3/’ or ‘/n/’ stand for the children – direct descendants 1 to n;

‘/1/1/’ or ‘/1/2/’ are the “children of children – “grandchildren.” The string like ‘/1/2/’ means that the first child from the root has two children, which are, in their turn, two grandchildren of the root.

Here’s a sample of what it looks like:

CodingSight - A sample output showing nodes (1st column) and node strings (2nd column)Figure 1A sample output showing nodes (1st column) and node strings (2nd column)

Unlike other data types, hierarchyID columns can take advantage of built-in methods. For example, if you have a hierarchyID column named RankNode, you can have the following syntax:

RankNode..

SQL Server HierarchyID Methods

One of the available methods is IsDescendantOf. It returns 1 if the current node is a descendant of a hierarchyID value.

You can write code with this method similar to the one below:

SELECT
 r.RankNode
,r.Rank
FROM dbo.Ranks r
WHERE r.RankNode.IsDescendantOf(0x58) = 1

Other methods used with hierarchyID are the following:

  • GetRoot – the static method that returns the root of the tree.
  • GetDescendant  – returns a child node of a parent.
  • GetAncestor – returns a hierarchyID representing the nth ancestor of a given node.
  • GetLevel – returns an integer that represents the depth of the node.
  • ToString – returns the string with the logical representation of a node. ToString is called implicitly when the conversion from hierarchyID to the string type occurs.
  • GetReparentedValue – moves a node from the old parent to the new parent.
  • Parse – acts as the opposite of ToString. It converts the string view of a hierarchyID value to hexadecimal.

SQL Server HierarchyID Indexing Strategies

To ensure that queries for tables using hierarchyID run as fast as possible, you need to index the column. There are two indexing strategies:

DEPTH-FIRST

In a depth-first index, the subtree rows are closer to each other. It suits queries like finding a department, its subunits, and employees. Another example is a manager and its employees stored closer together.

In a table, you can implement a depth-first index by creating a clustered index for the nodes. Further, we perform one of our examples, just like that.

CodingSight - Depth-first indexing strategy. The Chief Engineer subtree is highlighted in the org. chart and the result set. The list is sorted based on each subtree.Figure 2Depth-first indexing strategy. The Chief Engineer subtree is highlighted in the org. chart and the result setThe list is sorted based on each subtree.

BREADTH-FIRST

In a breadth-first index, the same level’s rows are closer together. It suits queries like finding all the manager’s directly reporting employees. If most of the queries are similar to this, create a clustered index based on (1) level and (2) node.

CodingSight - Breadth-first indexing strategy. A portion of the second rank level is highlighted in the org. chart and the result set. The list is sorted based on the scale.Figure 3: _Breadth-first indexing strategy. A portion of the second rank level is highlighted in the org. chart and the result set. The list is sorted based on the scale. _

It depends on your requirements if you need a depth-first index, a breadth-first, or both. You need to balance between the importance of the queries type and the DML statements you execute on the table.

SQL Server HierarchyID Limitations

Unfortunately, using hierarchyID can’t resolve all issues:

  • SQL Server can’t guess what the child of a parent is. You have to define the tree in the table.
  • If you don’t use a unique constraint, the generated hierarchyID value won’t be unique. Handling this problem is the developer’s responsibility.
  • Relationships of a parent and child nodes are not enforced like a foreign key relationship. Hence, before deleting a node, query for any descendants existing.

Visualizing Hierarchies

Before we proceed, consider one more question. Looking at the result set with node strings, do you find the hierarchy visualizing hard for your eyes?

For me, it’s a big yes because I am not getting younger.

For this reason, we are going to use Power BI and Hierarchy Chart from Akvelon along with our database tables. They will help to display the hierarchy in an organizational chart. I hope it will make the job easier.

Now, let’s get down to business.

Uses of SQL Server HierarchyID

You can use HierarchyID with the following business scenarios:

  • Organizational structure
  • Folders, subfolders, and files
  • Tasks and subtasks in a project
  • Pages and subpages of a website
  • Geographical data with countries, regions, and cities

Even if your business scenario is similar to the above, and you rarely query across the hierarchy sections, you don’t need hierarchyID.

For example, your organization processes payrolls for employees. Do you need to access the subtree to process someone’s payroll? Not at all. However, if you process commissions of people in a multi-level marketing system, it can be different.

In this post, we use the portion of the organizational structure and the chain of command on a cruise ship. The structure was adapted from the organizational chart from here. Take a look at it in Figure 4 below:

CodingSight - Figure 4: Organizational structure of a typical cruise ship rendered using Power BI, Hierarchy Chart by Akvelon, and SQL Server. Names are made-up and do not relate to actual people and cruise ships.Figure 4Organizational structure of a typical cruise ship rendered using Power BI, Hierarchy Chart by Akvelon, and SQL Server. Names are made-up and do not relate to actual people and cruise ships.

Now you can visualize the hierarchy in question. We use the below tables throughout this post:

  • Vessels – is the table standing for the cruise ships’ list.
  • Ranks – is the table of crew ranks. There we establish hierarchies using the hierarchyID.
  • Crew – is the list of the crew of each vessel and their ranks.

The table structure of each case is as follows:

CREATE TABLE [dbo].[Vessel](
[VesselId] [int] IDENTITY(1,1) NOT NULL,
[VesselName] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Vessel] PRIMARY KEY CLUSTERED
(
[VesselId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Ranks](
[RankId] [int] IDENTITY(1,1) NOT NULL,
[Rank] [varchar](50) NOT NULL,
[RankNode] [hierarchyid] NOT NULL,
[RankLevel] [smallint] NOT NULL,
[ParentRankId] [int]   -- this is redundant but we will use this to compare        
                       -- with parent/child
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_RankId] ON [dbo].[Ranks]
(
[RankId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [IX_RankNode] ON [dbo].[Ranks]
(
[RankNode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Crew](
[CrewId] [int] IDENTITY(1,1) NOT NULL,
[CrewName] [varchar](50) NOT NULL,
[DateHired] [date] NOT NULL,
[RankId] [int] NOT NULL,
[VesselId] [int] NOT NULL,
 CONSTRAINT [PK_Crew] PRIMARY KEY CLUSTERED
(
[CrewId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Crew]  WITH CHECK ADD  CONSTRAINT [FK_Crew_Ranks] FOREIGN KEY([RankId])
REFERENCES [dbo].[Ranks] ([RankId])
GO

ALTER TABLE [dbo].[Crew] CHECK CONSTRAINT [FK_Crew_Ranks]
GO

ALTER TABLE [dbo].[Crew]  WITH CHECK ADD  CONSTRAINT [FK_Crew_Vessel] FOREIGN KEY([VesselId])
REFERENCES [dbo].[Vessel] ([VesselId])
GO

ALTER TABLE [dbo].[Crew] CHECK CONSTRAINT [FK_Crew_Vessel]
GO

#programming #sql server #how to #sql server #sql server hierarchyid #sql