Saturday, 25 June 2016

Table Structure Acces in Database

Unknown 01:10 StoredProcedures No comments

Table Structure Get From Database in Sql Server

If We Want To get Any Table Structure from Database .
Show How Many Column Created and Datatype also Then Use This Code

Create Procedure in Sql Server

CREATE Procedure dbo.Gettable
@tablename nvarchar(100)
as
    DECLARE
           @object_name SYSNAME ,
          @object_id INT
        , @SQL NVARCHAR(MAX)

    SELECT
          @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
        , @object_id = [object_id]
    FROM (SELECT [object_id] = OBJECT_ID(@tablename, 'U')) o

    SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
        SELECT CHAR(13) + '    , [' + c.name + '] ' +
            CASE WHEN c.is_computed = 1
                THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
                ELSE
                    CASE WHEN c.system_type_id != c.user_type_id
                        THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'
                        ELSE '[' + UPPER(tp.name) + ']'
                    END +
                    CASE
                        WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
                            THEN '(' + CASE WHEN c.max_length = -1
                                            THEN 'MAX'
                                            ELSE CAST(c.max_length AS VARCHAR(5))
                                        END + ')'
                        WHEN tp.name IN ('nvarchar', 'nchar')
                            THEN '(' + CASE WHEN c.max_length = -1
                                            THEN 'MAX'
                                            ELSE CAST(c.max_length / 2 AS VARCHAR(5))
                                        END + ')'
                        WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
                            THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                        WHEN tp.name = 'decimal'
                            THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                        ELSE ''
                    END +
                    CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id
                        THEN ' COLLATE ' + c.collation_name
                        ELSE ''
                    END +
                    CASE WHEN c.is_nullable = 1
                        THEN ' NULL'
                        ELSE ' NOT NULL'
                    END +
                    CASE WHEN c.default_object_id != 0
                        THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +
                             ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
                        ELSE ''
                    END +
                    CASE WHEN cc.[object_id] IS NOT NULL
                        THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
                        ELSE ''
                    END +
                    CASE WHEN c.is_identity = 1
                        THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +
                                        CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'
                        ELSE ''
                    END
            END
        FROM sys.columns c WITH(NOLOCK)
        JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
        LEFT JOIN sys.check_constraints cc WITH(NOLOCK)
             ON c.[object_id] = cc.parent_object_id
            AND cc.parent_column_id = c.column_id
        WHERE c.[object_id] = @object_id
        ORDER BY c.column_id
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, '      ') +
        ISNULL((SELECT '
        , CONSTRAINT [' + i.name + '] PRIMARY KEY ' +
        CASE WHEN i.index_id = 1
            THEN 'CLUSTERED'
            ELSE 'NONCLUSTERED'
        END +' (' + (
        SELECT STUFF(CAST((
            SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +
                    CASE WHEN ic.is_descending_key = 1
                        THEN ' DESC'
                        ELSE ''
                    END
            FROM sys.index_columns ic WITH(NOLOCK)
            WHERE i.[object_id] = ic.[object_id]
                AND i.index_id = ic.index_id
            FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'
        FROM sys.indexes i WITH(NOLOCK)
        WHERE i.[object_id] = @object_id
            AND i.is_primary_key = 1), '') + CHAR(13) + ');'

    PRINT @SQL

Use This Code We Show table Structure like

SELECT * FROM tbl_Acc_Type
After Execution We get Table

ID    AccType
1   Payment
2   Receipt

We Use table Structure Then Use this Procedure To Directaly Show Structure
Syntax- GetTable TableName
Ex.-   GetTable tbl_Acc_Type

After Execution We get Table Structure

CREATE TABLE [dbo].[tbl_Acc_Type]
(
      [ID] [INT] NOT NULL IDENTITY(1,1)
    , [AccType] [NVARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
        , CONSTRAINT [PK_tbl_Acc_Type] PRIMARY KEY CLUSTERED ([ID])
);

We Will Create This Table in Other Database and use Structure Also

Unicque Row Num and Same Rank in Sql server

Unknown 00:18 Function No comments

Use Of Row_Number() ,Rank() function , Dense_Rank()

Row_Number()

This function will assign a unique id to each row returned from the query.

Consider the following query:

DECLARE @Table TABLE (
      Col_Value varchar(2)
)

INSERT INTO @Table (Col_Value)
      VALUES ('A'),('A'),('A'),('B'),('B'),('C'),('C');

SELECT
      Col_Value,
      ROW_NUMBER() OVER (ORDER BY Col_Value) AS 'Row ID'
FROM
      @Table;

After executing it, we will get:

Col_Value Row ID
     A    1
   A    2
   A    3
   B    4
   B    5
   C    6
   C    7
As we notice, each and every row has a unique ID.

Rank()

This function will assign a unique number to each distinct row, but it leaves a gap between the groups. Let me explain with a query, we will use the same query we used above with Rank().

SELECT
      Col_Value,
      Rank() OVER (ORDER BY Col_Value) AS 'Row ID'
FROM
      @Table;

After executing it, we will get:

Col_Value Row ID
     A    1
     A    1
     A    1
     B    4
     B    4
     C    6
     C    6

As we can see,rowid is unique for each distinct value, but with a gap. What is this gap?
This gap represents number of occurrence. For example: value a is repeated thrice and has rank 1, the next rank will be 1+3=4. Same with the next value 4+2=6 and so on.

Dense_Rank()

his function is similar to Rank with only difference, this will not leave gaps between groups.
So if we use the same query used for Rank, then:

SELECT
      Col_Value,
      DENSE_RANK() OVER (ORDER BY Col_Value) AS 'Row ID'
FROM
      @Table;

 After executing it, we will get:

Col_Value Row ID
        A     1
        A     1
        A     1
        B     2
        B     2
        C     3
        C     3

	So it is clear that it generates a unique id for each group and without repetition. As we are clear now what these functions do, we can use them in different scenarios based our need. For example: `Row_Number()` can be used if we want to skip and fetch some records. Take a look at this query:

	Similarly, we can use Dense_Rank()when we need to calculate row id with Select Distinct.
	SELECT DISTINCT Col_Value, DENSE_RANK() OVER (ORDER BY Col_Value) AS 'Row ID' FROM @Table; Will return:-
	Col_Value Row ID A 1 B 2 C 3

COALESCE function

Unknown 23:49 Function No comments

Concatenate many rows into a single text string using SQL Server
Using COALESCE function-

What is COALESCE function-
Concatenate many rows into a single text string with comma separated using SQL Server COALESCE function.

My table format is like this:
(Select Name from Tbl_Customer)

Name
Jeet
Nitin
Pulkit
Rahul
Vibha

I need output like this:
Name
1. Jeet ,Nitin,Rahul,Pulkit,Vibha

Use This Code
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name(ColumnName)
FROM Tbl_Customer
select @Names

Rank Given In Table

Unknown 23:07 Queries No comments

Rank Given in Table in SQL Server
If We Want to Give Indviual Rank And Same Rank to Same data Then Use

Use Of ROW_NUMBER() Function
1.This function will assign a unique id to each row returned from the query.
2.RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK()
Trivially, DENSE_RANK() is a rank with no gaps, i.e. it is “dense”. We can write:

Code
Create table-
CREATE TABLE Tbl_CityMaster1
(
Id INT PRIMARY KEY IDENTITY,
CityName nvarchar(20),
C_Id int
)
Data Insertd Then OutPut
OutPut-
Id CityName   C_Id
1    Allahabad    1
2    Allahabad   1
3    Allahabad    1
4    Patna        1
5    Patna        2
6    Patna        3
7    Varanasi    3
8    Varanasi    3
9    Varanasi    3
10    Varanasi    3

Give Rank

Query-
SELECT
    Id,CityName,[C_id],
    Row_index = ROW_NUMBER() OVER(PARTITION BY [C_Id] ORDER BY [C_Id]),
    SameRank = DENSE_RANK() OVER (ORDER BY [C_Id])
FROM Tbl_CityMaster

OutPut

Id CityName C_Id RowNo SameRank
1    Allahabad    1    1 1
2    Allahabad    1    2      1
3    Allahabad    1    3      1
4    Patna       1    2     2
5    Patna         2    2     2
6    Patna       3   2      2
7    Varanasi      3     1      3
8    Varanasi      3     2      3
9    Varanasi      3   3      3
10    Varanasi 3 4       3

Create Table

Unknown 22:54 Queries No comments

Create Table In Sql Server

Syntax-

CREATE TABLE TableName
(
ColumnName1 INT PRIMARY KEY IDENTITY,
ColumnName2,
ColumnName3
)

Ex.
CREATE TABLE Tbl_CityMaster
(
Id INT PRIMARY KEY IDENTITY,
CityName nvarchar(20),
C_Id int
)

Show Table Data

Unknown 22:25 Queries No comments

This IS Code For Show Table Data..
If We Want to Get Data Inserted in Any Table And Copy then use this Code in SQL Server

Syntax-
DECLARE @Fields VARCHAR(max); SET @Fields = '[ColumnName1], [ColumnName2], [ColumnName3]' -- your fields, keep []
DECLARE @Table VARCHAR(max); SET @Table = 'Table Name'   -- your Table Name
DECLARE @SQL    VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) +
''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max))
+ ''''''''') +' FROM ' + @Table + '
PRINT @S'
EXEC (@SQL)

Ex.-
DECLARE @Fields VARCHAR(max); SET @Fields = '[id], [Waiver], [isactive]' -- your fields, keep []
DECLARE @Table VARCHAR(max); SET @Table = 'plot_Waiver'               -- your table
DECLARE @SQL    VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) +
''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max))
+ ''''''''') +' FROM ' + @Table + '
PRINT @S'
EXEC (@SQL)
OutPut-

INSERT INTO plot_Waiver([id], [Waiver], [isactive])
SELECT '1', '40.00', '1' UNION
SELECT '2', '25.00', '1' UNION
SELECT '4', '20.00', '1' UNION
SELECT '5', '45.00', '1'

Use SQL Server Functions

Unknown 02:27 Function No comments

SQL Aggregate Functions
This functions return a single value, calculated from values in a column.
Like Ex.-

AVG() - Returns the average value.
    Syntax - SELECT AVG(column_name) FROM table_name
COUNT() - Returns the number of rows.
     Syntax - SELECT COUNT(column_name) FROM table_name
FIRST() - Returns the first value.
    Syntax - SELECT FIRST(column_name) FROM table_name
LAST() - Returns the last value.
    Syntax - SELECT LAST(column_name) FROM table_name
MAX() - Returns the largest value.
    Syntax - SELECT MAX(column_name) FROM table_name
MIN() - Returns the smallest value.
   Syntax - SELECT MIN(column_name) FROM table_name
SUM() - Returns the sum.
    Syntax - SELECT SUM(column_name) FROM table_name

SQL Scalar functions
This Functions return a single value, based on the input value.
Ex.

UCASE() - Converts a field to upper case
        Syntax - SELECT UCASE(column_name) FROM table_name;
   LCASE() - Converts a field to lower case
        Syntax - SELECT UCASE(column_name) FROM table_name;
   MID() - Extract characters from a text field.
        Syntax - SELECT MID(column_name,start,length) AS some_name FROM table_name;
   LEN() - Returns the length of a text field
       Syntax - SELECT LEN(column_name) FROM table_name;
   ROUND() - Rounds a numeric field to the number of decimals specified
         Syntax - SELECT ROUND(column_name,decimals) FROM table_name;
   NOW() - Returns the current system date and time
        Syntax - SELECT NOW() FROM table_name;
    FORMAT() - Formats how a field is to be displayed
        Syntax - SELECT FORMAT(column_name,format) FROM table_name;

CSS 3D Transforms

Unknown 22:08 css No comments

<!DOCTYPE html>

<head>

.container{

/* How pronounced should the 3D effects be */

perspective:800px;

-webkit-perspective:800px;

background: radial-gradient(#e0e0e0, #aaa);

width:350px;

height:380px;

margin:0 auto;

border-radius:6px;

position:relative;

}

.iphone-front,

.iphone-back{

/* Enable 3D transforms */

transform-style: preserve-3d;

-webkit-transform-style: preserve-3d;

/* We are using two separate divs for the front and back of the

phone. This will hide the divs when they are flipped, so that the

opposite side can be seen: */

backface-visibility: hidden;

-webkit-backface-visibility: hidden;

width:200px;

height:333px;

position:absolute;

top:50%;

left:50%;

margin:-166px 0 0 -100px;

background:url(http://demo.tutorialzine.com/2013/10/css3-features-you-can-finally-use/assets/img/iphone.png) no-repeat left center;

/* Animate the transitions */

/*transition:0.8s;*/

transition:1.5s;

}

.iphone-back{

/* The back side is flipped 180 deg by default */

transform:rotateY(180deg);

-webkit-transform:rotateY(180deg);

background-position:right center;

}

.container:hover .iphone-front{

/* When the container is hovered, flip the front side and hide it .. */

transform:rotateY(180deg);

-webkit-transform:rotateY(180deg);

}

.container:hover .iphone-back{

/* .. at the same time flip the back side into visibility */

transform:rotateY(360deg);

-webkit-transform:rotateY(360deg);

}

</style>

</head>

<body>

</div>

</body>

</html>

Current Date

Unknown 00:02 Function No comments

Show Current Date-

I Want To Show Current Date Then Use-
SELECT GETDATE() AS CurrentDate
OutPut-
           2016-06-08 12:11:00.083
More Format In Date Then Use-
Ex. 1:
SELECT CONVERT(varchar(20),GETDATE(),100) AS CurrentDate
OutPut-
           Jun 8 2016 12:13PM
Ex.2:
SELECT CONVERT(varchar(20),GETDATE(),101) AS CurrentDate
OutPut
       06/08/2016
Ex.3:
SELECT CONVERT(varchar(20),GETDATE(),102) AS CurrentDate
OutPut
      2016.06.08
Ex.4:
SELECT CONVERT(varchar(20),GETDATE(),103) AS CurrentDate
OutPut
     08/06/2016
Ex.5
CONVERT(varchar(20),GETDATE(),104) = 08.06.2016.
CONVERT(varchar(20),GETDATE(),105 )= 08-06-2016
CONVERT(varchar(20),GETDATE(),106) = 08 Jun 2016
CONVERT(varchar(20),GETDATE(),107) = Jun 08, 2016
CONVERT(varchar(20),GETDATE(),108) = 12:24:08
CONVERT(varchar(20),GETDATE(),109) = Jun 08, 2016
CONVERT(varchar(20),GETDATE(),110) = 06-08-2016
CONVERT(varchar(20),GETDATE(),111) = 2016/06/08
CONVERT(varchar(20),GETDATE(),112) = 20160608
CONVERT(varchar(20),GETDATE(),113) = 08 Jun 2016 12:26:08

ONLY FATCH TIME IN DATE THEN USE
EX.1
SELECT (LTRIM( RIGHT(CONVERT(varchar(20),GETDATE(),100),7))) as CURRENTTime
OutPut
   12:28PM

Ajax FilteredTextBox Extender

Unknown 00:11 Ajax No comments

How To Use FilterTextBoxIn Asp.net.
SOURCE OF PAGE
Firstly Add Directory on page
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
Only Restrict For Upper case
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</asp:ToolkitScriptManager>
<asp:TextBox ID="txtUpperCase" runat="server"/>
<asp:FilteredTextBoxExtender ID="UpperCase" runat="server" TargetControlID="txtUpperCase"        FilterType="UppercaseLetters"> </asp:FilteredTextBoxExtender>
OutPut-
Show UpperCase Letetr In Text box.
Only Restrict For Lower case
<asp:TextBox ID="txtLowerCase" runat="server"/>
<asp:FilteredTextBoxExtender ID="LowerCase" runat="server" TargetControlID="txtLowerCase"
FilterType="LowercaseLetters"> </asp:FilteredTextBoxExtender>
OutPut-
    Show LowerCase Letetr In Text box .

Only Restrict For Numeric Value
<asp:TextBox ID="txtNumbers" runat="server"/
<asp:FilteredTextBoxExtender ID="Numbers" runat="server" TargetControlID="txtNumbers"     FilterType="Numbers" </asp:FilteredTextBoxExtender>
OutPut-
Show Numbers In Text box
To Restrict certain or special characters
<asp:TextBox ID="TextBox1" runat="server"/>
<asp:FilteredTextBoxExtender ID="Custome" runat="server" TargetControlID="TextBox1"
   FilterMode="InvalidChars" InvalidChars="!@#$%^&*()~?><|\';:">
</asp:FilteredTextBoxExtender>
OutPut-
InvalidChar Not Enter In Text box.We Can Give Valid Character Same like As Ex.

DotNet Solution

Saturday, 25 June 2016

Table Structure Acces in Database

Unicque Row Num and Same Rank in Sql server

Friday, 24 June 2016

COALESCE function

Wednesday, 15 June 2016

Rank Given In Table

Create Table

Show Table Data

Monday, 13 June 2016

Use SQL Server Functions

Friday, 10 June 2016

CSS 3D Transforms

Wednesday, 8 June 2016

Current Date

Tuesday, 7 June 2016

Ajax FilteredTextBox Extender

Popular Posts

Blog Archive

Categories

Text Widget

Sample Text

Pages