ASP.Net Tips and Tricks

Ajax.Asp.Net Tips and Tricks: Adding Dynamic Controls to UpdatePanel and Identifying what control has triggered the event.

2009-09-23T05:19:00.000-07:00

Here is a cool technique on how to identify which control has triggered the UpdatePanel (when dynamic controls are added during runtime). Here we will use the Request Object to identify this.

Lets say we have updatePanel1

and during runtime we go and add dynamic buttons.... button1, button2 in the Panel1....

LinkButton button1 = new LinkButton();
button1.ID = "button1"
button1.Text = "button1"

LinkButton button2 = new LinkButton();
button2.ID = "button2"
button2.Text = "button2"

Panel1.Controls.Add(button1);
Panel1.Controls.Add(button2);

now we can identify which button is clicked, by looking at the Request Object and passing the key of the ScriptManager.ID:

protected override void CreateChildControls()
{
base.CreateChildControls();
if( ScriptManager1.IsInAsyncPostBack )
{
string fromWhere = Request[ScriptManager1.ID];
Label1.Text = fromWhere;
}
}

This will get the following wtring:
On Button1 click: UpdatePanel1|button1
On Button2 click: UpdatePanel1|button2

if we look carefully at the string "UpdatePanel1|button1" we see that it has 2 parts, the ID of the UpdatePanel which got triggered and the ID of the control (ie. button1) which triggered the event.

So we can do something like this.....

protected override void CreateChildControls()
{
base.CreateChildControls();
if( ScriptManager1.IsInAsyncPostBack )
{
string fromWhere = Request[ScriptManager1.ID];

    if( fromWhere.Contains("button1") )
    {
        //Do something here
    }
    else if (fromWhere.Contains("button2")
    {

//Do something else....as you wish....
}
}
}

DDL Triggers in SQL Server 2005

2009-09-14T03:17:00.000-07:00

Triggers are not new to SQL Server. But prior to SQL Server 2005 triggers were DML triggers, which were raised only when there is an INSERT,UPDATE or DELETE action. A new table, database or user being created raises a DDL event and to monitor those, DDL triggers were introduced in SQL Server 2005.

Implementation
Following is the syntax for DDL triggers.

CREATE TRIGGER trigger_name
ON { ALL SERVER DATABASE }
[ WITH [ ,...n ] ]
{ FOR AFTER } { event_type event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] EXTERNAL NAME <> [ ; ] }

DDL triggers can be created in either in the Database or the Server. If you want to monitor table creations and drops, you should create DDL trigger on the database, while to monitor operations like database creations you should create a DDL trigger on the Server.
Take a simple example of creating a database.

CREATE DATABASE [DDL_TRIGGERS_DB]
Let us assume that we want to log all the new table creations. We will log all the events in some other database called DDL_Trigger_Log in a table which has following schema.
CREATE TABLE [dbo].[tblDDLEventLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EventTime] [datetime] NULL,
[EventType] [varchar](15) NULL,
[ServerName] [varchar](25) NULL,
[DatabaseName] [varchar](25) NULL,
[ObjectType] [varchar](25) NULL,
[ObjectName] [varchar](25) NULL,
[UserName] [varchar](15) NULL,
[CommandText] [varchar](max) NULL,)

Then we need to create a DDL trigger so that all the relevent event data is updated in the above table. Follwing will be the DDL trigger.
CREATE TRIGGER [ddltrg_CREATE_TABLE_LOG] ON DATABASE -- Create Database DDL Trigger
FOR CREATE_TABLE -- Trigger will raise when creating a Table
AS
SET NOCOUNT ON
DECLARE @xmlEventData XML
-- Capture the event data that is created
SET @xmlEventData = eventdata()
-- Insert information to a EventLog table
INSERT INTO DDL_Trigger_Log.dbo.tblDDLEventLog
(
EventTime,
EventType,
ServerName,
DatabaseName,
ObjectType,
ObjectName,
UserName,
CommandText
)
SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),
'T', ' '),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
GO
Then create a table and retrieve data in the tblDDLEvetnLog table:
You can see that all the necessary information, we will look more details about DDL triggers.

