T-SQL Dynamic WHERE and ORDER BY

Posted: July 30, 2008 | Author: Jingyang Li | Filed under: SQL Server | Leave a comment

Answer to this thread: http://forums.asp.net/t/1298204.aspx

Declare @FilterBy nvarchar(50)

Declare @Filter nvarchar(50)

Declare @SortBy nvarchar(50)

Declare @Order nvarchar(4)

SET

@FilterBy=‘Country’

SET

@Filter=‘s’

SET

@SortBy=‘Rating’

SET

@Order=‘ASC’

SELECT

Category, Country, State, City, DateUploaded, Rating, NumRatings, NumViews, NumComments FROM [Photos]

WHERE

(ISNULL(Category,”) = CASE @FilterBy WHEN ‘Category’ THEN @Filter ELSE ISNULL(Category,”) END ) AND

(ISNULL(Country,”) = CASE @FilterBy WHEN ‘Country’ THEN @Filter ELSE ISNULL(Country,”) END ) AND

(ISNULL(State,”) = CASE @FilterBy WHEN ‘State’ THEN @Filter ELSE ISNULL(State,”) END ) AND

(ISNULL(City,”) = CASE @FilterBy WHEN ‘City’ THEN @Filter ELSE ISNULL(City,”) END )

ORDER

CASE

@Order

WHEN

‘ASC’ THEN

CASE

@SortBy

WHEN

‘DateUploaded’ THEN DateUploaded

WHEN ‘Rating’ THEN Rating

WHEN ‘NumRatings’ THEN NumRatings

WHEN ‘NumViews’ THEN NumViews

WHEN ‘NumComments’ THEN NumComments END

END

ASC,

CASE

@Order

WHEN

‘DESC’ THEN

CASE

@SortBy

WHEN

‘DateUploaded’ THEN DateUploaded

WHEN ‘Rating’ THEN Rating

WHEN ‘NumRatings’ THEN NumRatings

WHEN ‘NumViews’ THEN NumViews

WHEN ‘NumComments’ THEN NumComments END

END

DESC

–table script:

CREATE

TABLE [dbo].[Photos](

[Category] [nvarchar]

(255) NULL,

[Country] [nvarchar]

(255) NULL,

[State] [nvarchar]

(255) NULL,

[City] [nvarchar]

(255) NULL,

[DateUploaded] [nvarchar]

(255) NULL,

[Rating] [nvarchar]

(255) NULL,

[NumRatings] [nvarchar]

(255) NULL,

[NumViews] [nvarchar]

(255) NULL,

[NumComments] [nvarchar]

(255) NULL,

[id] [int] Not

NULL

)

Generate date range T-SQL

Posted: July 29, 2008 | Author: Jingyang Li | Filed under: SQL Server 2005 | Leave a comment

I came across this script from one blog to replace a calendar table:

;with mycte as

(

select cast(‘2001-01-01’ as datetime) DateValue

union all

select DateValue + 1 from mycte

where DateValue + 1 < ‘2010-3-31’

)

select

* from mycte

–http://blogs.conchango.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx

I used this script to answer a question here:

http://forums.asp.net/t/1297396.aspx

;with mycte as

(

select cast(‘2000-01-01’ as datetime) DateValue

union all

select DateValue + 1 from mycte

where DateValue + 1 < ‘2010-12-31’

)

SELECT

DatesRange.Name, mycte.dateValue

FROM

mycte CROSS JOIN

DatesRange

WHERE mycte.dateValue BETWEEN DatesRange.FromDT AND DatesRange.ToDT

OPTION (MAXRECURSION 0)

Video clips for VS

Posted: July 25, 2008 | Author: Jingyang Li | Filed under: My IT tools | Leave a comment

I have taken a short break with some very useful clips from this link.

http://www.internet.com/video/visualstudio_archive.php#archive

Dynamically textbox and save data to database with a SqlDataSource

