Using Nested Repeater Controls

Yes, DataGrid, DataList and the Repeater Controls are the three main data display controls that ships with ASP.NET.
Well, knowing ‘which to use when’ is what’s important!

Why would Microsoft create three display controls?
Naturally, each of these controls is suited for different tasks and has their own plus and minus points.

Here are a few guidelines that will help you pick the appropriate control depending on your requirements.

DataGrid: (Example tabular display)

Amongst all, I’d say this one is the easiest to develop.
It provides us with features like: Sorting, Paging & Editing.
Its derived from the WebControl class,
and hence we can set its BorderStyle , BackColor etc.
But coming to the performance factor, The DataGrid Control is stated to have the worst performance of the three data web controls.
And another drawback is that, we can’t customize it much according to our needs.
The DataGrid sticks to its simple tabular layout, and that’s where the other two Controls win the poll.


DataList: (Example you'll find the snaps displayed column wise)

The DataList can be used to provide a more customizable interface to the user.
Again this one too is derived from the WebControl class, and hence we can set the same properties.
Using the RepeatColumns property, you can specify how many DataList items should appear per table row.
DataList does provide inline editing, but if you consider the development time required to add in such functionalities,
I’d say be wise in your decision coz this one’s a bit time consuming!


Repeater: (Example you'll find the snaps repeating one below the other)

This control does the least for us, It just does what it says - “Repeats”!
It supports neither paging nor editing.
It’s mainly used to show hierarchical data, like forums.
The control is real handy when it comes to providing a customizable interface to the user. The Repeater class is not derived from the WebControl class, and hence it lacks those stylistic properties.
Coming to the performance issue, this one rules over the other two controls.


Nested Repeater Controls

In this article I’m going to deal with the Repeater Control, to be specific – “Nested Repeater Controls”.
By Nested repeaters, I mean embedding one repeater control within another.


Note:
If you are looking for the source code to bind your data source to a single repeater control,
or for the basics of the Repeater control, then just type in your requirement in the search engine you find on top, and click Search.
Welcome back to this page when you are ready to learn about nesting repeater controls :-)



Where would you use nested repeaters???
Well, Let’s suppose we have a requirement to display all the States, and the list of the Schools within that particular State.

What would we do to get our output look like this
______________________

Kerala
Carmel School
St.Mary’s School
Crescent public school
Bangalore
Donbosco School
St.Josephs School
Chennai
Sacred Heart School
______________________

As we all know, the number of Schools can vary from state to state.
See, now that’s a situation where a nested repeater comes handy.


Let’s begin coding…
Paste the following code within the

tags of your .aspx file.

<TABLE id="Table1" border="0">
<asp:repeater id="myRepeater" runat="server">
<ItemTemplate>
<TR>
<TD><b><u><%#DataBinder.Eval(Container.DataItem, "State")%></u></b></TD>
</TR>
<asp:repeater id="NestedRepeater" runat="server">
 <ItemTemplate>
 <TR>
 <TD><%#DataBinder.Eval(Container.DataItem,"School")%>
 <br>
 </TD>
 </TR>
 </ItemTemplate>
           </asp:repeater>
      </ItemTemplate>
  </asp:repeater>
</TABLE>

Next, Move to your Page_Load( ) event and bind your main repeater.

private void Page_Load(object sender, System.EventArgs e)
{
 SqlConnection con= new SqlConnection("Enter your connection string here");
 SqlDataAdapter sdap =
 new SqlDataAdapter("select distinct(State) from listOFschools;select * from listOFschools",con);
 DataSet ds = new DataSet();
 sdap.Fill(ds);
 ds.Relations.Add(
  new DataRelation("NestThem",ds.Tables[0].Columns["State"], ds.Tables[1].Columns["State"])
  );
 myRepeater.DataSource = ds;
 myRepeater.DataBind();
}

Finally, Enter the following Code in your Main Repeaters ItemDataBound( ) event to bind your nested repeater.

private void myRepeater_ItemDataBound(object sender, System.Web.UI.WebControls.RepeaterItemEventArgs e)
{
  DataRowView dv = e.Item.DataItem as DataRowView;
  if(dv != null)
  {
    Repeater nestedRepeater = e.Item.FindControl("NestedRepeater")  as Repeater;
    if(nestedRepeater != null)
 {
    nestedRepeater.DataSource =  dv.CreateChildView("NestThem");
 nestedRepeater.DataBind();
 }
  }
}

The codes are self-explanatory;
we’re just embedding a repeater control within another.
As you can see, it’s the Relations property of the dataset that draws out the required nested fields.

Run your program and that's it! We now have a nested repeater.
So, Use the Nested Repeater control at the appropriate situation and enhance the look of your page.

Search String From Passing Table Find All Columns No Need to Specify

This Code will Help to Find the string from all the Existing columns from passed table.
 You Need to Pass only tableName and searchString
USE [dbCMS]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSearchQuery]    Script Date: 11/24/2010 17:16:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Function [dbo].[fnModuleSearchQuery](@Table_Name SYSNAME,@Search_String VARCHAR(100))
Returns Varchar(Max) As
BEGIN
 Declare @Query_String Varchar(MAX); /*For Store the Full String*/
  Set @Query_String = '';
 Declare @Column_Name SYSNAME,
  @Sql_String Varchar(MAX)
  
  /*For Store All Column On the Table*/
 Declare Column_Cur Cursor For SELECT Name FROM sys.columns
           WHERE object_id = (Select object_id from sys.objects Where Name = @Table_Name)
           AND system_type_id IN (167, 175, 231, 239)
 
 Open Column_Cur
 Fetch Next From Column_Cur INTO @Column_Name
         WHILE (@@FETCH_STATUS = 0)
  BEGIN
   Set @Sql_String = 'SELECT * FROM ' + @Table_Name + ' WHERE '
                              + @Column_Name + ' LIKE ''%' + @Search_String + '%'''

   Set @Query_String = @Query_String + @Sql_String + ' UNION '
   --Execute(@sql_string)
   --Print @sql_string
      FETCH NEXT FROM Column_Cur INTO @Column_Name
  END

   CLOSE Column_Cur
   DEALLOCATE Column_Cur
 Return (Substring(@Query_String,0,Len(@Query_String)-4))
