Thursday, December 8, 2011

DBCC RESEED Table Identity Value – Reset Table Identity

During application development, we often input dummy data into our database for testing purposes. But then we come to the point where we want all records of the table to be deleted and also want to start the identity column values from 0. For this, we delete existing data using the truncate command. This will delete data from table and also reset the identity column value to 0.

truncate table [table_name] -- for example truncate table product

But the truncate command fails to delete the data if there is a relationship given to the table and the identity column is not reset.

The other way is...

In this case, first you need to delete data from the child and the master table.

After deleting data, fire this command and it will reset your identity column to 0.

DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value]) DBCC CHECKIDENT('product', RESEED, 0)

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.

DBCC CHECKIDENT (yourtable, reseed, 34)


Friday, November 18, 2011

How to use Web Service in Web Application


Wcf Service:
There is new baby of microsoft in the world. called WCF service. But there is huge Question how to call it in webapplication. thoug it is as easy as we are doing with webservice.
there is a huge differance in the plateform on which webservice is built and on which WCF is service in built.
Differance:
The Main differance bet'n this two services (WebService/WCF) is With WCF Service SOAP messages can be transmitted over a variety of supported protocols including IPC (named pipes), TCP, HTTP and MSMQ. Like any distributed messaging platform,on the other hand in WebService SOAP message can only transmitted via HTTP.
How To Create WCF Service:
To Create WCF service Create
1).Create blank website
2).Right-Click > Add Item >WCF Service
3). Add referance to
System.ServiceModel.
Three files will be created in solution .
A) App_Code/IService.cs
Content:
using System.ServiceModel;// NOTE: If you change the interface name "IService" here, you must also update the reference to "IService" in Web.config.
[ServiceContract]
public interface IService
{
[OperationContract]
string DoWork();

}

B) App_Code/Service.cs
Content:
using System;
using System.ServiceModel.Activation;
// NOTE: If you change the class name "Service" here, you must also update the reference to "Service" in Web.config.
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
public class Service : IService
{
public string DoWork()
{

return DateTime.Now.ToString();
}
}
C) Service.svc
Content:
<%@ ServiceHost Language="C#" Debug="true" Service="Service" CodeBehind="~/App_Code/Service.cs" %>

And Following lines of Configuration will automatically added to Web.Config file of your web site.
<system.serviceModel>
<behaviors>
<serviceBehaviors>
<behavior name="ServiceBehavior">
<serviceMetadata httpGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="false" />
behavior>
serviceBehaviors>
behaviors>
<services>
<service behaviorConfiguration="ServiceBehavior" name="Service">
<endpoint address="" binding="wsHttpBinding" contract="IService">
<identity>
<dns value="localhost" />
identity>
endpoint>
<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
service>
services>
<serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
system.serviceModel>
This are the steps to create WCF service.To call WCF service in our application we have to Add bindings in web.Config file & also have to create a class which acts as proxy of WCF service in our application.
How to Consume WCF service in WebApplication:
to call WCF service we have to add following service contract in our web.config file. please copy and paste below configuration tags between <system.serviceModel> system.serviceModel> elements.
Service binding configuration:
<bindings>
<wsHttpBinding>
<binding name="WSHttpBinding_IService" closeTimeout="00:01:00">
binding>
wsHttpBinding>
bindings>
<client>
<endpoint address="http://localhost:54464/SVCTest/Service.svc"
binding="wsHttpBinding" bindingConfiguration="WSHttpBinding_IService"
contract="IService" name="WSHttpBinding_IService">
<identity>
<dns value="localhost" />
identity>
endpoint>
client>
Now we have done with configuration of WCF service configuration.now we have to create ClassLibrary which contains proxy class to call WCF service methods.
Steps to create proxy is:
1) on solution Right-Click >Add New Project >Class Library.
2) Add Class and name it ServiceCaller.cs.
3) Add Referance to System.ServiceModel in Class Library project.
4 ) Add following code in that class file.
namespace WCFServices
{
[System.ServiceModel.ServiceContractAttribute(ConfigurationName = "IService")]
public interface IService
{

[System.ServiceModel.OperationContractAttribute(Action = "http://tempuri.org/IService/DoWork", ReplyAction = "http://tempuri.org/IService/DoWorkResponse")] //This line is must
string DoWork();
}
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
public partial class ServiceClient : System.ServiceModel.ClientBase<IService>, IService
{

public ServiceClient(){}
public string DoWork()
{

return base.Channel.DoWork();
}
}
}
Finally now we are done with creating WCF service caller class.I have given sample code for service proxy and configuration elements.But you can also create configuration Using Command line Called svcutil.exe Automatically.
Steps to create configuration and proxy class using Command-line(svcutil.exe):
1) Open Visual Studio 2008 Command Prompt.
Service.cs and output.config will be automaticall create at
Program Files (x86)\Microsoft Visual Studio 9.0\VC.
you can use this Service.cs as ServiceCaller and elements inside output.config as configuration elements for web.config file.
To call WCF service in our application is very easy now.
Just Create object of ServiceClient Class. and you will be able to call methos in WCF service.
Eg.
protected void Page_Load(object sender, EventArgs e)
{

WCFServices.ServiceClient sc = new WCFServices.ServiceClient();
string currentTime = sc.DoWork();
Response.Write(currentTime);
sc.Close();
//Response.Write(DateTime.Now.ToString());

}
Here we GO.
you can find more details of each and every class and terms and Namespace used here in following links.also can find more about making WCF service more secure from the referances given here.

