SQL Server Database Backups using SMO in C#

In the previous post we looked at the What is SMO and where we can use SMO. Now we will see how we can use SMO for taking SQL Server Database backups in C# code.

The first step in our code is to estblish the connection to the database. In our regular .net applications we will use SqlConnection for connecting to the database server. In SMO APIs we will be using the class called "Server" as shown below:

Microsoft.SqlServer.Management.Smo.Server objSrv = new Server("Database Server Name");

if we are planning to connect to any particular instance, we can even pass the instance name as part of the database server name as:

Microsoft.SqlServer.Management.Smo.Server objSrv = new Server("Database Server Name\\Instance Name");

With this line we will be establishing the connectivity to the database server. To get any information about the server, we can use this object. For example to get the SQL Server edition like Enterprise/Standard/Developer edition, we can use Information property within the Server object as shown below:

String sEdition = objSrv.Information.Edition;

Using this Information class we can get lots of information about the SQL Server like OSVersion, Collation, CaseSensitive, Clustered, Physical Memory and Version etc.

Next step for us is to create the instance of the Backup class which is core part for taking the backup of the database:

Microsoft.SqlServer.Management.Smo.Backup objBkpUp = new Backup();

There is should some option to provide details like Database Name, Backup Options (Database/Log File/Data File). The option is nothing but the Backup class as shown below:

objBkpUp.Action = BackupActionType.Database;
objBkpUp.Database = "MyDataBase";
objBkpUp.MediaName = "FileSystem";

And also we have to provide the info about the Device (File/Tape/Pipe) for taking the backup. For this we will be using the BackupDeviceItem class.

BackupDeviceItem objBkpDeviceItem = new BackupDeviceItem();
objBkpDeviceItem.DeviceType = DeviceType.File;
objBkpDeviceItem.Name = "C:\\filename.bak";

Once device is created we have to add it to the Backup instance as:

objBkpUp.Devices.Add(objBkpDeviceItem);

Finally we are ready to initiate a call for the backup operation by passing the Server instance:

objBkpUp.Initialize = true;
objBkpUp.SqlBackup(objSrv);

Here is the complete code:

Microsoft.SqlServer.Management.Smo.Backup objBkpUp = new Backup();
Microsoft.SqlServer.Management.Smo.Server objSrv = new Server("DBName");


Console.WriteLine("Server Edition: " + objSrv.Information.Edition);

objBkpUp.Action = BackupActionType.Database;
objBkpUp.Database = "MyDataBase";
objBkpUp.MediaName = "FileSystem";

BackupDeviceItem objBkpDeviceItem = new BackupDeviceItem();
objBkpDeviceItem.DeviceType = DeviceType.File;
objBkpDeviceItem.Name = "h:\\zzTest.bak";

objBkpUp.Devices.Add(objBkpDeviceItem);

objBkpUp.Initialize = true;
objBkpUp.SqlBackup(objSrv);

Please note that we have to add the following assembly references:

Microsoft.SqlServer.Smo
Microsoft.SqlServer.ConnectionInfo

and in the code we have to refer following namespace:

using Microsoft.SqlServer.Management.Smo;

What is SMO?

Are we wondering what is this SMO...?

Recently one of our team member asked me a question on who we can write a C# code for taking a SQL Server Database Bakup without using "Backup Database" sql command which made me to write this post.

SMO is Sql Server Management Objects providing a set of APIs for managing SQL Server.
In a short note:

"It is a collection of objects that are designed for programming all the aspects of Managing the Microsoft SQL Server"

And also we can say that SMO is a replacement of SQL-DMO(SQL Server Distributed Management Objects). SMO design goal is to mainly improve Scalability and Performance when compared to the SQL-DMO which is available with SQL Server 2000. SMO is relaced with SQL Server 2005 and even it supports SQL Server 2000 as well.

We can use SMO to perform any of the following administrative operations on the SQL Server:

Create databases

Perform backups

Create jobs

Configure SQL Server

Assign permissions

and to perform many other administrative tasks.

Following are the set of Assemblies available for the programming:

Microsoft.SqlServer.management

Microsoft.SqlServer.Management.NotificationServices

Microsoft.SqlServer.Management.Smo

Microsoft.SqlServer.Management.Smo.Agent

Microsoft.SqlServer.Management.Smo.Broker

