Monday, 14 April 2014

Connected and Disconnected Architecture in ADO.Net

Connected Architecture of ADO.NET

The architecture of ADO.net, in which connection must be opened to access the data retrieved from database is called as connected architecture. Connected architecture was built on the classes connection, command, datareader and transaction.

Connected architecture is when you constantly make trips to the database for any CRUD (Create, Read, Update and Delete) operation you wish to do. This creates more traffic to the database but is normally much faster as you should be doing smaller transactions.

Disconnected Architecture in ADO.NET

The architecture of ADO.net in which data retrieved from database can be accessed even when connection to database was closed is called as disconnected architecture. Disconnected architecture of ADO.net was built on classes connection, dataadapter, commandbuilder and dataset and dataview.

Disconnected architecture is a method of retrieving a record set from the database and storing it giving you the ability to do many CRUD (Create, Read, Update and Delete) operations on the data in memory, then it can be re-synchronized with the database when reconnecting. A method of using disconnected architecture is using a Dataset.

DataReader is Connected Architecture since it keeps the connection open until all rows are fetched one by one

DataSet is DisConnected Architecture since all the records are brought at once and there is no need to keep the connection alive

Difference between Connected and disconnected architecture

Connected	Disconnected
It is connection oriented.	It is dis_connection oriented.
Datareader	DataSet
Connected methods gives faster performance	Disconnected get low in speed and performance.
connected can hold the data of single table	disconnected can hold multiple tables of data
connected you need to use a read only forward only data reader	disconnected you cannot
Data Reader can't persist the data	Data Set can persist the data
It is Read only, we can't update the data.	We can update data

Example

Create Database “Student”

CREATE TABLE [dbo].[Student]

