In this blog, I will show you how to fix the following error:
“Reference to type ‘SqlConnection’ claims is defined in ‘System.Data’, but it could not be found”.

Are you anxious? Then go to “THE SOLUTION."

SPOILER
It’s a little bit painful but it can be solved.
You’ll be able to use .NET Framework Desktop App or ASP.NET inherit/use SqlConnection from netstandard2.

PROBLEM
If you are getting this message: “Reference to type ‘SqlConnection’ claims it is defined in ‘System.Data’, but it could not be found”; it is because you are trying to use the .NET Standard component directly from the .NET Framework (Desktop or ASP.NET).

Why is that you cannot use the object SqlConnection from .NET Framework hosted inside NetStandard 2.0?

ABOUT MY PROJECT
I do have an application, that is ASP.NET and WinForms, using .NET Framework 4.7.1. The core of the code is in assemblies that are shared between both, using System.Web and System.WebForms.

I enjoy migrating to new technologies to see how far they can go. And in this case, my hope was to use less memory on the server side.

I started using .NET Standard 1.6, but I didn’t feel comfortable to deploy it on production. And when 2.0 was released with the SQL Client, I imagined that it was the time. However, I didn't have time at that moment, so I started to migrate a month ago. Well, it took only two days.

I’ve decided to write this post because there isn't a good solution for my purpose. And, I made this on my own.

ATTACHED PROJECTS
I’ve attached two projects, one with the error and other with the error fixed, so you can analyze them both.

THE SOLUTION

STEPS
Create an intermediate library in .NET Standard 2.0 to open the Database Connection.
All the code to open connections must be in .NET Standard 2.0.

Install all the projects that use System.Data, the NuGet System.Data.SqlClient 4.5.1. or later.

Change all the code from .NET Framework libraries that use System.Data to the .NET Standard 2.0.
In the main program/ASP.NET, start the connection with this.
    using(var oCnn = Configurations.GetConnection())  
    {  
    …  
    }  

Why is this necessary?
If you don’t use “var”, your main code will make a reference to the System.Data on the current .NET Framework project. But with “var”, the compilation will refer to “netstandard2” System.Data.SqlClient at the compiling time.
That's all!

SAMPLE
See attached projects.

CONCLUSION
I always migrate to new technologies and make modern ways to do the same job more secure, better, faster, and modern to the end users. I calculate there will be a long time before the technology gets deprecated,  and I avoid using less than the 3rd version - I made an exception for this case, but only because I knew that the roadmap for .NET Standard 3.0 will be supporting WinForms. So I think it's good to refactor the codes to make it updated.
In these samples, I have used .NET Framework 4.7.1 and C# 7.2 (to use: “in” SqlConnection).

HostForLIFE.eu SQL Server 2019 Hosting

“One Gerbil, Two Gerbils or Three Gerbils?” is a common DBA joke about server and storage performance. No matter how many gerbils power your storage, you need to know what type they are and the power that they provide. Storage is not about gerbils it is about IOPs, bandwidth, latency, and tiers.

As a DBA it is important for you to understand and know what kind of storage is attached to your servers and how it is handling your data. It is not important to master everything about it, but it is very advantageous to be able to talk to your storage admins or “Gerbil CoLo, LLC” provider intelligently especially when you experience performance issues. Here is a list of things to I encourage you to know and ask.
 
Terminology
IOPs
IOPS stands for I/O (single read/write request) Operations Per Second. This is a performance metric that is dependent on the type of storage being used and can vary widely. It is important to understand how fast your storage can process data by knowing the expected IOPs and the actual IOPs once the array is processing workloads.
 
Bandwidth or Throughput
This is the measure of the size of the data in the I/O request. You can figure out throughput by taking I/O request size multiplied by the IOPs the Measure will be in Megabytes and Gigabytes per second.
 
Latency
In my opinion this is the most important metric to understand. It’s the time it takes to process that I/O request. Its an indicator of a possible storage bottleneck. You measure this time from when the request is issued to when the request is completed. This determines the responsiveness of your storage.
 
Storage Tier & Automatic Storage Tiers
A modern day array can be divided into tiers some of those tiers can be slower spinning disks while others can be fast flash or a hybrid of both. I think of these in terms of gerbils. You can get a small gerbil who has little legs that can run a marathon, a medium one that runs at a moderate speed 5k and a large gerbil that’s a speed racer. These together can work separately (pinned) or merged into a team like in a relay. Your data can be pass like a baton through each tier (automatic). Another words your data can be demoted or promoted between tiers of the storage device when needed for performance and compacity.
 
Performance Metrics
Note these apply to the Guest OS, there are metrics for the Hypervisor/Storage Stack that DBA’s do not normally have access to. The important part is that the different parts of the stack should mainly be in agreement about those numbers. If latency at the array side > latency at the Guest OS level, there is a big issue somewhere

• Avg. Disk sec/Read – Shows the average read latency.
• Avg. Disk sec/Write – Shows the average write latency.
• Avg. Disk sec/Transfer – Shows the combined averages for both read and writes.
• Disk Transfers/sec - is the rate of read and write operations on the disk.
• Disk Reads/sec - is the rate of read operations on the disk.
• Disk Writes/sec - is the rate of write operations on the disk.
• Avg. Disk Queue Length - is the average number of both read and write requests that were queued for the selected disk during the sample interval.
• Current Disk Queue Length - is the number of requests outstanding on the disk at the time the performance data is collected.

Storage Types
RAID (redundant array of independent disks)
 
RAID is a solution that protects your data from a disk failure. You tend to hear administrators talk in terms of RAID 0,1,5,6 and 10. As database administrators you need to know what RAID type your data is on. For Tempdb you want it on the fastest RAID as possible RAID 1 or 10 while maintaining disk fault tolerance. This is usually old SANs and no longer a concern with modern storage arrays. Modern arrays take a different approach with object-based storage models, which is more like the cloud.
 
FLASH
High speed storage based on non-volatile memory, you may see it referred to as NVMe, Non-volatile Memory Express. These are SSD, solid state drives. One thing to keep in mind is that NVMe’s are SSDs but not all SSDs are NVMe’s there are different types of SSDs. Not matter what type of SSD it is these are really great for Tempdb workloads.
 
Hyper-converged
This is referred to as HCI. Both the storage, networking and compute are bundled into one. This is a newest all in one hardware that claims to save money and creates ease of use. Keep in mind that these now means the HCI processing power is now handling everything (networking, storage, IOPS, etc).
 
Services
Snapshots
A capture of the state of your data is taken at a point in time. These snapshots can be used as restores or backup copies. These are usually snapshot copies of your mdf and ldf files. Note: Uncommitted transactions are not captured, and snapshots are not necessarily a replacement for backups. If your sysadmin asks about doing snapshots in lieu of backups, it’s your job ask a lot of hard questions to backup or storage vendor who is doing the snapshots and test both the back and more importantly the recovery. You need to be asking about to point in time recovery and how to handle page level restores for corruption just to name a couple.
 
Clones
A volume copy of your data, think of this a disk drive copy. It takes the files a makes a replica from snapshots creating a database copy.
 
Disk Replication (sync and async)
 
The replication of logical disk volumes from one array to another in real time (synchronous) or asynchronous for disaster recovery and continuity.
 
Summary
If you can educate yourself on these it will go a long way to being able to make sure you can have intelligent conversations with your storage admins or providers. This will enable you to better ensure you can advocate for your SQL environment when you experience performance issues related to storage. If your data is hosted elsewhere, like Gerbil Colo, LLC or even in a public cloud like Azure, make sure they can provide the above metrics to you. If they can’t, it might be time to host your data elsewhere.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article I am going to explain how to create and execute parameterized stored procedure from another stored procedure, how to prevent SQL Injection attacks, and how to insert data in the table using stored procedure in SQL Server with an example. And also I'll show you how you can use the procedure in SQL Server with an example.
 
When working with any data-driven application you need the database and you also need some data manipulation operations in DBMS (Database Management System) such as selection, insertion, updating, deletion and etc.
 
Your database may contain some useful and confidential information so it is necessary to protect it from any unauthorized activity and implement some security credentials with your DBMS (Database Management System) to secure your data from unauthorized activity.
 
If you're working with an SQL server then a stored procedure is one of the best options for data manipulation operations because a stored procedure accepts parameters as an argument, and parameterized statements prevent code injection techniques such as "SQL Injection" that might destroy your database.
 
So, here I will show you how you can use stored procedures in your SQL server database.
 
Requirements
Insert data in the table using a stored procedure.
Create a Stored Procedure in SQL Server.
Call or Execute Stored Procedure in SQL Server.
Call or Execute Stored Procedure From Another Stored Procedure in SQL Server.
Select and Display Inserted Data in Tabular Format in SQL Server Using Stored Procedure.

Before starting the actual implementation of our example we need a database for demonstration, so first we will create a database.
 
Create Database
    CREATE DATABASE db_europeanwindowshosting 

Before creating a stored procedure I will show you the syntax of the stored procedure in SQL Server.
 
Syntax
    CREATE PROCEDURE Your_Procedure_Name  
    -- list of parameters i.g: @Id INT = 0, @EmpName VARCHAR(50)=''  
    AS  
    BEGIN  
    -- SQL statements  
    END  

Now, we will start to write our stored procedure something like,
 
Create First Stored Procedure
    CREATE PROCEDURE Employee_Insert  
    @EmpId INT,   
    @EmpName VARCHAR(50),  
    @EmpDesignation  VARCHAR(50)  
    AS  
    BEGIN  
    -- Declare temporary table  
       
    DECLARE @Temp1 TABLE (EmployeeId INT, EmployeeName VARCHAR(50), EmployeeDesignation VARCHAR(50))  
       
    -- insert records in the temporary table  
       
    INSERT INTO @temp1 (EmployeeId, EmployeeName, EmployeeDesignation)  
         VALUES(1,'Peter','Software Engineer'),  
         (2,'Scott','Web Developer'),  
         (3,'Alexandria','Business Development Executive'),  
         (4,'Tom','Business Development Executive'),  
         (5,'Martin','Software Engineer')  
           
    -- Select all records from the temporary table      
    SELECT EmployeeId, EmployeeName, EmployeeDesignation FROM @Temp1  
    -- Select specific records from temporary table  
    SELECT EmployeeId, EmployeeName, EmployeeDesignation FROM @Temp1 WHERE EmployeeId = @EmpId AND EmployeeName = @EmpName AND EmployeeDesignation = @EmpDesignation  
       
    END  

If you analyzed the above procedure then you can see @EmpId, @EmpName, and @EmpDesignation where @ can be described as a parameter.
 
Now, I will show you how to create a second stored procedure and how you can insert data in the table using the first created stored procedure, and for that, I used a temporary table.
 
Create Second Stored Procedure
 
Now it's time to call/execute/create the stored procedure and insert data in table and also display the result in tabular format, for that here we will create another stored procedure and will call/execute the created stored procedure.

    CREATE PROCEDURE Employee_GetData  
    @Id INT,   
    @Name VARCHAR(50),  
    @Designation  VARCHAR(50)  
    AS  
    BEGIN  
    -- Execute First Procedure  
    EXECUTE Employee_Insert @EmpId = @Id , @EmpName = @Name, @EmpDesignation = @Designation  
    END  

Again if you analyze the above procedure then you can see our first created stored procedure is executed with "EXEC." You  can also use "EXECUTE", where @Id, @Name, and @Designation send parameters to the first stored procedure.

    --Execute Second Procedure By Passing Parameters  
    EXEC Employee_GetData @id=1, @name='Peter', @Designation ='Software Engineer'  

Now we will call/execute our second created procedure that will call/execute our first created procedure with the actual result as per our requirement.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this blog we will discuss how we can get primary column name of any table programmatically. However we can see primary column name by just right clicking on a table and see design view. But when we have dynamically create table and set primary key then might be we does not know the primary column name. So in this article we discuss about that.
 
In my database I have only one table named Employee, and you can see in the below image the primary key column is EmployeeId.
 
Get Primary Key Column In SQL Server
Here we use INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view so first we get what's inside that view.
 
Run the following queries to get the output of both views.
 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS   

Output

 
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
    select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE   

Output

 
Get Primary Key Column In SQL Server
 

Here are a few lines of sql query using which we can get the primary column name.
    select C.COLUMN_NAME FROM  
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T  
    JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C  
    ON C.CONSTRAINT_NAME=T.CONSTRAINT_NAME  
    WHERE  
    C.TABLE_NAME='Employee'  
    and T.CONSTRAINT_TYPE='PRIMARY KEY'   

Output

 
Explanation
    Here we join these two views, TABLE_CONSTRAINTS And CONSTRAINT_COLUMN_USAGE on CONSTRAINT_NAME.
    Then select those records where CONSTRAINT_COLUMN_USAGE.TABLE_NAME is Employee and TABLE_CONSTRAINTS.CONSTRAINT_TYPE is Primary Key.
    And then select CONSTRAINT_COLUMN_USAGE. COLUMN_NAME.

So that is just two or three lines of sql query for getting primary column name of any table. I hope you find some useful information in this article. If you find this helpful kindly share it with your friends.

HostForLIFEASP.NET SQL Server 2019 Hosting