Saturday, January 29, 2011

How To Access MSMQ Programatically


Before i get started with MSMQ,I believe most of the developers have question like What Is MSMQ ?
Message Queuing is a middleware component of the Windows operating system. Your application can use Message Queuing to send messages to another application even if the recipient application is not running or the computer on which the sender or recipient application is running is disconnected from the network. Messages are stored and forwarded by Message Queuing until they reach the destination queue. Later, when a recipient application runs, it can retrieve the messages from the queue. Message Queuing decouples sender and recipient applications so they do not need to run at the same time. Message Queuing provides built-in security, transaction support, and more
Second Question like How do I get Message Queuing ?
Message Queuing is a built-in component of Windows. Fig Below Shows where you can find MSMQ Component in your computer

To install Message Queuing on a computer running Windows 2000 or Windows XP 2003/2008 SERVER
1.Click Start, click Control Panel, and then click Add or Remove Programs.
2.Click Add/Remove Windows Components.
3.In the Windows Components Wizard dialog box, in the Components box, select the Message Queuing check box, and then click Next.
To install Message Queuing on a computer running Windows Server 2003
1.Point to Start, point to Control Panel, and then click Add or Remove Programs.
2.In the Add or Remove Programs dialog box, click Add/Remove Windows Components.
3.In the Windows Components Wizard dialog box, in the Components box, click Application Server, and then click Details.
4.In the Application Server dialog box, in the Subcomponents of Application Server box, select the Message Queuing check box, and then click OK.
5.In the Windows Components Wizard dialog box, click Next.
alright,Likewise click certain Next..Next and you will find it installed
Now Let us Start up with Creating MSMQ Queue programatically The following code snippet shows how to create a new message queue. First you want to check with MessageQueue.Exists if the queue already exists. If not, then you can create a new queue with MessageQueue.Create and provide the queue path plus if the queue is transactional or not. The queue path consists of three parts - the machine name, if it is a private queue or not plus the name of the queue. If you address a MSMQ instance on the same machine your code runs on, then you can use instead of the machine name also a dot. A public queue path uses "Machine name\Queue name" and a private queue path "Machine name\Private$\Queue name". The label of the queue is a user friendly name of the queue. Queues can also participate in transactions. MSMQ distinguishes between internal and external transactions. Internal transactions only encompass MSMQ. You start a transaction with a MessageQueueTransaction object, then send your messages with a MessageQueue object and at the end commit or abort the transaction. Aborting an internal transaction will roll back all send messages, meaning no message will be send. Messages can only be read/received after the internal transaction has been committed. External transaction means the component which sends the messages to a queue is registered in COM+ and participates in a COM+ transaction. In this case you use the COM+ transaction controls which itself use the MSDTC "Distributed Transaction Coordinator". The MSDTC will commit or abort the transaction. Otherwise internal and external transactions behave the same way. You can us internal or external transactions only when the queue has been marked as transactional. If the queue has been marked as transactional you can send only transactional messages and if it has been marked as "not transactional" then you can only send non transactional messages.

// check to make sure the message queue does not exist already
if (!MessageQueue.Exists(QueueName))
{
      // create the new message queue and make it transactional
      MessageQueue MQ = MessageQueue.Create(QueueName,true);
      // set the label name and close the message queue
      MQ.Label = LabelName;
      MQ.Close();
} 


The next code snippet shows how to delete an existing queue. First you check with MessageQueue.Exists if the queue exists then you delete it with MessageQueue.Delete. Message queues have also windows security ACLs assigned. This allows you to control access to queues through standard windows security. The security for queues can be set through the "Computer Management" interface by bringing up the properties window of a queue. If you do not have the right to delete the queue then MessageQueue.Delete raises a MessageQueueException exception, which for example is handled in the code snippet below.

 // check to make sure the message queue does exist
