Friday, December 31, 2010

Finds a Control recursively in Asp.net

On one of my project i need to Find the controls till get the result control so i have built the code like this...

Below is Method Definition :

/// <summary>
/// Finds a Control recursively. Note finds the first match and exists
/// </summary>
/// <param name="ContainerCtl"></param>
/// <param name="IdToFind"></param>
/// <returns></returns>
public static Control FindControlRecursive(Control Root, string Id)
{
string str =string.Empty;
if (Root.ID == Id)
return Root;
foreach (Control Ctl in Root.Controls)
{
Control FoundCtl = FindControlRecursive(Ctl, Id);
if (FoundCtl != null)
{
switch (Id)
{
case "hdnProductId":
staticProductId = ((System.Web.UI.HtmlControls.HtmlInputHidden)FoundCtl).Value;
break;
case "hdnProductName":
staticProductName = ((System.Web.UI.HtmlControls.HtmlInputHidden)FoundCtl).Value;
break;
}
break;
}
}
return null;
}

Below is Method Definition in GIF Format :


Below is how to call that method:-

FindControlRecursive(this.Page.Master, "hdnProductId");
FindControlRecursive(this.Page.Master, "hdnProductName");

Below is how to call that method in Gif Format:-

Wednesday, December 29, 2010

Convert month number to month name in sql

In this blog I will explain you how to convert month number to month name in sql. To explain it I have used

SELECT DATENAME( MONTH , DATEADD( MONTH , @MONTHNUMBER , 0 ) - 1 ).Below is the code snippet:


DECLARE @MONTHNUMBER INT

SET @MONTHNUMBER=1

SELECT DATENAME( MONTH , DATEADD( MONTH , @MONTHNUMBER , 0 ) - 1 ) AS [MONTHNAME]

-OUTPUT


MONTHNAME

------------------------------

January

DECLARE @MONTHNUMBER INT


SET @MONTHNUMBER=12

SELECT DATENAME( MONTH , DATEADD( MONTH , @MONTHNUMBER , 0 ) - 1 ) AS [MONTHNAME]

--OUTPUT


MONTHNAME

------------------------------

December



2 ).Below is the code snippet:

SELECT CONVERT(VARCHAR(3), DATENAME(MM, CreatedDate_ColumnName), 100)

Monday, December 27, 2010

Clear Controls in Asp.Net

Main Function to Read All controls in do action :-



Call the Method by this way. here in my first line this is multiview id i want to clear all controls under multiview, and on the second one all controls available in the page..

Tuesday, December 21, 2010

Distinct in XSLT

This Example Use for sort the year in xslt for this we need to get unique year id i.e. in xslt generic-id bases we can trace unique here example below:----

Test XML Feed Here:--
<state id="26179" status="Approved" golive="Wednesday, December 22, 2010" created="Wednesday, December 22, 2010" golivetime="9:00:00 AM" createdtime="9:42:11 AM" creator="Indigo Consulting">
<elements state="26179" parent="0">
<element id="97398" position="1" type="Custom">
<content>
<day>6</day>
<month>February</month>
<year>1996</year>
<title>testing first new2</title>
<historyimage />
</content>
</element>
<element id="97399" position="1" type="Custom">
<content>
<day>4</day>
<month>February</month>
<year>1996</year>
<title>testing first similar new2</title>
<historyimage />
</content>
</element>
<element id="97403" position="1" type="Custom">
<content>
<day>4</day>
<month>October</month>
<year>2012</year>
<title>testing first new5</title>
<historyimage />
</content>
</element>
</elements>
</state>

XSL Content:-
-----------
<xsl:key name="distinctYear" match="state/elements/element[@type = 'Custom']/content" use="./year"></xsl:key>

---Under Applytemplate section--------------
<xsl:for-each select="state/elements/element[@type='Custom']/content[generate-id(key('distinctYear', ./year)) = generate-id()]">
<xsl:sort select="year"/>
<li>
<a href="#{year}">
<xsl:value-of select="year" />
</a>
</li>
</xsl:for-each>

Sunday, December 19, 2010

ASP.NET 2.0 MasterPages and FindControl()

Argh. I'm going through an older application and replacing a somewhat complex scheme of user control templating with Master Pages today. For the most part this has been going real well until I hit a page that that relies on page inheritance where there's a common page base class that needs to have access to the controls on the page.

ASP.NET has never made this exactly easy, because the base class doesn't allow you access to the controls from the lower level as ASP.NET adds the properties higher up in the hierarchy. In the past I've been working around this by adding properties for the controls to the base class and then overriding these properties, but in ASP.NET 2.0 the control definitions are auto-generate with no chance to override the control definitions. The only workaround has been using FindControl() and dynamically retrieve the control definitions.

And this is where things get a bit tricky with MasterPages. The problem is that when you use MasterPages the page hierarchy drastically changes. Where a simple this.FindControl() used to give you a control instance you now have to drill into the container hierarchy pretty deeply just to get to the content container.

