CODE GEN

using System.ComponentModel;

using System.IO;

using Microsoft.CSharp;

using System.CodeDom;

public class ClassGen<T>

    {

        public static void Generate()

        {

            Type type = typeof(T);

            PropertyDescriptorCollection properties =TypeDescriptor.GetProperties(typeof(T));

            string classtemplate =

                @" class {0} {{

                        {1}

                       }}

                 }}";

            string template = @"public {0}  {1} {{get; set;}}";

            string content = "";

            using (var provider = new CSharpCodeProvider())

            {

                foreach (PropertyDescriptor prop in properties)

                {

                    var typeRef = new CodeTypeReference(prop.PropertyType);

                    string typeName = provider.GetTypeOutput(typeRef);

                    content += string.Format(template, typeName, prop.Name) + "\n";

                }

            }

            Console.WriteLine(content);

            content = string.Format(classtemplate, type.Name, content);

            File.WriteAllText("../" + type.Name + ".cs", content);

}

ClassGen<A>.Generate();

ClassGen<B>.Generate();

SQL prime number

This is a solution for www.hackerrank.com
problem

Write a query to print all prime numbers less than or equal to . Print your result on a single line, and use the ampersand () character as your separator (instead of a space).

For example, the output for all prime numbers  would be:

2&3&5&7

declare @primes as varchar(max);
;
with strip as(
select  * from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) as X(n)  --tens
),
seed as(
select ROW_NUMBER() over(order by (select null)) as n from strip as A
cross join strip as B
cross join strip as C
)
,cte_prime as(
select cte_numerator.n as n1,cte_denominator.n as n2,  
(case cte_numerator.n % cte_denominator.n when 0 then 0 else 1 end) as mod  --1 if has remainder, 0 if its divisable
from seed as cte_numerator 
inner join seed as cte_denominator on cte_numerator.n > cte_denominator.n
)
 
,
res as(
select n from seed
where 1= ALL(select mod from cte_prime where n1=seed.n and n2<>1  ) --always has remainder!!
and seed.n > 1
)
SELECT @Primes = COALESCE(@Primes +'&','') +CAST(n as varchar(3)) from res
print @primes

Azure Blob Storage

Install nuget pakages

1. WindowsAzure.Storage
2. Microsoft.WindowsAzure.ConfigurationManager

here is the extract from packages.config

<packages>
  <package id="Microsoft.WindowsAzure.ConfigurationManager" 

           version="3.2.3" targetFramework="net40" />
  <package id="WindowsAzure.Storage" 

           version="8.1.1" targetFramework="net40" />
</packages>

web.config

<add key="storage:account:name" value="d8view"/>
<add key="storage:account:key" value="7o1-----A=="/>
<add key="storage:account:connection" value="DefaultEndpointsProtocol=https;AccountName=d8view;AccountKey=7o1-----==" />
<add key="storage:account:PDFContainer" value="patients"/>

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;

declare the CloudBlobClient!

 CloudBlobClient blobClient;

 public constructor()
 {
            // Retrieve storage account from connection string.
            CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
            ConfigurationManager.AppSettings["storage:account:connection"]);
            // Create the blob client.
            blobClient = storageAccount.CreateCloudBlobClient();
 }

Retrive the blob with policy url

public string GetAzureBlobReference(string strContainer, string prefix)
        {
            // Retrieve reference to a previously created container.
            CloudBlobContainer container 
                               = blobClient.GetContainerReference(strContainer);
            var readPolicy = new SharedAccessBlobPolicy()
            {
                Permissions = SharedAccessBlobPermissions.Read,
                SharedAccessExpiryTime = DateTime.UtcNow + TimeSpan.FromMinutes(5)
            };
            var docref = container.GetBlobReference(prefix);
            return  docref.Uri.AbsoluteUri 
                    + docref.GetSharedAccessSignature(readPolicy) };
        }

download as byte[]

//download document templete from Azurebolb
 public byte[] DownloadTemplate(string templateName)
 {
     byte[] templateInByteArray;
     try
     {
         //string containerName = "template";
         string fileName = templateName;
 
         var accountName = ConfigurationManager.AppSettings["storage:account:name"];
         var accountKey = ConfigurationManager.AppSettings["storage:account:key"];
         var containerName = ConfigurationManager.AppSettings["storage:WordTemplate:Location"];
 
         var storageAccount = new CloudStorageAccount(new StorageCredentials(accountName, accountKey), true);
         CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
 
         // Retrieve reference to a previously created container.
         CloudBlobContainer container = blobClient.GetContainerReference(containerName);
 
         // Retrieve reference to a blob
         CloudBlockBlob blockBlob = container.GetBlockBlobReference(fileName);
 
         using (MemoryStream ms = new MemoryStream())
         {
             blockBlob.DownloadToStream(ms);
             templateInByteArray = ms.ToArray();
         }
     }
     return templateInByteArray;
 }

upload blob

