.

Monday, September 17, 2018

Performance Optimization of application/product

Data Archiving Procedures

Sunday, September 16, 2018

Difference between TRUNCATE and DELETE in SQL Server?

TRUNCATE:

It is a DDL command
It does not support WHERE clause/condition
Removes all the data all the time
Faster than DELETE as it locks entire table
It removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log It does not activate triggers
Table identity column is reset to seed value

Syntax:

TRUNCATE TABLE TableName

DELETE:

It is DML command
It supports WHERE clause/condition
Removes data based on conditions specified in the WHERE clause (removes all the data if there is no WHERE clause) Slower than TRUNCATE as it takes row level locks
It removes rows one at a time and records an entry in the transaction log for each deleted row
It does activate triggers
Table identity column is not reset

Syntax:

DELETE FROM TableName WHERE ColName = ‘YourCondition’

Note

Truncate and Delete both are logged operations and both can be rolled back when they are within transactions. It is myth that Truncate is not logged operations. It is indeed logged operations and it locks page level deallocations.

Fundamentals of Garbage Collection

In the common language runtime (CLR), the garbage collector serves as an automatic memory manager. It provides the following benefits:

Enables you to develop your application without having to free memory.
Allocates objects on the managed heap efficiently.
Reclaims objects that are no longer being used, clears their memory, and keeps the memory available for future allocations. Managed objects automatically get clean content to start with, so their constructors do not have to initialize every data field.
Provides memory safety by making sure that an object cannot use the content of another object.

Click here to read full article by Microsoft

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.

Difference between Having and Where clause

Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement.
We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.
Where Clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).
. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.

list of differences between SQL Server 2000 , 2005 and 2008