if (MessageQueue.Exists(QueuePath))
{
       try
      {
            MessageQueue.Delete(QueuePath);
      }
     catch ( MessageQueueException)
      {
             MessageQueue.Show( "You do not have the rights to delete the queue.");
      }
}

You can also enumerate a list of all message queues present on a MSMQ instance. To get a list of public queues you call GetPublicQueues, GetPublicQueuesByCategory, GetPublicQueuesByLabel or GetPublicQueuesByMachine on the MessageQueue class. To get the list of private queues you call MessageQueue.GetPrivateQueuesByMachine. The method names are self explanatory. All of them return an array of MessageQueue objects which you then use to obtain information about each queue. The code snippet below gets the list of private queues and then loops through each to get the queue name and label and if the queue is transactional or not.

 // get the list of message queues 
MessageQueue[] MQList = MessageQueue.GetPrivateQueuesByMachine(MachineName);
// check to make sure we found some private queues on that machine if (MQList.Length > 0) { // allocate a string array which holds for each queue the name, path, etc. string[,] MQNameList = new string[MQList.Length, 3]; // loop through all message queues and get the name, path, etc. for (int Count = 0; Count < MQList.Length; Count++) { MQNameList[Count, 0] = MQList[Count].QueueName; MQNameList[Count, 1] = MQList[Count].Label; MQNameList[Count, 2] = MQList[Count].Transactional.ToString(); } }

A look how to send and receive messages with the .NET framework

You use a Message object to send a message to a queue. First you create a new MessageQueue object and pass along the queue path you want the message to be send to. Next you create a Message object and pass along the object which contains the data you want to send. When the message is send, this object will get serialized and stored in the message body. The default serialization object used is the XmlMessageFormatter, which serializes the object to XML. You can also use a BinaryMessageFormatter, which formats the object into binary format, or an ActiveXMessageFormatter which serializes in a format which is compatible with COM message queuing components. This formatter works with most primitive types and requires for objects the implementation of the IPersistStream interface. If you want to use a formatter other then the default one then you need to create an instance of it and assign it to the Formatter property of the message object. Then you set the properties of the message object as required and finally call the Send() method on the message queue object you created. At the end you close the queue by calling Close() on the message queue object. Here is a sample code snippet:

// create a message queue object
MessageQueue MQueue = new MessageQueue(MessageQueuePath);
// create the message and set the base properties
Message Msg = new Message(TheObject);
Msg.ResponseQueue = new MessageQueue(ResponseMessageQueuePath);
Msg.Priority = MessagePriority.Normal;
Msg.UseJournalQueue = true;
Msg.Label = Label;
// we want a acknowledgement if received or not in the response queue
Msg.AcknowledgeType = AcknowledgeTypes.FullReceive;
Msg.AdministrationQueue = new MessageQueue(ResponseMessageQueuePath);
// send the message
MQueue.Send(Msg);
// close the mesage queue
MQueue.Close();


The message object itself provides you with a lot of control about the message you are sending. You can set a user friendly label with the Label property. You can set the priority of the message with the Priority property using a value of the MessagePriority enumeration. If you want the message to be placed in a journal when received/read then set the UseJournal property on the message to true. The AcknowledgeType property provides you control over which acknowledgement you require using a value of the AcknowledgeTypes enumeration. For example setting it to FullReceive means you will get an acknowledgement if it has been received/read but also if the message is undeliverable or expired. The acknowledgement is sent to the queue specified by the AdministrationQueue property of the message. Via the TimeToBeReceived property you can specify by when the message needs to be received/read otherwise it will expire and be removed by MSMQ. You don't set an absolute date/time but rather a time span using a TimeSpan object. There are many more properties you can set on the Message object. Please refer to the MSDN documentation for a complete list.

You can use MessageQueueTransaction to start an internal transaction. You create an instance of that class and call BeginTransaction(). For every message you send and want to be part of this transaction you pass along this transaction object in the Send() method. When done you call Commit() or Abort() on the transaction object. The messages you send as part of a transaction will only be available to read by the time you call Commit(). Don't forget to still call Close() on the message queue object. Using a transaction works only with queues which are marked as transactional. Here is a code snippet:

