Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Archive for September, 2009

Recursive Queries in sql server

Posted by Viral Sarvaiya on September 19, 2009


A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.

for more information click here…

for other links click here

Advertisements

Posted in Sql Server | Tagged: , | Leave a Comment »

PIVOT and UNPIVOT Table Examples

Posted by Viral Sarvaiya on September 19, 2009


I previously wrote two articles about PIVOT and UNPIVOT tables. I really enjoyed writing about them as it was interesting concept. One of the Jr. DBA at my organization asked me following question.

“If we PIVOT any table and UNPIVOT that table do we get our original table?”

I really think this is good question. Answers is Yes, you can but not always. When we pivot the table we use aggregated functions. If due to use of this function if data is aggregated, it will be not possible to get original data back.

Let me explain this issue demonstrating simple example.

USE AdventureWorks
GO
-- Creating Test Table
CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO
-- Selecting and checking entires in table
SELECT *
FROM Product
GO
-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO
-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO
-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM
(
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt
GO
-- Clean up database
DROP TABLE Product
GO

ResultSet:

-- Selecting and checking entires in table
Cust Product QTY
------------------------- -------------------- -----------
KATE VEG 2
KATE SODA 6
KATE MILK 1
KATE BEER 12
FRED MILK 3
FRED BEER 24
KATE VEG 3

-- Pivot Table ordered by PRODUCT
PRODUCT FRED KATE
-------------------- ----------- -----------
BEER 24 12
MILK 3 1
SODA NULL 6
VEG NULL 5

-- Pivot Table ordered by CUST
CUST VEG SODA MILK BEER CHIPS
------------------------- ----------- ----------- ----------- ----------- -----------
FRED NULL NULL 3 24 NULL
KATE 5 6 1 12 NULL

-- Unpivot Table ordered by CUST
CUST PRODUCT QTY
------------------------- -------- -----------
FRED MILK 3
FRED BEER 24
KATE VEG 5
KATE SODA 6
KATE MILK 1
KATE BEER 12
12


You can see in above example where we are using the SUM aggregated functions. SUM adds up values based on column used in the sum function. In our example Kate and Veg has two entries. In our pivot example with order by Cust the values are summed up. Now when table goes under UNPIVOT operations it transforms the table which is already went under PIVOT operation.

Looking at the final PIVOT – UNPIVOT table is little different from the original table and it contains the sum of the two records which we have observed in the PIVOT table. You can see that result which are displayed in red fonts are summed.

This way we can get the original table back if aggregate functions was not applied on the data or data was in such form that aggregate function might have not made any difference.

For more information click here

For other More Information click Here

Posted in Sql Server | Tagged: , , , | 2 Comments »

Add Watermark text on Image

Posted by Viral Sarvaiya on September 18, 2009


step 1. upload the image file
step 2. save the image and get the filename with the full the path where the image file will store.


dim pathname as string
// this "pathname" contains path with filename of the uploaded file with fileupload control.

Dim ImgStamp As New Bitmap(pathname)
Dim g As Graphics = Graphics.FromImage(ImgStamp)
g.SmoothingMode = SmoothingMode.AntiAlias
Dim y As Integer = 0
y = ImgStamp.Height - 85
Dim FSize As Double = ImgStamp.Height
Dim FWSize As Double = ImgStamp.Width
If FSize > 640 Then
'Set Position
FSize = ImgStamp.Width * 28 / 640
y = ImgStamp.Height / 1.1
ElseIf FWSize < 426 Then
'Set Position
FSize = ImgStamp.Width * 28 / 640
y = ImgStamp.Height / 1.1
Else
'Set Position
FSize = ImgStamp.Width * 28 / 640
y = ImgStamp.Height / 1.1
End If
g.DrawString("Put Water mark text here", New Font("font name", CType(FSize, Integer), FontStyle.Regular), Brushes.Azure, New Point(1, y))
ImgStamp.Save(path & imgName)

change the numbers according to the requirement, where you want the watermark text.

enjoy coding…..

Posted in ASP.NET | Tagged: , , , , , | Leave a Comment »

Sql Transaction Begin, Commit, Rollback Demo in asp.net

Posted by Viral Sarvaiya on September 18, 2009


Some programmer require to rollback and commit the transaction of the sqlserver according to the coding. That time this code will help you….


Imports System
Imports System.Data
Imports System.Data.SqlClient

public class MainClass
Shared Sub Main()
Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
"integrated security=sspi;database=MyDatabase")

' SQL Delete Commands
Dim sql As String = "DELETE FROM Employee " & _
"WHERE ID = 10"

' Create command
Dim thisCommand As New SqlCommand(sql, thisConnection)

' Create Transaction
Dim thisTransaction As SqlTransaction

Try
' Open Connection
thisConnection.Open()

' Begin transaction and attach it to command
thisTransaction = thisConnection.BeginTransaction()
thisCommand.Transaction = thisTransaction

' Run delete command
thisCommand.ExecuteNonQuery()

' Commit transaction
thisTransaction.Commit()

' Display success
Console.WriteLine("Transaction Committed. Data Deleted")

Catch ex As Exception
' Roll back transaction
thisTransaction.Rollback()

Console.WriteLine("Transaction rolled back : " & ex.Message)

Finally
' Close Connection
thisConnection.Close()

End Try
End Sub
End Class

for more information click here

enjoy coding….

Posted in ASP.NET, Sql Server | Tagged: , , , , | Leave a Comment »

OOPS Concepts and .NET

Posted by Viral Sarvaiya on September 16, 2009


Introduction

Object-Oriented Programming (OOP) is a software development paradigm that suggests developers to split a program in building blocks known as objects. The OOP paradigm allows developers to define the object’s data, functions, and its relationship with other objects.

Microsoft created the .NET Framework using OOP, and knowing this concepts has helped me to understand the .NET Framework and to design and develop better software components. The purpose of this article is to describe the basic OOP concepts using real world scenarios and to provide some code samples that demonstrate how to work with OOP and .NET.

this article is in two part

1) Part 1

2) Part 2

For more detail click here

Posted in ASP.NET | Tagged: , , , , , , , , | 5 Comments »

jQuery Tutorials Examples

Posted by Viral Sarvaiya on September 10, 2009


jQuery is a fast and concise JavaScript Library that simplifies HTML document traversing, event handling, animating, and Ajax interactions for rapid web development. jQuery is designed to change the way that you write JavaScript
So we have selected a set of best tutorials that will strengthen your practical side using jQuery .
Click here to read more…..

50 useful new jquery  techniques

create an amazing music player using mouse gestures hotkeys in jquery

creating a virtual jquery keyboard

Posted in Jquery | Tagged: , | Leave a Comment »

How to Encrypt connection string in web.config

Posted by Viral Sarvaiya on September 10, 2009


The most sensitive information stored in web.config file can be the connection string. You do not want to disclose the information related to your database to all the users where the application is deployed. Every time it is not possible to have a private machine for your sites, you may need to deploy the site in shared host environment. To encrypt the connection string in above situation is advisable.

ASP.NET 2.0 provides in built functionality to encrypt few sections of web.config file. The task can be completed using Aspnet_regiis.exe. Below is the web.config file and <connectionStrings> section.

   1: <connectionStrings>
   2:   <add name="cn1"
   3:           connectionString="Server=DB SERVER;
   4:                             database=TestDatabase;
   5:                             uid=UID;
   6:                             pwd=PWD;" />
   7:  </connectionStrings>

Fig – (1) Connection string section of web.config file

To encrypt the connection string section follow the steps,

1. Go to Start -> Programm Files -> Microsoft Visual Studio 2005 -> Visual Tools
-> Microsoft Visual Studio 2005 Command Prompt

2. Type following command,

aspnet_regiis.exe -pef “connectionStrings” C:\Projects\DemoApplication

-pef indicates that the application is built as File System website.  The second argument is the name of configuration section needs to be encrypted. Third argument is the physical path where the web.config file is located.

If you are using IIS base web site the command will be,

aspnet_regiis.exe -pe “connectionStrings” -app “/DemoApplication”

-pe indicates that the application is built as IIS based site. The second argument is the name of configuration section needs to be encrypted. Third argument “-app” indicates virtual directory and last argument is the name of virtual directory where application is deployed.

If everything goes well you will receive a message “Encrypting configuration section…Succeeded!”

Open your web.config file and you can see that connection string is encrypted,

   1: <connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">
   2:   <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
   3:    xmlns="http://www.w3.org/2001/04/xmlenc#">
   4:    <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
   5:    <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
   6:     <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
   7:      <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
   8:      <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
   9:       <KeyName>Rsa Key</KeyName>
  10:      </KeyInfo>
  11:      <CipherData>
  12:       <CipherValue>Ik+l105qm6WIIQgS9LsnF8RRxQtj2ChEwq7DbHapb440GynFEoGF6Y3EM3Iw/lyDV8+P8bIsketi5Ofy9gpZlCBir7n315Q6RPbdclUo79o/LKadhX4jHFpnSIQNIF/LhwjwkLFC0=</CipherValue>
  13:      </CipherData>
  14:     </EncryptedKey>
  15:    </KeyInfo>
  16:    <CipherData>
  17:     <CipherValue>JsLrQ5S8Pq3U72nQzmSl/XlLX72GM0O3EbPLaHRNvjTDgG9seDflGMjTfO10M1s7/mPh//3MhA7pr0dNHUJ143Svhu5YXODRC6z9CkR0uyE4H7uDvTKJ8eR3m9APhXoo1sT1K3tCLHD6a2BM+gqSk9d8PzCfbM8Gmzmpjz1ElIaxu62b4cg9SNxp8o86O9N3fBl2mq</CipherValue>
  18:    </CipherData>
  19:   </EncryptedData>
  20:  </connectionStrings>

Fig – (2) Encrypted connection string section

You do not have to write any code to decrypt this connection string in your application, dotnet automatically decrypts it. So if you write following code you can see plaintext connection string.

   1: Response.Write(ConfigurationManager.ConnectionStrings["cn1"].ConnectionString);

Now to decrypt the configuration section in web.config file use following command,

For File System Application,

aspnet_regiis.exe -pdf “connectionStrings” C:\Projects\DemoApplication

For IIS based Application

aspnet_regiis.exe -pd “connectionStrings” -app “/DemoApplication”

If you want to encrypt any nested section in web.config file like <pages> element within <system.web> you need to write full section name as shown below,

aspnet_regiis.exe -pef “system.web/Pages” C:\Projects\DemoApplication

You can encrypt all the sections of web.config file except following using the method I displayed in this article,

<processModel>
<runtime>
<mscorlib>
<startup>
<system.runtime.remoting>
<configProtectedData>
<satelliteassemblies>
<cryptographySettings>
<cryptoNameMapping>
<cryptoClasses>

To encrypt these section you needed to use Aspnet_setreg.exe tool.  For more detail about Aspnet_setreg.exe tool search Microsoft Knowledge Base article 329290, How to use the ASP.NET utility to encrypt credentials and session state connection strings.

Posted in ASP.NET | Tagged: , , , | 3 Comments »

Generate the Error Log

Posted by Viral Sarvaiya on September 10, 2009


This is the general thing is that, end user can not know the error is occur, means the error page will display when error is occur.
For that take a global.asax file, in this file just write the following code…


<%@ Application Language="VB" %>
<%@ import namespace="System.Web"%>
<%@ import namespace="System.Web.SessionState"%>
<%@ import namespace="System.IO"%>

<script runat="server">

Sub Application_Error(ByVal sender As Object, ByVal e As EventArgs)
Try
'Code that runs when an unhandled error occurs

Dim currErr As Exception = Server.GetLastError()
Dim objErr As Exception = Server.GetLastError().GetBaseException()

Dim fileName As String = ConfigurationManager.AppSettings("SITE_ROOTPATH") & "ErrorLog/Error.log." & DateTime.Today.ToString("dd-MM-yyyy") & ".txt"

Dim LogFileStrm As StreamWriter
If (File.Exists(fileName)) Then
LogFileStrm = File.AppendText(fileName)
Else
LogFileStrm = File.CreateText(fileName)
End If
LogFileStrm.WriteLine("==============" & DateTime.Now.ToString("dd / MM / yyyy h:m tt") & "====================")
LogFileStrm.WriteLine("Message: " & currErr.Message)
LogFileStrm.WriteLine("Source: " & currErr.Source)
LogFileStrm.WriteLine("StackTrace: " & currErr.StackTrace)
LogFileStrm.WriteLine("TargetSite: " & currErr.TargetSite.ToString())
LogFileStrm.WriteLine("Error: " & currErr.ToString())
LogFileStrm.WriteLine("---------Base Error Info ")
LogFileStrm.WriteLine("-------------Message: " & objErr.Message)
LogFileStrm.WriteLine("-------------Source: " & objErr.Source)
LogFileStrm.WriteLine("-------------StackTrace: " & objErr.StackTrace)
LogFileStrm.WriteLine("-------------TargetSite: " & objErr.TargetSite.ToString())
LogFileStrm.WriteLine("-------------Error: " & objErr.ToString())
LogFileStrm.WriteLine("===========================================")
LogFileStrm.Flush()
LogFileStrm.Close()

