Add Auto Number (serial number) For an Existing Datatable

Posted: August 19, 2011 | Author: | Filed under: ADO.NET | Leave a comment

It will be a lot easier to do it either from front end if you use datacontrol or from back end in T-SQL query with a Row_number function.

If both options are not your choice, you can work with your datatable object directly through datatable.Load method.

Here is the code snippet to add autoincremental number column to an existing datatable:

 private static DataTable AddAutoIncrementColumn() 
        { 

            DataColumn myDataColumn = new DataColumn(); 
            myDataColumn.AllowDBNull = false; 
            myDataColumn.AutoIncrement = true; 
            myDataColumn.AutoIncrementSeed = 1; 
            myDataColumn.AutoIncrementStep = 1; 
            myDataColumn.ColumnName = "autoID"; 
            myDataColumn.DataType = System.Type.GetType("System.Int32"); 
            myDataColumn.Unique = true; 

            //Create a new datatable 
            DataTable mydt = new DataTable(); 

            //Add this AutoIncrement Column to a new datatable 
            mydt.Columns.Add(myDataColumn);            

            return mydt; 

        } 

protected void Page_Load(object sender, EventArgs e) 
        { 

            DataTable myDataTable = new DataTable(); 
            myDataTable = AddAutoIncrementColumn(); 

            //Read your existing datatable into a datatableReader 
            DataTableReader reader = new DataTableReader(GetTable()); 
            myDataTable.Load(reader); 

            GridView1.DataSource = myDataTable; 
            GridView1.DataBind(); 

        }

http://msdn.microsoft.com/en-us/library/7x8ccbsb.aspx

Another way is to add a column and loop through the datatable to add the counter:
(From this thread: http://forums.asp.net/t/1712346.aspx/1?add+serial+number+in+existing+datatable )
 

dt.Columns.Add(“S.No”, typeof(int));
    for (int count = 0; count < dt.Rows.Count; count++)
         {
             dt.Rows[count][“S.No”] = count+1;
          }

 

Dynamic sort for a GridView

Posted: August 11, 2011 | Author: | Filed under: ASP.NET 2 | Leave a comment

Here is a sample to pass column variables to control SORT ORDER:

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”  
            DataSourceID=”SqlDataSource1″> 
            <Columns> 
                <asp:BoundField DataField=”Item” HeaderText=”Item” SortExpression=”Item” /> 
                <asp:BoundField DataField=”Category” HeaderText=”Category”  
                    SortExpression=”Category” /> 
                <asp:BoundField DataField=”id” HeaderText=”id” SortExpression=”id” /> 
            </Columns> 
        </asp:GridView> 
        <asp:SqlDataSource ID=”SqlDataSource1″ runat=”server”  
            ConnectionString=”<%$ ConnectionStrings:mytest1ConnectionString %>” > 
           
        </asp:SqlDataSource> 
        <asp:Button ID=”Button1″ runat=”server” onclick=”Button1_Click” Text=”Sort” /> 
         <asp:Button ID=”Button2″ runat=”server” onclick=”Button2_Click” Text=”ReverseSort” /> 
 
 
protected void Page_Load(object sender, EventArgs e) 
        { 
            if (!IsPostBack) 
            { 
                SqlDataSource1.SelectCommand = “SELECT id, Item, Category FROM Items order by id desc”; 
                GridView1.DataBind(); 
            } 
        } 
 
        protected void Button1_Click(object sender, EventArgs e) 
        { 
            string sortBySelected = “Item”;  
            string sortBySelected2 = “Category”; 
           
            SqlDataSource1.SelectCommand = “SELECT id,Item, Category FROM Items ORDER BY CASE WHEN @sortBySelected = ‘Item’ THEN Item END, CASE WHEN @sortBySelected2 = ‘Category’ THEN Category END”; 
            SqlDataSource1.SelectParameters.Clear(); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected”, sortBySelected); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected2”, sortBySelected2); 
                       
            GridView1.DataBind(); 
        } 
 
        protected void Button2_Click(object sender, EventArgs e) 
        { 
            string sortBySelected = “Item”; 
            string sortBySelected2 = “Category”; 
 
            SqlDataSource1.SelectCommand = “SELECT id,Item, Category FROM Items ORDER BY CASE WHEN @sortBySelected = ‘Item’ THEN Item END DESC, CASE WHEN @sortBySelected2 = ‘Category’ THEN Category END DESC”; 
            SqlDataSource1.SelectParameters.Clear(); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected”, sortBySelected); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected2”, sortBySelected2); 
 
        } 
    }