// create a message queue transaction and start it
MessageQueueTransaction Transaction = new MessageQueueTransaction();
Transaction.Begin();
// create a message queue 
MessageQueue MQueue = new MessageQueue(MessageQueuePath);
// create the message and set the base properties
Message Msg = new Message(TheObject);
// send the message as part of the transaction
MQueue.Send(Msg, Transaction);
// commit the transaction
Transaction.Commit();
// close the mesage queue 
MQueue.Close();


Reading a message will automatically remove it from the queue and if the message property UseJournal has been set to true a copy will be placed in the journal. You read messages by calling Receive() on the message queue object. This call is synchronous and blocks till a message is available. An overloaded version of Receive() allows to specify a time span, the time how long the call will wait to receive a message. The Receive() method returns a Message object. The Body property allows you to access the object which was stored in the message when sent. It is important to use the same formatter type when reading and sending the message or otherwise an exception will be thrown. For example, if you used a BinaryMessageFormatter when sending the message then you need to create a BinaryMessageFormatter object and assign it to the Formatter property on the message queue object before you call the Receive() method. The XmlMessageFormatter is not able to recognize which type has been serialized into XML. Therefore you need to set the TargetType property on the XmlMessageFormatter to the type you have stored in the message. Through this the XML formatter knows which type to create and de-serialize out of the message. For performance improvement Receive() reads by default only a handful of properties from the message. Accessing a message property which has not been read throws an InvalidOperationException exception. Before you call Receive() you set on the message queue object through the MessageReadPropertyFilter property which message properties you want to be read. Call MessageReadPropertyFilter.SetAll() if you want to read every property of the message. Here is a code snippet:

 // open the selected message queue
MessageQueue MQueue = new MessageQueue(ListOfMessageQueues.SelectedNode.Name);
MQueue.MessageReadPropertyFilter.SetAll();
// the target type we have stored in the message body
((XmlMessageFormatter)MQueue.Formatter).TargetTypes = new Type[] { typeof(Order) };
try
{
      // read the message from the queue, but only wait for 5 sec
      System.Messaging.Message Msg = MQueue.Receive(new TimeSpan(0, 0, 5));
      // read the order from the message body
      Order Order = (Order)Msg.Body;
      // close the message queue
      MQueue.Close();
}
// no message present to read
catch (MessageQueueException)
{
      MessageBox.Show(this, "There is no message in the queue at this time.");
}


The code snippet specifies a time span for how log to wait for a message. If no message is present within that timeout, then a MessageQueueException exception is thrown. You can also read messages asynchronously. First you register an event handler of the type ReceiveCompletedEventHandler with the property ReceiveCompleted on the message queue object. Then you call on the message queue object instead of Receive() the method BeginReceive(). BeginReceive() will return immediately and the event handler you registered will be called when a message is available for reading. In the event handler you then call EndReceive() which returns the message. When you call BeginReceive() you get an IAsyncResult handler back which you need to pass along when calling EndReceive() so the method knows which asynchronous read to finish. You can call BeginReceive() multiple times. In this case you are waiting for multiple messages and for each available message your event handler gets called. Your event handler stays active, so keeps still waiting for a message, even after calling Close() on the message queue object. This behavior is due to the fact that read and write handles to the message queues are cached for performance reasons. Set the MessageQueue.EnbaleConnectionCache property to false to disables the cache which will make sure that your event handler no longer listens for messages after you close your message queue object. Here is a code snippet for asynchronous reads:

 // it is important to set this to false, otherwise the message receiver event
// handler keeps still active even after closing the message queue object
MessageQueue.EnableConnectionCache = false;
// open the selected message queue
MessageQueue MQueue = new MessageQueue(MessageQueuePath);
MQueue.MessageReadPropertyFilter.SetAll();
// the target types we have stored in the message body
((XmlMessageFormatter)MQueue.Formatter).TargetTypes = new Type[] { typeof(Order) };
// set the event handler to be called when the message has been received
MQueue.ReceiveCompleted += new ReceiveCompletedEventHandler(MessageEventHandler);
// start the receive message process; this call returns immediately
IAsyncResult MQResult = MQueue.BeginReceive(new TimeSpan(1, 0, 0), MQueue);

/// <summary>
/// event handler called when the message is ready to be read
/// </summary>
private void MessageEventHandler(object sender, ReceiveCompletedEventArgs e)
{
     // get the message from the queue
     System.Messaging.Message Msg = 
 ((MessageQueue)e.AsyncResult.AsyncState).
          EndReceive(e.AsyncResult);
     // process the order
     Order Order = (Order)Msg.Body;
     // start looking for the next message
     IAsyncResult AsyncResult = ((MessageQueue)e.AsyncResult.AsyncState).BeginReceive(
            new TimeSpan(1, 0, 0), ((MessageQueue)e.AsyncResult.AsyncState));
}


When you call BeginReceive() you can also pass along some state information. This can be any object and in our code snippet above it is the message queue object. This way the event handler can get hold of the message queue object and then call EndReceive(). If you want to continue waiting for the next message then you need to call in the event handler again BeginReceive() on the same message queue object. BeginReceive() and Receive() always read the first message in the queue and remove it from the queue. You can use Peek() and BeginPeek() with EndPeek() to peek for the first message without removing it. MessageQueue.GetAllMessages() returns you a list of all messages in a queue without removing them from the queue. Here is a code snippet:

// open the message queue
MessageQueue MQueue = new MessageQueue(MessageQueuePath);

// set the properties we want to retrieve
MQueue.MessageReadPropertyFilter.Id = true;
MQueue.MessageReadPropertyFilter.Priority = true;
MQueue.MessageReadPropertyFilter.SentTime = true;
MQueue.MessageReadPropertyFilter.MessageType = true;
MQueue.MessageReadPropertyFilter.Label = true;
// get all the messages; does not remove the messages!
Message[] Msg = MQueue.GetAllMessages();
// create a string array to store the message information
string[,] Messages = new string[Msg.Length,5];
// loop through each message and get the info we need
for (int Count = 0; Count < Msg.Length; Count++)
{
      Messages[Count, 0] = Msg[Count].Id;
      Messages[Count, 1] = Msg[Count].Label;
      Messages[Count, 2] = Msg[Count].MessageType.ToString();
      Messages[Count, 3] = Msg[Count].Priority.ToString();
      Messages[Count, 4] = Msg[Count].SentTime.ToString();
}
// close the message queue
MQueue.Close();


Another way to obtain the list of messages without removing them from a queue is the method MessageQueue.GetMessageEnumerator(). It returns a MessageEnumerator enumerator which you use to move from message to message. If there is no message available it will wait the specified time span. Here is the code snippet:

 // open the message queue
MessageQueue MQueue = new MessageQueue(MessageQueuePath);
// we want to retrieve all properties for the message
MQueue.MessageReadPropertyFilter.SetAll();
// get a message enumerator we can use to go through all the messages
MessageEnumerator Enumerator = MQueue.GetMessageEnumerator();
// loop through all the messages 
while (Enumerator.MoveNext(new TimeSpan(0,0,1)))
{
      // get a reference to the current message
      Message Msg = Enumerator.Current;
}
// close the message queue
MQueue.Close();

