AspHelpDotNet: SQL Server

Enable or Disable all triggers in a Sql server

events that get executed during a DML statement execution

Copy, paste, run as mention below .

DECLARE @DisableTriggerCommand NVARCHAR(4000)
DECLARE @TriggerTableName NVARCHAR(500)

DECLARE DisableTriggerCursor CURSOR
FOR
(
SELECT NAME AS TableName
FROM sysobjects
WHERE id IN (
SELECT parent_obj
FROM sysobjects
WHERE xtype = 'tr'
)
)

OPEN DisableTriggerCursor

FETCH NEXT
FROM DisableTriggerCursor
INTO @TriggerTableName

WHILE @@fetch_status = 0
BEGIN
SET @DisableTriggerCommand = N'ALTER TABLE ' + @TriggerTableName + ' ENABLE TRIGGER ALL'

PRINT 'Print ' + @DisableTriggerCommand + CHAR(13)

EXECUTE sp_executesql @DisableTriggerCommand

FETCH NEXT
FROM DisableTriggerCursor
INTO @TriggerTableName
END

CLOSE DisableTriggerCursor

DEALLOCATE DisableTriggerCursor

Dot net Business Entity Create Free Auto using sql server Table

create PROC [dbo].[SpGenclass]
@TableName sysname
AS

declare @Result varchar(max) = 'public class ' + @TableName + CHAR(13) + '{'+ CHAR(13)
select
@Result = @Result + 'public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }'+ CHAR(13)
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
case
typ.name
when
'bigint'
then
'long'
when
'binary'
then
'byte[]'
when
'bit'
then
'bool'
when
'char'
then
'string'
when
'date'
then
'DateTime'
when
'datetime'
then
'DateTime'
when
'datetime2'
then
'DateTime'
when
'datetimeoffset'
then
'DateTimeOffset'
when
'decimal'
then
'decimal'
when
'float'
then
'float'
when
'image'
then
'byte[]'
when
'int'
then
'int'
when
'money'
then
'decimal'
when
'nchar'
then
'char'
when
'ntext'
then
'string'
when
'numeric'
then
'decimal'
when
'nvarchar'
then
'string'
when
'real'
then
'double'
when
'smalldatetime'
then
'DateTime'
when
'smallint'
then
'short'
when
'smallmoney'
then
'decimal'
when
'text'
then
'string'
when
'time'
then
'TimeSpan'
when
'timestamp'
then
'DateTime'
when
'tinyint'
then
'byte'
when
'uniqueidentifier'
then
'Guid'
when
'varbinary'
then
'byte[]'
when
'varchar'
then
'string'
else
'UNKNOWN_' + typ.name
end
ColumnType,
case
when
col.is_nullable = 1
and typ.name in
(
'bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier'
)
then
'?'
else
''
end
NullableSign
from
sys.columns col
join
sys.types typ
on col.system_type_id = typ.system_type_id
AND col.user_type_id = typ.user_type_id
where
object_id = object_id( @TableName)
) t
order by t.ColumnId set @Result = @Result + '}'print @Result

SQL Server

Login failed for user 'IIS APPPOOL\DefaultAppPool' SQl server

Login failed for user 'IIS APPPOOL\DefaultAppPool' SQl server
--sql

CREATE LOGIN [IIS APPPOOL\DefaultAppPool] FROM WINDOWS
GO
--USE Table Name
GO
CREATE USER [IIS APPPOOL\DefaultAppPool] FOR LOGIN [IIS APPPOOL\DefaultAppPool]
GO
EXEC sp_addrolemember 'db_datareader', 'IIS APPPOOL\DefaultAppPool'
GO
EXEC sp_addrolemember 'db_datawriter', 'IIS APPPOOL\DefaultAppPool'
GO

then give permision sql server roles>database Roles>'IIS APPPOOL\DefaultAppPool'>MemberShips

SQL Server

Dynamic Sql Qry

/* This stored procedure builds dynamic SQL and executes 
using sp_executesql */
Create Procedure sp_EmployeeSelect
    /* Input Parameters */
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary    Decimal(10,2)
        
