pvWay.MsSqlMultiPartVarChar.Core
1.0.0
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
<PackageReference Include="pvWay.MsSqlMultiPartVarChar.Core" Version="1.0.0" />
paket add pvWay.MsSqlMultiPartVarChar.Core --version 1.0.0
#r "nuget: pvWay.MsSqlMultiPartVarChar.Core, 1.0.0"
// 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.
Usage
Constructor
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(
retrievedMpText,
out var retrievedMpVarChar,
out var deserializationResult);
if (!deserializeSucceeded)
{
Console.WriteLine("it failed");
Console.WriteLine(deserializationResult);
// log and throw
}
else
{
// some ways to get the data
// using the Dicionnary
var enVal = retrievedMpVarChar.MpDic["en"];
Console.WriteLine(enVal);
// ==> displays "a nice text in English"
// using the GetPartForKey method
var frVal = retrievedMpVarChar.GetPartForKey("fr");
Console.WriteLine(frVal);
// ==> displays "un autre texte en français"
// using the TryGetPartForKey method
var deOk = retrievedMpVarChar.TryGetPartForKey("de", out var deVal);
Console.WriteLine(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)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
IF @str IS NULL
BEGIN
RETURN NULL;
END
/*
'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
BEGIN
SET @p1 = CHARINDEX('::', @str, 0);
SET @a = 0;
END
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;
END
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 Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
.NET Core | netcoreapp3.1 is compatible. |
-
.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.
Initial Version