Extract single row of a table separated by comma

DECLARE @XYList varchar(MAX)
SET @XYList = ''

SELECT @XYList = @XYList + CONVERT(varchar, X) + ',' + CONVERT(varchar, Y) + ','
FROM POINTS

-- Remove last comma
SELECT LEFT(@XYList, LEN(@XYList) - 1)

Concatenate multiple rows to single having similar group

SELECT
    Id,Name,
    Stuff(
    (SELECT '/' + State
       FROM Table_3 t2
        WHERE t2.Id=t1.Id and t2.Name=t1.Name
        FOR XML PATH(''),type).value('.','nvarchar(max)'), 1, 1, '') AS Class
FROM
    Table_3 t1
GROUP BY Id,Name

Use Column alias in Where Condition

select row_number() over (order by (select(1))) as SNo,* from (select SM.ADMNNO,           
               convert(varchar(13),SM.ADMNDATE,103) as ADMNDATE,SM.STDFIRSTNAME,             
               Convert(varchar(13),SM.DOB,103) as DOB, SM.F_MOBILE,SM.F_NAME,             
               SM.PRESENT_CLASS,SM.PRESENT_SEC,SM.ACTIVE,SM.FranchiseCode,SM.StudentRegNo,           
               (sum(SM.FeeOpeningBal)+sum(SL.Debit)-sum(SL.Credit)) AS Balance ,           
               convert(varchar(15),(sum(SM.FeeOpeningBal)+sum(SL.Debit)-sum(SL.Credit))) as BalanceS           
               from STUDENTMASTER SM Inner Join StudentLedger SL           
               on SM.ADMNNO=SL.ADMNNO AND SM.FranchiseCode=SL.FranchiseCode           
               where SM.FranchiseCode='DL') DerivedTable where Balance<>0

column name of a table separated by comma in sqlserver

DECLARE @TABLE_NAME VARCHAR(128)
DECLARE @SCHEMA_NAME VARCHAR(128)

-----------------------------------------------------------------------
-- *** User Customisation

-- Set up the name of the table here :
SET @TABLE_NAME = 'tmp_SupplierMaster'
-- Set up the name of the schema here, or just leave set to 'dbo' :
SET @SCHEMA_NAME = 'dbo'
--SET @SCHEMA_NAME = 'damsdata'

-----------------------------------------------------------------------

DECLARE @vvc_ColumnName VARCHAR(128)
DECLARE @vvc_ColumnList VARCHAR(MAX)

IF @SCHEMA_NAME =''
  BEGIN
  PRINT 'Error : No schema defined!'
  RETURN
  END