protected Label lblError = null;

protected DataGrid dgItemList = null;

protected void AssignControls()

{

this.lblError = this.FindControl("lblError") as Label;

this.dgItemList = this.FindControl("dgItemList") as DataGrid;

}

you now have to drill into the containership with code like this:

protected void AssignControls()

{

this.lblError = this.Master.FindControl("Content").FindControl("lblError") as Label;

this.dgItemList = this.Master.FindControl("Content").FindControl("dgItemList") as DataGrid;

}
Image Is below:-


This isn't so bad, except when you're trying to figure out how to get to your controls.

It really seems lame that Microsoft hasn't added a recursive FindControl() method to the Control class that drills into child containers. While this certainly isn't optimal in terms of performance it sure would make life a lot easier in a lot of situations, and this surely is one of them.

Not exactly rocket science to create a method that does this:

/// <summary>
/// Finds a Control recursively. Note finds the first match and exists
/// </summary>
/// <param name="ContainerCtl"></param>
/// <param name="IdToFind"></param>
/// <returns></returns>

public static Control FindControlRecursive(Control Root, string Id)

{

if (Root.ID == Id)

return Root;

foreach (Control Ctl in Root.Controls)

{

Control FoundCtl = FindControlRecursive(Ctl, Id);

if (FoundCtl != null)

return FoundCtl;

}

return null;

}

with this the code becomes:

/// <summary>
/// Assigns controls from the subclassed control to this instance so
/// we always can access the controls in our base class.
/// </summary>

protected void AssignControls()

{

this.lblError = wwWebUtils.FindControlRecursive(this.Master,"lblError") as Label;

this.dgItemList = wwWebUtils.FindControlRecursive(this.Master, "dgItemList") as DataGrid;

}


Image Is below:-




Although this is easier, I suspect it's better to do the explicit thing if that option is available to you as it probably has better performance. Also I suspect Microsoft didn't include this sort of a function in ASP.NET natively because there's potential ambiguity here – there could be more than one control Id that matches a name.

Tuesday, December 14, 2010

Read XML File Node Wise using with xml DocumentElement And display on label


XMl FILE Strecture
<?xml version="1.0" encoding="utf-8" ?>
<adminModules>
<Module id="1" parentid="0" url="javascript:void(0);" title="Locator" real="false" />
</adminModules>
===================
using System.Xml;
string strFileName = Server.MapPath("~/Modules/ModuleMenu/AdminModules.xml");
XmlDocument oXmlDoc = new XmlDocument();
try
{
oXmlDoc.Load(strFileName);
}
catch (Exception ex)
{
lblMessage.Text ="Error: " + ex.Message;
}
XmlNode oNode = oXmlDoc.DocumentElement;
lblMessage.Text += "Node Name: " + oNode.Name;
XmlNodeList oNodeList = oNode.SelectNodes("/adminModules/Module/@title");
lblMessage.Text += "NodeList count=" + oNodeList.Count;
for (int x = 0; x < oNodeList.Count; x++)
{
lblMessage.Text += "NodeList Item#" + x + " " + oNodeList.Item(x).InnerText;
}

Thursday, December 9, 2010

Split string in xsl/xslt

I have written code here for split the text in xsl. might be this is helpfull to you.
this is a function that split the string and print here..
<xsl:template name="SplitText">
<xsl:param name="inputString"/>
<xsl:param name="delimiter"/>
<xsl:choose>
<xsl:when test="contains($inputString, $delimiter)">
<xsl:value-of select="substring-before($inputString,$delimiter)"/>
<xsl:text disable-output-escaping = "no"> </xsl:text>
<xsl:call-template name="SplitText">
<xsl:with-param name="inputString" select="substring-after($inputString,$delimiter)"/>
<xsl:with-param name="delimiter" select="$delimiter"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:if test="$inputString != ''">
<xsl:value-of select="$inputString"/>
</xsl:if>
</xsl:otherwise>
</xsl:choose>
</xsl:template>


Here i have call the parent method with two parameters
1. Input string (Full string)
2. Delimiter (Split by char string)

In My example i have pass varModuleId that is some number with are separated by comma, which i need to split and use in code might be this will helpful to all
==================================

<xsl:call-template name="SplitText">
<xsl:with-param name="inputString" select="$varModuleId"/>
<xsl:with-param name="delimiter">,</xsl:with-param>
<!--<xsl:with-param name="delimiter" select="$delimiter"/>-->
</xsl:call-template>

Replace String with space in Xsl/Xslt

Replace Method for Replace contents,

<xsl:template name="replaceCharsInString">
<xsl:param name="stringIn"/>
<xsl:param name="charsIn"/>
<xsl:param name="charsOut"/>
<xsl:choose>
<xsl:when test="contains($stringIn,$charsIn)">
<xsl:value-of select="concat(substring-before($stringIn,$charsIn),$charsOut)"/>
<xsl:call-template name="replaceCharsInString">
<xsl:with-param name="stringIn" select="substring-after($stringIn,$charsIn)"/>
<xsl:with-param name="charsIn" select="$charsIn"/>
<xsl:with-param name="charsOut" select="$charsOut"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$stringIn"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>


