Bunch's Blog

Retrieving a Logged On User ID

Bunch — Fri, 20 Jan 2012 13:22:42 GMT

Here is an easy way to retrieve the user ID from who ever is logged on to a PC using ASP.Net. This is handy for ASP.Net applications where you want to put a "Welcome joe user" label at the top, auto populate a form with the user's ID or add the user ID to some data you are storing back into a database if you keep track of who edited a record last.

The code below assumes you have an aspx page with two labels on it, lblFName and lblLName. It grabs the user ID with HttpContext and then splits the ID into two values for the two names. It also assumes the user ID would be along the lines of yourdomain\joe.user. You can do more string manipulation on the two values if you want (i.e. like capitalizing the first letter) once you have the values in the string array.

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
  lblFName.Text = "";
  lblLName.Text = "";
  try
  {
   if (HttpContext.Current.User.Identity.Name.IndexOf(".") > 0)
   {
    string uname;
    uname = HttpContext.Current.User.Identity.Name.Split('\\')[1].ToString();
    string[] splitname = new string[1];
    splitname = uname.Split('.');
    lblFName.Text = splitname[0];
    lblLName.Text = splitname[1];
   }
  }
  catch
  {
   //found no name, put your error code here
  }
}
}

Tags: ASP.Net, CSharp

Using WCF Service With a Class

Bunch — Wed, 18 Jan 2012 14:19:49 GMT

This post is an example of how to write a WCF Service using a class. The example uses Visual Studio 2010, written in C#, SQL Server 2008 and hosted in IIS. So lets have at it.

The table is pretty simple just three columns, an TestID (int), Value1 (varchar(50)) and Value2 (varchar(50)). The stored procedure used will return one record from the table using the TestID as a parameter.

Table SQL:

CREATE TABLE [dbo].[Table_1](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[Value1] [varchar](50) NOT NULL,
[Value2] [varchar](50) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Stored Procedure:

CREATE PROCEDURE [dbo].[spGetValuesForTestID]
-- Add the parameters for the stored procedure here
@TestID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT TestID, Value1, Value2
FROM Table_1
WHERE TestID = @TestID
END

Once that is setup and some test values are inserted into the table we can start on the service itself. In VS click on File/New/Project then WCF under the Installed Templates. Then pick WCF Service Application. For the example keep the other settings as they are. This will create a new project with some default items already built. At this point have IService1.cs and Service1.svc.cs files open in the IDE.

In Service1.svc.cs you will see a note about renaming. For the example Service1 is renamed to TestValues and IService1 is renamed to TestService. The Refactor\Rename is handy to use here just like the note says.

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
public class TestValues : TestService

Go back to IService1.cs and remove the code in the public interface TestService and replace it with:

[ServiceContract]
public interface TestService
{
[OperationContract]
TestValuesClass ReturnData(int testID);
}

Now change the default CompositeType class to:

[DataContract]
public class TestValuesClass
{
[DataMember]
public int TestID { get; set; }

[DataMember]
public string Value1 { get; set; }

[DataMember]
public string Value2 { get; set; }
}

The ReturnData is what will be exposed to call and run everything to get the data. The TestValuesClass just sets up the properties for the data.

Next we focus on the Service1.svc.cs file. This is where you will do the actual connection to the database and return the data. This is just like in other applications you may have written. The code for this file is how I wrote it up but there are variations so you can change it up as you see fit. The first item is the public ReturnData function.

public TestValuesClass ReturnData(int testID)
{
     TestValuesClass tvc = new TestValuesClass();
     tvc = GetData(testID);
     return tvc;
}

Here you create a new TestValuesClass to hold the data. Then you call the function that returns the data, GetData. The class you created is what gets returned from the service. The GetData function is:

private TestValuesClass GetData(int testID)
{
     TestValuesClass tvc2 = new TestValuesClass();
     string cnStr = ConfigurationManager.ConnectionStrings["Test_ConnectionString"].ConnectionString;
     using (SqlConnection conn = new SqlConnection(cnStr))
     {
          SqlCommand cmd = new SqlCommand("spGetValuesForTestID", conn);
          cmd.CommandType = System.Data.CommandType.StoredProcedure;
          cmd.Parameters.AddWithValue("@TestID", testID);
          conn.Open();
          SqlDataReader dr;
          dr = cmd.ExecuteReader();
          while (dr.Read())
          {
               tvc2.TestID = int.Parse(dr["TestID"].ToString());
               tvc2.Value1 = dr["Value1"].ToString();
               tvc2.Value2 = dr["Value2"].ToString();
          }
     }
     return tvc2;
}

This is where you can change things up if you have different ways of connecting to databases and returning the data. For this example this works well. It grabs one record from Table_1 that has a TestID of the testID parameter passed to it. One note you do need to add two using statements to the top of the class for this example to work the System.Configuration and System.Data.SqlClient. Configuration is for grabbing the connection string in the web.config file and SqlClient for the various SQL objects.

That is all the coding that is needed for the example. To test out the service it first needs to be added to IIS as an Application. You can publish it straight from VS to the location you setup in IIS using Build/Publish in VS. Once the service is added to IIS you can browse to it and it will look like:

However you can not test the service in a browser like you can with a web service built using asmx files. A testing tool to use is WcfTestClient.exe. It can be found under Program Files\Microsoft Visual Studio 10.0\Common7\IDE. Once you launch the application you can use the same URL you used in a browser under Add Service. Once you expand TestService you can double click on ReturnData and you will see:

From here you can add a TestID value to the Value column and click on Invoke. The results the service would return to an application will be visible in the Response pane.

Tags: CSharp, WCF

Finding a GridViews Edit Control

Bunch — Fri, 23 Dec 2011 16:40:24 GMT

Here is one way to access a control in a GridView right after a user clicks a button to change from view to edit mode. This example shows a TextBox that gets populated with today's date. The GridView is setup like this:

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"
    DataKeyNames="TestID" DataSourceID="SqlDataSource1" onrowdatabound="GridView2_RowDataBound" >
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button ID="btnEdit" runat="server" Text="Edit" CommandName="Edit" CommandArgument='<%# Container.DataItemIndex %>' />
            </ItemTemplate>
            <EditItemTemplate>
                <asp:Button ID="btnCancel" runat="server" Text="Cancel" CommandName="Cancel" />
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Label ID="lblTest1" runat="server" Text='<%# BIND("TestID") %>' />
            </ItemTemplate>
            <EditItemTemplate>
                <asp:Label ID="lblTest4" runat="server" Text='<%# BIND("TestID") %>' />
            </EditItemTemplate>
        </asp:TemplateField>
            <asp:TemplateField>
            <ItemTemplate>
                <asp:Label ID="lblTest2" runat="server" Text='<%# BIND("Value1") %>' />
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="txtTest1" runat="server" Text="test" />
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Label ID="lblTest3" runat="server" Text='<%# BIND("Value2") %>' />
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="txtTest2" runat="server" />
            </EditItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

The txtTest2 TextBox is the one that we want to put today's date in. To enter that information during the switch into edit you can add the following:

protected void GridView2_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowState.HasFlag(DataControlRowState.Edit))
        {
            TextBox txtBox2 = (TextBox)e.Row.FindControl("txtTest2");

            txtBox2.Text = DateTime.Now.ToShortDateString();
        }
    }

The little trick lies in the DataControlRowState.Edit line. When that is true you can use the normal FindControl methods to grab the control you need. Probably not a common thing to do with a GridView but you never know.

Technorati Tags: ASP.Net, CSharp, GridView

Using Table Valued Parameters

Bunch — Thu, 03 Nov 2011 12:05:08 GMT

In SQL Server 2008 you can use table valued parameters which can be pretty useful. In the example I use a very simple one to overcome SQL Server’s lack of having a parameter as an array. The example goes through creating the new type, using it in a stored procedure and calling it from an application (VB.Net in this example). You start off with creating the new type. Under Programmability/Types/User-Defined Table Types create a new table. Here I created a table with a single column to hold an integer.

-- ================================
-- Create User-defined Table Type
-- ================================
USE TestDB

-- Create the data type
CREATE TYPE dbo.IntTable AS TABLE
(
col1 int NOT NULL,
PRIMARY KEY (col1)
)
GO

Now that the type is created you need some SQL to use it. In this stored procedure the parameter @PlayerIDList uses the new InTable data type to pass in a list of player IDs. Using a CTE you can go through each player ID in the table and then return basic player information like batting stance, throwing hand, number, etc.

USE TestDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spTestSprocTVP]
    -- Add the parameters for the stored procedure here
    @PlayerIDList IntTable READONLY
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

WITH PlayerIDs (PlayerID) AS
( SELECT * FROM @PlayerIDList )

    SELECT ply.FullName, ply.Bats, ply.Throws, ply.JerseyNumber
    FROM tblPlayers ply
    INNER JOIN PlayerIDs ON ply.PlayerID = PlayerIDs.PlayerID

To test a sproc like this using a TVP in SQL you can write something like:

DECLARE @test IntTable
INSERT INTO @test(col1) values(282), (643088), (643090), (179100), (54541)
EXEC spTestSprocTVP @test

Where you create a variable of the new data type, load it up with some test values and execute the stored procedure. Now that everything is setup and tested on the SQL side you need to add the code to call it from the application. This example assumes you already have a variable with player IDs created as an ArrayList. The code is also written out (no SQLDataSource used) so you can see all the steps.

Public Function GetPlayerDetails(ByVal playerIDs As ArrayList) As DataTable
    Dim dtPlayerDetails As DataTable = New DataTable
    Dim idList As List(Of SqlDataRecord) = New List(Of SqlDataRecord)
    Dim tvp As SqlMetaData() = {New SqlMetaData("n", SqlDbType.Int)}

    For Each id As Integer In playerIDs
        Dim rec As SqlDataRecord = New SqlDataRecord(tvp)
        rec.SetInt32(0, id)
        idList.Add(rec)
    Next

    Dim cn As SqlConnection = New SqlConnection
    cn.ConnectionString = connString
    Dim cmd As SqlCommand = cn.CreateCommand
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)

    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "spTestSprocTVP"
    cmd.Parameters.Add("@PlayerIDList", SqlDbType.Structured)
    cmd.Parameters("@PlayerIDList").TypeName = "IntTable"
    cmd.Parameters("@PlayerIDList").Value = idList

    cn.Open()
    da.Fill(dtPlayerDetails)
    cn.Close()

Return dtPlayerDetails
End Function

The main points to look at in the function is creating a new list of SqlDataRecord, creating a variable for the SqlMetaData type that matches your user-defined table type from the first step and then adding each item from the function’s parameter to the SqlDataRecord type list. All that is within the first eight lines of the function. Once the DataTable is returned you can do whatever you want with it like bind it to a GridView.

Technorati Tags: SQL,VB.Net

PRINT Ints in TSQL

Bunch — Thu, 20 Oct 2011 14:48:38 GMT

To print multiple int variables on one line you need to CAST them first. Otherwise the PRINT command will add them up (at least it does for me anyways using SQL 2008). In the example I needed to have two ID variables print out while I was working on a sproc so I could see if I was getting the correct IDs before adding in my update code.

This did not work, it adds the two variables together:

PRINT @FirstID + ‘ – ‘ + @NextID

This does work, it prints each variable with the dash in-between:

PRINT CAST(@FirstID AS Varchar(20)) + ‘ – ‘ + CAST(@NextID AS Varchar(20))

Technorati Tags: SQL

XmlWriter Formatting

Bunch — Mon, 18 Jul 2011 16:22:40 GMT

Setting up formatting when using a XmlWriter is pretty easy. Declare a XmlWriterSettings variable and set the formatting options you want. The two main items are for indenting and new lines. You add the XmlWriterSettings variable as the second parameter in the XmlWriter.Create function.

VB.Net Example

Dim mySettings As New XmlWriterSettings()
mySettings.Indent = True
mySettings.NewLineOnAttributes = True

Using writer As XmlWriter = XmlWriter.Create("c:\test.xml", settings)

C# Example

XmlWriterSettings mySettings = new XmlWriterSettings();
mySettings.Indent = true;
mySettings.NewLineOnAttributes = true;

using (XmlWriter writer = XmlWriter.Create("c:\test.xml", mySettings))

Technorati Tags: VB.Net,CSharp

Accessing Controls Within A Gridview

Bunch — Fri, 17 Jun 2011 13:44:11 GMT

Sometimes you need to access a control within a GridView, but it isn’t quite as straight forward as just using FindControl to grab the control like you can in a FormView. Since the GridView builds multiple rows the key is to specify the row. In this example there is a GridView with a control for a player’s errors. If the errors is greater than 9 the GridView should display the control (lblErrors) in red so it stands out. Here is the GridView:

<asp:GridView ID="gvFielding" runat="server" DataSourceID="sqlFielding" DataKeyNames="PlayerID" AutoGenerateColumns="false" >
    <Columns>
        <asp:BoundField DataField="PlayerName" HeaderText="Player Name" />
        <asp:BoundField DataField="PlayerNumber" HeaderText="Player Number" />
        <asp:TemplateField HeaderText="Errors">
            <ItemTemplate>
                <asp:Label ID="lblErrors" runat="server" Text='<%# EVAL("Errors") %>' />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

