Here is my presentation about database modeling on SQL Saturday #738 that was held in Petah Tiqwa, Israel at April 26, 2018.
Database modeling in practice
Adding Web API Controller To Existing MVC Project
If you have MVC project and you need to add Web API controller to this project, it can be done very easy.
1. Add Nuget package Microsoft.AspNet.WebApi.Core. This will add references to System.Web.Http.dll.
2. Add registration file for Web API. It would better to do it like Visual Studio does – add file App_Start\WebApiConfig.cs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
using System.Web.Http; namespace WebApplication { public static class WebApiConfig { public static void Register(HttpConfiguration config) { config.MapHttpAttributeRoutes(); config.Routes.MapHttpRoute( name: "DefaultApi", routeTemplate: "api/{controller}/{id}", defaults: new { id = RouteParameter.Optional } ); } } } |
3. Add using System.Web.Http; to Global.asax.cs
4. Add GlobalConfiguration.Configure(WebApiConfig.Register); in Application_Start() method before registering MVC routes.
Application_Start() would be like this.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
using System.Web.Http; using System.Web.Mvc; using System.Web.Optimization; using System.Web.Routing; namespace WebApplication { public class MvcApplication : System.Web.HttpApplication { protected void Application_Start() { AreaRegistration.RegisterAllAreas(); FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters); GlobalConfiguration.Configure(WebApiConfig.Register); RouteConfig.RegisterRoutes(RouteTable.Routes); BundleConfig.RegisterBundles(BundleTable.Bundles); } } } |
5. Create a controller and derive it from System.Web.Http.ApiController
Renaming Database
Sometimes you need to rename a SQL Server database. Beside the database name itself, this name is usually a part of logical database file names, physical file names, maybe file groups.
To rename the database name you need:
- Bring database offline;
- Change paths to database files in system catalog;
- Rename physical file names using Windows Explorer;
- Bring database online;
- Change database name;
- Change logical file names (optionally).
Here is the script to do this. Be careful to put the correct logical database file names.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
-- 0. Remember logical file names and paths USE Old_database; GO SELECT file_id, name AS [Logical name], physical_name AS [File path] FROM sys.database_files; GO -- 1. Bring database offline USE [master]; GO ALTER DATABASE Old_database SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE Old_database SET OFFLINE; GO -- 2. Change paths to database files in system catalog ALTER DATABASE Old_database MODIFY FILE ( -- logical file name NAME = 'Old_database', -- new path FILENAME = 'C:\DATA\New_database.mdf' ); GO ALTER DATABASE Old_database MODIFY FILE ( -- logical file name NAME = 'Old_database_log', -- new path FILENAME = 'C:\DATA\New_database_log.ldf' ); GO -- 3. Rename physical file names using Windows Explorer PRINT 'RENAME FILES IN FOLDER MANUALLY!!!' GO -- 4. Bring database online ALTER DATABASE Old_database SET ONLINE; GO ALTER DATABASE Old_database SET MULTI_USER; GO -- 5. Change database name ALTER DATABASE Old_database MODIFY NAME = New_database; GO -- 6. Change logical file names ALTER DATABASE New_database MODIFY FILE ( -- old logical file name NAME = 'Old_database', -- new NEWNAME = 'New_database' ); GO ALTER DATABASE New_database MODIFY FILE ( -- old logical file name NAME = 'Old_database_log', -- new NEWNAME = 'New_database_log' ); GO |
Drop Filestream In SQL Server
I have a table in SQL Server where one column is placed in FilestreamĀ filegroup. Now I need to delete this column and all files on disk.
Just recall the process to implement Filestream in SQL Server database:
- Add Filestream filegroup;
- Add file to a newly created Filestream filegroup;
- Create table where column is marked as FILESTREAM and you pointedĀ FILESTREAM_ON to a Filestream group.
To drop Filestream you need to implement these steps in reverse order, but check Filestream columns, files and filegroups before.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Filestream columns SELECT SCHEMA_NAME(t.schema_id) AS [schema], t.[name] AS [table], c.[name] AS [column], TYPE_NAME(c.user_type_id) AS [column_type] FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE t.filestream_data_space_id IS NOT NULL AND c.is_filestream = 1 ORDER BY 1, 2, 3; -- Filestream files and filegroups SELECT f.[name] AS [file_name], f.physical_name AS [file_path], fg.[name] AS [filegroup_name] FROM sys.database_files f JOIN sys.filegroups fg ON f.data_space_id = fg.data_space_id WHERE f.[type] = 2 ORDER BY 1; |
Then do the following:
1 2 3 4 |
ALTER TABLE [FilestreamTable] DROP COLUMN [FilestreamColumn]; ALTER TABLE [FilestreamTable] SET (FILESTREAM_ON="NULL") ALTER DATABASE [FilestreamDatabase] REMOVE FILE [FilestreamFile]; ALTER DATABASE [FilestreamDatabase] REMOVE FILEGROUP [FilestreamFilegroup]; |
Installing DLL Into GAC Using Powershell
To install DLL into Global Assembly Cache (GAC) you need Visual Studio or SDK to run gacutil.exe. It’s not convenient when you need to deploy ASP.NET application in IIS. This time you can do this with Powershell as described in TechNet articleĀ How to Install a DLL to the GAC on Windows Server 2012 Using Only PowerShell by Muhammad Khalid Latif.
1 2 3 4 |
Set-location "c:\deployment folder" [System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a") $publish = New-Object System.EnterpriseServices.Internal.Publish $publish.GacInstall("c:\deployment folder\MyDLL.dll") |
Line Number In C# Program
I have a method in ASP.NET application that works good but sometimes it fails. I need some kind of error logging or tracing. If you don’t want to write stupid messages everywhere in your code, you have some possibilities.
In debug mode:
1. Embrace your code in try-catch block. Compile the application in debug mode and place .pdb file along with .exe file. When the error is occurred, exception will show the line number.
2. You can write trace info manually as described by Scott Hanselman inĀ Getting the Line Number and File Name from C#.
1 2 3 4 5 6 7 8 9 |
using System.Diagnostics; static void WriteMessage(string message) { StackFrame callStack = new StackFrame(1, true); Console.WriteLine(message + " at line " + callStack.GetFileLineNumber() + " (" + callStack.GetMethod().Name + ")"); } |
.pdb file is necessary here, or you’ll see 0 instead of a line number.
In release mode:
Thanks to Marc Gravell https://stackoverflow.com/a/14122771, but you need .NET Framework 4.5 or higher here.
1 2 3 4 5 6 7 8 9 |
using System.Runtime.CompilerServices; static void WriteMessage(string message, [CallerLineNumber] int lineNumber = 0, [CallerMemberName] string caller = null) { Console.WriteLine(message + " at line " + lineNumber + " (" + caller + ")"); } |
SQL Scalar Functions – MIN of Multiple Values
SQL Server built-in function MIN() is an aggregate function. It can find minimal value on a single column from many rows.
To find minimum of multiple columns you can write UDF or use a subquery.
Important! My examples works fine even with nullable values, i.e. they ignore null values and return a minimum only from concrete values. If all values are null, they return null.
1. Scalar function for two values
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE FUNCTION [dbo].[Min2] ( @a int, @b int ) RETURNS int AS BEGIN RETURN CASE WHEN @a < @b THEN @a ELSE ISNULL(@b, @a) END END GO |
2. Scalar function for three values
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE FUNCTION [dbo].[Min3] ( @a int, @b int, @c int ) RETURNS INT AS BEGIN RETURN CASE WHEN @a < @b THEN CASE WHEN @a < @c THEN @a ELSE COALESCE(@c, @a) END WHEN @b < @c THEN @b ELSE CASE WHEN @a < @c THEN @a ELSE COALESCE(@c, @b, @a) END END END GO |
It can be quite hard to write a function for more input values. You can use a subquery here.
1 2 3 4 5 |
DECLARE @a int = 1, @b int = 2, @c int = null; SELECT 'Subquery', MinValue = (SELECT MIN(x) FROM (VALUES (@a), (@b), (@c)) AS n(x)); |
Shrinking VHD
To shrink VHD file occupied by virtual Windows OS you need to shrink the volume (i.e. disk C:) inside virtual machine and physical VHD file on host machine. It works fine when you don’t use differential VHDs.
Inside virtual Windows OS run command prompt:
1 2 3 4 5 |
diskpart list volume select volume # shrink desired=size_in_megabytes_to_get_from_OS exit |
Then shut down virtual machine.
On host Windows OS run command prompt:
1 2 3 4 5 6 |
diskpart select vdisk file="path_to_vhd" attach vdisk readonly compact vdisk detach vdisk exit |
Truncating DateTime In SQL Server
Sometimes we need to get only date from a datetime value, or truncate time to an hour or minute in SQL Server. There are some ways to do this:
- casting;
- manipulating with string conversion;
- working on total number of years/months/days/hours in DateTime value.
Casting works fine for truncating to date.
1 2 |
-- truncate to day SELECT CAST(@now AS date); |
You can convert datetime value to string (cast to nvarchar type), get a substring, and convert back to datetime. But it’s not efficient and can depend on string format (lond or short date format) or locale.
Another way is to calculate the number of years/months, etc. from a special date (DATEDIFF), then add this number of years/months to that special date (DATEADD).
1 2 3 4 5 6 |
-- truncate to month SELECT DATEADD(month, DATEDIFF(month, 0, @now), 0); -- truncate to hour SELECT DATEADD(hour, DATEDIFF(hour, 0, @now), 0); -- truncate to minute SELECT DATEADD(minute, DATEDIFF(minute, 0, @now), 0); |
Zero means the January 1, 1900.
Dynamic SQL Queries In SQL Server
SQL Server allows to execute SQL code from a variable. The simple way is to use EXEC command.
1 2 3 |
DECLARE @query_text nvarchar(max); SET @query_text = N'SELECT * FROM Clients'; EXEC (@query_text); |
The second way is to use sp_executesql stored procedure. It even allows to run code with parameters.
1. Simple query
1 2 3 4 5 6 |
SET @sql_text = N' SELECT * FROM Clients WHERE CountryCode = ''US'' '; EXEC sp_executesql @sql_text; |
Don’t forget to change single quote (‘) with two single quotes inside a string.
2. Query with a parameter
You need to pass a list of parameters to be used inside a dynamic SQL query and actual values for those parameters.
1 2 3 4 5 6 7 8 9 10 |
SET @sql_text = N' SELECT * FROM Clients WHERE CountryCode = @CountryCode '; SET @param_list = N' @CountryCode char(2) '; EXEC sp_executesql @sql_text, @param_list, @code; |
Note that name of the parameter inside the query and the name of variable with actual value could be different.
3. Query with output parameter
1 2 3 4 5 6 7 8 9 10 11 12 |
SET @sql_text = N' SELECT @ClientCount = COUNT(*) FROM Clients WHERE CountryCode = @CountryCode '; SET @param_list = N' @CountryCode char(2), @ClientCount int OUTPUT '; EXEC sp_executesql @sql_text, @param_list, @code, @TotalClients OUTPUT; |
4. Query with table-valued parameter
The rules of the SQL Server are the same: we need to create table-valued type and use a readonly parameter.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TYPE CodeOfCountry AS TABLE ( Code char(2) not null ); SET @sql_text = N' SELECT * FROM Clients c JOIN @CountryList cl ON c.CountryCode = cl.Code '; SET @param_list = N' @CountryList CodeOfCountry READONLY '; EXEC sp_executesql @sql_text, @param_list, @CodeList; |
5. Temporary tables with dynamic SQL
SQL Server allows only to use temp tables that were created before running dynamic SQL code. If you create a temp table during dynamic query, it would be inaccessible after that query.
Important! You can pass more parameters to sp_executesql procedure than is really used in dynamic query. This can be very helpful when you need to dynamically add joins or where clauses to a query. So you pass all the parameters, but consume just a part of them.
Also you can download full sample codeĀ Dynamic-SQL-queries.zip to play with it.