The MessageQueue object provides you with a lot of control how to read and send messages. But sometimes it would be useful to get notified when a message has been sent to a queue. You can achieve that by having a windows service running in the background and receiving or peeking synchronously or asynchronously messages. There is another approach you can use, MSMQ triggers.
Summary: 
Message Queuing is a middleware component of the Windows operating system. Your application can use Message Queuing to send messages to another application even if the recipient application is not running or the computer on which the sender or recipient application is running is disconnected from the network. Messages are stored and forwarded by Message Queuing until they reach the destination queue. Later, when a recipient application runs, it can retrieve the messages from the queue. Message Queuing decouples sender and recipient applications so they do not need to run at the same time. Message Queuing provides built-in security, transaction support, and more

Saturday, January 22, 2011

SMO Explained from Scratch to End

SMO Explained from Scratch to End

Introduction:

What is SMO

SMO(SqlServer Management Object) name itself indicated that Object model is responsible for managing Sql Server Programitically like you can create Database Objects dynamically and perform lots common DBA tasks including Backups, Restores,Index Maintenance, Integrity checks and more.. I am going to start series of articles which will guide you how to use SMO Object model in .net world and Let us end up with creating one successful tool like query analyzer of our own using SMO(SqlServer Management Object).
  • How to use Smo Object Model in .NET
  • What Tasks You can perform using SMO(SqlServer Management Object)
  • Create Sql Query Analyzer Like Tool
Before we will discuss anything about SMO,let me clear you one thing ,When ever you are going to use SMO Object Model be sure your Program includes Reference to Microsoft.SqlServer.Management.Smo namespace. Actually there are two ways to do this. The first one is by using SQL-DMO (Distributed Management Objects) which is a set classic classes that were used to programmatically manipulate and interact with SQL Servers, usually it was used before .Net World. With time and arrival of great .NET CLR Platform, lots of new changes were made to SQL-DMO Object model, which was named as SMO[Server Management Objects].True,name itself indicates object model is used to manipulate SqlServer programmatically .So we can say SQL Server Management objects (SMO) exposes the functionality of SQL Server database and replication management. SMO can be used to automate repetitive tasks or commonly performed administrative tasks. The SQL SMO is implemented as a .NET assembly and the model extends and replaces the SQL Server Distributed Management object (DMO) of the earlier versions. A number of enhancements have been made such as partial instantiation, capture mode execution, delegated execution, objects in space and integration with .NET framework. There are certain features that all Server Management objects share such as running methods, setting properties and manipulating collections. Specific tasks can be programmed using SMO objects. These include complex subjects that are required by programs with specialized functions such as backing up, monitoring statistics, replication, managing instance objects and setting configuration options.

SMO Object Model

The SMO object model is a hierarchy of objects with the Server object at the top level and all the instance objects residing within the Server object. The ManagedComputer is the top level class and has a separate object hierarchy. It represents Microsoft SQL Server services and network settings that are available through the WMI provider. A number of utility classes represent tasks such as Transfer, backup or Restore. The Model is made up of several namespaces and uses the System.Data.SqlClient object driver to connect to and communicate with different instances of SQL Server.
The SMO client requires SQL Server Native Client that is part of the SQL Server 2005/2008 and .NET Framework 2.0/above versions too.
Applications in SMO require the installation of Visual Studio 2005/2008.
Transaction processing in SMO is done by using the ServerConnection object which is referenced by the ConnectionContext property of the Server object. Methods such as StartTransaction, RollBackTransaction and CommitTransaction are available to the user.
SMO assembly provides classes categorized in following six namespaces:
Microsoft.SqlServer.Management.SmoContains instance classes and utility classes that are used to programmatically manipulate SQL Server.
Microsoft.SqlServer.Management.Smo.AgentContains classes that represent the SQL Server Agent.
Microsoft.SqlServer.Management.Smo.BrokerContains classes that represent Service Broker.
Microsoft.SqlServer.Management.Smo.MailContains classes that represent SQLiMail.
Microsoft.SqlServer.Management.Smo.RegisteredServerContains classes that represent Registered Server.
Microsoft.SqlServer.Management.Smo.WmiContains classes that represent the WMI Provider.
It is not possible to upgrade a SQL DMO application to SMO. The application will have to be rewritten using SMO classes.
A number of SMO namespaces are not supported by earlier versions of SQL Server. Some of the unsupported namespaces are:
  • Microsoft.SqlServer.Management.NotificationServices
  • Microsoft.SqlServer.Management.Smo.Broker
  • Microsoft.SqlServer.Management.Smo.Mail
  • Microsoft.SqlServer.Management.Smo.RegisteredServer
  • Microsoft.SqlServer.Management.Smo.Wmi
  • Microsoft.SqlServer.Management.Trace
