Code tricks: May 2016

Monday, May 2, 2016

bulk insert csv in database

BULK INSERT SchoolsTemp
    FROM 'C:\CSVData\Schools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
    )

Importing csv in database using C#

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlBulkInsertExample
{
    class Program
    {
      static void Main(string[] args)
        {
            DataTable prodSalesData = new DataTable("ProductSalesData");

            // Create Column 1: SaleDate
            DataColumn dateColumn = new DataColumn();
            dateColumn.DataType = Type.GetType("System.DateTime");
            dateColumn.ColumnName = "SaleDate";

            // Create Column 2: ProductName
            DataColumn productNameColumn = new DataColumn();
            productNameColumn.ColumnName = "ProductName";

            // Create Column 3: TotalSales
            DataColumn totalSalesColumn = new DataColumn();
            totalSalesColumn.DataType = Type.GetType("System.Int32");
            totalSalesColumn.ColumnName = "TotalSales";

            // Add the columns to the ProductSalesData DataTable
            prodSalesData.Columns.Add(dateColumn);
            prodSalesData.Columns.Add(productNameColumn);
            prodSalesData.Columns.Add(totalSalesColumn);

            // Let's populate the datatable with our stats.
            // You can add as many rows as you want here!

            // Create a new row
            DataRow dailyProductSalesRow = prodSalesData.NewRow();
            dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
            dailyProductSalesRow["ProductName"] = "Nike";
            dailyProductSalesRow["TotalSales"] = 10;

            // Add the row to the ProductSalesData DataTable
            prodSalesData.Rows.Add(dailyProductSalesRow);

            // Copy the DataTable to SQL Server using SqlBulkCopy
            using (SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=dbProduct;Integrated Security=SSPI;Connection Timeout=60;Min Pool Size=2;Max Pool Size=20;"))
            {
                dbConnection.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = prodSalesData.TableName;

                    foreach (var column in prodSalesData.Columns)
                        s.ColumnMappings.Add(column.ToString(), column.ToString());

                    s.WriteToServer(prodSalesData);
                }
            }
        }
    }
}

Calling C# code from Sql procedure: Method 2

Take a look this TSQL example

USE [XXX] GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[Testing_XXX]
@broadcastId [int],
@XXXTemplateHtml [nvarchar](max),
@XXXTemplateText [nvarchar](max),
@XXXTemplateSubject [nvarchar](max),
@XXXTemplateEmailHeaders [nvarchar](max),
@XXXTemplateHeader [nvarchar](max),
@XXXTemplateFooter [nvarchar](max),
@masterTemplate [nvarchar](max),
@parseOptions [nvarchar](4000),
@xsltTemplate [nvarchar](max) OUTPUT WITH EXECUTE AS CALLER AS EXTERNAL NAME  SolutionXXX.ProjectXXX].[StoredProcedures].[XXX_Parser_Parse] GO

When you call

EXTERNAL NAME [SolutionXXX.ProjectXXX].[StoredProcedures].[XXX_Parser_Parse]

It invokes a C# function looks like this

 [SqlProcedure]
public static void XXX_Parser_Parse(
    SqlInt32 broadcastId,
    [SqlFacet(MaxSize = -1)] 
    SqlString XXXTemplateHtml,
    [SqlFacet(MaxSize = -1)] 
    SqlString XXXTemplateText,
    [SqlFacet(MaxSize = -1)] 
    SqlString XXXTemplateSubject,
    [SqlFacet(MaxSize = -1)] 
    SqlString XXXTemplateEmailHeaders,
    [SqlFacet(MaxSize = -1)] 
    SqlString XXXTemplateHeader,
    [SqlFacet(MaxSize = -1)] 
    SqlString XXXTemplateFooter,
    [SqlFacet(MaxSize = -1)] 
    SqlString masterTemplate,
    SqlString parseOptions,
    [SqlFacet(MaxSize = -1)] 
    out SqlString xsltTemplate)
{
//blah blah blh
}

Calling C# code from Sql procedure

CLR is abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by T-SQL or can use lots of resources. CLR can be usually implemented where there is intense string operation, thread management or iteration methods which can be complicated for T-SQL. Implementing CLR provides more security to Extended Stored Procedure.

Let us create one very simple CLR where we will print current system datetime.

1) Open Microsoft Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr1

2) Either choose from existing database connection as reference or click on Add New Reference. In my example I have selected Add New Reference.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr2

3) If you have selected existing reference skip to next step or add database reference as displayed in image.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr3

4) Once database reference is added following project will be displayed in Solution Explorer. Right click on Solution Explorer >> Click on Add >> Stored Procedure.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr4

5) Add new stored procedure template from following screen.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr5

6) Once template added it will look like following image.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr6

7) Now where it suggest to //Put your code here. Replace it with code displayed in the image. Once the code is complete do following two steps.
a) Click on menu bar >> Build >> Build ProjectName
b) Click on menu bar >> Build >> Deploy ProjectName
Building and Deploying project should give successful message.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr7

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure] public static void CLRSPTest()
{
SqlPipe sp;
sp = SqlContext.Pipe;
String strCurrentTime = “Current System DateTime is: “
+ System.DateTime.Now.ToString();
sp.Send(strCurrentTime);
}
};
8) Now open SQL Server Management Studio and run following script in Query Editor. It should return current system datetime. Running it again the time will change.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr8

USE AdventureWorks
GOEXEC dbo.CLRSPTest
GO

Event based throttler test

using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using CommonUtilities.Schedulers;

[TestClass]
public sealed class TimeboundThrottlerTest
{
[TestMethod]
public void Test_TimeboundThrottler_ctor_Success()
{
TimeboundThrottler validThrottler = new TimeboundThrottler(10);
Assert.IsNotNull(validThrottler);
}

[TestMethod]
[ExpectedException(typeof(ArgumentOutOfRangeException))]
public void Test_TimeboundThrottler_ctor_Failure()
{
TimeboundThrottler invalidThrottler = new TimeboundThrottler(0);
}

[TestMethod]
public void Test_TimeboundThrottler_Enqueue()
{
TimeboundThrottler throttler = new TimeboundThrottler(3);
int runningTasks = 0;
int completedTasks = 0;

List<Task> tasks = new List<Task>();
List<CancellationTokenSource> cancellationTokens = new List<CancellationTokenSource>();

for (int i = 0; i < 15; i++)
{
var cts = new CancellationTokenSource();
ChangeNotifier notifier = new ChangeNotifier();
notifier.StartStateChanged += (s, e) => { lock (this) { runningTasks++; } };
notifier.CompletedStateChanged += (s, e) => { lock (this) { completedTasks++; } };
cancellationTokens.Add(cts);
tasks.Add(throttler.Enqueue(() => TestFuncWithCancellationAsync(cts, notifier)));
}

// Initially verify that only 3 tasks are picked and rest of the tasks are waiting
Assert.AreEqual(3, runningTasks);
Assert.AreEqual(0, completedTasks);

Thread.Sleep(1100);
// Initially verify that 3 new tasks are picked and rest of the tasks are waiting
Assert.AreEqual(6, runningTasks);
Assert.AreEqual(0, completedTasks);

// Complete one task, still verify that only 3 tasks are picked in next 1sec
cancellationTokens[0].Cancel();
Thread.Sleep(1100);
Assert.AreEqual(9, runningTasks);
Assert.AreEqual(1, completedTasks);
}

[TestMethod]
public void Test_TimeboundThrottler_Enqueue_TaskwithResult()
{
TimeboundThrottler throttler = new TimeboundThrottler(3);
int runningTasks = 0;
int completedTasks = 0;

List<Task<CancellationToken>> tasks = new List<Task<CancellationToken>>();
List<CancellationTokenSource> cancellationTokens = new List<CancellationTokenSource>();

for (int i = 0; i < 15; i++)
{
var cts = new CancellationTokenSource();
ChangeNotifier notifier = new ChangeNotifier();
notifier.StartStateChanged += (s, e) => { lock (this) { runningTasks++; } };
notifier.CompletedStateChanged += (s, e) => { lock (this) { completedTasks++; } };
cancellationTokens.Add(cts);
tasks.Add(throttler.Enqueue(() => TestFuncWithCancellation2Async(cts, notifier)));
}

// Initially verify that only 3 tasks are picked and rest of the tasks are waiting
Assert.AreEqual(3, runningTasks);
Assert.AreEqual(0, completedTasks);

Thread.Sleep(1100);
// Initially verify that 3 new tasks are picked and rest of the tasks are waiting
Assert.AreEqual(6, runningTasks);
Assert.AreEqual(0, completedTasks);

// Complete one task, still verify that only 3 tasks are picked in next 1sec
cancellationTokens[0].Cancel();
Thread.Sleep(1100);
Assert.AreEqual(9, runningTasks);
Assert.AreEqual(1, completedTasks);
Assert.AreEqual(tasks[0].Result, cancellationTokens[0].Token);
}

private async Task TestFuncWithCancellationAsync(CancellationTokenSource cts, ChangeNotifier notifier)
{
notifier.IsStarted = true;
while (true)
{
if (cts.Token.IsCancellationRequested)
{
break;
}
await Task.Delay(1000);
}
notifier.IsCompleted = true;
}

private async Task<CancellationToken> TestFuncWithCancellation2Async(CancellationTokenSource cts, ChangeNotifier notifier)
{
notifier.IsStarted = true;
while (true)
{
if (cts.Token.IsCancellationRequested)
{
break;
}
await Task.Delay(1000);
}
notifier.IsCompleted = true;
return cts.Token;
}

public class ChangeNotifier
{
// Local data
private bool isStarted = false;
private bool isCompleted = false;

// Ctor to assign data
public ChangeNotifier() { this.isStarted = false; this.isCompleted = false; }

// The event that can be subscribed to
public event EventHandler StartStateChanged;
public event EventHandler CompletedStateChanged;

public bool IsStarted
{
get { return this.isStarted; }
set
{
// If the value has changed...
if (this.isStarted != value)
{
// Assign the new value to private storage
this.isStarted = value;

// And raise the event
if (this.StartStateChanged != null)
this.StartStateChanged(this, EventArgs.Empty);
}
}
}

public bool IsCompleted
{
get { return this.isCompleted; }
set
{
// If the value has changed...
if (this.isCompleted != value)
{
// Assign the new value to private storage
this.isCompleted = value;

// And raise the event
if (this.CompletedStateChanged != null)
this.CompletedStateChanged(this, EventArgs.Empty);
}
}
}
}
}

Concurrent throttler

using System;
using System.Threading.Tasks;
using Interfaces;

public class ConcurrentThrottler : IThrottler
{
private TaskQueue queue;
private int _concurrentRequests;

public int ConcurrentOperations
{
get
{
return _concurrentRequests;
}
}

public ConcurrentThrottler(int concurrentRequests)
{
Validator.IsPositive(concurrentRequests, CallerDetailsExtensions.GetMemberName(() => concurrentRequests));

this._concurrentRequests = concurrentRequests;
queue = new TaskQueue(concurrentRequests);
}

public Task<T> Enqueue<T>(Func<Task<T>> taskGenerator)
{
return queue.Enqueue(() => taskGenerator());
}

public Task Enqueue(Func<Task> taskGenerator)
{
return queue.Enqueue(() => taskGenerator());
}
}

Time based Throttler

Time based throttler;

using System;
using System.Threading.Tasks;
using Interfaces;

public class TimeboundThrottler : IThrottler
{
private TaskQueue queue;
private int _requestsPerSecond;

public int ConcurrentOperations
{
get
{
return _requestsPerSecond;
}
}

public TimeboundThrottler(int requestsPerSecond)
{
Validator.IsPositive(requestsPerSecond, CallerDetailsExtensions.GetMemberName(() => requestsPerSecond));

this._requestsPerSecond = requestsPerSecond;
queue = new TaskQueue(requestsPerSecond);
}

public Task<T> Enqueue<T>(Func<Task<T>> taskGenerator)
{
TaskCompletionSource<T> tcs = new TaskCompletionSource<T>();
var unused = queue.Enqueue(() =>
{
tcs.Match(taskGenerator());
return Task.Delay(TimeSpan.FromSeconds(1));
});
return tcs.Task;
}

public Task Enqueue(Func<Task> taskGenerator)
{
//TaskCompletionSource will attach source of Task to the edstination. So TaskCompletionSource is matched to the taskGenerator.
//when the function comes out of the queue, TaskCompletionSource will be responsible for calling the function taskGenerator which will enerate Task
TaskCompletionSource<bool> tcs = new TaskCompletionSource<bool>();
var unused = queue.Enqueue(() =>
{
tcs.Match(taskGenerator());
return Task.Delay(TimeSpan.FromSeconds(1));
});
return tcs.Task;
}
}