Check Your Technoledge: 2015

Monday, November 16, 2015

How to use stored procedure with Entity Framework Code First

To learn about how we can use stored procedure with Entity framework code first, jalpesh vadgama has written a very good blog post here.

Thanks jalpesh vadgama.

Working with transaction in Entity Framework 6

In any Relation database, maintaining integrity of database is very important and Transaction is one way of maintaining database integrity. When you have situation, where you need to insert data into multiple tables and if inserting a data in one of the table fails you should always rollback other inserts transaction becomes very useful. Same scenario can be occurred for update or delete operations. If transactions are not there you will be end up with lots of junk data in tables. Entity framework is one of most popular ORM in Microsoft.NET World. So in this post we are going to learn how we can use transactions with Entity Framework 6.

Read full article here by jalpesh vadgama.

How doing a case sensitive search in SQL Server?

Question: How doing a case sensitive search in SQL Server?

Answer:

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1FROM Table1WHERE Column1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to be changed as follows.
SELECT Column1FROM Table1WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.
ALTER TABLE Table1ALTER COLUMN Column1 VARCHAR(20)COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.
EXEC sp_help DatabaseName

Second results set above script will return you collation of database DatabaseName.

Thanks @PinalDave for the beautiful post.

Thursday, October 8, 2015

Some useful c# code snippets for visual studio users

Public Class - code snippet

(Code snippet for public class)

Many times we, visual studio users, use common code snippets (already available with visual studio setup) like for, if, class, prop etc...

Out of these class is used many times, an every time we need to prepend Public with generated class declaration. So to automate this here I have created a custom C# code snippet that generates code for class declaration along with Public :

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
 <CodeSnippet Format="1.0.0">
  <Header>
   <Title>pclass</Title>
   <Shortcut>pclass</Shortcut>
   <Description>Code snippet for public class</Description>
   <Author>Microsoft Corporation</Author>
   <SnippetTypes>
    <SnippetType>Expansion</SnippetType>
    <SnippetType>SurroundsWith</SnippetType>
   </SnippetTypes>
  </Header>
  <Snippet>
   <Declarations>
    <Literal>
     <ID>name</ID>
     <ToolTip>Class name</ToolTip>
     <Default>MyClass</Default>
    </Literal>
   </Declarations>
   <Code Language="csharp"><![CDATA[public class $name$
 {
  $selected$$end$
 }]]>
   </Code>
  </Snippet>
 </CodeSnippet>
</CodeSnippets>

Copy above code and save into a file with name - "pclass.snippet" at "%USERPROFILE%\Documents\My Projects\Code Snippets\Visual C#\My C# Code Snippets". After restarting visual studio in any C# class file, type pclass + tab and see the difference.

Property with matching name - code snippet

(Code snippet for property and backing field with matching name)

One more code snippet - propfull we use many times, an every time we need to change Property name and private variable name with generated class declaration, though we use the same name for both with prepending underscore(_). So to automate this here I have created a custom C# code snippet:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
 <CodeSnippet Format="1.0.0">
  <Header>
   <Title>propmatchname</Title>
   <Shortcut>propmatchname</Shortcut>
   <Description>Code snippet for property and backing field with matching name</Description>
   <Author>Microsoft Corporation</Author>
   <SnippetTypes>
    <SnippetType>Expansion</SnippetType>
   </SnippetTypes>
  </Header>
  <Snippet>
   <Declarations>
    <Literal>
     <ID>type</ID>
     <ToolTip>Property type</ToolTip>
     <Default>int</Default>
    </Literal>
    <Literal>
     <ID>property</ID>
     <ToolTip>Property name</ToolTip>
     <Default>MyProperty</Default>
    </Literal>
   </Declarations>
   <Code Language="csharp"><![CDATA[private $type$ m_$property$;
 public $type$ $property$
 {
  get { return m_$property$;}
  set { m_$property$ = value;}
 }
 $end$]]>
   </Code>
  </Snippet>
 </CodeSnippet>
</CodeSnippets>

Copy above code and save into a file with name - "propmatchname.snippet" at "%USERPROFILE%\Documents\My Projects\Code Snippets\Visual C#\My C# Code Snippets". After restarting visual studio in any C# class file, type propmatchname + tab and see the difference. Now try to change either public property name OR private variable name. Both will be in sync.

Hope many visual studio users/developers love this.
Enjoy !!!

TSQL Query for Global Search in Database

Hello Friends,

2 months back I was looking for a string saved in database from my application. Suddenly I found that the string value was not saved in expected SQL table. However the application-logs had no logs for any error raised by insert query.

I was surprised and wondering where the hell my string value stored in database of approx. 15 GB of size and approx. 108 tables.

Finally to help myself, I prepared a TSQL script that helps me to find a varchar-value among any table from a database. Later I extended the same script to support seeking multiple databases.

You just need to specify @Text_To_Search, @DBname and a flag - @Single_DB_Only to indicate whether to seek in single database OR all databases on the sql-server.

SET NOCOUNT OFF
GO

declare @Text_To_Search nvarchar(max)
declare @TableName nvarchar(max)
declare @ColName nvarchar(max)
declare @CMD nvarchar(max)
declare @DBname nvarchar(max)
declare @Single_DB_Only bit

 
set @Text_To_Search = 'SOMETHING'   -- Enter the Text here which you want to search
set @Single_DB_Only = 1     -- If @Single_DB_Only = 0 then the search will be in only one database
if @Single_DB_Only = 1
 set @DBname = 'MY_DATABASE'    -- Enter Database Name in which you want to search
 

/* ********************** Validation ********************** */
IF @Single_DB_Only = 1 AND NOT EXISTS(SELECT name FROM [master].[sys].[databases] WHERE name = @DBname)
BEGIN
 PRINT 'Please enter valid database name'
 RAISERROR('Please enter valid database name', 20, -1) WITH LOG
END
/*-- ********************** Validation ********************** */
 
if exists(select * from [master].[dbo].sysobjects where xtype = 'U' and [Name] = 'MY_INFO_ALL_TABLES')
  drop table [master].[dbo].MY_INFO_ALL_TABLES
create table [master].[dbo].MY_INFO_ALL_TABLES (TableName nvarchar(max), ColName nvarchar(max))
 
if exists(select * from [master].[dbo].sysobjects where xtype = 'U' and Name = 'MY_INFO')
  drop table [master].[dbo].MY_INFO
create table [master].[dbo].MY_INFO (DBName nvarchar(max), TableName nvarchar(max), ColName nvarchar(max), FieldValue nvarchar(max))

if @Single_DB_Only = 0
Begin
 declare CUR_DB cursor for select name from [master].[sys].[databases] where name not in('master','tempdb', 'model','msdb','tempdb') and state = '0' 
 open CUR_DB
 fetch next from CUR_DB  into @DBname
 while @@FETCH_STATUS = 0
 Begin
  print @DBNAME 
  
  set @CMD = 'insert into [master].[dbo].MY_INFO_ALL_TABLES (TableName, ColName) select T1.Name as TableName , T0.Name as ColName from [{3}].sys.syscolumns T0 inner join [{3}].sys.sysobjects T1 on T0.id = T1.id where T1.xtype = ''U'' and T0.Name not like ''MY_TABLES%'' '
  set @CMD = replace(@CMD,'{3}',@DBname)
  -- print @CMD
  exec (@CMD)
  
  declare CUR cursor for select TableName , ColName from [master].[dbo].MY_INFO_ALL_TABLES
  open CUR
  fetch next from CUR  into @TableName, @ColName
  while @@FETCH_STATUS = 0
  Begin
     set @CMD = 'insert into [master].[dbo].MY_INFO (DBName,TableName,ColName,FieldValue) select ''{3}'',''{1}'',''{0}'', cast({0} as nvarchar) from [{3}].dbo.{1} where {0} like ''%{2}%'' '
     set @CMD = replace(@CMD,'{0}',@ColName)
     set @CMD = replace(@CMD,'{1}',@TableName)
     set @CMD = replace(@CMD,'{2}',@Text_To_Search)
     set @CMD = replace(@CMD,'{3}',@DBname)
     -- print @CMD
     exec (@CMD)
     fetch next from CUR into @TableName, @ColName
  End
  close CUR
  deallocate CUR
  
  fetch next from CUR_DB  into @DBname
 End
 close CUR_DB
 deallocate CUR_DB
End
Else
Begin
 print @DBNAME 
 
 set @CMD = 'insert into [master].[dbo].MY_INFO_ALL_TABLES (TableName, ColName) select T1.Name as TableName , T0.Name as ColName from [{3}].sys.syscolumns T0 inner join [{3}].sys.sysobjects T1 on T0.id = T1.id where T1.xtype = ''U'' and T0.Name not like ''MY_TABLES%'' '
 set @CMD = replace(@CMD,'{3}',@DBname)
 -- print @CMD
 exec (@CMD)
 
 declare CUR cursor for select TableName , ColName from [master].[dbo].MY_INFO_ALL_TABLES
 open CUR
 fetch next from CUR  into @TableName, @ColName
 while @@FETCH_STATUS = 0
 Begin
    set @CMD = 'insert into [master].[dbo].MY_INFO (DBName,TableName,ColName,FieldValue) select ''{3}'',''{1}'',''{0}'', cast({0} as nvarchar) from [{3}].dbo.{1} where {0} like ''%{2}%'' '
    set @CMD = replace(@CMD,'{0}',@ColName)
    set @CMD = replace(@CMD,'{1}',@TableName)
    set @CMD = replace(@CMD,'{2}',@Text_To_Search)
    set @CMD = replace(@CMD,'{3}',@DBname)
    -- print @CMD
    exec (@CMD)
    fetch next from CUR into @TableName, @ColName
 End
 close CUR
 deallocate CUR
 
 fetch next from CUR_DB  into @DBname
End
 
select * from [master].[dbo].MY_INFO
GO

Hope this TSQL script can save a lot time of yours,

Enjoy!!!

Heirarchical data - SQL query

Creating Table:

CREATE TABLE [Category](
 [CatId] [int] IDENTITY(1,1) NOT NULL,
 [PCatId] [int] NULL,
 [CatName] [varchar](50) NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
 [CatId] ASC
))
GO

Sample Data:

Insert into [Category] ([PCatId],[CatName]) values (0,'Cat1');
Insert into [Category] ([PCatId],[CatName]) values (0,'Cat2');
Insert into [Category] ([PCatId],[CatName]) values (1,'Cat3');
Insert into [Category] ([PCatId],[CatName]) values (1,'Cat4');
Insert into [Category] ([PCatId],[CatName]) values (2,'Cat5');
Insert into [Category] ([PCatId],[CatName]) values (2,'Cat6');
Insert into [Category] ([PCatId],[CatName]) values (0,'Cat7');
Insert into [Category] ([PCatId],[CatName]) values (7,'Cat8');
Insert into [Category] ([PCatId],[CatName]) values (7,'Cat9');
Insert into [Category] ([PCatId],[CatName]) values (7,'Cat10');
Insert into [Category] ([PCatId],[CatName]) values (8,'Cat11');
Insert into [Category] ([PCatId],[CatName]) values (8,'Cat12');
Insert into [Category] ([PCatId],[CatName]) values (8,'Cat13');
GO

Final SQL Query

Now, Get Category with CatId = 8 and all it's child-hierarchy :

declare @catId int = 8;

-- select * from Category;

WITH hierarchy AS (
  SELECT c1.CatId,
         c1.CatName,
         c1.PCatId,
         CAST(NULL AS VARCHAR(50)) AS parentname
    FROM Category c1
   WHERE c1.PCatId = 0
  UNION ALL
  SELECT c2.CatId,
         c2.CatName,
         c2.PCatId,
         y.CatName
    FROM Category c2
    JOIN hierarchy y ON y.CatId = c2.PCatId)
SELECT s.CatId,
       s.CatName,
    s.PCatId,
       s.parentname
  FROM hierarchy s
  where s.PCatId = @catId OR s.CatId = @catId
  order by s.PCatId

Code / Syntax Highlighting with Blogger Engine

Introduction

I've been using Blogger as my blogging engine for a couple of weeks. I've been quite impressed at how easy it makes it to update your blogs look and feel and how free you are with the HTML and semantic layout of the pages.
One thing that seemed to be missing was allowing developers to copy and paste code into their blogs and allow other users to copy and paste the code from the blog into their own code.

Using SyntaxHighlighter Javascript Library

So in my search to find something better i came across this post which usessyntax highlighter.

Adding Syntax Highlighter to Blogger Template

Copy the following code

<link href='http://alexgorbatchev.com/pub/sh/current/styles/shCore.css' rel='stylesheet' type='text/css'/> 

<link href='http://alexgorbatchev.com/pub/sh/current/styles/shThemeDefault.css' rel='stylesheet' type='text/css'/> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shCore.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCpp.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCSharp.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCss.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJava.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJScript.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPhp.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPython.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushRuby.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushSql.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushVb.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js' type='text/javascript'></script> 

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPerl.js' type='text/javascript'></script> 

<script language='javascript'> 

SyntaxHighlighter.config.bloggerMode = true;

SyntaxHighlighter.config.clipboardSwf = 'http://alexgorbatchev.com/pub/sh/current/scripts/clipboard.swf';

SyntaxHighlighter.all();

</script>

paste it into your Blogger Template just above the </head> tag
Save the template
Then you can start creating code blocks in your existing or new Blog entries.
There are 2 ways to add a code block using syntaxhighlighter

Method 1: Using the script Tag

<script type=&quot;syntaxhighlighter&quot; class=&quot;brush: csharp&quot;>

<![CDATA[

// Comment

public class Testing {

public Testing() {

}

public void Method() {

/* Another Comment

on multiple lines */

int x = 9;

}

}

]]></script>

becomes:

// Comment

public class Testing {

    public Testing() {

    }

    public void Method() {

        /* Another Comment

           on multiple lines */

        int x = 9;

    }

}

Method 2: Using the pre Tag

<pre class="brush: csharp">// Comment

public class Testing {

public Testing() {

}

public void Method() {

/* Another Comment

on multiple lines */

int x = 9;

}

}

</pre>