In the code behind you can add the code to change the label’s ForeColor property to red based on the amount of errors. In this case 10 or more errors triggers the color change.

Protected Sub gvFielding_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvFielding.DataBound
    Dim errorLabel As Label
    Dim errors As Integer
    Dim i As Integer = 0
    For Each row As GridViewRow In gvFielding.Rows
        errorLabel = gvFielding.Rows(i).FindControl("lblErrors")
        If Not errorLabel.Text = Nothing Then
            Integer.TryParse(errorLabel.Text, errors)
            If errors > 9 Then
                errorLabel.ForeColor = Drawing.Color.Red
            End If
        End If
        i += 1
    Next
End Sub

The main points in the DataBound sub is use a For Each statement to loop through the rows and to increment the variable i so you loop through every row. That way you check each one and if the value is greater than 9 the label changes to red. The If Not errorLabel.Text = Nothing line is there as a check in case no data comes back at all for Errors.

Technorati Tags: GridView,ASP.Net,VB.Net

CTE Updating

Bunch — Fri, 27 May 2011 16:47:34 GMT

Using a CTE to help with an update can be pretty handy. In this simple example the CTE is to select SiteIDs for a specific sales rep. The update is to change the address code to ‘Street’ since the rep entered them all in as ‘Mailing’ by mistake. In the update code you use an inner join to match up the SiteIDs from the CTE with the ones in tblAddress so you only update the specific sales rep’s sites and not all of them in the table.

WITH CTE(SiteID) AS
(
SELECT srs.SiteID
FROM tblSalesRepSites srs
WHERE srs.SalesRepID = '12345'
)

UPDATE tblAddress
SET AddressCode = 'Street'
FROM tblAddress addr INNER JOIN CTE ON addr.SiteID = CTE.SiteID

Technorati Tags: SQL

Short Circuiting Forgetfulness in VB.Net

Bunch — Mon, 02 May 2011 15:47:22 GMT

If nobody else but me reads this that is OK, this is just something I forget from time to time when working in VB.Net. To short circuit an expression the && or || operators in many other languages are AndAlso or OrElse in VB.Net.

Two really simple examples:

If a > b AndAlso a < c Then

If a > b OrElse a > c Then

Technorati Tags: VB.Net

Cycling Through Selected CTE Values

Bunch — Wed, 13 Apr 2011 17:05:24 GMT

A great benefit of using a CTE is their ability to replace some uses of Cursors. Sometimes you may need a Cursor to fetch an ID value from a table. Use that ID in a Select statement. Then fetch the next ID value, use that in the same Select statement over and over again until you ran through all the ID values. A CTE can help eliminate that and from what I can see is generally a bit faster than using a Cursor.

This example is using a table to hold purchase orders (tblPO) and another table to hold invoices (tblInv). tblPO has columns for the purchase order ID (POID), the purchase order number (PONum) and the amount of funds in the purchase order (POAmt). tblInv has columns for the associated purchase order (POID) and an amount for the invoice (InvAmt). The scenario is that you need a list of all purchase order IDs, numbers, amount of the PO and the amount that is still available. To calculate the amount available you need to total up all the invoice amounts for a purchase order and then subtract them from the purchase order amount.

WITH cte(POID, PONum, POAmnt) AS (
SELECT POID, PONum, POAmt FROM tblPO )

SELECT POID, PONum, POAmt,
(POAmt - (SELECT SUM(InvAmt)FROM tblInv WHERE POID = cte.POID)) AS 'Available'
FROM cte

You setup the CTE with the values you want except for the one that needs each POID individually (the amount available calculation). Then when you run a select from the CTE you can put the current POID from the CTE in the where clause. This ends up being a lot simpler and faster than setting up a Cursor and fetching each POID from tblPO.

A second way to set this up which accomplishes the same would be to create the CTE with just the ID you want to cycle through. Either way it works the same it just depends on how you like to write it.

WITH cte(POID) AS (
SELECT POID FROM tblPO)

SELECT cte.POID, po.PONum, po.POAmnt, (POAmt - (SELECT SUM(InvAmt)FROM tblInv WHERE POID = cte.POID)) AS 'Available'
FROM cte
INNER JOIN tblPO po ON po.POID = cte.POID

Technorati Tags:

SQL