Posted: July 24, 2008 | Author: Jingyang Li | Filed under: ASP.NET 2 | Leave a comment

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="dynamicTextBoxVB.aspx.vb" Inherits="dynamicTextBoxVB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
         <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"
            SelectCommand="SELECT [ID], [Name] FROM [NameForTextBox]" DataSourceMode="DataReader"></asp:SqlDataSource>
  
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"
            DeleteCommand="DELETE FROM [NameForTextBoxForInsert] WHERE [ID] = @ID" InsertCommand="INSERT INTO [NameForTextBoxForInsert] ([fromLabel], [fromTextBox]) VALUES (@fromLabel, @fromTextBox)"
            SelectCommand="SELECT [ID], [fromLabel], [fromTextBox] FROM [NameForTextBoxForInsert]"
            UpdateCommand="UPDATE [NameForTextBoxForInsert] SET [fromLabel] = @fromLabel, [fromTextBox] = @fromTextBox WHERE [ID] = @ID">
            <DeleteParameters>
                <asp:Parameter Name="ID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="fromLabel" Type="String" />
                <asp:Parameter Name="fromTextBox" Type="String" />
                <asp:Parameter Name="ID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="fromLabel" Type="String" />
                <asp:Parameter Name="fromTextBox" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>
        <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
            DataSourceID="SqlDataSource2">
            <Columns>
                <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
                    SortExpression="ID" />
                <asp:BoundField DataField="fromLabel" HeaderText="fromLabel" SortExpression="fromLabel" />
                <asp:BoundField DataField="fromTextBox" HeaderText="fromTextBox" SortExpression="fromTextBox" />
            </Columns>
        </asp:GridView>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Insert data from Textbox" />
    
    </div>
    </form>
</body>
</html>

 Imports System.Data.SqlClient
Imports System.Data
Imports System

Partial Class dynamicTextBoxVB
    Inherits System.Web.UI.Page
    Protected Overloads Overrides Sub OnInit(ByVal e As EventArgs)
        'generate all textbox 

        myfunctionAddTextBox()
        MyBase.OnInit(e)

    End Sub

    Protected Sub myfunctionAddTextBox()
        Dim ctrlTable1 As New Table()
        ctrlTable1.CellPadding = 0
        ctrlTable1.CellSpacing = 1
        ctrlTable1.Attributes.Add("Border", "1")


        Dim reader As System.Data.SqlClient.SqlDataReader = DirectCast(SqlDataSource1.[Select](DataSourceSelectArguments.Empty), System.Data.SqlClient.SqlDataReader)


        ' start 

        While reader.Read()
            Dim tr As New TableRow()


            Dim tc1 As New TableCell()
            Dim lbl1 As New Label()

            lbl1.Attributes.Add("runat", "Server")
            lbl1.EnableViewState = True
            lbl1.Width = 20
            lbl1.ID = "lbl" + reader(0).ToString()

            lbl1.Visible = True
            lbl1.Text = reader(1).ToString()
            '0:ID; 1:Name; 
            tc1.Controls.Add(lbl1)
            tr.Cells.Add(tc1)


            Dim tc2 As New TableCell()
            Dim tb2 As New TextBox()

            tb2.Attributes.Add("runat", "Server")
            ' tb2.EnableViewState = true; 
            tb2.Width = 80
            tb2.ID = "txt" + reader(0).ToString()
            'tb2.ReadOnly = true; 
            tb2.Visible = True


            tc2.Controls.Add(tb2)
            tr.Cells.Add(tc2)


            ctrlTable1.Rows.Add(tr)
        End While


        Me.Form.Controls.Add(ctrlTable1)
    End Sub
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim reader As SqlDataReader = DirectCast(SqlDataSource1.[Select](DataSourceSelectArguments.Empty), SqlDataReader)

        While reader.Read()
            Dim strLabel As String = ""
            Dim strTextBox As String = ""

            Dim myLabel As New Label()
            Dim myLabelId As String = "lbl" + reader(0).ToString()

            Dim txtTextBox As New TextBox()
            Dim myTxtId As String = "txt" + reader(0).ToString()





            txtTextBox = DirectCast(Me.FindControl(myTxtId), TextBox)
            If txtTextBox IsNot Nothing Then

                strTextBox = txtTextBox.Text.ToString()
            Else
                strTextBox = ""
            End If


            myLabel = DirectCast(Me.FindControl(myLabelId), Label)
            If myLabel IsNot Nothing Then

                strLabel = myLabel.Text.ToString()
            Else
                strLabel = ""
            End If



            If strLabel <> "" Then


                SqlDataSource2.InsertParameters("fromLabel").DefaultValue = strLabel
                SqlDataSource2.InsertParameters("fromTextBox").DefaultValue = strTextBox
                'SqlDataSource2.InsertParameters["createdBy"].DefaultValue = User.Identity.Name; 
                'SqlDataSource2.InsertParameters["createdDate"].DefaultValue = DateTime.Now.ToString(); 



                'add data to table2 
                SqlDataSource2.Insert()



                txtTextBox.Text = ""

            End If
        End While
        ' Close the reader 
        reader.Close()


    End Sub

End Class
 
 
 
