SplitString SQL function returning comma seperated values in a table

Missed on the function that will receive comma seperated IDs and return as a table after splitting them.

CREATE
FUNCTION [dbo].[SplitString] (
@myString
VARCHAR(500),
@deliminator
VARCHAR(10)
)
RETURNS
@ReturnTable
TABLE (
[id] [int]
IDENTITY(1,1) NOT NULL,
[part] [varchar]
(50) NULL
)
AS
BEGIN
DECLARE @iSpaces INT
DECLARE @part VARCHAR(50)
SELECT @iSpaces = CHARINDEX(@deliminator,@myString,0)
WHILE @iSpaces > 0
BEGIN
SELECT @part = SUBSTRING(@myString,0,CHARINDEX(@deliminator,@myString,0))
INSERT INTO @ReturnTable(part)
SELECT @part
SELECT @myString = SUBSTRING(@mystring,CHARINDEX(@deliminator,@myString,0)+ LEN(@deliminator),LEN(@myString) - CHARINDEX(' ',@myString,0))
SELECT @iSpaces = CHARINDEX(@deliminator,@myString,0)
END
IF LEN(@myString) > 0
INSERT INTO @ReturnTable
SELECT @myString
RETURN END

Fetch Cartesian product count from 2 column, both columns containing ID, one containing comma separated IDs

I am working on a chart which uses xml data to build the graph. Well, here I am not going to do anything on xml. Rather I will look into what data the chart is expecting, what were the challenges that I faced and how did I overcome it.

I have a table named ‘OfficeDetails’, of which I am interested in 3 columns viz., ‘AppID’, ‘ManagedBy’, ‘BU’. My graph will contain the information of the last 2 columns, but as I need some unique column for filtering the data, I had chosen AppID for that purpose.  ManagedBy and BU contains the ID and the actual values are available in OFFICELookupValue table.

The graph need the count of ManagedBy for each BU. However the graph need the count even if a ManagedBy –BU count is 0; i.e, if there are 10 unique BU and 5 ManagedBy then we need to return 10*5=50 records. The logic becomes more complicated because the BU column consists of comma separated Ids. I looked for some sort of logic to loop through the rows in OfficeDetails table. Most of them suggested cursors. Well, I am aware that cursor in SqlServer is not a very good idea. Looked around and I found a much better solution.

Step 1

Create a table variable:

DECLARE @tempOfficeDetails TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200)) 

Step 2

Insert the required data to the table variable, we may also filter data in this step:

INSERT INTO @tempOfficeDetails  

      SELECT AppID, ManagedBy, BU FROM OfficeDetails ORDER BY BU DESC

Step 3

Handling the comma separated values of BU need one more table variable:

DECLARE @tempOfficeDetailsSplitBU TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200))

Step 4

As we move through the While loop the top values are inserted to tempOfficeDetailsSplitBU  table:

SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails 

      WHILE @AppID IS NOT NULL 

      BEGIN  

            INSERT INTO @tempOfficeDetailsSplitBU SELECT AppID, ManagedBy, S.part FROM @tempOfficeDetails CROSS APPLY [dbo].[SplitString](@BU, ',') AS S WHERE AppID =@AppID 

            DELETE  @tempOfficeDetails WHERE @AppID = AppID 

            SET @AppID = NULL          

            SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails   

      END

Step 5

We Created one more table variable @BusinessFunctManagedBy which will store the all the records where ManagedBy –BU count is >0.

DECLARE @BusinessFunctManagedBy TABLE           

      (           

            BusinessFunct VARCHAR(254),           

            ManagedBy VARCHAR(254),           

            OfficeCount INT            

      )

Step 6

The step/logic to insert the records from tempOfficeDetailsSplitBU is below:

INSERT INTO @BusinessFunctManagedBy           

      SELECT officelkp2.[Value] AS BusinessFunct, officelkp.[Value] AS ManagedBy,            

            OfficeCount = CASE WHEN officelkp2.[Value] IS NULL THEN 0 ELSE COUNT(1) END           

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails         

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.ManagedBy 

      LEFT JOIN OFFICELookupValue officelkp2      

            ON officelkp2.pkID = OfficeDetails.BU           

      GROUP BY officelkp2.[Value], officelkp.[Value] ORDER BY officelkp2.[Value] DESC

Step 7

Since we need the records where BU-ManagedBy count is 0, we created 2 table variables and inserted records as below:

DECLARE @BusinessFunct TABLE           

      (           

            BusinessFunct VARCHAR(254)           

      )           

             

      DECLARE @ManagedByTab TABLE           

      (           

            ManagedBy VARCHAR(254)           

      )           

             

      INSERT INTO @BusinessFunct           

      SELECT DISTINCT officelkp.[Value]  AS BusinessFunct         

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails    

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.BU       

          

      --SELECT * from @BusinessFunct

             

      INSERT INTO @ManagedByTab           

      SELECT DISTINCT officelkp.[Value] AS ManagedBy         

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails      

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.ManagedBy 

