Show SQLDataSource ‘s SQL statement in ASP.NET

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

Long long time ago, we can write Response.write(strSQL) to see what has been passed to database in ASP page. Since ASP.NET 2.0, SQLDataSource has been doing alot behind the scene. In case, we are not able to view the SQL statement in SQL Profiler (which is a prefered method to me), here is a code sample to show you how to do it in SQLDataSource’s inserting/updating events.

C#:

protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)

{

SqlParameterCollection myCollection = (SqlParameterCollection)e.Command.Parameters;

IEnumerator ie = myCollection.GetEnumerator();

string strSQL = string.Empty;

while ((ie.MoveNext())) {

SqlParameter param = (SqlParameter)ie.Current;

string strValue = string.Empty;

if (param.Value ==null)

strValue =

"NULL";

else

strValue = param.Value.ToString();

strSQL += param.ParameterName.ToString() +

"=" + strValue + "<BR />";

}

Response.Write(strSQL.ToString());

}

VB.NET:

Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating

Dim myCollection As SqlParameterCollection = e.Command.Parameters()

Dim ie As IEnumerator = myCollection.GetEnumerator

Dim strSQL As String = ""

While (ie.MoveNext())

Dim param As System.Data.SqlClient.SqlParameter = ie.Current()

strSQL += param.ParameterName() &

"=" & param.Value() & "<BR />"

End While

Response.Write(strSQL.ToString())

End Sub

View SQLDataSource’s SQL statement in ASP.NET

Posted: December 30, 2008 | Author: Jingyang Li | Filed under: ASP.NET | Leave a comment

It is pretty easy to view what has been sent to SQL Server in SQL Profiler within SSMS. In case you want to access this information directly in your ASP.NET page for the SQLDataSource you are using, you can work within -ING events of SQLDataSouce to view this information. Here is an example with OnInserting event:

VB.NET:

Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Inserting

Dim mystring As String = e.Command.CommandText.ToString()

Dim i As Integer = 0

For i = 0 To e.Command.Parameters.Count – 1

mystring +=

"<Br />" + e.Command.Parameters(i).ToString() + ": " + e.Command.Parameters(i).Value.ToString()

Me.lblDebug.Text = mystring.ToString

End Sub

C#:

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)

{

string mystring=e.Command.CommandText.ToString();

for (int i = 0; i < e.Command.Parameters.Count; i++)

mystring += "<Br />" + e.Command.Parameters[i].ToString()
+ ": " + e.Command.Parameters[i].Value.ToString();

Response.Write(mystring);

}

Remove icon border shadows on the desktop

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

This link is for how to fix this on XP: http://www.mvps.org/marksxp/WindowsXP/boarders.php

It is the same procedure for Windows 2003 Server. Uncheck ‘Lock web items on desktop’ under Arrange Icon By (right click on desktop open space); Right click on your my computer icon and view its properties>>advanced tab >>performance–Settings>>Visual effects tab >> Check "use drop shadows for icon labels on desktop" >>Click OK.

FilterExpression with multiple columns in SQLDataSource

Posted: December 17, 2008 | Author: Jingyang Li | Filed under: ASP.NET 2 | 2 Comments

Update:

–***

By assigning default values to controlparameters, we can make FilterExpresssin work with multiple columns with this syntax:

FilterExpression="(UserName =’{0}’ or ‘{0}’ =’-1′) AND (LiveMonth={1} or {1}=-1) AND (LiveYear={2} or {2}=-1) "

along with a default value -1 for all controlparameters.

–****

It is pretty easy to use a FilterExpression with SqlDataSource if you choose to filter the data after dataset returned through SELECT command. Normally, I would recommend to filter data from database call with a SelectParameter. When you decide to use multiple filterparameters, it seems that it will not that hard to add another FilterParameter to FilterParameters collection. However, after you add another parameter to the FilterExpression, the filtering simply stops working.

There is no syntax error for this behavior. A work around for this is to use some code to handle the fileterparameters andFilterExpression of the SqlDataSource.

Here is a working sample with three dropdownlist. The code is hooked to dropdownlist’s SelectedIndexChanged event.

