Overblog Follow this blog
Administration Create my blog
Balavardhan Reddy Narani

Change the Background Row Color of the GridView control using MouseEvents without postback

August 25 2009 , Written by Balavardhan Reddy Published on #ASP.Net

Change the Background Row Color of the GridView control using MouseEvents without postback


Change BackGround Color of a Row in Gridview when user moves mouse on a particular selecting Row, without postback.

You need write the code in RowDataBound Event of Gridview,

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{

}



In this RowDataBound event add onmouseover and onmouseout Events.

if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='blue'");

e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor='white'");

}



when the user Moves Mouseover or MouseOut on Gridview the Particular selecting Row on gridview changes the BackGround Color without postback.

--
Read more

How to Access ViewState Across Pages

August 25 2009 , Written by Balavardhan Reddy Published on #ASP.Net

Access ViewState Across Pages

We can access the viewstate variables across pages. This is possible when we use Cross Page Posting or Server.transfer to redirect the other pages

Ex :
Here I have created two aspx pages named:

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

First.aspx :

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

    public StateBag FirstViewState()
    {
        return ViewState;
    }
}



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.


Second.aspx :

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

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



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.




--
Read more

Best Tips to write a Stored Procedure

August 11 2009 , Written by Balavardhan Reddy Published on #SQL Server2005


Good Tips to write stored procedure, to improve the performance. Below are the general rules to write good stored procedure.
 

  • The sp_ prefix :  Don't use the "sp_" prefix in a User created stored procedure name as the "sp_" prefix is reserved for system stored procedures. Any stored procedure that has the "sp_" prefix will be lookup in the MASTER database first, If a stored procedure uses same name in both the user database and a system database, the stored procedure in the System Database only get executed.

  • Use variable as few as possible, if you use more variables it can required more Cache space.

  • SET NOCOUNT OFF This returns the message that shows number of rows affected by executing that SQL statement. This can cause extra traffic and can have some impact on performance when the procedure is called frequently.

  • Provide Fully Qualified Names while calling the Objects like <DbName>.<SchemaName>.<ObjectName>
    instead of direcltly provind the Object Name, because it will causes an extra trip to the procedure cache to get the execution plan for execution. Also try to use the schema name while creating a procedure

  • SELECT statements - Try to use only the required number of columns in the SELECT clause instead of using *, it will return all columns, including un-necessary columns

  • SELECT vs SET - A single SELECT statement can assign values to different variables and is much faster than multiple SET statements assigning values to multiple different variables.

    Declare @a int
    Declare @b int

    Select @a=emp_id,@b=Job_id from emplloyee where email_id = '<search string>'

    instead of

    Set @a = (select emp_id from emplloyee where email_id = '<search string>')
    set @b = (select job_id from emplloyee where email_id = '<search string>')

  • SQL-92 - Always try to use ANSI 92 syntax. Till now the old syntax is working the old syntax will be deprecated in the next release of MS SQL server. As an example, for joining, use

    ex : select * from employee E
           inner join Jobs J
           on E.Job_id = J.Job_id

          instead of
          
          select * from employee E, Jobd J Where E.Job_id = J.Job_id

  • Try to avoid IN clause. While checking the existence of some values, then use EXISTS instead of IN. Because IN counts the NULL values also, and slower than EXISTS. EXISTS returns only Boolean(Yes/No) value, but IN returns all result set.
  • Avoid DISTINCT/ORDER BY clause : If you don't need the DISTINCT/ORDER BY clause, then try to avoid them. Unnecessary DISTINCT or ORDER BY clauses will cause extra work for the database. so it making performance slower.
  • WHERE clauses - In a WHERE clause, we use different operators to campare and get the result, these also directly affect how fast a query can run. Here are the operators which used in the WHERE clause, ordered by their performance.
        =, >, <, >=, <=, <>, !=, !>, !<
  • Avoid unnecessary conditions in the WHERE Clause.
  • Avoid using cursors - Try to use temporary table/table variables for required result set, then iterate by using while loop.
  • Subquery vs JOINs - more sub queries can be expressed as an equivalent form of JOIN. subquery is faster when we have to retrieve data from large number of tables because it becomes tedious to join more tables. JOIN is faster to retrieve data from database when we have less number of tables. But try to avoid correlated sub queries because it makes the query much slower.
  • Try to use table variables instead of Temporary Tables - Temp tables can cause stored procedures to recompile. But table variables were designed specifically to guard against stored procedure recompiles during execution.
  • Try to minimize the Dynamic Queries.
  • Last but Not least. Use proper indexes - Indexes is most importent to retrive data as fast as possible. Correct Indexes will give good result. Index scans are much faster than table scans. So identify the table scans from the execution plans. But when a table returns smaller rows, then it is better to use a table scan.

--
Read more

Published from Overblog

August 11 2009 , Written by Balavardhan Reddy

free counters
Read more

Save your SELECT query results in XML File or Text File.

August 6 2009 , Written by Balavardhan Reddy Published on #SQL Server2005


