“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

This article shows how we can determine the size of an entire database using C# and the size of each and every table in the database using a single SQL command. To explain this, it is divided into three sections,

• Getting Size of one Table in the Database using single SQL Command
• Getting Size of each Table in the Database using single SQL Command
• Getting Size of the entire Database using C#

Getting Size of one Table in the Database using single SQL Command
To get the table size in SQL Server, we need to use the system stored procedure sp_spaceused. If we Table Name as an argument, it gives the disk space used by the table and some other information like: Number of rows existing in the table, Total amount of reserved space for Table, Total amount of space reserved for the table but not yet used and Total amount of space used by indexes in Table.

Example
For the ADDRESS table in our database, if we run,
sp_spaceused 'TADDRS'

It will give the following result,

Getting Size of each Table in the Database using single SQL Command

We have seen how we can determine the size of one table. Now, suppose we want to determine the size of each table in the entire database. We could find the size of any table using this command just by changing the Table name in the parameter. Is that right? But would it not be much better if we have a one-line SQL command that gives the size of each table? Right?
 
Fortunately, SQL Server provides a way to do this. A stored procedure "sp_MSforeachtable" could do easily for us!
 
The sp_MSforeachtable stored procedure is one of many undocumented stored procedures tucked away in the depths of SQL Server. You can find more details of "sp_MSforeachtable" in this link.
 
sp_MSforeachtable is an undocumented stored procedure that is not listed in MSDN Books Online and can be used to run a query against each table in the database. In short, you can use this as,
EXEC sp_MSforeachtable @command1="command to run"
 
In the "command to run" put a "?" , where you want the table name to be inserted. For example, to run the sp_spaceused stored procedure for each table in the database, we'd use,

It will give the size of each table (including other details) like,

Getting the Size of the entire Database
Now we want to get the total size used by a database i.e. the additional space used by each table in the database. We have seen in the previous section how we can get the size of each table in the database. Here is sample code in C# that can be used to calculate the size of the entire database.
 
In this, we are executing the same command what we discussed in the above section and are querying the database using simple ADO.Net. We get the result in a DataSet and then iterate each table to get its size. The table size is stored in the "data" column of each table (see the above Picture-1). We just add the "data" column value of each table to get the size of the entire database. Sample code is also attached with this article.
    class MemorySizeCalculator  
    {  
      public void GetDbSize()  
      {  
        int sum = 0;  
       
     // Database Connection String   
     string sConnectionString = "Server = .; Integrated Security = true; database = HKS";  
       
      // SQL Command [Same command discussed in section-B of this article]  
      string sSqlquery = "EXEC sp_MSforeachtable @command1=\"EXEC sp_spaceused '?'\" ";  
       
      DataSet oDataSet = new DataSet();  
       
      // Executing SQL Command using ADO.Net  
      using (SqlConnection oConn = new SqlConnection(sConnectionString))  
      {  
             oConn.Open();  
             using (SqlCommand oCmdGetData = new SqlCommand(sSqlquery, oConn))  
             {  
                  oCmdGetData.ExecuteNonQuery();  
                  SqlDataAdapter executeAdapter = new SqlDataAdapter(oCmdGetData);  
                  executeAdapter.Fill(oDataSet);  
             }  
             oConn.Close();  
      }  
      // Iterating each table  
      for (int i = 0; i < oDataSet.Tables.Count; i++)  
      {  
        // We want to add only "data" column value of each table  
        sum = sum + Convert.ToInt32(oDataSet.Tables[i].Rows[0]["data"].ToString().Replace    
        ("KB", "").Trim());  
      }  
        Console.WriteLine("Total size of the database is : " + sum + " KB");  
     }  
    } 

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

I have not found any direct way to call. So, we need to write our own logic inside a method provided by SignalR Library.
 

There is a Hub class provided by the SignalR library.
 
In this class, we have 2 methods.
    OnConnectedAsync()
    OnDisconnectedAsync(Exception exception)

