XSD to C# class or VB.net class

While working with biztalk using web services i found this as a must to have tool.
The XSD.exe from microsoft can be used for this.

The Sample Code Generator (XSDObjectGen) tool takes an XSD schema as input and generates sample code showing how to mark up C# and VB.Net classes so that when serialized with the XML serializer, the resulting XML will be valid according to the original schema.

Download link from microsoft

MS-SQL Server Reporting Services training Video. ( SRS demo)

Here is an excellent wmv tutorial for SRS

Reporting Services Demo: How to Author, Manage, and Deliver Reports

Accessing Request or Session objects from class modules

When we need to use a request object from another class modules we have to use the
HttpContext.Current object. This contains all the state informations like request, session, Server, Respose etc.
Eg:
string r = HttpContext.Current.Request["MyParam"].ToString();
string s = HttpContext.Current.Session["MySessionVar"].ToString();

Note: Add "using System.Web" to the header.

Wraping Session to Gobal Session Class

using System;
using System.Web;

namespace MyApp
{
public class SessionGlobal
{

private const string _AccountNo = "_AccountNo";

// Set to default Values
public static void Clear()
{

 HttpContext.Current.Session[_AccountNo]="";

}

private static string getSessionVar(string key)
{
 if(HttpContext.Current.Session[key]!=null)
  return HttpContext.Current.
          Session[key].ToString();
 else
  return "";

}

public static string AccountNo
{
 get
 {
  return getSessionVar(_AccountNo);
 }
 set
 {
  HttpContext.Current.Session[_AccountNo]=value;
 }
}

public static string UserID
{
 get
 {
  return HttpContext.Current.
          Request.ServerVariables["AUTH_USER"];
 }

}

}
}
 

Software requirements for a complete installation of BizTalk Server 2006

The minimum software requirements for a complete installation of BizTalk Server 2006 on a single computer:

• Windows Server 2003 with Service Pack 1
• Microsoft Office Excel 2003 and InfoPath 2003 with Service Pack 2
• Microsoft Visual Studio 2005 with Microsoft Visual C# .NET
• Microsoft SQL Server 2005 or Microsoft SQL Server 2000 with Service Pack 4
• Microsoft SQL Server 2005 Analysis Services or Microsoft SQL Server 2000 Analysis Services with Service Pack 4
• Microsoft SQL Server 2005 Notification Services or Microsoft SQL Server 2000
• Notification Services 2.0 with Service Pack 1
• Microsoft Windows SharePoint Services 2.0 with Service Pack 2

Create a simple Serial/row No column in Datagrid/DataList

A very simple way to create Serial/row No column in Datagrid/DataList

create a variable in the code behind

protected int i=1;

Now in the Datagrid/DataList add a TemplateColumn with data bound to the variable



<asp:TemplateColumn HeaderText="SL#">
<ItemTemplate>
<%# i++ %>
</ItemTemplate>
</asp:TemplateColumn>

This may not be usefull for item identification when used in sortable grid

Avoiding "Case When" From your TSQL in MS-SQLServer

Today I was working on a query with a conditional construct where i had to use CASE WHEN. But a "Case when" will be evaluted for each record.
Keeping this thought was looking for a new logic. At last i arrived that

SELECT  FirstName, LastName, Status
FROM RequestTable
Inner join
(
    Select -1 as Appr , 'New' as Status
    Union
    Select 0, 'Created'
    Union
    Select 1, 'Accepted'
    Union
    Select 2, 'Rejected'
) T

On IsNull(Cast(Approved as smallint),-1) = Appr

How to display form only once in MDI application in .Net

You can create a static variable in the form to keep the reference of the form and a static Showform method to invoke the form.


Class Form1
{
static Form1 frm;

static ShowForm()
{
if (frm==null) frm=new Form1;
frm.show();
}
}

Now from the menu click call
Form1.ShowForm();

How to create an enumeration or position of a child table record

I have seen this question in many user forums. Lets consider pubs database in MS-SQL SERVER. Now see how to generate the position number of each titles of an author in the titleauthor table.

SELECT * FROM
(
        SELECT
               ( SELECT COUNT(au_Id)
                  FROM titleauthor a
                  WHERE a.au_Id = b. au_Id 
                  AND a.title_ID <= b.title_ID
                  ) as pos ,
               au_Id,
              title_ID
         FROM titleauthor b
          GROUP BY au_Id ,title_ID
)  T

ORDER BY  au_Id 

This is the result we get

pos             au_Id         title_ID
--------------------------------------
1         172-32-1176         PS3333
1         213-46-8915         BU1032
2         213-46-8915         BU2075
1         238-95-7766         PC1035
1         267-41-2394         BU1111
2         267-41-2394         TC7777
1         274-80-9391         BU7832
1         409-56-7008         BU1032
1         427-17-2319         PC8888
1         472-27-2349         TC7777
1         486-29-1786         PC9999
2         486-29-1786         PS7777
1         648-92-1872         TC4203
1         672-71-3249         TC7777
1         712-45-1867         MC2222
1         722-51-5454         MC3021
1         724-80-9391         BU1111
2         724-80-9391         PS1372
1         756-30-7391         PS1372
1         807-91-6654         TC3218
1         846-92-7186         PC8888
1         899-46-2035         MC3021
2         899-46-2035         PS2091
1         998-72-3567         PS2091
2         998-72-3567         PS2106

=====================================

How User Controls Communicate in ASP.NET1.1

ASP.Net has a good support for component oriented programming. Even the User controls can be excellect self independent components. A user control can not only have public methods & properties, it can also have events. Using this concepts we can easily decompose any complex page to simpler user controls ,ie., I mean to say don't hesitate to create usercontrols to split a complex page, even if the usercontrols is used only once.

Now Let's look at how user controls communicate using events.

First we will see how to add an event to a user control "Header"

1. Add delegate declaration in the namespace
2. Add event declaration in the class
3. Fire the event
   

namespace TestUserControl
{
  public delegate void ChangeEventHandler(
           object sender, string ID);
  public class Header : Web.UI.UserControl
  {
    protected WebControls.Label lblApp;

    public event ChangeEventHandler Change;

    public void ChangeAppName(string AppName)
    {
       this.lblApp.Text =AppName;
       Change (this,"Changed appname to : "
          + AppName.Trim().ToUpper());
    }
  }
}

Now Lets see how to consume the
event in a page

namespace TestUserControl
{
 public class WebForm1 : System.Web.UI.Page
 {
  protected WebControls.Button Button1;
  protected WebControls.TextBox TextBox1;
  protected Header hdr;
  
  private void Page_Load(object sender, 
           System.EventArgs e)
  {
   hdr.Change+= new 
           ChangeEventHandler(hdr_ChangeEvent);
  }
  private void Button1_Click(object sender,
            System.EventArgs e)
  {
   hdr.ChangeAppName("New Application" 
           + TextBox1.Text);
  }
  private void hdr_ChangeEvent(object sender,
            string ID)
  {
   TextBox1.Text= ID;
  }
 }
}

Note: Only relevent code has been kept,
place the code in the appropriate area.

A complete Editable Data Grid in ASP.NET 1.1

This is good piece of code generated by the Web Matrix,
It had a bug that no records could be added if the table is empty.
This is the fixed version. I feel this could help many beginners.


