There can be cases in mapping development when the developer needs to map some fields conditionally. More exactly, let say that there is need to map in the following situation: if a source field has a value, then a second source field must be mapped to a destination field, else, a third source field must be mapped to the destination field:

IF(SourceField1 == Value1) THEN

            DestinationField1 = SourceField2

ELSE [IF(SourceField1 == Value2)]

DestinationField1 = SourceField3

There are several solution to do this mapping (for example, using a Script functiod). In this example, we will do that using ValueMapping functoid.

Let’s consider the following example: we have a Biztalk application which simply receive a flat file, disassemble it according to a receive schema, perform a mapping as described above, assemble it according to a send schema and output the resulting file to a send port.

The input file is:

Receive schema is:

Tag field if the first character from record (in our example, A or B), Data1 is testdata1_1..5 and Data2 is testdata2_1..5

Send schema is:

And map is:

(of course, is a very simplified map and schemas).

The logic of the mapping is:

IF(Tag == “A”) THEN

            Dest.Data1 = Source.Data1

ELSE IF(SourceField2 == “B”)

Data2 = Source.Data2

            In Equal functoids, we have 2 comparisons which return true or false:

and

The ValueMapping functoids functions as follows: if the first parameter is true, then second parameter value is returned and is mapped to destination fields. Else, no value is returned, and destination field does not receive anything.

Thus, in our example, for the fields which starts with “A”, Data1 value will be mapped (testdata1_1..5), else, Data2 field will be mapped(testdata2_1..5).

According to these, we obtain output file:

We can see that, for records 1, 2, and 4 from source file, that start with “A” (Atestdata1_1testdata2_1, AtestData1_2testdata2_2, Atestdata1_4testdata2_4) we have Data1 fields in destination file (testdata1_1, testdata1_2, testdata1_4) and for records 3 and 5, that start with “B” (Btestdata1_3testdata2_3, Btestdata1_5testdata2_5), we have Data2 in destination file (testdata2_3, testdata2_5)

ValueMapping.rar (14.41 kb)

SWIFT is a standard which describe acollection of messages formats. There are a lot of financial applications whichworks with financial transactions, and, often, these applications must communicatebetween them. Because each of them worked with certain formats, there washarder and harder to maintain a good transmission of information, and requireda lot of time and effort.

