Multilingual .NET Application using SQL Server

I was tasked with a project and a very small portion of it was to insert Multilingual Characters into the SQL Server database using C# .NET

Although it’s a simple enough task once I googled and got information from here and there.
Here is my compilation of Step by Step guide, Hope this is helpful.

First Let’s Setup the Database table

SQL Query for creating Table 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_MultiLang](
    [Lang] [nvarchar](50) NULL,
    [Data] [nvarchar](50) NULL
) ON [PRIMARY]

GO

Let's setup a simple table with two columns, First is "Lang" of nvarchar that will have only English characters  and the second Data of nvarchar this will host Multilingual characters.

SQL Queries
Queries for Insert into table 
Key here is having "N" before inserting the Multilingual character or string

INSERT INTO tbl_MultiLang (Lang, Data) values(N'English',N'Hello')
INSERT INTO tbl_MultiLang (Lang, Data) values(N'Urdu',N'ہیلو')

SQL Output
Queries for Insert into table

C# Code
Following is the C# Code, illustrating the INSERT statement from the C# Application.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

private void button1_Click_1(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("server=****;database=testdb;uid=****;password=****"); SqlCommand cmd; try { conn.Open(); cmd = new SqlCommand("INSERT INTO tbl_MultiLang (Lang, Data) values(N'"+ tboxLang.Text +"',N'"+ tboxData.Text +"')", conn); cmd.ExecuteNonQuery(); conn.Close(); } catch (Exception ex) { MessageBox.Show("Error ", ex.Message); } }

I haven't shown any read-back C# code from DB as it is straight-forward.

I found this article particularly interesting and also my Reference
http://installurdu.blogspot.sg/p/insert-urdu-characters-in-sql-server.html

How to attach the Start time of a Call in Genesys Routing strategy

It’s a good idea to have the start time of a call as attach data travel with the call for any further treatment or decision making.
For this example, we will be using three variables

Variable	Type
_date	String
_time	String
_startTime	String

Routing strategy blocks will look like this


Let’s Start assigning values to the variables Date and Time
For Date there is an inbuilt function that returns date in the Format MM/DD/YYYY


Similarly lets assign value to the Time variable, there is an inbuilt function for this as well.
Format for time is in 24 hour format hh:mm



The last block is to concatenate the date and time into one, this puts us at ease if we are interacting with external systems or database.

The code for Concatenation is :

Cat[_date,' ',_time]

Looking at the logs we can see the variable _startTime has the value of both Date and Time as intended.

Genesys Interaction Routing Designer – Using IRD with parameterized Stored Procedure

There comes a time when the Standard DB dips and query just isn’t enough and one requires to pass parameters to a Stored Procedure and get parameters as output.

Although the basic scenario of using a stored procedure is covered well in the Genesys Support Website but I my thoughts were getting multiple return parameters would be useful to know.

For this Post I will use the Scenario: a stored procedure that takes Email Address, NRIC and Phone Number as input in order to identify the customer and return the first and the last name, which can then be attached to User Data to be displayed on Workspace’s Case Data.

I will be using Genesys Framework 8.x and Microsoft SQL Server 2012 for this scenario.

Let’s start with creating a table and a stored procedure in SQL Server

SQL Server

Script for Table

       

CREATE TABLE [dbo].[newCustomer](
 [custID] [int] IDENTITY(1,1) NOT NULL,
 [Salutation] [nvarchar](20) NULL,
 [FirstName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NULL,
 [NRICorPassport] [nvarchar](20) NULL,
 [Address] [nvarchar](max) NULL,
 [DataofBirth] [nvarchar](20) NULL,
 [Telephone] [nvarchar](20) NULL,
 [Email] [nvarchar](80) NULL,
 [SecurityQuestion] [nvarchar](max) NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

 

Script for Stored Procedure

       
CREATE PROCEDURE dbo.sp_getFullName @NRIC nvarchar(30) = NULL, @PNo nvarchar(30) = NULL, @Email nvarchar(150) = NULL, @FName nvarchar(150) Output, @LName nvarchar(150) Output
AS
SELECT @FName = FirstName, @LName = LastName
FROM newCustomer
WHERE NRICorPassport = @NRIC OR Telephone = @PNo or Email = @Email
GO
 

Interaction Routing Designer (IRD)

Now the SQL part is completed, Let’s move to the Genesys IRD portion
Before we start the Genesys portion, Please ensure you have a valid DAP that points to the your database and the connection of the DAP in the Connections TAB of IRD Application and URS.

Input and Output Variables


Starting the Database Wizard, Select the DB Access Point
and in the “data access expression” Select Procedure


Enter the Name of the Stored Procedure and all input and output parameters


You can choose to Assign the value to a Variable or Attach the output as User Data to the call.
We will be Assigning the output to Variables.
Select “Assign each value to a variable when the output is a string of values ….”

;[

Last but not the least step of the Database Wizard is the specify the “Separator” (since out output is being return as a string and the Variables to assign the values to.



Once we have the value in Variables, we can attach those value to User Data and later use.



Output on Workspace

As seen below the two output parameters we received from the stored procedure

Log Analysis / Points of Interest

1- “Here is XDATA” This is the start of Database Block
2- The Actual Details being passed to SQL Server can be seen i.e. the name of stored procedure and all of the input and output parameters.
3- Output Parameters returned by the SQL Server
4- Assignment of the variables