The Script Part

   ' TODO: update the ConnectionString and Command values for your application

    Dim ConnectionString As String = "server=(local);database=pubs;trusted_connection=true"
    Dim SelectCommand As String = "SELECT au_id, au_lname, au_fname from Authors"

    Dim isEditing As Boolean = False

    Sub Page_Load(Sender As Object, E As EventArgs)

        If Not Page.IsPostBack Then

            ' Databind the data grid on the first request only
            ' (on postback, bind only in editing, paging and sorting commands)

            BindGrid()

        End If

    End Sub

    ' ---------------------------------------------------------------
    '
    ' DataGrid Commands: Page, Sort, Edit, Update, Cancel, Delete
    '

    Sub DataGrid_ItemCommand(Sender As Object, E As DataGridCommandEventArgs)

        ' this event fires prior to all of the other commands
        ' use it to provide a more graceful transition out of edit mode

        CheckIsEditing(e.CommandName)

    End Sub

    Sub CheckIsEditing(commandName As String)

        If DataGrid1.EditItemIndex <> -1 Then

            ' we are currently editing a row
            If commandName <> "Cancel" And commandName <> "Update" Then

                ' user's edit changes (If any) will not be committed
                Message.Text = "Your changes have not been saved yet.  Please press update to save your changes, or cancel to discard your changes, before selecting another item."
                isEditing = True

            End If

        End If

    End Sub

    Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)

        ' turn on editing for the selected row

        If Not isEditing Then

            DataGrid1.EditItemIndex = e.Item.ItemIndex
            BindGrid()

        End If

    End Sub

    Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)

        ' update the database with the new values

        ' get the edit text boxes
        Dim id As String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
        Dim lname As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
        Dim fname As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text

        ' TODO: update the Command value for your application
        Dim myConnection As New SqlConnection(ConnectionString)
        Dim UpdateCommand As SqlCommand = new SqlCommand()
        UpdateCommand.Connection = myConnection

        If AddingNew = True Then
            UpdateCommand.CommandText = "INSERT INTO authors(au_id, au_lname, au_fname, contract) VALUES (@au_id, @au_lname, @au_fname, 0)"
        Else
            UpdateCommand.CommandText = "UPDATE authors SET au_lname = @au_lname, au_fname = @au_fname WHERE au_id = @au_id"
        End If

        UpdateCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11).Value = id
        UpdateCommand.Parameters.Add("@au_lname", SqlDbType.VarChar, 40).Value = lname
        UpdateCommand.Parameters.Add("@au_fname", SqlDbType.VarChar, 20).Value = fname

        ' execute the command
        Try
            myConnection.Open()
            UpdateCommand.ExecuteNonQuery()

        Catch ex as Exception
            Message.Text = ex.ToString()

        Finally
            myConnection.Close()

        End Try

        ' Resort the grid for new records
        If AddingNew = True Then
            DataGrid1.CurrentPageIndex = 0
            AddingNew = false
        End If

        ' rebind the grid
        DataGrid1.EditItemIndex = -1
        BindGrid()

    End Sub

    Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)

        ' cancel editing

        DataGrid1.EditItemIndex = -1
        BindGrid()

        AddingNew = False

    End Sub

    Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)

        ' delete the selected row

        If Not isEditing Then

            ' the key value for this row is in the DataKeys collection
            Dim keyValue As String = CStr(DataGrid1.DataKeys(e.Item.ItemIndex))

            ' TODO: update the Command value for your application
            Dim myConnection As New SqlConnection(ConnectionString)
            Dim DeleteCommand As New SqlCommand("DELETE from authors where au_id='" & keyValue & "'", myConnection)

            ' execute the command
            myConnection.Open()
            DeleteCommand.ExecuteNonQuery()
            myConnection.Close()

            ' rebind the grid
            DataGrid1.CurrentPageIndex = 0
            DataGrid1.EditItemIndex = -1
            BindGrid()

        End If

    End Sub

    Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)

        ' display a new page of data

        If Not isEditing Then

            DataGrid1.EditItemIndex = -1
            DataGrid1.CurrentPageIndex = e.NewPageIndex
            BindGrid()

        End If

    End Sub

    Sub AddNew_Click(Sender As Object, E As EventArgs)

        ' add a new row to the end of the data, and set editing mode 'on'

        CheckIsEditing("")

        If Not isEditing = True Then

            ' set the flag so we know to do an insert at Update time
            AddingNew = True

            ' add new row to the end of the dataset after binding

            ' first get the data
            Dim myConnection As New SqlConnection(ConnectionString)
            Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

            Dim ds As New DataSet()
            myCommand.Fill(ds)

            ' add a new blank row to the end of the data
            Dim rowValues As Object() = {"", "", ""}
            ds.Tables(0).Rows.Add(rowValues)

            ' figure out the EditItemIndex, last record on last page
            Dim recordCount As Integer = ds.Tables(0).Rows.Count

            If recordCount > 1 Then

                recordCount -= 1
                DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
                DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize
            Else
                DataGrid1.EditItemIndex = 0
            End If

            ' databind
            DataGrid1.DataSource = ds
            DataGrid1.DataBind()

        End If


    End Sub

    ' ---------------------------------------------------------------
    '
    ' Helpers Methods:
    '

    ' property to keep track of whether we are adding a new record,
    ' and save it in viewstate between postbacks

    Property AddingNew() As Boolean

        Get
            Dim o As Object = ViewState("AddingNew")
            If o Is Nothing Then
                Return False
            End If
            Return CBool(o)
        End Get

        Set(ByVal Value As Boolean)
            ViewState("AddingNew") = Value
        End Set

    End Property

    Sub BindGrid()

        Dim myConnection As New SqlConnection(ConnectionString)
        Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

        Dim ds As New DataSet()
        myCommand.Fill(ds)

        DataGrid1.DataSource = ds
        DataGrid1.DataBind()

    End Sub

The HTML part

<H2>Editable Data Grid </H2>
<HR SIZE=1>
</FORM>
<FORM runat="server"><?xml:namespace prefix = asp /><asp:datagrid id=DataGrid1 runat="server" DataKeyField="au_id" OnItemCommand="DataGrid_ItemCommand" OnEditCommand="DataGrid_Edit" OnUpdateCommand="DataGrid_Update" OnCancelCommand="DataGrid_Cancel" OnDeleteCommand="DataGrid_Delete" AllowPaging="true" PageSize="6" OnPageIndexChanged="DataGrid_Page" ForeColor="Black" BackColor="White" CellPadding="3" GridLines="None" CellSpacing="1" width="80%"><HEADERSTYLE backcolor="#4A3C8C" forecolor="white" font-bold="True"></HEADERSTYLE><PAGERSTYLE backcolor="#C6C3C6" font-size="smaller" mode="NumericPages" horizontalalign="Right"></PAGERSTYLE><ITEMSTYLE backcolor="#DEDFDE"></ITEMSTYLE><FOOTERSTYLE backcolor="#C6C3C6"></FOOTERSTYLE><COLUMNS><asp:EditCommandColumn ItemStyle-Width="10%" ItemStyle-Font-Size="smaller" EditText="Edit" CancelText="Cancel" UpdateText="Update" ButtonType="LinkButton"></asp:EditCommandColumn><asp:ButtonColumn ItemStyle-Width="10%" ItemStyle-Font-Size="smaller" CommandName="Delete" Text="Delete"></asp:ButtonColumn></COLUMNS></asp:datagrid><BR><asp:LinkButton id=LinkButton1 onclick=AddNew_Click runat="server" Text="Add new item" Font-Size="smaller"></asp:LinkButton><BR><BR><asp:Label id=Message runat="server" width="80%" forecolor="red" enableviewstate="false"></asp:Label></FORM>