Because of that, SWIFT standard wasreleased to describe how the messages should be structured and how to exposethe information, in order to be properly interpreted, to be easier tomanipulate and, thanks God, make developer life easier. [More...

In this article we will present a sample about how to use late binding in .NET using System.Reflection namespace and System.Type class.

What is Late Binding? – is a mechanism which allow to instantiate types and accessing members of objects created without knowing these types at compile time. Shortly, these types are not referred “classically”, through dot and new operators, but at runtime, acquiring information about structure type, instantiating objects and invoking its members. All of these without referring that type at all (anyway, we cannot do that, given that we did not include information about that type at compiling time.). All we need is to know structure of used type, more exactly, knowing what methods and properties we need to use.

This approach can be very useful in a lot of cases, due of these capabilities to manipulate objects at runtime, while they are unknown at compile time; this fact help us to provide a much more flexibility to our applications when interacting with others application and/or third-party software (such as COM).

Example – in the following example, we will manipulate objects from a .NET assembly using reflection mechanisms. In this assembly we have class Employee, which exposes some members and methods, and class EmployeeMethods, which exposes a collection of Employee objects and some methods which works on this collection. These 2 types (classes) will be used from main application (a website) without referring it at developing type, but operating on it at execution time. The referred assembly is stored on disk, and its location is given in web.config (you should copy it manually in this location).

Here is the code:

Code from assembly Objects.LateBinding.dll

using System;

using System.Collections.Generic;

namespace Objects.LateBinding

{

       public class Employee

       {

              public string EmployeeName = "";

              public int EmployeeAge = 0;

              public string EmployeeFunction = "";

              public Employee(string name, int age, string function)

              {

                     EmployeeName = name;

                     EmployeeAge = age;

                     EmployeeFunction = function;

              }

              public string PresentEmployee()

              {

                     return string.Format("Hi, I am {0}, {1} years old and I work as {2}", EmployeeName,EmployeeAge, EmployeeFunction);

              }

       }

       public class EmployeeMethods

       {

              public List<Employee> Employees;

              public void Init()

              {

                     //in real scenarios, data will be loaded from various sources

                     Employees = new List<Employee>();

                     Employees.Add(new Employee("John", 26, "Programmer"));

                     Employees.Add(new Employee("Joe", 30, "Web developer"));

                     Employees.Add(new Employee("Jim", 40, "Software architect"));

              }

              public Employee GetEmployee(int i)

              {

                     return Employees[i];

              }

              public int GetEmployeesCount()

              {

                     return Employees.Count;

              }

              public void AddEmployee(string name, int age, string function)

              {

                     Employees.Add(new Employee(name, age, function));

              }

       }

}

Code from Default.aspx.cs from main application:

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Reflection;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page

{

       Assembly Asmb = null;

       Type EmployeeMethods = null;

       Type EmployeeType = null;

       object EmpMethods = null;

    protected void Page_Load(object sender, EventArgs e)

    {

              InitTypes();

              if (!IsPostBack)

              {

                     InitEmployees();

                     PresentEmployees();

              }

    }

       private void InitTypes()

       {

              //get assembly with Employee classes

              Asmb = Assembly.LoadFrom(System.Configuration.ConfigurationManager.AppSettings["TestAssembly"]);

              //get EmployeeMethods type information

              EmployeeMethods = Asmb.GetType("Objects.LateBinding.EmployeeMethods");

              EmpMethods = Activator.CreateInstance(EmployeeMethods);

       }

       private void InitEmployees()

       {

              //invoke Init method on EmployeeMethods to init employee collection

              MethodInfo mtdInfo = EmployeeMethods.GetMethod("Init");

              mtdInfo.Invoke(EmpMethods, null);

       }

       private void PresentEmployees()

       {

              //get employees count from EmployeeMethods object created earlier

              MethodInfo mtdInfoEmpCount = EmployeeMethods.GetMethod("GetEmployeesCount");

              object employeesCount = mtdInfoEmpCount.Invoke(EmpMethods, null);

              int emplCount = (int)employeesCount;

              //get each employee object

              for (int i = 0; i < emplCount; i++ )

              {

                     object[] parEmpl = new object[] { i };

                     MethodInfo mtdInfoEmp = EmployeeMethods.GetMethod("GetEmployee");

                     object employee = mtdInfoEmp.Invoke(EmpMethods, parEmpl);//get employee

                     //invoke PresentEmployee method for current employee

                     EmployeeType = employee.GetType();

                     MethodInfo mtdInfoEmpPresent = EmployeeType.GetMethod("PresentEmployee");

                     object employeePresent = mtdInfoEmpPresent.Invoke(employee, null);

                     //display the 'response' of the method

                     Response.Write(employeePresent.ToString() + "<br/>");

              }

       }

       private void AddEmployee(string Name, int Age, string Function)

       {

              //Add employee with values from interface

              object[] parEmpl = new object[] { Name, Age, Function };

              MethodInfo mtdInfoEmpAdd = EmployeeMethods.GetMethod("AddEmployee");

              mtdInfoEmpAdd.Invoke(EmpMethods, parEmpl);

       }

       protected void btnAddEmployee_Click(object sender, EventArgs e)

       {

              InitEmployees();

              AddEmployee(txtName.Text, int.Parse(txtAge.Text), txtFunction.Text);

              PresentEmployees();

       }

}

Code from Default.aspx:

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Late binding</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

              <table>

                     <tr>

                           <td colspan="2"><b>Add an employee:</b></td>

                     </tr>

                     <tr>

                           <td>Name:</td>

                           <td><asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>

                     </tr>

                     <tr>

                           <td>Age:</td>

                           <td><asp:TextBox ID="txtAge" runat="server"></asp:TextBox></td>

                     </tr>

                     <tr>

                           <td>Function:</td>

                           <td><asp:TextBox ID="txtFunction" runat="server"></asp:TextBox></td>

                     </tr>

                     <tr>

                           <td colspan="2"><asp:Button ID="btnAddEmployee" runat="server" OnClick="btnAddEmployee_Click" Text="Add employee"/></td>

                     </tr>

              </table>

    </div>

    </form>

</body>

</html>

In this example we list some employees, hardcoded, and add a new one through web interface, using only reflection mechanisms.

Note: Code is not optimized, and no validation was made, since is not point of interest in this article.

Prj.rar (7.70 kb)

Introduction

Each ASP.NET developer has need, at least once, during developing of one or more applications, to create custom functionalities and or design features, related to application purpose. Indeed ASP.NET provides a very wide category of these controls, covering most of functionalities and developers tastes. But what if a developer needs an extra functionality, which ASP.NET controls cannot offer it? Or maybe somebody else wants to be original and create own controls, to reuse them each time need?

An answer could be: create a custom control. ASP.NET provides a mechanism which allows creating custom controls, as the developer defines it. In this way, developer can define control structure, properties, its representation, giving a shape for an object that control represents.

Custom controls, as a server control, can have 2 representations: one, in aspx (or ascx) file from application, which describes the structure of the control, and other in code file (C#, VB etc.) which describes the functionality of the control. For example, a classic ASP.NET server control, Label, has associated a class in ASP.NET libraries, which defines the functionality of the control (members, properties, behavior such as rendering mode etc.) and a representation in aspx files from application, where the class is instantiated in object, as they are defined in that page:

<asp:label ID="lblTest" Runat="server" Text="This is a test label"></asp:label>

This control is instantiated at runtime in an object of type Label and will generate a HTML control:

<span id="lblTest">This is a test label</span>,

and sent to the web browser, which will display.

The question is: how is generated a Label server control? Or, generally, how is generated every asp.net control in one or more Html controls, to be sent to the web browser which made the request. Let’s consider, for example a DataGrid control: a complex ASP.NET control, with numerous modalities for defining data displaying, which will be rendered as a HTML table control, containing a lot of simple HTML controls, such as buttons, textboxes, images etc, calls of javascript code etc., depending of its defining structure from aspx files. How functions this rendering mechanism, from a control to another?

The aspx definition of an ASP.NET control is made in a markup language, having an xml structure. Thus, the control definition is a collection of tags. The root tag is the definition of the control itself, while the children tags will provide information about control properties, children controls and so on (control metadata).

Each ASP.NET control has associated a control builder class. This class describes the way the control will be rendered. When the control starts to be parsed, the control builder class provides the necessary information about how will be interpreted the content of the control. Each tag will be interpreted in a form or another, depending of tag type. For example, <asp:ListItem> tag of a <asp:DropDownList> control will generate a option for DropDownListControl:

            If (Tag = “ListItem”) Then

                        AddOptionToControl

Example

To take a closer look to this mechanism, let’ create a custom control, a very simple tree. Our control can be defined as a tree structure, and will print some text on the screen. (Obviously, our tree is almost useless in real applications; it will be defined here just to demonstrate how custom controls can be implemented).

Here is the markup language definition of the SampleTree control

&lt;st:SampleTree ID="stWorld" runat="server" Text="Earth"&gt;

      &lt;Level1 Name="Europe"&gt;

            &lt;Level2 text="France"&gt;&lt;/Level2&gt;

            &lt;Level2 text="Great Britain"&gt;&lt;/Level2&gt;

            &lt;Level2 text="Romania"&gt;&lt;/Level2&gt;

      &lt;/Level1&gt;

      &lt;Level1 Name="Africa"&gt;

            &lt;Level2 text="Senegal"&gt;&lt;/Level2&gt;

            &lt;Level2 text="Maroc"&gt;&lt;/Level2&gt;

            &lt;Level2 text="Somalia"&gt;&lt;/Level2&gt;

      &lt;/Level1&gt;

      &lt;Level1 Name="Asia"&gt;

            &lt;Level2 text="Japan"&gt;&lt;/Level2&gt;

            &lt;Level2 text="China"&gt;&lt;/Level2&gt;

      &lt;/Level1&gt;

&lt;/st:SampleTree&gt;

As can be seen the tree has two levels, and has a property, Name (for the first level) or Text (for second level). When will be rendered, tree will generate the output

Let see the control definition:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.ComponentModel;

using System.Collections;

namespace STControl

{

       public class SampleTreeControlBuilder : ControlBuilder

       {

              public override Type GetChildControlType(string tagName, IDictionary attributes)

              {

                     if (tagName.ToLower() == "level1")

                           return typeof(TreeLevel1);

                     return null;

              }

       }

       [ToolboxData("&lt;{0}:SampleTree runat=server&gt;&lt;/{0}:SampleTree&gt;")]

       [ControlBuilder(typeof(SampleTreeControlBuilder))]

       [ParseChildren(false)]

       public class SampleTree : WebControl

       {

              private List&lt;TreeLevel1&gt; level1Collection = new List&lt;TreeLevel1&gt;();

              public List&lt;TreeLevel1&gt; Level1Collection

              {

                     get

                     {

                           return level1Collection;

                     }

                     set

                     {

                           level1Collection = value;

                     }

              }

              private string text;

              public string Text

              {

                     get {

                           return text;

                     }

                     set {

                           text = value;

                     }

              }

              protected override void AddParsedSubObject(object obj)

              {

                     if (obj is TreeLevel1)

                           this.level1Collection.Add((TreeLevel1)obj);

              }

              protected override void CreateChildControls()

              {

                     this.Controls.Clear();

                     HtmlGenericControl divTree = new HtmlGenericControl("div");

                     Label lblTreeText = new Label();

                     lblTreeText.Text = this.Text;

                     divTree.Controls.Add(lblTreeText);

                     foreach (TreeLevel1 level1 in this.level1Collection)

                     {

                            HtmlGenericControl divGroup = new HtmlGenericControl("div");

                           Label lblGroup = new Label();

                           lblGroup.Text = "___" + level1.Name;

                           divGroup.Controls.Add(lblGroup);

                           foreach (TreeLevel2 level2 in level1.Level2Collection)

                           {

                                  HtmlGenericControl divProp = new HtmlGenericControl("div");

                                  Label lblProp = new Label();

                                  lblProp.Text = "______" + level2.Text.ToString();

                                  divProp.Controls.Add(lblProp);

                                  divGroup.Controls.Add(divProp);

                           }

                           divTree.Controls.Add(divGroup);

                     }

                     this.Controls.Add(divTree);

              }

       }

       public class TreeLevel1ControlBuilder : ControlBuilder

       {

              public override Type GetChildControlType(string tagName, IDictionary attributes)

              {

                     if (tagName.ToLower() == "level2")

                           return typeof(TreeLevel2);

                     return null;

              }

       }

       [ControlBuilder(typeof(TreeLevel1ControlBuilder))]

       [ParseChildren(false)]

       public class TreeLevel1 : WebControl

       {

              private List&lt;TreeLevel2&gt; level2Collection = new List&lt;TreeLevel2&gt;();

              public List&lt;TreeLevel2&gt; Level2Collection

              {

                     get

                     {

                           return level2Collection;

                     }

                     set

                     {

                           level2Collection = value;

                     }

              }

              private string name;

              public string Name

              {

                     get

                     {

                           return this.name;

                     }

                     set

                     {

                           this.name = value;

                     }

              }

              protected override void AddParsedSubObject(object obj)

              {

                     if (obj is TreeLevel2)

                           this.level2Collection.Add((TreeLevel2)obj);

              }

       }

       public enum LevelType

       {

              Text = 1,

              Collection = 2

       }

       public class TreeLevel2 : WebControl

       {

              private LevelType type;

              public LevelType Type

              {

                     get {

                           return this.type;

                     }

                     set {

                           this.type = value;

                     }

              }

              private string text;

              public string Text

              {

                     get

                     {

                           return this.text;

                     }

                     set

                     {

                           this.text = value;

                     }

              }

       }

}

The main class is SampleTree, which is derived by WebControl. Of course, our functionality is based on WebControl functionality; we just add or modify some things in some things in behavior of control generation. This class has following attributes:

[ToolboxData("&lt;{0}:SampleTree runat=server&gt;&lt;/{0}:SampleTree&gt;")]

[ControlBuilder(typeof(SampleTreeControlBuilder))]

[ParseChildren(false)]

ToolboxData specifies which text will be added in aspx files when control will be added by drag and drop from toolbar. This attribute has no role in control generation.

ParseChildren attribute, when true, specifies that child tags must be map with class properties. When false (as our case), this map will not be made; thus, a custom mapping occurs, provided by associated ControlBuilder class.

ControlBuilder is a very important attribute for customization of rendering control. It specifies name of the class which will be used to render the control structure. In this way we will override the default control builder class associated with WebControl class; this thing allows us to provide our tags and associate them with specific classes

In SampleTreeControlBuilder, delegated to define parsing rules, we have overridden method GetChildControlType. This method is automatically called for every child tag encountered in control definition and gets as parameter the name of the tag:

public override Type GetChildControlType(string tagName, IDictionary attributes)

{

       if (tagName.ToLower() == "level1")

              return typeof(TreeLevel1);

       return null;

}

In this function, we specified that, if name of the tag is “Level1”, return TreeLevel1 type, which is a class defined below in the code. In this way, we defined the rule which sais that child tag &gt;Level1&lt; will be associated with an object of type TreeLevel1. If any other child tag occurs in control definition, it will not be recognized and will be ignored.

What happened next? If the tag is a “known” one, will be added to a list in the class:

protected override void AddParsedSubObject(object obj)

{

       if (obj is TreeLevel1)

              this.level1Collection.Add((TreeLevel1)obj);

}

In this way we will parse all the tags and add them as children objects in parent control. This mechanism can be applied recursively, allows us to define as many levels as we want (in our control, Level2 is processed in a similar way).

In the end, all we have to do is to transform children objects in controls, for any level from control definition. We will do that overriding function CreateChildControls(), generating the control with expected content and purpose.

CustomTree.rar (23.74 kb)

Introduction

There are situations, in web applications, when is need to create a backup and/or restore for a database. For example, application administrator wants to “split” the application, keeping in “main” application only stocks for last 5 years, and to configure a new web application for period older than 5 years. It’s annoying to create manually a backup for this period, so, we want to do a backup and a restore programmatically, and, for “old” database, call some stored procedures to delete the data older than 5 years, and, for “new” database, to delete data more recent than 5 years.

How to do that?

Let’s implement this functionality in following example. We create a website application based on framework 2.0, called BackupRestore, and add to solution a project of type ClassLibrary, called Utils.

Add class BackupRestore, and references Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.Smo to Utils project. These libraries come with classes used to implement this functionality.

This mechanism will function as follow: user can provide database name in interface; the rest of the settings:

-       name of SQL Servers (source and destinations) which will host the source and destination databases, with user and passwords

-       folder where database backup will be created

will be loaded from a xml file, and the application will be created.

            Let’s take a look to the listing of BackupRestore class code:

using System;

using System.Collections.Generic;

using System.Collections;

using System.Text;

using System.IO;

using System.Xml;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Server;

using Microsoft.SqlServer.Management.Common;

using System.Configuration;

namespace Utils

{

      public class BackupRestore

      {

            public BackupRestore()

            {

            }

            /// Load XML file with archive settings

            {

                  XmlDocument xmlSettings = new XmlDocument();

                  string xmlSettingsPath = ConfigurationManager.AppSettings["SettingsPath"];

                   try

                  {

                        xmlSettings.Load(xmlSettingsPath + "DBSettings.xml");

                  }

                  catch

                  {

                          throw new ApplicationException("Settings file could not be opened");

                  }

                  return xmlSettings;

            }

            /// read a key from archive configuration file

           private static string GetXmlSetting(XmlDocument xmlSettings, string Key)

           {

                  try

                  {

                        return xmlSettings.DocumentElement.SelectSingleNode("archiveSetting[@key='" + Key + "']").Attributes["value"].Value;

                  }

                  catch {

                        throw new ApplicationException("Setting " + Key + " ndoes not exists in settings file");

                  }

            }

            /// Performs a backup for a database

            public static string BackupDatabase(string DBName)

            {

                  XmlDocument xmlSettings = LoadXMLSettings();

                  string serverName = GetXmlSetting(xmlSettings, "DbSourceServer");

                  string databaseName = DBName;

                  string loginName = GetXmlSetting(xmlSettings, "DbSourceServerSQLLogin");

                  string password = GetXmlSetting(xmlSettings, "DbSourceServerSQLPassword");

                  string destinationPath = GetXmlSetting(xmlSettings, "DbBackupPath");

                  destinationPath += databaseName + "_xxxxx" + ".bak";

                  Backup sqlBackup = new Backup();

                  sqlBackup.Action = BackupActionType.Database;

                  sqlBackup.BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString();

                  sqlBackup.BackupSetName = "Archive";

                  sqlBackup.Database = databaseName;

 
                   BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);

                  ServerConnection connection = new ServerConnection(serverName, loginName, password);

                  Server sqlServer = new Server(connection);

                   Database db = sqlServer.Databases[databaseName];

                   sqlBackup.Initialize = true;

                  sqlBackup.Checksum = true;

                  sqlBackup.ContinueAfterError = true;

                  sqlBackup.Devices.Add(deviceItem);

                  sqlBackup.Incremental = false;

                   sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);

                  sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

                  sqlBackup.FormatMedia = false;

                  try

                  {

                        sqlBackup.SqlBackup(sqlServer);

                   }

                  catch(Exception ex)

                  {

                        throw new ApplicationException("Error creating backup for database " + databaseName + ": " + ex.Message);

                  }

                  return "Database backup created successfully";

            }

            /// Performs a database restore

            public static string RestoreDatabase(string DBName)

            {

                  XmlDocument xmlSettings = LoadXMLSettings();

                  string serverName = GetXmlSetting(xmlSettings, "DbDestinationServer");

                  string loginName = GetXmlSetting(xmlSettings, "DbDestinationServerSQLLogin");

                  string password = GetXmlSetting(xmlSettings, "DbDestinationServerSQLPassword");

                  string destinationPath = GetXmlSetting(xmlSettings, "DbRestorePath");

                  string sourceDatabaseName = DBName;

                  string sourcePath = GetXmlSetting(xmlSettings, "DbBackupPath");

                  //string databaseName = sourceDatabaseName + "_xxxxx";

                  sourcePath += sourceDatabaseName +  "_xxxxx" + ".bak";

                  Restore sqlRestore = new Restore();

                  BackupDeviceItem deviceItem = new BackupDeviceItem(sourcePath, DeviceType.File);

                  sqlRestore.Devices.Add(deviceItem);

                  sqlRestore.Database = sourceDatabaseName;

                  ServerConnection connection = new ServerConnection(serverName, loginName, password);

                  Server sqlServer = new Server(connection);

                  Database db = sqlServer.Databases[sourceDatabaseName];

                  sqlRestore.Action = RestoreActionType.Database;

                  String DataFileLocation = destinationPath + sourceDatabaseName + ".mdf";

                  String LogFileLocation = destinationPath + sourceDatabaseName + "_Log.ldf";

                  db = sqlServer.Databases[sourceDatabaseName];

                  RelocateFile rf = new RelocateFile(sourceDatabaseName, DataFileLocation);

                  sqlRestore.RelocateFiles.Add(new RelocateFile(sourceDatabaseName, DataFileLocation));

                  sqlRestore.RelocateFiles.Add(new RelocateFile(sourceDatabaseName + "_log", LogFileLocation));

                  sqlRestore.ReplaceDatabase = true;

                  try

                  {

                        sqlRestore.SqlRestore(sqlServer);

                        db = sqlServer.Databases[sourceDatabaseName];

                        db.SetOnline();

                        File.Delete(sourcePath);

                  }

                  catch(Exception ex)

                  {

                        return "Error restoring database " + sourceDatabaseName + ": " + ex.Message;

                  }

                  sqlServer.Refresh();

                  return "Database restored successfully";

            }

      }

}

