Search Results

When a .NET DLL library depends on an ADO.NET  data provider, we can either reference the ADO.NET  provider in our DLL project or we can use reflection in C# to dynamically load the data provider and not reference the provider assembly. The project in which the DLL is being used may prefer using an Oracle provider, a MySQL provider or a SQL Server provider, and therefore, there is a need to dynamically use the preferred provider. The code below implements such a requirement. Alternate sample code that uses the Instance field to implement the above requirement

Here we show an example of how to use a dynamically generated query to insert rows from a source table into a target table. We are using table variables, but the approach would be the same if you were using permanent stored tables. Note that we are using a parameterised INSERT query to prevent SQL Injection. The data from the source table is ordered by ProductId when inserting rows into the target table. A dynamically generated INSERT query is executed for each row in the source table, which is then executed by using the system stored procedure sp_executesql . By using this system stored procedure to run a dynamic SQL, we prevent SQL Injection; in addition, it may also make the dynamic query more performant and efficient ( though this is not always true) compared to when we use EXECUTE @dynamicQuery or EXEC(@dynamicQuery) since the system stored procedure generates execution plans that it can reuse.

In SQL Server, if we try to INSERT rows using a SELECT ORDER BY statement as in the SQL mentioned below, then the inserted rows are not necessarily inserted in the same sequence as the rows returned by the SELECT ORDER BY statement. In other words, there is no use of an ORDER BY clause for the SELECT statement when inserting rows, and it's impossible to control the order of rows inserted. The only control a developer has is over the default ordering when a SELECT statement is used without any ORDER BY clause. This article explains how we control this default ordering. INSERT INTO table1 (ProductId, Product) SELECT ProductId, Product FROM table2 ORDER BY Product; SELECT ProductId, Product from table1; --this will not show rows ordered by Product, but ordered by the primary key or clustered index of table1 or in some unknown order if neither a primary key nor a clustered index exists Using Primary key or Clustered Index to achieve order of INSERTed rows The solution is to make the column on which you want to have a default order the primary key in the target table. Then if you insert using a SELECT statement the inserted rows in the target table would have the default ordering by that column. Below examples use table variables to illustrate the solution, but the same concepts apply equally well to temp tables or regular tables in SQL Server. DECLARE @table_source TABLE (ProductId INT ,Product VARCHAR(50)); INSERT INTO @table_source (ProductId, Product) VALUES (2, 'Blue Trousers'), (1, 'Red Trousers'), (7, 'Coral Sweater'), (3, 'White Shirt'), (4, 'Auburn Shirt'), (5, 'Blue Shirt'), (6, 'Red Shirt'); SELECT * FROM @table_source; DECLARE @table_target TABLE (ProductId INT ,Product VARCHAR(50) PRIMARY KEY); --the above definition will result in inserted rows to be stored in ascending order of Product --instead of specifying a primary key, you can create a clustered index --for the target table on the column by which you want to sort rows inserted, if the target table is not a table variable -- CREATE CLUSTERED INDEX IX_TargetTable_Product ON tableName (Product); INSERT INTO @table_target (ProductId, Product) SELECT * FROM @table_source; SELECT * FROM @table_target; --this will automatically return rows in ascending order of Product What if we wanted to change the default order to a descending order of Product rather than an ascending order. The solution is the same as above except where the target table is declared/defined. The target table definition statement would be changed to the following. DECLARE @table_target TABLE (ProductId INT ,Product VARCHAR(50) PRIMARY KEY CLUSTERED (Product DESC)); --instead of defining a primary key as above you could create a clustered index and get the same effect, provided the table is not a table variable --(table variable cannot have a clustered index created by the below statement) --CREATE CLUSTERED INDEX IX_TargetTable_Product ON tableName (Product DESC);

When you publish a library called YourLibrary.dll to the NuGet platform, then you don't need to worry about what version of your library should be used in a .NET app. In other words, the NuGet platform will automatically include the correct version of the DLL for the .NET app. For example, after publishing your library to the NuGet platform, we will end up with the following structure. Based on the available .NET versions of YourLibrary.dll and the .NET app's .NET version, the best possible YourLibrary.dll will be selected by NuGet. Selection Algorithm by NuGet 1. ✅ Exact match → Use it (e.g., .NET 8 app uses net8.0 DLL) 2. ✅ Compatible newer version → Use highest compatible (e.g., .NET 7 app uses net6.0 DLL) 3. ✅ Fall back to .NET Standard → Use netstandard2.0 DLL (e.g., .NET Core 2.1 app will use this version)