Tuesday, September 6, 2011

Trigger

Create table test (Id int,Name varchar(50))
Create table tblTest (Id int,testvalue varchar(50),Name varchar(50))
Go
Create TRIGGER tritest /* change to ALTER when you edit this trigger */
ON tbltest
FOR INSERT, UPDATE /* Fire this trigger when a row is INSERTed or UPDATEd */
AS
BEGIN
UPDATE tblTest SET tbltest.testvalue=tbltest.name FROM INSERTED WHERE inserted.id=tbltest.id
END
Go
insert into tblTest values(2,'TEST','MURLI')

Sunday, September 4, 2011

SQL SERVER – Introduction to SERVERPROPERTY and example

SERVERPROPERTY is very interesting system function. It returns many of the system values. I use it very frequently to get different server values like Server Collation, Server Name etc.

Run following script to see all the properties of server.
SELECT 'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')
UNION ALL
SELECT 'CollationID', SERVERPROPERTY('CollationID')
UNION ALL
SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
UNION ALL
SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

Thursday, August 11, 2011

Using MERGE in SQL Server to insert, update and delete at the same time

Beginning with SQL Server 2008, now you can use MERGE SQL command to perform these operations in a single statement. This new command is similar to the UPSERT (fusion of the words UPDATE and INSERT.) command of Oracle; it inserts rows that don’t exist and updates the rows that do exist. With the introduction of the MERGE SQL command, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update or delete.

The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:

SYntex :-
MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE] ON <search_condition>
[WHEN MATCHED THEN <merge_matched>]
[WHEN NOT MATCHED [BY TARGET] THEN <merge_not_matched>]
[WHEN NOT MATCHED BY SOURCE THEN <merge_ matched=""> ]

Example:-

--Create a target table
CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
--Insert records into target table
INSERT INTO Products
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 20.00),
(3, 'Muffin', 30.00),
(4, 'Biscuit', 40.00)
GO
--Create source table
CREATE TABLE UpdatedProducts
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
--Insert records into source table
INSERT INTO UpdatedProducts
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 25.00),
(3, 'Muffin', 35.00),
(5, 'Pizza', 60.00)
GO


--Synchronize the target table with
--refreshed data from source table
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE ON (TARGET.ProductID = SOURCE.ProductID)
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action, DELETED.ProductID AS TargetProductID, DELETED.ProductName AS TargetProductName, DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID, INSERTED.ProductName AS SourceProductName, INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO

Wednesday, August 10, 2011

List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

Following script are very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database. This is simple but useful script from my personal archive.
USE AdventureWorks;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO

2.
Select * from INFORMATION_SCHEMA.CONSTRAINT_Table_Usage

Insert and Update with OpenXML in SQL

This is pretty easy to do. Below is an example that uses a table named 'test' that has an ID column called xmlID and a data column called xmlData.
 

declare @i int
 
exec sp_xml_preparedocument @i output,
'<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>'
 
insert into test
select xmlID, xmlData
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30))
where xmlID not in (select xmlID from test)
 
update test
set test.xmlData = ox.xmlData
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID
 
exec sp_xml_removedocument @i