The classes which do the job are Backup and Restore, from namespace Microsoft.SqlServer.Management, which get the necessary information to get the job (backup or restore) as properties, and then perform the specific operation.

In interface, we have a textbox where we introduce name of virtual directory, and buttons ‘Backup database’ and ‘Restore database’. These buttons have associated the code:

      private void BackupDatabase()

      {

            string DBName = txtDBName.Text;

            if (DBName != "")

            {

                  string Status = Utils.BackupRestore.BackupDatabase(DBName);

                  lblStatus.Text = Status;

            }

      }

      private void RestoreDatabase()

      {

            string DBName = txtDBName.Text;

            if (DBName != "")

            {

                  string Status = Utils.BackupRestore.RestoreDatabase(DBName);

                  lblStatus.Text = Status;

            }

      }

BackupRestoreSQLDB.rar (544.71 kb)

Introduction

There are situations, in web applications, when is need to create a virtual directory. For example, we have an application that generates automatically websites for users, creating files on disk, such as pages, images etc, and we want to create a virtual directory for every website we created on disk; It’s annoying to create manually a virtual directory for every physical directory created on disk; instead, we can create a functionality which allows us to create these virtual directories programmatically.

How to do that?

Let’s implement this functionality in following example. We create a website application based on framework 2.0, called VirtualDirectory, and add to solution a project of type ClassLibrary, called Utils.