Step 8

We created one more table variable TotalMatching which inserts the Cartesian product of ManagedBy-BU columns with count 0. Next it update the OfficeCount with values from BusinessFunctManagedBy table that we created earlier. Next delete all the records where record count is 0. Hence we are left with only the records with 0 count. Insert them to BusinessFunctManagedBy.

DECLARE @TotalMatching TABLE           

      (           

            BusinessFunct VARCHAR(254),           

            ManagedBy VARCHAR(254),            

            OfficeCount INT           

      )           

             

      INSERT INTO @TotalMatching           

      SELECT H.BusinessFunct,M.ManagedBy,0           

      FROM @BusinessFunct H           

      CROSS JOIN @ManagedByTab M           

             

      UPDATE T           

      SET T.OfficeCount = A.OfficeCount           

      FROM @TotalMatching T           

      INNER JOIN @BusinessFunctManagedBy A           

            ON T.BusinessFunct = A.BusinessFunct           

            AND T.ManagedBy=A.ManagedBy           

             

      DELETE FROM @TotalMatching           

      WHERE OfficeCount > 0           

             

      INSERT INTO @BusinessFunctManagedBy           

      SELECT * FROM @TotalMatching           

 The complete stored procedure is below:

CREATE PROC dbo.OFFICE_BusinessFunctManagedBy     

AS       

BEGIN  

      DECLARE @tempOfficeDetails TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200)) 

      DECLARE @tempOfficeDetailsSplitBU TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200))

      DECLARE  @AppID INT, @ManagedBy INT, @BU VARCHAR(200)

      INSERT INTO @tempOfficeDetails  

      SELECT AppID, ManagedBy, BU FROM OfficeDetails ORDER BY BU DESC 

      SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails 

      WHILE @AppID IS NOT NULL 

      BEGIN  

            INSERT INTO @tempOfficeDetailsSplitBU SELECT AppID, ManagedBy, S.part FROM @tempOfficeDetails CROSS APPLY [dbo].[SplitString](@BU, ',') AS S WHERE AppID =@AppID 

            DELETE  @tempOfficeDetails WHERE @AppID = AppID 

            SET @AppID = NULL          

            SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails   

      END  

      DECLARE @BusinessFunctManagedBy TABLE           

      (           

            BusinessFunct VARCHAR(254),           

            ManagedBy VARCHAR(254),           

            OfficeCount INT            

      )           

      INSERT INTO @BusinessFunctManagedBy           

      SELECT officelkp2.[Value] AS BusinessFunct, officelkp.[Value] AS ManagedBy,            

            OfficeCount = CASE WHEN officelkp2.[Value] IS NULL THEN 0 ELSE COUNT(1) END           

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails         

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.ManagedBy 

      LEFT JOIN OFFICELookupValue officelkp2      

            ON officelkp2.pkID = OfficeDetails.BU           

      GROUP BY officelkp2.[Value], officelkp.[Value] ORDER BY officelkp2.[Value] DESC

      --SELECT * from @BusinessFunctManagedBy order by BusinessFunct asc

      DECLARE @BusinessFunct TABLE           

      (           

            BusinessFunct VARCHAR(254)           

      )           

             

      DECLARE @ManagedByTab TABLE           

      (           

            ManagedBy VARCHAR(254)           

      )           

             

      INSERT INTO @BusinessFunct           

      SELECT DISTINCT officelkp.[Value]  AS BusinessFunct         

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails    

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.BU       

          

      --SELECT * from @BusinessFunct

             

      INSERT INTO @ManagedByTab           

      SELECT DISTINCT officelkp.[Value] AS ManagedBy         

      FROM  @tempOfficeDetailsSplitBU  AS OfficeDetails      

      LEFT JOIN OFFICELookupValue officelkp      

            ON officelkp.pkID = OfficeDetails.ManagedBy              

             

      DECLARE @TotalMatching TABLE           

      (           

            BusinessFunct VARCHAR(254),           

            ManagedBy VARCHAR(254),            

            OfficeCount INT           

      )           

             

      INSERT INTO @TotalMatching           

      SELECT H.BusinessFunct,M.ManagedBy,0           

      FROM @BusinessFunct H           

      CROSS JOIN @ManagedByTab M           

             

      UPDATE T           

      SET T.OfficeCount = A.OfficeCount           

      FROM @TotalMatching T           

      INNER JOIN @BusinessFunctManagedBy A           

            ON T.BusinessFunct = A.BusinessFunct           

            AND T.ManagedBy=A.ManagedBy           

             

      DELETE FROM @TotalMatching           

      WHERE OfficeCount > 0           

             

      INSERT INTO @BusinessFunctManagedBy           

      SELECT * FROM @TotalMatching           

             

      SELECT * FROM @BusinessFunctManagedBy ORDER BY LEN(BusinessFunct) DESC, 2, 1           

END

Finding ASP.Net control in User Control when using Master Page

Finding a control from User Control while using Master page can cause some confusion.

The code when NOT using Master page:

ucl_slareport WebUserControl1 = (ucl_slareport)FindControl(reportview);
Repeater ulRepeater1 = (Repeater)WebUserControl1.FindControl("Repeater1");
ulRepeater1.DataSource = reader;
ulRepeater1.DataBind();

Here ucl_slareport is my user control. The control that we are searching is a Repeater control. "reportview" is a new name of the control that we passing from a different function.

When we have the Master Page and trying to find the Repeater control, we first do a FindControl of the ContentPlaceHolder - "MainContent", and then we do a find control of the repeater.

ContentPlaceHolder mpContentPlaceHolder = (ContentPlaceHolder)this.Master.FindControl("MainContent");
ucl_slareport WebUserControl1 = (ucl_slareport)mpContentPlaceHolder.FindControl(reportview);
Repeater ulRepeater1 = (Repeater)WebUserControl1.FindControl("Repeater1");
ulRepeater1.DataSource = reader;
ulRepeater1.DataBind();

So that solves the problem of finding the control from the user control.

Calling an executable file from Website and passing arguments

I had a requirement where I needed to call a exe file from my web application. Also I needed to pass arguments to the exe.

Am calling the exe from my website through a button click. The below code explains:


Webpage.aspx

protected void Button1_Click(object sender, EventArgs e)
    {
        ProcessStartInfo prodRun = new ProcessStartInfo(ConfigurationManager.AppSettings["exepath"] + "ConClientServer.exe", ConfigurationManager.AppSettings["paths"]);       
        Process exProducts = new Process();
        prodRun.WorkingDirectory = @"C:\my work\EduProj\ConClientServer\bin\Debug\";
        prodRun.Arguments = "C:\\Development\\Temp\\NewFilesTemp\\cab.txt";
        exProducts = Process.Start(prodRun);
    }

The exe file name is mentioned in ProcessStartInfo - "ConClientServer.exe".
WorkingDirectory is where my exe resides.
The 4th line, I passed a single argument. We can however pass multiple argument, separated by comma.

 The exe file is called from the location mentioned in the working directory, it accepts the argument from the website as below:

 ConClientServer.exe
    class Program
    {
        static void Main(string[] args)
        {
            Client cl = new Client();
            if (args.Length > 0)
            {
                string path = args[0];               
                cl.FetchFileFromServer(path);
            }
        }
    }

    public class Client
    {       
        public void FetchFileFromServer(string storepath)
        {
              string downloadpath  = storepath;
         }
     }

IE Developer Toolbar disabled

I am using Windows XP and IE7. Recently, after some updates ran on my system I found that the IE Developer toolbar is not working. When I looked further, I found that “Enable third-party browser extensions*” is disabled in Tools->Internet Options->Advanced->Browsing. It’s grayed out, and there is no way it can be checked (tried through system administrator also). So what was the solution?

Somewhere after a lot of googling/binging, I got this solution:

Go to command prompt, type regedit, enter. The registry key window will open.

Go to HKEY_LOCAL_MACHINE->SOFTWARE->Policies->Microsoft->Internet Explorer->Main

Here you will see the Data as “no” for “Enable Browser Extensions”. Set it to “yes”. Hurray!!!

IE Developer Toolbar is working. If you check in Internet Options, the browser extension checkbox is checked.

Column values as a single string - MS Sql Server

There are situations where you need the resultant values of a single column to be displayed/processed as a single string. So here goes the query:

DECLARE @Str VARCHAR(MAX)
SELECT @Str = COALESCE(@Str+',' ,'') + CONVERT(varchar,Column1) FROM MyTable
SELECT @Str

Multiple Count from Single Table Sql Server

Recently my collegue was working with a query where he needed multiple counts from a table with different conditions. Say its something like a company has multiple office buildings with multiple floors. They need the count of total space for each floor of each building and empty space with the same conditions.
The query I referred was like this:

SELECT SUM(CASE WHEN condition1 THEN 1 ELSE 0 END) count1, SUM(CASE WHEN condition2 THEN 1 ELSE 0 END) count2 FROM yourtable

Customizing it to my need I wrote the following query

SELECT SUM(CASE SeatStatus WHEN 0 THEN 1 ELSE 0 END) FreeSpace, COUNT(Seat) TotalSpace FROM MyTable