Friday, August 26, 2016

SYS.SQL_MODULES in T-SQL (Catalog View)

Sys.Sql_Modules return objects detail in Sql Server database. We can get detail about Stored Procedures, Replication filter procedure , Views , DML Triggers ,SQL Scalar Function  ,SQL in line table, SQL Table value function ,Rules

Columns

object_id
definition
uses_ansi_nulls
uses_quoted_identifier
is_schema_bound
uses_database_collation
is_recompiled
null_on_null_input
execute_as_principal_id

SELECT  object_id, definition, uses_ansi_nulls, uses_quoted_identifier,
                 is_schema_bound,uses_database_collation, is_recompiled,null_on_null_input,                                      execute_as_principal_id
 FROM   sys.sql_modules




Thursday, August 25, 2016

Logical processing order of SELECT statement

Logical processing order of SELECT statement in SQL Server


When SQL query is executing on SQL server ,it has a order. This order call as Logical Processing Order.So let's logical processing order of SELECT statement.

1. FROM
2. ON
3. OUTER
4. WHERE
5 GROUP BY
6 CUBE or ROLLUP
7 HAVING
8 SELECT
9 DISTINCT
10 ORDER BY
11 TOP

SQL query to get table names and schema in Database

SQL query to get Table and Schmas in Database

There are many way to get table names and related schema in Database, Below I mention some of them

1.
SELECT SCHEMA_NAME(schema_id) 'SchemaName' , name 'TableInDb' 
FROM sys.tables

Return all tables and related schema in selected database



2.
SELECT TABLE_CATALOG ,TABLE_SCHEMA ,TABLE_NAME ,TABLE_TYPE  FROM INFORMATION_SCHEMA.TABLES

this will return more information than first one, it's return VIEWs in Database as well



3.
  SELECT t.name 'TableName' ,s.name 'SchemaName'
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]

  SELECT t.name 'TableName' ,s.name 'SchemaName'
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = N'dbo';







Wednesday, August 24, 2016

UNION and UNION ALL in T-SQL

UNION and UNION ALL


UNION

  1. use to select related information from two or more table
  2. selected column should use same data type
  3. selected column count should be equal
  4. Return DISTINCT value ( remove duplicate records)