protected void ddls_SelectedIndexChanged(object sender, System.EventArgs e)
    {
        bool bUsers = string.IsNullOrEmpty(ddlUsers.SelectedValue);
        bool bMonth = string.IsNullOrEmpty(ddlMonth.SelectedValue);
        bool bYear = string.IsNullOrEmpty(ddlYear.SelectedValue);
        SqlDataSource1.FilterParameters.Clear();
        SqlDataSource1.FilterExpression = "";
        if (bUsers & bMonth & bYear){}
        else if (!bUsers & bMonth & bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("UserName", TypeCode.String, "ddlUsers", "SelectedValue"));
            SqlDataSource1.FilterExpression = "UserName=’{0}’";
        }
        else if (bUsers & !bMonth & bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveMonth", TypeCode.Int32, "ddlMonth", "SelectedValue"));
            SqlDataSource1.FilterExpression = "LiveMonth={0}";
        }

        else if (bUsers & bMonth & !bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveMonth", TypeCode.Int32, "ddlYear", "SelectedValue"));
            SqlDataSource1.FilterExpression = "LiveYear={0}";
        }

        else if (!bUsers & bMonth & !bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("UserName", TypeCode.String, "ddlUsers", "SelectedValue"));
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveYear", TypeCode.Int32, "ddlYear", "SelectedValue"));
            SqlDataSource1.FilterExpression = "UserName=’{0}’ AND LiveYear={1}";
        }

        else if (!bUsers & !bMonth & bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("UserName", TypeCode.String, "ddlUsers", "SelectedValue"));
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveMonth", TypeCode.Int32, "ddlMonth", "SelectedValue"));
            SqlDataSource1.FilterExpression = "UserName=’{0}’ AND LiveMonth={1}";
        }
        else if (bUsers & !bMonth & !bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveMonth", TypeCode.Int32, "ddlMonth", "SelectedValue"));
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveYear", TypeCode.Int32, "ddlYear", "SelectedValue"));
            SqlDataSource1.FilterExpression = "LiveMonth={0} AND LiveYear={1}";
        }

        else if (!bUsers & !bMonth & !bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("UserName", TypeCode.String, "ddlUsers", "SelectedValue"));
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveMonth", TypeCode.Int32, "ddlMonth", "SelectedValue"));
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveYear", TypeCode.Int32, "ddlYear", "SelectedValue"));

SqlDataSource1.FilterExpression = "UserName=’{0}’ AND LiveMonth={1} AND LiveYear={2}";

}
}

The aspx page looks like this:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id"
          DataSourceID="SqlDataSource1">
          <Columns>
          <asp:BoundField DataField="LiveMonth" HeaderText="LiveMonth" SortExpression="LiveMonth" />
              <asp:BoundField DataField="dob" HeaderText="dob" SortExpression="dob" />
              <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
                  SortExpression="id" />
              <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
          </Columns>
      </asp:GridView>
      <asp:SqlDataSource ID="SqlDataSource1" runat="server"
      ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"
       CancelSelectOnNullParameter="true"
          SelectCommand="SELECT dob, name, LiveYear, LiveMonth, [id], UserName FROM [Dates4] " >

                      </asp:SqlDataSource>
      <asp:DropDownList ID="ddlUsers" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddls_SelectedIndexChanged">
      <asp:ListItem Value="">All</asp:ListItem>
      <asp:ListItem Value="aaa">aaa</asp:ListItem>
        <asp:ListItem Value="bbb">bbb</asp:ListItem>
          <asp:ListItem Value="ccc">ccc</asp:ListItem>
       <asp:ListItem Value="ddd">ddd</asp:ListItem>
       <asp:ListItem Value="xxx">xxx</asp:ListItem>
          </asp:DropDownList>
      <asp:DropDownList ID="ddlMonth" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddls_SelectedIndexChanged">
              <asp:ListItem Value="">All</asp:ListItem>
      <asp:ListItem Value="1">1</asp:ListItem>
        <asp:ListItem Value="2">2</asp:ListItem>
          <asp:ListItem Value="3">3</asp:ListItem>
             <asp:ListItem Value="4">4</asp:ListItem>
        <asp:ListItem Value="5">5</asp:ListItem>
          <asp:ListItem Value="6">6</asp:ListItem>
           <asp:ListItem Value="7">7</asp:ListItem>
            <asp:ListItem Value="8">8</asp:ListItem>
             <asp:ListItem Value="9">9</asp:ListItem>
              <asp:ListItem Value="10">10</asp:ListItem>
               <asp:ListItem Value="11">11</asp:ListItem>
                <asp:ListItem Value="12">12</asp:ListItem>
      </asp:DropDownList>
<asp:DropDownList ID="ddlYear" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddls_SelectedIndexChanged">
                     <asp:ListItem Value="">All</asp:ListItem>
      <asp:ListItem Value="2007">2007</asp:ListItem>
        <asp:ListItem Value="2008">2008</asp:ListItem>
          <asp:ListItem Value="2009">2009</asp:ListItem>
      </asp:DropDownList>

Edit:

Sample aspx page::

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id"

DataSourceID="SqlDataSource1">

<asp:BoundField DataField="LiveMonth" HeaderText="LiveMonth" SortExpression="LiveMonth" />

<asp:BoundField DataField="dob" HeaderText="dob" SortExpression="dob" />

<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"

SortExpression="id" />

<asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"

SelectCommand="SELECT dob, name, LiveYear, LiveMonth, [id], UserName FROM [Dates4] "

FilterExpression="(UserName =’{0}’ or ‘{0}’ =’-1′) AND (LiveMonth={1} or {1}=-1) AND (LiveYear={2} or {2}=-1) " >

FilterParameters>

asp:ControlParameter ControlID="ddlUsers" Name="UserName" PropertyName="SelectedValue" Type="String" DefaultValue="-1" />

asp:ControlParameter ControlID="ddlMonth" Name="LiveMonth" PropertyName="SelectedValue" Type="Int32" DefaultValue="-1" />

asp:ControlParameter ControlID="ddlYear" Name="LiveYear" PropertyName="SelectedValue" Type="Int32" DefaultValue="-1" />

