Build a C# DNS MX (Mail Exchange) Record Query Class by Peter A. Bromberg, Ph.D.

The .NET Framework provides wrappers to native APIs for a very large number of classes and functions, including the System.Net.Dns classes. However, it doesn't cover all of them. One useful API is the set of DNS query methods in the Dnsapi.dll library, and a particularly useful technique of note is the ability to query a domain and get back the list of MX (Mail Exchange) records of the mail servers associated with the domain. Once you have the code completed to handle this, you will be able to validate an email address against its domain for the mail exchange ("MX") type entries. If there aren't any, then you have a pretty good idea that the email address you are dealing with is BOGUS. Obviously, this can be helpful in tasks such as verifying user registrations on your web site and to fight SPAM. The implementation I provide here is a simplified and very "narrow" one that is intended solely to deal with the MX record lookup. If you want a more complete implementation, there are a number of examples in the Net-o-sphere. One interesting one can be found here. For starters, it might be wise to visit the Platform SDK section on DNS to familiarize ourselves with what we need to create a managed C# PInvoke call to the underlying method. As can be seen, there are three DnsQuery calls, with DnsQuery_W (Unicode) being the most commonly used. There is an fOptions parameter where the types of queries can be combined, the wType type of record parameter, and a ppQueryResultsSet [in, out] pointer parameter. Finally, we are told that we need to free the returned RR Sets with the DnsRecordListFree API call. Note that in the sample code implementation below, VBByRefStr is used for the marshaling directive. The argument is treated as "byref" on the managed side, but stays "byval" on the unmanaged side.  If the unmanaged side writes into the buffer, it won't mangle the managed string, which remains immutable.  Instead, a different string is propagated to the managed side, preserving managed string immutability. In VB.Net, you could code as if the VBByRefStr is actually byval on the managed side, and the compiler will clean up after you, but in C# you must explicitly add the "ref" keyword in the right places: namespace PAB.DnsUtils{ using System; using System.Collections; using System.ComponentModel; using System.Runtime.InteropServices; public class DnsMx { public DnsMx() { } [DllImport("dnsapi", EntryPoint="DnsQuery_W", CharSet=CharSet.Unicode, SetLastError=true, ExactSpelling=true)] private static extern int DnsQuery([MarshalAs(UnmanagedType.VBByRefStr)]ref string pszName, QueryTypes wType, QueryOptions options, int aipServers, ref IntPtr ppQueryResults, int pReserved); [DllImport("dnsapi", CharSet=CharSet.Auto, SetLastError=true)] private static extern void DnsRecordListFree(IntPtr pRecordList, int FreeType); public static string[] GetMXRecords(string domain) { IntPtr ptr1=IntPtr.Zero ; IntPtr ptr2=IntPtr.Zero ; MXRecord recMx; if (Environment.OSVersion.Platform != PlatformID.Win32NT) { throw new NotSupportedException(); } ArrayList list1 = new ArrayList(); int num1 = DnsMx.DnsQuery(ref domain, QueryTypes.DNS_TYPE_MX, QueryOptions.DNS_QUERY_BYPASS_CACHE, 0, ref ptr1, 0); if (num1 != 0) { throw new Win32Exception(num1); } for (ptr2 = ptr1; !ptr2.Equals(IntPtr.Zero); ptr2 = recMx.pNext) { recMx = ( MXRecord) Marshal.PtrToStructure(ptr2, typeof(MXRecord)); if (recMx.wType == 15) { string text1 = Marshal.PtrToStringAuto(recMx.pNameExchange); list1.Add(text1); } } DnsMx.DnsRecordListFree(ptr2, 0); return (string[]) list1.ToArray(typeof(string)); } private enum QueryOptions { DNS_QUERY_ACCEPT_TRUNCATED_RESPONSE = 1, DNS_QUERY_BYPASS_CACHE = 8, DNS_QUERY_DONT_RESET_TTL_VALUES = 0x100000, DNS_QUERY_NO_HOSTS_FILE = 0x40, DNS_QUERY_NO_LOCAL_NAME = 0x20, DNS_QUERY_NO_NETBT = 0x80, DNS_QUERY_NO_RECURSION = 4, DNS_QUERY_NO_WIRE_QUERY = 0x10, DNS_QUERY_RESERVED = -16777216, DNS_QUERY_RETURN_MESSAGE = 0x200, DNS_QUERY_STANDARD = 0, DNS_QUERY_TREAT_AS_FQDN = 0x1000, DNS_QUERY_USE_TCP_ONLY = 2, DNS_QUERY_WIRE_ONLY = 0x100 } private enum QueryTypes { DNS_TYPE_MX = 15 } [StructLayout(LayoutKind.Sequential)] private struct MXRecord { public IntPtr pNext; public string pName; public short wType; public short wDataLength; public int flags; public int dwTtl; public int dwReserved; public IntPtr pNameExchange; public short wPreference; public short Pad; } }} The downloadable sample solution includes a simple Console Tester app that makes the following call: static void Main(string[] args) { string[] s= PAB.DnsUtils.DnsMx.GetMXRecords("microsoft.com"); foreach (string st in s) Console.WriteLine("Server: {0}",st); Console.ReadLine(); which will return the following output: Remember, hamburger@hamburgerhal.com is a valid email address. But, that doesn't mean that you can actually send mail to it. This handy class, along with some good REGEX match strings, should be sufficient to validate most any email address that can be thrown at you. The downloadable solution has more user-friendly exception handling built in.   Download the Visual Studio.NET Solution that accompanies this article

출처 : http://www.eggheadcafe.com/articles/20050129.asp

Table of Contents

1. Introduction.......................................................................................................................................... 1

2. Style Guidelines.................................................................................................................................... 2

2.1 Tabs & Indenting................................................................................................................................ 2

2.2 Bracing............................................................................................................................................... 2

2.3 Commenting........................................................................................................................................ 2

2.3.1 Documentation Comments............................................................................................................. 2

2.3.2 Comment Style............................................................................................................................. 3

2.4 Spacing............................................................................................................................................... 3

2.5 Naming............................................................................................................................................... 4

2.6 Naming Conventions............................................................................................................................ 4

2.6.1 Interop Classes............................................................................................................................. 4

2.7 File Organization................................................................................................................................. 5

Introduction

Most of us will have experienced the dread of updating documentation at some point or other. C# and Visual Studio .NET (VS.NET) give us the ability to maintain code and documentation in the same file, which makes the whole process a lot easier. VS.NET does this by taking specially marked and structured comments from within the code and building them into an XML file. This XML file can then be used to generate human-readable documentation in a variety of forms including web pages, MSDN style documentation and Intellisense within the code window.

Configure XML Commenting

VS.NET produces XML comments by taking specially marked and structured comments from within the code and building them into an XML file. This XML file can then be used to generate human-readable documentation in a variety of forms including web pages, MSDN style documentation and Intellisense within the code window. The first thing you need to do is enable the XML commenting feature for your VS.NET project.

1. Right Click on the project in the solution explorer and select "Properties".
2. Within the properties dialog double click on the “Configuration Properties” node.
3. The Build node should be already selected and you should be able to see the “XML Documentation File” entry under “Outputs”. Here is where you must enter the name of the XML file that will contain the comment data. You can call the file what you like, but for compatibility with all the features of XML commenting, it should take the form of MyAssemblyName.Xml e.g. Adjuster.BusinessServices.dll has a related XML file called Adjuster.BusinessServices.Xml

With this enabled, your XML comment data file will be rebuilt each time you build your project. Any problems that occur when trying to generate the file will not prevent a build but will be flagged in the VS.NET Task List. Assuming you do not have compile warnings set to errors.

VS.NET Task List flagging XML commenting error.

With that enabled you can start to use the special XML tags in your procedure “headers”. To get you started, place the cursor on the line directly above a procedure’s definition. Once there, press the “/” key three times, this will automatically insert a summary tag into your code. If the procedure had any arguments there should now be a param tag for each one.

/// <summary> ///  /// </summary> /// <param name="data"></param> public void SaveData(ref DataSet data) { } 

The SaveData code above is what is inserted as default

/// <summary> /// Connects to the database and attempts to apply  /// all adds, updates and deletes /// </summary> /// <param name="data">a dataset, passed by reference,  /// that contains all the  /// data for updating>/param> public void SaveData(ref DataSet data) { } 

This SaveData code is after I have added my comments describing what the routine does in the summary tag and what the data parameter is. This very simple action has given us enough to provide basic documentation including intellisense just like that provided by the .NET Framework assemblies.

It is clear from just this feature, how useful XML commenting is. When you include a reference to a .NET project that has XML commenting enabled, the XML documentation file we named earlier is copied over along with the binary to the current project’s \bin directory. This gives you the intellisense across assemblies.

The summary tag is the most basic of tags. The list below is the complete set currently supported by VS.NET. The ones marked with a * are the ones I feel are the most useful and the ones we will be dealing in the following examples.

• c
  The c tag gives you a way to indicate that text within a description should be marked as code. Use code to indicate multiple lines as code.

• code*
  The code tag gives you a way to indicate multiple lines as code. Use <c> to indicate that text within a description should be marked as code.

• example*
  The example tag lets you specify an example of how to use a method or other library member. Commonly, this would involve use of the code tag.

• exception*
  The exception tag lets you specify which exceptions a class can throw.

• include
  The include tag lets you refer to comments in another file that describe the types and members in your source code. This is an alternative to placing documentation comments directly in your source code file.

• para
  The para tag is for use inside a tag, such as <remarks> or <returns>, and lets you add structure to the text.

• param*
  The param tag should be used in the comment for a method declaration to describe one of the parameters for the method.

• paramref
  The paramref tag gives you a way to indicate that a word is a parameter. The XML file can be processed to format this parameter in some distinct way.

• permission*
  The permission tag lets you document the access of a member. The System.Security.PermissionSet lets you specify access to a member.

• remarks*
  The remarks tag is where you can specify overview information about a class or other type. <summary> is where you can describe the members of the type.

• returns
  The returns tag should be used in the comment for a method declaration to describe the return value.

• see
  The see tag lets you specify a link from within text. Use <seealso> to indicate text that you might want to appear in a See Also section.

• seealso*
  The seealso tag lets you specify the text that you might want to appear in a See Also section. Use <see> to specify a link from within text.

• summary*
  The summary tag should be used to describe a member for a type. Use <remarks> to supply information about the type itself.

• value*
  The value tag lets you describe a property. Note that when you add a property via code wizard in the Visual Studio .NET development environment, it will add a <summary> tag for the new property. You should then manually add a <value> tag to describe the value that the property represents.

MSDN Style Documentation and NDOC

We have taken the intellisense format as far as it will go, but there is much more we can do with MSDN style documentation. There is a tool that comes with VS.NET that you will find at “Tools|Build Comment Web Pages…” which will take your C# XML comments from source files and generate linked HTML files. This comes straight out of the box so should not be totally disregarded. But if you want to create easy-to-use, helpful, cross-referenced and attractive documentation, then I can strongly recommend the free, open source tool NDoc. The screenshot below is taken from a compiled help file produced from NDoc and is an example of the quality it can produce.

The two routines below will show the correct usage for most of the XML comment tags we saw earlier. The cref attribute of the exception tag is used for cross-referencing to an Exception type. This attribute is also used in the seealso, permission and see tags to reference a type. The type must be available from the current compilation environment. The compiler checks that the referenced type exists and passes relevant data to the output XML.

/// <summary> /// Gets or sets the age of the person involved in the accident /// </summary> /// <value>Age of the claimant.</value> /// <remarks> The value must be numeric. /// </remarks> /// <exception cref="System.ApplicationException">Thrown when a non- /// numeric value is assigned.</exception> public string Age { } 

This Age property once processed by NDoc will produce this.

I have drawn attention to areas in the picture and their corresponding XML comment tags.

/// <summary> /// Connects to the database and attempts to apply all adds,  /// updates and deletes /// </summary> /// <seealso cref="Adjuster.BusinessServices.Accident"/>  /// <param name="data">a dataset, passed by reference,  /// that contains all the  /// data for updating</param> /// <example> This sample shows how to call the SaveData  /// method from a wireless device. /// <code> ///  ///AccidentCRUD accCRUD = new Adjuster.BusinessServices.AccidentCRUD(); ///accCRUD.SaveData(ref ds); /// ///</code> ///</example> ///<permission cref="System.Security.PermissionSet">Everyone  ///can access this method.</Permission> public void SaveData(ref DataSet data) { } 

This SaveData method once processed by NDoc will produce this

Again I have drawn attention to areas in the picture and their corresponding XML comment tags. The Accident cross-reference in the “See Also” section is the only one that I added. By default NDoc adds cross-referencing for the parent class, the parent class’ members and the parent class’ namespace.

With the combination of NDoc and VS.Net & C#’s ability to produce these comments you can get great technical documentation at a level so close to the code, that there is absolutely no excuse for it not telling it as it is.

About Patrick Long

I have been programming now for 10 years starting with COBOL, CICS and DB2 and ending up with ASP.NET and C#. I am currently working for Charteris in London. We have a strong partnership with Microsoft UK and I my current project involves working as part of a Microsoft team. When I am not working i like playing tournament paintball. Click here to view Patrick Long's online profile.

출처 : http://www.codeproject.com/csharp/csharpcommentinganddocs.asp

Introduction

The Windows API provides a function RegNotifyChangeKeyValue, which notifies the caller about changes to attributes or the content of a specified registry key.

Unfortunately, this function is not provided by Microsoft.Win32.RegistryKey class. Because I needed that functionality, I've written a simple wrapper class.

Usage