Microsoft.SqlServer.Management.Smo.Agent and Microsoft.SqlServer. Management. SMO namespaces are partially supported. Some classes in Microsoft.SqlServer. Management.Smo provide support for SQL Server 2000 and SQL Server 7.0 while some distributed Management objects have been laid off  in the transition.

Programming SQL Server Management objects

Connecting to SqlServer:

There are three ways to connect to SQL Server
  1. The ServerConnection object variable can be used to provide connection information
  2. The server object property can be set to provide connection information
  3. The name of the SQL Server can be passed in the Server object constructor.
Let us see how to connect programitically 
public void ConnectSQLServer()
        {
            server.ConnectionContext.ServerInstance = ServerAndInstanceName;
            if (this.useWindowsAuthentication)
            {  server.ConnectionContext.LoginSecure = useWindowsAuthentication;
           } 
           else            
             { 
                server.ConnectionContext.LoginSecure = this.useWindowsAuthentication;
                server.ConnectionContext.Login = this.userName;
                server.ConnectionContext.Password = this.password;
            }            
             server.ConnectionContext.Connect();
        }           

Let we See How SMO Views or interacts with  SQL Server  Objects, as you can see from Fig1 that  
Let we See How SMO Views or interacts with  SQL Server  Objects, as you can see from Fig1 that SQL Server contains Objects in Tree View Control,just to show the SMO Object hierarchy like
Server Instance : contains below sub Objects
1.Databases FolderContain all Databases
2.Data Transformation Services Local packages,Meta data service package...
3 Management Sqlserver Agent,Backup,current activity,Sqlserver log, Maintaince plan
4 Replication Publications, Subscriptions
5 Security Logins, Server Roles, Linked Servers, Remote services
6.support servicesSQLMail,Distributed Trans Coordinator
7.Meta data servicesContent
< Seeing Further clasifucation,below figure show how other objects are arranged under Root or Parent Objects,Anyways Concern here is now ,How can we Interact with these objects
Figure 1

Let us list of SQL servers available on the network

public void GetServers() 
{ 
// Get a list of SQL servers available on the network 
DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false); 
foreach (DatRow row in dtServers.Rows) 
{ string sqlServerName = row["Server"].ToString(); 
if  (row["Instance"] != null && row["Instance"].ToString().Length > 0) 
sqlServerName += @"\" + row["Instance" ].ToString();
 } 
}            

Let us Retrieve list of databases

public  List<string>GetDatabaseNameList()
 { 
List<string>dbList = new  List<string>(); 
foreach(Database db in server.Databases) 
dbList.Add(db.Name); 
return dbList; 
}        

Let us Retrieve list of tables within database  using SMO

public void List<string>GetTableNameList(Database db) 
{ 
List<string> tableList =new  List<string>(); 
foreach (Table table in db.Tables) 
tableList.Add(table.Name);
return tableList; 
}         

Let us Retrieve list of StoredProcedures within database  using SMO

public void List<string> GetStoredProcedureNameList(Database db) 
{ 
List<string> storedProcedureNameList = new List<string>(); 
foreach (StoredProcedure storedProcedure in db.StoredProcedures)
 storedProcedureNameList.Add(storedProcedure.Name); 
return storedProcedureNameList;
}             

Let us Retrieve list of views within database  using SMO

public void List<string>GetViewNameList(Database db) 
{ 
List<string> viewNameList = new  List<string>(); 
foreach (View view in db.Views)
 viewNameList.Add(view.Name);
 return viewNameList;
 }           

Let us Retrieve Column Names of Table using SMO

public void List<string>GetColumnNameList(Table table) 
{ 
List<string> columnList = new List<string>(); 
 foreach (Column column in table.Columns)
 columnList.Add(column.Name); 
return columnList; 
}           

Let us Retrieve User Names using SMO

public void List<string>GetUserNameList(Database db)
 { 
List<string>userNameList = new  List<string>(); 
 foreach (User user in db.Users) 
userNameList.Add(user.Name); 
return userNameList;
 }

 I think it is enough for playing with the existing objects of the database. Now most important part of it is how to create our own objects like          database,tables,procedures..etc.

Let we see  one by one how to  create sqlserver database objects likea>

How to Create Table using SMO


Once you are connected to SQL Server 2005 with a SMO connection, you can start manage and create object. Here is an example on how you can create a table with a promary key (this sample is written in C# and use a beta version of SQL Server 2005 and of the .Net Framework 2.0). The Object sqlserver, is the object created after the connection in this post about smo connection
 In order to connect to SQL Server using SMO you have to reference the following namespace (and the corresponding assembly):
using Microsoft.SqlServer.Management. Smo;
using  Microsoft.SqlServer.Management. Smo.Agent;
using  Microsoft.SqlServer.Management. Common;
     Table tbl;
      Column col;
     Index idx;
    tbl = new Table(sqlserver.database, "MyTable");
     col =  new Column(tbl, "MyCol1", DataType.Int);
     tbl.Columns.Add(col);
     col.Nullable =false;                    
    // Add the primary key      index  idx =  new Index(tbl, "PK_MyTable");
      tbl.Indexes.Add(idx); 
idx.IndexedColumns.Add(new IndexedColumn(idx, col.Name));
idx.IsClustered =true;
idx.IsUnique =true;
idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
 col = new Column(tbl, "myCol2", DataType.SmallInt);
tbl.Columns.Add(col);
col.Nullable = false; // Create the table tbl.Create();                       
Transfer a database; all objects and data
Server tgt =    new Server(".");    
// Setup source connection (in this sample source   is .\inst1 and target is '.' (the default instance)
Server svr = new Server(@".\inst1");Database db = svr.Databases["testdb"]; // Setup transferTransfer t = new Transfer(db); t.CopyAllObjects = true; t.DropDestinationObjectsFirst = true; t.CopySchema = true; t.CopyData = true; t.DestinationServer = "."; t.DestinationDatabase = "testdb"; t.Options.IncludeIfNotExists = true; // Do the work t.TransferData();  // Or use ScriptTransfer() if you need to capture the script (without data)

SMO connection

This sample is written in C# and use SQL Server 2005 beta 2 and a beta version of the .Net Framework 2.0. It show how, with SMO you can Connect to SQL Server 2005
In order to connect to SQL Server using SMO you have to reference the following namespace (and the corresponding assembly):
using Microsoft.SqlServer.Management.Smo;
using  Microsoft.SqlServer.Management.Smo.Agent;
using  Microsoft.SqlServer.Management.Common;
 //The following code allow you to connect with the intergrated  security :          
 ServerConnection conn;Server sqlserver;
conn = new ServerConnection();
conn.ServerInstance = "My Server"
 conn.LoginSecure = true conn.Connect();             
sqlserver = new Server(conn);
The object sqlserver is the object on with youe are going to work on
each SMO project (to have to list of database for example).
2) The following code allow you to connect to SQL Server with the SQL
Server authentification :
erverConnection conn;
Server sqlserver;
conn = new ServerConnection();
conn.ServerInstance = "My Server";
 conn.Login = "My login";
 conn.Password = "My Password";
 conn.LoginSecure =true;
 conn.Connect();
 sqlserver = new Server(conn);