Add class VirtualDirectory, and reference System.DirectoryServices to Utils project. This library comes with classes used to implement this functionality.

This mechanism will function as follow: user can provide virtual directory name in interface, and application will gel the rest of the settings:

-       name of IIS server which will host the application

-       source folder – the folder of the application, which will be created (in our case, this application)

-       destination folder – the folder where will be copies the new application. Note that on this directory must have writing rights the user NerworkService or the user under our application is running.

will be loaded from a xml file, and the application will be created.

            Let’s take a look to the listing of VirtualFolder class code:

using System;

using System.Collections.Generic;

using System.Collections;

using System.Text;

using System.IO;

using System.Xml;

using System.Data;

using System.Data.SqlClient;

using System.DirectoryServices;

using System.Configuration;

namespace Utils

{

public class VirtualDirectory

{

public VirtualDirectory()

{

}

/// Load XML file with archive settings

private static XmlDocument LoadXMLSettings()

{

XmlDocument xmlSettings = new XmlDocument();

string xmlSettingsPath = ConfigurationManager.AppSettings["ArchiveSettingsPath"];

try

{

xmlSettings.Load(xmlSettingsPath + "DirSettings.xml");

}

catch

{

throw new ApplicationException("Settings file could not be opened");

}

return xmlSettings;

}

}

/// Copy a directory to specified destination

private static void CopyDir(string sourceDir, string destDir)

{

DirectoryInfo dir = new DirectoryInfo(sourceDir);

try

{

if (!Directory.Exists(destDir))

Directory.CreateDirectory(destDir);

}

catch

{

throw new Exception("Cannot create directory at location " + destDir);

}

FileInfo[] files = dir.GetFiles("*");

foreach (FileInfo filePath in files)

{

try

{

filePath.CopyTo(Path.Combine(destDir, filePath.Name));

}

catch

{

throw new Exception(

"Cannot create file " + filePath.Name + " at location " + destDir);

}

}

DirectoryInfo[] children = dir.GetDirectories();

foreach (DirectoryInfo dirChild in children)

{

CopyDir(Path.Combine(sourceDir, dirChild.Name), Path.Combine(destDir, dirChild.Name));

}

}

/// Copy application directory to specified destination

private static void CopyDirectory(XmlDocument xmlSettings, string AppName)

{

string sourceDir = GetXmlSetting(xmlSettings, "WebAppSourceFolder");

string destDir = GetXmlSetting(xmlSettings, "WebAppDestinationFolder");

destDir += AppName;

if (!Directory.Exists(destDir))

   Directory.CreateDirectory(destDir);

else

{

   throw new Exception("Directory " + destDir + " already exists!");

}

try

{

   CopyDir(sourceDir, destDir);

}

catch {

   throw new Exception("Cannot copy directory at location " + destDir);

}

}

/// Creates virtual directory for application

public static string CreateApplication(string AppName)

{

try

{

XmlDocument xmlSettings = LoadXMLSettings();

CopyDirectory(xmlSettings, AppName);

CreateVirtualDirectory(AppName, xmlSettings);

}

catch(Exception ex) {

return ex.Message;

}

return "Directory created";

}

/// Creates a virtual directory in IIS

private static void CreateVirtualDirectory(string appName, XmlDocument xmlSettings)

{

string IISSServerName = GetXmlSetting(xmlSettings, "WebDestinationServer");

string applicationPath = GetXmlSetting(xmlSettings, "WebAppDestinationFolder");

try

{

DirectoryEntry teamTrackDirSchema = new System.DirectoryServices.DirectoryEntry(

"IIS://" + IISSServerName + "/Schema/AppIsolated");

bool canCreate = !(teamTrackDirSchema.Properties["Syntax"].

Value.ToString() == "BOOLEAN");

teamTrackDirSchema.Dispose();

if (canCreate)

{

System.DirectoryServices.DirectoryEntry virtualParent =

new System.DirectoryServices.DirectoryEntry("IIS://" +

IISSServerName + "/W3SVC/1/Root");

//if virtual dir exists, delete it

foreach (System.DirectoryServices.DirectoryEntry vd in virtualParent.Children)

{

if (vd.Name == appName)

{

virtualParent.Invoke("Delete", new String[] {

vd.SchemaClassName, appName });

virtualParent.CommitChanges();

}

}

//create virtual directory

System.DirectoryServices.DirectoryEntry vDir =

virtualParent.Children.Add(appName, "IIsWebVirtualDir");

vDir.Properties["Path"][0] = applicationPath + appName;

vDir.Properties["AppFriendlyName"][0] = appName;

vDir.Properties["EnableDirBrowsing"][0] = false;

vDir.Properties["AccessRead"][0] = true;

vDir.Properties["AccessExecute"][0] = true;

vDir.Properties["AccessWrite"][0] = false;

vDir.Properties["AccessScript"][0] = true;

vDir.Properties["AuthNTLM"][0] = false;

//Anonymous access = false | Integrated win auth = true

vDir.Properties["AuthFlags"].Value = 4;

vDir.Properties["EnableDefaultDoc"][0] = true;

vDir.Properties["DefaultDoc"][0] = "default.htm,default.aspx,default.asp";

vDir.Properties["AspEnableParentPaths"][0] = true;

vDir.CommitChanges();

vDir.Invoke("AppCreate", 1);

}

else

throw new ApplicationException("Cannot create virtual directory " + appName);

}

catch(Exception ex)

{

throw new ApplicationException("Error creating virtual directory " + appName);

}

}

/// read a key from archive configuration file

private static string GetXmlSetting(XmlDocument xmlSettings, string Key)

{

try

{

return xmlSettings.DocumentElement.SelectSingleNode(

"archiveSetting[@key='" + Key + "']").Attributes["value"].Value;

}

catch {

throw new ApplicationException(

"Setarea " + Key + " nu exista in fisierul de setari arhivare");

}

}

}

