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

How To Create a New Login Name in SQL Server?

To create a new login name, you can use the "CREATE LOGIN" statement in a simple syntax like this:

CREATE LOGIN login_name WITH PASSWORD = 'password'  

How To List All Login Names on the Server?

If you want to see a list of all login names defined on the server, you can use the system view, sys.server_principals as shown in this tutorial exercise:

-- Login with sa    SELECT name, sid, type, type_desc FROM sys.server_principals  WHERE type = 'S';  

How To Change the Password of a Login Name?

If a developer lost the password of his or her login name, you can reset the password with the "ALTER LOGIN" statement as shown in this tutorial example:

-- Login with sa    ALTER LOGIN my_DBA WITH PASSWORD = 'mrurli222'; 

How To Change a Login Name?

If you want to change a login name, you can use the "ALTER LOGIN" statement as shown in this tutorial example:

-- Login with "sa"    -- Change login name  ALTER LOGIN Fyi_Login WITH NAME = Dba_Login;  

How To Disable a Login Name?

If you want temporarily disable a login name, you can use the "ALTER LOGIN" statement with a DISABLE keyword. If you want to enable it later on, you can use the ENABLE keyword. The tutorial exercise below shows how to disable and enable login name "Dba_Login":

-- Login with "sa"    -- Disable a login  ALTER LOGIN Fyi_Login DISABLE;    
-- View login status  SELECT name, type, type_desc, is_disabled   FROM sys.server_principals   WHERE type = 'S';  

How To Delete a Login Name?

If you don't want to keep a login name any more, you should delete it by using the "DROP LOGIN" statement as shown in this tutorial example:

-- Login with "sa"    DROP LOGIN Dba_Login;  

How To Create a User Name in a Database?

User names are security principals at the database level. If you want to allow a login name to access a specific database, you need to create a user name in that database and link it to the login name.

Creating a user name can be done by using the "CREATE USER" statement as shown in this tutorial exercise:

-- Login with "sa"    -- Create a login  CREATE LOGIN Fyi_Login WITH PASSWORD = 'IYF'  GO    -- Select a database  USE FyiCenterData;  GO    -- Create a user and link it to a login  CREATE USER Fyi_User FOR LOGIN Fyi_Login;  GO  

Login name "Fyi_Login" should be able to access database "FyiCenterData" through user name "Fyi_User".

How To List All User Names in a Database?

If you want to see a list of all user names defined in a database, you can use the system view, sys.database_principals as shown in this tutorial exercise:

-- Login with sa    -- Select a database  USE FyiCenterData;  GO    -- List all user names  SELECT name, sid, type, type_desc      FROM sys.database_principals WHERE type = 'S';  

How To Delete an Existing Database User?

If you don't want to keep a database user any more, you should delete the user by using the "DROP USER" statement. This tutorial exercise shows how to delete "Dba_User":

-- Login with "sa"    USE FyiCenterData;  GO    DROP USER Dba_User;  

Stored procedure execution on SQL Server startup

I have studied a very interesting topic in SQL Server and wish to blog the same in my site.
Scenario:
On each SQL Server database startup, I need to execute a procedure in my database. It's a very basicscenario in all places.

Solution:

For this, SQL Server is providing an option of using a system stored procedure sp_procoption

create procedure Murli_Insert_Procedure

as

begin

insert into tblMurli(id,val) values (5,'F')

end

EXEC sp_procoption @ProcName = 'Murli_Insert_Procedure',@OptionName = 'startup',@OptionValue = 'true'

Now, your stored procedure is set as a initial startup which will execute on DB start.

Temporary table Vs Temporary variable in SQL Server

We have seen lot of difference between temporary variable and temporary table. Here is a nice difference in Transaction perspective.

Temporary table is transaction dependent and it abides to the database transaction whereas temporary variable is not transaction bound.


Sample Query:

---------------------Temporary table -------------------------------

drop table #temp

create table #temp (id int, val varchar(100))

begin tran ins

insert into #temp values (1,'Venkat')

rollback tran ins

select * from #temp

We are not getting any records indicating the temporary table will bound to the transaction strategies.

------------------Temporary variable --------------------------

Declare @tempval table(id int, val varchar(100))

begin tran ins

insert into @tempval values (1,'Venkat')

rollback tran ins

select * from @tempval

Even we have provided rollback transaction. Records are available in the table variable.