FilterParameters>

</asp:SqlDataSource>

<asp:DropDownList ID="ddlUsers" runat="server" AutoPostBack="true" >

<asp:ListItem Value="-1">All</asp:ListItem>

<asp:ListItem Value="aaa">aaa</asp:ListItem>

<asp:ListItem Value="bbb">bbb</asp:ListItem>

<asp:ListItem Value="ccc">ccc</asp:ListItem>

<asp:ListItem Value="ddd">ddd</asp:ListItem>

<asp:ListItem Value="xxx">xxx</asp:ListItem>

</asp:DropDownList>

<asp:DropDownList ID="ddlMonth" runat="server" AutoPostBack="true" >

<asp:ListItem Value="-1">All</asp:ListItem>

<asp:ListItem Value="1">1</asp:ListItem>

<asp:ListItem Value="2">2</asp:ListItem>

<asp:ListItem Value="3">3</asp:ListItem>

<asp:ListItem Value="4">4</asp:ListItem>

<asp:ListItem Value="5">5</asp:ListItem>

<asp:ListItem Value="6">6</asp:ListItem>

<asp:ListItem Value="7">7</asp:ListItem>

<asp:ListItem Value="8">8</asp:ListItem>

<asp:ListItem Value="9">9</asp:ListItem>

<asp:ListItem Value="10">10</asp:ListItem>

<asp:ListItem Value="11">11</asp:ListItem>

<asp:ListItem Value="12">12</asp:ListItem>

</asp:DropDownList>

<asp:DropDownList ID="ddlYear" runat="server" AutoPostBack="true" >

<asp:ListItem Value="-1">All</asp:ListItem>

<asp:ListItem Value="2007">2007</asp:ListItem>

<asp:ListItem Value="2008">2008</asp:ListItem>

<asp:ListItem Value="2009">2009</asp:ListItem>

</asp:DropDownList>

Search Special character in SQL Server

Posted: December 9, 2008 | Author: Jingyang Li | Filed under: SQL Server | Leave a comment

DECLARE @t TABLE (col1 varchar(50))
INSERT INTO @t (col1) VALUES (‘X/BCD11’)
INSERT INTO @t (col1) VALUES (‘D#FDR12’)
INSERT INTO @t (col1) VALUES (‘WB$CD13’)
INSERT INTO @t (col1) VALUES (‘CD EF13’)
INSERT INTO @t (col1) VALUES (‘XBCD13’)
INSERT INTO @t (col1) VALUES (‘A|CDE3’)

SELECT col1, Flag = CASE
    WHEN col1 like ‘%[/ #$\|]%’
    THEN ‘Found Special character!’
    ELSE ‘Normal’
    END,
    Flag2 = CASE
    WHEN PATINDEX(‘%[/ #$\|]%’, col1) > 0
    THEN ‘Found Special character!’
    ELSE ‘Normal’
    END
FROM @t

Script table data in SQL Server 2008

Posted: December 4, 2008 | Author: Jingyang Li | Filed under: SQL Server 2008 | Leave a comment

When you script your database objects, the default setting for script data for a table is false. You can change the setting to true to script your table data.

Right click on your database>>Tasks>>Generates Scripts… (Load the Wizard)>> click NEXT>>Choose database and click NEXT>>within Choose Script Options window, scroll down to Table/View options section and change Script data option to True.>>Next to choose table to script>> click NEXT and NEXT to clcik the Finish

button. You will have your table along with the data in a script.

Chart controls from Microsoft for VS 2008

Posted: December 1, 2008 | Author: Jingyang Li | Filed under: ASP.NET 3.5 | Leave a comment

Finally, there is addon Chart control from Microsoft fro Visual Studion 2008. You can download the Chart Controls from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=130f7986-bf49-4fe5-9ca8-910ae6ea442c&DisplayLang=en

(Microsoft Chart Controls for Microsoft Framework 3.5)

and http://www.microsoft.com/downloads/details.aspx?familyid=1D69CE13-E1E5-4315-825C-F14D33A303E9&displaylang=en

(Microsoft Chart Controls Add-on for Microsoft Visual Studio 2008).

I have used Dundas Chart Control for my previous projects with professional licence. It is a very nice control if you want to buy the licence. You can desing your chart visually with the Dundas wizard and you can get your chart ready in minutes. You can try the full function product free (with watermark on your charts while you are testing).

I have downloaded the sample environment for working with Microsoft Chart Controls. You can download the source code of the sample project here:

http://code.msdn.microsoft.com/mschart/Release/ProjectReleases.aspx?ReleaseId=1591

If you have issues about the control, you can post your question at MSDN forum:

http://social.msdn.microsoft.com/Forums/en-US/MSWinWebChart/threads/#page:1

Jingyang Li

SQL Server

Show SQLDataSource ‘s SQL statement in ASP.NET

View SQLDataSource’s SQL statement in ASP.NET

Remove icon border shadows on the desktop

FilterExpression with multiple columns in SQLDataSource

Search Special character in SQL Server

Script table data in SQL Server 2008

Chart controls from Microsoft for VS 2008

Recent Posts

Archives

Categories

Meta