.

Wednesday, February 8, 2012

Creating Hit Counter for Total Page Views

In Count.aspx
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Label ID="lblCounter" runat="server"></asp:Label>

    </div>
    </form>
</body>

In Count.aspx.cs

protected void Page_Load(object sender, EventArgs e)
    {
        this.countMe();

        DataSet tmpDs = new DataSet();
        tmpDs.ReadXml(Server.MapPath("~/counter.xml"));

        lblCounter.Text = tmpDs.Tables[0].Rows[0]["hits"].ToString();
      // application["activeuser"] = application["activeuser"] + 1;
    }
    private void countMe()
    {

        DataSet tmpDs = new DataSet();
        tmpDs.ReadXml(Server.MapPath("~/counter.xml"));

        int hits = Int32.Parse(tmpDs.Tables[0].Rows[0]["hits"].ToString());

        hits += 1;

        tmpDs.Tables[0].Rows[0]["hits"] = hits.ToString();

        tmpDs.WriteXml(Server.MapPath("~/counter.xml"));

    }

In counter.xml/
<?xml version="1.0" standalone="yes"?>
<counter>
<count>
    <hits>0</hits>
</count>
</counter>

Sunday, January 8, 2012

Get Stored Procedure Return Value

SqlConnection con = null;
        try
        {
            string connString = ConfigurationManager.ConnectionStrings["cnTest"].ConnectionString;
            con = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_ValidateUser";
            cmd.Parameters.AddWithValue("@Name", txt1.Text);
            con.Open();
            // Return value as parameter
            SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(returnValue);

            // Execute the stored procedure

            cmd.ExecuteNonQuery();
            con.Close();

            Int32 ret= Convert.ToInt32(returnValue.Value);
            Response.Write(Convert.ToString(ret));

        }
        catch (SqlException ex)
        {
            // handle error
        }
        catch (Exception ex)
        {
            // handle error
        }
        finally
        {
            con.Close();
        }

Create Proc usp_ValidateUser
@Name Varchar(50)
AS
BEGIN
        if exists(Select Name from tblTestUser where Name=@Name)
        Begin
                return 1
        End

        else
         Begin
                return -1
        End


END

Get selected index, value,text of ASP.Net drop down list in javascript

<%-- In Head section--%>
<script type="text/javascript" language="javascript">

            function getDropdownListValues() {
            var DropdownList = document.getElementById('<%=aspdropdown.ClientID %>');
            var SelectedIndex = DropdownList.selectedIndex;
            var SelectedValue = DropdownList.value;
            var SelectedText = DropdownList.options[DropdownList.selectedIndex].text;

            var LabelDropdownList = document.getElementById('<%=lblDropdownList.ClientID %>');
            var sValue = 'Index: ' + SelectedIndex + '<br/> Selected Value: ' + SelectedValue + '<br/> Selected Text: ' + SelectedText;

            LabelDropdownList.innerHTML = sValue;
        }
    </script>

<%-- In body section--%>

<div>
        <asp:Label runat="server" ID="lblText">Asp.net DropdownList</asp:Label><br />

        <asp:DropDownList ID="aspdropdown" runat="server" onchange="getDropdownListValues();">
            <asp:ListItem Value="Test1" Text="Testing1"></asp:ListItem>
            <asp:ListItem Value="Test2" Text="Testing2"></asp:ListItem>
            <asp:ListItem Value="Test3" Text="Testing3"></asp:ListItem>
            <asp:ListItem Value="Test4" Text="Testing4"></asp:ListItem>
            <asp:ListItem Value="Test5" Text="Testing5"></asp:ListItem>
        </asp:DropDownList> <br />

        <asp:Label runat="server" ID="lblDropdownList"></asp:Label>
    </div>

Friday, January 6, 2012

HTML To PDF Converter Asp.net

using Pdfizer; //download Pdfizer and itextsharp dll also
using System.IO;