'************* code added later
Dim strError As String = ""
strError = "==============" & DateTime.Now.ToString("dd / MM / yyyy h:m tt") & "====================<br>"
strError += "Message: " & objErr.Message & "<br>"
strError += "Source: " & objErr.Source & "<br>"
strError += "StackTrace: " & objErr.StackTrace & "<br>"
strError += "TargetSite: " & objErr.TargetSite.ToString() & "<br>"
strError += "Error: " & objErr.ToString() & "<br>"

''******************************
Dim errmsg As String = Server.UrlEncode(objErr.Message)
Response.Redirect("http://" & Request.ServerVariables("SERVER_NAME") & ":" & Request.ServerVariables("SERVER_PORT") & "/dmi/Error.aspx")

Server.ClearError()

Catch erx As Exception
Throw erx
End Try
End Sub

so this code is redirect the page to error page when error is occur.

Posted in ASP.NET | Tagged: , | Leave a Comment »

How to create,Read,Delete Cookies in Asp.net

Posted by Viral Sarvaiya on September 10, 2009


A cookie is a small bit of text file that browser creates and stores on your machine (hard drive). Cookie is a small piece of information stored as a string. Web server sends the cookie and browser stores it, next time server returns that cookie.Cookies are mostly used to store the information about the user. Cookies are stores on the client side.

Here i m going to explain you by providing example of Remember me Code :

Step 1 : if check box is checked for “Remember Me” then create cookie else Delete it.

if (chkRememberMe.Checked == true)
{
//Create Cookie to Store AdminInfo
HttpCookie aCookie = new HttpCookie("AdminInfo");
aCookie.Values["userName"] = txtUsername.Text;
aCookie.Values["Password"] = txtPassword.Text;
aCookie.Values["lastVisit"] = DateTime.Now.ToString();
aCookie.Expires = DateTime.Now.AddDays(10);
Response.Cookies.Add(aCookie);
}
else
{
//Delete Cookie
HttpCookie aCookie = new HttpCookie("AdminInfo");
aCookie.Expires = DateTime.Now.AddDays(-1);
Response.Cookies.Add(aCookie);
}

Step 2 : now check cookie is null or not in page load event & set username & password from cookie

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request.Cookies["AdminInfo"] != null)
{
txtUsername.Text = Request.Cookies["AdminInfo"]["userName"] == null ? null : Request.Cookies["AdminInfo"]["userName"].ToString();
string pwd = Request.Cookies["AdminInfo"]["Password"] == null ? null : Request.Cookies["AdminInfo"]["Password"].ToString();
txtPassword.Attributes.Add("value", pwd);
}
}

}

Posted in ASP.NET | Tagged: , , | 2 Comments »

Get the record of the excel file in the dataset

Posted by Viral Sarvaiya on September 3, 2009


here i demonstrate that how to get the data of the excel file in to the dataset.

the function have the two arguments,
1) path of the excel file, from where the excel file is.
2) name of the excel file.


Public Function ReadXLS(ByVal path As String, ByVal filename As String) As DataSet

Dim ds As New DataSet
Dim strConn As String
//this is the connection string
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & filename & ";Extended Properties=Excel 8.0;"
//take the connection of the OLEDB
Dim con As New OleDbConnection(strConn)
Dim dt As DataTable
If con.State = ConnectionState.Open Then
con.Close()
End If
con.Open()
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If dt Is Nothing Then
Return Nothing
End If
//take the name of the all the sheet of the excel file to the erray
Dim excelSheets() As String = New [String](dt.Rows.Count - 1) {}
Dim i As Integer = 0
For Each row As DataRow In dt.Rows
excelSheets(i) = row("TABLE_NAME").ToString()
If con.State = ConnectionState.Open Then
Dim adpt As OleDbDataAdapter = New OleDbDataAdapter("select * from [" & excelSheets(i) & "]", con)
adpt.Fill(ds)
return ds
i += 1

end if
Next
ds.Dispose()
ds.Clear()
con.Close()
End Function

i put this code, that have some error in a particular situation, when u have a numeric and varchar datatype mix in a single column then this code give error, the particular datatype is converting NULL in the dataset.

the solution is change the connection string as below…


strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & filename & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=10"""

now the error has been solved.

to know other interesting problem click here

enjoy coding….

Posted in ASP.NET | Tagged: , , | 2 Comments »

 
%d bloggers like this: