Copy values from one object to another

public static void CopyPropertyValues(object source, object destination)
{
    var destProperties = destination.GetType().GetProperties();

    foreach (var sourceProperty in source.GetType().GetProperties())
    {
        foreach (var destProperty in destProperties)
        {
            if (destProperty.Name == sourceProperty.Name &&
        destProperty.PropertyType.IsAssignableFrom(sourceProperty.PropertyType))
            {
                destProperty.SetValue(destination, sourceProperty.GetValue(
                    source, new object[] { }), new object[] { });

                break;
            }
        }
    }
}

What are the difference between DDL, DML and DCL commands?

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Genereate Serial Number in Sql Server 2000 or 2005 query

In Sql Server 2005 and above version you can generate serial number as:

select row_number() over(order by empid) as serial_Number, empid,empname from emp

In Sql server 2000, you can generate serial number as:

select (select sum(1) from emp a where a.empid<=obj.empid) as serial_number,* from emp obj

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)

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.

2) Run svcutil http://localhost:54464/SVCTest/Service.svc?wsdl

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.

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')

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')