(

[ID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,

[Name] [varchar](255) NULL,

[Age] [int] NULL,

[Address] [varchar](255) NULL

)

INSERT INTO Student([Name],[Age],[Address])VALUES('NAME 1','22','PUNE')

INSERT INTO Student([Name],[Age],[Address])VALUES('NAME 2','25','MUMBAI')

INSERT INTO Student([Name],[Age],[Address])VALUES('NAME 3','23','PUNE')

INSERT INTO Student([Name],[Age],[Address])VALUES('NAME 4','21','DELHI')

INSERT INTO Student([Name],[Age],[Address])VALUES('NAME 5','22','PUNE')

HTML

<title>Untitled Pagetitle>

</head>

<body>

<div>

<asp:GridView ID="GridView1" runat="server" BackColor="White"BorderColor="#CC9966"

BorderStyle="None" BorderWidth="1px" CellPadding="4">

</asp:GridView>

<br />

<asp:Button ID="Connected" runat="server" OnClick="Connected_Click"Text="Connected" />

<asp:Button ID="Disconnected" runat="server" EnableTheming="False"OnClick="Disconnected_Click"

Text="Disconnected" />

</div>

</form>

</body>

</html>

Code Behind

String StrSQL = "", StrConnection = "";

protected void Page_Load(object sender, EventArgs e)

{

StrSQL = "SELECT * FROM Student";

StrConnection = "Data Source=ServerName;Initial Catalog=Database;User ID=Username;Password=password";

}

protected void Connected_Click(object sender, EventArgs e)

{

using (SqlConnection objConn = new SqlConnection(StrConnection))

{

SqlCommand objCmd = new SqlCommand(StrSQL, objConn);

objCmd.CommandType = CommandType.Text;

objConn.Open();

SqlDataReader objDr = objCmd.ExecuteReader();

GridView1.DataSource = objDr;

GridView1.DataBind();

objConn.Close();

}

protected void Disconnected_Click(object sender, EventArgs e)

{

SqlDataAdapter objDa = new SqlDataAdapter();

DataSet objDs = new DataSet();

using (SqlConnection objConn = new SqlConnection(StrConnection))

{

SqlCommand objCmd = new SqlCommand(StrSQL, objConn);

objCmd.CommandType = CommandType.Text;

objDa.SelectCommand = objCmd;

objDa.Fill(objDs, "Student");

GridView1.DataSource = objDs.Tables[0];

GridView1.DataBind();

}

Connected and Disconnected Data Access Architecture

The ADO.NET Framework supports two models of Data Access Architecture, Connection Oriented Data Access Architecture and Disconnected Data Access Architecture.

In Connection Oriented Data Access Architecture the application makes a connection to the Data Source and then interact with it through SQL requests using the same connection. In these cases the application stays connected to the database system even when it is not using any Database Operations.

ADO.Net solves this problem by introduces a new component called Dataset. The DataSet is the central component in the ADO.NET Disconnected Data Access Architecture. A DataSet is an in-memory data store that can hold multiple tables at the same time. DataSets only hold data and do not interact with a Data Source. One of the key characteristics of the DataSet is that it has no knowledge of the underlying Data Source that might have been used to populate it.

  DataSet ds = new DataSet();

In Connection Oriented Data Access, when you read data from a database by using a DataReader object, an open connection must be maintained between your application and the Data Source. Unlike the DataReader, the DataSet is not connected directly to a Data Source through a Connection object when you populate it. It is the DataAdapter that manages connections between Data Source and Dataset by fill the data from Data Source to the Dataset and giving a disconnected behavior to the Dataset. The DataAdapter acts as a bridge between the Connected and Disconnected Objects.

  SqlDataAdapter adapter = new SqlDataAdapter("sql", "connection");
  DataSet ds = new DataSet();

  adapter.Fill(ds, "Src Table");

By keeping connections open for only a minimum period of time, ADO .NET conserves system resources and provides maximum security for databases and also has less impact on system performance.

Sunday, 13 April 2014

Difference Between Int32.Parse(), Convert.ToInt32(), and Int32.TryParse()

Introduction

Int32.parse(string)

Int32.Parse (string s) method converts the string representation of a number to its 32-bit signed integer equivalent. When s is a null reference, it will throw ArgumentNullException. If s is other than integer value, it will throw FormatException. When s represents a number less than MinValue or greater than MaxValue, it will throw OverflowException. For example:

Collapse | Copy Code

string s1 = "1234"; 
string s2 = "1234.65"; 
string s3 = null; 
string s4 = "123456789123456789123456789123456789123456789"; 

int result; 
bool success; 

result = Int32.Parse(s1); //-- 1234 
result = Int32.Parse(s2); //-- FormatException 
result = Int32.Parse(s3); //-- ArgumentNullException 
result = Int32.Parse(s4); //-- OverflowException

Convert.ToInt32(string)

Convert.ToInt32(string s) method converts the specified string representation of 32-bit signed integer equivalent. This calls in turn Int32.Parse ()method. When s is a null reference, it will return 0 rather than throw ArgumentNullException. If s is other than integer value, it will throwFormatException. When s represents a number less than MinValue or greater than MaxValue, it will throw OverflowException. For example:

Collapse | Copy Code

result = Convert.ToInt32(s1); //-- 1234 
result = Convert.ToInt32(s2); //-- FormatException 
result = Convert.ToInt32(s3); //-- 0 
result = Convert.ToInt32(s4); //-- OverflowException

Int32.TryParse(string, out int)

Int32.Parse(string, out int) method converts the specified string representation of 32-bit signed integer equivalent to out variable, and returns true if it is parsed successfully, false otherwise. This method is available in C# 2.0. When s is a null reference, it will return 0 rather than throwArgumentNullException. If s is other than an integer value, the out variable will have 0 rather than FormatException. When s represents a number less than MinValue or greater than MaxValue, the out variable will have 0 rather than OverflowException. For example:

Collapse | Copy Code

 success = Int32.TryParse(s1, out result); //-- success => true; result => 1234 
 success = Int32.TryParse(s2, out result); //-- success => false; result => 0 
 success = Int32.TryParse(s3, out result); //-- success => false; result => 0 
 success = Int32.TryParse(s4, out result); //-- success => false; result => 0

Convert.ToInt32 is better than Int32.Parse since it returns 0 rather than an exception. But again, according to the requirement, this can be used. TryParsewill be the best since it always handles exceptions by itself.

Wednesday, 9 April 2014

SQL Server Case Expression

SQL SERVER – CASE Statement/Expression Examples and Explanation

CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.

Two basic formulations for CASE expression
1) Simple CASE expressions
A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

Syntax:

CASE expressionWHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]END

Example:

DECLARE @TestVal INT
SET @TestVal = 3SELECTCASE @TestValWHEN 1 THEN 'First'WHEN 2 THEN 'Second'WHEN 3 THEN 'Third'ELSE 'Other'END

2) Searched CASE expressions
A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expressions is shown below:

Syntax:

CASEWHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2] [...]]
[ELSE expressionN]END

Example:

DECLARE @TestVal INT
SET @TestVal = 5SELECTCASEWHEN @TestVal <=3 THEN 'Top 3'ELSE 'Other'END

----------------------------------

Sometimes, you required to fetch or modify the records based on some conditions. In this case, you may use cursor or loop for modify your records. In this situation Case expression is best alternative for Cursor/looping and also provides better performance.

You can use CASE expressions anywhere in the SQL Query like CASE expressions can be used with in SELECT statement, WHERE clauses, Order by clause, HAVING clauses,Insert, UPDATE and DLETE statements.

Format of CASE expression

The CASE expression has following two formats:

Simple CASE expression

This compares an expression to a set of simple expressions to find the result. This expression compares an expression to the expression in each WHEN clause for equivalency. If the expression with in the WHEN clause is matched, the expression in the THEN clause will be returned.

Syntax
```
CASE expression
WHEN expression1 THEN Result1
WHEN expression2 THEN Result2
ELSE ResultN
END
```
Searched CASE expressions

This expression evaluates a set of Boolean expressions to find the result. This expression allows comparison operators, and logical operators AND/OR with in each Boolean expression.

Syntax
```
CASE
WHEN Boolean_expression1 THEN Result1
WHEN Boolean_expression2 THEN Result2
ELSE ResultN
END
```

CASE Expression Example


CREATE TABLE dbo.Customer 
( 
CustID INT IDENTITY PRIMARY KEY, 
FirstName VARCHAR(40) NOT NULL, 
LastName VARCHAR(40) NOT NULL, 
StateCode VARCHAR(20) NOT NULL, 
PayRate money NOT NULL DEFAULT 0.00,
Gender VARCHAR(1) NOT NULL, 
) 
GO
 
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender) 
VALUES('Tejendra', 'Kumar', 'UP', 150.00,'M')
 
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender) 
VALUES('Jolly', 'Kapoor', 'MP', 50.00 ,'F')
 
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender) 
VALUES('Pavan', 'Kumar', 'MP', 200.00 ,'M')
 
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender) 
VALUES('Boby', 'Sharma', 'DL', 180.00 ,'F')
 
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender) 
VALUES('Asif', 'Khan', 'DL', 210.00 ,'M')
GO 
 
SELECT * from Customer

SELECT statement with CASE expressions


--Simple CASE expression: 
SELECT FirstName, State=(CASE StateCode
 WHEN 'MP' THEN 'Madhya Pradesh' 
 WHEN 'UP' THEN 'Uttar Pradesh' 
 WHEN 'DL' THEN 'Delhi' 
 ELSE NULL 
 END), PayRate
FROM dbo.Customer
 
-- Searched CASE expression:
SELECT FirstName,State=(CASE 
 WHEN StateCode = 'MP' THEN 'Madhya Pradesh' 
 WHEN StateCode = 'UP' THEN 'Uttar Pradesh' 
 WHEN StateCode = 'DL' THEN 'Delhi' 
 ELSE NULL 
 END), PayRate
FROM dbo.Customer

Update statement with CASE expression


-- Simple CASE expression: 
UPDATE Customer 
SET StateCode = CASE StateCode
 WHEN 'MP' THEN 'Madhya Pradesh' 
 WHEN 'UP' THEN 'Uttar Pradesh' 
 WHEN 'DL' THEN 'Delhi' 
 ELSE NULL 
 END 
 
-- Simple CASE expression: 
UPDATE Customer 
SET StateCode = CASE 
 WHEN StateCode = 'MP' THEN 'Madhya Pradesh' 
 WHEN StateCode = 'UP' THEN 'Uttar Pradesh' 
 WHEN StateCode = 'DL' THEN 'Delhi' 
 ELSE NULL 
 END

ORDER BY clause with CASE expressions


-- Simple CASE expression: 
SELECT * FROM dbo.Customer
ORDER BY 
 CASE Gender WHEN 'M' THEN FirstName END Desc,
 CASE Gender WHEN 'F' THEN LastName END ASC
 
-- Searched CASE expression: 
SELECT * FROM dbo.Customer
ORDER BY 
 CASE WHEN Gender='M' THEN FirstName END Desc,
 CASE WHEN Gender='F' THEN LastName END ASC

Having Clause with CASE expression


-- Simple CASE expression: 
SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
FROM dbo.Customer
GROUP BY StateCode,Gender,FirstName
HAVING (MAX(CASE Gender WHEN 'M' 
 THEN PayRate 
 ELSE NULL END) > 180.00
 OR MAX(CASE Gender WHEN 'F' 
 THEN PayRate 
 ELSE NULL END) > 170.00)
 
-- Searched CASE expression: 
SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
FROM dbo.Customer
GROUP BY StateCode,Gender,FirstName
HAVING (MAX(CASE WHEN Gender = 'M' 
 THEN PayRate 
 ELSE NULL END) > 180.00
 OR MAX(CASE WHEN Gender = 'F' 
 THEN PayRate 
 ELSE NULL END) > 170.00)

What do you think?

I hope you will enjoy the tips while playing with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Introduction

This article will give you an idea about how to use CASE expression in T-SQL or as a formula of a particular column.

What is CASE Expression

CASE is the special scalar expression in SQL language. CASE expression is widely used to facilitate determining / setting a new value from user input values. CASE expression can be used for various purposes which depends on the business logic.

CASE expression is mostly used in SQL stored procedure or as a formula for a particular column, which optimizes the SQL statements.

Syntax of CASE Expression

SQL CASE expression is used as a type of IF-THEN-ELSE statement. It is similar to switch statement in recent programming languages such as C# and Java. The syntax of the CASE statement is simple as follows:

Collapse | Copy Code

1.    CASE column_name  
2.      WHEN condition1 THEN result1  
3.      WHEN condition2 THEN result2  
4.      ...  
5.      ELSE result  
6.    END

Sample Example of CASE Statement

Collapse | Copy Code

DECLARE @intInput INT
SET @intInput = 2
SELECT CASE(@intInput) WHEN 1 THEN 'One' WHEN 2 THEN 'Two' _
  WHEN 3 THEN 'Three' ELSE 'Your message.' END

Use of CASE Expression

The case expression can be used anywhere scalar expressions are allowed, including in WHERE and HAVING clauses of the select statement.

In this article, I would like to show the most commonly used case expression in:

Stored procedure
Formula of a particular column
View

Basic Use in a Stored Procedure

A simple example of using CASE in a stored procedure is given below:

Collapse | Copy Code

-- =============================================
-- Author: Md. Marufuzzaman
-- Create date: 
-- Description:    A simple example of CASE expression.
-- =============================================
/*
DECLARE @varCountry VARCHAR(100)
EXEC spGetCountry 1, @varCountry OUTPUT
SELECT @varCountry
*/

ALTER PROCEDURE [dbo].[spGetCountry]
 @intCode        INT
,@varOutPut         VARCHAR(100) OUTPUT
AS
BEGIN
 
SELECT CASE(@intCode) WHEN 1 THEN 'Country_1'
              WHEN 2 THEN 'Country_2'
              WHEN 3 THEN 'Country_3'
              WHEN 4 THEN 'Country_4'
              WHEN 5 THEN 'Country_5'
              WHEN 6 THEN 'Country_6'
              WHEN 7 THEN 'Country_7'
              WHEN 8 THEN 'Country_8'
              WHEN 9 THEN 'Country_9'
              WHEN 10 THEN 'Country_10'         
                      ELSE 'Unknown' END  
      
END

Basic Use in a Table Column Formula

When we create a Table in design mode, SQL server provides us the properties of each column, where we can set various property values like a default value of a column, identity of a column, etc. Every column has a special property that is a custom formula, where you can set your own formula for data manipulation. Let’s take an example:

Our target is to write a formula for a column, and this formula is responsible for setting a new value for another column.

Figure 1 - How we can set a formula for a particular column

A simple example of using CASE in a Table column formula is given below:

Collapse | Copy Code

// SQL CASE statement
(case [Code] when (1) then 'Country_1' when (2) then 'Country_2' _
 when (3) then 'Country_3' when (4) then 'Country_4' when (5) _
 then 'Country_5' when (6) then 'Country_6' when (7) then 'Country_7' _
 when (8) then 'Country_8' when (9) then 'Country_9' when (10) _
 then 'Country_10' else 'Unknown' end)

When you insert / update a value at column “code”, the SQL server will fire the formula which is associated with the column “code” and finally set the value of that particular column.

http://www.aspdotnet-suresh.com/2012/07/query-to-use-case-statement-in-sql.html

The CASE keyword is used as a conditional operator that considers a value, examines it, and acts on an option depending on the value. The formula of the CASE statement is:

CASE Expression
 WHEN Value1 THEN Result
 WHEN Value2 THEN Result

 WHEN Value_n THEN Result
END

In the following example, a letter that represents a student is provided. If the letter is m or M, a string is created as Male. If the value is provided as f or F, a string is created as Female:

DECLARE @CharGender Char(1),
 @Gender  Varchar(20);
SET @CharGender = 'F';
SET @Gender = 
 CASE @CharGender
  WHEN N'm' THEN N'Male'
  WHEN N'M' THEN N'Male'
  WHEN N'f' THEN N'Female'
  WHEN N'F' THEN N'Female'
 END;

SELECT N'Student Gender: ' + @Gender;
GO

Here is the result of executing it:

CASE...WHEN...THEN...ELSE

In most cases, you may know the only types of value that would be submitted to a CASE statement. In some other cases, an unpredictable value may be submitted. If you anticipate a value other than those you are aware of, the CASE statement provides a "fit-all' alternative by using the last statement as ELSE. In this case, the formula of the CASE statement would be:

CASE Expression
 WHEN Value1 THEN Result
 WHEN Value2 THEN Result
 WHEN Value_n THEN Result
 
 ELSE Alternative
END

The ELSE statement, as the last, is used when none of the values of the WHEN statements fits. Here is an example:

DECLARE @CharGender Char(1),
 @Gender  Varchar(20);
SET @CharGender = 'g';
SET @Gender = 
 CASE @CharGender
  WHEN N'm' THEN N'Male'
  WHEN N'M' THEN N'Male'
  WHEN N'f' THEN N'Female'
  WHEN N'F' THEN N'Female'
  ELSE 'Unknown'
 END;

SELECT N'Student Gender: ' + @Gender;
GO

This would produce:

If you don't produce an ELSE statement but a value not addressed by any of the WHEN statements is produced, the result would be NULL. Here is an example:

This means that it is a valuable safeguard to always include an ELSE sub-statement in a CASE statement.

Windows Programming Tips

Monday, 14 April 2014

Connected and Disconnected Architecture in ADO.Net

Connected and Disconnected Data Access Architecture

Sunday, 13 April 2014

Difference Between Int32.Parse(), Convert.ToInt32(), and Int32.TryParse()

Introduction

Int32.parse(string)

Convert.ToInt32(string)

Int32.TryParse(string, out int)

Wednesday, 9 April 2014

SQL Server Case Expression

SQL SERVER – CASE Statement/Expression Examples and Explanation

Format of CASE expression

Simple CASE expression

Syntax

Searched CASE expressions

Syntax

CASE Expression Example

SELECT statement with CASE expressions

Update statement with CASE expression

ORDER BY clause with CASE expressions

Having Clause with CASE expression

What do you think?

Table of Contents

Introduction

What is CASE Expression

Syntax of CASE Expression

Sample Example of CASE Statement

Use of CASE Expression

Basic Use in a Stored Procedure

Basic Use in a Table Column Formula