So, the OnConnectedAsync() method will add user and OnDisconnectedAsyn will disconnect a user because when any client gets connected, this OnConnectedAsync method gets called.

In the same way, when any client gets disconnected, then the OnDisconnectedAsync method is called.
 
So, let us see it by example.
 
Step 1
Here, I am going to define a class SignalRHub and inherit the Hub class that provides virtual method and add the Context.ConnectionId: It is a unique id generated by the SignalR HubCallerContext class.
    public class SignalRHub : Hub  
    {  
    public override Task OnConnected()  
    {  
    ConnectedUser.Ids.Add(Context.ConnectionId);  
    return base.OnConnected();  
    }  
    public override Task OnDisconnected()  
    {  
    ConnectedUser.Ids.Remove(Context.ConnectionId);  
    return base.OnDisconnected();  
    }  
    }  

Step 2
In this step, we need to define our class ConnectedUser with property Id that is used to Add/Remove when any client gets connected or disconnected.
 
Let us see this with an example.
    public static class ConnectedUser  
    {  
    public static List<string> Ids = new List<string>();  
    }  

Now, you will get the result of currently connected client using ConnectedUser.Ids.Count.
 
As you see, here I am using a static class that will work fine when you have only one server, but when you will work on multiple servers, then it will not work as expected. In this case, you could use a cache server like Redis cache, SQL cache.

Today, I'm gonna show you how to sort numbers in SQL Server. It's not a difficult task but not an easy way. In the front end are many functions that for sorting values but SQL Server has no predefined function available.

For example I will sort the numbers 12,5,8,64,548,987,6542,4,285,11,26. SQL Server has no array list or array so how can we hold the values after sorting the numbers? SQL Server has temporary tables. Temporary automatically creates and drops the table after the execution.

First of all, create a temporary table. Suppose a problem occurs in SQL Server or during program execution. A Temporary table can't be deleted or dropped the proper way. When we want to create a table a second time a confirm error occurs as in the following:

There is already an object named '#temp' in the database.

So this type of problem is avoided by checking first if the table exists like this:

    IF  EXISTS (SELECT * FROM sys.tables 
    WHERE name = N'#temp' AND type = 'U') --check the #temp already exists in database or not  
    --Not:-  type U stand for user 
    begin 
    drop table #temp 
    end 

If the table already exists in the database then drop the table #temp.

My values are 12,5,8,64,548,987,6542,4,285,11,26. They need to be be split up before the sort. How can we split the numbers? Of course we can at the comma (,).  If I split the at the comma then I get the numbers like this: 12 5 8 64 548 and so on. One question then arises is, how to split the value? Don't worry, I have done that.

select left('12,45,18,95',

CHARINDEX(',','12,45,18,95')-1))

If i run this query it should be return the value is 12

After that everything is fine, we get the value from the #temp table.

    select ROW_NUMBER() over (order by value) 'srNo', value from #temp order by value

The following is a complete Stored Procedure to sort the numbers. 

   ALTER proc [dbo].[Porc_sortnumber] 
    as 
    begin 
    DECLARE @value VARCHAR(MAX)='1,2,5,6,12,88,47,95,56,20' 
    declare @lenth int =1 
    IF  EXISTS (SELECT * FROM sys.tables 
    WHERE name = N'#temp' AND type = 'U') --check the #temp allready exists in database or not  
    --Not:-  type U stand for user 
    begin 
    drop table #temp 
    end 
    create table #temp (id int identity(1,1),value int)  
    while(@lenth!=0 ) 
    begin 
    insert into #temp(value) values(left(@value,(CHARINDEX(',',@value)-1))) 
    set @value= right(@value,len(@value)-((CHARINDEX(',',@value)))) 
    set @lenth=CHARINDEX(',',@value) 
     
    end 
    insert into #temp(value) values(@value) 
    select ROW_NUMBER() over (order by value) 'srNo', value from #temp order by value 
    end 

