C#.Net 3.0 features: Automatic Property

Microsoft introduced the new features in C#.Net 3.0 called Automatic Property.

No more need to declare the private variable for properties. It automatically creates the private variables for properties. But still you can use the conventional way of declaring the private variables to initialize the specific default value to private variable and also other calculation purposes.

Conventional Method:

class Customer
{
private long m_customerID;
private string m_name;

public string CustomerID
{
get
{
return m_customerID;
}
set
{
m_customerID = value;
}
}

public string Name
{
get
{
return m_name;
}
set
{
m_name = value;
}
}

}




C#.Net 3.0 New Method:

class Customer
{

public string CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

}


From the above declaration, you can find that the no private variable declared.


You may ask the question what would be default value for property. Here is the answer.

1. Value type = 0
2. Reference type = null

Performance difference between EXEC and sp_executesql

I presumed that you already know about the execution plan. Sometimes we may come to situation to use Dynamic SQL instead of direct T-SQL.

If we are using Direct T-SQL (not dynamic) in stored procedure, SQL Server reused execution plan from the cache. i.e. SQL Server will not compile the Stored Procedure again.

If we are using dynamic sql in stored procedure, SQL Server may not use the execution plan. It will recreate the execution plan every time with different string of SQL.

So, we have to think about the performance while using dynamic sql.

To execute the dynamic SQL in stored procedure, we have to use the following way.

1. EXEC (Non- parameterized)
2. sp_executesql (Parameterized)


There will be performance difference between above two.

Execution plan will not be created until you execute the dynamic sql. If you execute the dynamic sql using EXEC, execution plan will be created for every execution even values only changing. If you use sp_executesql, SQL Server Optimizer will try to use same execution plan. Because dynamic sql string will be the same, values only going to change. So it will be treated as Stored Procedure having input parameters.

Use the following query to test,


CREATE TABLE [dbo].[Item]
(
ID INT
)

GO

INSERT INTO [dbo].[Item](ID) VALUES (1)
INSERT INTO [dbo].[Item](ID) VALUES (2)

GO


DBCC FREEPROCCACHE

DECLARE @ItemID INT
DECLARE @Query NVARCHAR(200)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = '

SET @ItemID = 1
EXEC( @Query + @ItemID)

SET @ItemID = 2
EXEC( @Query + @ItemID)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = @ID'

SET @ItemID = 1
EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID

SET @ItemID = 2
EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID




To view the execution plan, use the following query.

SELECT usecounts, sql FROM sys.syscacheobjects



Results:

UseCounts SQL

1 SELECT * FROM [dbo].[Item] WHERE ID = 1
2 (@ID INT)SELECT * FROM [dbo].[Item] WHERE ID = @ID
1 SELECT usecounts, sql FROM sys.syscacheobjects
1 SELECT * FROM [dbo].[Item] WHERE ID = 2


From the results, executed the dynamic sql using sp_executesql uses same execution plan. EXEC create the execution plan every time.



Conclusion:

Always try to use sp_executesql to execute the dynamic sql to improve the performance.

Avoiding Global temporary table problems between databases

Global temp tables are used to store values that can be used across the stored procedures in particular application run. it will disappears when application is stopped.

Global temp tables are created by following query.

