Blog

posts from 2003

Integrate NDoc HTML Help in VS.NET

0 Comments
By Fons Sonnemans, 30-12-2003

Download NDocSample.zip

Introduction

NDoc 1.2 generates class libraries documentation from .NET assemblies and the XML documentation files generated by the C# compiler. NDoc uses add-on documenters to generate documentation in several different formats, including the MSDN-style HTML Help format (.chm), the Visual Studio .NET Help format (HTML Help 2), and MSDN-online style web pages.

This article explains the four steps to take in order to integrate the HTML Help 2 file you generated into Visual Studio.NET.

  1. Comment the Library using XML documentation tags
  2. Create HTML Help 2 using NDoc
  3. Registering the Help File using H2Reg
  4. Include Help Collection to VSCC

Step 1: Comment the Library using XML documentation tags

I have created a sample library with two classes Employee and EmployeeCollection. These classes are documented using the XML documentation tags (///). The assembly is named ReflectionIT.NDocSample.

using System;

namespace ReflectionIT.NDocSample
{
    /// <summary>
    /// Employee class used to demonstrate NDoc and Visual Studio.NET integration.
    /// </summary>
    /// <example>
    /// Some sample:
    /// <code>
    /// Employee emp = new Employee("Jim", 4000);
    /// emp.RaiseSalary();
    /// Console.WriteLine(emp.GetYearSalary);</code>
    /// </example>
    public class Employee
    {
        privatestring_name;
        privateint_salary;

        /// <summary>
        /// Initializes a new instance of the Employee class with a name and salary.
        /// </summary>
        /// <param name="name">The name of the employee</param>
        /// <param name="salary">The salary of the employee</param>
        public Employee(stringname, intsalary)
        {
            this.Name = name;
            this.Salary = salary;
        }

        /// <summary>
        /// Gets or sets the name of the employee.
        /// </summary>
        publicstring Name {
            get{return this._name;}
            set{this._name= value;}
        }

        /// <summary>
        /// Gets or sets the salary of the employee.
        /// </summary>
        publicint Salary {
            get{return this._salary;}
            set{this._salary= value;}
        }

        /// <summary>
        /// Returns the year salary for the employee using 12 months
        /// </summary>
        /// <returns>The year salary</returns>
        publicvirtualint GetYearSalary() {
            return Salary *12;
        }

        /// <summary>
        /// Raise the salary with 10%
        /// </summary>
        publicvirtualvoid RaiseSalary() {
            Salary += Salary *10/100;
        }
    }
}

The assembly is named ReflectionIT.NDocSample. I have set the name of the xml file in which the documentation comments are processed to ReflectionIT.NDocSample.xml. Make sure this is the same name as the assembly otherwise IntelliSense will not work correctly. Don't forget to compile the assembly; this will create the xml file.

Step 2: Create HTML Help 2 using NDoc

NDoc comes with a GUI front end (NDocGUI.exe) and a console application (NDocConsole.exe). I have used the NDocGui to create a NDoc project for my NDocSample library. You can easily do this using the 'New from Visual Studio Solution...' menu option.


Next you have to set the Documentation Type to 'HtmlHelp2'. I have also changed some extra properties like: CopyrightHref, CopyrightText, HtmlHelpName and Title.

Then you are ready to build the help file. Before you can do this you have to install Microsoft Html Workshop and Microsoft Visual Studio .NET Help Integration Kit (VSHIK2003). You build the help file using the Documentation, Build menu option (Ctrl+Shift+B). The result is an ReflectionIT.NDocSample.chm help file which NDoc converted to compiled Html Help.

Step 3: Registering the Help File using H2Reg

I have used H2Reg to register the NDocSample help file. H2Reg.exe is a small utility (177K), that allows you to register MS Help 2.x Collections: Namespaces, Titles, Plug-ins, and Filters without using MSI (Microsoft Installer).

To use H2Ref you have to create an ini file in which you describe what you want to register. Below you see the H2Reg_cmd.ini file I used to register the NDocSample help file.


; - - - - - - - Register -r switch

[Reg_Namespace]
;<nsName>|<nsColfile>|<nsDesc>
ReflectionIT.NDocSample|ReflectionIT.NDocSample.HxC|ReflectionIT NDocSample

[Reg_Title]
;<nsName>|<TitleID>|<LangId>|<HxS_HelpFile>|<HxI_IndexFile>|<HxQ_QueryFile>|
;<HxR_AttrQueryFile>|<HxsMediaLoc>|<HxqMediaLoc>|<HxrMediaLoc>|<SampleMediaLoc>
ReflectionIT.NDocSample|ReflectionIT.NDocSample|1033|ReflectionIT.NDocSample.HxS|||||||

;------- UnRegister -u switch

[UnReg_Namespace]
;<nsName>
ReflectionIT.NDocSample

[UnReg_Title]
;<nsName>|<TitleID>|<LangId>
ReflectionIT.NDocSample|ReflectionIT.NDocSample|1033


I have placed this ini file together with the ReflectionIT.NDocSample.HxC and ReflectionIT.NDocSample.HxC files into a folder called Help. In this folder you have to start H2Reg with the -R switch which I have automated using the following register.bat batch file.

copy . .\Ndoc\doc\*.hxS
copy..\Ndoc\doc\*.hxC
"C:\Program Files\Helpware\H2Reg\H2Reg.exe" -r cmdfile="C:\My Documents\Visual Studio Projects\NDocSample\Help\H2Reg_cmd.ini"

Step 4: Include Help Collection to VSCC

The last step is to include the help collection in Visual Studio .NET 2003 Combined Help Collection (VSCC). Therefore you have to open the 'Visual Studio .NET Combined Help Collection Manager' page using the following hyperlink: ms-help://MS.VSCC.2003/VSCCCommon/cm/CollectionManager.htm. In this page you have to select the 'ReflectionIT.NDocSample' checkbox and click the 'Update VSCC' button.


You can change the Title attribute of HelpCollection node in the .HxC file when you want a more descriptive name.

Result

The result off all these steps is great. You will see the help file integrated into Visual Studio.NET. It is context sensitive (F1) and will also appear in the Dynamic Help.



Conclusion

The Xml documentation comments of C# are great. With NDoc and H2Reg you can generate an Html Help 2 file and integrate it in Visual Studio.NET. This will make your code libraries really (re)usable.

I haven't figured out how to add Filters and Contents (TOC). I hope to update this article soon with these options. When you find this all to difficult you should look at DocumentX which is a lot easier to use, but not for free.

Any suggestions and feedback for improving this Add-In is most welcome. Send your suggestions and feedback to Fons.Sonnemans@reflectionit.nl

.NET Quick Reference Card

0 Comments
By Fons Sonnemans, 7-11-2003

Download DotNetRefCard.pdf

I have created a small quick reference card for Microsoft .NET with some hard to remember details. My printer supports two pages per side and double sided printing. This makes it possible to print the 4 pages on a single sheet which makes it easier to use.

Contents:

  • C# String escape sequence
  • C# Numeric & Char Literals
  • String Formatting
  • Standard Numeric Format Strings
  • Custom Numeric Format Strings
  • Standard DateTime Format Strings
  • Custom DateTime Format Strings
  • Regular Expressions

I hope you will find it useful.

Any suggestions and feedback for improving this reference card is most welcome. Send your suggestions and feedback to Fons.Sonnemans@reflectionit.nl

LocalizedTextProvider Component

0 Comments
By Fons Sonnemans, 10-8-2003

Download LocalizedText.zip

Introduction

I have read that the .NET framework has great features to localize your application. I had to use them recently to create an English, Dutch, French and German "version" of one of my applications. I found out they worked well but it was very time consuming to set all the text properties of all controls for each language. Even worse you have to translate common terms like: OK, Cancel, New, Open, Close, etc. on every form if you want to use the designer. You can, of course, write code to fetch them all from one resource file. But I don't like to write code, especially if you don't have to.

I came up with a solution for this problem, called the LocalizedTextProvider component. This component is actually an extender which, adds a LocalizedText property to a Control or MenuItem. When you add this component to a WinForm it will allow you to set the LocalizedText property ('Misc' category) of all controls on your form. You can select a Key from a predefined list. This list contains all keys from the string resources in the component. I have filled the list using the International Word List from the 'Microsoft Official Guidelines for User Interface Developers and Designers' website.



Example: the LocalizedText property for the Cancel button is set to Microsoft.Cancel

The Text for the Cancel button will automatically be translated to 'Annuleren' for Dutch users, 'Abbrechen' for German users and 'Annuler' for French users. You can test this by setting the CurrentUICulture property of the CurrentThread to a Dutch, German or French CultureInfo.

[STAThread]
staticvoid Main()
{
    Thread.CurrentThread.CurrentUICulture =new System.Globalization.CultureInfo("nl");
    //Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("fr");
    //Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("de");

    Application.Run(new Form1());
}

LocalizedTextProvider class

The LocalizedTextProvider class is derived from System.ComponentModel.Component and implements the System.ComponentModel.IExtenderProvider interface. This makes it an extender. The ProvideProperty attribute on the class specifies the name of the property (LocalizedText) that it offers to other components. The CanExtend method specifies whether this object can provide its extender properties to the specified object. In this case a Control or MenuItem. The GetLocalizedText() and SetLocalizedText() methods retrieve and store the resource key into a Hashtable. The UpdateText() method uses this key to retrieve the string from the resource file.

[ProvideProperty("LocalizedText", typeof(Component))]
publicclass LocalizedTextProvider : System.ComponentModel.Component, System.ComponentModel.IExtenderProvider, ISupportInitialize {
    /// <summary>
    /// Required designer variable.
    /// </summary>
    private System.ComponentModel.Container components=null;

    private Hashtable _localizedTextList=new Hashtable();
    internal conststring ResourceKey ="ReflectionIT.Windows.Forms.Resources.Microsoft";
    internal readonly ResourceManager ResourceManager =new ResourceManager( ResourceKey, System.Reflection.Assembly.GetExecutingAssembly());
    internal conststring DefaultText ="(None)";

    /// <summary>
    /// Required for Windows.Forms Class Composition Designer support
    /// </summary>
    public LocalizedTextProvider(System.ComponentModel.IContainer container){
        container.Add(this);
        InitializeComponent();
    }

    /// <summary>
    /// Required for Windows.Forms Class Composition Designer support
    /// </summary>
    public LocalizedTextProvider() {
        InitializeComponent();
    }

    #region Component Designer generated code
    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InitializeComponent(){
        components=new System.ComponentModel.Container();
    }
    #endregion

    /// <summary>
    /// Specifies whether this object can provide its extender properties to the specified object.
    /// </summary>
    /// <param name="target">The Object to receive the extender properties.</param>
    /// <returns>true if this object can provide extender properties to the specified object; otherwise, false</returns>
    bool IExtenderProvider.CanExtend(objecttarget) {
        return(target is Control)|(target is MenuItem);
    }

    /// <summary>
    /// Get the key of the LocalizedText for the given component
    /// </summary>
    /// <param name="component">Control or MenuItem</param>
    /// <returns>Key of the LocalizedText</returns>
    [Category("Misc")]
    [Editor(typeof(LocalizedTextEditor), typeof(System.Drawing.Design.UITypeEditor))]
    [DefaultValue(LocalizedTextProvider.DefaultText)]
    [Description("The LocalizedText for this control")]
    publicstring GetLocalizedText(Component component){
        if(_localizedTextList.ContainsKey(component)) {
            if(_localizedTextList[component]!= null){
                return(string)(_localizedTextList[component]);
            }
        }

        return LocalizedTextProvider.DefaultText;
    }
    
    /// <summary>
    /// Set the LocalizedText for the given component
    /// </summary>
    /// <param name="component">Control or MenuItem</param>
    /// <param name="value">Key of the LocalizedText</param>
    public void SetLocalizedText(Component component,stringvalue) {
        _localizedTextList[component]=value;

        if(value !=null&&value!= LocalizedTextProvider.DefaultText){
            UpdateText(component);
        }        
    }

    /// <summary>
    /// Update the text for the component
    /// </summary>
    /// <param name="component">Control or MenuItem</param>
    private void UpdateText(Component component){
        string s = GetLocalizedText(component);

        if(s !=null&& s.Length > 0){
            if(componentis Control) {
                ((Control)component).Text = ResourceManager.GetString(s);
            }else{
                if(componentis MenuItem){
                    ((MenuItem)component).Text = ResourceManager.GetString(s);
                }
            }
        }
    }

    #region Implementation of ISupportInitialize

    publicvoid BeginInit(){
        // ignore
    }

    /// <summary>
    /// Update the Text for the all components, overwrites the manually set Text
    /// </summary>
    public void EndInit(){
        foreach(Component componentin_localizedTextList.Keys) {
            UpdateText(component);
        }
    }

    #endregion

}

The GetLocalizedText() method has a Editor attribute which specifies the LocalizedTextEditor used to change a property. The LocalizedTextEditor class is derived from PeterBlum.UITypeEditorClasses.BaseDropDownListTypeEditor. This base class makes it very easy to create your own editor. You only have to override the method FillInList() and fill the listbox. I have downloaded it from Peter's website, thanks Peter.

/// <summary>
/// TypeEditor for LocalizedText used by the LocalizedTextProvider
/// </summary>
public class LocalizedTextEditor : PeterBlum.UITypeEditorClasses.BaseDropDownListTypeEditor {

    privatestatic ArrayList _keys;

    /// <summary>
    /// Static Constructor: Create a list of all Keys from the ResourceFile
    /// </summary>
    static LocalizedTextEditor() {
        _keys=new ArrayList();
        Assembly a = Assembly.GetExecutingAssembly();
        System.IO.Stream rs = a.GetManifestResourceStream(LocalizedTextProvider.ResourceKey +".resources");
        if(rs !=null){
            ResourceReader rr= new ResourceReader(rs);

            foreach(DictionaryEntry d inrr) {
                if(d.Value isstring){
                    _keys.Add((string)d.Key);
                }
            }
        }
    }

    /// <summary>
    /// Add all keys to the ListBox
    /// </summary>
    /// <param name="pContext"></param>
    /// <param name="pProvider"></param>
    /// <param name="pListBox"></param>
    protected overridevoid FillInList(ITypeDescriptorContext pContext, IServiceProvider pProvider, ListBox pListBox){
        pListBox.Items.Add(LocalizedTextProvider.DefaultText);
        foreach(string keyin_keys){
            pListBox.Items.Add(key);
        }
        pListBox.Sorted = true;
    }

}

Conclusion

Localization isn't hard but it is a lot of work. I hope my LocalizedTextProvider component makes it easier for you. You will probably have to extend the resource files with extra keys and or languages. Have a try.

Any suggestions and feedback for improving this article is most welcome. Send your suggestions and feedback to Fons.Sonnemans@reflectionit.nl


Assembly Comparison Tool (WinChurn)

0 Comments
By Fons Sonnemans, 11-7-2003

I have found a great tool on the GotDotNet user samples website

WinChurn allows you to compare two versions of the same managed assembly. This is extremely helpful when tracking changes in your assembly. The tool comes with a help system, as well as some examples to get you started. Optionally, the tool allows you to generate a policy file, however caution is urged when using this feature. While the tool is extremely robust and accurate at detecting public API changes, it cannot detect internal changes. The tool uses Reflection to generate the results.

Assembly Comparison Tool (WinChurn)

C# Minesweeper Game

5 Comments
By Fons Sonnemans, 29-6-2003

Download Minesweeper.zip

Introduction

I have always wondered how hard it would be to write a game in C#. I picked Minesweeper for my first try. Made an OO design and then went programming.

See the result in the downloadable zip file. The whole program is less then 400 lines of code!


Screenshot

I have reduced the complexity a bit by eliminating the flag-icon and all menu options.


Design

The application is build using 3 objects: a Form, a Game which holds an array of Square objects.


UML Class Diagram (Visio)

MinewsweeperForm class
This class creates a Game object when the Start button is clicked. It also updates the Timer and Mines display. It contains a Panel control in which the Game object generates the Squares.

Game class
This class contains a multidimensional array of Square objects which is initialized in the constructor. The mines are randomly placed in the squares. It has a Timer which is used to calculate the elapsed gametime. The events DismantledMinesChanged and Tick are used to notify the MinesweeperForm that is has to update the GUI.

Square class
This class contains a normal WinForm Button object which Click and MouseDown events are captured. These events trigger the real game logic. The number of surrounding mines is calculated (cascading). Mines are dismantled (marked) or explode which is reported to the Game object using events.

Conclusion

Writing the Minesweeper game was a lot of fun. It demonstrates how object orientation really makes it easy. Examine it by stepping trough the code using the VS.NET debugger. I hope you enjoyed this article and sample.

Any suggestions and feedback for improving this article is most welcome. Send your suggestions and feedback to Fons.Sonnemans@reflectionit.nl

Tags: CSharp, Apps

I Love CodeSmith

0 Comments
By Fons Sonnemans, 9-6-2003

I have tried CodeSmith for the first time and I love it. CodeSmith allows you to create templates that will generate code for any ASCII based language. It is simple and very powerful. You can easily adjust it to fit your needs.

You can download it from CodeSmith (it's FREEWARE)

C# InputBox

1 Comments
By Fons Sonnemans, 30-4-2003

Download InputBoxSample.zip

Introduction

Visual Basic 6.0 has an InputBox() function, Visual Basic.NET has one but in C# you don't. You can solve this easily by adding a reference to 'Microsoft.VisualBasic.dll' and use the static method Microsoft.VisualBasic.Interaction.InputBox().

The VB implementation has some shortcomings which I solved in my improved InputBox class. You never know whether the user entered a empty text or clicked the Cancel button. It is also impossible to have validation on the text.


Example: InputBox

InputBox class

The InputBox class is a public class with a private constructor. You use it by calling the static Show method. This method instantiates a new instance of the class, sets it's properties and returns the result. The result is not a string but a InputBoxResult object. This object has two properties: OK and Text. OK is a boolean indicating that the user clicked the OK button and not the Cancel button. The Text contains the string the user entered.

public static InputBoxResult Show(string prompt,stringtitle, stringdefaultResponse,
                                 InputBoxValidatingHandler validator,intxpos,intypos){
    using (InputBox form=new InputBox()) {
        form.labelPrompt.Text =prompt;
        form.Text = title;
        form.textBoxText.Text =defaultResponse;
        if(xpos >=0 &&ypos >=0){
            form.StartPosition = FormStartPosition.Manual;
            form.Left = xpos;
            form.Top = ypos;
        }
        form.Validator = validator;

        DialogResult result=form.ShowDialog();

        InputBoxResult retval=new InputBoxResult();
        if(result == DialogResult.OK){
            retval.Text = form.textBoxText.Text;
            retval.OK = true;
        }
        returnretval;
    }
}

publicstatic InputBoxResult Show(string prompt,stringtitle, stringdefaultText,
                                 InputBoxValidatingHandler validator){
    return Show(prompt, title,defaultText,validator,-1,-1);
}

Usage

You activate the InputBox by calling the static Show() method. It has 4 required and 2 optional arguments (using overloading).

private void buttonTest_Click ( object sender , System.EventArgs e) {
    InputBoxResult result= InputBox.Show("Test prompt:","Some title","Default text", null);
    if(result.OK){
        textBox1.Text = result.Text;
    }
}


Validation

You can add validation logic using the validator argument. The validator is a InputBoxValidatingHandler delegate which you can use to validate the Text. The following sample checks whether the Text is not empty. If so it sets Cancel to true and the Message to 'Required'.

private void buttonTest_Click ( object sender , System.EventArgs e) {
    InputBoxResult result= InputBox.Show("Test prompt:","Some title","Default text",
                                             new InputBoxValidatingHandler(inputBox_Validating));
    if(result.OK){
        textBox1.Text = result.Text;
    }
}

privatevoid inputBox_Validating(objectsender, InputBoxValidatingArgs e){
    if(e.Text.Trim().Length ==0){
        e.Cancel =true;
        e.Message ="Required";
    }
}


Example: Required text

Conclusion

The ImputBox class is just a simple class which you can use in your Windows Forms application to prompt for a text. It can also be used from Visual Basic.NET when you compile it in a Library and reference this Library from you VB project.

Any suggestions and feedback for improving this article is most welcome. Send your suggestions and feedback to Fons.Sonnemans@reflectionit.nl

Free VS.NET Add-In: Spelly.NET

0 Comments
By Fons Sonnemans, 1-4-2003

Spelly is a Visual Studio addin that allows you to easily spell-check source code and/or comments. Spelly is smart enough to understand identifiers with under_scores and MixedCase.

Spelly is available for both Visual Studio .NET and Visual C++ 5/6.

SQL-strings considered harmful

0 Comments
By Fons Sonnemans, 10-3-2003

Download SqlInsert.zip

Introduction

Did you know that any malicious user can corrupt your database by injecting harmful SQL strings? To prevent SQL injection, you can use the parameters collections when building SQL strings. However, I developed a more sophisticated method to construct SQL statements from user's input and execute them safely. In this article I describe how to write and execute SQL statements by using objects instead of SQL strings. These objects also address specific SQL statement syntax issues on different RDBMS: they enable you to write generic and RDBMS independent code.

SQL Inject

An SQL statement that is to be executed by SQL Server is in most applications constructed using an SQL string (or StringBuilder) and executed by means of a command object. An SQL string can contain two or more SQL statements (each statement separated by a semi colon) that all will be executed by SQL Server.

If you build SQL strings using unfiltered input, your application may be subject to malicious user input (remember, never trust user input). The risk is that when you insert user input into a string that becomes an executable statement, a malicious user can append SQL commands to your intended SQL statements by using escape characters.

Example: consider a form in which a record has to be added to a table called Titles. The user can type field values like title (name), publisher-ID, price etc. in textboxes on the form. On OK click the InsertTitle() method is called using these input values as arguments.


private void buttonOK_Click ( object sender , System.EventArgs e) {
    InsertTitle(textBoxId.Text, textBoxTitle.Text,comboType.Text,
                numberBoxPubId.Value,numberBoxPrice.Value,
                numberBoxAdvance.Value,numberBoxYTDSales.Value,
                textBoxNotes.Text,datePickerPubDate.Value);
}

privatevoid InsertTitle(string id,stringtitle, stringtype,
    intpubId,double price,doubleadvance, intytd_sales,
    string notes, DateTime pubdate){

    StringBuilder sql =new StringBuilder("insert into titles ");
    sql.Append("(title_id, title, type, pub_id, price, advance, ");
    sql.Append("ytd_sales, notes, pubdate)");
    sql.Append(" values (");
    sql.Append("'").Append(id).Append("' ,");
    sql.Append("'").Append(title).Append("' ,");
    sql.Append("'").Append(type).Append("' ,");
    sql.Append(pubId).Append(" ,");
    sql.Append(price).Append(" ,");
    sql.Append(advance).Append(" ,");
    sql.Append("'").Append(notes).Append("' ,");
    sql.Append(string.Format("{0:MM/dd/yyyy})",pubdate));

    SqlCommand c = new SqlCommand(sql.ToString(), MyConnection);

    c.ExecuteNonQuery();
}

In the example, the SQL statement is constructed from a simple string concatenation. The assumption is that some fields of the form are alphanumeric, and thus must be surrounded by single quotes. Since SQL sees the single quotes as a string delimiter, all a hacker needs to do is insert an extra quote, followed by any SQL code into the textBoxNotes. For example, say the value:

', null); delete from titles --

was entered. The InsertTitle() method would build the SQL statement to look something like this:

insert into titles ( title_id , title , type , pub_id , price , advance ,
[ ytd_sales ] , [ notes ] , [ pubdate ] )
values ( 'FS1234' , 'dummy' , 'business' , 1 , 1 , 1 , 1 , '' , null ) ; delete from titles - - , 22 /04/2000 )

This would cause SQL to execute the INSERT statement, possibly returning an error because the pubdate field was NULL (if the database were so programmed). The server would then execute the DELETE statement, deleting all records from the table. The harm is done!

The Solution
To prevent SQL injection, you can use the parameters collection when building SQL strings. No matter what a malicious user includes as input, the input is treated as a literal.

private void InsertTitle(string id,stringtitle, stringtype,
    intpubId,double price,doubleadvance, intytd_sales,
    string notes, DateTime pubdate){

    StringBuilder sql =new StringBuilder("insert into titles ");
    sql.Append("(title_id, title, type, pub_id, price, advance, ");
    sql.Append("ytd_sales, notes, pubdate)");
    sql.Append(" values (@par0, @par1, @par2, @par3, @par4, @par5, ");
    sql.Append("@par6, @par7, @par8)");

    SqlCommand c = new SqlCommand(sql.ToString(), MyConnection);
    c.Parameters.Add(new SqlParameter("par0", id));
    c.Parameters.Add(new SqlParameter("par1", title));
    c.Parameters.Add(new SqlParameter("par2", type));
    c.Parameters.Add(new SqlParameter("par3", pubId));
    c.Parameters.Add(new SqlParameter("par4", price));
    c.Parameters.Add(new SqlParameter("par5", advance));
    c.Parameters.Add(new SqlParameter("par6", ytd_sales));
    c.Parameters.Add(new SqlParameter("par7", notes));
    c.Parameters.Add(new SqlParameter("par8", pubdate));

    c.ExecuteNonQuery();
}

To mark a name as a parameter and not as a string of literal characters, you place a prefix in front of the parameter name (and, as an option, a suffix after it). For example, parameter marker characters might be "@" (SqlServer), ":" (Oracle), or "%". Typically, you use the parameter marker character recognized for your database. Use can used unnamed parameters when your database doesn't support named parameters. An unnamed parameter is a question mark (?) that you put anywhere in the query that you want to prompt for or substitute a literal value.

InsertStatement classes

To make parameter usage easier you should use Objects and not SQL strings to build your SQL statements. The InsertStatement classes are used to build an IDbCommand. They create the CommandText and the Parameters collection. I have currently four implementations for SqlServer, MS Access, MySql and Oracle. It enabled me to support also RDBMS specific syntax for: keywords, parameters, table-names & column-names, (outer)joins and aliasses.

private void InsertTitle(string id,stringtitle, stringtype,
    intpubId,double price,doubleadvance, intytd_sales,
    string notes, DateTime pubdate){

    SqlInsertStatement i =new SqlInsertStatement();

    i.TableName ="titles";
    i.Values.Add("title_id",new Constant(id));
    i.Values.Add("title",new Constant(title));
    i.Values.Add("type",new Constant(type));
    i.Values.Add("pub_id",new Constant(pubId));
    i.Values.Add("price",new Constant(price));
    i.Values.Add("advance",new Constant(advance));
    i.Values.Add("ytd_sales",new Constant(ytd_sales));
    i.Values.Add("notes",new Constant(notes));
    i.Values.Add("pubdate",new Constant(pubdate));

    SqlCommand c = (SqlCommand)i.CreateCommand();
    c.Connection = MyConnection;

    c.ExecuteNonQuery();
}

The SqlInsertStatement will create the following CommandText for a System.Data.SqlClient.SqlCommand:

insert into [ titles ] ( [ title_id ] , [ title ] , [ type ] , [ pub_id ] , [ price ] , [ advance ] ,
[ ytd_sales ] , [ notes ] , [ pubdate ] )
values ( @par0 , @par1 , @par2 , @par3 , @par4 , @par5 , @par6 , @par7 , @par8 )

The AccesInsertStatement would create the following CommandText for a System.Data.OleDb.OleDbCommand:

insert into [ titles ] ( [ title_id ] , [ title ] , [ type ] , [ pub_id ] , [ price ] , [ advance ] ,
[ ytd_sales ] , [ notes ] , [ pubdate ] )
values ( ? , ? , ? , ? , ? , ? , ? , ? , ? )

The MySqlInsertStatement would create the following CommandText for a Microsoft.Data.Odbc.OdbcCommand:

insert into `titles` (`title_id`, `title`, `type`, `pub_id`, `price`, `advance`,
`ytd_sales`, `notes`, `pubdate`)
values(?,?,?, ?,?,?,?, ?,?)

The OracleInsertStatement will create the following CommandText for a System.Data.OracleClient.OracleCommand:

INSERT INTO titles(title_id, title,type,pub_id,price, advance,
ytd_sales,notes, pubdate)
VALUES (:par0, :par1,:par2,:par3,:par4,:par5,:par6,:par7,:par8)

The InsertStatement and ValuesClause use arrays of tokens to store all information. The CreateCommand() method uses a CommandBuilder to create the CommandText and Parameters. These CommandBuilders will iterate through the arrays and translate each token (keyword, literal, constant, expression, etc) to the RDBMS specific implementation.

Select, Update and Delete classes

You can use the same solution for the Select, Update and Delete statements. They are a lot more difficult but it is really worth the effort, especially when you want to create generic, RDBMS independent components. I ended up with the following model:

The SelectStatementBase class has for each clause an association with a Clause object.

Example with an outerjoin and a like in the where clause:

SelectStatementBase s =new SqlSelectStatement();
s.Select.AddAstrix();

s.From.Add("sales","s");
s.From.Add(new Join("stores", "s.stor_id","stores.stor_id", JoinTypes.Left));

s.Where.And(new ConditionLike("title", new Constant("%book%")));

s.OrderBy.Add("ord_date",true);
s.OrderBy.Add("qty");

Update and Delete model:


Additional Information

Conclusion

Writing SQL statements using objects instead of strings made my life easier. I hope you will use my classes which use the Parameters so SQL injection is prevented.

The download contains the C# source code of the Insert classes and a sample project.

Any suggestions and feedback for improving this article is most welcome. Send your suggestions and feedback to Fons.Sonnemans@reflectionit.nl

Reliably and quickly improve your C# code with Xtreme Simplicity's C# Refactory

0 Comments
By Fons Sonnemans, 4-2-2003

I have found a promising C# tool which fully integrates with Visual Studio.NET. I'm going to test it soon (I hope).

Have a look yourself at http://www.xtreme-simplicity.net

12>

All postings/content on this blog are provided "AS IS" with no warranties, and confer no rights. All entries in this blog are my opinion and don't necessarily reflect the opinion of my employer or sponsors. The content on this site is licensed under a Creative Commons Attribution By license.