Below code for call the replace method and provided by three parameters
1. Full String
2. Replace text (charIn)
3 Replace from (charOut)
In my example i have used amp; i.e. & to replace with '' i.e. space for your reference code is here.

=========================
<xsl:variable name="myString" select="page/meta/sharefeed"/>
<xsl:variable name="myNewString">
<xsl:call-template name="replaceCharsInString">
<xsl:with-param name="stringIn" select="string($myString)"/>
<xsl:with-param name="charsIn" select="'amp;'"/>
<xsl:with-param name="charsOut" select="''"/>
</xsl:call-template>
</xsl:variable>
<xsl:variable name="myNewRealString" select="string($myNewString)"/>

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.


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.

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


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)

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 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

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.


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

Tuesday, October 26, 2010

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.

list column names for a table

To list the column names for a table:

In SQL Server, we can retrieve these details by two methods.

1. This can be taken from the sys.objects and sys.columns table (Used in SQL Server 2000 and higher versions).
2. Another option is to use INFORMATION_SCHEMA.COLUMNS (This is introduced in SQL Server 2005 and higher version)


Below is the query to achieve the same,

drop table venkat_table
go
create table venkat_table (id int,val varchar(100),val1 varchar(100))

First Option:

SELECT *FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='venkat_table'

Second Option:

Joining sys.objects and sys.columns table based on the object Id.

SELECT C.name,O.* FROM sys.objects O INNER JOIN sys.columns CON O.object_id=C.object_id WHERE O.name='venkat_table'

View Log file information in SQL Server

Scenario:

Usually, we will face log space issue in our SQL Server. Most of the time, we dont know the reason for our logfile space. I would suggest to get a third party tool to check it. By reading through some of the forums. I found there is some undocumented DBCC command which is used to dig the log file and get some information to us.

This command won't give a very great information at least we can get some high level information. Let's see the command,
DBCC Log(Databasename, Option number)

Default option number is 0

For instance,

dbcc log ('master',0) - 0 indicates a very minimal information.

The above command will provide the below details,

Reindexing the entire database in sql server

Re-indexing the entire database:

Usually, if the database is too slow. DBA's were advised to re-index the tables. For some cases, we need to rebuild the indexes availables in the entire database.

For this scenario, we can rebuild the index by taking each table and re-index all the indexes associated with each table.

Fill factor:

Amount or compactness of data in the leaf level is defined by the term fill factor. Based on the operations on the database DBA's will decide the fill factor. If the insert/update/delete are very high in that case we will have very less fill factor (Around 60 to 70). If there is very less insert/update/delete, in that case we will have very high fill factor (Around 90). On an average, we will give 80 -90 %.



Below is the script to achieve this,