Database Triggers
As specified before, DDL triggers are executed whenever you create, drop or alter an object at the database level. Users, tables, stored procedures,views, service broker objects like queues , functions and schemas are the objects which fall into the database objects.
In a DDL trigger you can specify the trigger options (ie the operations that need to be triggered). In the above example, it is specified to execute the triggers when a new table is created. However, rather than specify each operation, there are DDL event groups that you can specify. In that case the trigger will be executed for all the operations in that event group. For example, if you specified DDL_DATABASE_LEVEL_EVENTS instead of CREATE_TABLE all the events for CREATE_TABLE, ALTER_TALBE and DROP_TABLE that trigger will be executed hence all the events will be logged.
That trigger will look like below;
CREATE TRIGGER [ddltrg_CREATE_TABLE_LOG] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
/* Your code goes here */
At the end of the article, you will find the all the existing trigger events with it's highrachy. If you specificed an event, the trigger will be excuted for all the subsequent events.
EVENTDATA is an important function in DDL triggers. The EVENTDATA() function will be raised whenever a DDL trigger is fired. Output of the EVETNDATA() function is in XML format. The following is the XML format of the EVENTDATA() with example.
You can use above tags to suit your requirments.
Let us see what are the options that we can use with EVENTDATE() functions.
Apart from monitoring table creations. another requirment for DBAs is to prevent users creating tables or any other objects which does not conform to a standard. For example, if you want to stop users from creating tables which do not have prefix tbl, you can use following DDL trigger.
CREATE TRIGGER [ddltrg_CheckCreateTable] ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON
DECLARE @xmlEventData XML,
@tableName VARCHAR(50)
SET @xmlEventData = eventdata()
SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)'))
IF LEFT(@tableName, 3) <> 'tbl'
BEGIN
RAISERROR ( 'You cannot create table name without starting with tbl',
16,- 1 )
ROLLBACK
END
GO

After creating above DDL trigger, if you try create a table like the following,
CREATE TABLE Customer
(
ID INT,
Desccription VARCHAR(50)
)
You will get below error and table will not be created because of the ROLLBACK statement specified in the trigger.

Msg 50000, Level 16, State 1, Procedure ddltrg_, Line 17
You cannot create table name without starting with tbl
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

It is important to remember is that unlike DML triggers, in DDL triggers you won't find INSTEAD OF triggers. Instead of using INSTEAD OF triggers, you can write the trigger so that it triggers instead of the opreration. Because of this, in DML triggers you do not have to roll them back. As there is no such an option for DDL triggers, you have insert a ROLLBACK which might be a bit expensive.
You can extend the DDL trigger to include stored procedures , functions and for schemas.
Also, if you want to stop users doing ALTER_TABLE during peak hours, you can do this by using the PostTime XML tag of EVENTDATA().

Server Triggers
Server DDL triggers fire when server operations are performed. For example, if you want to audit create database operations, the following trigger can be used.
CREATE TRIGGER [ddlsvrtrg_CREATE_DATABASE_LOG] ON ALL SERVER
FOR CREATE_DATABASE
AS
/* Your code goes here */
This trigger will also have the same EVENTDATA() function with same output XML format. Hence you will have all the options that database triggers have.

Enable or Disable Triggers
As in DML triggers, you have the option to Enable or Disable DDL triggers (for both server and database triggers)
DISABLE TRIGGER ddltrg_CREATE_TABLE_LOG
ON ALL SERVER
GO
ENABLE TRIGGER ddltrg_CREATE_TABLE_LOG
ON ALL SERVER
GO
Trigger Execution Order
When there are several triggers, you can define which trigger to execute first and last. There is a system stored procedure named sp_settriggerorder to set the priority. This is the same stored procedure which you can use to set priority for DML triggers as well.
sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername'
, [ @order = ] 'value'
, [ @stmttype = ] 'statement_type'
[ , [ @namespace = ] { 'DATABASE' 'SERVER' NULL } ]
From @order parameter you can set either first or last, which is the order of the trigger execution. The @namespace parameter can be set either DATABASE or SERVER depending on whether the DDL trigger is a database or server dependent trigger.

System Tables
It is often necessary to know where the triggers are saved. In case of database DDL triggers, the information is stored in sys.triggers and sys.trigger_events. The sys.triggers view contains information like trigger name, create date etc and sys.trigger_events view contains the for which events those triggers are going to execute.
SELECT *
FROM sys.triggers
SELECT *
FROM sys.trigger_events

In case of Server DDL triggers, you have to use sys.server_triggers and sys.server_trigger_events.
SELECT *
FROM sys.server_triggers
SELECT *
FROM sys.server_trigger_events

Improvements

Eventhough there are 100+ events included for DDL triggers, there are few important events. Specifically events for database backup, database restore, and SQL Server Job related.

Clearing All Controls of a Form

2009-08-12T00:22:00.000-07:00

Forms and Controls already have a property that list all controls contained within them called 'Controls'.

You can iterate through this, checking each controls type and clearing them as appropriate. Note that some controls may have child controls and these would also need to be processed.

The following method can be used to clear all the controls

public void ClearControls(Control currentControl)
{
foreach (Control myControl in currentControl.Controls)
{
if (myControl.HasChildren)
{
ClearControls(myControl);
}

TextBox myTextBox = myControl as TextBox;
if (myTextBox != null)
{
myTextBox.Text = "";
}

RadioButton myRadioButton = myControl as RadioButton;
if (myRadioButton != null)
{
myRadioButton.Checked = false;
}
}
}

Use ClearControls(this) from within the Form class

Generate PDF Files Dynamically Using ASP.NET

2009-08-04T07:33:00.000-07:00

Introduction

There are currently many ways to generate PDF files dynamically. The most widely known way is to use ASP together with Acrobat Full Version (4.0 or 5.0) and Acrobat FDF Toolkit. With Microsoft .NET many developers are wondering about how to use ASP.NET instead of ASP to tackle this same situation. To my surprise I could not find any related documentation from Adobe. I asked this question in many forums and no one had an answer for me. I had to roll up my sleeves and to my surprise--it's not so difficult. Now, I will make it even easier for all of you.
Tools
Adobe Acrobat 5.0 Full Version, not Acrobat Reader 5.0
Acrobat FDF Toolkit Version 5, free downloaded
Microsoft .NET Framework SDK with Service Pack 1
Platform
Windows 2000 Server (Service Pack 2), Internet Information Server 5.0
Installation Procedure
Install Adobe Acrobat 5.0 Full Version.
Go to http://partners.adobe.com/asn/developer/acrosdk/forms.html to download the Acrobat FDF Toolkit package.
Follow installation instructions of FDF Toolkit Reference document at http://partners.adobe.com/asn/developer/acrosdk/docs/fdftk/FDFtkRef.pdf. That is, uncompress the Acrobat FDF Toolkit package, find two files: FdfAcX.dll and FdfTk.dll. Install them both in \WINNT\system32 directory, then go to the directory, and type:Regsvr32 FdfAcX.dll. (Note: you do NOT need to register FdfTk.dll, as FdfAcX.dll uses FdfTk.dll).
Create .NET compatible "wrapper" for FdfAcX.dll using TlbImp.exe(Type Library Importer). In Microsoft Command Window, type: tlbimp FdfAcX.dll /out:FdfAcX_NET.dll
Put the generated CLR assembley FdfAcx_NET.dll in your application's \bin directory. Remember the rules: The assembly file generated by Tlbimp.exe must be placed in the ASP.NET application's \bin directory. The original COM component file must be registered for the directory in which it resides.
Comparison between ASP and ASP.NET use of FDF Toolkit
I will use a simplest example to show you the difference between ASP and ASP.NET when using the FDF Toolkit. Suppose you create a PDF template file using Adobe Acrobat 5 Full version, in which you only create one Text field named txtMemo. You want to populate the Text field using ASP.NET. You put the template file named test.pdf in the web root directory.
In ASP, using VBScript, you could use object FdfApp.FdfApp exposed by FdfAcX.dll like this: Set FdfAcX = Server.CreateObject("FdfApp.FdfApp")
Then you can populate the Text field as follows: FdfAcX.FDFSetFile "http://www.yourserver.com/test.pdf"
FdfAcX.FDFSetValue "txtMemo", "This is a test", false
FdfAcX.FDFSaveToFile "C:\temp\test.fdf"
FdfAcX.FDFClose
Set FdfAcX = nothing
How to do the same thing using ASP.NET?We need to carefully examine the structure of the CLR assembly FdfAcX_NET.dll, which is generated using TlbImp.exe. After typing command: TlbImp FdfAcX_NET.dllwe get the following:

From the above graph, we could see that FdfApp is an interface(abstract), it implements FdfAcX_NET.IfdfApp interface. While FdfAppClass is the real implementation, it is a class which implements FdfAcX_NET.FdfApp interface, which in turn implements FdfAcX_NET.IfdfApp interface. We can also see that method FDFCreate is in class FdfAppClass, with return type of object.
Similarly, we could find out that both IfdfApp and IfdfDoc, at the bottom of the graph, are just interfaces. You can collapse them to find the methods defined inside them. FdfDoc is an interface, which implements IfdfDoc. FdfDocClass is the real implementation class, which implements FdfDoc interface. Methods FDFSetFile, FDFSetValue and FDFSaveToFile are all implemented in class FdfDocClass.
In order to use the assembly FdfAcX_NET.dll, we need to import it: <%@ Import Namespace="FdfAcX_NET" %>
Then instantiate to create an object of class FdfAppClass(the following is written in C#) in order to call its CreateObject method: FdfAppClass FdfAcX_App = new FdfAppClass();
According to the return type of method CreateObject, it is object type. I guess it has to be an object of FdfDoc, so that method FDFSetValue can be called later to set the Text field value. I cast the object type to FdfDoc like this: FdfDoc FdfAcX_Doc = (FdfDoc)FdfAcX_App.FDFCreate();
The following is the equivalent code in ASP: FdfAcX_Doc.FDFSetFile("http://www.yourserver.com/test.pdf");
FdfAcX_Doc.FDFSetValue("txtMemo", " This is a test", false);
FdfAcX_Doc.FDFSaveToFile(@"c:\temp\test.fdf");
FdfAcX_Doc.FDFClose();
Here is the VB.NET version: Dim FdfAcX_App As FdfAppClass
FdfAcX_App = new FdfAppClass()
Dim FdfAcX_Doc As FdfDoc
FdfAcX_Doc = FdfAcX_App.FDFCreate
FdfAcX_Doc.FDFSetFile("http://www.yourserver.com/test.pdf")
FdfAcX_Doc.FDFSetValue("txtMemo", " This is a test ", false)
FdfAcX_Doc.FDFSaveToFile("c:\temp\test.fdf")
FdfAcX_Doc.FDFClose
How to pass the generated file to web users?Create an ASP.NET page as follows and that is all there is to it.

Calling a DLL with C# (C Sharp)

2009-05-04T05:51:00.000-07:00

using System.Runtime.InteropServices;
using System;

class call_dll {

[StructLayout(LayoutKind.Sequential, Pack=1)]
private struct STRUCT_DLL {
public Int32 count_int;
public IntPtr ints;
}

[DllImport("mingw_dll.dll")]
private static extern int func_dll(
int an_int,
[MarshalAs(UnmanagedType.LPArray)] byte[] string_filled_in_dll,
ref STRUCT_DLL s
);

public static void Main() {

byte[] string_filled_in_dll = new byte[21];

STRUCT_DLL struct_dll = new STRUCT_DLL();
struct_dll.count_int = 5;
int[] ia = new int[5];
ia[0] = 2; ia[1] = 3; ia[2] = 5; ia[3] = 8; ia[4] = 13;

GCHandle gch = GCHandle.Alloc(ia);
struct_dll.ints = Marshal.UnsafeAddrOfPinnedArrayElement(ia, 0);

int ret=func_dll(5,string_filled_in_dll, ref struct_dll);

Console.WriteLine("Return Value: " + ret);
Console.WriteLine("String filled in DLL: " + System.Text.Encoding.ASCII.GetString(string_filled_in_dll));

}
}

How to import data from Excel to SQL Server

2009-04-28T07:51:00.000-07:00

This step-by-step article demonstrates how to import data from Microsoft Excel worksheets into Microsoft SQL Server databases by using a variety of methods.

Description of the TechniqueThe samples in this article import Excel data by using:
SQL Server Data Transformation Services (DTS)
Microsoft SQL Server 2005 Integration Services (SSIS)
SQL Server linked servers
SQL Server distributed queries
ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
ADO and the Microsoft OLE DB Provider for Jet 4.0

Import vs. AppendThe sample SQL statements that are used in this article demonstrate Create Table queries that import Excel data into a new SQL Server table by using the SELECT...INTO...FROM syntax. You can convert these statements to Append queries by using the INSERT INTO...SELECT...FROM syntax while you continue to reference the source and destination objects as shown in these code samples.
Use DTS or SSISYou can use the SQL Server Data Transformation Services (DTS) Import Wizard or the SQL Server Import and Export Wizard to import Excel data into SQL Server tables. When you are stepping through the wizard and selecting the Excel source tables, remember that Excel object names that are appended with a dollar sign ($) represent worksheets (for example, Sheet1$), and that plain object names without the dollar sign represent Excel named ranges.
Use a Linked ServerTo simplify queries, you can configure an Excel workbook as a linked server in SQL Server. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
306397 HOWTO: Use Excel with SQL Server Linked Servers and Distributed QueriesThe following code imports the data from the Customers worksheet on the Excel linked server "EXCELLINK" into a new SQL Server table named XLImport1:

SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]

You can also execute the query against the source in a passthrough manner by using OPENQUERY as follows:

SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
'SELECT * FROM [Customers$]')

Use Distributed QueriesIf you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables:

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]
SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

Use ADO and SQLOLEDBWhen you are connected to SQL Server in an ADO application by using Microsoft OLE DB for SQL Server (SQLOLEDB), you can use the same "distributed query" syntax from the Using Distributed Queries section to import Excel data into SQL Server.The following Visual Basic 6.0 code sample requires that you add a project reference to ActiveX Data Objects (ADO). This code sample also demonstrates how to use OPENDATASOURCE and OPENROWSET over an SQLOLEDB connection.

Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=;" & _
"Initial Catalog=;User ID=;Password="
'Import by using OPENDATASOURCE.
strSQL = "SELECT * INTO XLImport6 FROM " & _
"OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
"'Data Source=C:\test\xltest.xls;" & _
"Extended Properties=Excel 8.0')...[Customers$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
'Import by using OPENROWSET and object name.
strSQL = "SELECT * INTO XLImport7 FROM " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
"'Excel 8.0;Database=C:\test\xltest.xls', " & _
"[Customers$])"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
'Import by using OPENROWSET and SELECT query.
strSQL = "SELECT * INTO XLImport8 FROM " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
"'Excel 8.0;Database=C:\test\xltest.xls', " & _
"'SELECT * FROM [Customers$]')"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
cn.Close
Set cn = Nothing

Use ADO and the Jet ProviderThe sample in the preceding section uses ADO with the SQLOLEDB Provider to connect to the destination of your Excel-to-SQL import. You can also use the OLE DB Provider for Jet 4.0 to connect to the Excel source.The Jet database engine can reference external databases in SQL statements by using a special syntax that has three different formats:
[Full path to Microsoft Access database].[Table Name]
[ISAM Name;ISAM Connection String].[Table Name]
[ODBC;ODBC Connection String].[Table Name]This section uses the third format to make an ODBC connection to the destination SQL Server database. You can use an ODBC Data Source Name (DSN) or a DSN-less connection string:

DSN:
[odbc;DSN=;UID=;PWD=]
DSN-less:
[odbc;Driver={SQL Server};Server=;Database=;
UID=;PWD=]

The following Visual Basic 6.0 code sample requires that you add a project reference to ADO. This code sample demonstrates how to import Excel data to SQL Server over an ADO connection by using the Jet 4.0 Provider.

Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test\xltestt.xls;" & _
"Extended Properties=Excel 8.0"

'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=;Database=;" & _
"UID=;PWD=].XLImport9 " & _
"FROM [Customers$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing

You can also use this syntax, which the Jet Provider supports, to import Excel data into other Microsoft Access databases, indexed sequential access method (ISAM) ("desktop") databases, or ODBC databases.

Importing and Exporting Data

2009-04-28T07:18:00.000-07:00

Importing data is the process of retrieving data from sources external to Microsoft® SQL Server™ (for example, an ASCII text file) and inserting it into SQL Server tables. Exporting data is the process of extracting data from an instance of SQL Server into some user-specified format (for example, copying the contents of a SQL Server table to a Microsoft Access database).

Importing data from an external data source into an instance of SQL Server is likely to be the first step you perform after setting up your database. After data has been imported into your SQL Server database, you can start to work with the database.

Importing data into an instance of SQL Server can be a one-time occurrence (for example, migrating data from another database system to an instance of SQL Server). After the initial migration is complete, the SQL Server database is used directly for all data-related tasks, rather than the original system. No further data imports are required.

Importing data can also be an ongoing task. For example, a new SQL Server database is created for executive reporting purposes, but the data resides in legacy systems updated from a large number of business applications. In this case, you can copy new or updated data from the legacy system to an instance of SQL Server on a daily or weekly basis.

Usually, exporting data is a less frequent occurrence. SQL Server provides tools and features that allow applications, such as Access or Microsoft Excel, to connect and manipulate data directly, rather than having to copy all the data from an instance of SQL Server to the tool before manipulating it. However, data may need to be exported from an instance of SQL Server regularly. In this case, the data can be exported to a text file and then read by the application. Alternatively, you can copy data on an ad hoc basis. For example, you can extract data from an instance of SQL Server into an Excel spreadsheet running on a portable computer and take the computer on a business trip.

SQL Server provides tools for importing and exporting data to and from data sources, including text files, ODBC data sources (such as Oracle databases), OLE DB data sources (such as other instances of SQL Server), ASCII text files, and Excel spreadsheets.

Additionally, SQL Server replication allows data to be distributed across an enterprise, copying data between locations and synchronizing changes automatically between different copies of data.

Output Parameters with Stored Procedure in ADO.NET

2009-04-28T07:07:00.000-07:00

Here is a sample sproc that populates output parameters
from the Northwind Products table:

CREATE PROCEDURE CustOrderOne
@CustomerID nchar(5),
@ProductName varchar(50) output,
@Quantity int output

AS
SELECT TOP 1 @ProductName=PRODUCTNAME, @Quantity =quantity
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

And here is an example of some C# code to return and display the output parameters:

using System;
using System.Data;
using System.Data.SqlClient;
namespace OutPutParms
{
class OutputParams
{
[STAThread]
static void Main(string[] args)
{
using( SqlConnection cn = new SqlConnection("server=(local);Database=Northwind;user id=sa;password=;"))
{
SqlCommand cmd = new SqlCommand("CustOrderOne", cn);
cmd.CommandType=CommandType.StoredProcedure ;
SqlParameter parm=new SqlParameter("@CustomerID",SqlDbType.NChar) ;
parm.Value="ALFKI";
parm.Direction =ParameterDirection.Input ;
cmd.Parameters.Add(parm);
SqlParameter parm2=new SqlParameter("@ProductName",SqlDbType.VarChar);
parm2.Size=50;
parm2.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm2);
SqlParameter parm3=new SqlParameter("@Quantity",SqlDbType.Int);
parm3.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm3);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine(cmd.Parameters["@ProductName"].Value);
Console.WriteLine(cmd.Parameters["@Quantity"].Value.ToString());
Console.ReadLine();
}
}
}
}

Dynamic Template Columns in the ASP.NET 2.0 GridView Control

2009-04-24T03:13:00.000-07:00

One of the nice things about ASP.NET is its depth: the sheer number of tools and techniques built into this Web application framework can be quite staggering. Recently I was involved in a project where we needed to present the results of a database query as part of an ASP.NET application, but we needed extensive control over the on-screen formatting, down to the level of controls used to present individual columns of data, CSS classes used, and more. To make matters even trickier, we didn't know until runtime what the query would be. After some discussion and experimentation among the design team, though, we decided that there was no need to buy a third-party control to handle these demands. The built-in GridView could handle all of our requirements. The key lay in understanding and using the little-known ability to add columns to the GridView dynamically using templates at runtime.