protected void btnDownload_Click1(object sender, EventArgs e)
    {
        string sPathToWritePdfTo = Server.MapPath(".") + "\\test.pdf";
        string path = Server.MapPath("./images/logo3w.png");
        System.Text.StringBuilder sbHtml = new System.Text.StringBuilder();
        sbHtml.Append("<html>");
        sbHtml.Append("<head>");
        sbHtml.Append("</head>");
        sbHtml.Append("<body>");
        sbHtml.Append("<div>");
        sbHtml.Append("<img src='" + path + "' alt='' />");
        sbHtml.Append("</div>");
        sbHtml.Append("<br/>");
        sbHtml.Append("<table border='0' cellpadding='0' cellspacing='0'>");
        sbHtml.Append("<tr><td>testing the text</td></tr>");
        sbHtml.Append("</table>");
        sbHtml.Append("</body>");
        sbHtml.Append("</html>");

        using (System.IO.Stream stream = new System.IO.FileStream
        (sPathToWritePdfTo, System.IO.FileMode.OpenOrCreate))
        {
            Pdfizer.HtmlToPdfConverter htmlToPdf = new Pdfizer.HtmlToPdfConverter();
            htmlToPdf.Open(stream);
            htmlToPdf.Run(sbHtml.ToString());
            htmlToPdf.Close();
        }
        System.Web.HttpContext.Current.Response.Clear();
        System.Web.HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "devtesting.pdf"));
        System.Web.HttpContext.Current.Response.ContentType = "application/pdf";
        System.Web.HttpContext.Current.Response.WriteFile(sPathToWritePdfTo);
        System.Web.HttpContext.Current.Response.End();
    }

/*http://itextpdf.com/terms-of-use*/

Wednesday, January 4, 2012

Export To CSV using ASP

<%
sub Write_CSV_From_Recordset( RS )

    '
    ' This sub-routine Response.Writes the content of an ADODB.RECORDSET in CSV format
    ' The function closely follows the recommendations described in RFC 4180:
    ' Common Format and MIME Type for Comma-Separated Values (CSV) Files
    ' http://tools.ietf.org/html/rfc4180
    '
    ' @RS: A reference to an open ADODB.RECORDSET object
    '

    if RS.EOF then

      '
      ' There is no data to be written
      '
      exit sub

    end if

    dim RX
    set RX = new RegExp
        RX.Pattern = "\r|\n|,|"""

    dim i
    dim Field
    dim Separator

    '
    ' Writing the header row (header row contains field names)
    '

    Separator = ""
    for i = 0 to RS.Fields.Count - 1
      Field = RS.Fields( i ).Name
      if RX.Test( Field ) then
        '
        ' According to recommendations:
        ' - Fields that contain CR/LF, Comma or Double-quote should be enclosed in double-quotes
        ' - Double-quote itself must be escaped by preceeding with another double-quote
        '
        Field = """" & Replace( Field, """", """""" ) & """"
      end if
      Response.Write Separator & Field
      Separator = ","
    next
    Response.Write vbNewLine

    '
    ' Writing the data rows
    '

    do until RS.EOF
      Separator = ""
      for i = 0 to RS.Fields.Count - 1
        '
        ' Note the concatenation with empty string below
        ' This assures that NULL values are converted to empty string
        '
        Field = RS.Fields( i ).Value & ""
        if RX.Test( Field ) then
          Field = """" & Replace( Field, """", """""" ) & """"
        end if
        Response.Write Separator & Field
        Separator = ","
      next
      Response.Write vbNewLine
      RS.MoveNext
    loop

end sub

'
' EXAMPLE USAGE
'
' - Open a RECORDSET object (forward-only, read-only recommended)
' - Send appropriate response headers
' - Call the function
'
    Set oConnection = Server.CreateObject("ADODB.Connection")
    oConnection.Open "Driver={SQL Server};Server=test123;Database=Test123;Uid=testsa;Pwd=test@123;"

    Set SQLStmt = Server.CreateObject("ADODB.Command")
    set rs=Server.CreateObject("ADODB.recordset")
    rs.Open "Select Id,Name as UserName from tblTestUser", oConnection

    dim RS1
    set RS1 = Server.CreateObject( "ADODB.RECORDSET" )
    RS1.Open "Select Id,Name as UserName from tblTestUser",oConnection
    Response.ContentType = "text/csv"
    Response.AddHeader "Content-Disposition", "attachment;filename=export.csv"
    Write_CSV_From_Recordset RS1
%>

Tuesday, November 29, 2011

Using Try Catch And Exception Handling in Procedures

CREATE PROCEDURE usp_InsertTest
(
@Name varchar(50),
@Add varchar(50),
@Sal int ,
@ErrMsg varchar(4000) output
)
AS
declare @Err varchar(2000)
Begin
begin try
begin transaction
insert into Tbltest([Name],[Add],Sal) values(@Name,@Add,@Sal)
commit transaction
end try
begin catch
rollback transaction

end catch
End

Thursday, November 10, 2011

Difference between Truncate and Delete in SQL

Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.

TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .
You cann't rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.
A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.
If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work.
TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.
Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.
TRUNCATE is faster than DELETE.