Thursday, November 25, 2010

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.

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

Carmel School
St.Mary’s School
Crescent public school
Donbosco School
St.Josephs School
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">
<TD><b><u><%#DataBinder.Eval(Container.DataItem, "State")%></u></b></TD>
<asp:repeater id="NestedRepeater" runat="server">

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();
new DataRelation("NestThem",ds.Tables[0].Columns["State"], ds.Tables[1].Columns["State"])
myRepeater.DataSource = ds;

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");

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.

Wednesday, November 24, 2010

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

/****** Object: UserDefinedFunction [dbo].[fnSearchQuery] Script Date: 11/24/2010 17:16:51 ******/
Create Function [dbo].[fnModuleSearchQuery](@Table_Name SYSNAME,@Search_String VARCHAR(100))
Returns Varchar(Max) As
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
Set @Sql_String = 'SELECT * FROM ' + @Table_Name + ' WHERE '
+ @Column_Name + ' LIKE ''%' + @Search_String + '%'''

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

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

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

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

Tuesday, November 23, 2010

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

To Retrive All tables Columns with there data type:-

SELECT AS [Schema], AS [Table], AS [Column],
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 <> 'sysdiagrams'
AND <> 'dtproperties'

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

To Retrive Individual table columns with there datatypes :-

SELECT OBJECT_NAME( AS table_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 = c.cdefault
WHERE (OBJECT_NAME( = 'Your_Table_Name')
ORDER BY c.colorder

Wednesday, November 10, 2010

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.

stupid_is_as_stupid_does as (
select *from tbldual
select *
from stupid_is_as_stupid_does


1 row selected.

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