The main function called here is CreateApplication, which receive application name as parameter from interface, and does the following operations:

-       load settings from a xml file

-       copy new application to a location specified in the settings file

-       create virtual directory

Virtual directory is created using class DirectoryEntry, from library System.DirectoryEntry:

new System.DirectoryServices.DirectoryEntry("IIS://" + IISSServerName + "/W3SVC/1/Root"

In this example, we create a virtual directory under the first website ("/W3SVC/1/Root") found under IIS WebSites (in many cases, Default Web Site). Probably it’s better to provide the name of website under IIS websites which we want to be parent for our virtual directory, search it in websites list, return its index and use it to init the virtual directory object. Here, it’s enough to create under the first website in websites list (is Default Web Site in our case).

In interface, we have a textbox where we introduce name of virtual directory, and a button Create, to create the directory. This button has associated the code:

string VirtualDir = txtVirtDirName.Text;

if (VirtualDir != "")

{

       string Status = Utils.VirtualDirectory.CreateApplication(VirtualDir);

       lblStatus.Text = Status;

}

If an error occurs to this operation, it will be displayed, else, will be displayed text “Directory created”

VirtualDirectory.rar (25.68 kb)

Introduction

The XML standard  is an intense used standard, because confers possibility to “carry” in a easy and fully understandable way information between different entities, belonging to different application on contained in same application. Capability of an XML “message” to be configurable and processed, due of its “architecture” recommends it as an ideal “transporter” to communicate with outside world, receiving and sending information. As the time passed, more and more technologies started to use XML standard, while the new ones (such as BizTalk) made from XML technology a “cornerstone”, relying their entire philosophy on XML.

XML in SQL Server usage

                Obviously, a very powerful technology, such SQL Server, which is one of the most used RDBMS in the world, could not avoid using the XML standard. Among XML standard utilization, we distinguish 3 of most important SQL mechanisms based on XML technology.

1.     Receive XML data

2.     Return XML data

3.     Storing XML data

Receive XML data

Through this mechanism, we can receive XML documents from another applications and use further within SQL statements in order to insert/update/delete/select data. This is a very useful feature in some cases, such as:

-       SQL statements (stored procedure, function) are called by applications which use XML as basic model of representing data (for example, BizTalk Server). In this case, is difficult to process the message before to send it to SQL statement in order to send just individual values, but is easier to send entire XML message to SQL statement and to process it there.

-       In case of a bulk insert/update/delete operations from applications which call SQL statements. In this case, is inappropriate to get component fields of every row to be inserted, and send them to SQL statement, because, for each row, we must open a connection to SQL Server, execute the statement with these fields as parameters, and close connection. For a bigger number of this kind of calls, it is obviously that performance of application and SQL server also will be affected. Instead, we can send entire batch as an XML document, and perform the insert/update/delete operations from within SQL statement (usually, a stored procedure). Of course, in case of multiple deleting operation, we can construct a document which contains only the elements constituting the primary key of table(s) from within rows will be deleted, and perform the deletion for that records.

Example

Let’s discuss on following scenario:

-       An ASP.NET application – Visual C# 2005, framework 2.0 – we will call it Company

-       In Company application, we will have a page, Customers.aspx, where, when we will press a button, we will receive a dataset with customers, from a data provider (imported from another database or file, received from a web service etc.). In our case, will be an XML file stored in application.

-       After we will receive the dataset with customers, we will insert in our database using openxml function.

To import the customers from file Customers.xml from directory solution to database, the steps are:

1.     On a new or existing database, run script CustomerTable.sql from SQlFiles folder, to add table TblCustomer

2.     On the same database run the script usp_InsertCustomers.sql from same folder, to add stored procedure usp_InsertCustomers

3.     Install application Companies on IIS server and open it with URL http://localhost/Companies.aspx

4.     Click on button ‘Import customers’. If the import goes correctly, will appear the message “Customers were inserted”

5.     Check in table TblCustomer, to see the customers imported

What happened in the back? There are some operations which occurs when button ‘Import customers’ is pressed. The operations are:

1.     The content of the Customers.xml file is loaded in a DataSet object. When xml content is loaded, DataSet object identifies automatically the tables existing in xml structure, creates them, am populates them with data from xml. Normally, we should use a xml schema to check if xml content matches an expected structure and, thus, to avoid error. But, in this example, to simplify the model, we will consider a valid structure coming from xml file.

For this, we have the function

private DataSet LoadCustomersData()

{

DataSet dsCustomers = new DataSet("Customers");

dsCustomers.ReadXml(Server.MapPath("Customers.xml"));

return dsCustomers;

}

2.     The DataSet object is outputted to a string, which is passed to a DAL function, to be inserted in database

protected void ImportCustomers_Click(object sender, EventArgs e)

{

DataSet dsCustomers = LoadCustomersData();

StringWriter swCustomers = new StringWriter();

dsCustomers.WriteXml(swCustomers, XmlWriteMode.IgnoreSchema);

InsertCustomers(swCustomers.ToString());

}

private void InsertCustomers(string xmlCustomers)

{

lblResult.Text = CompaniesDAL.Customers.InsertCustomers(xmlCustomers);

      }

Of course, we can pass the xml file content to a DAL method directly, without using DataSet object. Here, we consider the possibility to receive this xml content to a various data sources, and in some cases, using a DataSet object may be useful, so, we used Dataset object.

3.     DAL method passes the xml string to a stored procedure, as a parameter, in order to be inserted in table TblCustomer.

public static string InsertCustomers(string xmlCustomers)

{

string strConnectionConfigurationManager.

ConnectionStrings["TestSQLXML"].

ToString();

SqlConnection sqlConnection = new SqlConnection(strConnection);

SqlCommand sqlCommand = new SqlCommand();

sqlCommand.Connection = sqlConnection;

sqlCommand.CommandText = "usp_InsertCustomers";

sqlCommand.CommandType = CommandType.StoredProcedure;

//pass xml string as parameter

SqlParameter sqlParam = new SqlParameter("@xmlCustomers", SqlDbType.VarChar, 8000);

sqlParam.Value = xmlCustomers;

sqlCommand.Parameters.Add(sqlParam);

try

{

sqlConnection.Open();

sqlCommand.ExecuteNonQuery();

}

catch (Exception ex)

{

return ex.Message;

}

finally {

if(sqlConnection.State == ConnectionState.Open)

sqlConnection.Close();

}

return "Customers were inserted";

}

4.     Inserting the xml in table TblCustomers using procedure usp_InsertCustomers. Let’s take a closer look to this procedure

create procedure [dbo].[usp_InsertCustomers]

@xmlCustomers varchar(8000)

as

begin

declare @i int

exec sp_xml_preparedocument @i output, @xmlCustomers

insert into dbo.TblCustomer

select * from

openxml(@i, '/Customers/Customer', 2)

with

(

CustomerType int,

CustomerFirstName varchar(50),

CustomerLastName varchar(50),

CustomerBirthDate datetime

)

exec sp_xml_removedocument @i

end

                We can observe how the procedure makes the job:

-       It receives the xml content as a string (@xmlCustomers varchar(8000))

-       Uses the procedure sp_xml_preparedocument to create an internal representation of xml document based on string received. The procedure returns a handle, stored in variable @i, to identity the document in next operations

-       Next, is performed a SELECT operation over xml document

o     openxml procedure get the parameters:

§  @i – specifies which document must be processed, since @i handles the xml document associated with it by the procedure sp_xml_preparedocument called earlier

§  it is specified the level from where the selection must be done ('/Customers/Customer')

§  last parameter Indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. Possible values are:

·         0 Defaults to attribute-centric mapping.

·         1 Use the attribute-centric mapping. Can be combined with XML_ELEMENTS; in which case, attribute-centric mapping is applied first, and then element-centric mapping is applied for all columns not yet dealt with.

·         2 Use the element-centric mapping. Can be combined with XML_ATTRIBUTES; in which case, attribute-centric mapping is applied first, and then element-centric mapping is applied for all columns not yet dealt with.

·         8 Can be combined (logical OR) with XML_ATTRIBUTES or XML_ELEMENTS.
In context of retrieval, this flag indicates that the consumed data should not be copied to the overflow property @mp:xmltext.

In our example, this parameter will take value 2, because our xml document is element-centric.

o    with clause specifies the structure of dataset that will be filled by select operation. Thus, we define columns that come from xml document:

§  CustomerType int

§  CustomerFirstName varchar(50)

§  CustomerLastName varchar(50)

§  CustomerBirthDate datetime

The columns must match with columns from TblCustomer, to be able to perform insert operation

-       Rows returned by select statement will be inserted in TblCustomer table.

insert into dbo.TblCustomer

select * from openxml...

-       After insert, we must release resources involved in these operations

exec sp_xml_removedocument @i – free memory occupied by xml document, associated with @i handle

Using this mechanism, we performed a SQL operation over multiple records. In this way, we reduce the number of operations of opening/closing SQL connections, increasing the performance. More than that, passing the XML document on the SQL server and processing there gives us the possibility to put all processing in one place, making developer’s life easier.

OpenXML.rar (31.76 kb)