pvWay.MsSqlMultiPartVarChar.Core 1.0.0

.NET Core 3.1
There is a newer version of this package available.
See the version list below for details.
dotnet add package pvWay.MsSqlMultiPartVarChar.Core --version 1.0.0
NuGet\Install-Package pvWay.MsSqlMultiPartVarChar.Core -Version 1.0.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="pvWay.MsSqlMultiPartVarChar.Core" Version="1.0.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add pvWay.MsSqlMultiPartVarChar.Core --version 1.0.0
#r "nuget: pvWay.MsSqlMultiPartVarChar.Core, 1.0.0"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install pvWay.MsSqlMultiPartVarChar.Core as a Cake Addin
#addin nuget:?package=pvWay.MsSqlMultiPartVarChar.Core&version=1.0.0

// Install pvWay.MsSqlMultiPartVarChar.Core as a Cake Tool
#tool nuget:?package=pvWay.MsSqlMultiPartVarChar.Core&version=1.0.0

Ms Sql MultiPart VarChar for .Net Core

Persists multi part text values (dictionary string - string) into one single VARCHAR column into an Ms SQL Db (2014 or >=).

The package contains a cSharp class for storing/retrieving the dictionary to/from the field and the SQL code for creating a scalar function for stored procedure implementation.



From the business layer of your applicaiton use the Dictionary constructor

	var dic = new Dictionary<string, string>()
		{"en", "a nice text in English"},
		{"fr", "un autre texte en français"}
	IMpVarChar myMpVarChar = new MpVarChar(dic);

Persisting into the Db

Now let's persist this value in one single NVARCHAR(MAX) into the Db.

The following example prepares a simple SQL statement for a DAO implementation of the DAL but of course you may want to use this with the ORM of your choice (EF, NHibernate...)

    // convert myMpVarChar to a string for insertion into the Db.
    var mpText = myMpVarChar.ToString(); 
    // hum yes... in this case we should make sure we escape the single quotes if any
    mpText = mpText.Replace("'", "''");

    // now we can use this var into an insert statement
    var insertStatement = $"INSERT INTO [dbo].[MyTable] ([MpText]) VALUES ('{mpText}');";
    // The line above will generate the following text
    // INSERT INTO [dbo].[MyTable] ([MpText]) VALUES ('en::a nice text in English::fr::un autre texte en français::');

    // for the simplicity i do not provide here the code executing this insert

The key value dictionnary is serialized to a single string that can be saved into the db into a VARCHAR(MAX) (or NVARCHAR(xxx)) column. Up to you to see if you need a MAX lenght or if a smaller column will do the job. the serialization cost is 4 char per dictionary entry. It takes the form '<key>::<value>::'. If the value of the key containst a ':' char it will be escaped with a '' char. This should also be taken into consideration for determining the final size of the string.

Retrieving the data from the Db

  // here above the SELECT code that populate the IDataRecord object 
  var ord = dataRecord.GetOrdinal("MpText");
  var retrievedMpText = dataRecord.GetString(ord); // let's retreive the raw text from the Db

  // time to deserialize
  var deserializeSucceeded = MpVarChar.TryDeserialize(
  	out var retrievedMpVarChar, 
  	out var deserializationResult);

  if (!deserializeSucceeded)
  	Console.WriteLine("it failed");
  	// log and throw
  	// some ways to get the data    
    // using the Dicionnary
    var enVal = retrievedMpVarChar.MpDic["en"];
	// ==> displays "a nice text in English"                
    // using the GetPartForKey method
    var frVal = retrievedMpVarChar.GetPartForKey("fr");
	// ==> displays "un autre texte en français"                
    // using the TryGetPartForKey method
    var deOk = retrievedMpVarChar.TryGetPartForKey("de", out var deVal);
	// ==> displays "a nice text in English" taking de first key in the dic as default value                

SQL side

Create a function that you can use from a Stored Procedure

  CREATE FUNCTION [dbo].[FnGetTranslation] 
      @str NVARCHAR(MAX),
      @lang VARCHAR(3)

      IF @str IS NULL
          RETURN NULL;

      'en::english text::fr::texte en français avec le caractère ''\:'' au milieu::nl::nederlandse tekst::'

      DECLARE @a INT = LEN(@lang);
      DECLARE @p1 INT = CHARINDEX(@lang + '::', @str, 0);

      IF @p1 = 0

          SET @p1 = CHARINDEX('::', @str, 0);
          SET @a = 0;


      DECLARE @p2 INT = CHARINDEX('::', @str, @p1 + @a + 2);
      DECLARE @len INT = @p2 - @p1 - @a - 2;
      DECLARE @s NVARCHAR(MAX) = SUBSTRING(@str, @p1 + @a + 2, @len);
      SET @s = REPLACE(@s, '\:', ':');
      RETURN @s;


Call the function from any SQL SELECT

  SELECT [dbo].[FnGetTranslation]([MpText], 'en')
  FROM [dbo].[MyTable]
  ORDER BY [dbo].[FnGetTranslation]([MpText], 'en')

Thanks for reading so far 😃

Product Versions
.NET net5.0 net5.0-windows net6.0 net6.0-android net6.0-ios net6.0-maccatalyst net6.0-macos net6.0-tvos net6.0-windows net7.0 net7.0-android net7.0-ios net7.0-maccatalyst net7.0-macos net7.0-tvos net7.0-windows
.NET Core netcoreapp3.1
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.
  • .NETCoreApp 3.1

    • No dependencies.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.0.1 1,076 6/7/2020
1.0.0 532 6/6/2020

Initial Version