----------------------------------------------------------------------------
DECLARE @DatabaseTable VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT name AS DatabaseTable
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DBCC DBREINDEX('+ @DatabaseTable + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @DatabaseTable
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO


-------------------------------------------------------------

How abt re-organsing the indexes (Rebuild index vs reorganise index):

1. Re-built can't be done on the production run time (Due to its high impact on rearranging the data and recreate of index) where as re-organise index can be done.

2. Indexes were recreated in case of re-building the index.

3. Re-built index is very effective when compared to the other.


To find the free space available in the log files:

Below is the command used to identify the amount the space available or percentage used.

DBCC SQLPERF (LOGSPACE)

Reindexing the entire database in sql server

Re-indexing the entire database:

Usually, if the database is too slow. DBA's were advised to re-index the tables. For some cases, we need to rebuild the indexes availables in the entire database.

For this scenario, we can rebuild the index by taking each table and re-index all the indexes associated with each table.

Fill factor:

Amount or compactness of data in the leaf level is defined by the term fill factor. Based on the operations on the database DBA's will decide the fill factor. If the insert/update/delete are very high in that case we will have very less fill factor (Around 60 to 70). If there is very less insert/update/delete, in that case we will have very high fill factor (Around 90). On an average, we will give 80 -90 %.



Below is the script to achieve this,

----------------------------------------------------------------------------
DECLARE @DatabaseTable VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT name AS DatabaseTable
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DBCC DBREINDEX('+ @DatabaseTable + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @DatabaseTable
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO


-------------------------------------------------------------

How abt re-organsing the indexes (Rebuild index vs reorganise index):

1. Re-built can't be done on the production run time (Due to its high impact on rearranging the data and recreate of index) where as re-organise index can be done.

2. Indexes were recreated in case of re-building the index.

3. Re-built index is very effective when compared to the other.


To find the free space available in the log files:

Below is the command used to identify the amount the space available or percentage used.

DBCC SQLPERF (LOGSPACE)

Performance monitoring command in SQL Server

DBCC SQLPERF(umsstats) - This command will provide all the OS related information for your threads in the SQL Server instance. It provides the below values,

Node Id
Avg Sched
LoadSched
SwitchesSched Pass
IO Comp Passes
Scheduler ID (The below data will be populated for each scheduler ID)
online - Whether it's online or not
num tasks - Number of tasks
num runnable - Runnable tasks
num workers - Parallel workers involved in the scheduler
active workers - Active parallel workers involved in the scheduler
work queued - Works queued up in this schedule
cntxt switches - How many context switching happening in this schedule
cntxt switches(idle) - Idle threads after context switches.
preemptive switches - Prirority switches happened in this schedule.

Get details on the table contigencies

DBCC Showcontig will give a detailed insight on the data pages(OS level) for the tables.

This command will scan the table and provide the below details,

- Pages Scanned......................... - Number of pages scanned (Denotes the number of pages occupied by this table )
- Extents Scanned..............................: Number of Extents scanned (Denotes the number of Extents occupied by this table )
- Extent Switches..............................: Mixed extents
- Avg. Pages per Extent........................
- Scan Density [Best Count:Actual Count].......: How the data is compacted in the page.
- Extent Scan Fragmentation ...................: Fragmentation from Extent point of view
- Avg. Bytes Free per Page.....................
- Avg. Page Density (full) ...................... (High page density denotes the data is intact and your search will be faster. If the value is less, it indicates your data is scattered.

DBCC SHOWCONTIG --- This command will provide the details for the whole database tables.

DBCC SHOWCONTIG ('Murli_table') -- This command will provide the necessary information for that particular table.

Get Databasename from Database ID in SQL Server

DB_Name(DB_ID) will provide you the database name. The hierarchy is like system tables will have the initial values followed by user databases.

select DB_NAME(1) --- returns Master database
select DB_NAME(2)--- returns Tempdb database
select DB_NAME(3)--- returns Model database
select DB_NAME(4)--- returns MSDB database
select DB_NAME(5)--- returns User database1 etc..,

To find recently executed queries in SQL Server

To find recently executed queries,

Select dmStats.last_execution_time as 'Last Executed Time',dmText.text as 'Executed Query' from sys.dm_exec_query_stats as dmStats Cross apply sys.dm_exec_sql_text(dmStats.sql_handle) as dmText Order By
dmStats.last_execution_time desc

Monday, October 25, 2010

Update records form diff tables

This blog post illustrates how to update more than one column in a table with values from columns in another table and explains how to do it in the three RDBMS that we support.

Table Structures and values:

TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:


I. TableA
a b c d
1 x y z
2 a b c
3 t x z

II. TableB
a1 b1 c1 d1 e1
1 x1 y1 z1 40
2 a1 b1 c1 50



The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

Oracle:

UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
/


Results after the update:

a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z



SQL Server:

UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO

Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.




Results after the update:

a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z



DB2 LUW:

–Same as Oracle–

UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40);



Results after the update:

a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z



NOTE:

It is very important to make sure that your where clause for the update statement is correct since that is what identifies the records that the update statement is going to qualify and do the update upon. If it is incorrect, then you can get wrong results. The reason I am mentioning this is because I have seen people write wrong where clauses and then wondering what went wrong because they specified the correct condition in the SET clause.

In the above example, if the Where condition was omitted, the other record’s columns would be updated to NULL value and this will be the final result set:


a b c d
————————————
1 Null Null Null
2 a1 b1 c1
3 Null Null Null

Configure SQL Server to Store ASP.NET Session State

For configure the Sql Server session state you must need to follow these steps first :-
1. First you need to execute script file i.e. InstallSqlState.sql and this script file automatically create database and required procedure to stores the session values
Note:- While execution of script it gives an error like "The specified @name ('[Uncategorized (Local)]') already exists" ignore this error
Note:- This InstallSqlState.sql you can get it from your pc it's in your windows folder dir Ex : C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322

2. You need to set the connection string in your configuration file ex:-
< mode="SQLServer" sqlConnectionString="data source=INDIGO62\SQLEXPRESS2008;user id=sa;password=murli" cookieless="false" timeout="20" />

Now you can access session objects as you used in In Proc state same as use here
Ex : Session["Name"] = "Murli";

For testing the records are in available or not in your database
check two tables ASPStateTempApplications and ASPStateTempSessions
in your tempdb database

for start world wide web publishing dir type this command in command prompt
net start w3svc
for strop this
net stop w3svc

Friday, October 22, 2010

Access Viewstate from another page

Yes, we can access the viewstate variables across pages. This is only possible if Cross Page Posting or Server.transfer is used to redirect the user to other page. If Response.redirect is used, then ViewState cannot be accessed across pages.

Before you continue reading this article, please read these articles on Cross Page Posting and Server.transfer.

Ok, so all set now... I will demonstrate this using the demo created by me. You can download the demo from the link at the top of this article.

I have created two *.aspx pages named:

1. ViewStateContainer.aspx: This page sets the ViewState variable and transfers the user to another page using Server.transfer.
2. AccessViewState.aspx: This page accesses the ViewState variable of ViewStateContainer.aspx page.

This is the code of ViewStateContainer.aspx page:
?

public partial class ViewStateContainer : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
ViewState["Page1"] = "Page1 ViewState";
Server.Transfer("AccessViewState.aspx");
}

public StateBag ReturnViewState()
{
return ViewState;
}
}

As you can see, I have set a ViewState variable in Page Load and transfer the user to AccessViewState.aspx page using the Server.transfer() method.

This page also contains a method ReturnViewState() which actually returns the ViewState of this page to the calling function. The return type of the method is StateBag class.

StateBag class: This class is the primary storage mechanism for all HTML and Web server controls. It stores attribute/value pairs as strings associated with the control. It tracks changes to these attributes only after the OnInit method is executed for a page request, and saves the changes to the page's or control's viewstate.

Now let's take look at AccessViewState.aspx Page code:
?


public partial class AccessViewState : System.Web.UI.Page
{
private StateBag PreviousPageViewState
{
get
{
StateBag returnValue = null;
if (PreviousPage != null)
{
Object objPreviousPage = (Object)PreviousPage;
MethodInfo objMethod = objPreviousPage.GetType().GetMethod ("ReturnViewState");
return (StateBag)objMethod.Invoke(objPreviousPage, null);
}
return returnValue;
}
}

protected void Page_Load(object sender, EventArgs e)
{
if (PreviousPage != null)
{
if (PreviousPageViewState != null)
{
Label1.Text = PreviousPageViewState["Page1"].ToString();
}
}
}
}

Whenever we use Server.transfer or Cross Page Posting, We can get the previous page object via PreviousPage property. Using Previous Page, we can find the controls of the previous page. For example, one can access Label control placed in ViewStateContainer Page in current Page.

Looking at the code, I have created a PreviousPageViewState property in this page, which returns the previous page's ViewState. It first checks whether PreviousPage is null or not, if it's not null, then it creates an object of the previous page. Now using Reflection, we can invoke the method of the previous class. Using MethodInfo class, I have invoked the ReturnViewState() method of ViewStateContainer Page.

In Page_Load event, I am able to access the ViewState variable of ViewStateContainer Page. You can access all the viewstate variables set in ViewStateContainer Page.

Thursday, October 21, 2010

Find Postback Control ID in Asp.net c#


string controlID = Page.Request.Params["__EVENTTARGET"];
Control postbackControl = null;
if (controlID != null && controlID != String.Empty)
{
postbackControl = Page.FindControl(controlID);
}
else
{
foreach (string ctrl in Page.Request.Form)
{ //Check if Image Button
if (ctrl.EndsWith(".x") || ctrl.EndsWith(".y"))
{
postbackControl = Page.FindControl(ctrl.Substring(0, ctrl.Length - 2));
break;
}
else
{
postbackControl = Page.FindControl(ctrl);
//Check if Button control
if (postbackControl is Button)
{
break;
}
}
}
}
Response.Write(postbackControl.ID);

Sort values in XSL

Distinct Year

<xsl:key name="distinctYear" match="state/elements/element[@type='Custom']/content" use="substring(date,7,10)"></xsl:key>

I have used three attributes here i.e.
1. name :- this is a key name which will use further.
2. match :- this is the route of content data
3. use :- this is attrubute value / name which we want to sort

<select id="ddYear" name="ddYear" onchange="javascript:fnOnchange()">
<xsl:for-each select="state/elements/element[@type='Custom']/content[generate-id() = generate-id(key('distinctYear', substring(date,7,10)))]">
<xsl:sort select="substring(date,7,10)" data-type="number" order="ascending" />
<option>
<xsl:value-of select="substring(date,7,10)"/>
</option>
</xsl:for-each>
</select>

here i used for-each loop to find all elements with distinct values

ex:-
generate-id() :- The generate-id() function returns a string value that uniquely identifies a specified node.
key(Param1,Param2) :- Param1 :- this is key name which assigned
Param2 :- this is the string value which want to be destinected

Monday, October 18, 2010

Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Using SqlBulkCopy to Import Excel Spreadsheet Data into SQL Server

Let's take an Excel Workbook with a worksheet, called Data, that contains 1000 rows of nonsense data broken into 2 columns, ID and Data.

I want to copy this data into a SQL Server Database Table, called ExcelData, with the same schema.

Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:

// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft
.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";

// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.;
Initial Catalog=Test;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
}
}

GridView Row Edit, Delete and Update

In this Post I am going to explain a simple light weight GridView Edit, Delete, and Update functionality. We are going to apply this functionality on Northwind Database, Employee table.

Following are the screen shots.

normal-vieweditmodeview