Updating using a gridview (ASP.NET 2.0)

This Example will show how Updating using a gridview is done in ASP.NET 2.0 with the SqlDatasource command.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 
                     Transitional//EN">

<HTML>

  <BODY>
  <FORM runat="server">

    <asp:SqlDataSource
       id="SqlDataSource1"
       runat="server"
       DataSourceMode="DataSet"
       ConnectionString=
            "<%$ ConnectionStrings:MyNorthwind%>"
       SelectCommand="SELECT EmployeeID,FirstName,
                   LastName,Title FROM Employees"
       UpdateCommand="Update Employees 
                    SET FirstName=@FirstName,
                    LastName=@LastName, Title=@Title
                    WHERE EmployeeID=@EmployeeID">
    </asp:SqlDataSource>

    <asp:GridView
       id="GridView1"
       runat="server"
       AutoGenerateColumns="False"
       DataKeyNames="EmployeeID"
       AutoGenerateEditButton="True"
       DataSourceID="SqlDataSource1">
       <columns>
          <asp:BoundField HeaderText="First Name" 
                    DataField="FirstName" />
          <asp:BoundField HeaderText="Last Name" 
                    DataField="LastName" />
          <asp:BoundField HeaderText="Title" 
                    DataField="Title" />
       </columns>
     </asp:GridView>

  </FORM>
  </BODY>

</HTML>

Look at 

 "<%$ ConnectionStrings:MyNorthwind%>"

Here

"<%$   %>"

is used to databind the Key in web.config.
Its another new feature of asp.net 2.0.

How to find the Primary key of table

While creating some generic database related userinterfaces we always have find the primary key of the table in runtime. Previously in my Code generation wizards I used to search for the first field with "Id" as suffix and give the user chance to change. But now a senario came when i had create an update command of an sqldatasource in runtime. So i had get the primary key to for specifiying the where clause.

Now I could find the "sp_pKeys" in MS-SQlServer to get the primary key
Eg:

use pubs
sp_pKeys 'authors'

How to insert multiple values using single Query

Today i happend to answer this question in a users group, I felt to share the method I used
in many occassions.
EG: If you have a user Service selection interface where customer can subscribe for more than
one Service at a time. You may be using a set of a check boxes or a multi-selection listbox. The best way is to can create a coma seperated list from the check list(say ServiceID),so that you can use a Query something like this :

"INSERT INTO Customer-Services "
& " SELECT " & CustmerID & " , ServicesID "
& " FROM Services WHERE ServicesID IN (" & ComaSerpatedList & ")"

DataSource WebControls in ASP.NET 2.0

ASP.NET 2.0 comes with lots of new ready to use widgets. It can reduces lots of repeatative coding work many have been doing through out for years. One Prominent set of controls(without any UI) are the DataSource WebControls which can be bound to any control as a data provider.

See an example where the SqlDataSource is bound to a New GridView control:

<!DOCTYPE HTML PUBLIC 
  "-//W3C//DTD HTML 4.0 Transitional//EN"> 
<HTML>
  <BODY> 
     <FORM runat="server"> 
        <asp:SqlDataSource 
            id="SqlDataSource1" 
            runat="server" 
            DataSourceMode="DataReader" 
            ConnectionString="Data Source=localhost;
            Integrated Security=SSPI;
            Initial Catalog=Northwind;" 
            SelectCommand="SELECT FirstName,
              LastName, Title 
            FROM Employees"> 
        </asp:SqlDataSource>      
        <asp:GridView id="GridView1"
            runat="server"
            DataSourceID="SqlDataSource1"> 
        </asp:GridView> 
     </FORM> 
  </BODY> 
</HTML> 

Prashant's Consulting Blog

I was thinking of sharing some tips on .Net, Sql Server, OO related stuff and some interesting consulting & programming experiences. It was Ganesh who suggested to start a blog like his at Ganesh's progamming Blog . This resulted in this Blog.

You can find some .Net, Sql Server, OO related stuff here.