END


--============For Display on the Records Need to call like this ==========

Declare @String varchar(MAX)
Select @String = dbo.fnSearchQuery('SiteDropBox','Bhilai')
Exec(@String)

Retrieving All Column Names And Types From SQL Server 2005 For .NET

To Retrive All tables Columns with there data type:-

SELECT   schemas.name AS [Schema],
        tables.name AS [Table],
        columns.name AS [Column],
        CASE
            WHEN columns.system_type_id = 34    THEN 'byte[]'
            WHEN columns.system_type_id = 35    THEN 'string'
            WHEN columns.system_type_id = 36    THEN 'System.Guid'
            WHEN columns.system_type_id = 48    THEN 'byte'
            WHEN columns.system_type_id = 52    THEN 'short'
            WHEN columns.system_type_id = 56    THEN 'int'
            WHEN columns.system_type_id = 58    THEN 'System.DateTime'
            WHEN columns.system_type_id = 59    THEN 'float'
            WHEN columns.system_type_id = 60    THEN 'decimal'
            WHEN columns.system_type_id = 61    THEN 'System.DateTime'
            WHEN columns.system_type_id = 62    THEN 'double'
            WHEN columns.system_type_id = 98    THEN 'object'
            WHEN columns.system_type_id = 99    THEN 'string'
            WHEN columns.system_type_id = 104   THEN 'bool'
            WHEN columns.system_type_id = 106   THEN 'decimal'
            WHEN columns.system_type_id = 108   THEN 'decimal'
            WHEN columns.system_type_id = 122   THEN 'decimal'
            WHEN columns.system_type_id = 127   THEN 'long'
            WHEN columns.system_type_id = 165   THEN 'byte[]'
            WHEN columns.system_type_id = 167   THEN 'string'
            WHEN columns.system_type_id = 173   THEN 'byte[]'
            WHEN columns.system_type_id = 175   THEN 'string'
            WHEN columns.system_type_id = 189   THEN 'long'
            WHEN columns.system_type_id = 231   THEN 'string'
            WHEN columns.system_type_id = 239   THEN 'string'
            WHEN columns.system_type_id = 241   THEN 'string'
            WHEN columns.system_type_id = 241   THEN 'string'
        END AS [Type],
        columns.is_nullable AS [Nullable]


FROM              sys.tables tables
   INNER JOIN    sys.schemas schemas ON (tables.schema_id = schemas.schema_id )
   INNER JOIN    sys.columns columns ON (columns.object_id = tables.object_id)


WHERE     tables.name <> 'sysdiagrams'
   AND   tables.name <> 'dtproperties'

ORDER BY [Schema], [Table], [Column], [Type]

--===============================

To Retrive Individual table columns with there datatypes :-

SELECT OBJECT_NAME(c.id) AS table_name, 
    c.name, t.name AS data_type, 
    c.isnullable, com.text AS default_text, 
    c.length, c.prec AS numeric_precision,
    c.scale AS numeric_scale, c.colorder
FROM sys.syscolumns AS c LEFT OUTER JOIN
  sys.systypes AS t ON c.type = t.type AND 
  c.xtype = t.xtype LEFT OUTER JOIN
 sys.syscomments AS com ON com.id = c.cdefault
WHERE (OBJECT_NAME(c.id) = 'Your_Table_Name')
ORDER BY c.colorder

Use With Clause in Sql

So here I am writing even more documentation for my current Gig, and thinking once again, why not post it to OraFAQ and get double duty out of the document. So here is a discussion of the WITH clause that comes with the SELECT statement now. It is easy to use, and handy as all get out, but seems many people have not yet noticed it. Hmm... a SELECT statement that does not start with SELECT.

I like examples as a learning tools, so lets start off with some seemingly silly code.

WITH
  stupid_is_as_stupid_does as (
                               select *from tbldual
                              )
select *
from stupid_is_as_stupid_does
/

tbldual
----------
X

1 row selected.

Note : You Must be select the With table after the execution o.w. you will get error like ) bracket not closed