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">
<asp:TemplateField HeaderText="Your Name" SortExpression="FirstName">

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
    if (ViewState["SortingDir"] == null)
            ViewState["SortingDir"] = GetSortDir.Ascending;
         return (GetSortDir)ViewState["SortingDir"];
       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";
            direction = sortDirection.Ascending;
            sortDirection = "Asc";
        DataView sortedView = new DataView(BindGridView());
        sortedView.Sort = e.SortExpression + " " + sortDirection;
        gvDetails.DataSource = sortedView;

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.

Saturday, November 23, 2013

Search string pattern using sql

In my previous post i explained, how to Parsing delimited string using sql using  SQL and asp net
Now in this article i will explain how to Search the string pattern on entire string using the sql. some time we wanted to search the specific string along with the position then we can think of this way to retrieve the string.

Below i have created some sample query which will retrieve the string on specific position 

create table #TempSQL(csvtext varchar(2000) not null)
insert #
TempSQL select 'tempa,tempb,tempc,tempd,tempe,tempf,tempg' union select 'temp1,temp,temp3,temp4,temp5,temp6'

       dbo.fnGetCsvPart(csvtext,0,default) as pos0
       ,dbo.fnGetCsvPart(csvtext,2,default) as pos2
       ,dbo.fnGetCsvPart(csvtext,2,1) as Entire_string
from #

Here is the generated output from above query.

pos0                       pos2                          Entire_String
------------------ ------------------------------ ------------------------------
temp1                        temp3                   temp3,temp4,temp5,temp6
tempa                        tempc                   tempc,tempd,tempe,tempf,tempg 

Now create this function

create  function dbo.fnGetCsvPart(@csvtext varchar(2000),@indexPos tinyint, @lastPos bit = 0)
returns varchar(5000)

   declare @ivar int; set @ivar = 0
   while 1 = 1
           if @indexPos = 0
                 if @lastPos = 1 or charindex('_',@csvtext,@ivar +1) = 0
                       return substring(@csvtext,@ivar +1,len(@csvtext)-@ivar +1)
                       return substring(@csvtext,@ivar +1,charindex('_',@csvtext,@ivar +1)-@ivar -1)
          select @indexPos = @indexPos-1, @ivar = charindex('_',@csvtext,@ivar +1)
          if @ivar = 0 break
 return null

You may call that directly on your query. check below  example.

select Isnull(dbo.fnGetCsvPart(Document_Name,2,default),'') as [Request]