C#:
public partial class dynamicTextBox : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e){
}
protected override void OnInit(EventArgs e){
//generate all textbox
myfunctionAddTextBox();
base.OnInit(e);}
protected void myfunctionAddTextBox(){
Table ctrlTable1 = new Table();ctrlTable1.CellPadding = 0;
ctrlTable1.CellSpacing = 1;
ctrlTable1.Attributes.Add("Border", "1"); 
SqlDataReader reader = (SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
// start 
while(reader.Read()){
TableRow tr = new TableRow(); 
TableCell tc1 = new TableCell();Label lbl1 = new Label();lbl1.Attributes.Add("runat", "Server");lbl1.EnableViewState = true;lbl1.Width = 20;lbl1.ID = "lbl" + reader[0].ToString();
lbl1.Visible = 
true;lbl1.Text = reader[1].ToString();//0:ID; 1:Name;tc1.Controls.Add(lbl1);
tr.Cells.Add(tc1);
 
TableCell tc2 = new TableCell();TextBox tb2 = new TextBox();tb2.Attributes.Add("runat", "Server");// tb2.EnableViewState = true;tb2.Width = 80;
tb2.ID = 
"txt" + reader[0].ToString();//tb2.ReadOnly = true;tb2.Visible = true;
tc2.Controls.Add(tb2);
tr.Cells.Add(tc2);
ctrlTable1.Rows.Add(tr);
}
reader.Close();
this.Form.Controls.Add(ctrlTable1);}
protected void Button1_Click(object sender, EventArgs e){
SqlDataReader reader = (SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty); 
 
while (reader.Read()){
String strLabel = "";String strTextBox = "";Label myLabel = new Label();String myLabelId = "lbl" + reader[0].ToString();TextBox txtTextBox = new TextBox();
String myTxtId = "txt" + reader[0].ToString(); 
 
 
 txtTextBox = (TextBox)this.FindControl(myTxtId);
if (txtTextBox != null)
{
strTextBox = txtTextBox.Text.ToString();
}
else{
strTextBox = "";}
 
myLabel = (
Label)this.FindControl(myLabelId);if (myLabel != null){
strLabel = myLabel.Text.ToString();
}
else{
strLabel = "";}
 
 
 
 
if (strLabel != ""){
 
SqlDataSource2.InsertParameters["fromLabel"].DefaultValue = strLabel;SqlDataSource2.InsertParameters["fromTextBox"].DefaultValue = strTextBox;
//SqlDataSource2.InsertParameters["createdBy"].DefaultValue = User.Identity.Name;//SqlDataSource2.InsertParameters["createdDate"].DefaultValue = DateTime.Now.ToString();
 
 
//add data to table2SqlDataSource2.Insert();
txtTextBox.Text = 
String.Empty; 
}
}
// Close the readerreader.Close();
 
}
 
}
 

 
 
 
Tables:
CREATE TABLE [dbo].[NameForTextBoxForInsert]([ID] [int] IDENTITY(1,1) NOT NULL,[fromLabel] [nvarchar](50) NULL,
[fromTextBox] [nvarchar]
(50) NULL) 
CREATE TABLE [dbo].[NameForTextBox](
[ID] [int] NULL,[Name] [nvarchar](50) NULL
)

Dynamically generate a table with UNPIVOT operation

Posted: July 24, 2008 | Author: Jingyang Li | Filed under: SQL Server 2005 | Leave a comment

In SQL Server 2005, you can use UNPIVOT to convert your table to a normalized form(table) like:

Table:

CREATE TABLE [dbo].[PW_Table](

[DateInt] [int]

NULL,

[Meter_1] [float]

NULL,

[Meter_2] [float] NULL,

[Meter_3] [float]

NULL,

[Meter_4] [float]

NULL,

[Meter_5] [float] NULL,

[Meter_6] [float]

NULL,

[Meter_7] [float]

NULL,

[myDate] [datetime] NULL

)

ON [PRIMARY]

–Query with UNPIVOT

SELECT   [dateInt], MeterID, ReadingValue FROM 
(SELECT   [dateInt], [Meter_1], [Meter_2], [Meter_3], [Meter_4], [Meter_5], [Meter_6], [Meter_7]
FROM         PW_Table) t1 
UNPIVOT 
(ReadingValue For MeterID
IN ([Meter_1], [Meter_2], [Meter_3], [Meter_4], [Meter_5], [Meter_6], [Meter_7])) as unPvt

You want this script to be more flexiable to include unknown number of columns. We can use dynamic query to do this:

 SET
NOCOUNT ON
DECLARE
@T AS TABLE(y nvarchar(20) NOT NULL PRIMARY KEY)

INSERT
INTO @T SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='PW_Table' and Column_Name Like 'Meter%'

DECLARE
@T1 AS TABLE(num int NOT NULL PRIMARY KEY)

DECLARE @i AS int