First of all I am going to explain about creating a Connection String.Connection String has mainly 3 properties.
DataSource — DataSource is your SQL Server name.
Initial Catalog
— Your Database name (Northwind in this case).
Integrated Security
– True

Integrated Security will access the SQL Server with the current windows identity established on the Operating System. For more information about Integrated Security refer this. We usually place the connection string in the Web.config file under “Configuration ==> ConnectionStrings” section.

Here is my connection string.


<connectionStrings>
<add name=“NorthwindConnectionString“ connectionString=“Data Source=*****(SQL Server Name);Initial Catalog=NorthWind;Integrated Security=True;“/>
</connectionStrings>

To learn more about different type of connection strings refer this.

Then, after we are done with your connection string in the Web.config file let’s move on to the C# code.

The Following Snippet is to bind GridView.
private void BindGridData() {
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))

{

conn.Open();

using (SqlCommand comm = new SqlCommand(“select E.EmployeeID,E.FirstName,E.LastName,E.Title,E.Country from Employees E”, conn))

{
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}

The First Line using statement allows the programmer to specify when objects that use resources should release them. The object provided to the using statement must implement the IDisposable interface. This interface provides the Dispose method, which should release the object’s resources. Refer this for Complete Reference.

The following is how we write asp.net GridView web server control code in aspx page. I guess most of the people will be aware of the GridView events. For complete Reference refer this.

In the Code you can see I put AutoGenerateColumns=”false”. That means I have to write column names whatever I want to show to the user. If we put AutoGenerateColumns=”True”, that means u don’t have to write any custom code on your page and user can see each and every column in your dataset or datatable whatever you are binding to your gridview.

It is always good to have some control on these instead of giving up everything to it. I want to hide EmployeeID for the user. But I need it for querying the databse while updating and deleting the user stuff. So

How can we hide a column in a GridView and access it in the Code behind ?

We can do this in several ways!!! , But depends on what column we want to hide. I always follow this method.

Writing a Template Field and making it invisible. In the template field I will Place a label or some controls, So that it’s very easy to access those controls in the code behind by using FindControl method.

Suppose if we are trying to hide primary key of our table, there is actually no need to hide. GridView has a Property called DataKeyNames. We have to give our primary key column name to it. It will do the rest. we can see how we can access that in the code behind in the GridView_Rowupdating Event which I am going to explain later part of this article.

What is TemplateField, ItemTemplate, and EditItemTemplate?

We will use Template Field, whenever we want to define some custom control in the data bound controls like GridView, DataGrid or DataRepeater.

ItemTemplate is the content to display for the items in the TemplateField.

EditItemTemplate as the name itself it is the content to display for the items in the TemplateField when it is in Edit mode. Refer this for complete reference.

Take this simple Snippet.

<asp:TemplateField HeaderText=”LastName”>
<ItemTemplate>
<asp:Label runat=”server” ID=”LastName” Text=’<%#Eval(“LastName”) %>‘ />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat=”server” ID=”txtLastName” Text=’<%#Eval(“LastName”) %>‘ />
<asp:RequiredFieldValidator runat=”server” ID=”rfdLastName” ControlToValidate=”txtLastName” ValidationGroup=”var1″ ErrorMessage=”*” />
</EditItemTemplate>
</asp:TemplateField>

In the above Snippet you are seeing a label in Item Template that means whenever we are showing GridView to the user Label will be visible to the user. If he clicks edit (or) in the edit mode he can be able to see the Textbox.

<asp:BoundField HeaderText=”FirstName” DataField=”FirstName” ReadOnly=”false”/>

The above piece of code will also do the same as label in ItemTemplate and Textbox in the EditItemTemplate. By default all the boundfields will be trasferred as Textboxes in Edit Mode. To avoid this we need to keep the property ReadOnly as false, Then in the edit mode nothing will happenes. It just visible like a label.

The reason I use TemplateField instead of BoundField is that, it is very easy to grab those controls in the codebehind if we user labels or textboxes. In case of the bound field’s we need to check what column it is and need to use index of that cloumn, which is little hard and even in future if we change the column order we have to change the code too.

<asp:GridView ID=”GridView1″ runat=”server” GridLines=”None” AutoGenerateColumns=”false” AlternatingRowStyle-BackColor=”#EEEEEE” EditRowStyle-BorderColor=”Red” onrowcancelingedit=”GridView1_RowCancelling” onrowcommand=”GridView1_RowCommand” onrowdeleting=”GridView1_RowDeleting” onrowediting=”GridView1_RowEditing” onrowupdating=”GridView1_RowUpdating” DataKeyNames=”EmployeeID”>

<Columns>

<asp:TemplateField Visible=”false” HeaderText=”EmployeeID”> <ItemTemplate>

<asp:Label runat=”server” ID=”EmployeeID” Text=’<%#Eval(“EmployeeID”)%>‘ />

</ItemTemplate>

</asp:TemplateField>

<%–<asp:BoundField HeaderText=”FirstName” DataField=”FirstName” />–%>

<asp:TemplateField HeaderText=”LastName”>
<ItemTemplate>
<asp:Label runat=”server” ID=”LastName” Text=’<%#Eval(“LastName”) %>‘ />
</ItemTemplate>

<EditItemTemplate>

<asp:TextBox runat=”server” ID=”txtLastName” Text=’<%#Eval(“LastName”) %>‘ />
<asp:RequiredFieldValidator runat=”server” ID=”rfdLastName” ControlToValidate=”txtLastName” ValidationGroup=”var1″ ErrorMessage=”*” />

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”Title”>

<ItemTemplate>
<asp:Label runat=”server” ID=”Title” Text=’<%#Eval(“Title”) %>‘ />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat=”server” ID=”txtTitle” Text=’<%#Eval(“Title”) %>‘ />
<asp:RequiredFieldValidator runat=”server” ID=”rfdTitle” ControlToValidate=”txtTitle” ValidationGroup=”var1″ ErrorMessage=”*” /> </EditItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”Country”>

<ItemTemplate>

<asp:Label runat=”server” ID=”Country” Text=’<%#Eval(“Country”) %>‘ />

</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat=”server” ID=”txtCountry” Text=’<%#Eval(“Country”) %>‘ />
<asp:RequiredFieldValidator runat=”server” ID=”rfdCountry” ControlToValidate=”txtCountry” ValidationGroup=”var1″ ErrorMessage=”*” />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Action”>
<ItemTemplate>
<asp:LinkButton ID=”btnEdit” Text=”Edit” runat=”server” CommandName=”Edit” /> <br />
<asp:LinkButton ID=”btnDelete” Text=”Delete” runat=”server” CommandName=”Delete” />
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID=”btnUpdate” Text=”Update” runat=”server” CommandName=”Update” />
<asp:LinkButton ID=”btnCancel” Text=”Cancel” runat=”server” CommandName=”Cancel” />
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

If you see the Last TemplateField part in the above code, I am using the link buttons for edit, update, delete and cancel. May be you may think why am I using link buttons as we are provided with some command field buttons by GridView.

1) If we use command buttons we have less control on them while doing validations, i.e. while assigning validation groups.