CREATE TABLE [dbo].[##Temp] (ID INT)

if global temp tables are created and used
by single instance only, problem(creating 2 temp table with same name) will not occur. The problem will occur when another instance creating same temp table at same time. This problem will occur on same database or another database with same name of temp table creation.

To avoid this confliction, we will use GUID with temp table name. But this method is more constlier and difficult to handle. Because temp table name is so lengthy.

EXEC ( 'CREATE TABLE [dbo].[##Temp' + CONVERT(VARCHAR, NEWID()) + ' ] (ID INT)' )


Otherwise we can use identity value of some table with temp table name.

EXEC ( 'CREATE TABLE [dbo].[##Temp' + @ID + ' ] (ID INT)' )


The above method will solve the problem even multiple instances running on same database. Because each instance will create unique identity value and using that id with temp table name.

Again problem will occur between different databases creating the same temp table with same identity value. This problem will occur when the multiple instance running on different databases at the same time. Because each instance trying to create temp table with same name.

To avoid confliction between different databases, use DB_ID() or DB_NAME() with temp table name.

EXEC ( 'CREATE TABLE [dbo].[##Temp' + CONVERT(VARCHAR, DB_ID()) + @ID + ' ] (ID INT)' )

EXEC ( 'CREATE TABLE [dbo].[##Temp' + CONVERT(VARCHAR, DB_NAME()) + @ID + ' ] (ID INT)' )

Because DB_ID() or DB_NAME() will be unique to each database.

Adding Documentation for Enum Members in .Net

To add the documentation for each member of enum, we have to add the

tag for each members like the below.

CODE:
-----


///<summary>
/// Priority
///</summary>
public enum Priority
{
///<summary>
/// Low Priority
///</summary>
Low,
///<summary>
/// Medium Priority
///</summary>
Medium,
///<summary>
/// High Priority
///</summary>
High,


}

Randomly selecting records from the Table - SQL Server

If you would like to retrieve the 10 records randomly, you have to use either RAND() or NEWID() function.

RAND():
------
SELECT TOP 10 EmployeeID, RAND() AS RNumber FROM dbo.Employee ORDER BY 2


NEWID():
--------
SELECT TOP 10 EmployeeID FROM dbo.Employee ORDER BY NEWID()


The above 2 methods of retrieving the records from the table have own mertis and demerits.

RAND() methods will give same sample of records in every run. so you have to come with own logic to produce different random number for every records.

NEWID() method give easy solution for the above problem. The idea behind this method is having of unique identifier for each rows. SQL Server maintain this for every rows.

There will be performance overhead problem in this approach when you are using this for TABLE having more records. Because its scans through whole table. you can avoid the full scan by limiting the records using WHERE condition.

SQL Server 2005.
---------------

SQL Server 2005 provides new option to get the records randonly. That new option is
TABLESAMPLE. This keyword is used with FROM clause. This approach could not read
through entire table. its just take the sample records instead of scanning entire
table.

TABLESAMPLE:
------------

SELECT EmployeeID FROM dbo.Employee TABLESAMPLE (50 ROWS)

SELECT EmployeeID FROM dbo.Employee TABLESAMPLE (50 PERCENT)

SELECT TOP 10 EmployeeID FROM dbo.Employee TABLESAMPLE (50 ROWS)


The above first query will not return 50 rows exactly.
First it will convert the ROWS into percent. And the select the records randomly.

Selection of random records based on DATA pages(8K) for that table instead of rows identifier. so it will not produce the expected result.

To get the expected result, we have to use the TOP clause in the select query. The TOP #(number) should be less than selection of records specified in the TABLESAMPLE.

Sometimes,Even this appraoch will not produce the expected result.so you have to use this approach carefully with your logic.

Handling Database NULL in .Net while using SqlParameter (.Net 2.0)

If you want to set the NULL for database column while inserting or updating, normal “null” OR “DBNULL.value” is not suitable.

C# code:

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = null;

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = DBNULL.value;


If you are using Sqlparameter for passing the parameter value to stored procedure, the above null types does not support. It will throw an error like “Parameter @MiddleName not specified.”.

C# code:

using System.Data.SqlTypes;

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = SqlString.Null;

The above code will execute properly without any error.

The same way you have to use for other data types like
DataTime --> SqlDateTime.Null
int32 --> SqlInt32.Null, etc

Performance Difference between Parameterized and Non-Parameterized call to Stored Procedure

You can call the sql stored procedures either with parameter ( using SqlParameter) or normal sql string ( using EXEC dbo.spName). There will be significant performance difference between two.

Non – Parameterized Stored Procedure Call:
If you call the stored procedure using EXEC ( like normal sql query) with command type as TEXT, execution plan is not reused. SQL Server caches the execution plan for SP to reuse. If you used EXEC for calling SP, new execution plan will create for every different string of EXEC statement. It will not reuse the execution plan from the SQL Server cache. SQL Server normally doing the parsing, optimizing, compiling process while creating the new execution plan.


Parameterized Stored Procedure Call:

If you call Stored procedure with Parameters ( example: SqlParameter) with Command Type as Stored Procedure, execution plan of the SP is reused again instead of creating the new one with different parameters.

Conclusion:

Use Parameterized method of calling when you call the stored procedure instead of EXEC method. You can’t feel the performance when few call to stored procedure. You can feel this performance difference when more calls to stored procedure (10000 calls frequently).

OUTPUT Command in SQL Server 2005

The output parameter that can be used in stored procedures. This is about returning effected data on a table with a few feature in SQL Server 2005.
SQL Server 2000

A simple question for you. If you want to retrieve last inserted identity value what do you do? Obviously SCOPE_IDENTITY() or @@IDENTITY will be your answer. There is a small different between these too, which I am not going to discuss right now. Even though both will satisfy the current requirement, I will use SCOPE_IDENTITY(), which is the correct one.

CREATE TABLE TempTable
(
ID INT IDENTITY(1 , 1)
, Code VARCHAR(25)
, Name VARCHAR(50)
, Salary Numeric(10 , 2)
)
INSERT INTO TempTable ( Code , Name , Salary )
VALUES ( 'A001' , 'John' , 100 )
INSERT INTO TempTable ( Code , Name , Salary )
VALUES ( 'A002' , 'Ricky' , 200 )

SELECT SCOPE_IDENTITY() AS LastInsertID

However, this will only valid when you need the last inserted ID. A Problem arises when you need the last updated or deleted data. In SQL Server 2000, you don't have any other option other than writing a trigger or triggers to capture them via inserted and/or deleted tables.

more...

Features in Microsoft SQL Server 2008 CTP

Microsoft is making a series of announcements at Tech*Ed related to SQL Server 2008 -- previously codenamed "Katmai". I've got some details on some of the new features including the MERGE statement, Table Valued Parameters, Change Data Capture and the Declarative Management Framework. There should also be a download of the June CTP available inside Connect.
more...

Other Links:
Four Pillar

Outlook with .NET 2.0

For accessing the outlook and its features you have to add reference of Microsoft Outlook 11.0 object library Version 9.2 (COM component) to your project.This COM component provides various objects through we can access the outlook.

1. Microsoft.Office.Interop.Outlook.Application
2. Microsoft.Office.Interop.Outlook.Explorer
3. Microsoft.Office.Interop.Outlook.Inspector
4. Microsoft.Office.Interop.Outlook.MAPIFolder
5. Microsoft.Office.Interop.Outlook.MailItem
6. Microsoft.Office.Interop.Outlook.AppointmentItem
7. Microsoft.Office.Interop.Outlook.TaskItem
8. Microsoft.Office.Interop.Outlook.ContactItem

more...

Debugging SQL Server 2005 Stored Procedures in Visual Studio

With Microsoft SQL Server 2000 it was possible to debug stored procedures from directly within Query Analyzer (see Debugging a SQL Stored Procedure from inside SQL Server 2000 Query Analyzer for more information). With SQL Server 2005, however, this functionality was moved out of SQL Server Management Studio and into the Visual Studio IDE. Using this technique, it is possible to step into your stored procedures, one statement at a time, from within Visual Studio. It is also possible to set breakpoints within your stored procedures' statements and have these breakpoints hit when debugging your application more...

New XML Capabilities in SQL Server 2005

Prior to SQL Server 2005, if developers wanted to convert XML data to relational data, they had to use combinations of the stored procedure sp_xml_preparedocument and the OPENXML function. While still valid, this methodology introduces some overhead. SQL 2005 provides native support for the XML data type, and new methods to directly parse and read the data more

Passing a Table to A Stored Procedure in SQL Server 2005

In this article, he trying to present a solution to the above scenario by using XML as the format to pass a table to a stored procedure. The CALLER can transform the table (Query result) to an XML variable and pass to the stored procedure. The CALLEE can either convert the XML parameter back to a TABLE variable or directly use XQuery on the XML variable. more

Enable CLR in SQL Server 2005

if you need to run CLR object in SQL Server 2005. you have to enable the CLR option.
I have used following command to enable the CLR features.


EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;

Compressing ViewState in ASP.Net 2.0

Developers often worry about performance of their web sites. Every developer wants that his web site be optimized for good performance. There are several factors affecting performance of your web site and one of them is ViewState. In this article he going to show a way by which you can compress ViewState thus improving the performance. more..

Deployment:Customize User Interfaces and Pass User Input to Installer Classes

This article going to demonstrate how to customize your MSI install to prompt the user for some information and then pass this information to an installer class. This can be useful when needing to do something during an install based on the user input.There are two key parts to this process the first is the addition of a custom user interface dialog and the second is passing whatever information is entered into the new user interface to the installer class in order to do something with this information during installation. more..

ASP.Net 2.0: Export GridView to Excel

The focus of the article is the Export to Excel functionality - the Gridview and it's data binding are only for demonstrating the Export functionality.
The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided. In this article, we will assume you are starting with a web page which holds a GridView named GridView1. The GridView in our demo code is bound to a table named "ContactPhone" in a SQL Express database. The following code which exports the databound GridView to Excel is not dependent on the specific databindings and can be used without changes for your scenario. more..

SQL Server 2005 Reporting Services

Included with SQL Server 2005 is a group of interrelated applications, collectively known as SQL Server Reporting Service (SSRS). SSRS includes all the development and management pieces necessary to publish end user reports in HTML, PDF, Excel, and CSV formats. Originally released as a SQL 2000 separate add on that could be downloaded from the web, all the Reporting Server pieces are now bundled in SQL 2005. With Reporting Services built into SQL, expect to see product adoption rise quickly. Microsoft's accounting package, Solomon, will soon discontinue use of Crystal Reports in favor of SSR. more..

Data partitioning in SQL Server 2005

Data partitioning, a new feature added to SQL Server 2005, provides a way to divide large tables and indexes into smaller parts. By doing so, it makes the life of a database administrator easier when doing backups, loading data, recovery and query processing.Data partitioning improves the performance, reduces contention and increases availability of data.A Table can be partitioned based on any column in the table. Microsoft defines that column as the partition key. more..

Coding Model and Compilation in ASP.NET 2.0

In ASP.NET 1.x ,application are compiled on first request or in a batch mode on startup. The disadvantage is we had to deploy uncompiled code into our production server. ASP.NET 2.0 offers new compilation method that precompiles source code into binary assemblies for deployment. Pre-Compiled application consists of assemblies ,resources which of no value for an attacker. PreCompiled application is more secure than an normal ASP.NET application.


Read more