The .NET Landscape: Specifications vs Runtimes 📋 .NET Standard = Specification (Not a Runtime) .NET Standard 2.0 is a formal specification - it's like a contract that defines which APIs must be available. • ❌ You cannot run apps on .NET Standard • ✅ You can build libraries that target .NET Standard • 🎯 Purpose: Ensures your library works across multiple .NET runtimes Think of it like USB-C: it's a standard that ensures devices (runtimes) can work with cables (libraries) that follow the spec. 🏃 Runtimes = What Actually Executes Your Code The table below summarises the actual platforms that run .NET applications: .NET Runtime Comparison Table ( Note: .NET Framework 4.6.1 to 4.7.1 implemented .NET Standard 2.0 but only partially and hence cannot be used to develop .NET Standard 2.0 libraries ) (LTS - Long Term Support, STS - Short Term Support) Runtime Released Status Implements .NET Standard 2.0? .NET Framework 4.7.2+ 2018 Windows-only, maintenance mode ✅ Yes .NET Core 2.0 2017 Out of support ✅ Yes (first to do so) .NET Core 2.1 2018 Out of support ✅ Yes .NET Core 3.1 2019 Out of support (2022) ✅ Yes .NET 5 2020 Out of support ✅ Yes .NET 6 2021 LTS (until Nov 2024) ✅ Yes (but has its additional built-in APIs) .NET 7 2022 Out of support ✅ Yes .NET 8 2023 LTS (until Nov 2026) ✅ Yes (but has its additional built-in APIs) Why Does .NET Core 2.1 runtime Use Your .NET Standard 2.0 Library? Here's the magic of .NET Standard: • .NET Core 2.1 implements the .NET Standard 2.0 specification • It has all the APIs that .NET Standard 2.0 requires • Therefore, any library built for .NET Standard 2.0 will work for .NET Core 2.1 runtime The table below shows which runtimes can use a library build for .NET Standard 2.0. Traditional Windows Cross-Platform / Modern Runtime .NET Framework 4.7.2+ .NET Core 2.0 /.NET Core 2.1+ / .NET 5+ Apps supported Legacy apps, Windows-specific services, and ASP.NET (System.Web). Only apps in Windows are supported and not in Linux/macOS. High-performance, cloud-native, and cross-platform (Linux/macOS/Windows). Example of a cross-platform .NET library Assume that you have a project called YourLibrary that produces a DLL called YourLibrary.dll. In the project file of this library (i.e file with the name YourLibrary.csproj), the following specification needs to be included within the first PropertyGroup, if you are creating a cross-platform library. net8.0;net6.0;netstandard2.0 Now, when you Build YourLibrary project, you get three separate DLLs created by the build process as shown below. When a .NET Core 2.1 App References YourLibrary: .NET Core 2.1 App └── needs YourLibrary └── checks available DLLs: ├── net8.0/YourLibrary.dll ❌ (too new, not compatible) ├── net6.0/YourLibrary.dll ❌ (too new, not compatible) └── netstandard2.0/YourLibrary.dll ✅ (compatible!) Visual Timeline of .NET Evolution Why .NET Standard exists The Problem (Pre-.NET Standard): The Solution (.NET Standard)

Table variables can have a non-clustered or a clustered index. Further, an order can be specified for index column(s). Script to create a UNIQUE clustered index on a table variable (note you can create a UNIQUE or non-unique clustered index for a table variable) DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), UNIQUE CLUSTERED (Product)); --above will create an index in ascending order of Product --to create an index with a descending order of Product --use the script below (note: you can also use multiple columns as in --(Product DESC, ProductId ASC) when defining an index) --DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), -- UNIQUE CLUSTERED (Product DESC)); An aletrnate create script is as below. DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), INDEX IX1 UNIQUE CLUSTERED (Product DESC)); Script to create a Non-unique Clustered Index DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), INDEX IX1 CLUSTERED (Product ASC)); Script to create a NON-UNIQUE clustered index on a table variable DECLARE @table_target TABLE ( ProductId INT ,Product VARCHAR(50), INDEX IX1 CLUSTERED(Product)); If you define a primary key for a table variable, then automatically a clustered index is created for the primary key column(s). so this is another way to create a clustered index for a table variable. DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50) PRIMARY KEY); --an alternate script for creating primary key on one or more columns is as below ---DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), PRIMARY KEY (ProductId ASC, Product DESC)); Script to create a non-clustered index on a table variable (note that we do not need a UNIQUE constraint for such an index) DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), INDEX IX1 NONCLUSTERED (Product)); If you have a primary key (non-clustered index) and also a clustered index on another column then the default order of rows in SELECT statement will be decided by the primary key and not the clustered index. Keep in mind that only 1 clustered index can be created per table and it decides the storage order of the rows for that table.