2) Secondly if you want to include a custom field (some button or link) in the same column it’s not possible.

If we use proper command names for the buttons like “EDIT”, “DELETE”, “UPDATE”, “CANCEL” these will also trigger the appropriate GridView Events.

Ok let’s move on to Code behind what we have to do when user clicks Edit, Delete, Update and Cancel

When we click on Edit the OnRowEditing event will be fired. You can see the C# code below

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindGridData();
}

In the above code snippet as you see GridViewEditEventArgs will give the row number whatever you are editing by NewEditIndex property. So GridView will put the appropriate row into the Edit Mode by assigning row number to its EditIndex property. Then again we have to call that BindGridData () method to bind data for the textboxes.

So once after done with editing data, if we click update the following method will be triggered. It will call OnRowUpdating Event.

1) The First line is to get the Primary Key of the table using DataKeyNames Property of GridView.

2) The Second line is to access the value of the invisible column.

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
string s = GridView1.DataKeys[e.RowIndex].Value.ToString();
Label EmployeeID = GridView1.Rows[e.RowIndex].FindControl(“EmployeeID”) as Label; TextBox LastName = GridView1.Rows[e.RowIndex].FindControl(“txtLastName”) as TextBox; TextBox Title = GridView1.Rows[e.RowIndex].FindControl(“txtTitle”) as TextBox;
TextBox Country = GridView1.Rows[e.RowIndex].FindControl(“txtCountry”) as TextBox; String UpdateQuery = string.Format(“UPDATE Employees SET LastName=’{0}’, Title=’{1}’,Country=’{2}’ WHERE EmployeeID = {3}”,LastName.Text, Title.Text,Country.Text, Convert.ToInt32(EmployeeID.Text));

GridView1.EditIndex = -1;

BindGridData(UpdateQuery);

}

Once we access the required fields in the code behind, we need to update the data in the database and show the updated data to the user. So am calling the method BindGridData method which is overloaded . I am passing Update Query as a parameter to it. You can see that method in the following snippet.

private void BindGridData(string Query)