Output

I hope this article will helpful!

HostForLIFE.eu SQL Server 2014 Hosting

HostForLIFE.eu is a popular online Windows and ASP.NET based hosting service provider catering to those people who face such issues. The company has managed to build a strong client base in a very short period of time. It is known for offering ultra-fast, fully-managed and secured services in the competitive market.

.NET 5 is the next version of .NET Core and the future of the .NET platform. With .NET 5 you have everything you need to build rich, interactive front end web UI and powerful backend services. .NET 5 contains great performance improvements in the runtime and libraries and for the gRPC components. These improvements, when applied to ASP.NET Core, result in some significant wins in throughput (RPS) and latency.

HostForLIFE.eu hosts its servers in top rate data centers that's located in Amsterdam (NL), London (UK), Washington, D.C. (US), Paris (France), Frankfurt (Germany), Chennai (India), Milan (Italy), Toronto (Canada) and São Paulo (Brazil) to ensure 99.9% network period. All data center feature redundancies in network connectivity, power, HVAC, security, and fire suppression. HostForLIFE.eu proudly announces available ASP.NET 5 feature for new customers and existing customers.

HostForLIFE.eu is a popular online Windows based hosting service provider catering to those people who face such issues. The company has managed to build a strong client base in a very short period of time. It is known for offering ultra-fast, fully-managed and secured services in the competitive market. Their powerful servers are especially optimized and ensure ASP.NET 5 performance. They have best data centers on three continent, unique account isolation for security, and 24/7 proactive uptime monitoring.

Further information and the full range of features ASP.NET 5 Hosting can be viewed here https://hostforlife.eu/European-ASPNET-5-Hosting.

You have a set of data, and you want to execute a set of queries on the records in a SQL table which are matching with this set of data. You have got a list of Product IDs from the QA department, you need to get the details of those products from the SQL table. And this situation happens frequently. How would you do this? What are the options you have?

Easy solution – You would write select query and put product ID in where clause and get the details. Do this for all the product IDs you have got. Its a time consuming task.
Is there any better way of achieving it where we can get the result in one query?
 
Solution
Yes, we can write a stored procedure, or you can just prepare a set of queries together to run for a single time also.
 
We will use temporary table variable to create arrays in SQL. We will insert the set of data (what you already have) into a temporary array variable. Use While clause, insert into clauses to generate our result table – which will show details of products matching product IDs.
 
Below is a sample set of queries which generates array variable named MYARRAY, you can store your data in this array. Then we will iterate through these array values using WHILE clause, we have used two variables to loop through array – INDEXVAR and TOTALCOUNT. As usual the loop will continue until INDEXVAR is smaller than TOTALCOUNT.
 
Using INDEXVAR and Where clause we will get current array index value. We will use this value to fetch data from actual table and insert it into our temporary result table PRODUCTDETAILSTABLE.
    Use DATABASENAME  
    GO  
      
    DECLARE @PRODUCTDETAILSTABLE table (PRODUCTNAME nvarchar(100), PRODUCTID int, PRODUCTCOST int)  
      
    -- Declare your array table variable  
    DECLARE @MYARRAY table (TEMPCOL nvarchar(50), ARRAYINDEX int identity(1,1) )  
      
    -- Add values to your array table, these are the values which you need to look for in your database  
    INSERT INTO @MYARRAY (TEMPCOL)  
       VALUES  
    ('PRD-2222'), ('PRD-3333'), ('PRD-4563'), ('PRD-4569'), ('PRD-6657'), ('PRD-3452'), ('PRD-6578')  
      
    --select * from @MYARRAY  
      
    DECLARE @INDEXVAR int  
    DECLARE @TOTALCOUNT int  
    DECLARE @CURINDEXEDPRODUCTID nvarchar (50)  
    SET @INDEXVAR = 0  
    SELECT @TOTALCOUNT= COUNT(*) FROM @MYARRAY  
    WHILE @INDEXVAR < @TOTALCOUNT  
    BEGIN  
        SELECT @INDEXVAR = @INDEXVAR + 1  
      
        -- Get value of current indexed product ID from array table  
        SELECT @CURINDEXEDPRODUCTID = TEMPCOL from @MYARRAY where ARRAYINDEX = @INDEXVAR  
      
        -- Get details of Product matching current indexed product ID from array  
        BEGIN  
            INSERT INTO @PRODUCTDETAILSTABLE (PRODUCTNAME, PRODUCTID, PRODUCTCOST)  
            (  
                select top 1 ProductName as PRODUCTNAME, ProductID as PRODUCTID, Cost as PRODUCTCOST  
                FROM  
                dbo.ProductDetails t  
                where t.ProductID= @CURINDEXEDPRODUCTID  
            )   
        END  
    END  
      
    Select * from @PRODUCTDETAILSTABLE  

You might come across a scenario where you need to update a set of records from your database or delete specific rows from your table – at that time you might need these array queries to fulfill your requirements. That’s it for this article. When someone asks you to pull data for specific list of IDs then I hope these queries help you to pull out data easily from the database.

This article will discuss alternative methods for performing cascading deletes using LINQ to SQL.  Cascading delete refers to the action of removing records associated by a foreign key relationship to a record that is the target of a deletion action.  LINQ to SQL does not specifically handle cascading deletes and it is up to the developer to determine whether or not that action is desired.  It is also up to the developer to determine how to go about accomplishing the cascading delete.
 
Problem
The problem with performing a cascading delete is not new to LINQ to SQL and one has essentially the same alternatives for performing such a delete.  The issue is one of determining how to handle the deletion or retention of records associated with a record targeted for deletion where that record maintains a foreign key relationship with records contained within other tables within the database and more specifically where the foreign key fields are not nullable.
 
As an example, consider the customer table within the Northwind database.  The customer table has a foreign key relationship established with the Orders table (which in turn maintains a foreign key relationship with the Order_Details table).  In order to delete a customer which has associated Orders, one needs to dispose of or otherwise handle the associated records in both the Orders and Order_Details tables.  In the LINQ to SQL jargon, the associated tables are referred to as entity sets.
 
LINQ to SQL will not violate the foreign key relationships and if an application attempts to delete a record with such relationships in place, the executing code will throw an exception.
 
Using the Northwind example, if one were to attempt to delete a customer with associated orders, an exception will occur.  That is not really a problem, that is how it should be, otherwise, why have foreign key relationships at all.  The issue is really one of determining if you would really want to delete records with associated entity sets, and if you do, how would you want to handle it - do you want to keep the associated records or delete them right along with the targeted record?

There are several possible alternatives at your disposal.  You can handle the cascading deletes using LINQ to SQL from within your code, or you can handle the foreign key relationships from within SQL Server.
 
If you were to execute this code against the Northwind database, it would create a customer with an associated order and order details.
    try  
    {  
              Customer c = new Customer();  
              c.CustomerID = "AAAAA";  
              c.Address = "554 Westwind Avenue";  
              c.City = "Wichita";  
              c.CompanyName = "HostForLIFE.eu";  
              c.ContactName = "Peter";  
              c.ContactTitle = "Boss";  
              c.Country = "USA";  
              c.Fax = "888-335-5933";  
              c.Phone = "888-225-4934";  
              c.PostalCode = "88888";  
              c.Region = "EA";  
       
              Order_Detail od = new Order_Detail();  
              od.Discount = .25f;  
              od.ProductID = 1;  
              od.Quantity = 25;  
              od.UnitPrice = 25.00M;  
       
              Order o = new Order();  
              o.Order_Details.Add(od);  
              o.Freight = 25.50M;  
              o.EmployeeID = 1;  
              o.CustomerID = "AAAAA";  
       
              c.Orders.Add(o);  
       
              using (NWindDataContext dc = new NWindDataContext())  
              {  
                       var table = dc.GetTable<Customer>();  
                       table.InsertOnSubmit(c);  
                       dc.SubmitChanges();  
              }  
    }  
    catch (Exception ex)  
    {  
              MessageBox.Show(ex.Message);  
    }