UNION ALL

  1. Same as UNION , except that it pull all rows in selected table (which mean it doesn't consider duplicate records).  

If you know that all records are unique , it's better to use UNION , otherwise use UNION ALL



Friday, August 19, 2016

How to pass parameter to an Exe

How do pass parameter to EXE


We can use below mention method pass parameter to an Exe from different application. WPF application  or Windows form application , we can use common way to do it.


1. Add reference System.Diagnostics your class.                                                                

2. Then need start process.                                                                                    
                                                                                          ProcessStartInfo processStrInfo = new ProcessStartInfo();
              processStrInfo.FileName =   /// Exe path;
              processStrInfo.Arguments =  /// Parameter to pass as string;
              processStrInfo.UseShellExecute = false;
              Process.Start(processStrInfo);                                                                                                              
3. parameter can send as string.

           string tempCommandArgs ="TestPara";                 


             ProcessStartInfo processStrInfo = new ProcessStartInfo();
             processStrInfo.FileName = "test.exe";  /// Exe path;
             processStrInfo.Arguments = tempCommandArgs  /// Parameter to pass as string;
             processStrInfo.UseShellExecute = false;
             Process.Start(processStrInfo);   

4 .When we passing multiple parameter, we can use space between parameters .

          tempCommandArgs  =COMPANYNAME +" "+USERID

5. If parameter value have spaces.
    ex - COMPANYNAME  can be "Test Company"
   
    In this time we can use below method (add stroke to start of parameter and end of parameter)

    tempCommandArgs  ="\"" + COMPANYNAME + "\"" + " "
                                                  +"\"" + USERlOGINNAME. + "\"";    

Note:-
This is way to pass parameter to Exe as argument , But running exe from another running application (exe) is not good practice. It will consume some more memory

Now take a look at how to consume passed argument form exe.

Note:-
This example I have done with WPF application

1. We can override OnStartup in App.xaml.cs

 protected override void OnStartup(StartupEventArgs e)
  {          
            string[] args = Environment.GetCommandLineArgs();
            args = e.Args;
         
            if (args.Count() > 0)
            {            
                COMPANYNAME = args[0];        
                USERNAME = args[1];
            }

 }

Thursday, August 18, 2016

How to convert WPF Application project to Class Library

How do convert WPF Application to Class Library


Let's see how can we convert exist WPF Application project to Class Library project.


  1. Right click on WPF application project and select project properties.
  2. Then select application and change the Out put type to Class Library, save it.
  3. Open App.xaml and remove StartupUri from it.
  4. Then build project, you can see  some error with  the project. so here is the trick to remove error,
  5. Exclude  App.xaml file from project and build project again , it will build project without any errors.
  6. So if you want add App.xaml file to project ,now you can include App.xaml file again.
Note:-
If you going create new project to your solution, you can chose WPF User Control Library or WPF Custom Control Library.





Friday, July 22, 2016

Dynamic WHERE clause with T-SQL

Dynamic where clause in T-SQL

There many ways to do Dynamic WHERE clause with T-SQL , So this time I'm going to use

  • COALESCE
  • OR Operator
  • ISNULL
  • CASE
  • Dynamic SQL query


Note - All example I have done with NORTHWIND database

1 . COALESCE

DECLARE @OrderID int,
                   @ProductID int


SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
               ,[ProductID]
               ,[UnitPrice]
               ,[Quantity]
               ,[Discount]
FROM [dbo].[OrderDetails]
WHERE OrderID = COALESCE(@OrderID , OrderID)
               AND [ProductID] =COALESCE(@ProductID , [ProductID])

   Find out more about COALESCE

2. OR Operator


DECLARE @OrderID int,
                   @ProductID int

SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
                 ,[ProductID]
                 ,[UnitPrice]
                 ,[Quantity]
                 ,[Discount]
FROM    [dbo].[OrderDetails]
WHERE  ((@OrderID IS NULL) OR (OrderID = @OrderID))
                 AND ((@ProductID IS NULL) OR (ProductID =@ProductID))


3. ISNULL


DECLARE @OrderID int,
                   @ProductID int

SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
               ,[ProductID]
               ,[UnitPrice]
               ,[Quantity]
               ,[Discount]
 FROM   [dbo].[OrderDetails]
 WHERE OrderID = ISNULL(@OrderID , OrderID)
                AND [ProductID] =ISNULL(@ProductID , [ProductID])

Find out more about ISNULL

4. CASE

 DECLARE @OrderID int,
                    @ProductID int

SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
             ,[ProductID]
             ,[UnitPrice]
             ,[Quantity]
             ,[Discount]
 FROM [dbo].[OrderDetails]
 WHERE OrderID = CASE WHEN @OrderID IS NOT NULL THEN @OrderID ELSE OrderID                                          END
                                  AND [ProductID] =CASE WHEN @ProductID IS NOT NULL THEN                                                 @ProductID ELSE ProductID END


5. Dynamic SQL Query

DECLARE @OrderID int,
                   @ProductID int ,
                   @DyWhere nvarchar(MAX),
                   @DyQuery nvarchar(MAX)

SET @OrderID =10249
SET @ProductID =NULL
SET @DyWhere =' WHERE'

SET @DyQuery ='SELECT [OrderID]
                                             ,[ProductID]
                                             ,[UnitPrice]
                                             ,[Quantity]
                                             ,[Discount]
                                              FROM [dbo].[OrderDetails]'


IF @OrderID IS NOT NULL
BEGIN
   SET @DyWhere =@DyWhere+ ' OrderID ='+CONVERT(nvarchar, @OrderID)
 
END

IF @ProductID IS NOT NULL
BEGIN
SET @DyWhere =@DyWhere + ' AND ProductID ='+CONVERT(nvarchar, @ProductID)
END

IF @OrderID IS NOT NULL OR @ProductID IS NOT NULL
BEGIN
SET @DyQuery =@DyQuery +@DyWhere
END


EXECUTE sp_ExecuteSQL @DyQuery

Find out more about  sp_ExecuteSQL



You can use above mentioned one of method according to your scenario , Last one (dynamic SQL query ) this can use for more dynamic scenarios.

Wednesday, July 13, 2016

Concatenating row values from table to single string using T-SQL

SQL query concatenate row value to string


We can do this in many ways , you can write SQL function , COALESCE , XML PATH.
So this time  I'm give example using XML PATH and COALESCE. I use NORTHWND sample database to provide example

Example 1 : COALESCE (find more about COALESCE)

Using COALESCE function , we can concatenate many row values in to one single string. this is one use of COALESCE , there are many ,so you can find some more clicking above mention link.


Example 2: XML PATH

 1


2

In second example you can see I used STUFF function . So I used it for remove extra comma from string. Usually STUFF function does  inserts a string into another string, it deletes first string character form begin for given length and put second string into it


Thursday, July 7, 2016

Common Table Expression (CTE) in Sql Server

What is Common Table Expression (CTE)

  • We can take CTE as tempory view (can be thought as tempory result set) that create in memory
  • Its scope valid until next query run
  • CTE need to use just after it declaration  
  • There are two type Recursive and Nonrecursive CTE
  • Can define multiple CTE
  • CTE are unindexable ( it cau use exist index in referenced object)
  • CTE can't have constrain
  • ORDER BY clause can't be used in the CTE_Query_Definition , execpt when use TOP keyword



Simple Example

ex 1
WITH CTE_CUSTOMER_EX2
(         [CustomerID]
          ,[CompanyName]
  ,[ContactName]
  ,[ContactTitle]
  ,[Address]
  ,[City]
)
AS
(
SELECT [CustomerID]
  ,[CompanyName]
  ,[ContactName]
  ,[ContactTitle]
  ,[Address]
  ,[City]

  FROM .[dbo].[Customers]
)


SELECT * FROM CTE_CUSTOMER_EX2


ex 2

;WITH CTE_CUSTOMER_EX1
AS
(
SELECT [CustomerID]
 ,[CompanyName]
 ,[ContactName]
 FROM .[dbo].[Customers]

)
SELECT * FROM CTE_CUSTOMER_EX1


Note

If there are query before CTE define , need to use Semicolon teminator before declare CTE


Multiple CTE


  With CTE_M1 as 

(
-select statement

     , CTE_M2 as 
(
-select statemet

)


you can find some more detail and examples using this Link and Link2