You can have a StreamWriter object as the return value from calling the method File.CreateText(). You can also create a StreamWriter object using one of its constructor overloads. The following example illustrates using a StreamWriter object with a FileStream object to write characters to the file aFile.txt
using System;
using System.IO;
namespace MyStreams
{
class Class1
{
public static void Main()
{
try
{
FileStream fs = new FileStream
("aFile.txt",FileMode.Create,
FileAccess.ReadWrite,FileShare.None);
string[] strings = {"C#", "ASP.NET", "XML"};
using(StreamWriter sw = new StreamWriter(fs))
{
Console.WriteLine("This StreamWriter instance uses {0}
to write to the file", sw.BaseStream);
Console.WriteLine("The Property sw.Encoding returns:
{0}",sw.Encoding);
sw.WriteLine("www.aspfree.com");
sw.WriteLine("contains many useful articles");
sw.WriteLine("on many different technologies like {0},
{1} and {2}", strings);
}
Console.WriteLine("Data has been written to the file");
Console.ReadLine();
}
catch(IOException ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
Wednesday, March 28, 2007
C# Delegates Explained
This may be the first time you've read about this new .NET type. A delegate is an object that refers to a static method or an instance method. In this article I discuss what delegates are, how you can create and use them, and how the C# compiler saves us time by generating the delegate's class. We will also look at the MSIL code, talk about multicast delegates and provide callback methods through the use of delegates.
A delegate is an object that is created to refer to a static method or an instance method, and then used to call this method. To start off, you create a new delegate type in a different way than you create any other class. You use the delegate keyword as in the following statement.
public delegate int DelegateToMethod(int x, int y);
It seems unusual I know, but I will explain how it's done. Let's take a look at the very first example that explains how to use a delegate.
The First Delegate Example
Copy the following code into your VS.NET class file and run the project.
using System;
namespace Delegates
{
public delegate int DelegateToMethod(int x, int y);
public class Math
{
public static int Add(int first, int second)
{
return first + second;
}
public static int Multiply(int first, int second)
{
return first * second;
}
public static int Divide(int first, int second)
{
return first / second;
}
}
public class DelegateApp
{
public static void Main()
{
DelegateToMethod aDelegate = new DelegateToMethod(Math.Add);
DelegateToMethod mDelegate = new DelegateToMethod(Math.Multiply);
DelegateToMethod dDelegate = new DelegateToMethod(Math.Divide);
Console.WriteLine("Calling the method Math.Add() through the aDelegate object");
Console.WriteLine(aDelegate(5,5));
Console.WriteLine("Calling the method Math.Multiply() through the mDelegate object");
Console.WriteLine(mDelegate(5,5));
Console.WriteLine("Calling the method Math.Divide() through the dDelegate object");
Console.WriteLine(dDelegate(5,5));
Console.ReadLine();
}
}
}
When you run the above code you will get the following:
Let's explain what's going on in this example step-by-step. We have defined a new delegate type using the statement
public delegate int DelegateToMethod(int x, int y);
You are used to defining a new class using the class keyword, then an identifier followed by {, then implementation in the form of methods, properties and fields followed by }. The case is different with delegates. When we define a new delegate type (like DelegateToMethod) the C# compiler generates a class called DelegateToMethod that derives the System.MultipcastDelegate as follows:
public sealed class DelegateToMethod : System.MulticastDelegate
{
public DelegateToMethod(object target, int method);
public virtual void Invoke(int x, int y);
public virtual IAsyncResult BeginInvoke(int x, int y,
AsyncCallback callback, object obj);
public virtual void EndInvoke(IAsyncResult result);
}
The Constructor method of this class takes two arguments. The first is an object reference of the type that defined the instance method that the delegate refers to, and the second is an int value of the function pointer to the method that the delegate encapsulates.
The Invoke() method has the same signature as our delegate declaration. This method is used to call the delegate's encapsulated method. Note that when we defined the delegate we provided a signature for the method that can be encapsulated. In other words, the delegate can't refer to a method with a different signature than the one that it is created with. The BeginInvoke() and EndInvoke() provide asynchronous calls, which are beyond the scope of this article.
The C# compiler generates the sealed class with the four virtual methods, but it doesn't generate any implementation for those methods because they have to be implemented by the Common Language Runtime. So up to the point we have discussed, the folks at Microsoft saved us a lot of time by providing the delegate keyword which we can use to generate a class based on the System.MulticastDelegate. Let's continue our example.
The Math class contains three simple methods (Add, Multiply and Divide) that accept two int values and return an int value. Note that those are static methods. The DelegateApp class creates three DelegateToMethod objects as shown next:
DelegateToMethod aDelegate = new DelegateToMethod(Math.Add);
DelegateToMethod mDelegate = new DelegateToMethod(Math.Multiply);
DelegateToMethod dDelegate = new DelegateToMethod(Math.Divide);
Those three statements create three delegate objects. I think that the issue that would confuse someone is illustrated in the following screen shot:
As you can see, the signature of the Constructor method is not shown; instead, the signature of the method that can be encapsulated by the delegate is shown, or we can say the signature of the delegate. Any method that accepts two int values can return an int. We can look at a delegate as a type-safe function pointer which means that the parameter list and the return type are known.
We create a delegate object using the new operator and pass it the method to be encapsulated. Note that we have said that the generated class' constructor is passed two parameters, and because the passed methods are static, the first parameter will be null value (if it was an instance method it would be the object reference that defined the method instead of the null value).
We have said that in order to call the delegate's encapsulated method we need to call the delegate's Invoke() method. Actually, we can't call this method directly; instead we use the object reference and pass it the arguments as we did in the above code. Take a look again:
Console.WriteLine(aDelegate(5,5));
The statement calls the encapsulated method (which is Math.Add) and passes the parameters as arguments to the method, which returns the value 10.
Put simply, to use a delegate:
Define a new type that inherits from System.MulticastDelegate class and provide the signature of the methods that can be encapsulated by the new type.
public delegate int DelegateToMethod(int x, int y);
Create an instance or static method that has the same signature as defined by the new delegate type.
public static int Add(int first, int second)
{
return first + second;
}
Create a new delegate object using the new operator and pass the method as a parameter to it.
DelegateToMethod aDelegate = new DelegateToMethod(Math.Add);
Invoke the delegate object and pass the arguments to it which in turn calls the referenced method.
Console.WriteLine(aDelegate(5,5));
A delegate is an object that is created to refer to a static method or an instance method, and then used to call this method. To start off, you create a new delegate type in a different way than you create any other class. You use the delegate keyword as in the following statement.
public delegate int DelegateToMethod(int x, int y);
It seems unusual I know, but I will explain how it's done. Let's take a look at the very first example that explains how to use a delegate.
The First Delegate Example
Copy the following code into your VS.NET class file and run the project.
using System;
namespace Delegates
{
public delegate int DelegateToMethod(int x, int y);
public class Math
{
public static int Add(int first, int second)
{
return first + second;
}
public static int Multiply(int first, int second)
{
return first * second;
}
public static int Divide(int first, int second)
{
return first / second;
}
}
public class DelegateApp
{
public static void Main()
{
DelegateToMethod aDelegate = new DelegateToMethod(Math.Add);
DelegateToMethod mDelegate = new DelegateToMethod(Math.Multiply);
DelegateToMethod dDelegate = new DelegateToMethod(Math.Divide);
Console.WriteLine("Calling the method Math.Add() through the aDelegate object");
Console.WriteLine(aDelegate(5,5));
Console.WriteLine("Calling the method Math.Multiply() through the mDelegate object");
Console.WriteLine(mDelegate(5,5));
Console.WriteLine("Calling the method Math.Divide() through the dDelegate object");
Console.WriteLine(dDelegate(5,5));
Console.ReadLine();
}
}
}
When you run the above code you will get the following:
Let's explain what's going on in this example step-by-step. We have defined a new delegate type using the statement
public delegate int DelegateToMethod(int x, int y);
You are used to defining a new class using the class keyword, then an identifier followed by {, then implementation in the form of methods, properties and fields followed by }. The case is different with delegates. When we define a new delegate type (like DelegateToMethod) the C# compiler generates a class called DelegateToMethod that derives the System.MultipcastDelegate as follows:
public sealed class DelegateToMethod : System.MulticastDelegate
{
public DelegateToMethod(object target, int method);
public virtual void Invoke(int x, int y);
public virtual IAsyncResult BeginInvoke(int x, int y,
AsyncCallback callback, object obj);
public virtual void EndInvoke(IAsyncResult result);
}
The Constructor method of this class takes two arguments. The first is an object reference of the type that defined the instance method that the delegate refers to, and the second is an int value of the function pointer to the method that the delegate encapsulates.
The Invoke() method has the same signature as our delegate declaration. This method is used to call the delegate's encapsulated method. Note that when we defined the delegate we provided a signature for the method that can be encapsulated. In other words, the delegate can't refer to a method with a different signature than the one that it is created with. The BeginInvoke() and EndInvoke() provide asynchronous calls, which are beyond the scope of this article.
The C# compiler generates the sealed class with the four virtual methods, but it doesn't generate any implementation for those methods because they have to be implemented by the Common Language Runtime. So up to the point we have discussed, the folks at Microsoft saved us a lot of time by providing the delegate keyword which we can use to generate a class based on the System.MulticastDelegate. Let's continue our example.
The Math class contains three simple methods (Add, Multiply and Divide) that accept two int values and return an int value. Note that those are static methods. The DelegateApp class creates three DelegateToMethod objects as shown next:
DelegateToMethod aDelegate = new DelegateToMethod(Math.Add);
DelegateToMethod mDelegate = new DelegateToMethod(Math.Multiply);
DelegateToMethod dDelegate = new DelegateToMethod(Math.Divide);
Those three statements create three delegate objects. I think that the issue that would confuse someone is illustrated in the following screen shot:
As you can see, the signature of the Constructor method is not shown; instead, the signature of the method that can be encapsulated by the delegate is shown, or we can say the signature of the delegate. Any method that accepts two int values can return an int. We can look at a delegate as a type-safe function pointer which means that the parameter list and the return type are known.
We create a delegate object using the new operator and pass it the method to be encapsulated. Note that we have said that the generated class' constructor is passed two parameters, and because the passed methods are static, the first parameter will be null value (if it was an instance method it would be the object reference that defined the method instead of the null value).
We have said that in order to call the delegate's encapsulated method we need to call the delegate's Invoke() method. Actually, we can't call this method directly; instead we use the object reference and pass it the arguments as we did in the above code. Take a look again:
Console.WriteLine(aDelegate(5,5));
The statement calls the encapsulated method (which is Math.Add) and passes the parameters as arguments to the method, which returns the value 10.
Put simply, to use a delegate:
Define a new type that inherits from System.MulticastDelegate class and provide the signature of the methods that can be encapsulated by the new type.
public delegate int DelegateToMethod(int x, int y);
Create an instance or static method that has the same signature as defined by the new delegate type.
public static int Add(int first, int second)
{
return first + second;
}
Create a new delegate object using the new operator and pass the method as a parameter to it.
DelegateToMethod aDelegate = new DelegateToMethod(Math.Add);
Invoke the delegate object and pass the arguments to it which in turn calls the referenced method.
Console.WriteLine(aDelegate(5,5));
Building a complete COM+ Server component using C# and .NET
To develop .NET managed components that can be configured to function under the COM+ Runtime, you need to provide these components with numerous attributes defined in the System.EnterpriseServices namespace. To start off, each .NET class that's supposed to run under COM+ needs to derive from the System.ServicedComponent class. This base class provides default implementations of the classic MTS/COM+ interface IObjectControl - Activate(), Deactivate(), and CanBePooled(). You can override the default implementations if you wish to do so, just as will be seen shortly.
Once any number of COM+ centric attributes are added to the .NET component, the assembly will have to be compiled. However, to place this assembly under the control of COM+, a new utility (regsvcs.exe) will have to be used as we will see soon. In addition to installing the component into the COM+ catalog, this utility also provides a lot of other services that we shall soon see.
Finally, for the COM+ Surrogate (dllhost.exe) to locate your assembly and to host it in a given activity, it must be able to locate your binary. Therefore, you should install your assembly into the system's Global Assembly Cache (GAC).
The various steps that are involved in creating a COM+ Server Component using C# and the .NET Framework are as follows (I'm going to assume you're using the VS.NET IDE):
Create a Visual C# - Class Library project
Generate a Key-Value pair to use when deploying your Shared Assembly
Configure your Project Property Pages with the right information
Develop the AccountManager.cs library
Modify the generated AssemblyInfo.cs to add the right assembly information
Build the Project Files
Deploy the component as a Shared Assembly, and Configure the Assembly in the COM+ Catalog
The BookKeeper Module
My goal is to simplify illustration of a typical COM+ serviced component development process. I am therefore, in this article, going to reuse the BookKeeper example for all database operations. As a result, all our data is going to be maintained in an XML datastore!!! I had used the BookKeeper example in an earlier article to illustrate ADO.NET's disconnected operation facility - the DataSet. To refresh, the DataSet facilitates the client to manipulate and update a local copy of any number of related tables while still disconnected from the data source and submit the modified data back for processing using a related data adapter at a later point in time.
The AccountManager Module
Our hypothetical AccountManager Module (that we will build in this article), is actually a COM+ Server component that performs just a couple of functions. It is the module that manages creation and deletion of accounts (Checking accounts or Savings accounts) for a Large Commercial Bank Project. It offers no other services except "Create Account", and "Delete Account".
1. Create a Visual C# - Class Library project
Create a new Visual C# Class Library project. Remember that the COM+ Runtime can only host types contained in a DLL.
2. Generate a Key-Value pair to use when deploying your Shared Assembly
Shared Assemblies are those that can be used by any client application, such as a system DLL that every process in the system can use. Unlike private-assemblies, shared assemblies must be published or registered in the system's Global Assembly Cache (GAC). As soon as they are registered in the GAC, they act as system components. An essential requirement for GAC registration is that the component must possess originator and version information. In addition to other metadata information, these two items allow multiple versions of the same component to be registered and executed on the same machine. Unlike Classic COM, we don't have to store any information in the system registry for clients to use these shared assemblies.
There are three general steps to registering shared assemblies in the GAC:
The Shared Name (sb.exe) utility should be used to obtain the public/private key pair. This utility generates a random key pair value, and stores it in an output file - for example, AccountManager.key.
Build the assembly with an assembly version number and the key information in the AccountManager.key
Using the .NET Global Assembly Cache (gacutil.exe) utility, register the assembly in the GAC.
The assembly now becomes a shared assembly and can be used by any client in the system.
Therefore, as a first step, use the Shared Name Utility to obtain a public/private key pair and store it in a file (AccountManager.key, in this case) as shown below.
Command Prompt
C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager>sn -k AccountManager.key
Microsoft (R) .NET Framework Strong Name Utility Version 1.0.2914.16
Copyright (C) Microsoft Corp. 1998-2001. All rights reserved.
Key pair written to AccountManager.key
C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager>
The -k option generates the random key pair and saves the key information in the AccountManager.key file. We use this file as input when we build our Shared Assemblies.
3. Configure your Project Property Pages with the right information
Configure the Project Properties with the right information. Make sure you specify the Assembly Name that you want for the Assembly. Specifically, move to the General tab, and in the Wrapper Assembly Key File area, enter the key file to use. In this case, it is AccountManager.key.
Move to the Reference Path Properties area, and select the directory that contains the BookKeeper executable.
Go to "Project Dependancy" and select the BookKeeper as a dependancy for this project. This means the BookKeeper project has to be compiled before compiling this project.
To the AccountManager project files, also add the BookKeeper.cs, and the AccountKey.cs files from the BookKeeper project.
4. Develop the AccountManager.cs library
Transactions
To develop a .NET class that supports transactions, here's what you have to do:
The class must derive from the System.ServicedComponent class to exploit COM+ Services as shown in Line 94.
The class must be created with the correct Transaction attribute such as Transaction (TransactionOption.Required) as shown in Line 78.
Besides this, you can use the System.EnterpriseServices.ContextUtil class to obtain information about the COM+ object context as shown in Line 138. This class exposes important methods of COM+ like SetComplete() and SetAbort(), and IsCallerInRole(), and important COM+ properties like IsInTransaction, and MyTransactionVote. Additionally, while it's not necessary to specify COM+ Application installation options, you can always specify what you want. Notice that we use attributes to specify a number of things.
In the AccountManager.create() method, we simply call ContextUtil.SetComplete() - Line 138 -when we've successfully created a new account into our database. If something has gone wrong during the process, we will vote to abort the transaction by calling ContextUtil.SetAbort() as shown on Line 142.
Instead of calling ContextUtil.SetComplete() and ContextUtil.SetAbort() explicitly, we can also use the AutoComplete( true ) attribute, as shown on line 165 which is conceptually equivalent to the previously shown AccountManager.create() method.
using System;
using System.Runtime.InteropServices;
using System.EnterpriseServices;
// Include the following for the Trace class
using System.Diagnostics;
// Include the following for Windows Message Box
using System.Windows.Forms;
// Include the BookKeeper namespace
using BookKeeper;
namespace Bank {
///////////////////////////////////////////////////////////////
///
/// The Account Manager interface
///
///
/// This interface defines create and delete methods to
/// add or delete Bank Accounts
///
///////////////////////////////////////////////////////////////
/// Indicate whether a managed interface is dual, IDispatch or
/// IUnknown based when exposed to COM
[ InterfaceTypeAttribute( ComInterfaceType.InterfaceIsDual ) ]
public interface IAccountManager {
///
/// The create method
///
///
/// Method used to create a new Bank account
///
/// Either Checking or Savings
/// Customers who own this account
/// Initial Deposit
///
int create (AccountType type, string[] customerNames, float startingBalance);
///
/// The delete method
///
///
/// Method used to delete and existing Bank account
///
/// the Account Number
///true if Account deleted, false if not
bool delete (int accountKey);
}
///////////////////////////////////////////////////////////////
///
/// AccountManager used to create new Accounts or delete accounts.
///
///////////////////////////////////////////////////////////////
/// Specify a name for your serviced component
[ ProgId( "COM+ Bank Server Account Manager" ) ]
/// Add content to hosting COM+ App's description field
[ Description( "COM+ Bank Server Account Manager" ) ]
/// Configure component's Transaction Option
[ Transaction( TransactionOption.Required ) ]
/// Configure component's object pooling
[ ObjectPooling( MinPoolSize = 5, MaxPoolSize = 10, CreationTimeout = 20 ) ]
/// Specify COM+ Context Attributes
[ MustRunInClientContext( false ) ]
/// Enable event tracking
[ EventTrackingEnabled( true ) ]
/// Enable JITA for the component
[ JustInTimeActivation( true ) ]
/// Enable Construction String Support for the component
[ ConstructionEnabled( Enabled=true, Default="Gopalan's Bank Server" ) ]
/// Configure activity-based Synchronization for the component
[ Synchronization( SynchronizationOption.Required ) ]
/// Indicate the type of class interface that will be generated for this class
[ ClassInterface( ClassInterfaceType.AutoDual ) ]
public class AccountManager : ServicedComponent, IAccountManager {
///
/// Public No-argument Default Constructor
///
public AccountManager() {
MessageBox.Show ("Bank::AccountManager() invoked...");
}
/////////////////////////////////////////////////////////////////////////
/// The Following methods support core functionality required of the
/// AccountManager component and implement the IAccountManager interface
/////////////////////////////////////////////////////////////////////////
///
/// The create method
///
///
/// Method used to create a new Bank account
///
/// Either Checking or Savings
/// Customers who own this account
/// Initial Deposit
///
/// Add content to hosting COM+ App's description field
[ Description( "Creates a new account for the Bank Server" ) ]
public int create (AccountType type, string[] customerNames, float startingBalance) {
MessageBox.Show ("Bank::create() invoked...");
AccountKey key = null;
try {
// Create the BookKeeper class
BookKeeper.BookKeeper keeper = new BookKeeper.BookKeeper();
if (null != keeper) {
// Call the BookKeeper to create a new Bank Account
key = keeper.createAccount (type, customerNames, startingBalance);
// Clean-up the BookKeeper object
keeper.Dispose();
}
else {
throw new Exception ( "BookKeeper Object could not be created." );
}
// Since everything went well, commit the changes
ContextUtil.SetComplete();
}
catch (Exception exception) {
// An Error occured, so rollback the changes
ContextUtil.SetAbort();
// Trace the current COM+ context ID (its GUID) to the output window
// use the ContextId static property of ContextUtil
Guid contextID = ContextUtil.ContextId;
String traceMessage = "Context ID is " + contextID.ToString();
Trace.WriteLine (traceMessage.ToString ());
MessageBox.Show (exception.ToString (), "Bank::create()");
}
return key.Key;
}
///
/// The delete method
///
///
/// Method used to delete and existing Bank account
///
/// the Account Number
///true if Account deleted, false if not
/// Take advantage of COM+'s method auto-deactivation
[ AutoComplete( true ) ]
/// Add content to hosting COM+ App's description field
[ Description( "Deletes an existing account from the Bank Server" ) ]
public bool delete (int accountKey) {
bool result = false;
MessageBox.Show ("Bank::delete() invoked...", "Key Value = "+accountKey);
/*
try {
// Programming Role based security
SecurityCallContext callContext;
callContext = SecurityCallContext.CurrentCall;
string caller = callContext.DirectCaller.AccountName;
bool isInRole = callContext.IsCallerInRole ( "Manager" );
if (false == isInRole) {
throw new Exception ( "Only Managers can delete Customers" );
}
*/
BookKeeper.BookKeeper keeper = new BookKeeper.BookKeeper();
if (null != keeper) {
AccountKey key = new AccountKey();
key.Key = accountKey;
result = keeper.deleteAccount (key);
keeper.Dispose();
}
else {
throw new Exception ( "BookKeeper Object could not be created." );
}
/*}
catch (Exception exception) {
Guid contextID = ContextUtil.ContextId;
String traceMessage = "Context ID is " + contextID.ToString();
Trace.WriteLine (traceMessage.ToString ());
MessageBox.Show (exception.ToString (), "Bank::delete()");
}*/
return result;
}
/////////////////////////////////////////////////////////////////////////
/// All the Following methods support overriding functionality required for
/// implementing some of the COM+ support interfaces like IObjectConstruct.
/////////////////////////////////////////////////////////////////////////
///
/// The method is called after the component's constructor
/// and is passed in the user specific constructionString
///
///
override public void Construct (string constructionString) {
MessageBox.Show ("Bank::Construct() invoked...");
MessageBox.Show (constructionString, "Construction String");
}
///
/// Do context specific initialization in this method
///
override public void Activate () {
MessageBox.Show ("Bank::Activate() invoked...");
}
///
/// Do context specific cleanup in this method
///
override public void Deactivate () {
MessageBox.Show ("Bank::Deactivate() invoked...");
}
///
/// Object Pooling support method
///
///true if pooling is supported, false if not
override public bool CanBePooled () {
MessageBox.Show ("Bank::CanBePooled() invoked...");
return true;
}
}
}
Object Pooling
Object Pooling is a feature that was introduced in COM+, but was missing in MTS. Object Pooling allows you to minimize the use of system resources, by pooling objects that support transactions but are expensive to create. This improves performance and helps system scalability. If you want to support object pooling in you components, you need to derive from the System.ServicedComponent class, and override any of the Activate(), Deactivate(), and CanBePooled() methods, and specify object pooling requirements in an ObjectPooling attribute as shown on Line 80. You can take advantage of the Activate() and Deactivate() methods to perform the appropriate initialization and cleanup. The CanBePooled() method is used to tell COM+ whether this object can be pooled or not. This way, you can provide any expensive object-creation functionality in the constructor of the component.
Since our COM+ components support Object Pooling, The COM+ runtime activates and deactivates them as required. After each Client call has been serviced, it puts the component object back into the object pool. As soon as a new Client call arrives, it picks the same component object back from the pool to service the new request.
5. Modify the generated AssemblyInfo.cs to add the right assembly information
You provide the compiler with your assembly information in an assembly file called AssemblyInfo.cs. The assembly information file is compiled with the rest of the project's source files. The information is in the form of assembly attributes - directives to the compiler on the information to embed in the assembly.
AssemblyInfo.cs
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
using System.Reflection;
using System.Runtime.CompilerServices;
using System.EnterpriseServices;
//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("AccountManager for Bank")]
[assembly: AssemblyDescription("Creates and Deletes Accounts for the Bank")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("eCommWare Corporation")]
[assembly: AssemblyProduct("COM+ Bank Server")]
[assembly: AssemblyCopyright("(c) 2001, Gopalan Suresh Raj. All Rights Reserved.")]
[assembly: AssemblyTrademark("Web Cornucopia")]
[assembly: AssemblyCulture("en-US")]
//
// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("1.0.0.0")]
//
// In order to sign your assembly you must specify a key to use. Refer to the
// Microsoft .NET Framework documentation for more information on assembly signing.
//
// Use the attributes below to control which key is used for signing.
//
// Notes:
// (*) If no key is specified, the assembly is not signed.
// (*) KeyName refers to a key that has been installed in the Crypto Service
// Provider (CSP) on your machine. KeyFile refers to a file which contains
// a key.
// (*) If the KeyFile and the KeyName values are both specified, the
// following processing occurs:
// (1) If the KeyName can be found in the CSP, that key is used.
// (2) If the KeyName does not exist and the KeyFile does exist, the key
// in the KeyFile is installed into the CSP and used.
// (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
// When specifying the KeyFile, the location of the KeyFile should be
// relative to the project output directory which is
// %Project Directory%\obj\. For example, if your KeyFile is
// located in the project directory, you would specify the AssemblyKeyFile
// attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
// (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
// documentation for more information on this.
//
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("AccountManager.key")]
[assembly: AssemblyKeyName("")]
[assembly: ApplicationName( "COM+ Bank Server Account Manager" )]
[assembly: ApplicationActivation( ActivationOption.Server )]
In particular, pay attention to the fact that we specify a version number for this library using the AssemblyVersion attribute and also specify the assembly key file using the AssemblyKeyFile attribute. The ApplicationName attribute is self-explanatory. However, the attribute of special interest is the ApplicationActivation attribute. As you may know, MTS and COM+ applications may either be hosted as a Library (e.g., Activated in the Caller's process) or Server (e.g., Activated in a new instance of dllhost.exe). The default attribute is to configure your COM+ application as a Library. Here we want to explicitly set the activation option to be specified as ActivationOption.Server.
6. Build the Project Files
Build the files that make up the project.
------ Rebuild All started: Project: BookKeeper, Configuration: Debug .NET ------
Preparing resources...
Updating references...
Performing main compilation...
Build complete -- 0 errors, 0 warnings
Building satellite assemblies...
------ Rebuild All started: Project: AccountManager, Configuration: Debug .NET ------
Preparing resources...
Updating references...
Performing main compilation...
Build complete -- 0 errors, 0 warnings
Building satellite assemblies...
---------------------- Done ----------------------
Rebuild All: 2 succeeded, 0 failed, 0 skipped
7. Deploy the component as a Shared Assembly and Configure it in the COM+ Catalog
After you've built the assembly, you can use the .NET Global Assembly Cache (GAC) utility to register this assembly into the GAC as shown below.
Command Prompt
C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager\bin\Debug>gacutil /i Bank.dll
Microsoft (R) .NET Global Assembly Cache Utility. Version 1.0.2914.16
Copyright (C) Microsoft Corp. 1998-2001. All rights reserved.
Assembly successfully added to the cache
C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager\bin\Debug>regsvcs /fc Bank.dll
RegSvcs - .NET Services Installation Utility Version 1.0.2914.16
Copyright (C) Microsoft Corp. 2000-2001. All rights reserved.
Installed Assembly:
Assembly: C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager\bin\Debug\Bank.dll
Application: COM+ Bank Server Account Manager
TypeLib: c:\myprojects\cornucopia\complus\bankserver\accountmanager\bin\debug\Bank.tlb
C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager\bin\Debug>
Successful registration against the cache turns this component into a shared assembly. A version of this component is copied into the GAC so that even if you delete this file locally, you will still be able to run your client program.
Configuring our Assembly in the COM+ Catalog
Configuring a .NET assembly in the COM+ Catalog means, you need to generate a COM Type Library (tlbexp.exe), and register the type in the system registry (regasm.exe). You also have to make sure that you enter the right information into the COM+ Catalog (RegDB). Instead of using all these tools individually, the .NET SDK provides an additional tool called the Register Services utility (regsvcs.exe). This utility simplifies the process by making sure that all required details are taken care of in a single step. It performs the following functions:
Our Assembly is loaded into memory
Out Assembly is registered (e.g., just like using regasm.exe)
A COM Type Library (.tlb file) is generated and registered (e.g., just like using tlbexp.exe)
The generated COM Type Library is installed in the specified COM+ Application
Our Components are configured according to the attributes that are specified in the type definitions
If you notice carefully, when we use the regsvcs.exe utility, we specify the /fc option (find or create) to instruct the tool to build a new COM+ application if one does not currently exist.
The Component Services Explorer
Once you have done all this, you can open up the Windows 2000 Component Services Explorer and discover that your .NET Assembly is now recognized as valid COM+ Application.
While you explore the various property windows for this COM+ Application, you realize that the various attributes that you specified in the C# class have been used to configure our component in the COM+ Catalog. Right Click the Component and check out the Activation tab for example as shown in the screen shot below.
The above settings have been automatically configured based on the following class-level attributes that you set programmatically in your original C# class from lines 82-88 in the source code above.
/// Configure component's object pooling
[ ObjectPooling( MinPoolSize = 5, MaxPoolSize = 10, CreationTimeout = 20 ) ]
/// Specify COM+ Context Attributes
[ MustRunInClientContext( false ) ]
/// Enable event tracking
[ EventTrackingEnabled( true ) ]
/// Enable JITA for the component
[ JustInTimeActivation( true ) ]
/// Enable Construction String Support for the component
[ ConstructionEnabled( Enabled=true, Default="Gopalan's Bank Server" ) ]
Now you need to build a client application that can access this COM+ Server component.
Once any number of COM+ centric attributes are added to the .NET component, the assembly will have to be compiled. However, to place this assembly under the control of COM+, a new utility (regsvcs.exe) will have to be used as we will see soon. In addition to installing the component into the COM+ catalog, this utility also provides a lot of other services that we shall soon see.
Finally, for the COM+ Surrogate (dllhost.exe) to locate your assembly and to host it in a given activity, it must be able to locate your binary. Therefore, you should install your assembly into the system's Global Assembly Cache (GAC).
The various steps that are involved in creating a COM+ Server Component using C# and the .NET Framework are as follows (I'm going to assume you're using the VS.NET IDE):
Create a Visual C# - Class Library project
Generate a Key-Value pair to use when deploying your Shared Assembly
Configure your Project Property Pages with the right information
Develop the AccountManager.cs library
Modify the generated AssemblyInfo.cs to add the right assembly information
Build the Project Files
Deploy the component as a Shared Assembly, and Configure the Assembly in the COM+ Catalog
The BookKeeper Module
My goal is to simplify illustration of a typical COM+ serviced component development process. I am therefore, in this article, going to reuse the BookKeeper example for all database operations. As a result, all our data is going to be maintained in an XML datastore!!! I had used the BookKeeper example in an earlier article to illustrate ADO.NET's disconnected operation facility - the DataSet. To refresh, the DataSet facilitates the client to manipulate and update a local copy of any number of related tables while still disconnected from the data source and submit the modified data back for processing using a related data adapter at a later point in time.
The AccountManager Module
Our hypothetical AccountManager Module (that we will build in this article), is actually a COM+ Server component that performs just a couple of functions. It is the module that manages creation and deletion of accounts (Checking accounts or Savings accounts) for a Large Commercial Bank Project. It offers no other services except "Create Account", and "Delete Account".
1. Create a Visual C# - Class Library project
Create a new Visual C# Class Library project. Remember that the COM+ Runtime can only host types contained in a DLL.
2. Generate a Key-Value pair to use when deploying your Shared Assembly
Shared Assemblies are those that can be used by any client application, such as a system DLL that every process in the system can use. Unlike private-assemblies, shared assemblies must be published or registered in the system's Global Assembly Cache (GAC). As soon as they are registered in the GAC, they act as system components. An essential requirement for GAC registration is that the component must possess originator and version information. In addition to other metadata information, these two items allow multiple versions of the same component to be registered and executed on the same machine. Unlike Classic COM, we don't have to store any information in the system registry for clients to use these shared assemblies.
There are three general steps to registering shared assemblies in the GAC:
The Shared Name (sb.exe) utility should be used to obtain the public/private key pair. This utility generates a random key pair value, and stores it in an output file - for example, AccountManager.key.
Build the assembly with an assembly version number and the key information in the AccountManager.key
Using the .NET Global Assembly Cache (gacutil.exe) utility, register the assembly in the GAC.
The assembly now becomes a shared assembly and can be used by any client in the system.
Therefore, as a first step, use the Shared Name Utility to obtain a public/private key pair and store it in a file (AccountManager.key, in this case) as shown below.
Command Prompt
C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager>sn -k AccountManager.key
Microsoft (R) .NET Framework Strong Name Utility Version 1.0.2914.16
Copyright (C) Microsoft Corp. 1998-2001. All rights reserved.
Key pair written to AccountManager.key
C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager>
The -k option generates the random key pair and saves the key information in the AccountManager.key file. We use this file as input when we build our Shared Assemblies.
3. Configure your Project Property Pages with the right information
Configure the Project Properties with the right information. Make sure you specify the Assembly Name that you want for the Assembly. Specifically, move to the General tab, and in the Wrapper Assembly Key File area, enter the key file to use. In this case, it is AccountManager.key.
Move to the Reference Path Properties area, and select the directory that contains the BookKeeper executable.
Go to "Project Dependancy" and select the BookKeeper as a dependancy for this project. This means the BookKeeper project has to be compiled before compiling this project.
To the AccountManager project files, also add the BookKeeper.cs, and the AccountKey.cs files from the BookKeeper project.
4. Develop the AccountManager.cs library
Transactions
To develop a .NET class that supports transactions, here's what you have to do:
The class must derive from the System.ServicedComponent class to exploit COM+ Services as shown in Line 94.
The class must be created with the correct Transaction attribute such as Transaction (TransactionOption.Required) as shown in Line 78.
Besides this, you can use the System.EnterpriseServices.ContextUtil class to obtain information about the COM+ object context as shown in Line 138. This class exposes important methods of COM+ like SetComplete() and SetAbort(), and IsCallerInRole(), and important COM+ properties like IsInTransaction, and MyTransactionVote. Additionally, while it's not necessary to specify COM+ Application installation options, you can always specify what you want. Notice that we use attributes to specify a number of things.
In the AccountManager.create() method, we simply call ContextUtil.SetComplete() - Line 138 -when we've successfully created a new account into our database. If something has gone wrong during the process, we will vote to abort the transaction by calling ContextUtil.SetAbort() as shown on Line 142.
Instead of calling ContextUtil.SetComplete() and ContextUtil.SetAbort() explicitly, we can also use the AutoComplete( true ) attribute, as shown on line 165 which is conceptually equivalent to the previously shown AccountManager.create() method.
using System;
using System.Runtime.InteropServices;
using System.EnterpriseServices;
// Include the following for the Trace class
using System.Diagnostics;
// Include the following for Windows Message Box
using System.Windows.Forms;
// Include the BookKeeper namespace
using BookKeeper;
namespace Bank {
///////////////////////////////////////////////////////////////
///
/// The Account Manager interface
///
///
/// This interface defines create and delete methods to
/// add or delete Bank Accounts
///
///////////////////////////////////////////////////////////////
/// Indicate whether a managed interface is dual, IDispatch or
/// IUnknown based when exposed to COM
[ InterfaceTypeAttribute( ComInterfaceType.InterfaceIsDual ) ]
public interface IAccountManager {
///
/// The create method
///
///
/// Method used to create a new Bank account
///
/// Either Checking or Savings
/// Customers who own this account
/// Initial Deposit
///
int create (AccountType type, string[] customerNames, float startingBalance);
///
/// The delete method
///
///
/// Method used to delete and existing Bank account
///
/// the Account Number
///
bool delete (int accountKey);
}
///////////////////////////////////////////////////////////////
///
/// AccountManager used to create new Accounts or delete accounts.
///
///////////////////////////////////////////////////////////////
/// Specify a name for your serviced component
[ ProgId( "COM+ Bank Server Account Manager" ) ]
/// Add content to hosting COM+ App's description field
[ Description( "COM+ Bank Server Account Manager" ) ]
/// Configure component's Transaction Option
[ Transaction( TransactionOption.Required ) ]
/// Configure component's object pooling
[ ObjectPooling( MinPoolSize = 5, MaxPoolSize = 10, CreationTimeout = 20 ) ]
/// Specify COM+ Context Attributes
[ MustRunInClientContext( false ) ]
/// Enable event tracking
[ EventTrackingEnabled( true ) ]
/// Enable JITA for the component
[ JustInTimeActivation( true ) ]
/// Enable Construction String Support for the component
[ ConstructionEnabled( Enabled=true, Default="Gopalan's Bank Server" ) ]
/// Configure activity-based Synchronization for the component
[ Synchronization( SynchronizationOption.Required ) ]
/// Indicate the type of class interface that will be generated for this class
[ ClassInterface( ClassInterfaceType.AutoDual ) ]
public class AccountManager : ServicedComponent, IAccountManager {
///
/// Public No-argument Default Constructor
///
public AccountManager() {
MessageBox.Show ("Bank::AccountManager() invoked...");
}
/////////////////////////////////////////////////////////////////////////
/// The Following methods support core functionality required of the
/// AccountManager component and implement the IAccountManager interface
/////////////////////////////////////////////////////////////////////////
///
/// The create method
///
///
/// Method used to create a new Bank account
///
/// Either Checking or Savings
/// Customers who own this account
/// Initial Deposit
///
/// Add content to hosting COM+ App's description field
[ Description( "Creates a new account for the Bank Server" ) ]
public int create (AccountType type, string[] customerNames, float startingBalance) {
MessageBox.Show ("Bank::create() invoked...");
AccountKey key = null;
try {
// Create the BookKeeper class
BookKeeper.BookKeeper keeper = new BookKeeper.BookKeeper();
if (null != keeper) {
// Call the BookKeeper to create a new Bank Account
key = keeper.createAccount (type, customerNames, startingBalance);
// Clean-up the BookKeeper object
keeper.Dispose();
}
else {
throw new Exception ( "BookKeeper Object could not be created." );
}
// Since everything went well, commit the changes
ContextUtil.SetComplete();
}
catch (Exception exception) {
// An Error occured, so rollback the changes
ContextUtil.SetAbort();
// Trace the current COM+ context ID (its GUID) to the output window
// use the ContextId static property of ContextUtil
Guid contextID = ContextUtil.ContextId;
String traceMessage = "Context ID is " + contextID.ToString();
Trace.WriteLine (traceMessage.ToString ());
MessageBox.Show (exception.ToString (), "Bank::create()");
}
return key.Key;
}
///
/// The delete method
///
///
/// Method used to delete and existing Bank account
///
/// the Account Number
///
/// Take advantage of COM+'s method auto-deactivation
[ AutoComplete( true ) ]
/// Add content to hosting COM+ App's description field
[ Description( "Deletes an existing account from the Bank Server" ) ]
public bool delete (int accountKey) {
bool result = false;
MessageBox.Show ("Bank::delete() invoked...", "Key Value = "+accountKey);
/*
try {
// Programming Role based security
SecurityCallContext callContext;
callContext = SecurityCallContext.CurrentCall;
string caller = callContext.DirectCaller.AccountName;
bool isInRole = callContext.IsCallerInRole ( "Manager" );
if (false == isInRole) {
throw new Exception ( "Only Managers can delete Customers" );
}
*/
BookKeeper.BookKeeper keeper = new BookKeeper.BookKeeper();
if (null != keeper) {
AccountKey key = new AccountKey();
key.Key = accountKey;
result = keeper.deleteAccount (key);
keeper.Dispose();
}
else {
throw new Exception ( "BookKeeper Object could not be created." );
}
/*}
catch (Exception exception) {
Guid contextID = ContextUtil.ContextId;
String traceMessage = "Context ID is " + contextID.ToString();
Trace.WriteLine (traceMessage.ToString ());
MessageBox.Show (exception.ToString (), "Bank::delete()");
}*/
return result;
}
/////////////////////////////////////////////////////////////////////////
/// All the Following methods support overriding functionality required for
/// implementing some of the COM+ support interfaces like IObjectConstruct.
/////////////////////////////////////////////////////////////////////////
///
/// The method is called after the component's constructor
/// and is passed in the user specific constructionString
///
///
override public void Construct (string constructionString) {
MessageBox.Show ("Bank::Construct() invoked...");
MessageBox.Show (constructionString, "Construction String");
}
///
/// Do context specific initialization in this method
///
override public void Activate () {
MessageBox.Show ("Bank::Activate() invoked...");
}
///
/// Do context specific cleanup in this method
///
override public void Deactivate () {
MessageBox.Show ("Bank::Deactivate() invoked...");
}
///
/// Object Pooling support method
///
///
override public bool CanBePooled () {
MessageBox.Show ("Bank::CanBePooled() invoked...");
return true;
}
}
}
Object Pooling
Object Pooling is a feature that was introduced in COM+, but was missing in MTS. Object Pooling allows you to minimize the use of system resources, by pooling objects that support transactions but are expensive to create. This improves performance and helps system scalability. If you want to support object pooling in you components, you need to derive from the System.ServicedComponent class, and override any of the Activate(), Deactivate(), and CanBePooled() methods, and specify object pooling requirements in an ObjectPooling attribute as shown on Line 80. You can take advantage of the Activate() and Deactivate() methods to perform the appropriate initialization and cleanup. The CanBePooled() method is used to tell COM+ whether this object can be pooled or not. This way, you can provide any expensive object-creation functionality in the constructor of the component.
Since our COM+ components support Object Pooling, The COM+ runtime activates and deactivates them as required. After each Client call has been serviced, it puts the component object back into the object pool. As soon as a new Client call arrives, it picks the same component object back from the pool to service the new request.
5. Modify the generated AssemblyInfo.cs to add the right assembly information
You provide the compiler with your assembly information in an assembly file called AssemblyInfo.cs. The assembly information file is compiled with the rest of the project's source files. The information is in the form of assembly attributes - directives to the compiler on the information to embed in the assembly.
AssemblyInfo.cs
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
using System.Reflection;
using System.Runtime.CompilerServices;
using System.EnterpriseServices;
//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("AccountManager for Bank")]
[assembly: AssemblyDescription("Creates and Deletes Accounts for the Bank")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("eCommWare Corporation")]
[assembly: AssemblyProduct("COM+ Bank Server")]
[assembly: AssemblyCopyright("(c) 2001, Gopalan Suresh Raj. All Rights Reserved.")]
[assembly: AssemblyTrademark("Web Cornucopia")]
[assembly: AssemblyCulture("en-US")]
//
// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("1.0.0.0")]
//
// In order to sign your assembly you must specify a key to use. Refer to the
// Microsoft .NET Framework documentation for more information on assembly signing.
//
// Use the attributes below to control which key is used for signing.
//
// Notes:
// (*) If no key is specified, the assembly is not signed.
// (*) KeyName refers to a key that has been installed in the Crypto Service
// Provider (CSP) on your machine. KeyFile refers to a file which contains
// a key.
// (*) If the KeyFile and the KeyName values are both specified, the
// following processing occurs:
// (1) If the KeyName can be found in the CSP, that key is used.
// (2) If the KeyName does not exist and the KeyFile does exist, the key
// in the KeyFile is installed into the CSP and used.
// (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
// When specifying the KeyFile, the location of the KeyFile should be
// relative to the project output directory which is
// %Project Directory%\obj\
// located in the project directory, you would specify the AssemblyKeyFile
// attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
// (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
// documentation for more information on this.
//
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("AccountManager.key")]
[assembly: AssemblyKeyName("")]
[assembly: ApplicationName( "COM+ Bank Server Account Manager" )]
[assembly: ApplicationActivation( ActivationOption.Server )]
In particular, pay attention to the fact that we specify a version number for this library using the AssemblyVersion attribute and also specify the assembly key file using the AssemblyKeyFile attribute. The ApplicationName attribute is self-explanatory. However, the attribute of special interest is the ApplicationActivation attribute. As you may know, MTS and COM+ applications may either be hosted as a Library (e.g., Activated in the Caller's process) or Server (e.g., Activated in a new instance of dllhost.exe). The default attribute is to configure your COM+ application as a Library. Here we want to explicitly set the activation option to be specified as ActivationOption.Server.
6. Build the Project Files
Build the files that make up the project.
------ Rebuild All started: Project: BookKeeper, Configuration: Debug .NET ------
Preparing resources...
Updating references...
Performing main compilation...
Build complete -- 0 errors, 0 warnings
Building satellite assemblies...
------ Rebuild All started: Project: AccountManager, Configuration: Debug .NET ------
Preparing resources...
Updating references...
Performing main compilation...
Build complete -- 0 errors, 0 warnings
Building satellite assemblies...
---------------------- Done ----------------------
Rebuild All: 2 succeeded, 0 failed, 0 skipped
7. Deploy the component as a Shared Assembly and Configure it in the COM+ Catalog
After you've built the assembly, you can use the .NET Global Assembly Cache (GAC) utility to register this assembly into the GAC as shown below.
Command Prompt
C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager\bin\Debug>gacutil /i Bank.dll
Microsoft (R) .NET Global Assembly Cache Utility. Version 1.0.2914.16
Copyright (C) Microsoft Corp. 1998-2001. All rights reserved.
Assembly successfully added to the cache
C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager\bin\Debug>regsvcs /fc Bank.dll
RegSvcs - .NET Services Installation Utility Version 1.0.2914.16
Copyright (C) Microsoft Corp. 2000-2001. All rights reserved.
Installed Assembly:
Assembly: C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager\bin\Debug\Bank.dll
Application: COM+ Bank Server Account Manager
TypeLib: c:\myprojects\cornucopia\complus\bankserver\accountmanager\bin\debug\Bank.tlb
C:\MyProjects\Cornucopia\COMplus\BankServer\AccountManager\bin\Debug>
Successful registration against the cache turns this component into a shared assembly. A version of this component is copied into the GAC so that even if you delete this file locally, you will still be able to run your client program.
Configuring our Assembly in the COM+ Catalog
Configuring a .NET assembly in the COM+ Catalog means, you need to generate a COM Type Library (tlbexp.exe), and register the type in the system registry (regasm.exe). You also have to make sure that you enter the right information into the COM+ Catalog (RegDB). Instead of using all these tools individually, the .NET SDK provides an additional tool called the Register Services utility (regsvcs.exe). This utility simplifies the process by making sure that all required details are taken care of in a single step. It performs the following functions:
Our Assembly is loaded into memory
Out Assembly is registered (e.g., just like using regasm.exe)
A COM Type Library (.tlb file) is generated and registered (e.g., just like using tlbexp.exe)
The generated COM Type Library is installed in the specified COM+ Application
Our Components are configured according to the attributes that are specified in the type definitions
If you notice carefully, when we use the regsvcs.exe utility, we specify the /fc option (find or create) to instruct the tool to build a new COM+ application if one does not currently exist.
The Component Services Explorer
Once you have done all this, you can open up the Windows 2000 Component Services Explorer and discover that your .NET Assembly is now recognized as valid COM+ Application.
While you explore the various property windows for this COM+ Application, you realize that the various attributes that you specified in the C# class have been used to configure our component in the COM+ Catalog. Right Click the Component and check out the Activation tab for example as shown in the screen shot below.
The above settings have been automatically configured based on the following class-level attributes that you set programmatically in your original C# class from lines 82-88 in the source code above.
/// Configure component's object pooling
[ ObjectPooling( MinPoolSize = 5, MaxPoolSize = 10, CreationTimeout = 20 ) ]
/// Specify COM+ Context Attributes
[ MustRunInClientContext( false ) ]
/// Enable event tracking
[ EventTrackingEnabled( true ) ]
/// Enable JITA for the component
[ JustInTimeActivation( true ) ]
/// Enable Construction String Support for the component
[ ConstructionEnabled( Enabled=true, Default="Gopalan's Bank Server" ) ]
Now you need to build a client application that can access this COM+ Server component.
Tuesday, February 20, 2007
Unsafe Code in C#
C# .net hides most of memory management, which makes it much easier for the developer. Thanks for the Garbage Collector and the use of references. But to make the language powerful enough in some cases in which we need direct access to the memory, unsafe code was invented.
Commonly while programming in the .net framework we don?t need to use unsafe code, but in some cases there is no way not to, such as the following:
* Real-time applications, we might need to use pointers to enhance performance in such applications.
* External functions, in non-.net DLLs some functions requires a pointer as a parameter, such as Windows APIs that were written in C.
* Debugging, sometimes we need to inspect the memory contents for debugging purposes, or you might need to write an application that analyzes another application process and memory.
Unsafe code is mostly about pointers which have the following advantages and disadvantages.
Advantages of Unsafe Code in C#:
* Performance and flexibility, by using pointer you can access data and manipulate it in the most efficient way possible.
* Compatibility, in most cases we still need to use old windows APIs, which use pointers extensively. Or third parties may supply DLLs that some of its functions need pointer parameters. Although this can be done by writing the DLLImport declaration in a way that avoids pointers, but in some cases it?s just much simpler to use pointer.
* Memory Addresses, there is no way to know the memory address of some data without using pointers.
Disadvantages of Unsafe Code in C#:
* Complex syntax, to use pointers you need to go throw more complex syntax than we used to experience in C#.
* Harder to use, you need be more careful and logical while using pointers, miss using pointers might lead to the following:
o Overwrite other variables.
o Stack overflow.
o Access areas of memory that doesn?t contain any data as they do.
o Overwrite some information of the code for the .net runtime, which will surely lead your application to crash.
* Your code will be harder to debug. A simple mistake in using pointers might lead your application to crash randomly and unpredictably.
* Type-safety, using pointers will cause the code to fail in the .net type-safety checks, and of course if your security police don?t allow non type-safety code, then the .net framework will refuse to execute your application.
After we knew all the risks that might face us while using pointer and all the advantages those pointers introduces us of performance and flexibility, let us find now how to use them. The keyword unsafe is used while dealing with pointer, the name reflects the risks that you might face while using it. Let?s see where to place it. We can declare a whole class as unsafe:
unsafe class Class1
{
//you can use pointers here!
}
Or only some class members can be declared as unsafe:
class Class1
{
//pointer
unsafe int * ptr;
unsafe void MyMethod()
{
//you can use pointers here
}
}
The same applies to other members such as the constructor and the properties.
To declare unsafe local variables in a method, you have to put them in unsafe blocks as the following:
static void Main()
{
//can't use pointers here
unsafe
{
//you can declare and use pointer here
}
//can't use pointers here
}
You can?t declare local pointers in a ?safe? method in the same way we used in declaring global pointers, we have to put them in an unsafe block.
static void Main()
{
unsafe int * ptri; //Wrong
}
If you got too excited and tried to use unsafe then when you compile the code just by using
csc test.cs
You will experience the following error:
error CS0227: Unsafe code may only appear if compiling with /unsafe
For compiling unsafe code use the /unsafe
csc test.cs /unsafe
In VS.net go to the project property page and in ?configuration properties>build? set Allow Unsafe Code Blocks to True.
After we knew how to declare a block as unsafe we should now learn how to declare and use pointers in it.
Declaring pointers
To declare a pointer of any type all what you have to do is to put ?*? after the type name such as
int * ptri;
double * ptrd;
NOTE: If you used to use pointer in C or C++ then be careful that in C# int * ptri, i; ?*? applies to the type itself not the variable so ?i? is a pointer here as well, same as arrays.
void Pointers
If you want to declare a pointer, but you do not wish to specify a type for it, you can declare it as void.
void *ptrVoid;
The main use of this is if you need to call an API function than require void* parameters. Within the C# language, there isn?t a great deal that you can do using void pointers.
Using pointers
Using pointers can be demonstrated in the following example:
static void Main()
{
int var1 = 5;
unsafe
{
int * ptr1, ptr2;
ptr1 = &var1;
ptr2 = ptr1;
*ptr2 = 20;
}
Console.WriteLine(var1);
}
The operator ?&? means ?address of?, ptr1 will hold the address of var1, ptr2 = ptr1 will assign the address of var1, which ptr1 was holding, to ptr2. Using ?*? before the pointer name means ?the content of the address?, so 20 will be written where ptr2 points.
Now var1 value is 20.
sizeof operator
As the name says, sizeof operator will return the number of bytes occupied of the given data type
unsafe
{
Console.WriteLine("sbyte: {0}", sizeof(sbyte));
Console.WriteLine("byte: {0}", sizeof(byte));
Console.WriteLine("short: {0}", sizeof(short));
Console.WriteLine("ushort: {0}", sizeof(ushort));
Console.WriteLine("int: {0}", sizeof(int));
Console.WriteLine("uint: {0}", sizeof(uint));
Console.WriteLine("long: {0}", sizeof(long));
Console.WriteLine("ulong: {0}", sizeof(ulong));
Console.WriteLine("char: {0}", sizeof(char));
Console.WriteLine("float: {0}", sizeof(float));
Console.WriteLine("double: {0}", sizeof(double));
Console.WriteLine("decimal: {0}", sizeof(decimal));
Console.WriteLine("bool: {0}", sizeof(bool));
//did I miss something?!
}
The output will be:
sbyte: 1
byte: 1
short: 2
ushort: 2
int: 4
uint: 4
long: 8
ulong: 8
char: 2
float: 4
double: 8
decimal: 16
bool: 1
Great, we don?t have to remember the size of every data type anymore!
Casting Pointers
A pointer actually stores an integer that represents a memory address, and it?s not surprising to know that you can explicitly convert any pointer to or from any integer type. The following code is totally legal.
int x = 10;
int *px;
px = &x;
uint y = (uint) px;
int *py = (int*) y;
A good reason for casting pointers to integer types is in order to display them. Console.Write() and Console.WriteLine() methods do not have any overloads to take pointers. Casting a pointer to an integer type will solve the problem.
Console.WriteLine(?The Address is: ? + (uint) px);
As I mentioned before, it?s totally legal to cast a pointer to any integer type. But does that really mean that we can use any integer type for casting, what about overflows? On a 32-bit machine we can use uint, long and ulong where an address runs from zero to about 4 billion. And on a 64-bit machine we can only use ulong. Note that casting the pointer to other integer types is very likely to cause and overflow error. The real problem is that checked keyword doesn?t apply to conversions involving pointers. For such conversions, exceptions wont be raised when an overflow occur, even in a checked context. When you are using pointers the .net framework will assume that you know what you?re doing and you?ll be happy with the overflows!
You can explicitly convert between pointers pointing to different types. For example:
byte aByte = 8;
byte *pByte = &aByte;
double *pDouble = (double*) pByte;
This is perfectly legal code, but think twice if you are trying something like that. In the above example, the double value pointed to by pDouble will actually contain a byte (which is 8), combined by an area of memory contained a double, which surely won?t give a meaningful value. However, you might want to convert between types in order to implement a union, or you might want to cast pointers to other types into pointers to sbyte in order to examine individual bytes of memory.
Pointers Arithmetic
It?s possible to use the operators +, -, +=, -=, ++ and -- with pointers, with a long or ulong on the right-hand side of the operator. While it?s not permitted to do any operation on a void pointer.
For example, suppose you have a pointer to an int, and you want to add 1 to it. The compiler will assume that you want to access the following int in the memory, and so will actually increase the value by 4 bytes, the size of int. If the pointer was pointing to a double, adding 1 will increase its value by 8 bytes the size of a double.
The general rule is that adding a number X to a pointer to type T with a value P gives the result P + X *sizeof(T).
Let?s have a look at the following example:
uint u = 3;
byte b = 8;
double d = 12.5;
uint *pU = &u;
byte *pB = &b;
double *pD = &d;
Console.WriteLine("Before Operations");
Console.WriteLine("Value of pU:" + (uint) pU);
Console.WriteLine("Value of pB:" + (uint) pB);
onsole.WriteLine("Value of pD:" + (uint) pD);
pU += 5;
pB -= 3;
pD++;
Console.WriteLine("\nAfter Operations");
Console.WriteLine("Value of pU:" + (uint) pU);
Console.WriteLine("Value of pB:" + (uint) pB);
Console.WriteLine("Value of pD:" + (uint) pD);
The result is:
Before Operations
Value of pU:1242784
Value of pB:1242780
Value of pD:1242772
After Operations
Value of pU:1242804
Value of pB:1242777
Value of pD:1242780
5 * 4 = 20, where added to pU.
3 * 1 = 3, where subtracted from pB.
1 * 8 = 8, where added to pD.
We can also subtract one pointer from another pointer, provided both pointers point to the same date type. This will result a long whose value is given by the difference between the pointers values divided by the size of the type that they represent:
double *pD1 = (double*) 12345632;
double *pD2 = (double*) 12345600;
long L = pD1 ? pD2; //gives 4 =32/8(sizeof(double))
Note that the way of initializing pointers in the example is totally valid.
Pointers to Structs and Class members
Pointers can point to structs the same way we used before as long as they don?t contain any reference types. The compiler will result an error if you had any pointer pointing to a struct containing a reference type.
Let?s have an example,
Suppose we had the following struct:
struct MyStruct
{
public long X;
public double D;
}
Declaring a pointer to it will be:
MyStruct *pMyStruct;
Initializing it:
MyStruct myStruct = new MyStruct();
pMyStruct = & myStruct;
To access the members:
(*pMyStruct).X = 18;
(*pMyStruct).D = 163.26;
The syntax is a bit complex, isn?t it?
That?s why C# defines another operator that allows us to access members of structs through pointers with a simpler syntax. The operator ?Pointer member access operator? looks like an arrow, it?s a dash followed by a greater than sign: ->
pMyStruct->X = 18;
pMyStruct->D = 163.26;
That looks better!
Fields within the struct can also be directly accessed through pointer of their type:
long *pL = &(myStruct.X);
double *pD = &(myStruct.D);
Classes and pointers is a different story. We already know that we can?t have a pointer pointing to a class, where it?s a reference type for sure. The Garbage Collector doesn?t keep any information about pointers, it?s only interested in references, so creating pointers to classes could cause the Garbage Collector to not work probably.
On the other hand, class members could be value types, and it?s possible to create pointers to them. But this requires a special syntax. Remember that class members are embedded in a class, which sets in the heap. That means that they are still under the control of the Garbage Collector, which can at any time decide to move the class instance to a new location. The Garbage Collector knows about the reference, and will update its value, but again it?s not interested in the pointers around, and they will still be pointing to the old location.
To avoid the risk of this problem, the compiler will result an error if you tried to create pointers pointing to class members in the same way we are using up to now.
The way around this problem is by using the keyword ?fixed?. It marks out a block of code bounded by braces, and notifies the Garbage Collector that there may be pointers pointing to members of certain class instances, which must not be moved.
Let?s have an example,
Suppose the following class:
class MyClass
{
public long X;
public double D;
}
Declaring pointers to its members in the regular way is a compile-time error:
MyClass myClass = new MyClass();
long *pX = &(myClass.X); //compile-time error.
To create pointers pointing to class members by using fixed keyword:
fixed (long *pX = &(myClass.X))
{
// use *pX here only.
}
The variable *pX is scoped within the fixed block only, and tells the garbage collector that not to move ?myClass? while the code inside the fixed block.
stackalloc
The keyword "stackalloc" commands the .net runtime to allocate a certain amount of memory on the stack. It requires two things to do so, the type (value types only) and the number of variables you?re allocating the stack for. For example if you want to allocate enough memory to store 4 floats, you can write the following:
float *ptrFloat = stackalloc float [4];
Or to allocate enough memory to store 50 shorts:
short *ptrShort = stackalloc short [50];
stackalloc simply allocates memory, it doesn?t initialize it to any value. The advantage of stackalloc is the ultra-high performance, and it?s up to you to initialize the memory locations that were allocated.
A very useful place of stackalloc could be creating an array directly in the stack. While C# had made using arrays very simple and easy, it still suffers from the disadvantage that these arrays are actually objects instantiated from System.Array and they are stored on the heap with all of the overhead that involves.
To create an array in the stack:
int size;
size = 6; //we can get this value at run-time as well.
int *int_ary = stackalloc int [size];
To access the array members, it?s very obvious to use *(int_ary + i), where ?i ?is the index. But it won?t be surprising to know that it?s also possible to use int_ary[i].
*( int_ary + 0) = 5; //or *int_ary = 5;
*( int_ary + 1) = 9; //accessing member #1
*( int_ary + 2) = 16;
int_ary[3] = 19; //another way to access members
int_ary[4] = 7;
int_ary[5] = 10;
In a usual array, accessing a member outside the array bounds will cause an exception. But when using stackalloc, you?re simply accessing an address somewhere on the stack; writing on it could cause to corrupt a variable value, or worst, a return address from a method currently being executed.
int[] ary = new int[6];
ary[10] = 5;//exception thrown
int *ary = stackalloc int [6];
ary[10] = 5;// the address (ary + 10 * sizeof(int)) had 5 assigned to it.
This takes us to the beginning to the article; using pointer comes with a cost. You have to be very certain of what you?re doing, any small error could cause very strange and hard to debug run-time bugs.
Commonly while programming in the .net framework we don?t need to use unsafe code, but in some cases there is no way not to, such as the following:
* Real-time applications, we might need to use pointers to enhance performance in such applications.
* External functions, in non-.net DLLs some functions requires a pointer as a parameter, such as Windows APIs that were written in C.
* Debugging, sometimes we need to inspect the memory contents for debugging purposes, or you might need to write an application that analyzes another application process and memory.
Unsafe code is mostly about pointers which have the following advantages and disadvantages.
Advantages of Unsafe Code in C#:
* Performance and flexibility, by using pointer you can access data and manipulate it in the most efficient way possible.
* Compatibility, in most cases we still need to use old windows APIs, which use pointers extensively. Or third parties may supply DLLs that some of its functions need pointer parameters. Although this can be done by writing the DLLImport declaration in a way that avoids pointers, but in some cases it?s just much simpler to use pointer.
* Memory Addresses, there is no way to know the memory address of some data without using pointers.
Disadvantages of Unsafe Code in C#:
* Complex syntax, to use pointers you need to go throw more complex syntax than we used to experience in C#.
* Harder to use, you need be more careful and logical while using pointers, miss using pointers might lead to the following:
o Overwrite other variables.
o Stack overflow.
o Access areas of memory that doesn?t contain any data as they do.
o Overwrite some information of the code for the .net runtime, which will surely lead your application to crash.
* Your code will be harder to debug. A simple mistake in using pointers might lead your application to crash randomly and unpredictably.
* Type-safety, using pointers will cause the code to fail in the .net type-safety checks, and of course if your security police don?t allow non type-safety code, then the .net framework will refuse to execute your application.
After we knew all the risks that might face us while using pointer and all the advantages those pointers introduces us of performance and flexibility, let us find now how to use them. The keyword unsafe is used while dealing with pointer, the name reflects the risks that you might face while using it. Let?s see where to place it. We can declare a whole class as unsafe:
unsafe class Class1
{
//you can use pointers here!
}
Or only some class members can be declared as unsafe:
class Class1
{
//pointer
unsafe int * ptr;
unsafe void MyMethod()
{
//you can use pointers here
}
}
The same applies to other members such as the constructor and the properties.
To declare unsafe local variables in a method, you have to put them in unsafe blocks as the following:
static void Main()
{
//can't use pointers here
unsafe
{
//you can declare and use pointer here
}
//can't use pointers here
}
You can?t declare local pointers in a ?safe? method in the same way we used in declaring global pointers, we have to put them in an unsafe block.
static void Main()
{
unsafe int * ptri; //Wrong
}
If you got too excited and tried to use unsafe then when you compile the code just by using
csc test.cs
You will experience the following error:
error CS0227: Unsafe code may only appear if compiling with /unsafe
For compiling unsafe code use the /unsafe
csc test.cs /unsafe
In VS.net go to the project property page and in ?configuration properties>build? set Allow Unsafe Code Blocks to True.
After we knew how to declare a block as unsafe we should now learn how to declare and use pointers in it.
Declaring pointers
To declare a pointer of any type all what you have to do is to put ?*? after the type name such as
int * ptri;
double * ptrd;
NOTE: If you used to use pointer in C or C++ then be careful that in C# int * ptri, i; ?*? applies to the type itself not the variable so ?i? is a pointer here as well, same as arrays.
void Pointers
If you want to declare a pointer, but you do not wish to specify a type for it, you can declare it as void.
void *ptrVoid;
The main use of this is if you need to call an API function than require void* parameters. Within the C# language, there isn?t a great deal that you can do using void pointers.
Using pointers
Using pointers can be demonstrated in the following example:
static void Main()
{
int var1 = 5;
unsafe
{
int * ptr1, ptr2;
ptr1 = &var1;
ptr2 = ptr1;
*ptr2 = 20;
}
Console.WriteLine(var1);
}
The operator ?&? means ?address of?, ptr1 will hold the address of var1, ptr2 = ptr1 will assign the address of var1, which ptr1 was holding, to ptr2. Using ?*? before the pointer name means ?the content of the address?, so 20 will be written where ptr2 points.
Now var1 value is 20.
sizeof operator
As the name says, sizeof operator will return the number of bytes occupied of the given data type
unsafe
{
Console.WriteLine("sbyte: {0}", sizeof(sbyte));
Console.WriteLine("byte: {0}", sizeof(byte));
Console.WriteLine("short: {0}", sizeof(short));
Console.WriteLine("ushort: {0}", sizeof(ushort));
Console.WriteLine("int: {0}", sizeof(int));
Console.WriteLine("uint: {0}", sizeof(uint));
Console.WriteLine("long: {0}", sizeof(long));
Console.WriteLine("ulong: {0}", sizeof(ulong));
Console.WriteLine("char: {0}", sizeof(char));
Console.WriteLine("float: {0}", sizeof(float));
Console.WriteLine("double: {0}", sizeof(double));
Console.WriteLine("decimal: {0}", sizeof(decimal));
Console.WriteLine("bool: {0}", sizeof(bool));
//did I miss something?!
}
The output will be:
sbyte: 1
byte: 1
short: 2
ushort: 2
int: 4
uint: 4
long: 8
ulong: 8
char: 2
float: 4
double: 8
decimal: 16
bool: 1
Great, we don?t have to remember the size of every data type anymore!
Casting Pointers
A pointer actually stores an integer that represents a memory address, and it?s not surprising to know that you can explicitly convert any pointer to or from any integer type. The following code is totally legal.
int x = 10;
int *px;
px = &x;
uint y = (uint) px;
int *py = (int*) y;
A good reason for casting pointers to integer types is in order to display them. Console.Write() and Console.WriteLine() methods do not have any overloads to take pointers. Casting a pointer to an integer type will solve the problem.
Console.WriteLine(?The Address is: ? + (uint) px);
As I mentioned before, it?s totally legal to cast a pointer to any integer type. But does that really mean that we can use any integer type for casting, what about overflows? On a 32-bit machine we can use uint, long and ulong where an address runs from zero to about 4 billion. And on a 64-bit machine we can only use ulong. Note that casting the pointer to other integer types is very likely to cause and overflow error. The real problem is that checked keyword doesn?t apply to conversions involving pointers. For such conversions, exceptions wont be raised when an overflow occur, even in a checked context. When you are using pointers the .net framework will assume that you know what you?re doing and you?ll be happy with the overflows!
You can explicitly convert between pointers pointing to different types. For example:
byte aByte = 8;
byte *pByte = &aByte;
double *pDouble = (double*) pByte;
This is perfectly legal code, but think twice if you are trying something like that. In the above example, the double value pointed to by pDouble will actually contain a byte (which is 8), combined by an area of memory contained a double, which surely won?t give a meaningful value. However, you might want to convert between types in order to implement a union, or you might want to cast pointers to other types into pointers to sbyte in order to examine individual bytes of memory.
Pointers Arithmetic
It?s possible to use the operators +, -, +=, -=, ++ and -- with pointers, with a long or ulong on the right-hand side of the operator. While it?s not permitted to do any operation on a void pointer.
For example, suppose you have a pointer to an int, and you want to add 1 to it. The compiler will assume that you want to access the following int in the memory, and so will actually increase the value by 4 bytes, the size of int. If the pointer was pointing to a double, adding 1 will increase its value by 8 bytes the size of a double.
The general rule is that adding a number X to a pointer to type T with a value P gives the result P + X *sizeof(T).
Let?s have a look at the following example:
uint u = 3;
byte b = 8;
double d = 12.5;
uint *pU = &u;
byte *pB = &b;
double *pD = &d;
Console.WriteLine("Before Operations");
Console.WriteLine("Value of pU:" + (uint) pU);
Console.WriteLine("Value of pB:" + (uint) pB);
onsole.WriteLine("Value of pD:" + (uint) pD);
pU += 5;
pB -= 3;
pD++;
Console.WriteLine("\nAfter Operations");
Console.WriteLine("Value of pU:" + (uint) pU);
Console.WriteLine("Value of pB:" + (uint) pB);
Console.WriteLine("Value of pD:" + (uint) pD);
The result is:
Before Operations
Value of pU:1242784
Value of pB:1242780
Value of pD:1242772
After Operations
Value of pU:1242804
Value of pB:1242777
Value of pD:1242780
5 * 4 = 20, where added to pU.
3 * 1 = 3, where subtracted from pB.
1 * 8 = 8, where added to pD.
We can also subtract one pointer from another pointer, provided both pointers point to the same date type. This will result a long whose value is given by the difference between the pointers values divided by the size of the type that they represent:
double *pD1 = (double*) 12345632;
double *pD2 = (double*) 12345600;
long L = pD1 ? pD2; //gives 4 =32/8(sizeof(double))
Note that the way of initializing pointers in the example is totally valid.
Pointers to Structs and Class members
Pointers can point to structs the same way we used before as long as they don?t contain any reference types. The compiler will result an error if you had any pointer pointing to a struct containing a reference type.
Let?s have an example,
Suppose we had the following struct:
struct MyStruct
{
public long X;
public double D;
}
Declaring a pointer to it will be:
MyStruct *pMyStruct;
Initializing it:
MyStruct myStruct = new MyStruct();
pMyStruct = & myStruct;
To access the members:
(*pMyStruct).X = 18;
(*pMyStruct).D = 163.26;
The syntax is a bit complex, isn?t it?
That?s why C# defines another operator that allows us to access members of structs through pointers with a simpler syntax. The operator ?Pointer member access operator? looks like an arrow, it?s a dash followed by a greater than sign: ->
pMyStruct->X = 18;
pMyStruct->D = 163.26;
That looks better!
Fields within the struct can also be directly accessed through pointer of their type:
long *pL = &(myStruct.X);
double *pD = &(myStruct.D);
Classes and pointers is a different story. We already know that we can?t have a pointer pointing to a class, where it?s a reference type for sure. The Garbage Collector doesn?t keep any information about pointers, it?s only interested in references, so creating pointers to classes could cause the Garbage Collector to not work probably.
On the other hand, class members could be value types, and it?s possible to create pointers to them. But this requires a special syntax. Remember that class members are embedded in a class, which sets in the heap. That means that they are still under the control of the Garbage Collector, which can at any time decide to move the class instance to a new location. The Garbage Collector knows about the reference, and will update its value, but again it?s not interested in the pointers around, and they will still be pointing to the old location.
To avoid the risk of this problem, the compiler will result an error if you tried to create pointers pointing to class members in the same way we are using up to now.
The way around this problem is by using the keyword ?fixed?. It marks out a block of code bounded by braces, and notifies the Garbage Collector that there may be pointers pointing to members of certain class instances, which must not be moved.
Let?s have an example,
Suppose the following class:
class MyClass
{
public long X;
public double D;
}
Declaring pointers to its members in the regular way is a compile-time error:
MyClass myClass = new MyClass();
long *pX = &(myClass.X); //compile-time error.
To create pointers pointing to class members by using fixed keyword:
fixed (long *pX = &(myClass.X))
{
// use *pX here only.
}
The variable *pX is scoped within the fixed block only, and tells the garbage collector that not to move ?myClass? while the code inside the fixed block.
stackalloc
The keyword "stackalloc" commands the .net runtime to allocate a certain amount of memory on the stack. It requires two things to do so, the type (value types only) and the number of variables you?re allocating the stack for. For example if you want to allocate enough memory to store 4 floats, you can write the following:
float *ptrFloat = stackalloc float [4];
Or to allocate enough memory to store 50 shorts:
short *ptrShort = stackalloc short [50];
stackalloc simply allocates memory, it doesn?t initialize it to any value. The advantage of stackalloc is the ultra-high performance, and it?s up to you to initialize the memory locations that were allocated.
A very useful place of stackalloc could be creating an array directly in the stack. While C# had made using arrays very simple and easy, it still suffers from the disadvantage that these arrays are actually objects instantiated from System.Array and they are stored on the heap with all of the overhead that involves.
To create an array in the stack:
int size;
size = 6; //we can get this value at run-time as well.
int *int_ary = stackalloc int [size];
To access the array members, it?s very obvious to use *(int_ary + i), where ?i ?is the index. But it won?t be surprising to know that it?s also possible to use int_ary[i].
*( int_ary + 0) = 5; //or *int_ary = 5;
*( int_ary + 1) = 9; //accessing member #1
*( int_ary + 2) = 16;
int_ary[3] = 19; //another way to access members
int_ary[4] = 7;
int_ary[5] = 10;
In a usual array, accessing a member outside the array bounds will cause an exception. But when using stackalloc, you?re simply accessing an address somewhere on the stack; writing on it could cause to corrupt a variable value, or worst, a return address from a method currently being executed.
int[] ary = new int[6];
ary[10] = 5;//exception thrown
int *ary = stackalloc int [6];
ary[10] = 5;// the address (ary + 10 * sizeof(int)) had 5 assigned to it.
This takes us to the beginning to the article; using pointer comes with a cost. You have to be very certain of what you?re doing, any small error could cause very strange and hard to debug run-time bugs.
Reading and Writing XML Files in C#
Typically, you use an XmlTextWriter if you need to write XML as raw data without the overhead of a DOM. The XmlTextWriter is an implementation of the XmlWriter class that provides the API which writes XML to file, stream, or a TextWriter. This class provides numerous validation and checking rules to ensure that the XML being written is well formed. When certain violations occur, exceptions are thrown and these exceptions should be handled. The XmlTextWriter has different constructors, each of which specifies a different type of the location to which to write the XML data. This sample uses the constructor that writes XML to a file. In particular, the following sample code constructs an XmlTextWriter with a string representing the file location for the newbooks.xml file.
XmlTextWriter myXmlTextWriter = new XmlTextWriter ("newbooks.xml", null);
C# Code:
myXmlTextWriter.Formatting = Formatting.Indented;
myXmlTextWriter.WriteStartDocument(false);
myXmlTextWriter.WriteDocType("bookstore", null, "books.dtd", null);
myXmlTextWriter.WriteComment("This file represents another fragment of a book store inventory database");
myXmlTextWriter.WriteStartElement("bookstore");
myXmlTextWriter.WriteStartElement("book", null);
myXmlTextWriter.WriteAttributeString("genre","autobiography");
myXmlTextWriter.WriteAttributeString("publicationdate","1979");
myXmlTextWriter.WriteAttributeString("ISBN","0-7356-0562-9");
myXmlTextWriter.WriteElementString("title", null, "The Autobiography of Mark Twain");
myXmlTextWriter.WriteStartElement("Author", null);
myXmlTextWriter.WriteElementString("first-name", "Mark");
myXmlTextWriter.WriteElementString("last-name", "Twain");
myXmlTextWriter.WriteEndElement();
myXmlTextWriter.WriteElementString("price", "7.99");
myXmlTextWriter.WriteEndElement();
myXmlTextWriter.WriteEndElement();
//Write the XML to file and close the myXmlTextWriter
myXmlTextWriter.Flush();
myXmlTextWriter.Close();
In creating this element, the preceding sample code also shows how, for each XML node type, there is corresponding XML write method. For example, writing an element calls the WriteElementString method and writing an attribute calls the WriteAttributeString method. For nested levels, you use the WriteStartElement / WriteEndElement pair and, for more complex attribute creation, you can use the WriteStartAttribute / WriteEndAttribute pair.
When writing XML, notice how the sample code writes the XML declaration with the version "1.0" using the WriteStartDocument method. If you want the writer to check that the document is well formed (XML declaration first, DOCTYPE in prolog, only one root level element, and so on), you must call this optional WriteStartDocument method before calling any other write method. Next, the code calls the WriteDocType method to writes the document type with the name "bookstore". The third parameter in the call to the WriteDocType specifies that the writer is to write SYSTEM "books.dtd". By writing this, the XML file indicates that there is an external DTD to validate against.
Finally, the sample code calls the Flush method to persist the XML data to a file before calling the Close method. (While this sample only really requires the Close method, there are occasions where the XML generated needs to be persisted and the writer reused.)
To check the output from the XmlTextWriter, perform a round trip test by reading in the generated file with an XmlTextReader to validate that the XML is well formed.
Reading Xml Files :
XmlReader class is the API that provides XML parsing, the XmlTextReader is the implementation designed to handle byte streams.
Typically, you use the XmlTextReader if you need to access the XML as raw data without the overhead of a DOM. Not having to access the DOM results in a faster way to reading XML. For example, an XML document could have a header section used for routing the document for processing elsewhere. The XmlTextReader has different constructors to specify the location of the XML data. This sample loads XML from the books.xml file, as shown in the following code.
XmlTextReader reader = new XmlTextReader ("books.xml");
Once loaded, the XmlTextReader moves across the XML data by using the Read method sequentially retrieving the next record from the document. The Read method returns false if there are no more records.
while (reader.Read())
{
// Do some work here on the data
...
}
To process the XML data, each record has a node type that can be determined from the NodeType property. After the NodeType enumeration returns the node type, the sample tests the node type to see whether it is either an element or document type. If the node is either one of these two types, the sample processes the node using the Name and Value properties to display details about the node. The Name property returns the node name (for instance, the element and attribute names, while the Value property returns the node value (node text) of the current node (record).
while (reader.Read())
{
switch (reader.NodeType)
{
case XmlNodeType.Element: // The node is an Element
Console.Write("<" + reader.Name);
while (reader.MoveToNextAttribute()) // Read attributes
Console.Write(" " + reader.Name + "='" + reader.Value + "'");
Console.Write(">");
break;
case XmlNodeType.DocumentType: // The node is a DocumentType
Console.WriteLine(NodeType + "<" + reader.Name + ">" + reader.Value);
break;
...
}
}
The XmlNodeType returned depends on the XmlReader class being used. For example, the XmlTextReader class never returns an XmlNodeType that is a Document, DocumentFragment, Entity, EndEntity and Notation node. See the .NET Framework Class Library for details on what XmlNodeType are returned by each XmlReader class.
XmlTextWriter myXmlTextWriter = new XmlTextWriter ("newbooks.xml", null);
C# Code:
myXmlTextWriter.Formatting = Formatting.Indented;
myXmlTextWriter.WriteStartDocument(false);
myXmlTextWriter.WriteDocType("bookstore", null, "books.dtd", null);
myXmlTextWriter.WriteComment("This file represents another fragment of a book store inventory database");
myXmlTextWriter.WriteStartElement("bookstore");
myXmlTextWriter.WriteStartElement("book", null);
myXmlTextWriter.WriteAttributeString("genre","autobiography");
myXmlTextWriter.WriteAttributeString("publicationdate","1979");
myXmlTextWriter.WriteAttributeString("ISBN","0-7356-0562-9");
myXmlTextWriter.WriteElementString("title", null, "The Autobiography of Mark Twain");
myXmlTextWriter.WriteStartElement("Author", null);
myXmlTextWriter.WriteElementString("first-name", "Mark");
myXmlTextWriter.WriteElementString("last-name", "Twain");
myXmlTextWriter.WriteEndElement();
myXmlTextWriter.WriteElementString("price", "7.99");
myXmlTextWriter.WriteEndElement();
myXmlTextWriter.WriteEndElement();
//Write the XML to file and close the myXmlTextWriter
myXmlTextWriter.Flush();
myXmlTextWriter.Close();
In creating this element, the preceding sample code also shows how, for each XML node type, there is corresponding XML write method. For example, writing an element calls the WriteElementString method and writing an attribute calls the WriteAttributeString method. For nested levels, you use the WriteStartElement / WriteEndElement pair and, for more complex attribute creation, you can use the WriteStartAttribute / WriteEndAttribute pair.
When writing XML, notice how the sample code writes the XML declaration with the version "1.0" using the WriteStartDocument method. If you want the writer to check that the document is well formed (XML declaration first, DOCTYPE in prolog, only one root level element, and so on), you must call this optional WriteStartDocument method before calling any other write method. Next, the code calls the WriteDocType method to writes the document type with the name "bookstore". The third parameter in the call to the WriteDocType specifies that the writer is to write SYSTEM "books.dtd". By writing this, the XML file indicates that there is an external DTD to validate against.
Finally, the sample code calls the Flush method to persist the XML data to a file before calling the Close method. (While this sample only really requires the Close method, there are occasions where the XML generated needs to be persisted and the writer reused.)
To check the output from the XmlTextWriter, perform a round trip test by reading in the generated file with an XmlTextReader to validate that the XML is well formed.
Reading Xml Files :
XmlReader class is the API that provides XML parsing, the XmlTextReader is the implementation designed to handle byte streams.
Typically, you use the XmlTextReader if you need to access the XML as raw data without the overhead of a DOM. Not having to access the DOM results in a faster way to reading XML. For example, an XML document could have a header section used for routing the document for processing elsewhere. The XmlTextReader has different constructors to specify the location of the XML data. This sample loads XML from the books.xml file, as shown in the following code.
XmlTextReader reader = new XmlTextReader ("books.xml");
Once loaded, the XmlTextReader moves across the XML data by using the Read method sequentially retrieving the next record from the document. The Read method returns false if there are no more records.
while (reader.Read())
{
// Do some work here on the data
...
}
To process the XML data, each record has a node type that can be determined from the NodeType property. After the NodeType enumeration returns the node type, the sample tests the node type to see whether it is either an element or document type. If the node is either one of these two types, the sample processes the node using the Name and Value properties to display details about the node. The Name property returns the node name (for instance, the element and attribute names, while the Value property returns the node value (node text) of the current node (record).
while (reader.Read())
{
switch (reader.NodeType)
{
case XmlNodeType.Element: // The node is an Element
Console.Write("<" + reader.Name);
while (reader.MoveToNextAttribute()) // Read attributes
Console.Write(" " + reader.Name + "='" + reader.Value + "'");
Console.Write(">");
break;
case XmlNodeType.DocumentType: // The node is a DocumentType
Console.WriteLine(NodeType + "<" + reader.Name + ">" + reader.Value);
break;
...
}
}
The XmlNodeType returned depends on the XmlReader class being used. For example, the XmlTextReader class never returns an XmlNodeType that is a Document, DocumentFragment, Entity, EndEntity and Notation node. See the .NET Framework Class Library for details on what XmlNodeType are returned by each XmlReader class.
Reading and Writing files using Microsoft .NET class libraries
Writing Text Files :
Text files are the most common type of files. You can make text files in every editor, notepad is preferably used to make text files. The extension of the text file is .txt . Lets see what Microsoft .net class libraries provide us for writing and reading text files. Lets first make a simple interface that lets us enter some data in the multi-line textbox.
Button Click Code:
After making the interface let see some of the code. All the code in this example will be written in the Button click event handler since we want the file to be written when the button is clicked.
// Don't forget to import System.IO namespace
private void Button1_Click(object sender, System.EventArgs e)
{
FileStream fs = File.Create(Server.MapPath("test.txt"));
StreamWriter sw = new StreamWriter(fs);
sw.Write(TextBox1.Text);
sw.Close();
fs.Close();
}
As you can see the code is really simple. Lets examine the code and see what's going on.
1. First of all don't forget to import the System.IO namespace.
2. Make the object of FileStream class. The FileStream class is your best option when you don't care about the internal structure of the files with which you're working.
3. Next we make the object of the StreamWriter class. You can think of StreamWriter class as an additional layer of functionality on top of the FileStream class.
4. File.Create method makes a new file. The Server.MapPath means that file will be created in the virtual folder of IIS which will be located at C:\inetpub\wwwroot\YourProjectFolder\
5. Next we write the Text from the TextBox to the file using the StreamWriter Write method.
6. Finally and the most important step is to close the FileStream object explicitly using the Close method.
There are many other ways of writing text to files. One of the way is to use the TextWriter class. For more information about the TextWriter class visit this link Reading Text Files :
We have seen how we can write the Text Files lets see how we can read from the text files. Reading is also as simple as writing the text files. Lets look at the reading code below:
private void Button1_Click(object sender, System.EventArgs e)
{
FileStream fs = File.OpenRead(Server.MapPath("test.txt"));
StreamReader sr = new StreamReader(fs);
while(sr.Peek() > -1)
{
Response.Write(sr.ReadLine());
}
sr.Close();
fs.Close();
}
Lets see what the code does:
1. First we made the FileStream object and tells it to open the file "test.txt".
2. We make the StreamReader object and tell it what FileStream to read.
3. The while loop checks the end of the file when the end of the file is reached sr.Peek() returns '-1' and the loop breaks.
4. Finally we close the StreamReader and FileStream objects.
Text files are the most common type of files. You can make text files in every editor, notepad is preferably used to make text files. The extension of the text file is .txt . Lets see what Microsoft .net class libraries provide us for writing and reading text files. Lets first make a simple interface that lets us enter some data in the multi-line textbox.
Button Click Code:
After making the interface let see some of the code. All the code in this example will be written in the Button click event handler since we want the file to be written when the button is clicked.
// Don't forget to import System.IO namespace
private void Button1_Click(object sender, System.EventArgs e)
{
FileStream fs = File.Create(Server.MapPath("test.txt"));
StreamWriter sw = new StreamWriter(fs);
sw.Write(TextBox1.Text);
sw.Close();
fs.Close();
}
As you can see the code is really simple. Lets examine the code and see what's going on.
1. First of all don't forget to import the System.IO namespace.
2. Make the object of FileStream class. The FileStream class is your best option when you don't care about the internal structure of the files with which you're working.
3. Next we make the object of the StreamWriter class. You can think of StreamWriter class as an additional layer of functionality on top of the FileStream class.
4. File.Create method makes a new file. The Server.MapPath means that file will be created in the virtual folder of IIS which will be located at C:\inetpub\wwwroot\YourProjectFolder\
5. Next we write the Text from the TextBox to the file using the StreamWriter Write method.
6. Finally and the most important step is to close the FileStream object explicitly using the Close method.
There are many other ways of writing text to files. One of the way is to use the TextWriter class. For more information about the TextWriter class visit this link Reading Text Files :
We have seen how we can write the Text Files lets see how we can read from the text files. Reading is also as simple as writing the text files. Lets look at the reading code below:
private void Button1_Click(object sender, System.EventArgs e)
{
FileStream fs = File.OpenRead(Server.MapPath("test.txt"));
StreamReader sr = new StreamReader(fs);
while(sr.Peek() > -1)
{
Response.Write(sr.ReadLine());
}
sr.Close();
fs.Close();
}
Lets see what the code does:
1. First we made the FileStream object and tells it to open the file "test.txt".
2. We make the StreamReader object and tell it what FileStream to read.
3. The while loop checks the end of the file when the end of the file is reached sr.Peek() returns '-1' and the loop breaks.
4. Finally we close the StreamReader and FileStream objects.
Oracle connection strings
Oracle ODBC connection strings
Open connection to Oracle database using ODBC
"Driver= {Microsoft ODBCforOracle};Server=Your_Oracle_Server.world;Uid=Your_Username;Pwd=Your_Password;"
Oracle OLE DB & OleDbConnection (.NET framework) connection strings
Open connection to Oracle database with standard security:
1. "Provider=MSDAORA;Data Source= Your_Oracle_Database;UserId=Your_Username;Password=Your_Password;"
2. "Provider= OraOLEDB.Oracle;Your_Oracle_Database;UserId=Your_Username;Password=Your_Password;"
Open trusted connection to Oracle database
"Provider= OraOLEDB.Oracle;DataSource=Your_Oracle_Database;OSAuthent=1;"
Open connection to Oracle database using ODBC
"Driver= {Microsoft ODBCforOracle};Server=Your_Oracle_Server.world;Uid=Your_Username;Pwd=Your_Password;"
Oracle OLE DB & OleDbConnection (.NET framework) connection strings
Open connection to Oracle database with standard security:
1. "Provider=MSDAORA;Data Source= Your_Oracle_Database;UserId=Your_Username;Password=Your_Password;"
2. "Provider= OraOLEDB.Oracle;Your_Oracle_Database;UserId=Your_Username;Password=Your_Password;"
Open trusted connection to Oracle database
"Provider= OraOLEDB.Oracle;DataSource=Your_Oracle_Database;OSAuthent=1;"
MySQL connection strings
MySQL ODBC connection strings
Open connection to local MySQL database using MySQL ODBC 3.51 Driver
"Provider=MSDASQL; DRIVER={MySQL ODBC 3.51Driver}; SERVER= localhost; DATABASE=Your_MySQL_Database; UID= Your_Username; PASSWORD=Your_Password; OPTION=3"
MySQL OLE DB & OleDbConnection (.NET framework) connection strings
Open connection to MySQL database:
"Provider=MySQLProv;Data Source=Your_MySQL_Database;User Id=Your_Username; Password=Your_Password;"
Open connection to local MySQL database using MySQL ODBC 3.51 Driver
"Provider=MSDASQL; DRIVER={MySQL ODBC 3.51Driver}; SERVER= localhost; DATABASE=Your_MySQL_Database; UID= Your_Username; PASSWORD=Your_Password; OPTION=3"
MySQL OLE DB & OleDbConnection (.NET framework) connection strings
Open connection to MySQL database:
"Provider=MySQLProv;Data Source=Your_MySQL_Database;User Id=Your_Username; Password=Your_Password;"
MS Access connection strings
MS Access ODBC connection strings
Standard Security:
"Driver= {MicrosoftAccessDriver(*.mdb)};DBQ=C:\App1\Your_Database_Name.mdb;Uid=Your_Username;Pwd=Your_Password;"
Workgroup:
"Driver={Microsoft Access Driver (*.mdb)}; Dbq=C:\App1\Your_Database_Name.mdb; SystemDB=C:\App1\Your_Database_Name.mdw;"
Exclusive "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\App1\Your_Database_Name.mdb; Exclusive=1; Uid=Your_Username; Pwd=Your_Password;"
MS Access OLE DB & OleDbConnection (.NET framework) connection strings
Open connection to Access database:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; User Id=admin; Password="
Open connection to Access database using Workgroup (System database):
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; Jet OLEDB:System Database=c:\App1\Your_System_Database_Name.mdw"
Open connection to password protected Access database:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; Jet OLEDB:Database Password=Your_Password"
Open connection to Access database located on a network share:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\Server_Name\Share_Name\Share_Path\Your_Database_Name.mdb"
Open connection to Access database located on a remote server:
"Provider=MS Remote; Remote Server=http://Your-Remote-Server-IP; Remote Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb"
Standard Security:
"Driver= {MicrosoftAccessDriver(*.mdb)};DBQ=C:\App1\Your_Database_Name.mdb;Uid=Your_Username;Pwd=Your_Password;"
Workgroup:
"Driver={Microsoft Access Driver (*.mdb)}; Dbq=C:\App1\Your_Database_Name.mdb; SystemDB=C:\App1\Your_Database_Name.mdw;"
Exclusive "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\App1\Your_Database_Name.mdb; Exclusive=1; Uid=Your_Username; Pwd=Your_Password;"
MS Access OLE DB & OleDbConnection (.NET framework) connection strings
Open connection to Access database:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; User Id=admin; Password="
Open connection to Access database using Workgroup (System database):
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; Jet OLEDB:System Database=c:\App1\Your_System_Database_Name.mdw"
Open connection to password protected Access database:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; Jet OLEDB:Database Password=Your_Password"
Open connection to Access database located on a network share:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\Server_Name\Share_Name\Share_Path\Your_Database_Name.mdb"
Open connection to Access database located on a remote server:
"Provider=MS Remote; Remote Server=http://Your-Remote-Server-IP; Remote Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb"
SQL Server connection strings
SQL ODBC connection strings
Standard Security:< br> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Uid=Your_Username;Pwd=Your_Password;"
Trusted connection:< br> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;"
SQL OLE DB connection strings
Standard Security:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"
Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
SQL OleDbConnection .NET strings
Standard Security:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"
Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
SQL SqlConnection .NET strings
Standard Security:
1. "Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;" < br>2. "Server=Your_Server_Name;Database=Your_Database_Name;UserID=Your_Username;Password=Your_Password;Trusted_Connection=False"
Trusted connection:
1. "Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
2."Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=True;"
Standard Security:< br> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Uid=Your_Username;Pwd=Your_Password;"
Trusted connection:< br> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;"
SQL OLE DB connection strings
Standard Security:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"
Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
SQL OleDbConnection .NET strings
Standard Security:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"
Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
SQL SqlConnection .NET strings
Standard Security:
1. "Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;" < br>2. "Server=Your_Server_Name;Database=Your_Database_Name;UserID=Your_Username;Password=Your_Password;Trusted_Connection=False"
Trusted connection:
1. "Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
2."Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=True;"
Sunday, February 18, 2007
Database Design and Various Useful Definition
What is normalization? Explain different levels of normalization?
Check out the article Q100139 from Microsoft knowledge base and of course, there's much more information available in the net. It'll be a good idea to get a hold of any RDBMS fundamentals text book, especially the one by C. J. Date. Most of the times, it will be okay if you can explain till third normal form.
What is denormalization and when would you go for it?
As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
It will be a good idea to read up a database designing fundamentals text book.
What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
What are user defined datatypes and when you should go for them?
User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables. See sp_addtype, sp_droptype in books online.
What is bit datatype and what's the information that can be stored inside a bit column?
Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.
Candidate key, Alternate key, Composite key
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.
What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.
Check out the article Q100139 from Microsoft knowledge base and of course, there's much more information available in the net. It'll be a good idea to get a hold of any RDBMS fundamentals text book, especially the one by C. J. Date. Most of the times, it will be okay if you can explain till third normal form.
What is denormalization and when would you go for it?
As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
It will be a good idea to read up a database designing fundamentals text book.
What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
What are user defined datatypes and when you should go for them?
User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables. See sp_addtype, sp_droptype in books online.
What is bit datatype and what's the information that can be stored inside a bit column?
Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.
Candidate key, Alternate key, Composite key
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.
What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.
Hackers attack key Net traffic computers
Hackers briefly overwhelmed at least three of the 13 computers that help manage global computer traffic on Tuesday in one of the most significant attacks against the Internet since 2002.
Experts said the unusually powerful attacks lasted as long as 12 hours but passed largely unnoticed by most computer users, a testament to the resiliency of the Internet. Behind the scenes, computer scientists worldwide raced to cope with enormous volumes of data that threatened to saturate some of the Internet's most vital pipelines.
The Homeland Security Department confirmed it was monitoring what it called "anomalous" Internet traffic.
"There is no credible intelligence to suggest an imminent threat to the homeland or our computing systems at this time," the department said in a statement.
The motive for the attacks was unclear, said Duane Wessels, a researcher at the Cooperative Association for Internet Data Analysis at the San Diego Supercomputing Centre. "Maybe to show off or just be disruptive; it doesn't seem to be extortion or anything like that," Wessels said.
Other experts said the hackers appeared to disguise their origin, but vast amounts of rogue data in the attacks were traced to South Korea.
The attacks appeared to target UltraDNS, the company that operates servers managing traffic for Web sites ending in "org" and some other suffixes, experts said. Officials with NeuStar Inc., which owns UltraDNS, confirmed only that it had observed an unusual increase in traffic.
Among the targeted "root" servers that manage global Internet traffic were ones operated by the Defence Department and the Internet's primary oversight body.
"There was what appears to be some form of attack during the night hours here in California and into the morning," said John Crain, chief technical officer for the Internet Corporation for Assigned Names and Numbers. He said the attack was continuing and so was the hunt for its origin.
"I don't think anybody has the full picture," Crain said. "We're looking at the data."
Crain said Tuesday's attack was less serious than attacks against the same 13 "root" servers in October 2002 because technology innovations in recent years have increasingly distributed their workloads to other computers around the globe.
Experts said the unusually powerful attacks lasted as long as 12 hours but passed largely unnoticed by most computer users, a testament to the resiliency of the Internet. Behind the scenes, computer scientists worldwide raced to cope with enormous volumes of data that threatened to saturate some of the Internet's most vital pipelines.
The Homeland Security Department confirmed it was monitoring what it called "anomalous" Internet traffic.
"There is no credible intelligence to suggest an imminent threat to the homeland or our computing systems at this time," the department said in a statement.
The motive for the attacks was unclear, said Duane Wessels, a researcher at the Cooperative Association for Internet Data Analysis at the San Diego Supercomputing Centre. "Maybe to show off or just be disruptive; it doesn't seem to be extortion or anything like that," Wessels said.
Other experts said the hackers appeared to disguise their origin, but vast amounts of rogue data in the attacks were traced to South Korea.
The attacks appeared to target UltraDNS, the company that operates servers managing traffic for Web sites ending in "org" and some other suffixes, experts said. Officials with NeuStar Inc., which owns UltraDNS, confirmed only that it had observed an unusual increase in traffic.
Among the targeted "root" servers that manage global Internet traffic were ones operated by the Defence Department and the Internet's primary oversight body.
"There was what appears to be some form of attack during the night hours here in California and into the morning," said John Crain, chief technical officer for the Internet Corporation for Assigned Names and Numbers. He said the attack was continuing and so was the hunt for its origin.
"I don't think anybody has the full picture," Crain said. "We're looking at the data."
Crain said Tuesday's attack was less serious than attacks against the same 13 "root" servers in October 2002 because technology innovations in recent years have increasingly distributed their workloads to other computers around the globe.
Net watchdog proposes '.xxx' porn domain
The Internet's key oversight agency has revived a proposal it earlier rejected to create an online red-light district, after adding stronger provisions to prohibit child pornography and require labelling of websites with sexually explicit materials.
The use of the proposed ".xxx" domain name would remain voluntary, but any porn sites that choose to use it instead of the more popular ".com" would be subject to the new terms issued by the Internet Corporation for Assigned Names and Numbers.
The idea of a separate ".xxx" domain has generated significant opposition from conservative groups and even some pornography websites.
But ICANN officials said they initially rejected the proposal in May not because of the opposition but because of concerns that the agency might be put in a difficult position of having to enforce all of the world's laws governing pornography. They noted that various nations' speech-related laws sometimes conflict with one another.
The new proposal does not directly address any potential conflicts in laws, but it calls for the company backing it, ICM Registry Inc of Jupiter, Florida, to hire independent organisations to monitor porn sites' compliance with the new rules.
ICANN, the agency in Marina del Rey, California, designated by the US government to oversee domain name policies, opened the proposal to public comment but did not indicate when it would rule.
If approved, ICM would be required to help develop mechanisms for promoting child safety and preventing child pornography, "including practices that appeal to paedophiles or suggest the presence of child pornography on the site."
Porn sites would have to participate in a self-descriptive labelling system, likely one from the Internet Content Rating Association. Under it, websites add tags based on such criteria as the presence of nudity and whether it is in an artistic or educational context, such as for sites on breast feeding. Relatively few sites now participate, although major browsers have mechanisms for reading the tags.
ICM also would have to develop automated tools to check for compliance and give users ways to report violations.
ICM believes the domain would help the $12 billion online porn industry clean up its act, as those using it must abide by rules designed to bar such trickery as spamming and malicious scripts.
Anti-porn advocates, however, countered that sites would be free to keep their current ".com" address, in effect making porn more easily accessible by creating yet another channel to house it.
Many porn sites also objected, fearing that an ".xxx' domain would pave the way for governments or even private industry to filter speech that is protected in the United States by the First Amendment.
The use of the proposed ".xxx" domain name would remain voluntary, but any porn sites that choose to use it instead of the more popular ".com" would be subject to the new terms issued by the Internet Corporation for Assigned Names and Numbers.
The idea of a separate ".xxx" domain has generated significant opposition from conservative groups and even some pornography websites.
But ICANN officials said they initially rejected the proposal in May not because of the opposition but because of concerns that the agency might be put in a difficult position of having to enforce all of the world's laws governing pornography. They noted that various nations' speech-related laws sometimes conflict with one another.
The new proposal does not directly address any potential conflicts in laws, but it calls for the company backing it, ICM Registry Inc of Jupiter, Florida, to hire independent organisations to monitor porn sites' compliance with the new rules.
ICANN, the agency in Marina del Rey, California, designated by the US government to oversee domain name policies, opened the proposal to public comment but did not indicate when it would rule.
If approved, ICM would be required to help develop mechanisms for promoting child safety and preventing child pornography, "including practices that appeal to paedophiles or suggest the presence of child pornography on the site."
Porn sites would have to participate in a self-descriptive labelling system, likely one from the Internet Content Rating Association. Under it, websites add tags based on such criteria as the presence of nudity and whether it is in an artistic or educational context, such as for sites on breast feeding. Relatively few sites now participate, although major browsers have mechanisms for reading the tags.
ICM also would have to develop automated tools to check for compliance and give users ways to report violations.
ICM believes the domain would help the $12 billion online porn industry clean up its act, as those using it must abide by rules designed to bar such trickery as spamming and malicious scripts.
Anti-porn advocates, however, countered that sites would be free to keep their current ".com" address, in effect making porn more easily accessible by creating yet another channel to house it.
Many porn sites also objected, fearing that an ".xxx' domain would pave the way for governments or even private industry to filter speech that is protected in the United States by the First Amendment.
The Essentials of Google Search
Choosing search terms
Choosing the right search terms is the key to finding the information you need.
Start with the obvious – if you're looking for general information on Hawaii, try
Hawaii.
But it's often advisable to use multiple search terms; if you're planning a Hawaiian vacation, you'll do better with vacation Hawaii than with either vacation or Hawaii by themselves. And vacation Hawaii golf may produce even better (or, depending on your perspective, worse) results.
vacation Hawaii golf
You might also ask yourself if your search terms are sufficiently specific. It's better to search on luxury hotels Maui than on tropical island hotels. But choose your search terms carefully; Google looks for the search terms you chose, so luxury hotels Maui will probably deliver better results than really nice places to spend the night in Maui.
Capitalization
Google searches are NOT case sensitive. All letters, regardless of how you type them, will be understood as lower case. For example, searches for george washington, George Washington, and gEoRgE wAsHiNgToN will all return the same results.
Automatic "and" queries
By default, Google only returns pages that include all of your search terms. There is no need to include "and" between terms. Keep in mind that the order in which the terms are typed will affect the search results. To restrict a search further, just include more terms. For example, to plan a vacation to Hawaii, simply type vacation hawaii.
vacation Hawaii
Automatic exclusion of common words
Google ignores common words and characters such as "where" and "how", as well as certain single digits and single letters, because they tend to slow down your search without improving the results. Google will indicate if a common word has been excluded by displaying details on the results page below the search box.
If a common word is essential to getting the results you want, you can include it by putting a "+" sign in front of it. (Be sure to include a space before the "+" sign.)
Another method for doing this is conducting a phrase search, which simply means putting quotation marks around two or more words. Common words in a phrase search (e.g., "where are you") are included in the search.
For example, to search for Star Wars, Episode I, use:
Star Wars Episode +I
~ OR ~
"Star Wars Episode I"
Word variations (stemming)
Google now uses stemming technology. Thus, when appropriate, it will search not only for your search terms, but also for words that are similar to some or all of those terms. If you search for pet lemur dietary needs, Google will also search for pet lemur diet needs, and other related variations of your terms. Any variants of your terms that were searched for will be highlighted in the snippet of text accompanying each result.
Phrase searches
Sometimes you'll only want results that include an exact phrase. In this case, simply put quotation marks around your search terms.
Phrase searches are particularly effective if you're searching for proper names ("George Washington"), lyrics ("the long and winding road"), or other famous phrases ("This was their finest hour").
"the long and winding road"
Negative terms
If your search term has more than one meaning (bass, for example, could refer to fishing or music) you can focus your search by putting a minus sign ("-") in front of words related to the meaning you want to avoid.
For example, here's how you'd find pages about bass-heavy lakes, but not bass-heavy music:
bass -music
Note: when you include a negative term in your search, be sure to include a space before the minus sign.
And finally... "I'm Feeling Lucky"
After you've entered your search terms, you might want to try the "I'm Feeling Lucky" button, which takes you straight to the most relevant website that Google found for your query. You won't see the search results page at all, but if you did, the "I'm Feeling Lucky" site would be listed on top.
For example, if you're looking for the Stanford University homepage, just enter Stanford and click "I'm Feeling Lucky" instead of the Google Search button. Google will take you directly to "www.stanford.edu."
Choosing the right search terms is the key to finding the information you need.
Start with the obvious – if you're looking for general information on Hawaii, try
Hawaii.
But it's often advisable to use multiple search terms; if you're planning a Hawaiian vacation, you'll do better with vacation Hawaii than with either vacation or Hawaii by themselves. And vacation Hawaii golf may produce even better (or, depending on your perspective, worse) results.
vacation Hawaii golf
You might also ask yourself if your search terms are sufficiently specific. It's better to search on luxury hotels Maui than on tropical island hotels. But choose your search terms carefully; Google looks for the search terms you chose, so luxury hotels Maui will probably deliver better results than really nice places to spend the night in Maui.
Capitalization
Google searches are NOT case sensitive. All letters, regardless of how you type them, will be understood as lower case. For example, searches for george washington, George Washington, and gEoRgE wAsHiNgToN will all return the same results.
Automatic "and" queries
By default, Google only returns pages that include all of your search terms. There is no need to include "and" between terms. Keep in mind that the order in which the terms are typed will affect the search results. To restrict a search further, just include more terms. For example, to plan a vacation to Hawaii, simply type vacation hawaii.
vacation Hawaii
Automatic exclusion of common words
Google ignores common words and characters such as "where" and "how", as well as certain single digits and single letters, because they tend to slow down your search without improving the results. Google will indicate if a common word has been excluded by displaying details on the results page below the search box.
If a common word is essential to getting the results you want, you can include it by putting a "+" sign in front of it. (Be sure to include a space before the "+" sign.)
Another method for doing this is conducting a phrase search, which simply means putting quotation marks around two or more words. Common words in a phrase search (e.g., "where are you") are included in the search.
For example, to search for Star Wars, Episode I, use:
Star Wars Episode +I
~ OR ~
"Star Wars Episode I"
Word variations (stemming)
Google now uses stemming technology. Thus, when appropriate, it will search not only for your search terms, but also for words that are similar to some or all of those terms. If you search for pet lemur dietary needs, Google will also search for pet lemur diet needs, and other related variations of your terms. Any variants of your terms that were searched for will be highlighted in the snippet of text accompanying each result.
Phrase searches
Sometimes you'll only want results that include an exact phrase. In this case, simply put quotation marks around your search terms.
Phrase searches are particularly effective if you're searching for proper names ("George Washington"), lyrics ("the long and winding road"), or other famous phrases ("This was their finest hour").
"the long and winding road"
Negative terms
If your search term has more than one meaning (bass, for example, could refer to fishing or music) you can focus your search by putting a minus sign ("-") in front of words related to the meaning you want to avoid.
For example, here's how you'd find pages about bass-heavy lakes, but not bass-heavy music:
bass -music
Note: when you include a negative term in your search, be sure to include a space before the minus sign.
And finally... "I'm Feeling Lucky"
After you've entered your search terms, you might want to try the "I'm Feeling Lucky" button, which takes you straight to the most relevant website that Google found for your query. You won't see the search results page at all, but if you did, the "I'm Feeling Lucky" site would be listed on top.
For example, if you're looking for the Stanford University homepage, just enter Stanford and click "I'm Feeling Lucky" instead of the Google Search button. Google will take you directly to "www.stanford.edu."
Database Programming SQL Server
What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
Cursors allow row-by-row prcessing of the result sets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors. Here is an example:
If you have to give a flat hike to your employees using the following criteria:
Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike
In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END
Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the 'My code library' section of my site or search for WHILE.
What is a join and explain different types of joins.
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
For more information see pages from books online titled: "Join Fundamentals" and "Using Joins".
Can you have a nested transaction?
Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT
What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement. See books online to learn how to create extended stored procedures and how to add them to SQL Server.
Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy. For an example of creating a COM object in VB and calling it from T-SQL, see 'My code library' section of this site.
What is the system function to get the current user's user id?
USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder.Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers.
Also check out books online for 'inserted table', 'deleted table' and COLUMNS_UPDATED().
There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?
Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.
What is a self join? Explain it with an example.
Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.
CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)
INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'
SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)
SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid
Cursors allow row-by-row prcessing of the result sets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors. Here is an example:
If you have to give a flat hike to your employees using the following criteria:
Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike
In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END
Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the 'My code library' section of my site or search for WHILE.
What is a join and explain different types of joins.
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
For more information see pages from books online titled: "Join Fundamentals" and "Using Joins".
Can you have a nested transaction?
Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT
What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement. See books online to learn how to create extended stored procedures and how to add them to SQL Server.
Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy. For an example of creating a COM object in VB and calling it from T-SQL, see 'My code library' section of this site.
What is the system function to get the current user's user id?
USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder.Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers.
Also check out books online for 'inserted table', 'deleted table' and COLUMNS_UPDATED().
There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?
Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.
What is a self join? Explain it with an example.
Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.
CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)
INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'
SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)
SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid
SQL Server architecture
What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
Explain different isolation levels ?
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
CREATE INDEX myIndex ON myTable(myColumn)
What type of Index will get created after executing the above statement?
Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.
What's the maximum size of a row ?
8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".
Explain Active/Active and Active/Passive cluster configurations
Hopefully you have experience setting up cluster servers. But if you don't, at least be familiar with the way clustering works and the two clusterning configurations Active/Active and Active/Passive. SQL Server books online has enough information on this topic and there is a good white paper available on Microsoft site.
Explain the architecture of SQL Server
This is a very important question and you better be able to answer it if consider yourself a DBA. SQL Server books online is the best place to read about SQL Server architecture. Read up the chapter dedicated to SQL Server Architecture.
What is lock escalation?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.
What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
Explain the storage models of OLAP
Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more information.
What are constraints? Explain different types of constraints.
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"
Whar is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?
Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.
Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
Explain different isolation levels ?
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
CREATE INDEX myIndex ON myTable(myColumn)
What type of Index will get created after executing the above statement?
Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.
What's the maximum size of a row ?
8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".
Explain Active/Active and Active/Passive cluster configurations
Hopefully you have experience setting up cluster servers. But if you don't, at least be familiar with the way clustering works and the two clusterning configurations Active/Active and Active/Passive. SQL Server books online has enough information on this topic and there is a good white paper available on Microsoft site.
Explain the architecture of SQL Server
This is a very important question and you better be able to answer it if consider yourself a DBA. SQL Server books online is the best place to read about SQL Server architecture. Read up the chapter dedicated to SQL Server Architecture.
What is lock escalation?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.
What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
Explain the storage models of OLAP
Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more information.
What are constraints? Explain different types of constraints.
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"
Whar is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?
Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.
Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
How to execute an application in a remote application domain
Application domain is a construct in the CLR that is the unit of isolation for an application. Application domains provide a secure and versatile unit of processing that the common language runtime can use to provide isolation between applications. 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.
Make a file CallingAssembly.cs containing following code
using System;
using System.Reflection;
using System.Runtime.Remoting;
public class CallingAssembly
{
public static void Main(String[] argv)
{
AppDomainSetup objADS = new AppDomainSetup();
objADS.ApplicationBase = "file:///" + Environment.CurrentDirectory;
AppDomain objAD = AppDomain.CreateDomain("RemoteDomainExample", null, objADS);
objAD.ExecuteAssembly("CalledAssembly.exe");
AppDomain.Unload(objAD);
}
}
Two Application domains interact with each other through Remoting. AppDomain class is drived from MarshalByRefObject, which Enables access to objects across application domain boundaries in applications that support remoting. CreateDomain creates a new application domain with the given name using the assembly binding information and supplied evidence. ExecuteAssembly methos of AppDomain, Executes the assembly contained in the specified file in that domain.
Make a file CalledAssembly.cs containing following code
using System;
using System.Threading;
public class CalledAssembly
{
public static void Main(String[] argv)
{
Console.WriteLine("Called Assembly Application Domain: " + Thread.GetDomain().FriendlyName);
Console.WriteLine("Assembly Called");
}
}
Make a file CallingAssembly.cs containing following code
using System;
using System.Reflection;
using System.Runtime.Remoting;
public class CallingAssembly
{
public static void Main(String[] argv)
{
AppDomainSetup objADS = new AppDomainSetup();
objADS.ApplicationBase = "file:///" + Environment.CurrentDirectory;
AppDomain objAD = AppDomain.CreateDomain("RemoteDomainExample", null, objADS);
objAD.ExecuteAssembly("CalledAssembly.exe");
AppDomain.Unload(objAD);
}
}
Two Application domains interact with each other through Remoting. AppDomain class is drived from MarshalByRefObject, which Enables access to objects across application domain boundaries in applications that support remoting. CreateDomain creates a new application domain with the given name using the assembly binding information and supplied evidence. ExecuteAssembly methos of AppDomain, Executes the assembly contained in the specified file in that domain.
Make a file CalledAssembly.cs containing following code
using System;
using System.Threading;
public class CalledAssembly
{
public static void Main(String[] argv)
{
Console.WriteLine("Called Assembly Application Domain: " + Thread.GetDomain().FriendlyName);
Console.WriteLine("Assembly Called");
}
}
What are Delegates?
In your Visual Basic.NET journey, you have definitely encountered a well used but little understood phenomenon called a delegate. You use them everyday, but might not know it. In this article, we will take a look at what a delegate is and how it will help you to develop better software.
A delegate can be defined as a type safe function pointer. It encapsulates the memory address of a function in your code. Whenever you create or use an event in code, you are using a delegate. When the event is thrown, the framework examines the delegate behind the event and then calls the function that the delegate points to. As we will see later, delegates can be combined to form groups of functions that can be called together.
Let's first take a quick look at how to define and invoke a delegate. First we declare our delegate in our form class:
Private Delegate Sub MyDelSub()Then we use the delegate by simply declaring a variable of the delegate and assigning the sub or function to run when called. First the sub to be called:
Private Sub WriteToDebug() Debug.WriteLine( "Delegate Wrote To Debug Window" )End SubYou will notice also that it matches our declaration of MyDelSub; it's a sub routine with no parameters. And then our test code:
Dim del As MyDelSubdel = New MyDelSub(AddressOf WriteToDebug)del.Invoke()When we invoke the delegate, the WriteToDebug sub is run. Visual Basic hides most of the implementation of delegates when you use events, which are based off invoking a delegate. This is the equivalent of the above delegate invoke also.
Private Event MyEvent() 'declare it in the class'to use it, add a handler and raise the event.AddHandler MyEvent, AddressOf WriteToDebugRaiseEvent MyEvent()If delegates stopped at this point, they would be useless since events are less work and do the same thing. Let's get into some of the more advanced features of delegates. We will start with multicast delegates.
A delegate can be defined as a type safe function pointer. It encapsulates the memory address of a function in your code. Whenever you create or use an event in code, you are using a delegate. When the event is thrown, the framework examines the delegate behind the event and then calls the function that the delegate points to. As we will see later, delegates can be combined to form groups of functions that can be called together.
Let's first take a quick look at how to define and invoke a delegate. First we declare our delegate in our form class:
Private Delegate Sub MyDelSub()Then we use the delegate by simply declaring a variable of the delegate and assigning the sub or function to run when called. First the sub to be called:
Private Sub WriteToDebug() Debug.WriteLine( "Delegate Wrote To Debug Window" )End SubYou will notice also that it matches our declaration of MyDelSub; it's a sub routine with no parameters. And then our test code:
Dim del As MyDelSubdel = New MyDelSub(AddressOf WriteToDebug)del.Invoke()When we invoke the delegate, the WriteToDebug sub is run. Visual Basic hides most of the implementation of delegates when you use events, which are based off invoking a delegate. This is the equivalent of the above delegate invoke also.
Private Event MyEvent() 'declare it in the class'to use it, add a handler and raise the event.AddHandler MyEvent, AddressOf WriteToDebugRaiseEvent MyEvent()If delegates stopped at this point, they would be useless since events are less work and do the same thing. Let's get into some of the more advanced features of delegates. We will start with multicast delegates.
Transfer Record in Excel Through the SQL Server 2000
PRINT 'Begin CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
PRINT ''
GO
SET NOCOUNT ON
DECLARE @Conn int -- ADO Connection object to create XLS
, @hr int -- OLE return value
, @src varchar(255) -- OLE Error Source
, @desc varchar(255) -- OLE Error Description
, @Path varchar(255) -- Drive or UNC path for XLS
, @Connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
, @WKS_Created bit -- Whether the XLS Worksheet exists
, @WKS_Name varchar(128) -- Name of the XLS Worksheet (table)
, @ServerName nvarchar(128) -- Linked Server name for XLS
, @DDL varchar(8000) -- Jet4 DDL for the XLS WKS table creation
, @SQL varchar(8000) -- INSERT INTO XLS T-SQL
, @Recs int -- Number of records added to XLS
, @Log bit -- Whether to log process detail
-- Init variables
SELECT @Recs = 0
-- %%% 1 = Verbose output detail, helps find problems, 0 = minimal output detail
, @Log = 1
-- %%% assign the UNC or path and name for the XLS file, requires Read/Write access
-- must be accessable from server via SQL Server service account
-- & SQL Server Agent service account, if scheduled
SET @Path = 'C:\TEMP\Test_'+CONVERT(varchar(10),GETDATE(),112)+'.xls'
-- assign the ADO connection string for the XLS creation
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'
-- %%% assign the Linked Server name for the XLS population
SET @ServerName = 'EXCEL_TEST'
-- %%% Rename Table as required, this will also be the XLS Worksheet name
SET @WKS_Name = 'People'
-- %%% Table creation DDL, uses Jet4 syntax,
-- Text data type = varchar(255) when accessed from T-SQL
SET @DDL = 'CREATE TABLE '+@WKS_Name+' (SSN Text, Name Text, Phone Text)'
-- %%% T-SQL for table population, note the 4 part naming required by Jet4 OLE DB
-- INSERT INTO SELECT, INSERT INTO VALUES, and EXEC sp types are supported
-- Linked Server does not support SELECT INTO types
SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name+' (SSN, Name, Phone) '
SET @SQL = @SQL+'SELECT au_id AS SSN'
SET @SQL = @SQL+', LTRIM(RTRIM(ISNULL(au_fname,'''')+'' ''+ISNULL(au_lname,''''))) AS Name'
SET @SQL = @SQL+', phone AS Phone '
SET @SQL = @SQL+'FROM pubs.dbo.authors'
IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
-- Create the Conn object
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT
IF @hr <> 0 -- have to use <> as OLE / ADO can return negative error numbers
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT char(9)+'Assigned ConnectionString property'
-- Set a the Conn object's ConnectionString property
-- Work-around for error using a variable parameter on the Open method
EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT char(9)+'Open Connection to XLS, for file Create or Append'
-- Call the Open method to create the XLS if it does not exist, can't use parameters
EXEC @hr = sp_OAMethod @Conn, 'Open'
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
-- %%% This section could be repeated for multiple Worksheets (Tables)
IF @Log = 1 PRINT char(9)+'Execute DDL to create '''+@WKS_Name+''' worksheet'
-- Call the Execute method to Create the work sheet with the @WKS_Name caption,
-- which is also used as a Table reference in T-SQL
-- Neat way to define column data types in Excel worksheet
-- Sometimes converting to text is the only work-around for Excel's General
-- Cell formatting, even though the Cell contains Text, Excel tries to format
-- it in a "Smart" way, I have even had to use the single quote appended as the
-- 1st character in T-SQL to force Excel to leave it alone
EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText + adExecuteNoRecords
-- 0x80040E14 for table exists in ADO
IF @hr = 0x80040E14
-- kludge, skip 0x80042732 for ADO Optional parameters (NULL) in SQL7
OR @hr = 0x80042732
BEGIN
-- Trap these OLE Errors
IF @hr = 0x80040E14
BEGIN
PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append'
SET @WKS_Created = 0
END
SET @hr = 0 -- ignore these errors
END
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'
-- Destroy the Conn object, +++ important to not leak memory +++
EXEC @hr = sp_OADestroy @Conn
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
-- Linked Server allows T-SQL to access the XLS worksheet (Table)
-- This must be performed after the ADO stuff as the XLS must exist
-- and contain the schema for the table, or worksheet
IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT 'Created Linked Server '''+@ServerName+''' and Login'
EXEC sp_addlinkedserver @server = @ServerName
, @srvproduct = 'Microsoft Excel Workbook'
, @provider = 'Microsoft.Jet.OLEDB.4.0'
, @datasrc = @Path
, @provstr = 'Excel 8.0'
-- no login name or password are required to connect to the Jet4 ISAM linked server
EXEC sp_addlinkedsrvlogin @ServerName, 'false'
END
-- Have to EXEC the SQL, otherwise the SQL is evaluated
-- for the linked server before it exists
EXEC (@SQL)
PRINT char(9)+'Populated '''+@WKS_Name+''' table with '+CONVERT(varchar,@@ROWCOUNT)+' Rows'
-- %%% Optional you may leave the Linked Server for other XLS operations
-- Remember that the Linked Server will not create the XLS, so remove it
-- When you are done with it, especially if you delete or move the file
IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT 'Deleted Linked Server '''+@ServerName+''' and Login'
EXEC sp_dropserver @ServerName, 'droplogins'
END
GO
SET NOCOUNT OFF
PRINT ''
PRINT 'Finished CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
GO
PRINT ''
GO
SET NOCOUNT ON
DECLARE @Conn int -- ADO Connection object to create XLS
, @hr int -- OLE return value
, @src varchar(255) -- OLE Error Source
, @desc varchar(255) -- OLE Error Description
, @Path varchar(255) -- Drive or UNC path for XLS
, @Connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
, @WKS_Created bit -- Whether the XLS Worksheet exists
, @WKS_Name varchar(128) -- Name of the XLS Worksheet (table)
, @ServerName nvarchar(128) -- Linked Server name for XLS
, @DDL varchar(8000) -- Jet4 DDL for the XLS WKS table creation
, @SQL varchar(8000) -- INSERT INTO XLS T-SQL
, @Recs int -- Number of records added to XLS
, @Log bit -- Whether to log process detail
-- Init variables
SELECT @Recs = 0
-- %%% 1 = Verbose output detail, helps find problems, 0 = minimal output detail
, @Log = 1
-- %%% assign the UNC or path and name for the XLS file, requires Read/Write access
-- must be accessable from server via SQL Server service account
-- & SQL Server Agent service account, if scheduled
SET @Path = 'C:\TEMP\Test_'+CONVERT(varchar(10),GETDATE(),112)+'.xls'
-- assign the ADO connection string for the XLS creation
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'
-- %%% assign the Linked Server name for the XLS population
SET @ServerName = 'EXCEL_TEST'
-- %%% Rename Table as required, this will also be the XLS Worksheet name
SET @WKS_Name = 'People'
-- %%% Table creation DDL, uses Jet4 syntax,
-- Text data type = varchar(255) when accessed from T-SQL
SET @DDL = 'CREATE TABLE '+@WKS_Name+' (SSN Text, Name Text, Phone Text)'
-- %%% T-SQL for table population, note the 4 part naming required by Jet4 OLE DB
-- INSERT INTO SELECT, INSERT INTO VALUES, and EXEC sp types are supported
-- Linked Server does not support SELECT INTO types
SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name+' (SSN, Name, Phone) '
SET @SQL = @SQL+'SELECT au_id AS SSN'
SET @SQL = @SQL+', LTRIM(RTRIM(ISNULL(au_fname,'''')+'' ''+ISNULL(au_lname,''''))) AS Name'
SET @SQL = @SQL+', phone AS Phone '
SET @SQL = @SQL+'FROM pubs.dbo.authors'
IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
-- Create the Conn object
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT
IF @hr <> 0 -- have to use <> as OLE / ADO can return negative error numbers
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT char(9)+'Assigned ConnectionString property'
-- Set a the Conn object's ConnectionString property
-- Work-around for error using a variable parameter on the Open method
EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT char(9)+'Open Connection to XLS, for file Create or Append'
-- Call the Open method to create the XLS if it does not exist, can't use parameters
EXEC @hr = sp_OAMethod @Conn, 'Open'
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
-- %%% This section could be repeated for multiple Worksheets (Tables)
IF @Log = 1 PRINT char(9)+'Execute DDL to create '''+@WKS_Name+''' worksheet'
-- Call the Execute method to Create the work sheet with the @WKS_Name caption,
-- which is also used as a Table reference in T-SQL
-- Neat way to define column data types in Excel worksheet
-- Sometimes converting to text is the only work-around for Excel's General
-- Cell formatting, even though the Cell contains Text, Excel tries to format
-- it in a "Smart" way, I have even had to use the single quote appended as the
-- 1st character in T-SQL to force Excel to leave it alone
EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText + adExecuteNoRecords
-- 0x80040E14 for table exists in ADO
IF @hr = 0x80040E14
-- kludge, skip 0x80042732 for ADO Optional parameters (NULL) in SQL7
OR @hr = 0x80042732
BEGIN
-- Trap these OLE Errors
IF @hr = 0x80040E14
BEGIN
PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append'
SET @WKS_Created = 0
END
SET @hr = 0 -- ignore these errors
END
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'
-- Destroy the Conn object, +++ important to not leak memory +++
EXEC @hr = sp_OADestroy @Conn
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
-- Linked Server allows T-SQL to access the XLS worksheet (Table)
-- This must be performed after the ADO stuff as the XLS must exist
-- and contain the schema for the table, or worksheet
IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT 'Created Linked Server '''+@ServerName+''' and Login'
EXEC sp_addlinkedserver @server = @ServerName
, @srvproduct = 'Microsoft Excel Workbook'
, @provider = 'Microsoft.Jet.OLEDB.4.0'
, @datasrc = @Path
, @provstr = 'Excel 8.0'
-- no login name or password are required to connect to the Jet4 ISAM linked server
EXEC sp_addlinkedsrvlogin @ServerName, 'false'
END
-- Have to EXEC the SQL, otherwise the SQL is evaluated
-- for the linked server before it exists
EXEC (@SQL)
PRINT char(9)+'Populated '''+@WKS_Name+''' table with '+CONVERT(varchar,@@ROWCOUNT)+' Rows'
-- %%% Optional you may leave the Linked Server for other XLS operations
-- Remember that the Linked Server will not create the XLS, so remove it
-- When you are done with it, especially if you delete or move the file
IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT 'Deleted Linked Server '''+@ServerName+''' and Login'
EXEC sp_dropserver @ServerName, 'droplogins'
END
GO
SET NOCOUNT OFF
PRINT ''
PRINT 'Finished CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
GO
Subscribe to:
Posts (Atom)