ASP.NET : Trigger an Event in the Parent Page from within a Page inside IFrame

Web Applications have started dominating the traditional Windows based applications and in the process, the requirements are ever increasing and in many situations we find that too many functionalities need to come as part of a single page.

At this point we resolve to break down each of these functionalities as separate pages and try to call these pages inside an IFrame. This will ensure that the page does not become too heavy and also easy maintainability.

The problem starts when an event has to be triggered in the parent page after an event occurs inside the IFrame. Now let us take an example before I explain how this can be accomplished.

Let us say we have a page that contains Product information. I have some ten list of options like adding categories of a Product, Viewing Product information, etc.

Now for all these I want to give a singly entry system, with my Parent page where I show up all the products in a list box. On top of this I have all the ten options specified. All the other functionalities are taken to ten different pages. I keep an IFrame beside my ListBox that calls the 10 different pages.The aspx page for the Parent Page (say ProductHome.aspx) will have this code:

<table class="table" height="60%" width="80%" align="center" border="0">
<tr>
<td width="20%" height="100%">
<asp:linkbutton id="lnkAddProduct" CssClass="sublink" Runat="server" text="Add a New Product"></asp:linkbutton>
<asp:listbox id="lstProducts" CssClass="textbox" Runat="server" width="100%" Rows="10" AutoPostBack="True" ></asp:listbox></td>
<td width="80%" height="100%"><iframe id="MyFrame" style="WIDTH: 100%; HEIGHT: 100%" name="main" src="" frameBorder="0" scrolling="yes" height="100%" runat="server"></iframe>
</td>
</tr>
</table>


On click of every product I show up the page that displays Product information. Another page is for creating a new Product. Whenever I create a new Product in a page inside an IFrame, I must be able to add that product to the ListBox in the Parent page.

For this to happen, all I need to do is just a few lines of code in the code behind of the child page that contains the new Product information (let us call it AddProduct.aspx). In the Button Click Event (say btnSave_Click), after saving the information to the database:

Session["BindProducts"] = "true";
Session["Page"] = "AddProduct.aspx";
Session["ShowMessage"] = "The Product was added successfully";
Page.RegisterStartupScript("RefreshParent","<script language='javascript'>RefreshParent()</script>");

All that the above code does is to refresh the parent page after setting a few Session variables.

Now in the aspx page of the child page, add the following script block:

<script language="javascript">
function RefreshParent()
{
window.parent.location.href = "ParentPage.aspx";
}
</script>

So now when the Parent Page refreshes, you must bind the ListBox again with the new Product added. For this to happen, add the following code in Page_Load event of Parent Page (ProductHome.aspx):

if(Session["BindProducts"] != null)
{
if(Session["BindProducts"].ToString() == "true")
{
//Call the function to Bind the Products list box again
BindProducts();
}
}

And now remember to set the IFrame's src attribute to your child Page with a message displaying that the Product was added successfully. This will give the end user a feeling that he is only working with the one single page. Add the following code in Page_Load event of ProductHome.aspx to set the URL of the IFrame:


#region Set URL of IFrame
if(Session["Page"] != null)
{
string url = Session["Page"].ToString();
HtmlControl MyFrame = (HtmlControl)this.FindControl("MyFrame");
MyFrame.Attributes["src"] = url;
Session["Page"] = null;
}
#endregion

Now as a last step, display the message that the Product was added in the Child Page. Add the following code in Page_Load event of the Child Page (AddProduct.aspx):

if(Session["ShowMessage"] != null)
{
lblVisitsAdded.Text = Session["ShowMessage"].ToString();
Session["ShowMessage"] = null;
}

The above steps can be repeated for all the other functionalities.

How To : Create a Dynamic Time Table in ASP.NET

There will be many times where you will need to have a time table in your applications.

For ex. Think of a travel agency person who wants to mark that the buses/transport facilities would be available on specific times - on specific days. This availability information is prone to have frequent changes and therefore must be editable also.

Lets suppose that we maintain two tables - one for number of days the bus will run on like Sunday, Monday, etc. - we will call it AvailabilityDays, and one for the timings - such as 8:30, 9:00, 9:30, 11:30, 12:30 , etc. called AvailabilityTimes.

All we need is an editable grid with checkboxes, with columns as AvailabilityTimes and rows as the AvailabilityDays.

The first step is to get data through a Stored Procedure into a DataSet. The following code gets the code into a DataSet from an SP that has just 2 select statements from the


DataTable avDaysTable = new DataTable();
DataTable avTimeTable = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlCon = new SqlConnection("server=[servername];uid=[username];pwd=[password];database=[database]");
SqlCommand cmd = new SqlCommand("GetAvailabilityDaysAndTime",sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
dAdapter.Fill(ds);
avDaysTable = ds.Tables[0];
avTimeTable = ds.Tables[1];

Now that we have two datatables with Days and Times, we now need to construct a dynamic table containing these. For that we first declare an empty table in the aspx page:

<asp:table CssClass="sub-heading" id="MyTable" Width="100%" Height="50%" Runat="server" Font-Size="10" Font-Name="Arial" BorderStyle="Ridge" GridLines="Both"></asp:table>

Now to build the dynamic time table, we first take the count of the columns that need to come, and first construct the header row with the number of columns.

int columnCount = avTimeTable.Rows.Count;
TableItemStyle tableStyle = new TableItemStyle();
tableStyle.HorizontalAlign = HorizontalAlign.Center;
tableStyle.VerticalAlign = VerticalAlign.Middle;
tableStyle.Width = Unit.Pixel(100);
tableStyle.CssClass = "button-editable-heading";

TableRow headerrow;
TableCell headerCell;
headerCell = new TableCell();
headerCell.Text = "";
headerrow = new TableRow();
headerrow.Cells.Add(headerCell);
for(int i=0;i<avTimeTable.Rows.Count;i++)
{
headerCell = new TableCell();
headerCell.Text = avTimeTable.Rows[i][1].ToString();
headerrow.Cells.Add(headerCell);
}
headerrow.ApplyStyle(tableStyle);
MyTable.Rows.Add(headerrow);

Next, for each of the columns in the header, we now need to add as many number of days as in the AvailabilityDays table. This will constitute the rows of the table. To each of these rows, we also need to add a Checkbox so that the availability for a day and for a particular time can be set. Finally, add these rows to the main table to complete the time table grid.

CheckBox chk;
for(int i=0;i<avDaysTable.Rows.Count;i++)
{
//add rows to first column
headerrow = new TableRow();
headerrow.ID = i.ToString();
headerCell = new TableCell();
headerCell.Text = avDaysTable.Rows[i][1].ToString();
headerCell.ApplyStyle(tableStyle);
headerrow.Cells.Add(headerCell);
for(int j=0;j<avTimeTable.Rows.Count;j++)
{
headerCell = new TableCell();
chk = new CheckBox();
chk.ID = "chk" + i.ToString() + j.ToString();;
headerCell.Controls.Add(chk);
headerrow.Cells.Add(headerCell);
}
MyTable.Rows.Add(headerrow);
}
MyTable.Rows.Add(headerrow);

Thats it. We now have a time table grid ready to take the values for any day any time. In the next article, I'll explain how we can store and retrieve values in the time table.

How to : Get Excel data into a .NET DataSet object

There may be requirement where in data in an Excel sheet has to be manipulated inside .NET Applications. Although there are many ways to achieve this (like using Excel object of COM), the most preferred way is to use the .NET Data providers to achieve this.

The following is a sample code that can be used to get Excel data into a .NET Dataset object.

The parameter excelSheetName in the below method is the name of the Excel sheet and the sheetNumber parameter indicates the number of the sheet (out of all the sheets in an Excel file).

public DataSet RetrieveExcelData(string excelSheetName,int sheetNumber)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Connection String.
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelSheetName + ";Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
if(i==sheetNumber)
break;
}
OleDbCommand excelCommand = new OleDbCommand("Select * from ["+excelSheets[sheetNumber-1]+"]",objConn);
OleDbDataAdapter excelAdapter = new OleDbDataAdapter(excelCommand);
DataSet excelDataSet = new DataSet();
excelAdapter.Fill(excelDataSet);
return excelDataSet;
}
catch(OleDbException ex)
{
throw ex;
}
catch(Exception ex)
{
throw ex;
}
finally
{
// Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}

Through the above method, we avoid the extra reference of the COM dll and also get the flexibility of manipulating Excel shett data through ADO.NET.

ASP.NET TextBox, Trim White Spaces within words

ASP.NET TextBox provides a lot of features which reduces many a plumbing work required in previous versions of HTML Input Type.

Some of the built-in functionalities of <asp:textbox> such as TextMode to specify whether it is a Text field, a password field or Text Area simplify the programming task and provide meaningful interfaces to work with.

One of the important tasks we do when receiving user input in TextBox is to Trim the white spaces entered by the users. This can very well minimize the size of the data as well as storage in DataStores.

Some of the built-in methods that ship with the string class are

string.TrimStart()

Removes the specified set of occurances from the Beginning of the string

string.TrimEnd()

Removes the specified set of occurances from the End of the string

string.Trim()

Removes White Spaces from Beginning & End of the string. Very useful in applications while retrieving user input to remove the white spaces.

To utilize the above functions with TextBox input entered by the users, we can simply pass TextBox1.Text.Trim(). Since TextBox1.Text returns a string of the text entered by the user.

However, if we would like to trim the extra white spaces entered by the users in the TextBox in the form of a sentence etc., then the above methods would'nt be useful.

Say the user has entered "This       is     a     Word". We would like to save it/process it as "This is a Word", with the extra white spaces trimmed off.

To do that, we can use RegularExpressions. The following code demonstrates how we can remove extra white spaces recursively between words.

using System.Text.RegularExpressions;

private string RemoveSpaces(string str)
{
string result = "";
Regex regulEx = new Regex(@"[\s]+");
result = regulEx.Replace(str," ");
return result;
}

The above function receives a string and trims all the occurances of extra white spaces between words.

So, an input of "This       is     a     Word" will return the output as "This is a Word"

Thanks

Exception Management Strategy for N-tier Applications - Part III

This article is in continuation of my previous two posts on Exception Management Strategy for N-tier Applications. If you havent read them, please check Part I and Part II.

Code Snipped Continued ...

The sample code for wrapping an exception that is caused in the DAL is shown below:

try
{// statements that access SQL Server
}
catch(SqlException sqlEx)
{
DataOperationFailedException dataOp = new DataOperationFailedException(sqlEx.Message,sqlEx);
dataOp.ErrorNumber = sqlEx.Number;
dataOp.SqlServerName = sqlEx.Server.ToString();
dataOp.ActionTaken = "";
dataOp.LogonUser = "";
dataOp.MstrStacktrace = sqlEx.StackTrace;
dataOp.ContextInfo = "MyNamespace.MyClass";
//Publish exception
ExceptionSQLPublisher.Publish(dataOp);

//Rethrow exception
throw dataOp;
}
The same way each module in the application can be wrapped suitably and the exception rethrown to above layer.

If the Web.config is configured to take to a specific error page, customized error messages for each type of exception cannot be shown.

So, the global.asax can be used to show customized error messages by finding the exception type that occured in the application. Here is the sample code:

protected void Application_Error(Object sender, EventArgs e)
{
Exception ex = Server.GetLastError().InnerException;
if(ex.GetType().ToString()== "Microsoft.ApplicationBlocks.
ExceptionManagement.DataOperationFailedException")
{
Response.Redirect(@"..\Error.aspx?Type=Data");
}
}

Exception Management Strategy for N-tier Applications - Part II

The previous article explained how important it is to have a Base exception class that can be used through out your application. In this article, let us examine what Exception Management stategy can be built for these Enterprise Applications. Let us suppose that the strategy we design must take care of the following points:
1. Any Exception raised in any of the Layers must be Logged. 2. The custom error page exposed by the Presentation Layer must also indicate some sort of context information (Either about the Module or some specific type of exception).

The second point is important because in a large application, its certainly difficult to track which module caused the exception. So if the custom error page contains some context specific information about the module that caused the exception, handling the exception would be easy.

So taking into consideration the above points, the following strategy can be designed:

1. Design specific custom Exception classes for each module that inherits from the MyAppBaseApplicationException class. Any exception that occurs in that module will be wrapped using this Exception class.

2. Any exception that occurs throught out your application will therefore be wrapped with the corresponding class and logged. Once the exception is logged, the isLogged flag of the base exception class will be set to true.This will avoid logging again on the higher layers.

3. The logging will happen to the database by default and if the database access fails, the error messages will be logged to Windows event log.

4. After the exceptions are logged the wrapped exceptions are then re-thrown to the next higher layer.

5. In the final layer, you will therefore have only the wrapped exceptions or the exception that occurs in the Presentation layer.

6. The custom error page in the Presentation layer can be shown with customised error messages for each module.

A sample custom exception class for the Data Access module is shown below:

public class DataOperationFailedException:MSTRBaseApplicationException
{
#region Fields
private int sqlErrorNumber;
private string sqlServerName;
private string storedProcName;
private int lineNumber;
#endregion

#region Properties
//Add properties to access the private variables
#endregion
}

Code Snippet Continued in my Next Post.

Exception Management Strategy for N-tier Applications - Part I

In this article, which is spread across two parts, I would like to discuss about how effectively exceptions can be managed Enterprise applications spread across N-Tiers.

In any application that uses Enterprise Architecture, managing exceptions forms a critical requirement. To build successful and flexible applications that can be maintained and supported easily, an appropriate exception management strategy must be adopted.

Now what are exceptions, by the way?

Exceptions represent a breach of an implicit assumption made within code.

For example, if your code tries to access a file that is assumed to exist, but the file is missing, an exception would be thrown. However, if your code does not assume that the file exists and checks for its presence first, this scenario would not necessarily generate an exception.

Microsoft Application Blocks are generally used for Enterprise Applications to manage Exceptions. The Application Blocks provide a set of APIs that can be directly used for managing exceptions. If your application is complex and spread across manylayers, we can build a custom application class that uses these Application Blocks for handling exceptions.

Now let us suppose we have the following layers in our Application:
1. Data Access Layer (DAL)
2. Business Layer (BL)
3. Business Facade Layer(BFL)
4. Presentation Layer (PL)

and with these layers, we also have class library that contains the Microsoft Application Blocks for Exception Management.

To this library we will add our custom Base Exception class say, MyAppBaseApplicationException class.
This MyAppBaseApplicationException class will inherit from BaseApplicationException that comes with the Exception Management Block. The following is the sample code that can be used for MyAppBaseException:


///

/// Summary description for MyAppBaseApplicationException.
///

public class MyAppBaseApplicationException:CognizantBaseAppException
{
#region Fieldsprivate int errorNumber; //The error number assigned by sql or by the .net framework
private string actionTaken; //The task being performed when the exception occurred
private string contextInfo; //A field to be used to get the context in which the exception occured
private bool exceptionHandled; //Indicates whether or not the exception has been handled
private bool displayToClient; //Indicates whether or not the end user should see the exception details.
private string logonUser; //The user of the system when the exception occurred.
private string exceptionDate; //The date and time when the exception occurred.
private bool isLogged;//Indicates if the exception is logged to SQL Server
private string mstrStackTrace;//For stack trace
#endregion //Private Data Members
#region Constructors

/// Constructor with exception id and inner exception
public MyAppBaseApplicationException(string message, Exception inner) : base(message,inner)
{
Initialize();
}
///Add other constructors if needed
#endregion

#region Properties
///Add properties to access the private members
#endregion

You may need to add additional properties pertaining to your application. The isLogged flag is important as it indicates if a specific exception has been logged or not. This is necessary when your Application is spread across many layers.

Having written the Base class, the next article will concentrate on how to use this MyAppBaseApplicationException class and how we log the exceptions in all the four layers.

Points to consider to increase performance in ADO.NET – Part II

In my previous article, ten points on the general considerations on increasing the performance of ADO.NET were discussed. Let us examine in more detail on other factors that affect the performance.

1. Use of Transactions
Transactions are important for ensuring data integrity. But they can also have an operational cost if they are not rightly used. You must select the right transaction management mechanism for your application to significantly improve scalability and performance.
The following points should be remembered during your decision of using transactions:

a) Use SQL transactions when:
· You need server controlled transactions
· You can complete the transaction operation on a single data store in just one simple call to the datastore
b) Use ADO.NET transactions when:
· You need client-controlled transactions
· When you want to make multiple calls to a single datastore
c) Keep transactions as short as possible with as little code as possible to avoid locks.
d) Use the appropriate isolation level.
SQL Server and other database systems support various levels of isolation for transactions. Isolation shields database operations from the effect of other concurrent transactions.
There are basically four levels of isolation-
· Read uncommitted,
· Read committed,
· Repeatable read
· Serializable

The highest isolation level, serializable, reduces concurrency and provides the highest level of data integrity.
The lowest isolation level, read uncommitted, gives the opposite result.

By selecting the correct level of isolation that pertains to your application, you can improve performance and scalability.

2. Avoid code that can lead to deadlock.
Sometimes your code may get trapped in a deadlock when it is highly data centric. To avoid deadlocks, always access tables in the same order across transactions in your application. The likelihood of a deadlock increases when you access tables in a different order each time you access them.

3. Consider SET NOCOUNT ON for SQL Server
When you use SET NOCOUNT ON, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results. When you use SET NOCOUNT OFF, the count is returned. Using SET NOCOUNT ON can improve performance because network traffic can be reduced.

4. The Parameter Collection
Use the Parameters collection when you call a stored procedure and when you build SQL commands. Also ensure that you explicitly set the data type for each parameter. This will avoid the round trips to server by checking the data type on the client and the likelihood that the Parameter object could set an invalid type.

3. Cache stored procedure SqlParameter objects.
Often, applications must run commands multiple times. To avoid recreating the SqlParameter objects each time, cache them so that they can be reused later. You can use a Hashtable object to cache SqlParameter objects.
For code samples for the parameter caching approach, check out this link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp.

4. Avoid moving Binary Large Objects Repeatedly
Avoid moving BLOB data more than one time from the server to client and vice versa.
For example, if you build a Web application that serves images, store the images on the file system and the file names in the database instead of storing the images as BLOBs in the database. Storing the images as BLOBs in the database means that you must read the BLOB from the database to the Web server and then send the image from the Web server to the browser. Reading the file name from the database and having the Web server send the image to the browser reduces the load on the database server.

5. Paging Records
Paging records is a common application scenario. The records that you need to page through can often be based on user input. For example, they can be based on a search keyword entered through a search screen. Or, the records can be common to all users, like a product catalogue.

There are two basic approaches to paging:
· You can return the whole result set from the database to the client. The client caches the result set and then displays the most relevant results to the user by using the correct filtering mechanism. But this will involve processing cost at the client and memory for caching.

· You can have the database assume the additional role of a filter by making the database return only the most relevant result set to the client. This will involve network cost for the amount of data sent across the network.

Depending upon the type of application and load on the server, we must try to decide the best between the above two approaches.

6. Exception Management
a) Avoid relying on an Exception error handler to detect connection state availability. use the ConnectionState.Open or ConnectionState.Close method to check the state before use.

b) Use try/finally more often than try/catch/finally. Using finally gives you the option to close the connection, even if an exception occurs.

c) Use specific handlers to catch specific exceptions. For example, if you know that your code may cause an SqlException, use catch(SqlException sqlEx) and then use a generic exception handler like catch(Exception ex).

I have covered most of the points I've learnt on increasing performance in ADO.NET.
One interesting topic which remains to be addressed is about the best use of DataSet and DataReader, which I think is addressed in numerous articles on web. Here is one:
http://geekswithblogs.net/ranganh/archive/2005/04/25/37618.aspx

Ten points to consider to increase performance in ADO.NET

Here is a consolidated list of how you can improve the performance of your .NET apps:

1. Design your data access layer based on how the data is used.

In larger applications, it’s always better to go for a separate Data Access Layer (DAL) to abstract the underlying data store complexity and to provide a logical separation. Having the data access logic in the same presentation layer may increase performance but at the cost of maintainability.

You can use the Microsoft Application Blocks for simplifying your tasks of Data Access and Exception handling. (I personally prefer this)

2. Cache your data to avoid unnecessary round trips and network overhead.

Try to cache data that is used across your application, in the layer that is close to the consumer of data. This will reduce the latency in network to fetch data. Note that if your data in cache needs to be updated too frequently, then better you don’t cache that data.

3. Acquire late, release early.

Open database connections right only when you need them. Close the database connections as soon as you are finished. Acquire locks late, and release them early.

4. Close disposable resources.

Make sure that you call either the Dispose or Close method on resources that are disposable, as soon as you are finished with using the resource.

5. Reduce round trips.

· If you have some 3 or 4 SQL statements, try to use batch sql statements in a stored procedure to decrease round trips.
· Use ExecuteScalar method for getting a single result.
· Use ExecuteNonQuery method when you want to execute any DDL statement.
· Use connection pooling to help avoid extra round trips. By reusing connections from a connection pool, you can avoid the round trips that are associated with connection establishment and authentication.

6.Return only the data you need.

Evaluate the data that your application actually requires and return only that data. This will minimize the bandwidth consumption in the network.

7. Use Windows authentication.

From a security perspective, use Windows authentication instead of SQL authentication. This ensures that credentials are not passed over the network, database connection strings do not contain credentials, and you can apply standard Windows security policies to accounts. Remember to use connection pooling with your connection.

8. Use stored procedures.

Avoid embedded SQL statements and use Store Procedures (SP) instead. This has the following advantages:
· A Logical separation of Data access code from your Business Logic code
· Queries can be optimized for performance from SQL server
· Deployment becomes easier as, for any change in SP you don’t need to redeploy you application.
· SPs allow the batch execution of SQL commands
· You can impose specific restrictions on selected stored procedures for security reasons. This is very difficult to be done from embedded SQL.
· You can avoid the most dangerous SQL Injection by using parameterized SPs.

9. Consider how to handle exceptions.

You can use try/finally blocks to ensure that connections and other resources are closed, regardless of whether exceptions are generated or not. The best way for abstracting all exceptions from user is to log them to a file or Windows Event log.

10. Use appropriate normalization.

You may want a normalized database to minimize data duplication but be aware that you don’t over-normalize. This can affect the performance and scalability of your application

The above list is purely based on my working experience and there are many more points to add to this, which I’ll be adding in the next part of this article.

What are Application Domains?

Many of us have come across the word "Application Domains" during our projects. But mostly I have seen many people having a wrong notion on what Application Domains really are.
Now lets examine what these App Domains are.

Application domains are basically logical boundaries which provide a secure, versatile unit of processing that the Common Language Runtime (CLR) uses to provide isolation between applications.

Thus, you can run several application domains in a single process with the same level of isolation that would exist in separate processes, but without incurring the additional overhead of making cross-process calls or switching between processes.
The ability to run multiple applications within a single process dramatically increases server scalability.

Thus, the Application domains provide an isolation that has the following advantages:

1. Application Security
2. Faults in one application cannot affect other applications.
3. Individual applications can be stopped without stopping the entire process.
4. Code running in one application cannot directly access code or resources from another application. The CLR enforces this isolation by preventing direct calls between objects in different application domains.
5. The Scope of the code is controlled by the Application domain. The Application domain provides configuration settings such as application version policies, the location of any remote assemblies it accesses, and information about where to locate assemblies that are loaded into the domain.
6. The Permissions granted to code can be controlled by the application domain in which the code is running.

HOW TO: Programmatically create SSIS packages using Whidbey

Hi,

If you had read my last two articles, you would now be familiar with SSIS and how to create simple packages Business Intelligence Development Studio.

In this article, let us now check how to programmatically create a package/edit an existing package using the APIs provided with SQL Server 2005.

Let us start with creating a simple Console Application in Whidbey.

First of all, we need to add the following references to your Whidbey project:

1. Microsoft.SqlServer.DTSPipelineWrap.dll
2. Microsoft.SQLServer.DTSRuntimeWrap.dll
3. Microsoft.SQLServer.ManagedDTS.dll

The reference dlls can be found in the following location:
C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

You must then add the namespace references in the Program.cs file as follows:

#region Using directives
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
#endregion

An SSIS package can be created using the Package class present in the Microsoft.SqlServer.Dts.Runtime namespace as follows:

Package pkg = new Package();
pkg.Name = "MyCreatedPackage";
pkg.ID = "MyPackageID";

Now lets see how to take an existing package, modify its properties and save the package to your file system or to Yukon server.

We begin with defining a Package object and an Application object.

Package pkg = new Package();
Application a = new Application();

The Application class is used to discover and access Package objects.
Next we declare the MainPipe interface which is used to programmatically configure the data flow task. The IDTSComponentMetaData90 Interface contains the definition of a data flow component; including the custom properties, inputs, outputs, and input and output columns defined on a data flow component.

IDTSComponentMetaData90 oledbSource;
IDTSComponentMetaData90 oledbDestination;
MainPipe dataFlow;

In case you are trying to modify the properties of an existing package, you must first load the external package that you already have as a template.

pkg = a.LoadPackage(@"C:\TestPackages\MyPackage.dtsx", null);

You must now add the connection using the ConnectionManager class and set connection properties. The ConnectionManager class contains all the information necessary to connect to a single type of data source.
//Add connections
ConnectionManager conMgr = pkg.Connections.Add("OLEDB");

/// Set stock properties
conMgr.Name = "OLEDBConnection";
conMgr.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=mdpkb2e139;Auto Translate=False;";

The next step is to add/edit the data flow task. The following is the code to add a Data Flow Task and set its properties:

TaskHost th = pkg.Executables.Add("DTS.Pipeline") as TaskHost;
th.Name = "DataFlow";
dataFlow = th.InnerObject as MainPipe;

//set source component
oledbSource = dataFlow.ComponentMetaDataCollection.New();
oledbSource.ComponentClassID = "DTSAdapter.OLEDBSource";
oledbSource.Name = "OLEDBSource";
CManagedComponentWrapper instanceSource = oledbSource.Instantiate();

oledbSource.RuntimeConnectionCollection.New();
oledbSource.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnection"].ID;

oledbSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnection"]);
instanceSource.ProvideComponentProperties();
instanceSource.SetComponentProperty("OpenRowset", "Emp");
instanceSource.SetComponentProperty("AccessMode", 0);

// Acquire Connections and reinitialize the component
instanceSource.AcquireConnections(null);
instanceSource.ReinitializeMetaData();
instanceSource.ReleaseConnections();

In case you already have a template with the data flow task, we can get the handle of it using the Executable collection. The following is the code for modifying a DataFlow task:

Executable exe = x.Executables["Data Flow Task"];
TaskHost th2 = exe as TaskHost; d
ataFlow = th2.InnerObject as MainPipe;
IDTSComponentMetaDataCollection90 metadataCollection = dataFlow.ComponentMetaDataCollection;
//set source component
oledbSource = dataFlow.ComponentMetaDataCollection[0];
if (oledbSource.RuntimeConnectionCollection.Count > 0)
{
oledbSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(x.Connections["OLEDBConnection"]);
oledbSource.RuntimeConnectionCollection[0].ConnectionManagerID = x.Connections["OLEDBConnection"].ID;
}
CManagedComponentWrapper instanceSource = oledbSource.Instantiate();

instanceSource.SetComponentProperty("OpenRowset", "Temp_1");
instanceSource.SetComponentProperty("AccessMode", 0);

// Acquire Connections and reinitialize the component
instanceSource.AcquireConnections(null);
instanceSource.ReinitializeMetaData();
instanceSource.ReleaseConnections();

The same steps are followed for setting the properties of the destination component. The following is the code for modifying the connection properties of OLEDB Destination component:

//set destination component
oledbDestination = dataFlow.ComponentMetaDataCollection[1];
if (oledbDestination.RuntimeConnectionCollection.Count > 0)
{
oledbDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(x.Connections["OLEDBConnection"]);
oledbDestination.RuntimeConnectionCollection[0].ConnectionManagerID = x.Connections["OLEDBConnection"].ID;
}
CManagedComponentWrapper instanceDest = oledbDestination.Instantiate();
instanceDest.SetComponentProperty("OpenRowset", "Temp_2");
instanceDest.SetComponentProperty("AccessMode", 0);
// Acquire Connections and reinitialize the component
instanceDest.AcquireConnections(null);
instanceDest.ReinitializeMetaData();
instanceDest.ReleaseConnections();

The next step is to "Map" the columns of the source and destination components. The following is the code to map the input column collection (source collection) to the External Metadata column collection (Destination collection):

IDTSInput90 input = oledbDestination.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
IDTSInputColumn90 vCol = instanceDest.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
instanceDest.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);
}

Finally we save the package to the file system.

string currentDirectory = System.IO.Directory.GetCurrentDirectory();
a.SaveToXml(currentDirectory + "\\DTSDataflow.dtsx", pkg, null);
Console.WriteLine("Successfully created an SSIS package");
Console.ReadLine();

In case you want to save the package directly to Yukon server, you can use the following code:

a.SaveToSqlServer(pkg, null, "mdpkb2e139", "", "");

Here it goes! You have now programmatically created a simple SSIS package.

SSIS in Yukon - Part II - Creating a Simple Package in SSIS

In the previous article we had discussed about the new IDE for SSIS.
Now let us see how to create a simple SSIS package. The following are the steps:

1. From the File menu, click New-> Project.
2. Under Projects, choose Business Intelligence Projects and under Templates, choose Integration Service Project, and then click OK.
3. By default a package named Package.dtsx is created under the folder SSIS Packages. Click View-> Solution Explorer to see this.
4. Right click on the Connections pane, and then click New OLE-DB Connection.
5. Create two new connections - one to your Yukon source server and another to your Yukon destination server. Name these connections as MySourceConnection and MyDestinationConnection.
6. From the View menu, click Toolbox.
7. Now drag and drop a Data Flow Task component on the Control Flow pane.
8. Double-click the Data Flow Task component to take you to the Data Flow pane.
9. From the toolbox, drag and drop a OLE DB Source component and OLE DB Destination component. Name them as MySource and MyDestination respectively.
10. Double-click MySource.
11. Set the connection of MySource to MySourceConnection.
12. Set the Data-access mode to Table or View.
13. Set the name of the Source table from the listed tables of the database.
14. By clicking on the Columns tab on the left, you will be able to see the list of columns for the table. You can uncheck the columns that are not needed and then click OK.
15. Set the properties of MyDestination OLEDB component in the same way as in 14.
16. Click on MySource component. Drag the Green arrow you notice and make it point to MyDestination component. You have now connected the Source and the Destination components.
17. From the Debug menu click Start to run your first SSIS project.

Once your package has executed successfully, you will see components (Dataflow task, MySource and MyDestination) coloured in Green. And if there is any failure during execution, the components are coloured in Red. The success or failure of the package can be monitored by clicking on the Progress tab in the project during execution.

Note: The Source and Destination components must contain the same number of columns and same column names (case sensitive) to avoid Validation errors.

You can also have Event Handlers at the Package level or for each component in the Event Handler tab.

So this is how we go about creating a simple package. My next article will focus on how you can programmatically create these SSIS packages using Whidbey.

SQL Server Integration Services (SSIS) in Yukon

Many of us are familiar with Data Transformation Services (DTS) in SQL Server 2000.

SQL Server 2005 (code named Yukon) provides a completely new platform called SQL Server Integration Services (SSIS) that serves as the primary enterprise platform Extract, Transform and Load (ETL).

There are a looot of enhancements that has happened to DTS in Sql 2000. Creating packages is now separated to a new Development environment called Business Intelligence Development Studio (bienv.exe). This gives the same look n feel as our Visual Studio Dev Environment.
We have everything similar to VS - the Solution, Project, toolbox, setting configurations, etc.

The toolbox contains various tasks and componets that can be used in the editor.

There are 4 tabs provided in the editor:

1. Control flow - For controlling the task flow
2. Data Flow - For Data pumping tasks
3. Event handlers - For handling events and errors
4. Package Explorer - Gives a treeview view of the various components in the package
5. Execution results - For viewing the status during execution

The connections form a separate section that is common to the above four tabs. Connection manages for File, SQL Server, OLE DB can be added here.


Another cool feature is the Logging feature. All we have to do is just Right click the editor pane and enable Logging with a specific connection. All events now get logged in the specified server.

Every package that is a part of a SSIS project can be executed from BI. The deployment of these packages can be done either to the file system or to SQL Server. The packages deployed in SQL Server will be saved in the DTS Server.

My next article will throw more light on working with SSIS and programmability in Whidbey for SSIS.

HOW TO : Call a C# function inside Yukon Stored Procedure

Recently I had an interesting requirement in my project to use a C# procedure inside SQL.
Prior to SQL Server 2005, we had to use only Extended store procedures..
But as Yukon is integrated with CLR, you can now call a C# Whidbey function inside SQL Server procedure.

The following is the way you can do it:

1. Start Visual Studio 2005.
2. On the File menu, click New Project.
3. Under Projects, choose Visual C# and under Templates choose SQL Server Project template. Click OK. By default a project named SqlServerProject1 is created.
4. You will now be prompted to specify a connection. Provide the connection of the Yukon server for which you need the C# function.

5. Right click SqlServerProject1 on Solution Explorer and click Add New Item. Choose any template that is provided.
6. Now replace the following code with the existing code provided with the template:

#region Using directives
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
#endregion
public class MyClass
{
public MyClass()
{
}
public static void GetDepartmentDetailsByNum(int DeptNo)
{
SqlPipe sp = SqlContext.GetPipe();
sp.Send("The Department number is " + DeptNo.ToString() + "\n");
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText = "select * from dept where deptnum=" + DeptNo.ToString();
SqlDataReader rdr = cmd.ExecuteReader();
sp.Send(rdr);
}
public static void GetSum(int x, int y)
{
SqlPipe sp = SqlContext.GetPipe();
int z=x+y;
sp.Send(z.ToString());
}
}
7. Now save the project and Build the project.
8. Open SQL Server Management Studio and connect to the Yukon server.
9. Right click the Database where the C# function is needed and then click New Query.
10. In the query editor, paste the following code:

CREATE ASSEMBLY ManagedCsharpProcedure
FROM 'C:\Laksk\SqlServerProject1\bin\Debug\SqlServerProject1.dll'

CREATE PROCEDURE [dbo].[GetSum]
@x int ,@y int
AS
EXTERNAL NAME
SqlManaged.DeptDetails.GetSum
GO

11. Now test the GetSum procedure using the following statement :
exec GetSum 5,4

Thats it !!! You now have a C# method being called in SQL Server. The same method can also be used with recordsets as in the C# function GetDepartmentDetailsByNum that is included in the same class. So on passing the Dept Number, we get the Department details through the C# function.