SET @i=1

 

WHILE @i <20

BEGIN

INSERT INTO @T1 SELECT @i

SET @i=@i+1

END


DECLARE @cols AS nvarchar(MAX), @cols2 AS nvarchar(MAX),@y AS nvarchar(20)

SET @y = (SELECT MIN(y) FROM @T)

SET @cols = N''
SET @cols2 = N''

WHILE @y IS NOT NULL

BEGIN

SET @cols = @cols + N',['+CAST(@y AS nvarchar(20))+N']' 


SET @y = (SELECT MIN(y) FROM @T WHERE y > @y)

END

SET @cols = SUBSTRING(@cols, 2, LEN(@cols))
 

DECLARE @sql AS nvarchar(MAX)

SET  @sql = N'SELECT dateInt, MeterID, ReadingValue FROM (SELECT dateInt, ' +@cols + N' FROM   PW_Table) as t
UNPIVOT 
(ReadingValue For MeterID
IN (' + @cols + N')) AS unPvt'

EXEC sp_executesql @sql

Truncate tables with foreign key between them

Posted: July 2, 2008 | Author: Jingyang Li | Filed under: SQL Server | Leave a comment

How to truncate tables with foreign key linked with each other?

You can drop the Foreign Key before you do theTRUNCATE and add it back to the table after you are done. By the way, the TRUNCATE action is logged but it is just not the same way as the DELETE action. You can roll back a truncate in a transaction with the page logging.

Here is a simple sample for the question:

–Drop the foreign key ALTER TABLE dbo.CategoryTable1Sub

DROP CONSTRAINT FK_CategoryID

truncate

table dbo.CategoryTable1

truncate table dbo.CategoryTable1Sub

–Add Foreign key back

ALTER

TABLE dbo.CategoryTable1Sub ADD CONSTRAINT

FK_CategoryID

FOREIGN KEY

(

CatID )REFERENCES dbo.CategoryTable1

( Category_ID )

Dynamically change ORDER BY column and direction

Posted: July 2, 2008 | Author: Jingyang Li | Filed under: SQL Server | Leave a comment

Declare @Ordered varchar(20)
Declare
 @AscDesc varchar(5)--Declare @mystring varchar(25)SET @Ordered ='num1'SET @AscDesc='ASC';WITH Publication AS(SELECT col1, col2, col3, num1, ROW_NUMBER() OVER(ORDER BY CASE @AscDescWHEN 'ASC' THENCASE @Ordered WHEN 'col1' THEN col1 WHEN 'col2' THEN col2WHEN 'col3' THEN col3WHEN 'num1' THEN CONVERT(VARCHAR(50),num1)WHEN 'Date' THEN CONVERT(varchar,PublicationDate,112)END
END
 ASC,CASE @AscDescWHEN 'DESC' THENCASE @Ordered WHEN 'col1' THEN col1WHEN 'col2' THEN col2WHEN 'col3' THEN col3WHEN 'num1' THEN CONVERT(VARCHAR(50),num1)WHEN 'Date' THEN CONVERT(varchar,PublicationDate,112)END END DESC) AS RowNum, idFROM [aTable])select * from Publication

Pivot data not from SQL Server 2005

Posted: July 1, 2008 | Author: Jingyang Li | Filed under: SQL Server | Leave a comment

It is a happy journey to work the new functions in SQL Server 2005. I have played quite a lit with pivot functions in both static and dynamic ways. But the right place for doing this pivot should be in the application.

Jeff presented a good example in his blog:http://weblogs.sqlteam.com/jeffs/archive/2005/05/11/5101.aspx

with a customized PIVOT function: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx

A simple test with GridView and SqlDataSource:

Page:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"

SelectCommand="SELECT [RecordNo], [SessionNo], [FluencyNo], [FluencyScore] FROM [pivotData3$]" DataSourceMode="DataReader">

</asp:SqlDataSource>

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="true"></asp:GridView>

Code:

protected

void Page_Load(object sender, EventArgs e)

{

GridView2.DataSource =

PivotFront.Pivot((SqlDataReader)SqlDataSource2.Select(DataSourceSelectArguments.Empty), "SessionNo", "FluencyNo", "FluencyScore");

GridView2.DataBind();

}

Jingyang Li

SQL Server

T-SQL Dynamic WHERE and ORDER BY

Generate date range T-SQL

Video clips for VS

Dynamically textbox and save data to database with a SqlDataSource

Dynamically generate a table with UNPIVOT operation

Truncate tables with foreign key between them

Dynamically change ORDER BY column and direction

Pivot data not from SQL Server 2005

Recent Posts

Archives

Categories

Meta