SQL SERVER 2008:
1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is used.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Can encrypt the entire database introduced in 2008.
--check it(http://technet.microsoft.com/en-us/library/cc278098(SQL.100).aspx)
(http://www.sqlservercentral.com/articles/Administration/implementing_efs/870/)
(http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx)
(http://www.sql-server-performance.com/articles/dev/encryption_2005_1_p1.aspx)
(http://geekswithblogs.net/chrisfalter/archive/2008/05/08/encrypt-documents-with-sql-server.aspx)
13.Can compress tables and indexes.
-http://www.mssqltips.com/tip.asp?tip=1582
14.Date and time are seperately used for date and time datatype,geospatial and timestamp with internal timezone
is used.
15.Varchar(max) and varbinary(max) is used.
16.Table datatype introduced.
17.SSIS avails in this version.
18.Central Management Server(CMS) is Introduced.
-http://msdn.microsoft.com/en-us/library/bb934126.aspx
-http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx
19.Policy based management(PBM) server is Introduced.
-http://www.mssqltips.com/tip.asp?tip=1492
-http://msdn.microsoft.com/en-us/library/bb510667.aspx

SQL SERVER 2005:

1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is introduced.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Cant encrypt
13.Can Compress tables and indexes.(Introduced in 2005 SP2)
14.Datetime is used for both date and time.
15.Varchar(max) and varbinary(max) is used.
16.No table datatype is included.
17.SSIS is started using.
18.CMS is not available.
19.PBM is not available.

SQL SERVER 2000:
1.Query Analyser and Enterprise manager are separate.
2.No XML datatype is used.
3.We can create maximum of 65,535 databases.
4.Nill
5.Nill
6.Nill
7.Nill
8.Nill
9.Nill
10.Nill
11.Nill
12.Nill
13.cant compress the tables and indexes.
14.Datetime datatype is used for both date and time.
15.No varchar(max) or varbinary(max) is available.
16.No table datatype is included.
17.No SSIS is included.
18.CMS is not available.
19.PBM is not available

Thursday, October 20, 2011

Inserting Multiple records in sql using while loop

CREATE proc usp_InsertMultiple

as

Declare @UserIdNew int
Declare @CountTo int=0
Declare @char int
Declare @userIds VARCHAR(MAX)
SELECT @userIds = COALESCE(@userIds+',' ,'') +CAST(UserId as varchar(Max)) from TblUsers
Set @userIds=@userIds+','
SELECT @userIds
if(@userIds <>'')
begin
while(CHARINDEX (',',@userIds)>0)
begin
        set @char= CHARINDEX (',',@userIds)
        print @char
        set @UserIdNew=LTRIM(RTRIM(substring(@userIds,1,CHARINDEX(',',@userIds)-1)))
        insert into tbl01 (UserId) values(@UserIdNew)
        set @userIds=SUBSTRING(@userIds,charindex(',',@userIds)+1,LEN(@userIds))
        set @CountTo=@CountTo+1
        set @UserIdNew=''
end
end

Thursday, October 13, 2011

Split , seprated strings in SQL

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
   returns @temptable TABLE (items varchar(8000))
as
begin
     declare @idx int
    declare @slice varchar(8000)

       select @idx = 1
          if len(@String)<1 or @String is null return

     while @idx!= 0
     begin
         set @idx = charindex(@Delimiter,@String)
         if @idx!=0
             set @slice = left(@String,@idx - 1)
        else
             set @slice = @String

          if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)

          set @String = right(@String,len(@String) - @idx)
          if len(@String) = 0 break
       end
   return
   end

/*use this as*/

Select testid,testname from tblKG01 where Company_ID= 93 and KGID in ( select * from dbo.split(@Ids,',') )

watermark textbox using javascript

Using Java script
<script language="javascript" type="text/javascript">
        function WaterMark(txtName, event) {
            var defaultText = "Enter Username Here";
            // Condition to check textbox length and event type
            if (txtName.value.length == 0 & event.type == "blur") {
                //if condition true then setting text color and default text in textbox
                txtName.style.color = "Gray";
                txtName.value = defaultText;
            }
            // Condition to check textbox value and event type
            if (txtName.value == defaultText & event.type == "focus") {
                txtName.style.color = "black";
                txtName.value = "";
            }
        }
</script>

<asp:TextBox ID="txtUserName" runat="server" Text="Enter Username Here" ForeColor="Gray" onblur = "WaterMark(this, event);" onfocus = "WaterMark(this, event);" />


using AJAX Toolkit Control

<asp:TextBox ID="txtUserName" runat="server" ForeColor="Gray"   />
<cc1:TextBoxWatermarkExtender ID="txt" runat="server" WatermarkText="hii enter here" TargetControlID="txtUserName"></cc1:TextBoxWatermarkExtender>

Wednesday, October 12, 2011

Createing a alert Class in asp.net

                                  /*AlertBoxes.cs*/

using System.Web;
using System.Web.UI;

/// <summary>
/// Summary description for AlertBoxes
/// </summary>

namespace alert
{
    public class AlertBoxes
    {
        public static void ShowAlertMessage(string error)
        {
            Page page = HttpContext.Current.Handler as Page;
            if (page != null)
            {
                error = error.Replace("'", "\'");
                ScriptManager.RegisterStartupScript(page, page.GetType(), "err_msg", "alert('" + error + "');", true);
            }
        }
    }
}

/*use like this*/
                alert.AlertBoxes.ShowAlertMessage("alert");

Tuesday, October 11, 2011

Scrolling title using java script

/* Save this file as abc.js */
msg = " the test site "; //title

msg = "" + msg;pos = 0;
function scrollMSG() {
document.title = msg.substring(pos, msg.length) + msg.substring(0, pos);
pos++;
if (pos > msg.length) pos = 0
window.setTimeout("scrollMSG()",200);
}
scrollMSG();

/*use this on aspx page*/

<script src="js/abc.js" type="text/javascript"></script>

Inserting Data with xml in sql server

DataSet dsCategories = new DataSet();
dsCategories = objCompany.GetDefaultData();
string strCategoryDetails = dsCategories.GetXml();

Create PROCEDURE [dbo].[usp_Testingxml]
(
@CategoryDetails nText
)

AS

DECLARE @handle int
Declare @CompanyId int
set @CompanyId = 1


EXEC sp_xml_preparedocument @handle OUTPUT, @CategoryDetails
Insert into tblCmpcategories(CategoryId,CategoryName,CompanyId)
SELECT CategoryId, CatName, @CompanyId FROM
OPENXML (@handle, '/NewDataSet/Table',2)
WITH (CategoryId int ,CatName VARCHAR(50) ) --xml fields
EXEC sp_xml_removedocument @handle

--http://msdn.microsoft.com/en-us/magazine/cc163782.aspx

Sunday, October 9, 2011

Cursors Example

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

CREATE PROCEDURE [dbo].[usp_GetUserLeavesSummaryWithoutCarryOverForReport]
(
@CompanyId int ,
@UserID int
)
AS
declare @ReportTable table(userid int,LeaveTypeId int,LeaveType varchar(50),LeavesTaken float,UnApproved float,LeavesEntitled float,LeavesPending float,Name varchar(50),DepartmentId int )
declare @uservalue int
declare @uservalues cursor



begin
set @uservalues=Cursor for select TblUsers.UserId from TblUsers where TblUsers.Company_Id=@CompanyId
open @uservalues
fetch next
from @uservalues into @uservalue

while @@FETCH_STATUS=0

insert into @ReportTable(userid,LeaveTypeId,LeaveType,LeavesTaken,UnApproved,LeavesEntitled,LeavesPending,Name,DepartmentId)
Select UED.User_ID,UED.LeaveType_ID , LM.LeaveType,
     isnull(dbo.[getLeavesCountByUserForReport](LM.LeaveTypeID,@uservalue,@CompanyId,@date),0) as LeavesTaken,

     ( select FirstName+' '+SurName from tblUsers where UserID=@uservalue) as Name,
     ( select Department_Id from tblUsers where UserID=@uservalue) as Departmentid
      from tblLeaveMaster LM
      inner join
      Tbl_UserLeaveEntitlementDetail UED
      on
     UED.LeaveType_ID=LM.LeaveTypeID
     where UED.Company_ID=@CompanyId and UED.User_ID=@uservalue and UED.Entitlement <> 0
fetch next
from @uservalues into @uservalue
end
close @uservalues
deallocate @uservalues