AS
    Set NoCount ON
    /* Variable Declaration */
    Declare @SQLQuery AS NVarchar(4000)
    Declare @ParamDefinition AS NVarchar(2000) 
    /* Build the Transact-SQL String with the input parameters */ 
    Set @SQLQuery = 'Select * From tblEmployees where (1=1) ' 
    /* check for the condition and build the WHERE clause accordingly */
    If @EmployeeName Is Not Null 
         Set @SQLQuery = @SQLQuery + ' And (EmployeeName = @EmployeeName)'

    If @Department Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Department = @Department)' 
  
    If @Designation Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Designation = @Designation)'
  
    If @Salary Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Salary >= @Salary)'

    If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
         Set @SQLQuery = @SQLQuery + ' And (JoiningDate 
         BETWEEN @StartDate AND @EndDate)'
    /* Specify Parameter Format for all input parameters included 
     in the stmt */
    Set @ParamDefinition =      ' @EmployeeName NVarchar(100),
                @Department NVarchar(50),
                @Designation NVarchar(50),
                @StartDate DateTime,
                @EndDate DateTime,
                @Salary    Decimal(10,2)'
    /* Execute the Transact-SQL String with all parameter value's 
       Using sp_executesql Command */
    Execute sp_Executesql     @SQLQuery, 
                @ParamDefinition, 
                @EmployeeName, 
                @Department, 
                @Designation, 
                @StartDate, 
                @EndDate,
                @Salary
                
    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

DuplicateData SQL Server

100 % Duplicate Data Remove from Table Sql Server

--Setp 1

--First Duplicate data into temp table  

---Setp 2

select ID 

FROM    DuplicateData a

        LEFT JOIN

        (

            SELECT MIN(ID) ID, English

            FROM    DuplicateData

            GROUP   BY  English

        ) b ON  a.VID = b.ID AND

                a.English = b.English 

WHERE   b.ID IS NULL

--setp 3

 delete from Maintable  where ID in (

select ID 

FROM    DuplicateData a

        LEFT JOIN

        (

            SELECT MIN(ID) ID, English

X

C# SQL Server

Some Common Function C# Easy Way for Qury Execution with SQL Server

 public DataTable GetDataTable(string query)
        {
            dynamic da = new SqlDataAdapter(query, strConnectionString);
            dynamic ds = new DataSet();
            da.Fill(ds);
            return ds.Tables(0);
        }
        public DataTable GetDataTableSP(string cSPName, string cwhere)
        {
            DataSet dataset = new DataSet();
            SqlCommand ExecCommand = new SqlCommand();
            try
            {
                SqlConnection objConnection = new SqlConnection(strConnectionString);
                ExecCommand = new SqlCommand(cSPName, objConnection);
                ExecCommand.CommandType = CommandType.StoredProcedure;
                ExecCommand.Connection.Open();
                ExecCommand.CommandTimeout = 0;
                ExecCommand.Parameters.Add(new SqlParameter("@cwhere", cwhere));
                SqlDataAdapter myadapter = new SqlDataAdapter(ExecCommand);
                myadapter.Fill(dataset);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ExecCommand.Connection.Close();
            }
            return dataset.Tables[0];
        }
       
        public DataTable runSP(string cSPName, ListDictionary MyListDic)
        {
            SqlCommand ExecCommand = default(SqlCommand);
            DataSet dataset = new DataSet();
            try
            {
                SqlConnection objConnection = new SqlConnection(strConnectionString);
                ExecCommand = new SqlCommand(cSPName, objConnection);
                ExecCommand.CommandType = CommandType.StoredProcedure;
                ExecCommand.Connection.Open();
                ExecCommand.CommandTimeout = 0;
                foreach (DictionaryEntry item in MyListDic)
                {
                    ExecCommand.Parameters.AddWithValue(item.Key.ToString(), item.Value.ToString());
                }
                SqlDataAdapter myadapter = new SqlDataAdapter(ExecCommand);
                myadapter.Fill(dataset);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ExecCommand.Connection.Close();
            }
            return dataset.Tables[0];
        }

        public string ExecuteScalar(string SqlString)
        {
            object str = "";
            SqlConnection objConnection = new SqlConnection(strConnectionString);
            SqlCommand dbcmd = new SqlCommand(SqlString, objConnection);
            try
            {
                dbcmd.Connection.Open();
                str = dbcmd.ExecuteScalar();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbcmd.Connection.Close();
            }
            return Convert.ToString(str);
        }
        public void ExecuteNonQuery(string SqlString)
        {
            SqlConnection objConnection = new SqlConnection(strConnectionString);
            SqlCommand dbcmd = new SqlCommand(SqlString, objConnection);
            try
            {
                dbcmd.Connection.Open();
                dbcmd.ExecuteNonQuery();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbcmd.Connection.Close();
            }
        }
        //   f

SQL Server

SQL SERVER Function all

--TSQL Aggregate Functions
--AVG Aggregate Function TSQL Tutorial


SELECT AVG(c.amount) AS AverageAmount 
   FROM contracts c 
WHERE c.amount > 400 ;


--COUNT Aggregate Function TSQL Tutorial

SELECT COUNT(c.contract_id) AS CountContracts 
   FROM contracts c 
WHERE c.amount > 400 ;
--MAX Aggregate Function TSQL Tutorial

SELECT MAX(c.amount) AS MaxAmount 
   FROM contracts c 
WHERE c.amount > 300 ;
--MIN Aggregate Function TSQL Tutorial

SELECT MIN(c.amount) AS MinAmount 
   FROM contracts c 
WHERE c.amount > 300 ;
--MIN Aggregate Function TSQL Tutorial

SELECT MIN(c.amount) AS MinAmount 
   FROM contracts c 
WHERE c.amount > 300 ;


--TSQL String Functions
--Charindex TSQL Tutorial


--charindex ( search_expression ,string_expression [ , start_location ] )

USE model; 
GO 
DECLARE @string varchar(64); 

SELECT @string = 'My example'; 
SELECT CHARINDEX('y', @string) as 'Col_1', 
CHARINDEX('x', @string) as 'Col_2', 
CHARINDEX('le', @string,8) as 'Col_3'; 
GO

--Concat Example 1:

SELECT CONCAT ('Daniel', ' - ','Scott') AS FullName;

--Left Example 1:

SELECT LEFT ('abcdefghik',3); 
SELECT LEFT ('12345',2); 

--Len Example 1:

--SELECT LEN ('abcde'); 
--Result: 5
--SELECT LEN ('0012345fff'); 
--Result: 10

--LOWER Example:

DECLARE @mystring varchar(250); 
SET @mystring = 'Convert TRANSACT SQL LOWER Function'; 
SELECT LOWER(@mystring);

--LTRIM Example:

DECLARE @mystring varchar(250); 
SET @mystring = '      Test LTRIM function - delete spaces from the beginning of my string.'; 
SELECT LTRIM(@mystring);  

--Substring Example:

SELECT Name, SUBSTRING(name, 1, 1) AS Initial, SUBSTRING(name, 1, 3) AS Short_name 
FROM states;

--Patindex TSQL Tutorial

USE model; 
GO 
SELECT PATINDEX('%u%',country) as 'Example' 
FROM dbo.students; 
GO

--Replace function

SELECT REPLACE('abcdefghijk','def','yyy'); 

----Right Example 1:

SELECT RIGHT ('zxcvbnm',3); 
Result: bnm
SELECT RIGHT ('12345',2); 
Result: 45

--RTRIM Example:

DECLARE @mystring varchar(250); 
SET @mystring = 'Test RTRIM function - delete spaces from the beginning of my string.       '; 
SELECT RTRIM(@mystring); 


--UPPER Example:

DECLARE @mystring varchar(250); 
SET @mystring = 'Convert transact sql UPPER Function'; 
SELECT UPPER(@mystring);


---TSQL Date and Time Data Types and Functions


--@@Datefirst Example

SELECT @@SERVERNAME AS 'Server Name', 
@@DATEFIRST AS 'First Day';  

--CURRENT_TIMESTAMP Example:

SELECT 'The current timestamp is: '+ CONVERT(char(25), CURRENT_TIMESTAMP) as System_date;

--DATEADD Example:

DECLARE @date datetime = '2014-01-01 13:10:10'; 
SELECT 'add_year' as Add_year, DATEADD(year,2,@date) as Result;

--DATEDIFF Example:

DECLARE @start_date datetime = '2007-02-19 22:24:10'; 
DECLARE @end_date datetime = '2014-02-19 22:24:10'; 
SELECT DATEDIFF(year, @start_date, @end_date) as Result;
SELECT DATEDIFF(year, '2013-02-19 21:21:59', '2014-02-19 21:21:59') as Year_diff, 
DATEDIFF(month, '2013-02-19 21:21:59', '2014-02-19 21:21:59') as Months_diff, 
DATEDIFF(day, '2013-02-19 21:21:59', '2014-02-19 21:21:59') as Days_diff;

--DATEFROMPARTS Example:

SELECT DATEFROMPARTS ( 2014, 02, 22 ) AS Date;

--DATENAME Example:

SELECT DATENAME(year,'2014-02-22 08:23:49.1234567 +05:10') as Year, 
DATENAME(month,'2014-02-22 08:23:49.1234567 +05:10') as Month, 
DATENAME(day,'2014-02-22 08:23:49.1234567 +05:10') as Day, 
DATENAME(dayofyear,'2014-02-22 08:23:49.1234567 +05:10') as Dayofyear, 
DATENAME(week,'2014-02-22 08:23:49.1234567 +05:10') as Week, 
DATENAME(weekday,'2014-02-22 08:23:49.1234567 +05:10') as Weekday, 
DATENAME(quarter,'2014-02-22 08:23:49.1234567 +05:10') as Quarter;

--DATEPART Example:

SELECT DATEPART(year,'2014-02-22 08:23:49.1234567 +05:10') as Year, 
DATEPART(month,'2014-02-22 08:23:49.1234567 +05:10') as Month, 
DATEPART(day,'2014-02-22 08:23:49.1234567 +05:10') as Day, 
DATEPART(dayofyear,'2014-02-22 08:23:49.1234567 +05:10') as Dayofyear, 
DATEPART(week,'2014-02-22 08:23:49.1234567 +05:10') as Week, 
DATEPART(weekday,'2014-02-22 08:23:49.1234567 +05:10') as Weekday, 
DATEPART(quarter,'2014-02-22 08:23:49.1234567 +05:10') as Quarter;


--DATETIME2FROMPARTS Example:
--DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fractions, precision)
SELECT DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 7, 1) as Date_1, 
DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 77, 2) as Date_2, 
DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 777, 3) as Date_3, 
DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 7777, 4) as Date_4;
--DAY :
SELECT DAY('2014-02-22') as Day_1, 
DAY('2014-02-22 08:23:49.1234567 +07:10') as Day_2, 
DAY(SYSDATETIME()) as Day_3; 


--EOMONTH Example:

DECLARE @lastday DATETIME = '02/22/2014'; 
SELECT EOMONTH ( GETDATE() ) AS Result;

--GETDATE() Example:

select getdate() as System_date;

--GETUTCDATE() Example:
--This value represents the current UTC time: Coordinated Universal Time.
select getutcdate() as System_date;

--ISDATE Example:

SELECT ISDATE('01/28/2014') as Valid_date, 
ISDATE('21/21/2014') as Invalid_date;

--MONTH Example:

SELECT MONTH('2014-03-01') as Month_1, 
MONTH('2014-03-01 08:38:49.1234567 +07:10') as Month_2, 
MONTH(SYSDATETIME()) as Month_3;

--SMALLDATETIMEFROMPARTS Example:

SELECT SMALLDATETIMEFROMPARTS ( 2014, 03, 01, 15, 50 ) AS Result;

--SWITCHOFFSET Example:

select switchoffset (CONVERT(datetimeoffset, GETDATE()), '-03:00') AS Result_1, 
switchoffset (CONVERT(datetimeoffset, GETDATE()), '+03:00') AS Result_2;

--SYSDATETIME Example:

SELECT CONVERT (date, SYSDATETIME()) as Date, 
CONVERT (time, SYSDATETIME()) as Time, 
SYSDATETIME() as SysDateTime;

--SYSDATETIMEOFFSET Example:

SELECT CONVERT (date, SYSDATETIMEOFFSET()) as Date, 
CONVERT (time, SYSDATETIMEOFFSET()) as Time, 
SYSDATETIMEOFFSET() as SysDateTimeOffSet;

--SYSUTCDATETIME Example:

SELECT CONVERT (date, SYSUTCDATETIME()) as Date, 
CONVERT (time, SYSUTCDATETIME()) as Time, 
SYSDATETIME() as SysDateTime, 
SYSUTCDATETIME() as SysUtcDateTime;

--TIMEFROMPARTS Example:

SELECT TIMEFROMPARTS ( 16, 47, 56, 7, 1 ) as Time_1, 
TIMEFROMPARTS ( 14, 47, 56, 70, 2 ) as Time_2, 
TIMEFROMPARTS ( 14, 47, 56, 700, 3 ) as Time_3;
--TODATETIMEOFFSET
SELECT TODATETIMEOFFSET (GETDATE(), '-01:00') Result_1, 
TODATETIMEOFFSET (SYSDATETIME(), -60) Result_2;

--YEAR Example:

SELECT YEAR('2014-03-01') as Result_1, 
YEAR('2014-03-01 17:01:25.1234567 +07:10') as Result_2, 
YEAR(SYSDATETIME()) as Result_3;

--TSQL System Functions

--@@Connections Example

SELECT GETDATE() AS 'Login date', 
@@CONNECTIONS AS 'Login id'; 

--@@Error Example

USE model; 
GO 
UPDATE products 
   SET product_type = 'DEDE' 
   WHERE product_id = 2; 
IF @@ERROR <> 0 
   PRINT N'Error: Product type.'; 
GO 

--@@IDENTITY
USE model; 
GO 
insert into students(id,first_name, last_name, gender,city, country) 
values(7,'Ashley','THOMPSON','F','Liverpool','England'); 
GO 
SELECT @@IDENTITY AS 'Identity';
--@@Rowcount Example
UPDATE students 
SET section = N'History' 
WHERE id = 7 
IF @@ROWCOUNT > 0 
PRINT 'Updated'  

--COALESCE
select COALESCE(null,11) as Value;


--Error_line() Example

USE model; 
GO 
BEGIN TRY 
   SELECT 8/0; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_LINE() AS Error_Line; 
END CATCH; 
GO



--Error_message() Example

USE model; 
GO 
BEGIN TRY 
   SELECT -5*9/0; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_MESSAGE() AS Error_Message; 
END CATCH; 
GO

--Error_number() Example

USE model; 
GO 
BEGIN TRY 
   SELECT 18/0; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_NUMBER() AS Error_Number; 
END CATCH; 


--Error_procedure() Example

CREATE PROCEDURE test_procedure 
AS 
   SELECT 3/0; 
GO 


USE model; 
GO 
BEGIN TRY 
   EXECUTE test_procedure; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_PROCEDURE() AS Error_Procedure; 
END CATCH; 
GO

--Error_severity() Example
--Return the code number of the error that use the CATCH block of a TRY ... CATCH
USE model; 
GO 
BEGIN TRY 
   SELECT 26/0; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_SEVERITY() AS Error_Severity; 
END CATCH; 
GO


--Error_state() Example

USE model; 
GO 
BEGIN TRY 
   SELECT 16/0; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_STATE() AS Error_State; 
END CATCH; 
GO

--HOST_ID() Example:

select HOST_ID() as Host_number;
--HOST_NAME() Example:

select HOST_NAME() as Host_name;

--ISNULL Example:

SELECT ISNULL('abc',28) as col_1, 
ISNULL('01/28/2014',7) as col_2, 
ISNULL(null,9) as col_3, 
ISNULL(null,null) as col_4, 
ISNULL('-',-1) as col_5;

--ISNUMERIC Example:

SELECT ISNUMERIC('abc') as col_1, 
ISNUMERIC('01/28/2014') as col_2, 
ISNUMERIC(123) as col_3, 
ISNUMERIC(null) as col_4, 
ISNUMERIC(-1) as col_5, 
ISNUMERIC(0.8) as col_6;

--NULLIF Example:

select NULLIF ( 'aaa' , 'aaa' ) as col_1, 
NULLIF ( 'bbb' , '3' ) as col_2, 
NULLIF ( 'ccc' , null ) as col_3;

--TSQL Security Functions

--CURRENT_USER Example:

select CURRENT_USER as Username;


--ORIGINAL_LOGIN() Example:

select ORIGINAL_LOGIN() as Original_user;

--SESSION_USER Example:

select SESSION_USER as 'Session_user';

--SYSTEM_USER Example:

select SYSTEM_USER as 'System_user';

--SYSTEM_USER Example:

select SYSTEM_USER as 'System_user';

---TSQL Metadata Functions


--APP_NAME() Example:

select APP_NAME() as Name;

--DB_ID() Example:

SELECT DB_ID() AS Database_1, 
DB_ID(N'master') AS Database_2;

--DB_NAME() Example:

SELECT DB_NAME() AS 'Database name', 
DB_NAME (1) AS 'Database name by id';

--OBJECT_DEFINITION Example:

SELECT OBJECT_DEFINITION(OBJECT_ID('master.dbo.products_list')) AS 'Object definition of my view';

--OBJECT_ID() Example:

SELECT OBJECT_ID('model.dbo.products') AS 'Object ID of Table', 
OBJECT_ID('master.dbo.products_list') AS 'Object ID of View';

--OBJECT_NAME() Example:

SELECT OBJECT_NAME(279672044) AS 'Object name of View', 
OBJECT_NAME(245575913,2) AS 'Object name of Table';


--OBJECT_SCHEMA_NAME Example:

SELECT OBJECT_SCHEMA_NAME(279672044) AS 'Schema object name of View', 
OBJECT_SCHEMA_NAME(245575913,2) AS 'Schema object name of Table';
--SCHEMA_ID() Example:

SELECT SCHEMA_ID() AS 'Schema id', 
SCHEMA_ID('dbo') AS 'Schema id by name';
--SCHEMA_NAME() Example:

SELECT SCHEMA_NAME() AS 'Schema name', 
SCHEMA_NAME(1) AS 'Schema name by id';

--TSQL Configuration Functions

--@@LOCK_TIMEOUT Example

SELECT @@LOCK_TIMEOUT AS Lock_Timeout;

--@@Max_connections Example

SELECT @@SERVERNAME AS 'Server Name', 
@@MAX_CONNECTIONS AS 'Max Connections'; 
--@@SERVERNAME Example

SELECT @@SERVERNAME AS 'Local Server Name';

--@@SERVICENAME Example

SELECT @@SERVICENAME AS 'Instance Service Name';

--@@SPID Example

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login_Name', USER AS 'User_Name';

C# SQL Server TreeView

How To make Tree View Unlimited with Self Join SQlServer

<asp:TreeView ID="TreeView1" runat="server" ImageSet="XPFileExplorer" NodeIndent="15">

<NodeStyle Font-Names="Tahoma" Font-Size="8pt" ForeColor="Black" HorizontalPadding="2px"

NodeSpacing="0px" VerticalPadding="2px"></NodeStyle>

<SelectedNodeStyle BackColor="#B5B5B5" Font-Underline="False" HorizontalPadding="0px"

VerticalPadding="0px" />

</asp:TreeView>

protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
List<BE_Account> lst = new DAL_Account().GETRecord();
List<BE_Account> lstAC = lst;
AddTopTreeViewNodes(lst);
}
}

private void AddTopTreeViewNodes(List<BE_Account> List)
{
List<BE_Account> Listss = List.Where(Id => Id.ControlAccountID == 0).ToList();
foreach (BE_Account Item in Listss)
{
TreeNode newNode = new TreeNode(Item.Description, Item.AccountID.ToString());
TreeView1.Nodes.Add(newNode);
AddChildTreeViewNodes(List, newNode);
newNode.CollapseAll();
}
}

private void AddChildTreeViewNodes(List<BE_Account> List, TreeNode parentTreeViewNode)
{
List<BE_Account> Lsts = List.Where(Id => Id.ControlAccountID == Convert.ToInt32(parentTreeViewNode.Value)).ToList();
foreach (BE_Account Item in Lsts)
{
TreeNode newNode = new TreeNode(Item.Description, Item.AccountID.ToString());
parentTreeViewNode.ChildNodes.Add(newNode);

AddChildTreeViewNodes(List, newNode);
}
}

SQL Server

Self join hierarchy in sql server like Menu GL Acount

;WITH HIERARCHY(Pagetitle, Parentid, ID, FullyQualifiedName, Level)
AS
(
SELECT E.Pagetitle, E.Parentid, E.ID, CAST('.' + E.Pagetitle + '.' as NVarchar(MAX)), 0
FROM pages E
WHERE E.Parentid = 0
UNION ALL
SELECT E.Pagetitle, E.Parentid, E.ID, H.FullyQualifiedName + '.' + E.Pagetitle + '.', H.Level+1
FROM pages E
INNER JOIN HIERARCHY H on H.ID = E.Parentid
)

SELECT case when H.Level=0 then '->'+H.Pagetitle else H.Pagetitle end as Pagetitle, H.Parentid, H.ID, H.Level
from HIERARCHY H
order by H.FullyQualifiedName

Subscribe to: Posts ( Atom )

Contact Form

Enable or Disable all triggers in a Sql server

Dot net Business Entity Create Free Auto using sql server Table

Login failed for user 'IIS APPPOOL\DefaultAppPool' SQl server

Dynamic Sql Qry

100 % Duplicate Data Remove from Table Sql Server

Some Common Function C# Easy Way for Qury Execution with SQL Server

SQL SERVER Function all

How To make Tree View Unlimited with Self Join SQlServer

Self join hierarchy in sql server like Menu GL Acount

Search

Category

Popular Posts