A GridView template is a class that implements the ITemplate interface. It defines the controls that will be displayed on the GridView in a column, how they will bind to data, and can have special-case code to handle headers and footers. In this article I'll show you a simplified example of building a GridView up from scratch using a template to respond to a dynamic query; the technique can be extended to cover much more complex situations.

The Template Class

Let's start with the Template class itself. This is the class that holds the code that will do the actual heavy lifting of putting controls in the DataGrid, as well as formatting them and binding them to data. It starts off with some private member variables and a constructor to set them:

// dynamically added label column
public class GridViewLabelTemplate : ITemplate
{

private DataControlRowType templateType;
private string columnName;
private string dataType;

public GridViewLabelTemplate(DataControlRowType type,
string colname, string DataType)
{
templateType = type;
columnName = colname;
dataType = DataType;
}
The next block of code gets called whenever an instance of this template is instantiated. If you think of a template as corresponding to a column in the GridView, this happens every time a header, cell, or footer of the GridView is created for that column. You can inspect the templateType member to figure out which of these is the case. Here, you want to create whatever control or controls you need to display the data. You're not limited to a single control, though for this article I'm only using one label for display. You can also do whatever you need to format the control to your liking. I'm going to grab the container for the control (which ends up being the wrapping table cell) and set its CSS style so that I can right-justify numeric columns. This method also sets up for data-binding by registering an event handler.

public void InstantiateIn(System.Web.UI.Control container)
{
DataControlFieldCell hc = null;

switch (templateType)
{
case DataControlRowType.Header:
// build the header for this column
Literal lc = new Literal();
lc.Text = "" + BreakCamelCase(columnName) + "";
container.Controls.Add(lc);
break;
case DataControlRowType.DataRow:
// build one row in this column
Label l = new Label();
switch (dataType)
{
case "DateTime":
l.CssClass = "ReportNoWrap";
break;
case "Double":
hc = (DataControlFieldCell)container;
hc.CssClass = l.CssClass = "ReportNoWrapRightJustify";
break;
case "Int16":
case "Int32":
hc = (DataControlFieldCell)container;
hc.CssClass = l.CssClass = "ReportNoWrapRightJustify";
break;
case "String":
l.CssClass = "ReportNoWrap";
break;
}
// register an event handler to perform the data binding
l.DataBinding += new EventHandler(this.l_DataBinding);
container.Controls.Add(l);
break;
default:
break;
}
}
As you'd expect, the event handler you set up for databinding gets called when data is bound to the GridView. In this case, I'm going to use this event handler to do some formatting of the bound data:

private void l_DataBinding(Object sender, EventArgs e)
{
// get the control that raised this event
Label l = (Label)sender;
// get the containing row
GridViewRow row = (GridViewRow)l.NamingContainer;
// get the raw data value and make it pretty
string RawValue =
DataBinder.Eval(row.DataItem, columnName).ToString();
switch (dataType)
{
case "DateTime":
l.Text = String.Format("{0:d}", DateTime.Parse(RawValue));
break;
case "Double":
l.Text = String.Format("{0:###,###,##0.00}",
Double.Parse(RawValue));
break;
case "Int16":
case "Int32":
l.Text = RawValue;
break;
case "String":
l.Text = RawValue;
break;
}
}
The last thing in my template class is a little helper method that's used in displaying column headers. Here I'm making an assumption about naming conventions in my database - that column names are all CamelCase, and that I'd prefer to display these on the GridView interface as individual words broken at the obvious points.

// helper method to convert CamelCaseString to Camel Case String
// by inserting spaces
private string BreakCamelCase(string CamelString)
{
string output = string.Empty;
bool SpaceAdded = true;

for (int i = 0; i < spaceadded =" false;" spaceadded =" true;">

The Test Page

To test this, I banged together a simple ASP.NET page consisting of three controls:

A TextBox control named txtQuery
A Button control named btnDisplay
A GridView control named grdMain. The GridView control has its AutoGenerateColumns property set to False.

The idea is simple: when the user clicks the button, I'll build a new DataTable based on whatever text is entered in the TextBox (so I'm depending on the user to enter a valid SQL query; naturally, in a production application, you'd want to do some error checking!). Then the code will walk through all of the columns of the DataTable and add one dynamic column to the GridView for each DataTable column. Here's how it looks in code:

protected void btnDisplay_Click(object sender, EventArgs e) {     // create new DataTable from user input     string connectionString =          "Data Source=localhost;Initial Catalog=AdventureWorks;"         + "Integrated Security=True";     SqlConnection conn;     conn = new SqlConnection(connectionString);     DataTable dtReport = new DataTable();     SqlCommand cmd = new SqlCommand(txtQuery.Text);     cmd.CommandType = CommandType.Text;     cmd.Connection = conn;     SqlDataAdapter da = new SqlDataAdapter();     da.SelectCommand = cmd;     da.Fill(dtReport);      // clear any existing columns     grdMain.Columns.Clear();      // walk the DataTable and add columns to the GridView     for (int i = 0; i < tf =" new" itemtemplate ="              new" headertemplate ="              new" datasource =" dtReport;" visible =" true;">

The only tricky part is the little bit of plumbing that actually hooks the template class up to the GridView. This is accomplished by creating a new TemplateField object, and telling the TemplateField what template to use for its ItemTemplate and HeaderTemplate (you can set other templates as well, such as the AlternatingItemTemplate and FooterTemplate, and they need not all point to the same template class).
Figure 1 shows the whole thing in action. This version isn't excessively pretty because I stripped it down to just the essentials, but it demonstrates enough that you should be able to add your own formatting back in when you need it.

Where Do You Go From Here?

This technique can be extremely powerful when you want to use some of the built-in services of the GridView framework (such as the overall databinding and ability to set cell foreground and background colors) and yet maintain close control over your data. While I can't share much code from our production application with you, I can indicate a couple of the areas where we pushed this technique even further. First, depending on the nature of your data, it may make sense to build special cases within your code to handle particular columns. For example, we've also implemented a hyperlink template column that accepts both text to display and a URL to link to:

public class GridViewHyperlinkTemplate : ITemplate {     private DataControlRowType templateType;     private string columnName;     private string url;     private string text;      public GridViewHyperlinkTemplate(DataControlRowType type,          string colname, string URL, string Text)     {         templateType = type;         columnName = colname;         url = URL;         text = Text;     }      public void InstantiateIn(System.Web.UI.Control container)     {         switch (templateType)         {             case DataControlRowType.Header:                 Literal lc = new Literal();                 lc.Text = "" + columnName+ "";                  container.Controls.Add(lc);                 break;             case DataControlRowType.DataRow:                 HyperLink hl = new HyperLink();                 hl.Target = "_blank";                 hl.CssClass = "ReportNoWrap";                 hl.DataBinding += new EventHandler(this.hl_DataBinding);                  container.Controls.Add(hl);                 break;             default:                 break;         }     }      private void hl_DataBinding(Object sender, EventArgs e)     {         HyperLink hl = (HyperLink)sender;         GridViewRow row = (GridViewRow)hl.NamingContainer;         hl.NavigateUrl = DataBinder.Eval(row.DataItem, url).ToString();         hl.Text = DataBinder.Eval(row.DataItem, text).ToString();     }  }

Note that the data binding code for this template sets both the Text and the NavigateUrl of the Hyperlink control. We use this template in some cases where we can recognize patterns in the underlying SQL Server data thanks to naming conventions in our data columns:

for (int i = 0; i < columnname ="=" urlfound =" true;" tf =" new" itemtemplate ="              new" headertemplate ="              new" columnname ="=">

The other thing to note is that you may also want to get specific formatting on a row-by-row as well as a column-by-column basis. In this case, don't spend a lot of time barking up the template tree! Instead, you'll need to dig into the RowDataBound event of the GridView.
The built-in GridView with automatic column creation can probably handle 95% of your data display needs. But for the other 5%, it's nice to know that these powerful techniques exist. Microsoft's designers didn't think of everything, but in ASP.NET 2.0 they did a lot of work to expose the functionality we need to extend the basic framework, and it's certainly made life a lot easier for those of us working with Web applications.
Click here to download the code.