Microsoft.SqlServer.Management.Smo.Mail

Microsoft.SqlServer.Management.Smo.RegisteredServers

Microsoft.SqlServer.Management.Smo.Wmi

Microsoft.SqlServer.Management.Trace namespaces

In the comming post I will explain few set of Objects and how can we write simple program in C# to backup a particular database using SMO APIs.

How to get deep nested elements in SQL Queries using FOR XML PATH

Recently in one of the forums I came across this question.

How can we have deep nested elements in the xml when we are using FOR XML PATH in a SQL Query.

For example the following query

SELECT 'value' AS 'orderdatabase/allorders/customer/information/orderaddress/shippingaddress/address1/home/address1/blahblahblahblahblahblahblahblah'
FOR XML PATH('')

will give an error indicating that the identifier is too long and it should not exceed 128 characters. But the output expected from the query is:

<root>
 <orderdatabase>
  <allorders>
   <customer>
    <information>
     <orderaddress>
      <shippingaddress>
       <address1>
        <home>
         <address1>
          <blahblahblahblahblahblahblahblah>value</blahblahblahblahblahblahblahblah>
         </address1>
        </home>
       </address1>
      </shippingaddress>
     </orderaddress>
    </information>
   </customer>
  </allorders>
 </orderdatabase>
</root>

In most of the scenarios in our real world code this might be required. We can resolve this with a workaround using sub queries which will return an xml which is subset of the parent xml. Following is the sample query to resolve the above issue:

SELECT '' 'orderdatabase/allorders/customer/information/orderaddress',
(
SELECT 'value' 'shippingaddress/address1/home/address1/blahblahblahblahblahblahblahblah'
FOR XML PATH(''), type
) AS 'orderdatabase/allorders/customer/information/orderaddress'
FOR XML PATH(''),root('root')

We can refer any of the sql table columns using {sql:column("column name")}.

This is a simple tip/work around for long nested xml's from a query.

How to use Multiple DataReaders in ADO.Net

Most of the folks says that we cannot use one more DataReader when one DataReader is Opened. It is obsolutely correct upto ADO.Net 1.0 version.

With ADO.Net 2.0 we can create DataReader while another DataReader is opened on the same SqlConnection.

Please note that the second DataReader should use the same SqlConnection.
Following is the sample to demonstrate the same (in C#):

static void Main(string[] args)
{

SqlConnection objCon = null;
try
{

string sConString = "Data Source=.\\sqlexpress;Initial Catalog=DB1;Integrated Security=True;MultipleActiveResultSets=true";

SqlDataReader dr1 = null, dr2 = null;

objCon = new SqlConnection();
objCon.ConnectionString = sConString;

SqlCommand objCmd = new SqlCommand();
objCmd.CommandText = "select top 10 * from Page";
objCmd.Connection = objCon;

SqlCommand objCmd1 = new SqlCommand();
objCmd1.Connection = objCon;

objCon.Open();

dr1 = objCmd.ExecuteReader();
while (dr1.Read())
{
Console.WriteLine(dr1.GetInt32(0).ToString());

objCmd1.CommandText = "select top 10 * from PageData Where PageId=" + dr1.GetInt32(0).ToString();

// Creating the Second DataReader
dr2 = objCmd1.ExecuteReader();

while (dr2.Read())
{
Console.WriteLine(dr2.GetInt32(1));
}

// Closing the Data Reader
dr2.Close();
}
// Closing the Data Reader
dr1.Close();

}
catch (Exception objExp)
{
Console.WriteLine("Exception: " + objExp.Message);
}
finally
{
if (objCon != null)
{
objCon.Close();
}
}
}

Please note that in the Connection String we have to add "MultipleActiveResultSets=true" for allowing mutiple DataReaders on the same Connection.

Fusion Charts for SharePoint List

Most of the time when ever we have data, end users will expect some kind of reports/charts.

For example when we are using tasks, we may have to provide a pie chart indicating the different status in the pie chart. Or We may have to show a visual indicator for the percentage completion for each task item.

We can create the charts by using any of the tools on MOSS 2007:

1. Excel Services (Excel Web Access)
2. Reporting Services
3. Silver Light, etc

In order to use these features we should have MOSS 2007 License and also some environments there will be restrictions on custom coding. By keeping this points in mind I came up with this Post.

Here we will see how we can utilize the Fusion Charts for the SharePoint List (Task List). Following image shows the sample Task List:



Following are the steps to implement the fusion charts:
1. Download Fusion Charts and upload to a document library. In this case I created a separate document library called FusionCharts where all the fusion charts are uploaded.
2. Come up with the RPC Protocol URL for extracting the data from the task list.
In our case following is the URL:

http://localhost/sites/MOSSDemos/_vti_bin/owssvr.dll?Cmd=Display&Query=Status&List=%7B7C6C7072%2D12B5%2D4B05%2D90A4%2D052160CC8F74%7D&XMLDATA=TRUE


Note that we are extracting only the Status field from the Task List.
And the List id is the Task List Guiid within the SharePoint Site.

3. Copy the following javascript into the content editor web part:

<script language="javascript">

function fnXmlHttp(AspFileName,strXML)
{
var objXMLHttp= new ActiveXObject("Microsoft.XMLHTTP");
var sResponse;
try
{
objXMLHttp.open("POST",AspFileName,false) //POSTING THE DATA
objXMLHttp.setRequestHeader("Content-Type", "text/xml");
objXMLHttp.send(strXML)
}
catch(e)
{
}
if (parseInt(objXMLHttp.Status) != 200)
{
sResponse = "N,Cannot connect to the Server.";
}
else
{
sResponse=new String(objXMLHttp.responseText);
if (sResponse == "")
{
sResponse = "N,Error at server. Please contact administrator.";
}
}
return sResponse;

}


var sXML;
var bLoad;

var sSiteUrl = "http://localhost/sites/MOSSDemos/_vti_bin/owssvr.dll?";

// This Parameter Contains List Id too
var sCmdParameters = "Cmd=Display&Query=Status&List=%7B7C6C7072%2D12B5%2D4B05%2D90A4%2D052160CC8F74%7D&XMLDATA=TRUE"

// Request the RPC Call
sXML = fnXmlHttp(sSiteUrl + sCmdParameters,"");

// Create the XML Object Instance
var objXML = new ActiveXObject("MSXML2.DOMDocument");

// Load the XML
bLoad = objXML.loadXML(sXML);

// Check whether the XML is loaded into the document
if(bLoad)
{
// Declare all local variables
var iNotStarted = 0;
var iInProgress = 0;
var iCompleted = 0;
var iDeferred = 0;
var iWaitingonsomeoneelse = 0;
var sObjectData;

var objData = objXML.documentElement.childNodes(1);

// Loop through all the variables
for (i=0;i<objData.childNodes.length;i++)
{
var Status = objData.childNodes(i).attributes.getNamedItem("ows_Status").value;

switch(Status)
{
case "Not Started":
iNotStarted = iNotStarted + 1; break;
case "In Progress":
iInProgress = iInProgress + 1; break;
case "Completed":
iCompleted = iCompleted + 1; break;
case "Deferred":
iDeferred = iDeferred + 1; break;
case "Waiting on someone else":
iWaitingonsomeoneelse = iWaitingonsomeoneelse + 1; break;
default:
break;
}

}

// Create the Object for the Fusion Chart
// Opening Object Tag
sObjectData = "<object classid=\"clsid:D27CDB6E-AE6D-11cf-96B8-444553540000\" ";
sObjectData += "codebase=\"http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0\" ";
sObjectData += "width=\"400\" ";
sObjectData += "height=\"300\" ";
sObjectData += "id=\"Pie3D\"> ";

// Setting Parameters
sObjectData += "<param name=\"movie\" value=\"http://localhost/sites/MOSSDemos/FusionCharts/FusionCharts/FCF_Pie3D.swf?chartWidth=400&chartHeight=300\"> ";
sObjectData += "<param name=\"FlashVars\" value=\"&dataXML=<graph caption='Tasks By Status Percentage' ";
sObjectData += "xAxisName='Site' yAxisName='Qty by Site' showNames='1' decimalPrecision='1' showPercentageInLabel='1' formatNumberScale='0' ";
sObjectData += "pieYScale='70' pieBorderAlpha='40' pieFillAlpha='80' pieSliceDepth='15'> ";
sObjectData += "<set name='Not Started' value='" + iNotStarted + "' color='#660033' link='' /> ";
sObjectData += "<set name='Completed' value='" + iInProgress + "' color='#3399ff' link='' /> ";
sObjectData += "<set name='In Progress' value='" + iCompleted + "' color='#99cc99' link='' /> ";
sObjectData += "<set name='Deferred' value='" + iDeferred + "' color='#00ff99' link='' /> ";
sObjectData += "<set name='Waiting for someone else' value='" + iWaitingonsomeoneelse + "' color='#000033' link='' /> ";
sObjectData += "</graph>\"> ";
sObjectData += "<param name=\"quality\" value=\"high\"> ";
sObjectData += "</object> ";
document.write(sObjectData);
}


</script>

Following are the lines to be changed in the script:
A. Location of the FCF_Pie3D.swf file within the "object" tag.
B. Site Url in the variable "sSiteUrl".
C. Task List Id in the variable "sCmdParameters".

With this, A Pie chart representations the Task Data within the SharePoint List as shown below:

SharePoint List Data Access

In this post we will see what are the different ways that we extract the data from the SharePoint List at a higher and this will be the basic for the future posts. List can be a Custom List, Document Library, or Survey.

Following are the different ways of accessing data from a list at a broad level:

1. Using Object Model

2. Using Web Services

3. Using Remote Procedure Calls

SharePoint Object Model
There are rich set of APIs for accessing the SharePoint List. Most of the times we will be using following objects:

A. SPSite -> Represents Site Collection
B. SPWeb -> Represents a Web (which is a sub site)
C. SPList -> Represents a List (Can be a Custom List/Document Library etc)
D. SPListItem -> Represents an Item in a List

Apart from these classes we may be using SPFolder, SPFile, etc classes too.
More info is available @ SharePoint Object Model

Please note that we will be using the SharePoint Object in the custom applications/Web Parts which will be hosting on the Same SharePoint Servers.

Web Services
The other approach of accessing the data using sharepoint web services.
For example we can use Lists.asmx for accessing the data from a List.

Like this we have lots of web services for accessing the data from SharePoint. You can get list of web services @ List of SharePoint Web Services

Since it is a web service, we can use in any custom applications and it can be hosted on SharePoint Server or any other server.

Using Remote Procedure Calls
From SharePoint 2003, we have one more approach for accesing the sharepoint data using RPC Protocal.
Following is the syntax:

http://Server_Name/[sites/][Site_Name/]_vti_bin/owssvr.dll?Cmd=
Method_name[&Parameter1=Value1&Parameter2=Value2...]

Here we are just providing the method and parameters to query the sharepoint database. For example if we want to access the sharepoint task list we can provide the following URL:

http://localhost/sites/DemoSite/_vti_bin/owssvr.dll?Cmd=Display&List=%7B2E5E526D%2D993C%2D45B9%2DAF0F%2DDE71F8985220%7D&XMLDATA=TRUE&Query=*

In the url, we are specifying the following parameters:

Method Name: Display -> For fetching the data
Parameter: List -> For providing the List Id (GUID)
Parameter: XMLDATA -> For extracting data in the xml format
Parameter: Query -> To provide list of column names separated by the space. * indicates all the columns.

You can get complete list of commands and their parameters @ URL Protocol

This post is the basics for the upcoming post on Displaying Data in a graphical format as shown below:

Custom XML from a Query

Most of us knows that SQL Server 2000 supports following xml clause:

1. FOR XML AUTO
2. FOR XML RAW
3. FOR XML EXPLICIT

Using any of these we can get the xml output from a query. We will be using FOR XML Explicit for getting the custom format of the xml, but it is little difficult in writing the query where we have to use set operator "UNION" for the set of queries.

Today one of my team member asked a question where he is looking for an xml from the Excel file and he said it is a one time implementation with the custom format. I thought of sharing with everyone through my blog:

Following is the Excel format:

Following is the output format they are looking for:
<root>
<sup id="101" name="101_Supplier">
<site id="S101">S101_Name</site>
<site id="S102">S102_Name</site>
<site id="S103">S103_Name</site>
</sup>
<sup id="102" name="102_Supplier">
<site id="S102">S102_Name</site>
<site id="S103">S103_Name</site>
<site id="S105">S105_Name</site>
</sup>
</root>

If we observer the xml format it is not a striaght farword, because each suplier has one to many relationship. We can achive it using FOR XML Path in SQL Server 2005 onwards. Here are the steps to follow:

We can create the table in sql server with the same format mentioned in the excel. Here is the sample script after loading the data into the sql server table using export import wizard:

CREATE TABLE [dbo].[SUPDATA](
[SUPPLIER_ID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUPPLIER_NAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SITE_DIVISION_ID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SITE_DIVISION_NAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Following is the query to get the data in the required xml format:

SELECT
SUPPLIER_ID '@id',
SUPPLIER_NAME '@name',
(
SELECT SITE_DIVISION_ID 'site/@id',
SITE_DIVISION_NAME 'site'
FROM SUPDATA I
WHERE I.SUPPLIER_ID = O.SUPPLIER_ID
FOR XML PATH (''), type
)

FROM
(SELECT DISTINCT SUPPLIER_ID, SUPPLIER_NAME FROM dbo.SUPDATA) O
FOR XML path('sup'),root('root')

If you observe carefully in the query, we used type keyword following FOR XML PATH to make sure that the inner query returns xml and should be available without any tag encryption.

This type of queries helps us in converting data into the required xml formats.

SharePoint 2010 Silent Features

In continuation to my previous post on SharePoint 2010 sneak preview.

SharePoint 2010 is the business collobaration platform for the web enterprise that enables to connect and empower people through an integrated set of rich features.

Following are the few silent features from SharePoint 2010 version which I obeserved from the sneak preview. I will be explaining these features and how we can utilize them in our regular job by comparing with the existing version - SharePoint 2007.

Web Edit

This is an excellent feature where the content authors can modify the content inline on the page much quickly and easily when compared to SharePoint 2007. They can also view the preview immediately while changing the fonts or font size...

New User Interface

Now you can find the completely new user interface very much similar to the Office 2007, nothing but the Ribons. It has a contextual interface which makes the user to access the content with very less number of clicks and better performance.

User inteface is integrated with the Asynchronous access. Now its easy for the users to act easily on mutiple items in a single shot. For example when we want to checkout multiple files in a document library, we have to do those many number of time. Now we can do it in a single shot by selecting mutiple files in a document library.

Even you can see the revamped UI for the Central Administration. This makes the administrator to complete their job quickly and easily...

Seamless Image Uploading

In SharePoint 2007, when ever we want to show an image on a page, we will upload the image to a Picture Library and then we will provide the image link on the page. Now we can keep a pull stop for the long way doing this activity... We can seemlessly upload the images while we are editing the content using the Web Edit feature. You can resize the image with in the same browser.

Rich Themes

Apart from the out of the box themes, now you can we import the themes from the office. For example if you want to import the theme from a powerpoint, yes you can import the theme(colors, fonts, etc) to your sharepoint site with same look and feel across all the browsers.

Visio Web Access

In SharePoint 2007 we have a feature of publishing the Excell directly on to a web. Users can access the excell without having any dependency of excell installation on the client machine, which is great feature. Now in SharePoint 2010 Microsoft extended the similar concept to Visio. Using Visio 2010 we can publish the Visio diagrams onto the SharePoint Site and the users can directly access the same using the browser without any installation on the client machine.

BDC -> BCS

Now Business Data Catalog is Business Connectivity Services. Using BCS you can read and write data directly from your Line of Business Data. We can use either SharePoint Designer or Visual Studio to implement the same.

BCS has connectivity to LOB, Web Services, Databases and display the data in a friendly way enabling users to interact and update it easily on the web, offcourse on office client too.

Silverlight WebPart

Now Silverlight web part is available out of the box.

SharePoint Workspace

Most of the times we want to do the work offline on a list of items and then sync with the sharepoint list. We dont have any other option than going with an Excel. Now with SharePoint Workspace, we can take the sharepoint data offline to work and sync whenever hook to the network.

Usage Reports

There is a central database in the SharePoint Server farm to log usage data and also the health data. This provide us with different varities of usage reports which is not available in current version.

In a summary, SharePoint 2010 has a set of rich fetures for all different categories of users like administrators, IT Professions, Developres and End Users.

Thanks,
-Sri

Sneak Preview on MOSS 2010

There were few videos on MOSS 2010 for categories like

1. MOSS 2010 Overview
2. MOSS 2010 for IT Professionals
3. MOSS 2010 for IT Developers

Have a look at it...