IF NOT EXISTS (SELECT * FROM sys.tables T JOIN sys.schemas S
          ON T.schema_id=S.schema_id
          WHERE T.Name=@TABLE_NAME AND S.name=@SCHEMA_NAME)
  BEGIN
  PRINT 'Error : The table '''+@TABLE_NAME+''' in schema '''+
        @SCHEMA_NAME+''' does not exist in this database!'
  RETURN
  END

DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT   CASE WHEN PATINDEX('% %',C.name) > 0
         THEN '['+ C.name +']'
         ELSE C.name
         END
FROM     sys.columns C
JOIN     sys.tables T
ON       C.object_id  = T.object_id
JOIN     sys.schemas S
ON       S.schema_id  = T.schema_id
WHERE    T.name    = @TABLE_NAME
AND      S.name    = @SCHEMA_NAME
ORDER BY column_id


SET @vvc_ColumnList=''

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @vvc_ColumnName

WHILE @@FETCH_STATUS=0
  BEGIN
  SET @vvc_ColumnList = @vvc_ColumnList + @vvc_ColumnName

  -- get the details of the next column
  FETCH NEXT FROM TableCursor INTO @vvc_ColumnName

  -- add a comma if we are not at the end of the row
  IF @@FETCH_STATUS=0
    SET @vvc_ColumnList = @vvc_ColumnList + ','
  END

CLOSE TableCursor
DEALLOCATE TableCursor

PRINT 'Here is the comma separated list of column names :'
PRINT '--------------------------------------------------'
PRINT @vvc_ColumnList

Sql Various Testing (Procedure,Cursor)

CREATE Procedure [damsdata].[sp_AutoImport_Final]                  
as                     
begin                     
Declare @FCode varchar(20),       
@Main_Table varchar(50),       
@Temp_Table varchar(50),       
@FromDate varchar(50),       
@ToDate varchar(50),       
@Offline_Sync_Date varchar(50),       
@DateRange varchar(50),       
@Condition varchar(50),       
@ChildTable int,       
@RelatedTab_Name varchar(50),       
@RelatedTab_TempName varchar(50),       
@Related_Condition varchar(50),       
@ChildID varchar(max),       
@nsql NVARCHAR(max),       
@Data int,       
@parm1 int,       
@SyncType varchar(20),   
@Parent_Columns NVARCHAR(max),   
@Child_Columns NVARCHAR(max), 
@Related_Column_Id varchar(MAX)       
set @SyncType='Auto'      
if OBJECT_ID('tempdb..#Upload_Status') is not null       
drop table #Upload_Status       
select * into #Upload_Status from DataImport where WebUpdate=0      
       
set @FromDate=(select CONVERT(VARCHAR(30),FromDate,101)from DataImport)       
set @ToDate=(select CONVERT(VARCHAR(30),ToDate,101) from DataImport)       
set @Offline_Sync_Date=(select CONVERT(VARCHAR(30),AsOnDate,101) from DataImport)       
       
Declare C1 Cursor for select FranchiseCode from #Upload_Status       
open C1       
fetch next from C1 into @FCode       
while(@@FETCH_STATUS=0)       
begin       
       
Declare InnerCursor Cursor for select Main_Table,Temp_Table,DateRange_Column,Condition_Column,Has_Child_Table,Related_Table,Related_Temp_Table,Related_Column,Parent_Columns,Child_Columns from DataMapping       
open InnerCursor       
fetch next FROM InnerCursor into @Main_Table,@Temp_Table,@DateRange,@Condition,@ChildTable,@RelatedTab_Name,@RelatedTab_TempName,@Related_Condition,@Parent_Columns,@Child_Columns       
while(@@FETCH_STATUS=0)       
begin       
declare @Sqlstr varchar(MAX),       
@ChildSqlstr varchar(MAX),       
@InnerChildSqlstr varchar(MAX),       
@SyncReport varchar(max)       
--check the data within the date range in temp table.       
--If Exist then delete the records from Main table and insert records from temp table to main table       
if(@DateRange='No')       
begin       
set @nsql='select @parm1 = COUNT(*)from '+@Temp_Table+' where '+@Condition+' in ('''+@FCode+''')'        
end       
else       
begin       
set @nsql='select @parm1 = COUNT(*)from '+@Temp_Table+' where '+@Condition+' in ('''+@FCode+''') and '+@DateRange+' between '''+@FromDate+''' and '''+@ToDate+''''        
end       
exec sp_executesql @nsql, @params = N'@parm1 int OUTPUT', @parm1 = @parm1 OUTPUT        
set @Data=(SELECT @parm1 AS newCount)       
if(@Data>0)       
       
begin       
       
if(@ChildTable>0)--Check Wheather the Table has some Related Table with it.       
begin       
if OBJECT_ID('tempdb..#GetDataFromMainTable') is not null       
drop table #GetDataFromMainTable     
create table #GetDataFromMainTable (entryNo varchar(max))       
     
--set @ChildSqlstr='select '+@Related_Condition+' into #GetDataFromMainTable from '+@Temp_Table+' where '+@Condition+' in ('''+@FCode+''') and '+@DateRange+' between '''+@FromDate+''' and '''+@ToDate+''''        
--exec(@ChildSqlstr)     
--Select all foreign key Id into temp table   
set  @Related_Column_Id=''    
INSERT INTO #GetDataFromMainTable EXEC('exec sp_childParent '+@Temp_Table+','+@Related_Condition+','+@Condition+','+@FCode+','+@DateRange+','''+@FromDate+''','''+@ToDate+'''')     
 
SELECT @Related_Column_Id = @Related_Column_Id + CONVERT(varchar, entryNo) + ','  
FROM #GetDataFromMainTable 
SET @Related_Column_Id=(SELECT LEFT(@Related_Column_Id, LEN(@Related_Column_Id) - 1)) 
--Print @Related_Column_Id 
 
--declare innerChildCursor cursor for select * from #GetDataFromMainTable       
--open innerChildCursor       
--fetch next from innerChildCursor into @ChildID       
--while(@@FETCH_STATUS=0)       
--begin       
--Based on the Id delete from Child Table and then select from TEMP child table and then insert into Child Table       
set @InnerChildSqlstr='Delete from '+@RelatedTab_Name+' where '+@Related_Condition+' in ('+@Related_Column_Id+')'       
set @InnerChildSqlstr='insert into '+@RelatedTab_Name+' ('+@Child_Columns+' ) select * from  '+@RelatedTab_TempName+' where '+@Related_Condition+' in ('+@Related_Column_Id+')'       
exec(@InnerChildSqlstr)      
print @InnerChildSqlstr     
-- print @RelatedTab_TempName     
-- print @Related_Condition     
-- print @ChildID     
--fetch next from innerChildCursor into @ChildID       
--end       
--CLOSE innerChildCursor       
--DEALLOCATE innerChildCursor       
        
        
end       
if(@DateRange='No')       
begin               
SET @Sqlstr='Delete from '+@Main_Table+' where '+@Condition+' in ('''+@FCode+''');'                       
end       
else       
begin       
SET @Sqlstr='Delete from '+@Main_Table+' where '+@Condition+' in ('''+@FCode+''') and '+@DateRange+' between '''+@FromDate+''' and '''+@ToDate+''';'                       
end       
set @Sqlstr=@Sqlstr+'Insert into '+@Main_Table+' ('+@Parent_Columns+' ) select * from '+@Temp_Table+' where '+@Condition+' in ('''+@FCode+''');'                   
print @Sqlstr   
exec(@Sqlstr)       
       
end       
fetch next FROM InnerCursor into @Main_Table,@Temp_Table,@DateRange,@Condition,@ChildTable,@RelatedTab_Name,@RelatedTab_TempName,@Related_Condition,@Parent_Columns,@Child_Columns       
end       
close InnerCursor       
deallocate InnerCursor       
       
set @SyncReport='Insert into tbl_SyncTrack values ('''+@FCode+''',GETDATE(),'''+@SyncType+''','''+@FromDate+''','''+@ToDate+''','''+@Offline_Sync_Date+''')'       
exec(@SyncReport)       
fetch next from C1 into @FCode       
end       
close C1       
deallocate C1           
end         

Get the value of treenode which are checked in.net

List<string> PermissionId = new List<string>();


 private string CallNodesSelector()
        {
            TreeNodeCollection nodes = this.TreeView1.Nodes;
            foreach (TreeNode n in nodes)
            {
                GetNodeRecursive(n);
            }
            List<string> distinctPermissionIds = PermissionId.Distinct().ToList();
            string PermissionIds = "1,";
            foreach (string value in distinctPermissionIds)
            {
                PermissionIds += value + ",";
            }
            PermissionIds = PermissionIds.Substring(0, PermissionIds.Length - 1);
            return PermissionIds;

        }

        private void GetNodeRecursive(TreeNode treeNode)
        {
            if (treeNode.Checked == true)
            {
                if (treeNode.Parent != null)
                {
                    TreeNode NodeParent = treeNode.Parent;
                    if (NodeParent.Checked)
                    {

                    }
                    else
                    {
                        PermissionId.Add(NodeParent.Value);
                    }
                }
                string Text = treeNode.Text;
                PermissionId.Add(treeNode.Value);

            }
            foreach (TreeNode tn in treeNode.ChildNodes)
            {
                GetNodeRecursive(tn);
            }

        }

Comma separated values in sqlserver

http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/

Progressbar in c#

----http://msdn.microsoft.com/en-us/library/system.componentmodel.backgroundworker(v=vs.90).aspx---- 

private void btnProcess_Click(object sender, EventArgs e)
        {
            if (BackGroundWorkerPhaseI.IsBusy != true)
            {
                BackGroundWorkerPhaseI.RunWorkerAsync();
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            if (BackGroundWorkerPhaseI.WorkerSupportsCancellation == true)
            {
                BackGroundWorkerPhaseI.CancelAsync();
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void BackGroundWorkerPhaseI_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                for (int i = 1; (i <= 100); i++)
                {
                    if ((BackGroundWorkerPhaseI.CancellationPending == true))
                    {

                        e.Cancel = true;
                        for (int j = i; j >= 0; j--)
                        {
                            if (j == 10)
                            {
                                SqlQuery = "truncate table tbl_Complete_Teacher_Configuration";

                            }
                            if (j == 20)
                            {
                                SqlQuery = "truncate table  tbl_Complete_Teacher_Configuration; truncate table  tbl_Complete_Class_Configuration";
                            }
                            if (j == 30)
                            {
                                SqlQuery = "truncate table  tbl_Complete_Teacher_Configuration; truncate table  tbl_Complete_Class_Configuration;truncate table tbl_Configuration_master ";
                            }
                            if (j == 40)
                            {
                                SqlQuery = "truncate table  tbl_Complete_Teacher_Configuration; truncate table  tbl_Complete_Class_Configuration;truncate table tbl_Configuration_master; truncate table tbl_Allotment_Master ";

                            }
                            flag = deleteData.DeleteDataValues(SqlQuery);
                            BackGroundWorkerPhaseI.ReportProgress(j);
                        }
                        break;
                    }
                    else
                    {
                        // Wait 100 milliseconds.
                        if (i == 10)
                        {
                            SqlQuery = "sp_CreateTeacherConfigurtion";
                            flag = insertData.InsertDataValues(SqlQuery);
                        }
                        if (i == 20)
                        {
                            SqlQuery = "sp_CreateClassConfigurtion";
                            flag = insertData.InsertDataValues(SqlQuery);
                        }
                        if (i == 30)
                        {
                            SqlQuery = "sp_CreateConfigurationMaster";
                            flag = insertData.InsertDataValues(SqlQuery);
                        }
                        // Report progress.
                        if (i == 40)
                        {
                            SqlQuery = "sp_FillAlotmentsFromCombinedConditions";
                            flag = insertData.InsertDataValues(SqlQuery);
                        }
                        if (i == 50)
                        {
                            flag = ReflectCombinedConditions();
                        }
                        if (i == 60)
                        {
                          flag = GenerateAllotments();
                        }
                        //if (i == 90)
                        //{
                        //    flag = ReflectCombinedConditions();
                        //}
                        BackGroundWorkerPhaseI.ReportProgress(i);
                    }
                }
            }
            catch (Exception ex)
            {
            }
        }

        private void BackGroundWorkerPhaseI_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {

            // Change the value of the ProgressBar to the BackgroundWorker progress.
            PhaseIProgressBar.Value = e.ProgressPercentage;
            // Set the text.
            this.Text = e.ProgressPercentage.ToString() + "%";
        }

        private void BackGroundWorkerPhaseI_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if ((e.Cancelled == true))
            {
                MessageBox.Show("Operation Aborted.!", "Time Table", MessageBoxButtons.OK, MessageBoxIcon.Error);
                PhaseIProgressBar.Value = 0;
                this.Text = "Operation Aborted";
            }

            else if (!(e.Error == null))
            {
                MessageBox.Show("Error: " + e.Error.Message, "Time Table", MessageBoxButtons.OK, MessageBoxIcon.Error);
                PhaseIProgressBar.Value = 0;
            }

            else
            {
                MessageBox.Show("PHASE-I Generation Completed.", "Time Table", MessageBoxButtons.OK, MessageBoxIcon.Information);
                PhaseIProgressBar.Value = 0;
            }
        }

Javascript function to show message before redirect

Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "redirect script","alert('Invalid Login Details!'); location.href='index.html';",true);

Get the database space used in sqlserver

select
    a.FILEID,
    [FILE_SIZE_MB] =
        convert(decimal(12,2),round(a.size/128.000,2)),
    [SPACE_USED_MB] =
        convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
    [FREE_SPACE_MB] =
        convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
    NAME = left(a.NAME,15),
    FILENAME = left(a.FILENAME,30)
from
    dbo.sysfiles a

Converting Columns into Rows in sqlserver

;with mycte
 as
 (
SELECT rn,cols,val
FROM   (SELECT  row_number() over(order by (select(1))) rn ,A,B,C,D,E,F,G,H,I,J
        FROM  #AsPerDateFinal) AS src1
UNPIVOT (val FOR cols
IN ( A,B,C,D,E,F,G,H,I,J)) AS unpvt
 )
 
SELECT [1] as NintyDays ,[2] as SixtyDays ,[3] as ThirtyDays,[4] as SevenDays,[5] as Today
FROM   (SELECT rn,cols,val
        FROM   mycte) AS src2 PIVOT
( Max(val) FOR rn IN ([1], [2], [3],[4],[5])) AS pvt










declare @sql1 varchar(max)
declare @sql2 varchar(max)
set @sql2=(SELECT STUFF ( ( SELECT DISTINCT  ',[' +CAST(statecd AS VARCHAR)+']' FROM M_States WHERE statecd<42 FOR XML PATH('')),1,1,'' ) )
set @sql1='SELECT '+@sql2+' FROM (SELECT STATENM,STATECD FROM M_STATES WHERE STATECD<42) AS K
PIVOT(MAX(STATENM) FOR STATECD IN ('+@sql2+')) AS P'
--SELECT @sql1
EXECUTE(@sql1)

Sql Server Various Testings

USE [dams]
GO
/****** Object:  StoredProcedure [damsdata].[sp_DashBoardAsPerDateUpdated]    Script Date: 12/11/2013 17:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [damsdata].[sp_DashBoardAsPerDateUpdated]
(
@FranchiseCode varchar(10),
@Session nvarchar(10),
@FirstDay varchar(20),
@SevenDay varchar(20),
@ThirtyDay varchar(20),
@sixtyDay varchar(20),
@NintyDay varchar(20)
)
as
begin

Declare @DayChange varchar(20),

@TotalStudentsAsPer varchar(max),
@NewStudentsAsPer varchar(max),
@CollectionAsPer decimal(20,2),

@ServiceTaxAsPer decimal(20,2),
@ChequeReturnAsPer decimal(20,2),
@FeeConcessionAsPer decimal(20,2),
@FeeRefundAsPer decimal(20,2),
@BadDebtAsPer decimal(20,2),
@SmsSentAsPer bigint,
@RunningBatchesAsPer varchar(max)

DECLARE @DateRange table (DRange varchar(20))
insert into @DateRange (DRange) values (@NintyDay),(@sixtyDay),(@ThirtyDay),(@SevenDay),(@FirstDay)

if OBJECT_ID('tempdb..#AsPerDateFinal') is not null
drop table #AsPerDateFinal

create Table #AsPerDateFinal(A varchar(max),B varchar(max),
C varchar(max),D varchar(max),E varchar(max),F varchar(max)
,G varchar(max),H varchar(max),I varchar(max),J varchar(max))

Declare C1 Cursor for select DRange from @DateRange
open C1
fetch next from C1 into @DayChange
while(@@FETCH_STATUS=0)
begin

 set @TotalStudentsAsPer=(Select Count(*) as TotalStudents FROM STUDENTMASTER WHERE FranchiseCode=@FranchiseCode and  
 ADMNDATE <  CONVERT(VARCHAR(15),@DayChange,103))

 SET @NewStudentsAsPer=(Select count(*) as NewAdmissions from STUDENTMASTER where FranchiseCode=@FranchiseCode AND Session=@Session  
 and ADMNDATE < CONVERT(VARCHAR(15),@DayChange,103))

 set @CollectionAsPer=(select SUM(CurrentPaid) AS Collection from FeeReceipt where FranchiseCode=@FranchiseCode  
 and ReceiptDate < CONVERT(VARCHAR(15),@DayChange,103))

 set @ServiceTaxAsPer=(select SUM(STax) AS ServiceTax from FeeReceipt where FranchiseCode=@FranchiseCode  
and ReceiptDate < CONVERT(VARCHAR(15),@DayChange,103))

  set @ChequeReturnAsPer=(select SUM(AMOUNT) as  ChequeReturn from CHEQUERETURN where FranchiseCode=@FranchiseCode  
and vDATE < CONVERT(VARCHAR(15),@DayChange,103))

  set @FeeConcessionAsPer=(select SUM(AMOUNT) as  FeeConcession from FeeConcession where FranchiseCode=@FranchiseCode 
 and VDATE < CONVERT(VARCHAR(15),@DayChange,103))

  set @FeeRefundAsPer=(select SUM(AMOUNT) as  FeeRefund from FeeRefund where FranchiseCode=@FranchiseCode 
 and DATE < CONVERT(VARCHAR(15),@DayChange,103))

  set @BadDebtAsPer=(select SUM(AMOUNT) as  BadDebt from BadDebt where FranchiseCode=@FranchiseCode 
 and VDATE < CONVERT(VARCHAR(15),@DayChange,103))

 set @SmsSentAsPer=(select Count(*) as  SmsSent from SMSSentHistory where FranchiseCode=@FranchiseCode 
 and MDate < CONVERT(VARCHAR(15),@DayChange,103))

 set @RunningBatchesAsPer=(select Count(*) as  RunningBatches from BatchMaster where FranchiseCode=@FranchiseCode and BatchCompleteFlag='N' 
 and StartDate < CONVERT(VARCHAR(15),@DayChange,103))


 insert into #AsPerDateFinal (A,B,C,D,E,F,G,H,I,J)
        values (@TotalStudentsAsPer,@NewStudentsAsPer,@CollectionAsPer,@ChequeReturnAsPer,
                @FeeConcessionAsPer,@FeeRefundAsPer,@BadDebtAsPer,@SmsSentAsPer,@ServiceTaxAsPer,@RunningBatchesAsPer)
       

fetch next from C1 into @DayChange
end
close C1
deallocate C1

--select * from #AsPerDateFinal


;with mycte
 as
 (
SELECT rn,cols,val
FROM   (SELECT  row_number() over(order by (select(1))) rn ,A,B,C,D,E,F,G,H,I,J
        FROM  #AsPerDateFinal) AS src1
UNPIVOT (val FOR cols
IN ( A,B,C,D,E,F,G,H,I,J)) AS unpvt
 )
 
SELECT [1] as NintyDays ,[2] as SixtyDays ,[3] as ThirtyDays,[4] as SevenDays,[5] as Today
FROM   (SELECT rn,cols,val
        FROM   mycte) AS src2 PIVOT
( Max(val) FOR rn IN ([1], [2], [3],[4],[5])) AS pvt

end

Reset Identity Value while there is no records in table

DBCC CHECKIDENT ('TEMP_User', RESEED, 0)

Reset Identity Value while there is no records in table

DBCC CHECKIDENT ('TEMP_User', RESEED, 0)