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

            //

            InitializeComponent();

        }



        /// Nettoyage des ressources utilisées.

        protected override void Dispose( bool disposing )

        {

            if( disposing )

            {

                if (components != null)

                {

                    components.Dispose();

                }

            }

            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();

            this.SuspendLayout();

            // 

            // 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.Controls.Add(this.listBox1);

            this.Controls.Add(this.rt1);

            this.Controls.Add(this.button1);

            this.Name = "Form1";

            this.Text = "Form1";

            this.Load += new System.EventHandler(this.Form1_Load);

            this.ResumeLayout(false);



        }

        #endregion



        /// Point d'entrée principal de l'application.

        [STAThread]

        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.

            Samples.Types.Add(Class1);



            //a variable declaration

            CodeVariableDeclarationStatement Var = new

CodeVariableDeclarationStatement("System.String","str1");

            // 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.

                

newCodeTypeReferenceExpression("System.Console"), "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));



            Start.Statements.Add(Var);

            // 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;



            try

            {

                generator.GenerateCodeFromCompileUnit(CompileUnit,Sw,genOptions);

            }

            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)

            listBox1.DataSource=cr.Output;



            //where is it? 

            MessageBox.Show ( cr.PathToAssembly );





          }

    }

}

  ¶ 9:56 AM 0 comments

  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")
Next
Next

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!

  ¶ 3:40 AM 0 comments

  My Wiki contributions These days, I created two wiki pages :
http://c2.com/cgi/wiki?MarcBoizeau
and
http://c2.com/cgi/wiki?OracleVsMicrosoft

have A look!
  ¶ 9:47 AM 0 comments

  WIKIWIKI 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 :
http://c2.com/cgi-bin/wiki?CsharpFeatures
http://c2.com/cgi-bin/wiki?OracleDatabase

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

  ¶ 10:16 AM 1 comments

  Connect a .Net application to an Oracle Database 1 Hello,
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,
password)

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:
MY_DATABASE_NAME.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MY_SERVER_NAME)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = My_SERVICE_NAME)
)
)
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

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

//dataset
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
myConnection.Close();
}
}
}


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.












  ¶ 6:18 AM 1 comments