The usage of this is straight forward. First you have to instantiate a RegistryMonitor object. You set its RegistryKey property and subscribe to the monitor's RegChanged event. Now you may start the monitoring by calling Start(). This creates an internal thread running in an endless loop. In this loop, the thread waits for both an event fired by RegNotifyChangeKeyValue and the terminate event, which is set by the Stop() method. Whenever the registry key changes, the RegChanged event is fired.

Example

Here's a console sample monitoring HKCU\Environment (that's where the current user's environment variables are stored):

public class MonitorSample{    static void Main()     {        RegistryMonitor monitor = new RegistryMonitor();        monitor.RegistryKey = Registry.CurrentUser.OpenSubKey(@"Environment",                                                              false);        monitor.RegChanged += new EventHandler(OnRegChanged);        monitor.Start();         while(true);    }             private void OnRegChanged(object sender, EventArgs e)    {        Console.WriteLine("registry key has changed");    }}

Provided with this article is another demo, which is a WinForm application (however, a VS.NET 2003 solution).

Points of interest

The implementation is pretty straightforward. One interesting point is the access to the registry handle. The RegNotifyChangeKeyValue expects a registry handle, but, unfortunately, the handle is a private member of RegistryKey. Well, reflection is your friend:

public static IntPtr GetRegistryHandle(RegistryKey registryKey){    Type type = registryKey.GetType();    FieldInfo fieldInfo = type.GetField("hkey",                               BindingFlags.Instance | BindingFlags.NonPublic);    return (IntPtr)fieldInfo.GetValue(registryKey);}

History

About Thomas Freudenberg

Click here to view Thomas Freudenberg's online profile.

출처 : http://www.codeproject.com/csharp/registrymonitor.asp

Introduction

As MFC/SDK programmers move into .NET, what surprises them most is the fact that everything is now so much more easier then ever before. Christian Graus was complaining that it was too easy and that the abominable allowance of gotos annoyed him. He might have a point there, but making coding easy is not such a bad thing after all. It's funny when you think of all the effort Chris Maunder and others put into those MFC & SDK tray icon classes that are ever so popular with copy/paste programmers. I dedicate this article to Chris M and others involved in the brilliant tray icon class project over the last few years.

Adding  the icon to your project

Ctrl-Shift-A will bring up the Add-New-Item dialog box. Select Icon File from the list of available templates. If the list is too populated to your liking select Resources from the tree control on the left. This will bring up a smaller list on the right and it will be easier for you to select Icon File. Now click open. You'll end up with the VS.NET icon editor. You may now create your icon here or copy/paste an icon from elsewhere.

Now right click on this icon from Solution Explorer. Take properties. And change the Build Action property to Embedded Resource. This will instruct the compiler to embed this icon along with your EXE file, thus saving you the annoyance of having to distribute the icon with your EXE.

Adding the NotifyIcon member to your form

Okay. Now that we have our icon ready we need to add it to our form class.

private NotifyIcon m_notifyicon;

Alright, so we have added a NotifyIcon member. Now let's initialize it and set some default properties. This should be done from the form object's constructor.

m_notifyicon = new NotifyIcon();m_notifyicon.Text = "Hello, what's cooking?"; m_notifyicon.Visible = true; m_notifyicon.Icon = new Icon(GetType(),"Icon1.ico"); //Thanks to Hasaki for this.

Alright, now compile and run your program. You'll see the icon in your tray. That was rather simple, huh? But usually people like to add a context menu to their tray icons. A tray icon simply sitting there is not very useful.

Adding a context menu to the tray icon

The first thing we need to do is to add a ContextMenu member to our form.

private ContextMenu m_menu;  

Now we need to initialize it and add some menu items.

m_menu = new ContextMenu(); m_menu.MenuItems.Add(0,     new MenuItem("Show",new System.EventHandler(Show_Click))); m_menu.MenuItems.Add(1,     new MenuItem("Hide",new System.EventHandler(Hide_Click))); m_menu.MenuItems.Add(2,     new MenuItem("Exit",new System.EventHandler(Exit_Click)));

We have added three menu items and have also associated click event handlers for each of those menu items. I could have created an array of MenuItem objects but that's not really needed here.

Now we need to associate this ContextMenu with our tray icon. So we do this.

m_notifyicon.ContextMenu = m_menu;

Now let's fill up those event handlers.

protected void Exit_Click(Object sender, System.EventArgs e) {    Close();}protected void Hide_Click(Object sender, System.EventArgs e) {    Hide();}protected void Show_Click(Object sender, System.EventArgs e) {    Show();}

Okay. Compile and run it. Now right clicking on the tray icon brings up the context menu. You can hide and show the form window using the two menu options. And the "Exit" option will exit the application.

A small problem

Now you'll notice a slight annoyance. The tray icon does not vanish when you exit the program. But when you move the mouse over the tray the icon vanishes. So, what do we do to avoid that? Again as with everything else with this whole .NET thing, it's as easy as 1,2,3. Override your form object's Dispose function and put the following lines of code into it.