//update generated pdf Azurebolb
        public bool UploadPDFToAzureBlob(byte[] pdf, string fileName)
        {
            try
            {
                //string containerName = "generatedpdf";
 
                var accountName = ConfigurationManager.AppSettings["storage:account:name"];
                var accountKey = ConfigurationManager.AppSettings["storage:account:key"];
                var containerName = ConfigurationManager.AppSettings["storage:PDFExport:Location"];
 
                var storageAccount = new CloudStorageAccount(new StorageCredentials(accountName, accountKey), true);
 
                CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
 
                CloudBlobContainer container = blobClient.GetContainerReference(containerName);
 
 
                // Retrieve reference to a blob named "myblob".
                CloudBlockBlob blockBlob = container.GetBlockBlobReference(fileName);
                blockBlob.Properties.ContentType = "application/pdf";
                // Create or overwrite the "myblob" blob with contents from a local file.
                using (var stream = new MemoryStream(pdf, writable: false))
                {
                    blockBlob.UploadFromStream(stream);
                }
            }

            return true;
        }

Delete blob

var accountName = ConfigurationManager.AppSettings["storage:account:name"];
var accountKey = ConfigurationManager.AppSettings["storage:account:key"];
var containerName = ConfigurationManager.AppSettings["storage:PDFExport:Location"];
 
var storageAccount = new CloudStorageAccount(new StorageCredentials(accountName, accountKey), true);
var deleteFolderPath = patientId+"/"+"Episode" + episodeId;
 
CloudBlobContainer container = storageAccount.CreateCloudBlobClient().GetContainerReference(containerName);
var folderDelete = container.GetDirectoryReference(deleteFolderPath);
//((CloudBlob)folderDelete).DeleteIfExists(); 

Text Migration to SQL Server

In this article I will discuss some of the method used to transfer data from text file to sql server!

Part 1: Extract the data

First create the destination table structure in the SQL Server. This will be handy when generating mapping file

Bulk Copy Program bcp.exe

This will generate the mapping file as xml shown below

bcp.exe [dbmame].[dbo].[table]
format nul -f format.xml -x
-S .\sqlexpress -T -c

The generated format.xml mapping  file is shown below

...

 

  
  
 
 

  
  
 

...

T-SQL OPENROWSET (BULK ..) to read text file into SQL Server

• data.txt : data file
• error.txt : place-holder name for moving erroneous rows
• format.xml : mapping file discussed above

SELECT   *  FROM  OPENROWSET(BULK 'E:\..\data.txt',
ERRORFILE = 'E:\..\error.txt',
MAXERRORS = 100,
FORMATFILE = 'E:\..\format.xml' ) AS T

Unfortunately OPENROWSET does not accept parameters. So the only way is to create a dynamic sql string and execute using T-SQL sp_executesql  say for example when looping through folder of files

INSERT INTO dbo.Table..( Id, ...)
exec sp_executesql  @query

delemeting single qoute is tricky one

set @query  = 'SELECT  '+@recnumber+' AS recnumber,  *  FROM  OPENROWSET(BULK '   
   + '''' + @path +@fileName + ''''
      +', FORMATFILE = ' + ''''+ @formatfile + ''''    
      +' ) AS T' 

To read all the files in a particular folder use the extended stored procedure master.sys.xp_dirtree

CREATE TABLE #files (
    [filename] NVARCHAR(255),
    depth INT,
    [isfile] BIT
)

insert into #files exec
master.sys.xp_dirtree  @path,0,1

select * from #files

use this simple construct to loop through all files  using While

While ( (Select Count(*) From #files) >  0 )
Begin
    select Top 1 @fileName = [filename] From #files
    --your exec here..
    delete from #files where [filename]=@fileName

End

Part 2: Transform the data

sample data which need to be grouped and concatenated

RecNo Property Data
71594 URN 1317604
71594 MED Sertraline | ? | Daily
71594 SAV CALLAWAY, kelly | 08/08/2016 | 11:05
71594 SAV CALLAWAY, kelly | 08/08/2016 | 11:07
71594 SAV SINANAJ, jasmina | 08/08/2016 | 13:44

T-SQL for XML comes to rescue FOR XML PATH('')

 SELECT        RecNo, Property, Count(*) AS [Num],
 STUFF ((
                SELECT ', [' + Data +'] ' FROM  dbo.TABLE1 AS I
    WHERE I.RecNo=O.RecNo AND I.Property=O.Property
    FOR XML PATH('')
   ),1,1,'') Csv
 FROM Mig.MIGRN_TRIAGE AS O
 GROUP BY RecNo, Property

Now the result table looks like this

RecNo Property Data
71594 URN [1317604]
71594 MED [Sertraline | ? | Daily]
71594 SAV [CALLAWAY, kelly | 08/08/2016 | 11:05],[CALLAWAY, kelly | 08/08/2016 | 11:07]
,[SINANAJ, jasmina | 08/08/2016 | 13:44]

Use PIVOT to transform row data to columnar data

PIVOT_TABLE AS(

 select P.* from   (select RecNo, Property, Csv from dbo.TABLE1 )  as T
 PIVOT(
  Max( Csv)
  for [Property] in ([KID],[PRO],[MED],[SAV] )
  ) AS P
)

Part 3: Load the Data

Use the OLE DB drivers to load data into another SQL Server/Oracle ...

--EXEC sp_configure 'show advanced options', 1;
--RECONFIGURE;

--EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
--RECONFIGURE;

SELECT TOP 10  *  FROM
OPENROWSET('SQLNCLI'
,'Server=.\sqlexpress2016;Trusted_Connection=yes;database=dbname'
,'select * from [dbo].[table1]') AS A

SELECT TOP 10 *
FROM OPENDATASOURCE('SQLNCLI',
'Data Source=.\sqlexpress; Integrated Security=SSPI'
).[dbmame].[dbo].[table1]  

SharePoint: Query List within Time interval

SharePoint saves the time in current time zone it is specified in the regional setting!

When select a list item within specified time from a client machine using "Client Side Object Model"(CSOM), need to create the Datetime object in that (server)time zone. But C# creates the DateTime object with client Time zone

1. Get the server time zone

ClientContext context = new ClientContext(SiteUrl);

context.Credentials = new NetworkCredential("Administrator", "******", "DCE.local");

//context.Credentials = CredentialCache.DefaultCredentials;

//Get the server time zone

Microsoft.SharePoint.Client.TimeZone spTimeZone = context.Web.RegionalSettings.TimeZone;

context.Load(spTimeZone);

context.ExecuteQuery();

string fixedTimeZoneName = spTimeZone.Description.Replace("and", "&");

TimeZoneInfo serverZoneInfo = TimeZoneInfo.GetSystemTimeZones().FirstOrDefault(tz => tz.DisplayName == fixedTimeZoneName);

//var localTimeZone = TimeZoneInfo.Local;

2. Set time as midnight in server time zone

DateTimeOffset dtStartDateOff = new DateTimeOffset(DateTime.Parse(dtStartDate.ToShortDateString()),

                     +serverZoneInfo.GetUtcOffset(dtStartDate) ).ToUniversalTime();

DateTimeOffset dtEndDateOff = new DateTimeOffset(DateTime.Parse(dtEndDate.ToShortDateString()), 

                    +serverZoneInfo.GetUtcOffset(dtEndDate)).ToUniversalTime();

3. Create the CAML time with IncludeTimeValue='TRUE' StorageTZ='TRUE'

CamlQuery camlQuery = new CamlQuery();

string camlQueryString = @"<View><Query><Where><And> <Eq><FieldRef Name='Location' /><Value Type='Lookup'>{0}</Value></Eq> <And><Geq><FieldRef Name='PricingDate' /><Value IncludeTimeValue='TRUE' Type='DateTime' StorageTZ='TRUE' >{1}</Value></Geq> <Leq><FieldRef Name='PricingDate' /><Value IncludeTimeValue='TRUE' Type='DateTime' StorageTZ='TRUE'>{2}</Value></Leq> </And></And></Where> <OrderBy><FieldRef Name='PricingDate' Ascending='True' /></OrderBy></Query></View>";

string startDateFx = dtStartDateOff.ToString("yyyy-MM-ddTHH:mm:ssZ");

string endDatFx = dtEndDateOff.ToString("yyyy-MM-ddTHH:mm:ssZ");

camlQuery.ViewXml = string.Format(camlQueryString, location, startDateFx, endDatFx);

4. Execute CSOM

Web web = context.Web;

ListCollection lists = web.Lists;

List list = lists.GetByTitle(listName);     

ListItemCollection listItems = list.GetItems(camlQuery);

context.Load<ListItemCollection>(listItems);

context.ExecuteQuery();

int c = listItems.Count();

var priceListItems = listItems.ToList();

5. Return Date time will represent in client time zone. Adjust to show it server time-zone

var priceList2 = priceListItems.Select(i => new FuelPriceItem { 

PricingDate = DateTime.Parse(i["PricingDate"].ToString()), 

FuelPrice = double.Parse(i["FuelPrice"].ToString()) }).ToList();
          var priceList = priceList2.Select(i => new FuelPriceItem { 

PricingDate = TimeZoneInfo.ConvertTimeFromUtc(i.PricingDate, serverZoneInfo), 

FuelPrice = i.FuelPrice }).ToList();

         

             

Stored Procedure for Add Master and Detail

https://www.blogger.com/blogger.g?blogID=2984821582526309017#editor/target=post;postID=1068201323384154429

Convert a delimited string to a table in SQL

SQL Server
Convert a delimited string to a table using  Recursive Common Table Expression

declare @string as varchar(200),@start as int, @finish as int
set @string='Sinthiya,I,,Love,You,Very,Much,'+','

with cte(start,finish) as
(
  select 1 as start,CHARINDEX(',',@string) as finish
  union all 
  select cte.finish+1 as start, CHARINDEX(',',@string,cte.finish+1) as finish 
  from cte where cte.finish <>0
)
select  SUBSTRING(@string,cte.start,cte.finish-cte.start) from cte where cte.finish<>0