Asp.Net and SQL SERVER: September 2008

Saturday, September 27, 2008

Asp.Net Code Compressor



The below code compress your code when rendering to browser 
and it helps to increase the speed of loading

Click these link


http://blog.madskristensen.dk/

http://www.mironabramson.com/


http://msmvps.com/blogs/omar

http://www.codeproject.com/

http://www.codeproject.com/

http://www.codeproject.com/

http://www.jigar.net/

http://www.jigar.net/

Send mail from SQL SERVER 2005 after Insert Statement

The below stored procedure send a mail.

The profile name should be already created.

The Procedure that sends the mail is msdb.dbo.sp_send_dbmail

ALTER TRIGGER [MailOnError] ON [dbo].[LogMessages] AFTER
INSERT AS
DECLARE @LoggerId    INT
DECLARE @TableName   VARCHAR(75)
DECLARE @TableNameId INT
DECLARE @MessageId   INT
DECLARE @MessageText VARCHAR(1000)
DECLARE @LoggedTime  DATETIME
DECLARE @Body        VARCHAR(1100)
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        -- Insert statements for trigger here
        SELECT @LoggerId    = LoggerId   ,
               @TableName   = TableName  ,
               @TableNameId = TableNameId,
               @MessageId   = MessageId  ,
               @MessageText = MessageText,
               @LoggedTime  = LoggedTime
        FROM   LogError
        
        IF @MessageId = 3
        BEGIN
                SET @Body = 
                'LoggerId = ' + CAST( @LoggerId AS VARCHAR ) + ',   ' + 
                'TableName  = '+ @TableName + ', ' + 
                'TableNameId = ' + CAST( @TableNameId AS VARCHAR ) + ', ' + 
                'MessageId = ' + CAST( @MessageId AS VARCHAR ) + ', ' + 
                'MessageText = ' + @MessageText + ', ' + 
                'LoggedTime = '+ CAST( @LoggedTime AS VARCHAR )
                
                EXEC msdb.dbo.sp_send_dbmail
                        @profile_name = 'DBOException' ,
                        @recipients   = 'babukumarasamy@gmail.com' ,
                        @subject      = 'Exception occured!' ,
                        @body         = @Body,
                        @importance   = 'High',
                        @body_format  = 'HTML',
                        @sensitivity  = 'Confidential'
        END
END

Read the book
Pro SQL Server 2012 Administration By Ken Simmons, Sylvester Carstarphen
Page#: 326

SQL Server 2005 Database Mail Setup and Configuration Scripts

This sight gives us how to configure and send mail from sql server 2005

SQL Server 2005 Database Mail Setup and Configuration Scripts
Setting up Database Mail for SQL 2005
SQL Server Database Mail and SQL Agent Mail setup by using a script

Friday, September 12, 2008

GridView - Create THeader and TBody

This is useful when we are applying sytle.




    protected void Page_Load(object sender, EventArgs e)
    {
        //Add data to the GridView  
        GridView1.DataSource = SQLHelper.ExecuteDataset("Data Source=SERVER;Initial Catalog=; Uid=; PWD=; Connect Timeout=360; pooling=true; Max Pool Size=200; ", CommandType.Text, "Select * from a, b");
      GridView1.DataBind();

      GenerateHeaders(GridView1);
    }

    private void GenerateHeaders(GridView grdView)
    {
        //HtmlTextWriterStyle style = new HtmlTextWriterStyle();
        //TableRow tr = new TableRow();
        ////tr.Style.Add(HtmlTextWriterStyle
        //tr.Style.Add(style , 
        if grdView.Rows.Count > 0)
        {
            //This replaces  with  and adds the scope attribute
            grdView.UseAccessibleHeader = true;

            //This will add the  and  elements
            grdView.HeaderRow.TableSection = TableRowSection.TableHeader;
            //grid.HeaderStyle.AddAttributesToRender(


            //This adds the  element. Remove if you don't have a footer row
            grdView.FooterRow.TableSection = TableRowSection.TableFooter;
        }
    }

GridView - Confirm MessageBox before Deleting


    public static void AddConfirmDelete(GridView gv, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            foreach (DataControlField dcf in gv.Columns)
            {
                CommandField cf = dcf as CommandField;
                if (cf != null)
                {
                    TableCell cell = e.Row.Cells[gv.Columns.IndexOf(dcf)];
                    foreach (Control ctl in cell.Controls)
                    {
                        switch (cf.ButtonType)
                        {
                            case ButtonType.Button:
                                if (ctl is Button && ((Button)ctl).Text == cf.DeleteText)
                                {
                                    Button btn = (Button)ctl;
                                    //btn.OnClientClick = "return confirm(\"Are you sure?\")";
                                    btn.Attributes.Add("onclick", "return confirm(\"Are you sure Button?\")");
                                }


                                if (ctl is Button && ((Button)ctl).Text == cf.EditText)
                                {
                                    Button btn = (Button)ctl;
                                    //btn.OnClientClick = "return confirm(\"Are you sure?\")";
                                    btn.Attributes.Add("onclick", "return confirm(\"Are you sure EditText?\")");
                                }
                                break;
                            case ButtonType.Image:
                                if (ctl is ImageButton && ((ImageButton)ctl).ImageUrl == cf.DeleteImageUrl)
                                {
                                    ImageButton ib = (ImageButton)ctl;
                                    //ib.OnClientClick = "return confirm(\"Are you sure?\")";
                                    ib.Attributes.Add("onclick", "return confirm(\"Are you sure Image?\")");
                                }

                                if (ctl is Button && ((Button)ctl).Text == cf.EditText)
                                {
                                    Button btn = (Button)ctl;
                                    //btn.OnClientClick = "return confirm(\"Are you sure?\")";
                                    btn.Attributes.Add("onclick", "return confirm(\"Are you sure EditImage?\")");
                                }
                                break;
                            case ButtonType.Link:
                                if (ctl is LinkButton && ((LinkButton)ctl).Text == cf.DeleteText)
                                {
                                    LinkButton lb = (LinkButton)ctl;
                                    lb.Attributes.Add("onclick", "return confirm(\"Are you sure Link?\")");
                                }

                                if (ctl is LinkButton && ((LinkButton)ctl).Text == cf.EditText)
                                {
                                    LinkButton lb = (LinkButton)ctl;
                                    lb.Attributes.Add("onclick", "return confirm(\"Are you sure EditLink?\")");
                                }
                                break;
                        }

                    }
                }
            }
        }
    }