protected override void Dispose( bool disposing ) {     if( disposing )     {         this.m_notifyicon.Dispose(); //we dispose our tray icon here    }    base.Dispose( disposing );}

Full Source Listing

using System;using System.Drawing;using System.Windows.Forms;using System.Data;namespace TrayTest{    public class Form1 : System.Windows.Forms.Form    {        private NotifyIcon m_notifyicon;            private ContextMenu m_menu;                public Form1()        {            Text = "TrayIcon test program";                        m_menu = new ContextMenu();                                                m_menu.MenuItems.Add(0,                new MenuItem("Show",new System.EventHandler(Show_Click)));            m_menu.MenuItems.Add(1,                new MenuItem("Hide",new System.EventHandler(Hide_Click)));            m_menu.MenuItems.Add(2,                new MenuItem("Exit",new System.EventHandler(Exit_Click)));            m_notifyicon = new NotifyIcon();            m_notifyicon.Text = "Right click for context menu";            m_notifyicon.Visible = true;            m_notifyicon.Icon = new Icon(GetType(),"Icon1.ico");            m_notifyicon.ContextMenu = m_menu;                                }                protected void Exit_Click(Object sender, System.EventArgs e)         {            Close();        }        protected void Hide_Click(Object sender, System.EventArgs e)         {            Hide();        }        protected void Show_Click(Object sender, System.EventArgs e)         {            Show();        }                protected override void Dispose( bool disposing )        {            if( disposing )            {                this.m_notifyicon.Dispose();            }            base.Dispose( disposing );        }                [STAThread]        static void Main()         {            Application.Run(new Form1());        }            }}        

Conclusion

I would like to thank James Johnson for his valuable tips while I was struggling with embedding icons into my exe. Also a special thanks to Colin for keeping me cheered up with Bobs while I was burying myself in despair after my bad experiences with the new CD writer.

About Nishant S

Editor Site Builder

Nish is a Microsoft Visual C++ MVP from Trivandrum, India and has been coding since the year 1990. Currently, Nish works as a Contract Employee for CodeProject and works from home. He has authored a romantic comedy Summer Love and Some more Cricket as well as a programming book – Extending MFC applications with the .NET Framework. He also maintains an MVP tips and tricks web site - www.voidnish.com where you can find a consolidated list of his articles, writings and ideas on VC++, MFC, .NET and C++/CLI. Nish intends to travel a lot and cover as much of the planet as he possibly can before the end of his life. It must also be mentioned here that Nish does not usually speak about himself in the 3rd person. Oh, by the way, you might want to check out his blog on C++/CLI, MFC, .NET and a lot of other stuff - blog.voidnish.com Click here to view Nishant S's online profile.

출처 : http://www.codeproject.com/csharp/trayiconmenu01.asp

Introduction

Ever got fed up with creating all the code behind parameters for your stored procedures? I have. So, I wrote this stored proc to do the code for me. (I love code that writes code). This has been written for use with "Microsoft Data Application Block" (SQLHelper.cs), however it could be hacked around to write the code without using the MDAB or even to write the code in VB. The script handles both input and output parameters, setting the size of text types, and you could even use it for direct access to a view or table.

Using the code

To implement, just copy the code into Query Analyzer and run. This will create a SPROC called "tools_CS_SPROC_Builder".

To use, just execute the SPROC passing the name of the SPROC you want the code for (see below). Note: don't include any owner prefix, e.g.: (dbo.).

EXEC tools_CS_SPROC_Builder 'mySprocsNameHere'

The message window in the Query Analyzer will write out all the code required for your class or code behind. There is a variable in "tools_CS_SPROC_Builder" called "@connName" which you can set to the name of your connection instance. By default, it's set to "conn.Connection" - just alter this for your own project's naming convention.

The code

CREATE     PROCEDURE tools_CS_SPROC_Builder(@objName nvarchar(100))AS/*___________________________________________________________________Name:  CS SPROC BuilderVersion: 1Date:  10/09/2004Author:  Paul McKenzieDescription: Call this stored procedue passing the name of your   database object that you wish to insert/update  from .NET (C#) and the code returns code to copy  and paste into your application.  This version is  for use with "Microsoft Data Application Block".    */SET NOCOUNT ONDECLARE @parameterCount intDECLARE @errMsg varchar(100)DECLARE @parameterAt varchar(1)DECLARE @connName varchar(100)//Change the following variable to the name of your connection instanceSET @connName='conn.Connection'SET @parameterAt=''  SELECT  dbo.sysobjects.name AS ObjName,  dbo.sysobjects.xtype AS ObjType, dbo.syscolumns.name AS ColName,  dbo.syscolumns.colorder AS ColOrder,  dbo.syscolumns.length AS ColLen,  dbo.syscolumns.colstat AS ColKey,  dbo.systypes.xtypeINTO #t_objFROM          dbo.syscolumns INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtypeWHERE      (dbo.sysobjects.name = @objName)  AND  (dbo.systypes.status <> 1) ORDER BY  dbo.sysobjects.name,  dbo.syscolumns.colorderSET @parameterCount=(SELECT count(*) FROM #t_obj)IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objNameIF(@errMsg is null) BEGIN  PRINT 'try'  PRINT '   {'  PRINT '   SqlParameter[] paramsToStore = new SqlParameter[' + cast(@parameterCount as varchar) + '];'  PRINT ''    DECLARE @source_name nvarchar,@source_type varchar,    @col_name nvarchar(100),@col_order int,@col_type varchar(20),    @col_len int,@col_key int,@col_xtype int,@col_redef varchar(20)   DECLARE cur CURSOR FOR  SELECT * FROM #t_obj  OPEN cur  -- Perform the first fetch.  FETCH NEXT FROM cur  INTO @source_name,@source_type,@col_name,@col_order,    @col_len,@col_key,@col_xtype   if(@source_type=N'U') SET @parameterAt='@'  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.  WHILE @@FETCH_STATUS = 0  BEGIN   SET @col_redef=(SELECT       CASE @col_xtype    WHEN 34 THEN 'Image'    WHEN 35 THEN 'Text'    WHEN 48 THEN 'TinyInt'    WHEN 52 THEN 'SmallInt'    WHEN 56 THEN 'Int'    WHEN 58 THEN 'SmallDateTime'    WHEN 59 THEN 'Real'    WHEN 60 THEN 'Money'    WHEN 61 THEN 'DateTime'    WHEN 62 THEN 'Float'    WHEN 99 THEN 'NText'    WHEN 104 THEN 'Bit'    WHEN 106 THEN 'Decimal'    WHEN 122 THEN 'SmallMoney'    WHEN 127 THEN 'BigInt'    WHEN 165 THEN 'VarBinary'    WHEN 167 THEN 'VarChar'    WHEN 173 THEN 'Binary'    WHEN 175 THEN 'Char'    WHEN 231 THEN 'NVarChar'    WHEN 239 THEN 'NChar'    ELSE '!MISSING'      END AS C)    --Write out the parameter   PRINT '   paramsToStore[' + cast(@col_order-1 as varchar)     + '] = new SqlParameter("' + @parameterAt + @col_name    + '", SqlDbType.' + @col_redef    + ');'   --If the type is a string then output the size declaration   IF(@col_xtype=231)OR(@col_xtype=167)OR(@col_xtype=175)    OR(@col_xtype=99)OR(@col_xtype=35)    BEGIN    PRINT '   paramsToStore[' + cast(@col_order-1 as varchar)      + '].Size=' + cast(@col_len as varchar) + ';'    END   PRINT '   paramsToStore['+ cast(@col_order-1 as varchar)     + '].Value =  ;'      -- This is executed as long as the previous fetch succeeds.      FETCH NEXT FROM cur   INTO @source_name,@source_type,@col_name,@col_order,     @col_len,@col_key,@col_xtype   END  PRINT ''  PRINT '   SqlHelper.ExecuteNonQuery(' + @connName +   ', CommandType.StoredProcedure,"' + @objName + '", paramsToStore);'  PRINT '   }'  PRINT 'catch(Exception excp)'  PRINT '   {'  PRINT '   }'  PRINT 'finally'  PRINT '   {'  PRINT '   ' + @connName + '.Dispose();'  PRINT '   ' + @connName + '.Close();'  PRINT '   }'    CLOSE cur  DEALLOCATE cur ENDif(LEN(@errMsg)>0) PRINT @errMsgDROP TABLE #t_objSET NOCOUNT ONGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

Output Example

try   {   SqlParameter[] paramsToStore = new SqlParameter[9];    paramsToStore[0] = new SqlParameter("@organisationid", SqlDbType.BigInt);   paramsToStore[0].Value =  ;   paramsToStore[1] = new SqlParameter("@DisplayName", SqlDbType.NVarChar);   paramsToStore[1].Size=100;   paramsToStore[1].Value =  ;   paramsToStore[2] = new SqlParameter("@DefaultCurrencyFID", SqlDbType.Int);   paramsToStore[2].Value =  ;   paramsToStore[3] = new SqlParameter("@TaxCode", SqlDbType.NVarChar);   paramsToStore[3].Size=60;   paramsToStore[3].Value =  ;   paramsToStore[4] = new SqlParameter("@UserFID", SqlDbType.BigInt);   paramsToStore[4].Value =  ;   paramsToStore[5] = new SqlParameter("@IsClient", SqlDbType.Bit);   paramsToStore[5].Value =  ;   paramsToStore[6] = new SqlParameter("@IsContractor", SqlDbType.Bit);   paramsToStore[6].Value =  ;   paramsToStore[7] = new SqlParameter("@IsSupplier", SqlDbType.Bit);   paramsToStore[7].Value =  ;   paramsToStore[8] = new SqlParameter("@IsDesigner", SqlDbType.Bit);   paramsToStore[8].Value =  ;    SqlHelper.ExecuteNonQuery(conn.Connection, CommandType.StoredProcedure,"usp_Insert_Organisation", paramsToStore);   }catch(Exception excp)   {   }finally   {   conn.Connection.Dispose();   conn.Connection.Close();   }

All you then have to do is copy-paste and fill in the values you want to pass and catch any exceptions... (Yes you do have to write some code!).

Points of Interest

In order for me to work out the @col_xtype variable from sysobjects, I ran lots of tests on a table I created with every type variation in both directions. It certainly works fine for all the usual data types, but I haven't had a chance to test all types within .NET, so if you find a bug, let me know. Enjoy!

McCodeJunky

Click here to view McCodeJunky's

멋진 사람 ㅋㅋ

출처 : http://www.codeproject.com/cs/database/CSCodeBuilder.asp

Introduction

This paper is written for users who deal with SQL Profiler or collect Microsoft SQL Server traces and want to automate trace collection, trace manipulation, and trace replay.

Knowledge of the C# language and some experience with the SQL Profiler tool is required to fully take advantage of the information provided.

SQL Server trace is a mechanism for monitoring and recording activity inside SQL Server. Essentially, when any activity occurs (a query is sent against the instance of SQL Server, for example), a special entity called an event is generated inside the server. The event is then shipped to a monitoring tool called SQL Profiler, which displays this event and all its attributes (called columns).

This combination of SQL Trace and SQL Profiler has proven to be a very powerful performance analyzing and tuning tool—it enables users to see what activity a particular application generates and what impact it has on the server.

However, the SQL Profiler tool is completely manual; therefore, it is not easy to automate the creation of the trace or perform trace analysis and trace manipulation. For most of these tasks, users must start SQL Profiler and use it interactively.

Trace and Replay objects, a new feature in Microsoft SQL Server 2005 that offers a new managed API for tracing and trace replay, are introduced to enable automation.

Trace and Replay objects simplify the management of SQL Server in the following ways:

• First, they enable users to completely automate tuning, security audits, and health monitoring of traced servers.
• Second, they enable automatic functional verification of new server releases and security packs.
• Third, they provide users with the ability to establish performance benchmarks against current server performance.

This paper discusses Trace objects and Replay objects in detail.

Trace Objects

Organizations where the tracing of SQL Servers is performed on a regular basis often have to deal with large amounts of trace data, which needs to be processed and analyzed. SQL Profiler provides features to filter the traces; such an approach can be extremely labor-intensive, however. The ability to create a small program that would perform analysis in an automated fashion would provide a better way to handle this. For example, such a program could crawl through trace data and identify the most frequently executed query on the server. Such a program could be reused and perhaps later expanded to include increasingly sophisticated analysis logic.

Another important aspect of automation is a timely reaction to events that are happening on the server. It would be convenient to have a program that could start a trace, monitor events of the trace, and perhaps alert the administrator if the duration of particular queries exceeds a certain threshold.

It would also be convenient to be able to implement a way to perform other tasks, like moving trace files into SQL Server tables, or from tables to files, in a customized fashion.

Trace Object technology can help address these and other problems.

Trace objects serve the purpose of starting a new trace, and of reading and writing trace log files and trace tables. Essentially, there are three classes for accomplishing these purposes:

• TraceServer—Starts and reads a new trace.
• TraceFile—Reads and writes to a trace file.
• TraceTable—Reads and writes to a trace table.

These classes belong to the Microsoft.SqlServer.Management.Trace namespace and provide an abstraction of a stream of trace events. This stream could be read-only (TraceServer) or provide reading and writing capabilities (TraceFile and TraceTable). Figure 1 illustrates the relationships between these classes.

Figure 1. Microsoft.SqlServer.Management.Trace namespace

Example 1: How to Start a Trace

To gain a better understanding of Trace objects, here's an example of how to construct a simple C# program that starts a trace and prints the names of the first 10 events:

1. Create a TraceServer object:

   TraceServer reader = new TraceServer(); 

2. Create an object that is responsible for storing connection information:

   ConnectionInfoBase ci = new SqlConnectionInfo("localhost"); ((SqlConnectionInfo)ci).UseIntegratedSecurity = true; 

3. Pass connection information to the InitializeAsReader() method of the TraceServer class as a first argument. The second argument is the name of the trace template file. You can create such a template file by using the SQL Profiler tool, or use stock templates that are shipped with SQL Server 2005 (templates are located in the \Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates folder):

   reader.InitializeAsReader(ci, @"Standard.tdf"); 

4. Declare an event counter:

   int eventNumber = 0; 

5. Continue reading in the loop and incrementing a counter until 10 events have printed:

   while (reader.Read()) { Console.Write( reader.GetValue(0).ToString() ); eventNumber ++; if (eventNumber == 10) break; } 

6. Close the reader:

   reader.Close(); 

The complete program looks as follows:

// need to reference // %Program Files$\Microsoft SQL // Server\90\SDK\Assemblies\ // Microsoft.SqlServer.ConnectionInfo.dll public void FileToTable() { TraceServer reader = new TraceServer(); ConnectionInfoBase ci = new SqlConnectionInfo("localhost"); ((SqlConnectionInfo)ci).UseIntegratedSecurity = true; reader.InitializeAsReader(ci, @"Standard.tdf"); int eventNumber = 0; while (reader.Read()) { Console.Write( "{0}\n", reader.GetValue(0).ToString() ); eventNumber ++; if (eventNumber == 10) break; } reader.Close(); } 

The same code is applicable to starting a trace against Analysis Services. The only difference is in the ConnectionInfo object. Instead of SqlConnectionInfo, OlapConnectionInfo should be created:

OlapConnectionInfo connection = new OlapConnectionInfo(); connection.ServerName = "localhost"; 

Note   The reader.Read() call is synchronous, meaning that it will not return until the event is read or the trace is stopped. To stop a trace, you need to call TraceServer.Stop() from a separate thread.

Example 2: FileToTable Utility

The following sample is a simple utility that converts data that is stored in a file to a table. The advantage of using this utility over the Transact-SQL fn_trace_gettable function is that this utility can read Microsoft SQL Server 7.0 and SQL Server 2000 files, and it can modify the data "as it goes."

// need to reference // %Program Files$\Microsoft SQL // Server\90\SDK\Assemblies\ // Microsoft.SqlServer.ConnectionInfo.dll //Main routine public void FileToTable() { SqlConnectionInfo connInfo = new SqlConnectionInfo("localhost"); connInfo.DatabaseName = "master"; TraceFile reader = new TraceFile(); reader.InitializeAsReader("input.trc"); TraceTable writer = new TraceTable(); writer.InitializeAsWriter(reader, connInfo, "SampleOutputTable"); writer.WriteNotify += new WriteNotifyDelegate(OnWriteNotify); while( writer.Write() ){}; writer.Close(); } // Event handler gets invoked before writing of every row private void OnWriteNotify( IDataRecordChanger currentRecord, out bool skipRecord ) { currentRecord.SetValue(1, "New Data"); skipRecord = false; } 

The preceding utility is provided as an example. With moderate modifications, you can change this utility to become a Table-to-File converter as shown in the following example:

1. The main routine in this utility is called FileToTable(). Start this routine by creating the SqlConnectionInfo object:

   SqlConnectionInfo connInfo = new SqlConnectionInfo("localhost"); connInfo.DatabaseName = "master"; 

   For more information about that class, see SQL Server Books Online for SQL Server 2005. This class basically provides connection information to the server and the database where the table will be stored (master in this case).

2. Use calls to create and initialize the TraceReader class:

   TraceFile reader = new TraceFile(); reader.InitializeAsReader("input.trc"); 

   Note   The input.trc filename is provided here. The full path is also acceptable.

3. Create and initialize the table writer object:

   TraceTable writer = new TraceTable(); writer.InitializeAsWriter(reader, connInfo, "SampleOutputTable"); 

   The table name is SampleOutputTable. The server connection information is provided earlier in Step 1.

4. Set up the OnWriteNotify event handler:

   writer.WriteNotify += new WriteNotifyDelegate(OnWriteNotify); 

   This event handler will be invoked on every write the writer performs.

5. Sit in the loop and write:

   while( writer.Write() ){}; 

6. As writing is done, close the writer (which closes the reader as well):

   writer.Close(); 

Through the IDataRecordChanger interface, the event handler can examine the record (by way of the IDataReader interface from which the IDataRecordChanger inherits the record) and change the values of the columns. In this case, the column is changed with index 1 and the value is set to the string "New Data":

currentRecord.SetValue(1, "New Data"); skipRecord = false; 

Trace Objects Advanced

This section discusses how Trace objects are built in more detail. This information is helpful, but not essential for understanding how to use the objects. Figure 2 illustrates the Trace object hierarchy.

Figure 2. Trace object hierarchy

The main interface for accessing data is the .NET Framework interface called IDataRecord from the System.Data namespace. It allows users to access a row of data at a time—a row consists of columns that can be examined by index or name. (For more information, see the .NET Framework documentation).

The IDataRecord interface is extended to IDataReader, which is also a standard .NET Framework interface. The most important thing IDataReader adds is Read() methods, which allow record navigation in sequential read-only forward-only fashion.

The methods associated with IDataReader are implemented in the abstract class TraceReader, which provides reading capabilities that all three classes (TraceServer, TraceTable, and TraceFile) inherit. TraceTable and TraceFile also possess writing capabilities that are implemented in the TraceReaderWriter class, which extends the TraceReader class.

TraceServer, TraceTable, and TraceFile, in addition to the capabilities discussed earlier, provide the following initialization methods:

• TraceServer:

  InitializeAsReader (ConnectionInfoBase serverConnInfo, String profileFileName)

  • serverConnInfo: Provides connection information to the server that will be traced.
  • profileFileName: A path to a file that contains a SQL Profiler trace profile. (For more information about how to create one, see SQL Server Books Online.)
• TraceTable:

  InitializeAsReader (ConnectionInfoBase serverConnInfo, String tableName, String tableOwner)

  • serverConnInfo: Provides connection information to the server that contains trace table.
  • tableName: The name of the table.
  • tableOwner: The schema to which the table belongs.

  InitializeAsWriter (TraceReader source, ConnectionInfoBase serverConnInfo, String destinationTableName)

  • source: A TraceReader object that contains trace data (could be TraceFile or TraceTable)
  • serverConnInfo: Provides connection information to the server that contains trace table.
  • destinationTableName: The name of the table to which the trace data is written.
• TraceFile:

  InitializeAsReader (String fileName)

  • Filename: The name of the file that contains trace data.

  InitializeAsWriter (TraceReader source, String destinationFileName)

  • source: A TraceReader object that contains trace data (could be TraceFile or TraceTable).
  • destinationFileName: The name of the file to which the trace data is written.

    Note   TraceFile and TraceTable also have InitializeAsReplayOutputWriter() methods, which will be discussed later in this paper.

Replay Objects

Replay objects provide functionality to replay a trace against an instance of SQL Server, thus simulating the load that occurred on the server at the time the trace was taken. This technology is built on top of the SQL Profiler replay that now is being made accessible by means of the programmatic interface.

Replay objects contain two classes: the TraceReplay class, which implements all the replay functionality, and the TraceReplayOptions helper class, which helps configure the TraceReplay object.

The TraceReplay class provides the following methods:

• Start()—Starts replay.
• Pause()—Pauses replay.
• Stop()—Stops replay.

The TraceReplay class provides the following properties:

• Source—a mandatory TraceReader object that contains the trace to be replayed.
• OutputFile—an optional file to which the output will be streamed.
• OutputTable—an optional table to which the output will be streamed.
• Connection—mandatory Connection information objects. TraceReplay will replay the trace against the server that is specified in this connection object.
• Options—configuration options (see below).

The TraceReplay class provides the following events:

• OnReplayStart—a handler is invoked on Replay Start.
• OnReplayPause—a handler is invoked on Replay Pause.
• OnReplayStop—a handler is invoked on Replay Stop.
• OnReplayEvent—a handler is invoked before replaying an event.
• OnReplayResultEvent—a handler is invoked on every result of repayable event.

TraceReplayOptions provides the following configuration options:

• NumberOfReplayThreads—the number of threads Replay employs.
• HealthMonitorPollInterval—how often the health monitor wakes up and examines stale threads.
• HealthMonitorWaitInterval—the stale thread maximum time to live.
• Mode—the mode of replay (either Sequential or connection level).
• KeepResults—specifies whether to keep the replay results.

Example 1: FileReplay Utility

// need to reference // %Program Files$\Microsoft SQL // Server\90\SDK\Assemblies\ // Microsoft.SqlServer.ConnectionInfo.dll public void TestReplay_SQL_File() { TraceReplay replay = new TraceReplay(); TraceFile source = new TraceFile(); source.InitializeAsReader("input.trc"); replay.Source = source; SqlConnectionInfo connection = new SqlConnectionInfo(); connection.ServerName = "localhost"; connection.UseIntegratedSecurity = true; replay.Connection = connection; replay.OnReplayEvent += new TraceReplay.OnReplayEventHandler(OnReplayEvent); replay.Start(); } public static void OnReplayEvent( int recordNumber, IDataRecordChanger currentRecord, out bool skipRecord ) { skipRecord = false; currentRecord.SetValue(2, 2); //setting database id to the new value } 

1. The main routine is called TestReplay_SQL_File(). Start this routine by creating the TraceReplay object:

    TraceReplay replay = new TraceReplay(); 

2. Use calls to create and initialize the FileReader class that will be used as a source of the replay:

    TraceFile source = new TraceFile(); source.InitializeAsReader("input.trc"); replay.Source = source; 

   Note   The input.trc filename is provided here. The full path is also acceptable.

3. Create and configure connection information by using the SqlConnectionInfo class:

   SqlConnectionInfo connection = new SqlConnectionInfo(); connection.ServerName = "localhost"; connection.UseIntegratedSecurity = true; replay.Connection = connection; 

4. Set up the OnReplay event handler:

   replay.OnReplayEvent += new TraceReplay.OnReplayEventHandler(OnReplayEvent); 

5. Start Replay:

   replay.Start(); 

   Note   The start method does not return until Replay is finished; if you need to stop or pause a replay in progress, use a separate thread.

Again, the IDataRecordChanger interface not only provides a way to examine the record (by way of the IDataReader interface from which the IDataRecordChanger inherits the record) but also changes the values of the columns. In this case the column is changed with index 2 and the value is set to 2:

skipRecord = false; currentRecord.SetValue(2, 2); //setting database ID to the new value 

Conclusion

The Trace and Replay objects technology takes advantage of the Microsoft .NET platform that is now integrated with SQL Server 2005 to enable its users to perform routine tracing and replay tasks in automated fashion.

The unique features of the Trace and Replay objects enable users to easily manipulate tracing, read and write to trace files and tables, and replay traces. Optionally, users can also modify the data in trace files by using writers' event handlers.

The scenarios of usage can be analysis of large trace file logs, live monitoring tasks, converting or altering trace data, and automated replay.

Introduction

This article describes the past, present, and future of Microsoft data access technologies, including DB-Library, ESQL, DAO, Microsoft® Data Access Components (MDAC) (including ODBC, ADO, and OLE DB), ADO.NET, and SQL Native Client. This article identifies which technologies are being enhanced, and which technologies and components are being deprecated or excluded from future releases.

Microsoft Data Access Components (MDAC)

With Microsoft Data Access Components (MDAC), developers can connect to and use data from a wide variety of relational and non-relational data sources. You can connect to many different data sources using ActiveX® Data Objects (ADO), Open Database Connectivity (ODBC), or OLE DB. You can do this through providers and drivers that are either built and shipped by Microsoft, or that are developed by various third parties.

Current MDAC Architecture

With the current MDAC architecture, client-server, n-tier, or Web browser applications can access SQL, Semi-Structured, and Legacy Data stores. Additionally, with MDAC (depending on requirements), these applications have the flexibility to access the data using ADO, OLE DB, or ODBC.

Figure 1. Current MDAC architecture

For the purposes of this document, you can divide the MDAC stack into the following components, based on technology and products:

Current MDAC Components

These components are supported in the current release. Use these components when you develop new applications or upgrade existing applications.

• ADO: ActiveX Data Objects (ADO) provides a high-level programming model that will continue to be enhanced. Although a little less performant than coding to OLE DB or ODBC directly, ADO is straightforward to learn and use, and can be used from script languages such as Microsoft Visual Basic® Scripting Edition (VBScript) or Microsoft JScript®.
• ADOMD: ADO Multi-Dimensional (ADOMD) is to be used with multi-dimensional data providers such as Microsoft OLAP Provider, also known as Microsoft Analysis Services Provider. No major feature enhancements have been made to it since MDAC 2.0; however, it will be available on the 64-bit Microsoft Windows® operating system.
• ADOX: ADO Extensions for DDL and Security (ADOX) enables the creation and modification of definitions of a database, a table, an index, or a stored procedure. You can use ADOX with any provider. The Microsoft Jet OLE DB Provider provides full support for ADOX, while the Microsoft SQL OLE DB Provider provides limited support. No major enhancements are planned for ADOX in future MDAC releases; however, it will be available on the 64-bit Windows operating system.
• OLE DB: OLE DB is a comprehensive set of COM interfaces for accessing a diverse range of data in a variety of data stores. OLE DB providers exist for accessing data in databases, file systems, message stores, directory services, workflow, and document stores. OLE DB core services (though not every OLE DB provider) will be available on the 64-bit Windows operating system.
• SQLOLEDB: Microsoft OLE DB Provider for SQL Server (SQLOLEDB) supports access to Microsoft SQL Server 6.5 and later. This OLE DB Provider will be the primary focus of future MDAC feature enhancements. It will be available on the 64-bit Windows operating system.
• Microsoft SQL Server Network Libraries: The SQL Server Network Libraries allow SQLOLEDB and SQLODBC to communicate with the SQL Server database. The following SQL Server Network Libraries are currently deprecated in MDAC releases: Banyan Vines, AppleTalk, Servernet, IPX/SPX, Giganet, and RPC. TCP/IP, Named Pipes, and Shared Memory SQL Server Network Libraries will continue to be enhanced and will be available on the 64-bit Windows operating system.
• ODBC: The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that allows applications to access data from a variety of Database Management Systems (DBMS). Applications using this API are limited to accessing relational data sources only. ODBC will be available on the 64-bit Windows operating system.
• SQLODBC: Microsoft SQL Server ODBC Driver (SQLODBC) enables access to Microsoft SQL Server 6.5 and later. SQLODBC will be available on the 64-bit Windows operating system.

Deprecated MDAC Components

These components are still supported in the current release of MDAC, but might be removed in future releases. Microsoft recommends that when you develop new applications, you avoid using these components. Additionally, when you upgrade or modify existing applications, remove any dependency on these components.

• Jet: Starting with version 2.6, MDAC no longer contains Jet components. In other words, MDAC 2.6, 2.7, 2.8, and all future MDAC releases do not contain Microsoft Jet, Microsoft Jet OLE DB Provider, or the ODBC Desktop Database Drivers.
• MSDASQL: The Microsoft OLE DB Provider for ODBC (MSDASQL) provides ADO clients access to databases through ODBC Drivers. This has been the default provider for ADO; however, for future releases of MDAC and the 64-bit Windows operating system, MSDASQL has been deprecated. Therefore, to access the database from ADO, clients must use appropriate Native OLE DB Providers, such as SQLOLEDB, to access Microsoft SQL Server. MSDASQL will be not available on the 64-bit Windows operating system; however, it will still be possible to use on the 64-bit Windows operating system through the 32-bit Windows subsystem.
• MSDADS: With the Microsoft OLE DB Provider for Data Shaping (MSDADS), you can create hierarchical relationships between keys, fields, or rowsets in an application. No major feature enhancements have been made since MDAC 2.1. This Provider has been deprecated in future MDAC releases. Microsoft recommends that you use XML instead of MSDADS.
• Oracle ODBC: The Microsoft Oracle ODBC Driver (Oracle ODBC) provides access to Oracle database servers. It provides full support for Oracle 7. It also uses Oracle 7 emulation to provide limited support for Oracle 8 databases. The Oracle ODBC Driver has not yet been tested with Oracle 9 databases.
• RDS: Remote Data Services (RDS) is a proprietary Microsoft mechanism to access remote ADO Recordset objects across the Internet or an intranet. No major feature enhancements have been made to RDS since MDAC 2.1. This component is being deprecated. Microsoft now ships the Microsoft SOAP Toolkit 2.0, in which applications can access remote data by using an open, XML-based standard. Applications that use RDS should migrate to SOAP.
• JRO: The Microsoft Jet OLE DB Provider and other related components have been removed from the MDAC stack since MDAC 2.6. Jet Replication Objects (JRO) is used only with Jet (Access) databases, basically to create or compact the Jet Database and Jet Replication Management. JRO has been deprecated and MDAC 2.7 will be its last release. It will not be available on the 64-bit Windows operating system.
• SQL XML: SQL XML provides extensions to Microsoft OLE DB Provider for SQL Server (SQLOLEDB) to allow clients to request Microsoft SQL Server 2000 data through XML, and to retrieve XML streams. It was first released with MDAC 2.6. With SQL XML Web Release 1, clients can insert, update, and delete data in SQL Server 2000 using Updategrams and Bulk Load. This component is not being deprecated, but it is being removed from future MDAC releases. Current and later versions of this product are available as Web downloads. SQL XML will be available on the 64-bit Windows operating system.

MDAC Releases

Here is a list of the supportability scenarios of past, present, and future MDAC releases, starting with the earliest.

• MDAC 1.5, MDAC 2.0, and MDAC 2.1: These versions of MDAC were independent releases that were released through the Microsoft Windows NT® Option Pack, the Microsoft Windows Platform SDK, or the MDAC Web site. These versions of MDAC are no longer supported.
• MDAC 2.5: This version of MDAC was included with the Windows 2000 operating system. Future services packs of MDAC 2.5 will be included with corresponding Windows 2000 service packs. Additionally, these MDAC services packs will be released to the MDAC Web site in accordance with the Windows 2000 service pack release schedule. You can only install this version of MDAC on Windows NT, Windows 95, and Windows 98 platforms. You can only install this version on Windows 2000 and Windows Millennium Edition platforms through the operating systems or their services packs. This version is currently supported.
• MDAC 2.6: MDAC 2.6 RTM, SP1, and SP2 were included with Microsoft SQL Server 2000 RTM, SP1, and SP2, respectively. Additionally, these MDAC service packs were released to the MDAC Web site in accordance with the Microsoft SQL Server 2000 service pack release schedule. You can install this version of MDAC and its service packs on Windows 2000, Windows Millennium Edition, Windows NT, Windows 95, and Windows 98 platforms. This version is no longer supported.
• MDAC 2.7: This version of MDAC is included with the Microsoft Windows XP RTM and SP1 operating systems. You can install this version of MDAC and its service packs on Windows 2000, Windows Millennium, Windows NT, and Windows 98 platforms. You can only install this version on the Windows XP platform through the operating system or its services packs.
  • The 32-bit version of MDAC 2.7 has been released to the MDAC Web site.
  • The 64-bit version of MDAC 2.7 will release with the 64-bit version of Windows XP only.
• MDAC 2.8: This version of MDAC is included with Windows Server 2003 and Windows XP SP2 and later.
  • The 32-bit version of MDAC 2.8 will also be released to the MDAC Web site at the same time Windows Server 2003 is released to the customer.
  • The 64-bit version of MDAC 2.8 will release with the 64-bit version of Windows Server 2003 only.

SQL Native Client (SQLNCLI)

SQL Native Client (SQLNCLI) is a data access technology that is new to Microsoft SQL Server 2005, and it is a stand alone data access Application Programming Interface (API) that is used for both OLE DB and ODBC. It combines the SQL OLE DB Provider and the SQL ODBC Driver into one native dynamic link library (DLL) while also providing new functionality that is separate and distinct from the Microsoft Data Access Components (MDAC). SQL Native Client can be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features such as Multiple Active Result Sets (MARS), User-Defined Types (UDT), and XML data type support.

ADO.NET

ADO.NET is an evolutionary improvement over traditional ADO for creating distributed, data sharing applications. It is a high-level application programming interface that is targeted at loosely coupled, n-tier, Internet-based applications that support disconnected access to data. It is a core component of the Microsoft .NET Framework.

Figure 2. ADO.NET architecture

ADO.NET provides .NET-managed providers for connected access, and DataSets that read and write in XML for disconnected management of retrieved data and user interaction. The following data providers are available for ADO.NET:

• Microsoft SQL .NET Data Provider: This provider allows .NET applications to directly access a Microsoft SQL Server database.
• Microsoft OLE DB .NET Data Provider: This provider allows .NET applications to access databases using their native OLE DB Providers.
• Microsoft ODBC .NET Data Provider: This provider allows .NET applications to access databases by using their ODBC drivers.
• Microsoft Oracle .NET Data Provider: This provider allows .NET applications to access an Oracle database.

The DataSet reads and writes XML and the XMLDataDocument integrates relational and XML views.

Obsolete Data Access Technologies

Obsolete technologies are technologies that have not been enhanced or updated in several product releases and that will be excluded from future product releases. Do not use these technologies when you write new applications. When you modify existing applications that are written using these technologies, consider migrating those applications to ADO.NET.

The following components are considered obsolete:

• DB-Library: This is a SQL Server-specific programming model that includes C APIs. There have been no feature enhancements to the DB-Library since SQL Server 6.5. Its final release was with SQL Server 2000 and it will not be ported to the 64-bit Windows operating system.
• Embedded SQL (E-SQL): This is a SQL Server-specific programming model that enables Transact-SQL statements to be embedded in Visual C code. No feature enhancements have been made to the E-SQL since SQL Server 6.5. Its final release was with SQL Server 2000 and it will not be ported to the 64-bit Windows operating system.
• Data Access Objects (DAO): DAO provides access to JET (Access) databases. This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®, and scripting languages. It was included with Microsoft Office 2000 and Office XP. DAO 3.6 is the final version of this technology. It will not be available on the 64-bit Windows operating system.
• Remote Data Objects (RDO): RDO was specifically designed to access remote ODBC relational data sources, and made it easier to use ODBC without complex application code. It was included with Microsoft Visual Basic versions 4, 5, and 6. RDO version 2.0 was the final version of this technology.

For More Information

For more information, visit the following Microsoft Web site:

• The Data Access and Storage Developer Center Web site

출처 : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/data_mdacroadmap.asp