Thursday, December 12, 2013

list table name and schema using sql



Just a hours ago, i was looking for code which will list-out all the tables in database with its schema name. I found so many way to do this , but here i found simple and easy way to implement it.

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
 AS ListofTables_withSchema
FROM sys.tables

This will returns all the List of table along with the schema names.
 
 
 
 
 Happy coding...

Saturday, December 7, 2013

Gridview header sorting using C#

In my previous post i explained, how to Search string pattern using sql on SQL i had explained how to Add Image in between Rows of a GridView using C#.
 
Now in this article i will explain one of the useful feature i.e Gridview Sorting.
you need to set the AllowSorting property as a True. and SortExpression Property of columns to the specific field name from the database.
Lets look at the below sample gridview code.
 
<asp:GridView ID="gvDetails" runat="server"  onsorting="gvDetails_Sorting" AllowSorting="True">
<Columns>
<asp:TemplateField HeaderText="Your Name" SortExpression="FirstName">
<ItemTemplate>
<%#Eval("YourName")%>'/>
</ItemTemplate>
</asp:TemplateField>
</asp:GridView>

Now for sorting you need to create one public property which store the value of direction in the viewstate and base on that select query get fired.


public GetSortDir direction
{
  get
  {
    if (ViewState["SortingDir"] == null)
        {
            ViewState["SortingDir"] = GetSortDir.Ascending;
         }
         return (GetSortDir)ViewState["SortingDir"];
   }
   set
   {
       ViewState["SortingDir"] = value;
   }
}

Now check the gridview directon and base on viewstate it will get set the new direction.

    protected void gvDetails_Sorting(object sender, GridViewSortEventArgs e)
    {
        string sortDirection = string.Empty;
        if (direction == sortDirection.Ascending)
        {
            direction = sortDirection.Descending;
            sortDirection = "Desc";
        }
        else
        {
            direction = sortDirection.Ascending;
            sortDirection = "Asc";
        }
       
        DataView sortedView = new DataView(BindGridView());
        sortedView.Sort = e.SortExpression + " " + sortDirection;
        gvDetails.DataSource = sortedView;
        gvDetails.DataBind();
    }


Here BindGridView() is the function which returns the datatable & it contain the query result.
that's it now check the gridview and click on the header and see the sorting effects get added in your gridview.