But if you then tried to delete the customer without handling the entity sets using something like this:
    using (NWindDataContext dc = new NWindDataContext())  
    {  
       
              var q =  
                       (from c in dc.GetTable<Customer>()  
                       where c.CustomerID == "AAAAA"  
                       select c).Single<Customer>();  
       
                       dc.GetTable<Customer>().DeleteOnSubmit(q);  
                       dc.SubmitChanges();  
    }

Handling the Delete with LINQ to SQL
You can handle the cascading deletes manually deleting all of the related entities in the associated entity set; here is a simple approach to doing that:
    try  
    {  
              using (NWindDataContext dc = new NWindDataContext())  
              {  
       
                       var q =  
                       (from c in dc.GetTable<Customer>()  
                       where c.CustomerID == "AAAAA"  
                       select c).Single<Customer>();  
       
                       foreach (Order ord in q.Orders)  
                       {  
                                 dc.GetTable<Order>().DeleteOnSubmit(ord);  
       
                                 foreach (Order_Detail od in ord.Order_Details)  
                                 {  
                                           dc.GetTable<Order_Detail>().DeleteOnSubmit(od);  
                                 }  
                        }  
                        dc.GetTable<Customer>().DeleteOnSubmit(q);  
                        dc.SubmitChanges();  
              }  
              UpdateDataGrid();  
    }  
    catch (Exception ex)  
    {  
              MessageBox.Show(ex.Message);  
    }

In looking at this example, to delete the customer along with the related orders and order details, the code first selects the matching customer by the customer ID field (it's the primary key).  Once a match is found, the code loops through the orders related to each customer and marks them for deletion using the DeleteOnSubmit call.
 
Further, since another relationship exists between the order and order details, the code loops through all of the order details associated with the order and marks them for deletion as well.  Lastly, the customer itself if marked for deletion and then Submit Changes is called on the data context.  The order in which the entities are marked for deletion does not matter, LINQ to SQL sorts that out during the execution of the Submit Changes call based upon the configuration of the foreign keys.

Handling the Cascading Delete from within SQL Server
It is possible to manage the cascading deletes entirely from within SQL Server.  To do this, one need only set the delete rule for the foreign key relationship to cascade.

If you have a database diagram built, the easiest way to set the delete rule is to merely open the diagram, click on the foreign key relationship within the diagram, and then open the INSERT and UPDATE property to expose the Delete Rule property, and then set the Delete Rule property to Cascade as shown in Figure 3.
 
To repeat the example of deleting a customer with related orders, if we were to set all of the constraint delete rules to cascade we could delete a customer with this bit of code:
    try  
    {  
              using (NWindDataContext dc = new NWindDataContext())  
              {  
                       var q =  
                       (from c in dc.GetTable<Customer>()  
                       where c.CustomerID == "AAAAA"  
                       select c).Single<Customer>();  
       
                       dc.GetTable<Customer>().DeleteOnSubmit(q);  
                       dc.SubmitChanges();  
              }  
              UpdateDataGrid();  
    }  
    catch (Exception ex)  
    {  
              MessageBox.Show(ex.Message);  
    }

As you can see in this example code, there was no need to bother with marking each member of an entity set for deletion as in this case SQL Server was instructed on how to deal with a deletion of the customer or order records.  As a result, deleting the customer also results in the deletion of the related records contained in the Order and Order Details tables.
 
Handling the Cascading Delete From within SQL Server
It is also possible to set the foreign key field in the entity sets to nullable and then set the delete rule for that field to "Set Null".  One could also set a default value for the field and set the delete rule to "Set Default".
 
Either approach could be useful if there was a need to delete (in this example) a customer record but retain the order and order detail records.  Either approach could be handled in a manner similar to that used in the previous solution alternative.  Setting the foreign key value to nullable is probably not advisable but it is a workable alternative.
 
Handling the Cascading Delete With a Stored Procedure
One may create or add a stored procedure that will accomplish the cascading delete and evoke that stored procedure using LINQ to SQL.  Stored procedures added to the designer may be evoked directly from the data context, for example, if we had stored procedure called DeleteCustomer that took the customer ID as an argument and handled the cascading deletes, we could do something like this:
    Using(NwindDataContext dc = new NwindDataContext())  
    {  
         dc.DeleteCustomer("AAAAA");  
    }

Cascading deletes are not new to LINQ to SQL; it is the same issue it has always been.  In this article, I have described a few approaches to dealing with cascading deletes from within the code and from the SQL Server side but as is true with many things in .NET, there are several other ways to accomplish such action from within LINQ to SQL.

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

MARS- Multiple Active Result Sets is a feature supported in sqlserver 2005. In this method having a single connection to the database, our applications can execute. Multiple queries and stored procedures and divides each result of a query or stored procedure as active results. These results are forward-only, read-only data. Previous to MARS, it was required multiple database connections to retrieve data for a separate query or stored procedure, but within a single connection to the database, it's possible to retrieve all data. This helps your application performance and code management greatly.

In the previous methods each sqlconnection object must be disposed of correctly and datareaders associated with each query must be disposed of. Otherwise, it will lead to some errors like "There is already an open DataReader associated with this Connection- error".

With the introduction of MARS in Sqlserver2005 bye-bye to all these problems and performance bottlenecks.

Let's start the Implementation

Settings

You must change your connection string as follows :

String connectionString = "Data Source=TestServer;" +"Initial Catalog=Pubs;IntegratedSecurity=SSPI;" + "MultipleActiveResultSets=True";

MARS is disabled by default on the Connection object. You have to enable it with the addition of MultipleActiveResultSets=true in your connection string.

Program:

This c# code snippet demonstrates the use of MARS.

using System.Data;  
using System.Data.SqlClient;  

public class MARSdemo {  
Public static void Main() {  
    String MarsConnectionStr = "Data Source=TestServer;" + "Initial Catalog=Pubs;IntegratedSecurity=SSPI;" + "MultipleActiveResultSets=True";  
    string Mars_Command_Str1 = "select * from Salary;";  
    string Mars_Command_Str2 = "select * from Employees;";  

    SqlCommand cmd1 = new SqlCommand(Mars_Command_Str1, MarsConnectionStr);  
    SqlCommand cmd2 = new SqlCommand(Mars_Command_Str1, MarsConnectionStr);  
    SqlDataReader rdr1 = null;  
    SqlDataReader rdr2 = null;  
    try {  
        con.Open();  
        rdr1 = cmd1.ExecuteReader();  
        rdr2 = cmd2.ExecuteReader();  
    } catch (SqlException ex) {} finally {  
        if (con.State == ConnectionState.Open) {  
            con.Close();  
        }  
        rdr1.Close();  
    }  
}  
}

If you try to execute this code with sqlserver 2000 or lower versions, u will get an InvalidOperationException and the following error message.

"There is already an open DataReader associated with this Command which must be closed first."

MARS works on the concept of "Multiplexing" and "interleaving". SQL's Select statement is a multiplexed enabled statement. These multiplexed enabled statements can pause its execution in between and allow a non-multiplexed statement to execute like an INSERT statement.

For example, consider you are retrieving 1 million records using a select statement and in between an insert query comes via a MARS-enabled application under the same connection object, then select statement pauses until this insert operation completes and afterward resumes execution.

Note: Managed code (stored procedures, functions, triggers) can't be multiplexed.

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.