.comment-link {margin-left:.6em;}
Marc Boizeau's blog
Tuesday, December 28, 2004
  Code generation with .net
Hello, today I would like to introduce code generation with c# and .NET. (It may be usefull when dealing with repetitive Database access code).

To start with this feature, here is a complete sample on how to build a ".exe" file, using natives code generation and compilation tools.

The following code will produce, after compilation with csc.exe, a winform application.
This application has one purpose: produce another application, a console one.
Two framework's objects are important here: System.CodeDom.Compiler.ICodeGenerator and System.CodeDom.Compiler.ICodeCompiler.
The first is a common interface to generate .net code. The second one is used to compile the code. Here I use the cSharp implementation of these interfaces to produce a basic console application.
using System;

using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.CodeDom;
using System.CodeDom.Compiler;
using Microsoft.CSharp;
using System.Collections.Specialized;
namespace WindowsApplication2
/// Main form

public class Form1 : System.Windows.Forms.Form
private System.Windows.Forms.Button button1;
private System.Windows.Forms.RichTextBox rt1;
private System.Windows.Forms.ListBox listBox1;
private System.ComponentModel.Container components = null;

public Form1()
// Requis pour la prise en charge du Concepteur Windows Forms

/// Nettoyage des ressources utilisées.
protected override void Dispose( bool disposing )
if( disposing )
if (components != null)
base.Dispose( disposing );

#region Code généré par le Concepteur Windows Form
/// <summary>
/// Méthode requise pour la prise en charge du concepteur - ne modifiez pas
/// le contenu de cette méthode avec l'éditeur de code.
/// </summary>
private void InitializeComponent()
this.button1 = new System.Windows.Forms.Button();
this.rt1 = new System.Windows.Forms.RichTextBox();
this.listBox1 = new System.Windows.Forms.ListBox();
// button1
this.button1.Location = new System.Drawing.Point(432, 8);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(232, 104);
this.button1.TabIndex = 0;
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
// rt1
this.rt1.Location = new System.Drawing.Point(24, 16);
this.rt1.Name = "rt1";
this.rt1.Size = new System.Drawing.Size(384, 192);
this.rt1.TabIndex = 1;
this.rt1.Text = "richTextBox1";
// listBox1
this.listBox1.Location = new System.Drawing.Point(24, 224);
this.listBox1.Name = "listBox1";
this.listBox1.Size = new System.Drawing.Size(896, 251);
this.listBox1.TabIndex = 3;
// Form1
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(936, 526);
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);


/// Point d'entrée principal de l'application.
static void Main()
Application.Run(new Form1());

private void Form1_Load(object sender, System.EventArgs e)
//nothing special here

private void button1_Click(object sender, System.EventArgs e)
{// here is starting the main part of the code:
//the CodeCompileUnit is the abstract code structure:

// Create a new CodeCompileUnit to contain the program graph
CodeCompileUnit CompileUnit = new CodeCompileUnit();
// Declare a new namespace called Samples.
CodeNamespace Samples = new CodeNamespace("Samples");
// Add the new namespace to the compile unit.
CompileUnit.Namespaces.Add( Samples );

// Add the new namespace import for the System namespace.
Samples.Imports.Add( new CodeNamespaceImport("System") );

// Declare a new type called Class1.
CodeTypeDeclaration Class1 = new CodeTypeDeclaration("echo");
// Add the new type to the namespace's type collection.

//a variable declaration
CodeVariableDeclarationStatement Var = new
// Declare a new code entry point method
CodeEntryPointMethod Start = new CodeEntryPointMethod();
// Create a new method invocation expression.
CodeMethodInvokeExpression cs1 =
new CodeMethodInvokeExpression(
// Call the System.Console.WriteLine method.

), "ReadLine");
CodeAssignStatement as1 = new CodeAssignStatement(
new CodeVariableReferenceExpression("str1"),cs1);

// Create a new method invocation expression.
CodeMethodInvokeExpression cs2 =
new CodeMethodInvokeExpression(
// Call the System.Console.WriteLine method.
new CodeTypeReferenceExpression("System.Console"), "WriteLine");
cs2.Parameters.Add(new CodeVariableReferenceExpression(Var.Name));

// Add the new method code statement.
Start.Statements.Add( as1);
// Add the new method code statement.
Start.Statements.Add(new CodeExpressionStatement(cs2));

// Add the code entry point method to the type's members collection
Class1.Members.Add( Start );

System.IO.StringWriter Sw = new System.IO.StringWriter();

Microsoft.CSharp.CSharpCodeProvider provider =
new CSharpCodeProvider();
System.CodeDom.Compiler.ICodeGenerator generator = provider.CreateGenerator(Sw);
CodeGeneratorOptions genOptions = new CodeGeneratorOptions();

// The code generator should insert blank lines
genOptions.BlankLinesBetweenMembers = true;

catch (Exception Exc)
System.Windows.Forms.MessageBox.Show (Exc.Message);
rt1.Text = Sw.ToString();

// Compilation
//instanciate csharp compiler
System.CodeDom.Compiler.ICodeCompiler MyCompiler = provider.CreateCompiler();
System.CodeDom.Compiler.CompilerParameters cp = new CompilerParameters();
cp.GenerateExecutable = true;
cp.CompilerOptions= " /target:exe";

//where your exe will be saved
cp.OutputAssembly = "c:\\echo.exe";
// Invoke compilation.
CompilerResults cr = MyCompiler.CompileAssemblyFromSource ( cp,rt1.Text);
// Return the results of compilation.

//eventually load compilation output to the listbox (usefull to debug)

//where is it?
MessageBox.Show ( cr.PathToAssembly );


Friday, December 17, 2004
  Change all querytable in an Excel File
A quick way to change, for instance, the name of a table in each query of a file:

Dim q As QueryTableDim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

For Each q In ws.QueryTables
q.CommandText = Replace(q.CommandText,"MaChaine", "MaNouvelleChaine")

Of course dont forget to deeply test the result!

Thanks to "ADE" on microsoft.public.fr.officexp who ask the question.

That's all folks!

Thursday, December 16, 2004
  My Wiki contributions
These days, I created two wiki pages :

have A look!
Tuesday, December 07, 2004
I've found one of the most strange web site I ever sow.
WIKIWIKI which means "quick quick" in Hawai.
Everybody can post in one second about everything. Every pages are editable by everybody.

There is contributions about c# and Oracle technolgies :

try it! just click on EditText to edit yourself the OracleDatabase dedicated page.

  Connect a .Net application to an Oracle Database 1
today I would like to talk about something that, I think, is very important when you want to build an Oracle and Microsoft based Application.
I want to talk about the ways you can use to connect a .Net application to an Oracle Database. It is a very wide subject with many aspects. I will dedicate many post to this.

Today, lets concentrate on architectural aspects.

Okay you've got an Oracle Database and a Windows .Net Platform. How to go from here to there?

1 - First of all, Oracle is a relational Database implementing the SQL standard, somewhere in your system you need to build
SQL queries to acces your data(and your stored procedures calls).

2 - Oracle is a secure application. You need some login informations to connect to the database.(database name, login,

3 - Oracle is also a network based application. You need an Oracle Client to access the database.

4 - Oracle and Microsoft are two different corporations. You need a specific - so called - middleware to make those two
technologies talking together.

5 - .Net provide an Object Oriented API. So, you need to manipulate some objects of this API to connect the application to
the Oracle Client and retrieve the data.

Ok lets see that more deeply:

1 - SQL. Ok nothing special about this. If You ever work with a relational database you know what I'm talking about.
If you never, you can survive to this article by knowing that :
In this language three keywords are important "select", "from" and "where" to get your column of data named "A" in a table
named B, just ask: "Select A from B" if you just want the value which are greater than 1 ask "select A from B where A>1".
Just remember that you can retrieves data, but also change it.

2 - Those of you Who ever work with Oracle, Know how to connect to a database. Fo those of you Who never work with this
system lets say that an Oracle server can run many Databases. Each database has its own name. Each database has its own
groups of users. The database's administrator can gives the right to read or to write on a table to a specific user. So when
you connect to a database you provide a"user name" and a "password" and the system authorize you or not to acces ressources
regarding your rights. All applications need a database name, a login and a password.

3 - The client computer needs what Oracle calls "Oracle connectivity" it means the basics software which allow the computer to act as client for Oracle. You may have to install these softwares yourself using an Oracle CD. You may also have to configure the tnsnames.ora file. It is a configuration file you find on every machine connected to Oracle. Common path is :

<oracle installation directory>/network/admin/
TNS is standing for "Transparent Network Substrate" which is a part of Oracle network protocol. To connect to your data base, this file must contains information about the server on which you database is.
A databse entry of your tnsnames.ora is like the following:
Ask your favorite database administrator for the MY_SERVER_NAME, and MY_SERVICE_NAME.
Don't ask me any question about the ".world" I never understand why it is sometimes unnecessary and sometimes mandatory (ask this question to your DBA to)

4 - Middleware. Ok now hard things begin. I call "middleware" any software which allow very differrent software to communicate each others. Whith microsoft You have different way to plug your application on a database.
Here i 'm going to speak about two common methods. ODBC and OLE DB.
ODBC is standing for "Open DataBase Connectivity" (forget the word "Open" lol).
It is a microsoft standard and coms with most of windows operating systems. It is a kind of repository where all connection to databases are stored. An ODBC entry is named a Data Source Name(DSN). To configure a DSN, you choose an ODBC driver dedicate to your Database System. And then give the database, login and password info.
For instance to connect to "My_database" on windows XP:

1 Open the configuration panel
2 Choose "administration tools"
3 Then click on ODBC data sources
4 Choose the "System Data source" (you need administrator rights)
5 click on "ADD.."
6 Choose a database odbc drivers, here you may choose "Microsoft ODBC for Oracle" or something like "Oracle in OraHome92"
7 A driver specific window pops up, put the DSN name(which you will use to acces it later), your database name, login and password. Often a "test" button allows you to check that you 've put the right arguments.
8 validate

That's it. you have configured a DSN for an ODBC connection.

Good news, For OLE DB you don't need any configuration like this. in fact in that case, this layer is nearly transparent.

5 - The objects. The .Net framework provide the System.Data API. The principles are
- You build a "connection" object using a "connection string" (which will contain information related to the database the ODBC or OLEDB layer, and so on)
- Then you build a "DATAadaptater" object which is dedicated to encapsulate the SQL strings.
- Then you build a "dataset" object which will allow you to access the data and maybe put it into graphical object.

for instance, if you connect via oleb your C# code will be close to this one :

using System;
using System.Data;
using System.Data.OleDb;
using System.Xml;

namespace oracle
public class oracleData
static string strConnect;//Connection String
static OleDbConnection myConnection; //connnection object
static OleDbDataAdapter myAdapter;// see it later
static OleDbCommand mySelect; // select command
static DataSet myDS;
static void Main(string[] args)
//The connection string :
strConnect ="Provider=\"OraOLEDB.Oracle.1\";User ID=MY_LOGIN;PASSWORD=My_Password;Data Source=My_database;Extended Properties=;Persist Security Info=False";

//the connection itself
myConnection = new OleDbConnection();
myConnection.ConnectionString= strConnect; //affect the connection string
myConnection.Open();//open the connection

mySelect =new OleDbCommand("select * from dual ",myConnection);
myAdapter = new OleDbDataAdapter(mySelect);

myDS = new DataSet("MyDataSetName"); //instanciate the data set
myAdapter.Fill(myDS,"MyTable"); //load the data into the dataset

//An easy way to retrieve the content of a dataset : XML export
myDS.WriteXml("c:\\data.xml",XmlWriteMode.WriteSchema);// double "\" because "\" is an escape character

Assuming the oledb provider "OraOLEDB.Oracle.1"is installed, you can use this in a .cs file and compile it with csc.exe (locate in
c:/window/microsoft .net/framework/v1.1.4322/ on my station)

Take close attention to the connection string strConnect and replace the name of the database/login/password by yours.

Ok thats all folks! (and that is enought for today)
Comments are welcome!

A colleague of mine says to me about this blog :"there's a lot of code"( lol). Next time I will try talk in a more "human" way.

You are a developer and work with Oracle and Microsoft technologies? Have a look!
How to:
Use updatable views in Access
Get data in Excel from Oracle 1
Get data in Excel from Oracle 2
Draw the Mandelbrot set using C#
Use the "Grouping Sets" SQl Syntax
Use the "Rollup" SQl Syntax
Use the "Rank over" SQl Syntax

Go to knol
When does the next bus pass?
two lines in the datagrid header
Context saving with persistent datasets
.net webservice session
Winform, Web Services & credential
back to work
just a link
October 2004 / November 2004 / December 2004 / January 2005 / February 2005 / March 2005 / April 2005 / June 2005 / July 2005 / August 2005 / September 2005 / December 2005 / February 2006 / December 2006 / March 2009 /

Powered by Blogger