To save the result of your SELECT query to XML or a text file. Well use xp_cmdshell.  xp_cmdshell is an extended stored procedure kept in the master database. It issues OS commands directly to the Windows command shell. You need to be a member of the sys_admin group to use this command or have the xp_sqlagent_proxy_account.

To Save your SELECT query results to XML File or a text file, use this query :

--For XML

EXEC master..xp_cmdshell'bcp "SELECT TOP 5 * FROM cscs.dbo.employee FOR XML PATH (''e''), ROOT(''emp'')" queryout "c:\text.xml" -c -T -x'


-- For Text

EXEC master..xp_cmdshell'bcp "SELECT TOP 5 * FROM cscs.dbo.employee" queryout "c:\text.txt" -c -T -x'

Note: By default, xp_cmdshell is disabled in SQL 2005 for security reasons. To enable it, use the Surface Area Configuration tool or sp_configure. To enable xp_cmdshell using sp_configure, use this query :

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

--
Read more

Compare data between two tables in SQL Server 2005

August 6 2009 , Written by Balavardhan Reddy Published on #SQL Server2005


Inorder to find the difference of two tables in schema and data betweeen Source and distination tables.  we can use Tablediff in SQL Server 2005

You can run this utility from the command line or a batch file.


Ex: if you have two Databases one for Production and another for Development in differerent servers, and this two databases have a table called Employee.  If you want to find the differences between Production and Delelopment Databases employee table. Do the below procedure.

Syntax to run the tablediff untility


C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "<SourceServerName>" -sourcedatabase "<SourceDBName>" -sourcetable "<SourceTableName>" -destinationserver "<DestServerName>" -destinationdatabase "<DestDBName>" -destinationtable "<DestTableName>"


It can give the Output result as Below


Microsoft (R) SQL Server Replication Diff Tool
Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.

User-specified agent parameter values:
-sourceserver <SourceServerName>
-sourcedatabase <SourceDBName>
-sourcetable <SourceTableName>
-destinationserver <DestServerName>
-destinationdatabase "<DestDBName>
-destinationtable <DestTableName>

Table [SourceDBName].[dbo].[SourceTableName] on <SourceServerName> and Table [DestDBName].[db
o].[DestTableName] on <DestServerName> have 24 differences.

Err                     <Table Primary Key>
Mismatch                523       --If difference the record
Src. Only               745       --If the record existed only in Source


You can also save this information inot File by using -f parameter with the file name


C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "<SourceServerName>" -sourcedatabase "<SourceDBName>" -sourcetable "<SourceTableName>" -destinationserver "<DestServerName>" -destinationdatabase "<DestDBName>" -destinationtable "<DestTableName>" -f: "c:\difference"


It can create the SQL Script file in C:\ with difference.sql as script file.

--
Read more

How to encrypt a stored procedure in SQL Server 2005

August 6 2009 , Written by Balavardhan Reddy Published on #SQL Server2005


Inorder to encrypt the text of stored procedures containing sensitive information, Sql Server provides WITH ENCRYPTION to encrypt the Stored Procedure.

CREATE procedure [dbo].[SP_GET_Employee_Details_encrypt]
WITH ENCRYPTION AS

SELECT EMP_id,First_name,Last_name,Email_id
from Employee
Order by Email_id


Once the stored procedure has been created WITH ENCRYPTION, attempts to view the stored procedure returns a message specifying that the text is encrypted:


EXEC sp_helptext
SP_GET_Employee_Details_encrypt

The text for object 'SP_GET_Employee_Details_encrypt' is encrypted.

One note of caution. Save the original text of the stored procedure before encrypting it, as there is no straightforward way to decode the encrypted text. One hack is to attach a debugger to the server process and retrieve the decrypted procedure from memory at runtime.

--

Read more

How to check the active connections for each Database in SQL Server 2005

August 6 2009 , Written by Balavardhan Reddy Published on #SQL Server2005


We need to check the number of active connections for a Database on our server.

We can get that details by using the following SQL script. This SQL script displays the DatabaseName, the number of connections and the login name :

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

---
Read more

Display Last Modified Stored Procedure using SQL Server 2005/2008

August 5 2009 , Written by Balavardhan Reddy Published on #SQL Server2005


To get a list of the Stored Procedure that were last modified, use this query

SELECT TOP 10 modify_date,name  
                 FROM sys.procedures
                 ORDER BY modify_date DESC


--
Read more

Execute particular javascript method only once, while Page onLoad only in an ASP.NET

August 5 2009 , Written by Balavardhan Reddy Published on #ASP.Net


We can execute Javascript method only once while asp.net page Loading only. We can validate the Asp.Net page IsPostBack event in Javascript method.
ASP.NET Master/Content Page scenario and have declared a javascript onload, then the script gets executed every on every postback.

However if you would like to execute it only once during the first postback, then here's how to do so:
write below code in MasterPage


<script type="text/javascript">

function invokeMeMaster() {

var chkPostBack = '<%= Page.IsPostBack ? "true" : "false" %>';

if (chkPostBack == 'false') {

alert('Only the first time');

}
}



window.onload = function() { invokeMeMaster(); };

</script>



The code determines if there is a postback in Javascript and then executes code based on the result. Hence the alert popup is shown only once.
Read more
1 2 > >>