GridView - EmptyData Alignment.

grdView.BorderColor = System.Drawing.Color.White;
grdView.EmptyDataRowStyle.HorizontalAlign = HorizontalAlign.Center;

grdView.EmptyDataRowStyle.CssClass = "EmptyDataRowStyle";

We can set bordercolor for the gridview
grdView.Attributes.Add("bordercolor", "#acc0e9");

GridView - Show Image Empty Grid

In code behind file use this code

gvControl.EmptyDataText = "<img src="http://www.blogger.com/images/NoData.jpg%27" border="0" >";

GridView - Get and Set More than one DataKeyNames

To set more than one DataKeys in grid view,

<asp:GridView ID="grdView" runat="server" DataKeyNames="Name,Email">

To get the Value of the DataKey
DataKey myData = grdView.DataKeys[RowIndex++];

myData.Values["StarRate"];

OR

String value = grdView.DataKeys[RowIndex++].Values["StarRate"];

Empty grid to show the header

If our resultset is contains no rows, the gridview didn't show the header.

To show the header in the gridview ,

We have to desing the gridview header in the Table and put it inside the EmptyDataTemplate of the gridview as given below.


       <asp:GridView ID="GridView1" runat="server" CssClass="th">
           <EmptyDataTemplate>
               <table>
                   <tr>
                       <td>
                           <b>&Namelt;/b></td>
                   </tr>
               </table>
           </EmptyDataTemplate>
       </asp:GridView>

Sql Server Split Function

Fastest split function.

DECLARE @String VARCHAR(MAX),
        @xml AS xml,
        @Delimiter AS VARCHAR(10)
DECLARE @TEMP TABLE (LocID INT, Location VARCHAR(50))

INSERT INTO @Temp
SELECT 9, 'Chennai' UNION ALL
SELECT 13, 'Salem' UNION ALL
SELECT 42, 'Idappadi' UNION ALL
SELECT 32, 'Poolampatti' UNION ALL
SELECT 11, 'Chitoor'

SET @String    = '13,42,47,32,11,,9'
SET @Delimiter =','
SET @xml       = CAST((''+REPLACE(@String, @Delimiter, '')+'') AS XML)

--Split the string
SELECT   N.value('.', 'VARCHAR(MAX)') AS LocationID
FROM     @xml.nodes('x')              AS T ( N )
WHERE    N.value('.', 'VARCHAR(MAX)') <> ''
ORDER BY CAST(N.value('.', 'VARCHAR(MAX)') AS INT)

--How to use it in join with a table
SELECT     LocID,
           Location
FROM       @Temp
           INNER JOIN @xml.nodes('x') AS T ( N )
           ON         N.value('.', 'VARCHAR(MAX)') = LocID

Reference

By using the above batch of query, we can pass ,(comma) separated values as XML from the application.
The size doesn't matter, when we pass the variable as XML from the application,
This is very small query, so no need to write it in a function.
This increase the performance of the query.

--Split Function

CREATE FUNCTION [dbo].[Split](@String NVARCHAR(4000), @Delimiter CHAR(1))
RETURNS @Results TABLE (id int identity, Items NVARCHAR(4000))
AS
BEGIN
DECLARE @index INT
DECLARE @slice NVARCHAR(4000)

SELECT @index = 1
IF @String is null RETURN
BEGIN
WHILE @index != 0
BEGIN

SELECT @index = CHARINDEX(@Delimiter,@String)
IF @index !=0
BEGIN
SELECT @slice = left(@String,@index - 1)
END
ELSE
BEGIN
SELECT @slice = @String
END

INSERT INTO @Results(Items) VALUES(@slice)
SELECT @String = right(@String,LEN(@String) - @index)
IF LEN(@String) = 0 BREAK

END RETURN
END
END

-----------------------------------------------------------------------------Split Function With Identity Colum

CREATE FUNCTION [dbo].[Split](@String NVARCHAR(4000), @Delimiter CHAR(1))
RETURNS @Results TABLE (id int identity, Items NVARCHAR(4000))
AS
BEGIN
DECLARE @index INT
DECLARE @slice NVARCHAR(4000)

SELECT @index = 1
IF @String is null RETURN
BEGIN
WHILE @index != 0
BEGIN

SELECT @index = CHARINDEX(@Delimiter,@String)
IF @index !=0
BEGIN
SELECT @slice = left(@String,@index - 1)
END
ELSE
BEGIN
SELECT @slice = @String
END

INSERT INTO @Results(Items) VALUES(@slice)
SELECT @String = right(@String,LEN(@String) - @index)
IF LEN(@String) = 0 BREAK

END RETURN
END
END

---------------------------------------------------------------------------
--Simple split function in www.sqlteam.com

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[Split] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) 
AS Item  FROM Pieces
)

Asp.Net and SQL SERVER