{

string connectionstring = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

using (SqlConnection conn = new SqlConnection(connectionstring))
{
conn.Open();
using (SqlCommand comm = new SqlCommand(Query + “;select E.EmployeeID,E.FirstName,E.LastName,E.Title,E.Country from Employees E”, conn))
{
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}

If the user click cancels the following event (OnRowCancelling) will be fired. When you are setting the value of the EditIndex to -1 that means you are exiting from the editmode. So i.e. when user clicks cancel, here we are exiting from the editmode and rebinding the data to the GridView by calling BindGridData method.

protected void GridView1_RowCancelling(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindGridData();
}

If the user clicks the Delete button OnRowDeleting event will be fired. The following is the code for that event. Going into that code we are trying to get the primary key of the employee table(EmployeeID) by using GridView DataKeyNames and then pass the Query to the BindGridData method to bind the updated data to the GridView.
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string EmployeeID = GridView1.DataKeys[e.RowIndex].Value.ToString();
string Query = “delete Employee where Employee.EmployeeID = “ + EmployeeID;
BindGridData(Query);
}

Happy Coding .

Friday, October 8, 2010

Validation Expressions

Metacharacter Match
\ the escape character - used to find an instance of a metacharacter like a period, brackets, etc.
. (period) match any character except newline
x match any instance of x
^x match any character except x
[x] match any instance of x in the bracketed range - [abxyz] will match any instance of a, b, x, y, or z
| (pipe) an OR operator - [x|y] will match an instance of x or y
() used to group sequences of characters or matches
{} used to define numeric quantifiers
{x} match must occur exactly x times
{x,} match must occur at least x times
{x,y} match must occur at least x times, but no more than y times
? preceding match is optional or one only, same as {0,1}
* find 0 or more of preceding match, same as {0,}
+ find 1 or more of preceding match, same as {1,}
^ match the beginning of the line
$ match the end of a line
POSIX Class Match
[:alnum:] alphabetic and numeric characters
[:alpha:] alphabetic characters
[:blank:] space and tab
[:cntrl:] control characters
[:digit:] digits
[:graph:] non-blank (not spaces and control characters)
[:lower:] lowercase alphabetic characters
[:print:] any printable characters
[:punct:] punctuation characters
[:space:] all whitespace characters (includes [:blank:], newline, carriage return)
[:upper:] uppercase alphabetic characters
[:xdigit:] digits allowed in a hexadecimal number (i.e. 0-9, a-f, A-F)

Character class Match
\d matches a digit, same as [0-9]
\D matches a non-digit, same as [^0-9]
\s matches a whitespace character (space, tab, newline, etc.)
\S matches a non-whitespace character
\w matches a word character
\W matches a non-word character
\b matches a word-boundary (NOTE: within a class, matches a backspace)
\B matches a non-wordboundary

  • \
    The backslash escapes any character and can therefore be used to force characters to be matched as literals instead of being treated as characters with special meaning. For example, '\[' matches '[' and '\\' matches '\'.
  • .
    A dot matches any character. For example, 'go.d' matches 'gold' and 'good'.
  • { }
    {n} ... Match exactly n times
    {n,} ... Match at least n times
    {n,m} ... Match at least n but not more than m times
  • [ ]
    A string enclosed in square brackets matches any character in that string, but no others. For example, '[xyz]' matches only 'x', 'y', or 'z', a range of characters may be specified by two characters separated by '-'. Note that '[a-z]' matches alphabetic characters, while '[z-a]' never matches.
  • [-]
    A hyphen within the brackets signifies a range of characters. For example, [b-o] matches any character from b through o.
  • |
    A vertical bar matches either expression on either side of the vertical bar. For example, bar|car will match either bar or car.
  • *
    An asterisk after a string matches any number of occurrences of that string, including zero characters. For example, bo* matches: bo, boo and booo but not b.
  • +
    A plus sign after a string matches any number of occurrences of that string, except zero characters. For example, bo+ matches: boo, and booo, but not bo or be.
  • \d+
    matches all numbers with one or more digits
  • \d*
    matches all numbers with zero or more digits
  • \w+
    matches all words with one or more characters containing a-z, A-Z and 0-9. \w+ will find title, border, width etc. Please note that \w matches only numbers and characters (a-z, A-Z, 0-9) lower than ordinal value 128.
  • [a-zA-Z\xA1-\xFF]+
    matches all words with one or more characters containing a-z, A-Z and characters larger than ordinal value 161 (eg. ä or Ü). If you want to find words with numbers, then add 0-9 to the expression: [0-9a-zA-Z\xA1-\xFF]+



Typical examples

  • (bo*)
    will find "bo", "boo", "bot", but not "b"
  • (bx+)
    will find "bxxxxxxxx", "bxx", but not "bx" or "be"
  • (\d+)
    will find all numbers
  • (\d+ visitors)
    will find "3 visitors" or "243234 visitors" or "2763816 visitors"
  • (\d+ of \d+ messages)
    will find "2 of 1200 messages" or "1 of 10 messages"
  • (\d+ of \d+ messages)
    will filter everything from the last occurrence of "2 of 1200 messages" or "1 of 10 messages" to the end of the page
  • (MyText.{0,20})
    will find "MyText" and the next 20 characters after "MyText"
  • (\d\d.\d\d.\d\d\d\d)
    will find date-strings with format 99.99.9999 or 99-99-9999 (the dot in the regex matches any character)
  • (\d\d\.\d\d\.\d\d\d\d)
    will find date-strings with format 99.99.9999
  • (([_a-zA-Z\d\-\.]+@[_a-zA-Z\d\-]+(\.[_a-zA-Z\d\-]+)+))
    will find all e-mail addresses