Stone Coast Web Design's Weblog

Navigation

Search

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

| |

E-mail

Total Posts: 112
This Year: 50
This Month: 0
This Week: 0
Comments: 0

Tuesday, April 08, 2008

How to Share Data Between Stored Procedures

Tuesday, April 08, 2008 12:35:23 PM (Eastern Standard Time, UTC-05:00) ( SQL )

Erland Sommarskog, a SQL Server MVP, has a nice entry on how to share data between stored procedures. The example I'll use the most often is using OUTPUT parameters

This method can only be used when the result set is one single row. Nevertheless, this is a method that is sometimes overlooked. Say you have this simple stored procedure:

CREATE PROCEDURE insert_customer @name    nvarchar(50),
                                 @address nvarchar(50),
                                 @city    nvarchar(50) AS
DECLARE @cust_id int
BEGIN TRANSACTION
SELECT @cust_id = coalesce(MAX(cust_id), 0) + 1 FROM customers (UPDLOCK)
INSERT customers (cust_id, name, address, city)
   VALUES (@cust_id, @name, @address, @city)
COMMIT TRANSACTION
SELECT @cust_id

That is, the procedure inserts a row into a table, and returns the id for the row.

Rewrite this procedure as:

CREATE PROCEDURE insert_customer @name    nvarchar(50),
                                 @address nvarchar(50),
                                 @city    nvarchar(50),
                                 @cust_id int OUTPUT AS
BEGIN TRANSACTION
SELECT @cust_id = coalesce(MAX(cust_id), 0) + 1 FROM customers (UPDLOCK)
INSERT customers (cust_id, name, address, city)
   VALUES (@cust_id, @name, @address, @city)
COMMIT TRANSACTION

You can now easily call insert_customer from another stored procedure. Just recall that in T-SQL you need to specify the OUTPUT keyword also in the call:

EXEC insert_customer @name, @address, @city, @cust_id OUTPUT

Comments [0] | Trackback |

Using the Gridview.Sorting and Gridview.Sorted Events

Tuesday, April 08, 2008 11:53:34 AM (Eastern Standard Time, UTC-05:00) ( ASP.NET | Gridview )

If you're using a SQLDataSource, it's pretty simple to add sorting to a Gridview. But often times we need some specific event to occur when we sort a column such as change the text in a label control, add ASC or DESC up and down arrows to indicate how a Gridview is being sorted, databind some other object on the page etc. Below are two examples - the first one uses the Gridview.Sorting event and the last one uses Gridview.Sorted.

<script runat="server">

  Sub CustomersGridView_Sorting(sender As Object, e As GridViewSortEventArgs)

    ' Cancel the sorting operation if the user attempts
    ' to sort by address.
    If e.SortExpression = "Address" Then

      e.Cancel = True
      Message.Text = "You cannot sort by address."
      SortInformationLabel.Text = ""

    Else

      Message.Text = ""

    End If

  End Sub

  Sub CustomersGridView_Sorted(ByVal sender As Object, ByVal e As EventArgs)

    ' Display the sort expression and sort direction.
    SortInformationLabel.Text = "Sorting by " & _
      CustomersGridView.SortExpression.ToString() & _
      " in " & CustomersGridView.SortDirection.ToString() & _
      " order."

  End Sub

</script>

<html  >
  <head runat="server">
    <title>GridView Sorted and Sorting Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <h3>GridView Sorted and Sorting Example</h3>

      <asp:label id="Message"
        forecolor="Red"
        runat="server"/>

      <br/>

      <asp:label id="SortInformationLabel"
        forecolor="Navy"
        runat="server"/>

      <br/>  

      <asp:gridview id="CustomersGridView" 
        datasourceid="CustomersSource" 
        autogeneratecolumns="true"
        allowpaging="true"
        emptydatatext="No data available." 
        allowsorting="true"
        onsorting="CustomersGridView_Sorting"
        onsorted="CustomersGridView_Sorted"  
        runat="server">

      </asp:gridview>

<asp:sqldatasource .... />

Comments [0] | Trackback |

Friday, April 04, 2008

Sharing Client-Side Code with Server-Side Code

Friday, April 04, 2008 1:41:59 PM (Eastern Standard Time, UTC-05:00) ( ASP.NET | Javascript/AJAX )

I often come across the need to share data between javascript client-side code and my asp.net server controls. Many functions just perform better on the client-side and the need to pass data from my server controls to javascript comes up often. One of the easier ways of doing this is to create a asp:hiddenfield. Doing this gives your javascript code access to the data it contains. In this example, we programatically create a asp:Hiddenfield with a ID="sharedData" and assign a value of "New client initial value." When we click the server-side button, btnGetData which calls the javascript function getSharedData(), it passes the hiddenfield value from sharedData to getSharedData().

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

<!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>
    <script type="text/javascript">
    function getSharedData()
    {
        alert("Shared data is " + document.getElementById("sharedData").value + ".");
    }
    
    </script>    
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
        <asp:Button ID="btnGetData" runat="server" Text="Get Data" OnClientClick="getSharedData()" />
    </form>
</body>
</html>

Code behind - Default.aspx.vb:

Partial Class Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreLoad
        Dim hiddenField As System.Web.UI.WebControls.HiddenField = New System.Web.UI.WebControls.HiddenField
        hiddenField.ID = "sharedData"
        hiddenField.Value = "New client initial value"

        Page.Form.Controls.Add(hiddenField)
    End Sub
End Class

Comments [0] | Trackback |

Thursday, March 20, 2008

Using Javascript with ASP.NET

Thursday, March 20, 2008 2:53:14 PM (Eastern Standard Time, UTC-05:00) ( ASP.NET | Javascript/AJAX )

I've been working on a project which required the use of some javascript with some server-side controls. I spent considerable time monkeying around with the Page.ClientScript.RegisterClientScriptBlock() method which registers a client script on the page. I couldn't quite get it right using this method and ended up with a much simpler approach anyway. Every server-side web control has an "Add" method which you can use when the page loads. In the example below, I register a click event for a button and 2 different textboxes. Take notice that for the textboxes I use the client side id value for them (e.g. 'Header1_txtFirstName'). These values can be obtained by looking at the source code of the page in the browser.

Private Sub Page_Load(ByVal Src As Object, ByVal E As EventArgs)
        If Not Page.IsPostBack Then
            btnDelete.Attributes.Add("onClick", "return confirm('You sure you want to delete the record?');")
            txtFirstName.Attributes.Add("onClick", "document.getElementById('Header1_txtFirstName').value = '';")
            txtLastName.Attributes.Add("onClick", "document.getElementById('Header1_txtLastName').value = '';")
        End If
End Sub

Candidate Name: 
<asp:TextBox ID="txtFirstName" runat="server" Text="First Name" /> 'this becomes Header1_txtFirstName
<asp:TextBox ID="txtLastName" runat="server" Text="Last Name" /> 'this becomes Header1_txtLastName

More information on this topic here: http://dotnetslackers.com/articles/aspnet/JavaScript_with_ASP_NET_2_0_Pages_Part1.aspx

Comments [0] | Trackback |

Tuesday, March 11, 2008

First Look at Silverlight 2.0

Tuesday, March 11, 2008 10:45:50 AM (Eastern Standard Time, UTC-05:00) ( Silverlight )

Scott Gutherie has a nice 8 part tutorial series on Silverlight 2.0 as well as a nice blog post on Expression Blend with Silverlight 2.

Comments [0] | Trackback |

Wednesday, March 05, 2008

Using Findcontrol

Wednesday, March 05, 2008 4:26:52 PM (Eastern Standard Time, UTC-05:00) ( ASP.NET )

I've been working on a project which has involved finding the values or setting the values on controls (label, dropdownlist etc.) embedded inside of other other controls (repeater, gridview etc.). You have to drilldown or look inside the parent object containing these controls to make this work. This is where the Page.FindControl method comes in useful. Here are some examples:

        Dim lb As LinkButton = sender
        If Not (lb Is Nothing) Then
                        
        'Get the value for a dropdownlist using Parent.FindControl
        Dim strDirRace As String = CType(lb.Parent.FindControl("ddlDirRace"), DropDownList).SelectedItem.Text

        'Get the value for a dropdownlist specifying the name of repeater control housing it
        Dim strDirRace As String = CType(rptDirectorDetails.FindControl("ddlDirRace"), DropDownList).SelectedItem.Text
            
        Dim lblDirRace As Label = CType(lb.Parent.FindControl("lblDirRaceUpdateStatus"), Label)
            lblDirRace.Text = "* Update Complete *"        
        End If

        When you use a Master page, then you need to do this

          Dim cbx As CheckBox = CType(Master.FindControl("Content1").FindControl("cbxAllTel"), CheckBox)

More info on this topic: http://msdn2.microsoft.com/en-us/library/31hxzsdw.aspx

Comments [0] | Trackback |

Monday, March 03, 2008

Gridview Hidden Field, How to get hidden field value in gridview

Monday, March 03, 2008 12:44:01 PM (Eastern Standard Time, UTC-05:00) ( )

A GridView contains a property called "DataKeyNames" which is typically the primary key of each record. This is often useful when you need to grab the unique recordID for each row. To get ID for a single selected row, you can do something like this:

If you are using RowUpdating, RowDatabound, etc.

Dim intId as Integer= GridView1.DataKeys(e.RowIndex).Value

By name :

Dim intId as Integer= GridView1.DataKeys("ID").Value

By Index:

Dim intId as Integer= GridView1.DataKeys(0).Value

<Columns>

<asp:TemplateField>
<ItemTemplate>
<asp:HiddenField ID="hdID" runat="server" Value='<%# Eval("ID") %>' />
</ItemTemplate>
</asp:TemplateField>

To get the ID value for a group of records in a Gridview, you can do something like this:

Protected Sub btn_ExportSelectedRecords_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        'First loop through the GridView and see which IDs were selected. I use the StringBuilder since the list could be a very long list        
        Dim sb As New StringBuilder
        Dim row As GridViewRow
        For Each row In searchResultsGridView.Rows
            If (CType(row.FindControl("CheckBox1"), CheckBox)).Checked Then
                lblSelectedIDDirs.Text = sb.Append(searchResultsGridView.DataKeys(row.RowIndex).Value.ToString() + ","c).ToString()
            End If
        Next

End Sub

<asp:GridView ID="searchResultsGridView" Runat="server" DataSourceID="searchResultsDataSource" AllowSorting="true" AllowPaging="true" PageSize="20" 
 DataKeyNames="IDDir">
                       
 <Columns>
              
 <asp:TemplateField HeaderText="">
 <ItemTemplate>
                    <asp:HiddenField ID="hdID" runat="server" Value='<%# Eval("IDDir") %>' />
                </ItemTemplate>
                </asp:TemplateField> 
                
                 <asp:TemplateField HeaderText="">
                 <ItemTemplate>
                    <asp:Checkbox id="Checkbox1" runat="server" /> 
                 </ItemTemplate>
                </asp:TemplateField>

Comments [0] | Trackback |

Thursday, February 07, 2008

TRIM Function in SQL Server

Thursday, February 07, 2008 10:52:21 AM (Eastern Standard Time, UTC-05:00) ( )

I was surprised to learn that both SQL Server 2000/2005 don't offer a built in TRIM function to remove leading or trailing whitespace from a string. What is possible, however, is to use a user defined function (UDF) to do the same thing.

SQL Server 2000:

CREATE FUNCTION dbo.TRIM(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN LTRIM(RTRIM(@string))
END

SQL Server 2005:

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO

Then you call the function like this

SELECT DISTINCT(dbo.TRIM(Industry)) AS Industry FROM TCompanies
WHERE Industry IS NOT NULL

Comments [0] | Trackback |

Saturday, February 02, 2008

SQL Server Split Function

Saturday, February 02, 2008 8:18:09 PM (Eastern Standard Time, UTC-05:00) ( )

Using a comma delimited string with id's as input parameter for a SQL query

I just recently found out about a custom Split function for SQL Server 2000/2005 while I was reading this article: Designing Reports with SQL Server Reporting Services 2005. It mentioned a custom Split function made in T-SQL that could take in a delimited string with id's.

Today I had the need of such a functionality in my current project and luckily remembered where I saw it in the first place. If you check several checkboxes and press the button you get, for those selected records, to see everything that's in the database for those.

Split function:

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'Split' 
)
   DROP FUNCTION Split
GO
CREATE FUNCTION dbo.Split
(
    @ItemList NVARCHAR(4000), 
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(4000)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END  
GO

This is how to call the split function using a stored procedure:

CREATE PROCEDURE USP_RetrieveInformationForSelectedEmployees 
@p_selectedEmployees NVARCHAR(50) 
AS 
BEGIN SELECT * FROM Employees 
WHERE EmployeeID in (SELECT Item FROM split(@p_selectedEmployees, ',')) 
END

Lastly, this is how it's all put together:

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Configuration" %>
<%@ Import Namespace="System.Data" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  
<script runat="server">
  
   protected void Button1_Click(object sender, EventArgs e)
  {
  RetrieveInformationForSelectedEmployees();
  }
   
   private void RetrieveInformationForSelectedEmployees()
  {
  StringBuilder sb = new StringBuilder();
   
   // First loop through the GridView and see which
   // employees were selected. I use the StringBuilder
   // since the list could be a very long list.
   foreach (GridViewRow row in GridView1.Rows)
  {
   if (((CheckBox)row.FindControl("CheckBox1")).Checked)
  {
  sb.Append(GridView1.DataKeys[row.RowIndex].Value.ToString() + ',');
  }
  }
   
   using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings
  ["NorthwindConnectionString"].ConnectionString))
  {
   using (SqlCommand cmd = new SqlCommand("USP_RetrieveInformationForSelectedEmployees", conn))
  {
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@p_selectedEmployees", sb.ToString());
   
  DataSet ds = new DataSet();
  SqlDataAdapter da = new SqlDataAdapter(cmd);
  da.Fill(ds);
   
  GridViewResult.DataSource = ds;
  GridViewResult.DataBind();
  }
  }
  }
   
  </script>
   
  <html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
   <title>Untitled Page</title>
  </head>
  <body>
   <form id="form1" runat="server">
   <div>
   <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
   DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
   <Columns>
   <asp:TemplateField>
   <ItemTemplate>
   <asp:CheckBox ID="CheckBox1" runat="server" />
   </ItemTemplate>
   </asp:TemplateField>
   <asp:BoundField DataField="LastName" HeaderText="LastName" 
   SortExpression="LastName" />
   <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
   SortExpression="FirstName" />
   <asp:BoundField DataField="Title" HeaderText="Title" 
   SortExpression="Title" />
   </Columns>
   </asp:GridView>
   <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
   ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
   SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title]
  FROM [Employees] ORDER BY [LastName], [FirstName]">
   </asp:SqlDataSource>
   
   <asp:Button ID="Button1" runat="server" Text="Retrieve data" OnClick="Button1_Click" />
   <p>
   <asp:GridView runat="server" ID="GridViewResult" />
   </p>
   </div>
   </form>
  </body>
 </html>

Comments [0] | Trackback |

Mostly Web Development

Navigation

Search

Categories

On this page

Archive

Blogroll

SQL Server 2000:

SQL Server 2005: