Copying a table using SMO

This is a sample code to create a copy of a table using Server Management Objects (SMO).

The class DBObject is used to hold the names of the table and its related functions and views that need to be copied. Currently, for simplicity sake, I will only keep the table name. It can be further extended to store other db object names.


using System;
using System.Collections.Specialized;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace SMODemo
{
public class DBObject
{
public string TableName { get; set; }

public DBObject(string tableName)
{
this.TableName = tableName;
}

// Can be further extended to save the dependent view names and functions

}

The following code is a console application. It creates a copy of the Employee table in the SO database. The user is prompted for the destination table name. The columns, indexes and keys (primary and foreign) are copied when the table is copied.


class Program
{
static Database db;

static void Main(string[] args)
{
CopyMain();
}

private static void CopyMain()
{
string connectionString = System.Configuration.ConfigurationSettings.AppSettings["SO"];

//Connect to the local, default instance of SQL Server.
SqlConnection conn = new SqlConnection(connectionString);
Server srv = new Server(new ServerConnection(conn));

//Reference the SO database.
db = srv.Databases["SO"];

Console.WriteLine("Enter destination table name:");

string destinationTableName = Console.ReadLine();
DBObject destinationObject = new DBObject(destinationTableName);
DBObject sourceObject = new DBObject("Employee");

CopyObjects(sourceObject, destinationObject);
}

private static void CopyObjects(DBObject sourceObject, DBObject destinationObject)
{
Table table = db.Tables[sourceObject.TableName];
CopyTable(table, destinationObject.TableName);

// Can be further extended to copy the dependent views and functions
}

private static void CopyTable(Table sourceTable, string destinationName)
{
Table destinationTable = new Table(db, destinationName);

foreach (Column col in sourceTable.Columns)
destinationTable.Columns.Add(CopyColumn(destinationTable, col));

foreach (Index index in sourceTable.Indexes)
destinationTable.Indexes.Add(CopyIndex(destinationTable, index, sourceTable.Name));

foreach (ForeignKey fk in sourceTable.ForeignKeys)
destinationTable.ForeignKeys.Add(CopyForeignKeys(destinationTable, fk, sourceTable.Name));

// TODO: Create copy for Triggers

destinationTable.Create();
}

private static Column CopyColumn(Table parent, Column sourceColumn)
{
Column destinationColumn = new Column(parent, sourceColumn.Name, sourceColumn.DataType);

if (sourceColumn.Computed)
{
destinationColumn.Computed = true;
destinationColumn.ComputedText = sourceColumn.ComputedText;
}

if (sourceColumn.Identity)
{
destinationColumn.Identity = true;
destinationColumn.IdentityIncrement = sourceColumn.IdentityIncrement;
destinationColumn.IdentitySeed = sourceColumn.IdentitySeed;
}

destinationColumn.Default = sourceColumn.Default;
destinationColumn.Nullable = sourceColumn.Nullable;

return destinationColumn;
}

private static Index CopyIndex(Table parent, Index sourceIndex, string sourceTableName)
{
Index destinationIndex = new Index(parent, sourceIndex.Name.Replace(sourceTableName, parent.Name));
destinationIndex.IndexKeyType = sourceIndex.IndexKeyType;

foreach (IndexedColumn sourceIndexedCol in sourceIndex.IndexedColumns)
{
destinationIndex.IndexedColumns.Add(new IndexedColumn(destinationIndex, sourceIndexedCol.Name, sourceIndexedCol.Descending));
}

return destinationIndex;
}

private static ForeignKey CopyForeignKeys(Table parent, ForeignKey sourceForeignKey, string sourceTableName)
{
ForeignKey destinationForeignKey = new ForeignKey(parent, sourceForeignKey.Name.Replace(sourceTableName, parent.Name));

foreach (ForeignKeyColumn fkSourceCol in sourceForeignKey.Columns)
destinationForeignKey.Columns.Add(new ForeignKeyColumn(destinationForeignKey, fkSourceCol.Name, fkSourceCol.ReferencedColumn));

destinationForeignKey.DeleteAction = sourceForeignKey.DeleteAction;
destinationForeignKey.IsChecked = sourceForeignKey.IsChecked;
destinationForeignKey.IsEnabled = sourceForeignKey.IsEnabled;
destinationForeignKey.ReferencedTable = sourceForeignKey.ReferencedTable;
destinationForeignKey.ReferencedTableSchema = sourceForeignKey.ReferencedTableSchema;
destinationForeignKey.UpdateAction = sourceForeignKey.UpdateAction;

return destinationForeignKey;
}
}
}


Memory games

Shedding some light on memory allocation of value types and reference types and demonstrating the effect on the values when reference types are passed by value or passed by reference.




using System;

namespace TestApp
{
    class Program
    {
        static void Main(string[] args)
        {
            Sample originalSample = new Sample(10, "Original String");

            Console.WriteLine("Printing original values ...");
            Console.WriteLine("Int: " + originalSample.myInt);
            Console.WriteLine("String: " + originalSample.myString);

            // Pass originalSample by value
            TestMethod1(originalSample);
            Console.WriteLine("After TestMethod1 ...");
            Console.WriteLine("Int: " + originalSample.myInt);
            Console.WriteLine("String: " + originalSample.myString);

            // Pass originalSample by ref
            TestMethod2(ref originalSample);
            Console.WriteLine("After TestMethod2 ...");
            Console.WriteLine("Int: " + originalSample.myInt);
            Console.WriteLine("String: " + originalSample.myString);

            Console.Read();
        }

        static void TestMethod1(Sample a)
        {
            a = new Sample();

            a.myInt = 20;
            a.myString = "TestMethod1";
        }

        static void TestMethod2(ref Sample a)
        {
            a = new Sample();

            a.myInt = 30;
            a.myString = "TestMethod2";
        }
    }
}

Program output:

Explanation:

In Main()

Let us assume that originalSample is allocated memory on the heap at location x000000

HEAP location x000000

———————

myInt 10

myString Original String

STACK

Hence the value of originalSample on the stack will be

Variable Value

————– ——-

originalSample x000000

In TestMethod1()

ref keyword is not used

What does this mean?

It means ‘a’ is local to TestMethod1. Hence a will be created on the stack as a COPY of originalSample.

The value of a on the stack will be the same as the value of the passed variable i.e. originalSample.

This value is nothing but the address on heap of originalSample

STACK

Variable Value

————– ——-

originalSample x000000

a x000000

HEAP location x000000

———————

myInt 10

myString Original String

Now if the following statements are executed

a.myInt = 20;

a.myString = "TestMethod1";

Then this will change the object at heap location x000000. But originalSample resides at x000000.

So if the above changes are made, it will change the contents of originalSample.

However, to demonstrate the difference between pass by value and ref, I have assigned a new Sample() to a.

a = new Sample();

When this runs, the new keyword has the effect of allocating new location on the heap. And now a would point to this new location.

So lets assume, new memory is allocated to a on the heap at x000004

So now the contents of the heap and stack will be as follows

HEAP location x000000

———————

myInt 10

myString Original String

HEAP location x000004

———————

myInt 0

myString ""

STACK

Variable Value

————– ——-

originalSample x000000

a x000004

a.myInt = 20;

a.myString = "TestMethod1";

Now since we are making changes to a, the contents of heap location x000004 will be affected as follows:

HEAP location x000004

———————

myInt 20

myString TestMethod1

Thus a different area of the heap is affected and originalSample remains unchanged.

Thus pass by value will not change the originalSample as long as we create a new Sample() and assign it to local variable and make changes to it.’

In TestMethod2()

ref keyword is used

What does this mean?

It means ‘a’ is a reference to originalSample. Hence a will be created on the stack as a REFERENCE of originalSample.

The value of a on the stack will be the reference to originalSample and not value of originalSample

STACK

Variable Value

————– ——-

originalSample x000000

a originalSample …. Points to originalSample which in turn points to heap location x000000

HEAP location x000000

———————

myInt 10

myString Original String

Now if the following statements are executed

a.myInt = 20;

a.myString = "TestMethod1";

Then this will change the object at heap location x000000. But originalSample resides at x000000.

So if the above changes are made, it will change the contents of originalSample.

Thus whether the value is passed by value or reference, any changes made to its member variables will be reflected.

However, to demonstrate the difference between pass by value and ref, I will assign a new Sample() to a.

a = new Sample();

The new keyword has the effect of allocating new location on the heap.

So lets assume, new memory is allocated to a on the heap at x000004

Now a should point to this new location.

However a is a reference to originalString.

Thus originalString will now point to the new location

So now the contents of the heap and stack will be as follows

HEAP location x000000

———————

myInt 10

myString Original String

HEAP location x000004

———————

myInt 0

myString ""

STACK

Variable Value

————– ——-

originalSample x000004

a originalSample

Now we can see that there is no variable on stack pointing to x000000.

Hence, it will be garbage collected in the next garbage collector cycle.

a.myInt = 30;

a.myString = "TestMethod2";

Now since we are making changes to a, the contents of heap location x000004 will be affected as follows:

HEAP location x000004

———————

myInt 20

myString TestMethod1

However, since a is reference to originalString, both pointing to the same location, we can see that originalString has been eventually modified.

Thus pass by reference will change the originalSample.

Thus we can see the effect on reference variables when they are passed by value or passed by reference.

Conclusion:

Reference type data whether passed by value or reference will change if the changes are made directly to the passed variable rather than creating a copy.

If a variable is passed by VALUE and a copy of the variable is created in the called method, changes made to the copy WILL NOT be reflected in the original variable.

If a variable is passed by REFERENCE and a copy of the variable is created in the called method, changes made to the copy WILL not be reflected in the original variable.

Auto refreshing cached files

Scenario:

A web site is used only by a limited set of users. Most of the pages in the web site can only be accessed by defined set of users. This information is stored in an xml file ‘permissions.xml’. The format of the file is as follows:


<permissions>
<page name="page1">
<user>user1</user>
<user>user2</user>
</page>
<page name="page2">
<user>user1</user>
<user>user3</user>
</page>
</permissions>


Thus, page1 can only be accessed by user1 & user2 and page2 by user1 and user3.

Now, there is an admin page to edit these permissions. So if a user decides to change the page permissions, the permissions.xml would get updated internally. As soon as it gets updated the web site should reflect the changes without any delay.

To do this, CacheDependency can be used.

In this case, the Cache should store the XmlDocument.

This is my sample code:

protected void Page_Load(object sender, EventArgs e)
{
XmlDocument permissionsDoc = null;
if (Cache["Permissions"] == null)
{
string path = Server.MapPath("~/XML/Permissions.xml");
permissionsDoc = new XmlDocument();
permissionsDoc.Load(Server.MapPath("~/XML/Permissions.xml"));
Cache.Add("Permissions", permissionsDoc,
new CacheDependency(Server.MapPath("~/XML/Permissions.xml")),
Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
CacheItemPriority.Default, new CacheItemRemovedCallback(ReloadPermissionsCallBack));
}
else
{
permissionsDoc = (XmlDocument)Cache["Permissions"];
}
}

private void ReloadPermissionsCallBack(string key, object value, CacheItemRemovedReason reason)
{
XmlDocument doc = new XmlDocument();
doc.Load(Server.MapPath("~/XML/Permissions.xml"));
Cache.Insert("Permissions", doc ,
new CacheDependency(Server.MapPath("~/XML/Permissions.xml")),
Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
CacheItemPriority.Default, new CacheItemRemovedCallback(ReloadPermissionsCallBack));
}


In the above code, when the permissionsDoc is added to the Cache, the CacheDependency is set. This ensures the item is removed from the cache when it changes. The CacheItemRemovedCallback is a delegate that suggests which method is to be called once the item is removed from the cache.

In the example, when the permissions.xml file changes, it gets removed from the Cache thus firing ReloadPermissionsCallBack(). The method in turn, reloads the updated xml file and inserts it in the Cache.

Please note, in ReloadPermissionsCallBack(), while doing a Cache.Insert, the CacheDependency is specified again. This is required because, if it is not done, there will no more be a dependency on the xml file changes. So from the second time onwards, if the xml file gets updated, the cached XmlDocument will  not be removed and thus the updated one will not be reloaded.

Additional links on CacheDependency can be found on MSDN at http://msdn.microsoft.com/en-us/library/system.web.caching.cachedependency.aspx