Marc Boizeau's blog

Go to knol

2009-03-13T08:58:00.000-07:00

This blog is now quite inactive.
Here:http://knol.google.com/k/marc-boizeau/marc-boizeau/g6fqt9cl9ver/0#knols
you will find my new articles both in french and english. ( about Business intelligence for instance and DB design )

Ce blog est maintenant inactif, vous trouverez ici :
http://knol.google.com/k/marc-boizeau/marc-boizeau/g6fqt9cl9ver/0#knols
mes nouveaux articles en francais et en anglais ( Conception de base de données mais aussi algorithmes de compression)

When does the next bus pass?

2006-12-12T05:38:00.000-08:00

I work in a remote bussiness center downtown Paris. Last weeks I spend nearly 3 hour a days comuting.
Every evening, I have to wait for a bus and one time out of two it rain cats and dog.
So every evening I check the bus company's web site in order to known at what time the next bus pass to my station.
Late in the evening at Office I have no time to loose! So I decide to speed up this repetitive task:

A few times ago(lol) I wrote a C# code to perform a web crawling. I used the same technology to code the following extractor. As usual this has been made with VS studio but, use csc.exe (.Net 1.1 framework) to compile it as well. (see previous posts for exact command line)

using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Net; using System.IO; using System.Text; using System.Text.RegularExpressions; namespace bus { /// /// Summary description for Form1. /// public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.Button button1; private System.Windows.Forms.RichTextBox richTextBox1; private System.Windows.Forms.RichTextBox richTextBox2; /// /// Required designer variable. /// private System.ComponentModel.Container components = null; /// /// Use a regular expression to extract informations from a string /// all matching results are stored in a string array. /// /// /// /// private static string [] extractReg(string strHTML,Regex r) { Match m; String[] results = new String[101]; // Use the Matches method to find all matches in the input string. // Loop through the match collection to retrieve all // matches. int i=0; m = r.Match(strHTML); for (i=0; (m.Success &&amp; i<100); m =" m.NextMatch())"> /// Clean up any resources being used. /// protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows Form Designer generated code /// /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// private void InitializeComponent() { this.button1 = new System.Windows.Forms.Button(); this.richTextBox1 = new System.Windows.Forms.RichTextBox(); this.richTextBox2 = new System.Windows.Forms.RichTextBox(); this.SuspendLayout(); // // button1 // this.button1.Location = new System.Drawing.Point(0, 0); this.button1.Name = "button1"; this.button1.Size = new System.Drawing.Size(344, 40); this.button1.TabIndex = 0; this.button1.Text = "check it "; this.button1.Click += new System.EventHandler(this.button1_Click); // // richTextBox1 // this.richTextBox1.Location = new System.Drawing.Point(0, 48); this.richTextBox1.Name = "-"; this.richTextBox1.Size = new System.Drawing.Size(160, 96); this.richTextBox1.TabIndex = 1; this.richTextBox1.Text = "-"; // // richTextBox2 // this.richTextBox2.Location = new System.Drawing.Point(168, 48); this.richTextBox2.Name = "-"; this.richTextBox2.Size = new System.Drawing.Size(176, 96); this.richTextBox2.TabIndex = 1; this.richTextBox2.Text = "-"; // // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(352, 150); this.Controls.Add(this.richTextBox1); this.Controls.Add(this.button1); this.Controls.Add(this.richTextBox2); this.Name = "Form1"; this.Text = "Form1"; this.ResumeLayout(false); } #endregion /// /// The main entry point for the application. /// [STAThread] static void Main() { Application.Run(new Form1()); } /// /// This is the main method here we call the web pages and extract method /// /// /// private void button1_Click(object sender, System.EventArgs e) { System.Net.HttpWebRequest r ; string strRes; int count =0; string [] nbmin ; //I found this regular expression which work well for RATP's real time bus position web site // You will have to change it regarding the information you want to check. Regex re = new Regex(">(?'1'[0-9]+ mn).*<", RegexOptions.IgnoreCaseRegexOptions.Compiled); // URL with rich querystrings RATP users will pay attention to "ligne" , "dir", standig for direction , "arr" standing for "arret" (los "libdir" and "lib arr") string URL1 ="http://www.ratp.info/horaires/index.php?reseau=BUS&etape=prec_bustram_ssotr&ligne=179&libligne=GARE+DE+ROBINSON+%2F+PONT+DE+SEVRES&dem=dem3&libarr=LA+BOURSIDIERE&libdir=GARE+DE+ROBINSON&act=act_valid&amp;arr=179_5206_5436&dir=A"; string URL2 = "http://www.ratp.info/horaires/index.php?reseau=BUS&etape=prec_bustram_ssotr&ligne=179&libligne=GARE+DE+ROBINSON+%2F+PONT+DE+SEVRES&dem=dem3&libarr=LA+BOURSIDIERE&libdir=PONT+DE+SEVRES&act=act_valid&amp;arr=179_5206_5436&dir=R"; r =(HttpWebRequest)WebRequest.Create(URL1); r.Proxy.Credentials = System.Net.CredentialCache.DefaultCredentials ; System.Net.HttpWebResponse rp = (HttpWebResponse)r.GetResponse(); // Gets the stream associated with the response. Stream receiveStream = rp.GetResponseStream(); Encoding encode = System.Text.Encoding.GetEncoding("utf-8"); // Pipes the stream to a higher level stream reader with the required encoding format. StreamReader readStream = new StreamReader( receiveStream, encode ); Char[] read = new Char[256]; // Reads 256 characters at a time. count = readStream.Read( read, 0, 256 ); strRes =""; while (count > 0) { // Dumps the 256 characters on a string . String str = new String(read,0, count); strRes +=str; count = readStream.Read(read, 0, 256); } // Releases the resources of the response. rp.Close(); // Releases the resources of the Stream. readStream.Close(); //write the result of the extraction this.richTextBox1.Text = "Bus 179 en direction de Robinson" + String.Join("\n", extractReg(strRes,re)); strRes =""; r.Proxy.Credentials = System.Net.CredentialCache.DefaultCredentials ; r =(HttpWebRequest)WebRequest.Create(URL2); r.Proxy.Credentials = System.Net.CredentialCache.DefaultCredentials ; rp = (HttpWebResponse)r.GetResponse(); count =0; // Gets the stream associated with the response. receiveStream = rp.GetResponseStream(); encode = System.Text.Encoding.GetEncoding("utf-8"); // Pipes the stream to a higher level stream reader with the required encoding format. readStream = new StreamReader( receiveStream, encode ); read = new Char[256]; // Reads 256 characters at a time. count = readStream.Read( read, 0, 256 ); while (count > 0) { // Dumps the 256 characters on a string . String str = new String(read,0, count); strRes +=str; count = readStream.Read(read, 0, 256); } // Releases the resources of the response. rp.Close(); // Releases the resources of the Stream. readStream.Close(); re = new Regex(">(?'1'[0-9]+ mn).*<", RegexOptions.IgnoreCaseRegexOptions.Compiled); //write the result this.richTextBox2.Text = "Bus 179 en direction de pont de Sèvres " + String.Join("\n", extractReg(strRes,re)); } } }

Thanks

2006-02-15T06:58:00.000-08:00

Hi everybody.

I didn’t post for a long time on this blog, sorry about this. Today I'm going to take the time to do something:

A quick ”Hello” to Stéphane, Eric, Eric (yes), Sophiane, Lucky (yes again), Bulent, Thierry, Christophe, Laurent, Fabienne, Sophianne, Michel and Nathalie. I worked with these guys and girls mainly on Oracle and Microsoft technologies for about three years. We made great things together.

Thanks for everything.

googled2cd966929769ab9

2005-12-16T05:23:00.000-08:00

This is what google asked me to put on the sitemap feature

two lines in the datagrid header

2005-12-02T08:04:00.000-08:00

Ok, this is a dirty workaround but it works.

In a winform datagrid you can't directly change the columns header height. is is the workaroun Eric, a collegue of mine find somewhere on internet : change the datagrid header font size without changing the column header font.

for instance
System.Drawing.Font f =new System.Drawing.Font("Verdana", 15.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point,Convert.ToByte(0));

MyDatagrid.HeaderFont = f;

Context saving with persistent datasets

2005-09-21T10:30:00.000-07:00

This article propose a way to use dataset to make persistent an arbitrary set of data.in the context of a winform application

Sometimes you need to stored datas but you don't think a database is the right way.
For instance if you want to store the user's context (search criterias,options, the last time he use the application...) in order to set it back the next time the application is used, you need a specific tool.
To achieve this you may use a little xml file.
But, most of the time, you don't know exactly what you will need to store in advance.
So defining and changing and changing again the schema of the xml file could be something close to a good headache.

An idea is to propose a set of methods which adapt the schema inline.

The microsoft .Net DataSet object has two powerfull methods ReadXml and WriteXml.
These methods manipulates the datas and also the schema of the dataset.
So I choose this object to implement my context saving class for winform application.
(It could be easily adapt for a server side use)

The idea is that when a coder need a new persistent data, he use the following syntax:

Contexte.getSimpleval("MyDataName");
The schema will be changed at the first use and the next time, it will bring back the last setled value.

When modified, the data is stored like this:
Contexte.setSimpleval("MyNewinformationName", MyStringValue);// the stored info are here of string type

If the amount of information is important data could be sort by collections using the syntaxes:

getVal(string "MyNewCollectionName","MyNewinformationName")
and
setVal(string "MyNewCollectionName","MyNewinformationName", MyStringValue)

The "save()" and "load()" methods are called at the begining and at the end of the application.
(typically the onLoad and onClose events of the main window).

Here is the code of the class, please critisize, comment and ask questions!

using System;
using System.Data;
using System.IO;

namespace MyNamespace
{
/// <summary>
/// The Contexte class is used to make datas persistant
/// Marc Boizeau
/// Two types of data are managed
/// - collections: values identified by a collection name and a field name
/// - Simple values just identified by a name
/// </summary>
public class Contexte
{
private const string FILE_NAME="Context.xml";
// context data container dataset
static private DataSet ds;

public Contexte()
{
}

/// <summary>
/// Retrives the good path for context
/// </summary>
/// <returns> the context path, string </returns>
static private string getFilePath()
{
//to change
string path= Environment.GetEnvironmentVariable("APPDATA")+"/MyApplication/";

if (!Directory.Exists(path))
Directory.CreateDirectory(path);

path+=FILE_NAME;

return path ;

}
//load the dataset with the xml file
public static void load()
{
if (ds!=null)
ds = null;

ds = new DataSet("ContexteExpertises");

try
{
if (File.Exists(getFilePath()))
ds.ReadXml( getFilePath(),XmlReadMode.ReadSchema);
}
catch(Exception e)
{// files do
}

}

//save the datafile
public static void Save()
{
if(ds== null)
{
ds =new DataSet("ContexteExpertises");
}
ds.WriteXml(getFilePath(),XmlWriteMode.WriteSchema);
}

// retrieve a datatable by its name
private static DataTable getTable(string TableName)
{
DataTable dt;
if (!ds.Tables.Contains(TableName))
{
dt = new DataTable(TableName);
dt.Columns.Add("NAME");
dt.Columns.Add("VALUE");
dt.Constraints.Add("PK",dt.Columns["NAME"],true);
ds.Tables.Add(dt);
}
else
dt = ds.Tables[TableName];
return dt;
}

//retrieves a data by a table name and a field name
public static string getVal(string TableName, string Entry)
{
DataTable dt;
DataView dv;
dt = getTable(TableName);
dv = dt.DefaultView;

if (Entry==null)
Entry="";

dv.RowFilter= " NAME ='" + Entry+ "'";
//test if exists
if (dv.Count==0)
{//if not creates
DataRow dr =dt.NewRow();
dr["NAME"]= Entry;
dr["VALUE"]="";
dt.Rows.Add(dr);
}
//at this point the entry exist
return dt.Rows.Find(Entry)["VALUE"].ToString();
}

// setting a data
public static void setVal(string TableName, string Entry,string Val)
{
DataTable dt;
DataView dv;
dt = getTable(TableName);
dv = dt.DefaultView;
dv.RowFilter= " NAME ='" + Entry+ "'";

if (Entry==null)
Entry="";

if (Val==null)
Val="";

//test if exists
if (dv.Count==0)
{//if not creates
DataRow dr =dt.NewRow();
dr["NAME"]= Entry;
dr["VALUE"]= "";
dt.Rows.Add(dr);
}
//at this point the entry exists
dt.Rows.Find(Entry)["VALUE"]=Val;
}

/// <summary>
/// retrieves a simple value
/// </summary>
/// <param name="Entry"></param>
/// <returns></returns>
public static string getSimpleVal(string Entry)
{
if (ds==null)
load();
return getVal("SIMPLE", Entry);
}

/// <summary>
/// set a simple value
/// </summary>
/// <param name="Entry"></param>
/// <returns></returns>
public static void setSimpleVal(string Entry,string val)
{
if (ds==null)
load();
setVal("SIMPLE", Entry,val);
}

}
}

.net webservice session

2005-08-30T09:59:00.000-07:00

Maybe it is obvious but I've seen the question on a forum;
Q:"How to make the session collection work for a .net webservice like for asp.net? "

A:"Just put the following before the web method:"
[ WebMethod(Description="Desc",EnableSession=true)]

thats all folks!

hope this helps.

comments are welcome

Ok I've posted this a few time ago. And it still works. But, the session collection is not a real session objectin this case. It is instanciate just before the web method call, but I never seen when it is uninstanciate. For instance the "end_session" event never fire!
Also, I experiment troubles while debuging an object stored in this collection for a web method.
So, use this the least you can and do not expect this to be a good equivalent to the session collection in classical ASP .Net.

Winform, Web Services & credential

2005-08-29T03:55:00.000-07:00

Just a tip today.

Last week I built a web service based application which needs a windows authentication.
The schema was:
A winform client
A group of IIS web services.
An oracle database (as alway on this blog lol)

How could the winform client say to the web services. "Hey i'm a member of this windows network, my login is Jdoe, trust me"

Ok, first I asked my favourite web administrator to configure my IIS application to not allow anonymous users. Exactly like for a classical ASPX application.

Then I changed my web.config file, puting the line :
<authentication mode="Windows" />

Ok IIS was then fine to authenticate users.
but my Winform webservice client wasn't ready yet.

In order to be "windowsly" authenticate a webservice client need to send credentials to the server.
The .net framework provide a secure and elegant way to to that:

using System;
using System.Net;
....

MyService.MyWebMethod ws = new MyService.MyWebMethod();
ws.Credentials = CredentialCache.DefaultCredentials;
...

and it works !
I was then able to retrieve informations about my connected users. For instance
the login :

...
strlogin = user.identity.name
...

that's all folks!
hope this helps!
Comments are welcome!

back to work

2005-08-19T06:32:00.000-07:00

Hello everybody

I'm back to work! I didn't post these last three weeks because I was
here and also there ,I also went to this place .

just a link

2005-07-08T08:24:00.000-07:00

Blog Directory

Search for Blogs

self description

2005-06-24T09:42:00.000-07:00

Somebody ask for the code of the self descripting c# code i've done a few times ago here it is :

using System;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
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 button2;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Button bt_itself;
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.listBox1 = new System.Windows.Forms.ListBox();
this.rt1 = new System.Windows.Forms.RichTextBox();
this.bt_itself = new System.Windows.Forms.Button();
this.button1 = new System.Windows.Forms.Button();
this.button2 = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// listBox1
//
this.listBox1.Location = new System.Drawing.Point(24, 360);
this.listBox1.Name = "listBox1";
this.listBox1.Size = new System.Drawing.Size(1016, 108);
this.listBox1.TabIndex = 3;
//
// rt1
//
this.rt1.Location = new System.Drawing.Point(24, 16);
this.rt1.Name = "rt1";
this.rt1.Size = new System.Drawing.Size(632, 328);
this.rt1.TabIndex = 1;
this.rt1.Text = "richTextBox1";
//
// bt_itself
//
this.bt_itself.Location = new System.Drawing.Point(696, 40);
this.bt_itself.Name = "bt_itself";
this.bt_itself.Size = new System.Drawing.Size(72, 24);
this.bt_itself.TabIndex = 0;
this.bt_itself.Text = "it self";
this.bt_itself.Click += new System.EventHandler(this.bt_itself_Click);
//
// button1
//
this.button1.Location = new System.Drawing.Point(696, 8);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(72, 24);
this.button1.TabIndex = 0;
this.button1.Text = "genere";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// button2
//
this.button2.Location = new System.Drawing.Point(696, 72);
this.button2.Name = "button2";
this.button2.Size = new System.Drawing.Size(72, 24);
this.button2.TabIndex = 4;
this.button2.Text = "compile";
this.button2.Click += new System.EventHandler(this.button2_Click);

//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(1032, 526);
this.Controls.Add(this.button2);
this.Controls.Add(this.listBox1);
this.Controls.Add(this.rt1);
this.Controls.Add(this.button1);
this.Controls.Add(this.bt_itself);
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());
}
// methode de base
string join(string[] arr,string sep)
{string strReturn="" ;
foreach (string elt in arr)
strReturn += elt+sep;
strReturn.Substring(0,strReturn.Length-sep.Length);

return strReturn;
}

void seekandreplace(string[] arr,string strSrc,string strReplace)
{
int i=0;
for(i=0;i<=arr.Length-1;i++)
{ if(arr[i]==strSrc)
arr[i]=strReplace;
}
}
string format (string str)
{
str = str.Replace("\\","\\\\");
str = str.Replace("\"","\\\"");
return str;
}
string joinformat(string[] arr,string sep)
{
string strReturn= "" ;
foreach (string elt in arr)
{ strReturn +=format( elt)+sep;}

strReturn = strReturn.Substring( 0,strReturn.Length-sep.Length);

return strReturn;
}
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.

new CodeTypeReferenceExpression("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();

}

private void button2_Click(object sender, System.EventArgs e)
{
Microsoft.CSharp.CSharpCodeProvider provider =
new CSharpCodeProvider();
///
// 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";
cp.ReferencedAssemblies.AddRange( new string[]{"System.Windows.Forms.dll","System.dll","System.drawing.dll",""});
//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 );
}

private void bt_itself_Click(object sender, System.EventArgs e)
{
string[] arr=new string[]{
"using System;",
"using System.Collections;",
"using System.ComponentModel;",
"using System.Windows.Forms;",
"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 button2;",
" private System.Windows.Forms.Button button1;",
" private System.Windows.Forms.Button bt_itself;",
" 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.listBox1 = new System.Windows.Forms.ListBox();",
" this.rt1 = new System.Windows.Forms.RichTextBox();",
" this.bt_itself = new System.Windows.Forms.Button();",
" this.button1 = new System.Windows.Forms.Button();",
" this.button2 = new System.Windows.Forms.Button();",
" this.SuspendLayout();",
" // ",
" // listBox1",
" // ",
" this.listBox1.Location = new System.Drawing.Point(24, 360);",
" this.listBox1.Name = \"listBox1\";",
" this.listBox1.Size = new System.Drawing.Size(1016, 108);",
" this.listBox1.TabIndex = 3;",
" // ",
" // rt1",
" // ",
" this.rt1.Location = new System.Drawing.Point(24, 16);",
" this.rt1.Name = \"rt1\";",
" this.rt1.Size = new System.Drawing.Size(632, 328);",
" this.rt1.TabIndex = 1;",
" this.rt1.Text = \"richTextBox1\";",
" // ",
" // bt_itself",
" // ",
" this.bt_itself.Location = new System.Drawing.Point(696, 40);",
" this.bt_itself.Name = \"bt_itself\";",
" this.bt_itself.Size = new System.Drawing.Size(72, 24);",
" this.bt_itself.TabIndex = 0;",
" this.bt_itself.Text = \"it self\";",
" this.bt_itself.Click += new System.EventHandler(this.bt_itself_Click);",
" // ",
" // button1",
" // ",
" this.button1.Location = new System.Drawing.Point(696, 8);",
" this.button1.Name = \"button1\";",
" this.button1.Size = new System.Drawing.Size(72, 24);",
" this.button1.TabIndex = 0;",
" this.button1.Text = \"genere\";",
" this.button1.Click += new System.EventHandler(this.button1_Click);",
" // ",
" // button2",
" // ",
" this.button2.Location = new System.Drawing.Point(696, 72);",
" this.button2.Name = \"button2\";",
" this.button2.Size = new System.Drawing.Size(72, 24);",
" this.button2.TabIndex = 4;",
" this.button2.Text = \"compile\";",
" this.button2.Click += new System.EventHandler(this.button2_Click);",
" ",
" // ",
" // Form1",
" // ",
" this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);",
" this.ClientSize = new System.Drawing.Size(1032, 526);",
" this.Controls.Add(this.button2);",
" this.Controls.Add(this.listBox1);",
" this.Controls.Add(this.rt1);",
" this.Controls.Add(this.button1);",
" this.Controls.Add(this.bt_itself);",
" 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());",
" }",
"// methode de base ",
" string join(string[] arr,string sep)",
" {string strReturn=\"\" ;",
" foreach (string elt in arr)",
" strReturn += elt+sep;",
" strReturn.Substring(0,strReturn.Length-sep.Length);",
" ",
" return strReturn;",
" }",
"",
" void seekandreplace(string[] arr,string strSrc,string strReplace)",
" {",
" int i=0;",
" for(i=0;i<=arr.Length-1;i++)",
" { if(arr[i]==strSrc)",
" arr[i]=strReplace;",
" }",
" }",
" string format (string str)",
" {",
" str = str.Replace(\"\\\\\",\"\\\\\\\\\");",
" str = str.Replace(\"\\\"\",\"\\\\\\\"\");",
" return str; ",
" }",
" string joinformat(string[] arr,string sep)",
" {",
" string strReturn= \"\" ;",
" foreach (string elt in arr)",
" { strReturn +=format( elt)+sep;}",
" ",
" strReturn = strReturn.Substring( 0,strReturn.Length-sep.Length);",
" ",
" return strReturn;",
" }",
" 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.",
" ",
" new CodeTypeReferenceExpression(\"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();",
"",
" }",
" ",
" private void button2_Click(object sender, System.EventArgs e)",
" {",
" Microsoft.CSharp.CSharpCodeProvider provider =",
" new CSharpCodeProvider();",
" ///",
"// 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\";",
" cp.ReferencedAssemblies.AddRange( new string[]{\"System.Windows.Forms.dll\",\"System.dll\",\"System.drawing.dll\",\"\"});",
" //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 );",
" }",
"",
" private void bt_itself_Click(object sender, System.EventArgs e)",
" {",
" string[] arr=new string[]{",
"Les sanglots longs des violons de l'automne bercent mon coeur d'une langueur monotone",
" };",
" seekandreplace(arr,\"Les sanglots longs des violons de l'automne bercent mon coeur d'une langueur monotone\",\"\\\"\"+joinformat(arr,\"\\\",\\n\\\"\")+\"\\\"\");",
" this.rt1.Text = join(arr,\"\\n\");",
" }",
" }",
"}"
};
seekandreplace(arr,"Les sanglots longs des violons de l'automne bercent mon coeur d'une langueur monotone","\""+joinformat(arr,"\",\n\"")+"\"");
this.rt1.Text = join(arr,"\n");
}
}
}

A web crawler(regular expressions and httprequest)

2005-06-17T05:21:00.000-07:00

Hello everybody, i'm back to the blog!

Today I'd like to demonstrate two powerfull features of the .net Framework: the httpWebrequest/httWebResponse and the RegEx object. I will then propose an implementation of a mini Web Crawler.

httpWebrequest and httWebResponse are both object of the System.Net namespace.
With this objects, retrieving a text stream from a URI is as easy as this:

// Creates an HttpWebRequest with the specified URL.
HttpWebRequest myHttpWebRequest;

myHttpWebRequest = (HttpWebRequest)WebRequest.Create(strURL);

// Sends the HttpWebRequest and waits for the response.
HttpWebResponse myHttpWebResponse = (HttpWebResponse)myHttpWebRequest.GetResponse();
// Gets the stream associated with the response.
Stream receiveStream = myHttpWebResponse.GetResponseStream();
Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
// Pipes the stream to a higher level stream reader with the required encoding format.
StreamReader readStream = new StreamReader( receiveStream, encode );
Char[] read = new Char[256];
// Reads 256 characters at a time.
int count = readStream.Read( read, 0, 256 );

while (count > 0)
{
// Dumps the 256 characters on a string .
String str = new String(read,0, count);
strRes +=str;
count = readStream.Read(read, 0, 256);
}
// Releases the resources of the response.
myHttpWebResponse.Close();
// Releases the resources of the Stream.
readStream.Close();

The regEx object is the .net implementation for regular expressions. If you don't know what it is just remember it's the best way to perform text analysis.
For instance here is a function which retrieves in an array the "href"s in a web page.

private string [] extractURIS(string strHTML)
{
Match m;

String[] results = new String[101];

// Create a new Regex object and define the regular expression.
Regex r = new Regex("href\\s*=\\s*(?:\"(?'1'[^\"]*)\"|(?'1'\\S+)).*>",
RegexOptions.IgnoreCase|RegexOptions.Compiled);

// Use the Matches method to find all matches in the input string.
// Loop through the match collection to retrieve all
// matches.
int i=0;
m = r.Match(strHTML);

for (i=0; (m.Success && i<100); m = m.NextMatch())
{i++;

// Add the match string to the string array.
results[i] = m.Groups[1].ToString() ;

}
return results;
}

OK, have a great breath and look at this
"href\\s*=\\s*(?:\"(?'1'[^\"]*)\"|(?'1'\\S+)).*>"
let's decrypt this regular expression .
"href" just mean that the patern is preceded by the word "href"
\\s stands for "any space character"
\\s* stands for "a 0 or n space character"
so the first part of the regEx :"href\\s*=\\s*" mean that the pattern we are looking for begins by "href" then spaces, then the "=" character and then spaces.

[^\"] means "everything but a double quote character" (the \ is here to say " isnt a special character of the RegEx)
so [^\"]* is a succession of 0 or n character whithout any double quote

(?'1'......) means that whe give the name '1' to the .... expression
Here we use it two times in the subexpression:
\"(?'1'[^\"]*)\"|(?'1'\\S+)
knowing that "|" is a logicall OR, you can guess that '1' is either
a succession of "without double quote" between two double quotes or "a succession of at least one non spaces characters"(\\S+)

(?:....) just propagate the subexpression to the top level .

a dot (.) means "any character"
so:
.*> means that our regEx ends with a succession of any character an then a ">"

wow...
for further info have a look to : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconregularexpressionslanguageelements.asp

So lets use it in our little web crawler.

To compile the following , save it with a ".cs" extension and use the csc.exe of your framework installation, for instance use the following:
C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\csc.exe /target:winexe /recurse:*.*
int the cs directory.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Net;
using System.IO;
using System.Web;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections.Specialized;

namespace webrequest
{
/// <summary>
/// Description résumée de Form1.
/// </summary>
public class WebCrawler : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btLoad;
private System.Windows.Forms.TextBox tbURI;
private System.Windows.Forms.RichTextBox rtbHTML;
private System.Windows.Forms.RichTextBox rtbLOg;

private string MainString;
private StringDictionary dicURL;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.TextBox tbiProf;
private System.Windows.Forms.TextBox tbMax;
private System.Windows.Forms.Label label3;
/// <summary>
/// Variable nécessaire au concepteur.
/// </summary>
private System.ComponentModel.Container components = null;

public WebCrawler()
{
dicURL = new StringDictionary();
//
// Requis pour la prise en charge du Concepteur Windows Forms
//
InitializeComponent();

//
// TODO : ajoutez le code du constructeur après l'appel à InitializeComponent
//
}

/// <summary>
/// Nettoyage des ressources utilisées.
/// </summary>
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.btLoad = new System.Windows.Forms.Button();
this.tbURI = new System.Windows.Forms.TextBox();
this.rtbHTML = new System.Windows.Forms.RichTextBox();
this.rtbLOg = new System.Windows.Forms.RichTextBox();
this.tbiProf = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.tbMax = new System.Windows.Forms.TextBox();
this.label3 = new System.Windows.Forms.Label();
this.SuspendLayout();
//
// btLoad
//
this.btLoad.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.btLoad.Location = new System.Drawing.Point(16, 112);
this.btLoad.Name = "btLoad";
this.btLoad.Size = new System.Drawing.Size(272, 120);
this.btLoad.TabIndex = 0;
this.btLoad.Text = "Crawl!";
this.btLoad.Click += new System.EventHandler(this.btLoad_Click);
//
// tbURI
//
this.tbURI.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.tbURI.Location = new System.Drawing.Point(120, 16);
this.tbURI.Name = "tbURI";
this.tbURI.Size = new System.Drawing.Size(712, 20);
this.tbURI.TabIndex = 1;
this.tbURI.Text = "http://oraclevsmicrosoft.blogspot.com";
//
// rtbHTML
//
this.rtbHTML.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.rtbHTML.Location = new System.Drawing.Point(8, 248);
this.rtbHTML.Name = "rtbHTML";
this.rtbHTML.Size = new System.Drawing.Size(832, 136);
this.rtbHTML.TabIndex = 2;
this.rtbHTML.Text = "";
//
// rtbLOg
//
this.rtbLOg.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Right)));
this.rtbLOg.Location = new System.Drawing.Point(297, 40);
this.rtbLOg.Name = "rtbLOg";
this.rtbLOg.Size = new System.Drawing.Size(536, 200);
this.rtbLOg.TabIndex = 3;
this.rtbLOg.Text = "";
this.rtbLOg.LinkClicked += new System.Windows.Forms.LinkClickedEventHandler(this.rtbLOg_LinkClicked);
//
// tbiProf
//
this.tbiProf.Location = new System.Drawing.Point(120, 48);
this.tbiProf.Name = "tbiProf";
this.tbiProf.Size = new System.Drawing.Size(48, 20);
this.tbiProf.TabIndex = 4;
this.tbiProf.Text = "5";
//
// label1
//
this.label1.Location = new System.Drawing.Point(8, 16);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(88, 16);
this.label1.TabIndex = 5;
this.label1.Text = "Starting URI";
//
// label2
//
this.label2.Location = new System.Drawing.Point(8, 48);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(88, 16);
this.label2.TabIndex = 6;
this.label2.Text = "Max depht";
//
// tbMax
//
this.tbMax.Location = new System.Drawing.Point(120, 80);
this.tbMax.Name = "tbMax";
this.tbMax.Size = new System.Drawing.Size(104, 20);
this.tbMax.TabIndex = 4;
this.tbMax.Text = "1000";
//
// label3
//
this.label3.Location = new System.Drawing.Point(8, 88);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(112, 16);
this.label3.TabIndex = 6;
this.label3.Text = "Max number of links";
//
// WebCrawler
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(848, 398);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.tbiProf);
this.Controls.Add(this.rtbLOg);
this.Controls.Add(this.rtbHTML);
this.Controls.Add(this.tbURI);
this.Controls.Add(this.btLoad);
this.Controls.Add(this.tbMax);
this.Controls.Add(this.label3);
this.MinimumSize = new System.Drawing.Size(856, 432);
this.Name = "WebCrawler";
this.Text = "Form1";
this.ResumeLayout(false);

}
#endregion

/// <summary>
/// Point d'entrée principal de l'application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new WebCrawler());
}

// ne garde que le body du document et remplace toutes les balises par un espace simple
// enfin decode le html pour remplacer les énbsp; et autres
private string deleteTagg(string strHTML)
{
Match m;
string strRes ="";
Regex r = new Regex ("<body.*>(.|\\n)*</body>",
RegexOptions.IgnoreCase);
m = r.Match(strHTML);
if (m.Success )
{
strRes = m.Value;

}
r = null;
r = new Regex ("<(.|\\n)*?>",
RegexOptions.IgnoreCase);
strRes = r.Replace(strRes," ");

strRes =HttpUtility.HtmlDecode(strRes);

return strRes ;
}
private string [] extractURIS(string strHTML)
{
Match m;

String[] results = new String[101];

// Create a new Regex object and define the regular expression.
Regex r = new Regex ("href\\s*=\\s*(?:\"(?'1'[^\"]*)\"|(?'1'\\S+)).*>",
RegexOptions.IgnoreCase|RegexOptions.Compiled);

// Use the Matches method to find all matches in the input string.
// Loop through the match collection to retrieve all
// matches.
int i=0;
m = r.Match(strHTML);

for (i=0; (m.Success && i<100); m = m.NextMatch())
{i++;

// Add the match string to the string array.
results[i] = m.Groups[1].ToString() ;

}
return results;
}
private string loadUrl(string strURL)
{
string strRes="" ;
try
{
// Creates an HttpWebRequest with the specified URL.
HttpWebRequest myHttpWebRequest;

myHttpWebRequest = (HttpWebRequest)WebRequest.Create(strURL);

// Sends the HttpWebRequest and waits for the response.
HttpWebResponse myHttpWebResponse = (HttpWebResponse)myHttpWebRequest.GetResponse();
// Gets the stream associated with the response.
Stream receiveStream = myHttpWebResponse.GetResponseStream();
Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
// Pipes the stream to a higher level stream reader with the required encoding format.
StreamReader readStream = new StreamReader( receiveStream, encode );
Char[] read = new Char[256];
// Reads 256 characters at a time.
int count = readStream.Read( read, 0, 256 );

while (count > 0)
{
// Dumps the 256 characters on a string .
String str = new String(read,0, count);
strRes +=str;
count = readStream.Read(read, 0, 256);
}
// Releases the resources of the response.
myHttpWebResponse.Close();
// Releases the resources of the Stream.
readStream.Close();

}

catch(Exception exp)
{
rtbLOg.AppendText("error at URI : "+ strURL +"\n");
strRes ="";
}
return strRes;
}

private void btLoad_Click(object sender, System.EventArgs e)
{
recure(tbURI.Text,Convert.ToInt16(tbiProf.Text));
rtbLOg.AppendText(dicURL.Count.ToString ());
}
private void recure(string strURL,int ilevel)
{
MainString="";
Match m;

if ((ilevel)>0 )
{

MainString = loadUrl(strURL);

if (dicURL.Count<Convert.ToInt32(tbMax.Text))
{
// récuperation des URLS
string []strArUris= extractURIS(MainString);
for (int i=0; i<strArUris.Length&&dicURL.Count<Convert.ToInt32(tbMax.Text);i++)
{
if (strArUris[i]!=null)
{
// check that the URi begins with a http
Regex r = new Regex ("http://.+",
RegexOptions.IgnoreCase);
m = r.Match(strArUris[i]);

if (!dicURL.ContainsKey(strArUris[i])&& m.Success )
{
dicURL.Add(strArUris[i],"1");
recure(strArUris[i],ilevel-1);
rtbLOg.AppendText(strArUris[i]+" " +ilevel+"\n");

}
else
dicURL[strArUris[i]]=Convert.ToString(Convert.ToInt32(dicURL[strArUris[i]])+1);

}
}
}
//here could be a text flow extraction
// rtbHTML.Text =deleteTagg(rtbHTML.Text);
//rtbHTML.Text =deleteTagg("</body>");
}

}

private void rtbLOg_LinkClicked(object sender, System.Windows.Forms.LinkClickedEventArgs e)
{
//Launch in ie
System.Diagnostics.Process.Start(e.LinkText);
rtbHTML.Text= deleteTagg(loadUrl(e.LinkText));
}

}
}

Thats all folks! Hope this helps, comments are welcome.

link

2005-04-28T00:55:00.000-07:00

just a link today:
Database Forums

call an Oracle stored proc with oledb .net

2005-04-19T06:23:00.000-07:00

Thanks to Emilio who ask the question on microsoft.public.dotnet.framework.adonet newsgroup.

here is a syntaxe I've just test :


this.cmdInsertLib.CommandText = "MyStoredProc";
this.cmdInsertLib.CommandType = System.Data.CommandType.StoredProcedure;
this.cmdInsertLib.Parameters.Add(
  new System.Data.OleDb.OleDbParameter("P1",
  System.Data.OleDb.OleDbType.VarWChar, 100));
this.cmdInsertLib.Parameters.Add(
  new System.Data.OleDb.OleDbParameter("P2", 
  System.Data.OleDb.OleDbType.VarWChar, 100));
cmdInsertLib.Parameters[0].Value = textBox1.Text.ToString();
cmdInsertLib.Parameters[1].Value = textBox2.Text.ToString();
cmdInsertLib.ExecuteNonQuery();

that's all folks

hope this helps

comments are welcome

Updating two lines with one data adapter.

2005-04-07T10:10:00.000-07:00

Datatables and dataAdapter are designed to manipulate one database table at a time. The idea is that dataTable is like an asynchronous part of the database. So, when you make a change on one row of the dataTable you are suppose to change one and only one row in the database's table when synchronizing. Of course, sometimes it is not what you would like to do.

Today I would like to demonstrate how to update multiple rows in an Oracle table without any Stored Procedure but with oracle specific SQL.

A few days ago, I worked on a little multi-language tool. In a financial application, some labels where needed to be translate from French to English.

Therefore, I decided to make a little tool for the users in order to administrate their label’s translations.

There were three tables:

REF_LABEL:

IDT_LABEL PK

COM_LABEL unique

....

REF_LANGUAGE :

IDT_LNG PK

COM_LNG

REF_TRANSLATION:

IDT_LABEL FK PK

IDT_LNG PK

COM_TRANS

The requirement was to "provide
a grid with COM_LABEL, the French translation and the English one for each
label. The French and English translation available for modification"

First, I defined the main query:

string strSelect= " select COM_LABEL, FR.COM_TRANS, EN.COM_TRANS from
REF_TRANSLATION EN, REF_TRANSLATION FR, REF_LABEL L"
+

" where L.IDT_LABEL = EN.IDT_LABEL " +

" and L.IDT_LABEL = FR.IDT_LABEL" +

" and EN.IDT_LNG = 2 -- the English id " +

" and FR.IDT_LNG = 1 -- the French one " ;"

Then, I set the preceding as the CommandText property of an oledbCommand:

System.Data.OleDb.OleDbCommand cmdS = new System.Data.OleDb.OleDbCommand();

cmdS.CommandText =
strSelect ;

cmdS.Connection = MyValidConnection;

Next step was the update SQL command,
pay attention to the decode function:

System.Data.OleDb.OleDbCommand cmdU = new System.Data.OleDb.OleDbCommand();

cmdU.CommandText = "update ref_translation set com_trans = decode
(idt_lng,1, ?,2,?) where idt_label = ?"

cmdU.Connection =
MyValidConnection;

"?" are parameters, that I
defined by:

cmdU.Parameters.Add(new System.Data.OleDb.OleDbParameter("COM_TRANS_FR",
System.Data.OleDb.OleDbType.Variant, 80, "LBL_VAL_FR"));

cmdU.Parameters.Add(new System.Data.OleDb.OleDbParameter("COM_TRANS_EN",
System.Data.OleDb.OleDbType.Variant, 80, "LBL_VAL_EN"));

cmdU.Parameters.Add(new System.Data.OleDb.OleDbParameter("IDT_LABEL",
System.Data.OleDb.OleDbType.Decimal, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(4)), ((System.Byte)(0)), "IDT_LABEL",
System.Data.DataRowVersion.Original, null));

The "decode" function is Oracle specific. If you do not know this function, let us says that it is something like a C/C++/C# switch clause or a VB select case, but as a function. It takes
at least three arguments and return the third argument if the first is equal to the second, the fifth if the first is equal to the fourth etc... For further
info : http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/functions33a.htm#1017439

Then, I defined my dataAdapter by adding the following:

System.Data.OleDb.OleDbDataAdapter daLib = new System.Data.OleDb.OleDbDataAdapter();

daLib.SelectCommand = cmdS;

daLib.UpdateCommand = cmdU;

Hoops! Two pages, that is too long for one post.

That’s all folks!

Comments are welcome!

oracle functions

2005-04-01T06:38:00.000-08:00

Today just a little Oracle trick: the all_arguments view.

On your favourite Oracle Client try this :

select distinct package_name from all_arguments

this will list every packages including yours on the current schema.

then try this one:

select distinct object_name
from all_arguments where package_name like 'STANDARD'

this will list every standard functions installed. I've found it useful when try to remember the exact syntax of "NUMTODSINTERVAL " or "NUMTOYMINTERVAL".

that's all folks

hope this helps

comments are welcome

link

2005-03-25T06:31:00.000-08:00

I just register this blog to this site :www.popdex.com

Quotes & parameters

2005-03-17T05:38:00.000-08:00

Saw this question on a newsgroup(microsoft.public.data.oledb ) :

"How can legally include " ' " (single quote) in my SQL statement?"

This is a common question and many times it could be a security problem because of SQL injection. Single quote is the SQL string delimiter so you may have to avoid it from you sql code or double it in order to have a valid sql statement.

There is a more elegant way to solve this problem: the use of parameters.

lets say that cnx is a valid connection the syntax in c# for .net will be :

OleDbDataAdapter daRes = new OleDbDataAdapter("SELECT IDT_EMP,EMP_NAME FROM EMP where IDT_EMP = ?",cnx);
daRes.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("IDT_EMP", System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(4)), ((System.Byte)(0)), "IDT_EMP", System.Data.DataRowVersion.Current, null));

notice :If you have many parameters all of them will be represent by a "?". You will have to follow the order of the ?s in the SQLstatement when you add your parameters to the oledbcommand parameters collection .

that's all folks

hope this helps !

comments are welcome.

Oracle sequences and ADO .Net.

2005-02-22T08:58:00.000-08:00

A few days ago, I twisted my brain on a tricky problem. The point was « the tables in the database have triggers witch inserts a sequence value when inserting a row. The client application uses a dataset and datatables objects with a master-detail relation which requires the primary key to be stored in the datatable objects »

That is not a brand new problem. The sequence mechanism is database oriented. That is a good idea. When there are numerous users, it is better to use a centralized id generator. But, an advanced cache oriented data access API like ADO .NET needs to manage this unique identifier without synchronizes the entire dataset.

So, last week, I desperately try to build a master/detail dataset model that does not need to know the primary keys of new entries. That was not an elegant solution. Neither it is to use the ADO .net autoId system (as it is client side oriented).

I have founded the following solution.

Let us have a table named EMP with the following fields:
ID_EMP Number(6)
EMP_NAME VARCHAR2(100)

..And the following sequence:

CREATE SEQUENCE PSI_ADMIN.S_EMP
START WITH 1
INCREMENT BY 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

A typical way to retrieve and manipulate values in .Net is to build dataAdapter object with the insert, delete, update and select Command.

For instance:

OleDbDataAdapter da = new OleDbDataAdapter() ;
da.SelectCommand.CommandText= "SELECT ID_EMP,EMP_NAME FROM EMP"
da.UpdateCommand.CommandText= "UPDATE EMP SET EMP_NAME= ? WHERE ID_EMP= ?" ;
da.UpdateCommand.Parameters.Add("EMP_NAME",OleDbType.VarChar,100,"EMP_NAME");
da.UpdateCommand.Parameters.Add("ID_EMP" , OleDbType.Numeric,6,"ID_EMP");
da.InsertCommand.CommandText= "INSERT INTO EMP(ID_EMP,EMP_NAME) VALUES(?,?)";
da.InsertCommand.Parameters.Add("ID_EMP" , OleDbType.Numeric,6,"ID_EMP");
da.InsertCommand.Parameters.Add("EMP_NAME",OleDbType.VarChar,100,"EMP_NAME");
da.DeleteCommand.CommandText= "DELETE EMP WHERE ID_EMP= ?" ;
da.DeleteCommand.Parameters.Add("ID_EMP" , OleDbType.Numeric,6,"ID_EMP");

da.SelectCommand.Connection = cnx ;
da.UpdateCommand.Connection = cnx ;
da.InsertCommand.Connection = cnx ;
da.DeleteCommand.Connection = cnx ;

….where “cnx” is a valid OLEDB connection to the Oracle database.
The "da" object may be use for instance to fill a dataset that can be itself manipulated by a datagrid:
…
da.Fill(ds,"EMP");
…

When synchronizing:
…
da.Update(ds,"EMP");
…
…where "ds" is a valid dataset.

This code will works fine as long as you can provide a valid ID_EMP.
If you want to use the Oracle sequence, and that is my point, you need something more.
You need to handle the “update” event and retrieve the sequence’s next Val from Oracle.

Here is how I implement this:
First, handle the event in the dataAdapter’s construction:
…
da.RowUpdating +=new OleDbRowUpdatingEventHandler(da_RowUpdating);
..
In addition, somewhere else in the class, put the following method:
private void da_RowUpdating(object sender, OleDbRowUpdatingEventArgs e)
{
// Is it an insertion ?
if (e.StatementType == StatementType.Insert)
da.InsertCommand.Parameters["ID_EMP"].Value= (new OleDbCommand("select S_EMP.nextval from Dual",cnx).ExecuteScalar());

}

When the dataAdapter’s Update method is called, this event fire for each new, deleted or updated row, just before the SQL action is performed.
If the action’s type is “insert” the sequence‘s next Val replace the current id_emp parameter.

That’s all folks!

Hope this helps.

Comments are welcome.

Back to work

2005-02-10T03:17:00.000-08:00

Hello everybody!
Waow! I didn't post for about 20 days! Sorry about this. I wasn't able to post because I was here and also there, I've also been to this place and this one.
Oups! I nearly forget this island and this one.

Sorry, that's hard to post code sample today.

Code generation and self description

2005-01-23T09:04:00.000-08:00

A few weeks ago I proposed here an example of C# code generation.
It remind me an old exercise I used to do when discovering a new language.
"The shortest program which produce its own source code on the standard output."
Of course, programs which read explicitly their source code to reproduce it are not accepted.
The idea of the exercise is to find self reference possibilities offers by the language.
I've put the executable here and the source code is hereJust download and then launch the echo.exe then click the "itself" button , the complete source code is displayed in the upper richtext box. Then click the "compile" button , the source code is compiled and saved in c:\echo.exe (be careful not to replace a program of your owns).
Just launch this new program and compare with the first one.... If you want, you can try to change the code to see effects (debug information shown in the second box).

Briefly the principles:
I used a double coding representation. The C# code contains in one point a string array declaration with all the lines of the code between "" and coma separated. All the line of the code ? Not exactly, the array declaration is replaced into the array itself by an arbitrary string (I choose a French poet's famous verses). When you click the "it self" button this exact string is replace by a construction of the array declaration. So at this time the array contains all the code including its own declaration. Then a "foreach" instruction join all the lines into one big string which is the exact source code.
When you push the "compile" button the text box content is compiled .

There is two key factors in this very solution (I'm sure there is at least another solution ).
- The declaration syntax of an array in C# which authorize long static arrays.
- The compilation feature of the .Net framework.

The main part of the code is writed two times. Maybe there is no true connection with this but I can't help comparing this to the double-helix DNA in living cells.

The comparison with living cells ends here. This code isn't (AFAIK) a kind of virus or worm and can't be used for malicious purpose. (For instance, runtime compiling features can't be used during an in-line context).

Ok, now, two questions :

- Is it possible to code that kind of program in PL-SQL? (;-))
Maybe next time !
- The array based solution is interesting but it doesn't imply the Icodegenerator interface. Is there a solution using a more sophisticated structure than an array?
For instance is it possible that a program build its own abstract description usng this interface?

That's all folks !
Remember there is 10 sorts of people : those who understand native binary code and the others !

Comments, answers and suggestions are welcome!

links

2005-01-18T01:40:00.000-08:00

A few links today:

C# tutorial and offshore development - dotnetspider offers online training, C# tutorials, offshore development, software outsourcing, link search and sample C# projects with source code.

http://blog.spsclerics.com A Colleague of mine. This blog is mainly about Microsoft Sharepoint portal Server. But you will also find things about C#, .net technology (and the authour's hollidays;-)
that's all folks

Oracle data dictionary

2005-01-13T08:42:00.000-08:00

Hello everybody (Happy coding and merry source code for 2005! )

Today I would like to talk about Oracle data Dictionary.
Without any administration tool it's possible to retrieve information about tables,views and constraints.
To understand this article you need an Oracle client connected to a valid schema.
(SQL+ for instance)

First I suggest you to try this syntax:
select * from DICT
This query returns all the views dealing with this schema's dictionary, very useful to retrieve the exact name of a view. The dictionary contains a big amount of views, let's focus on a few one:

Imagine you want to list all the tables you can acces in a schema, you may then try the following:
SELECT * FROM ALL_TABLES

If you're not a DBA but a developer you may just be interested in your own tables. So try this one:
SELECT * FROM ALL_TABLES
WHERE OWNER ='My_name'

The same thing about views:
SELECT * FROM ALL_VIEWS
WHERE OWNER ='My_name'

Now imagine you need to know all the constraints of a table :
SELECT * FROM ALL_CONSTRAINTS
WHERE
table_name= 'My_table'

That is useful, but you may want something more fine tunned:
What are the foreign keys?:
SELECT * FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE= 'R'
and table_name= 'My_table'

What is the primary key? :
SELECT * FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE= 'P'
and table_name= 'My_table'

That's interesting but most of the time you need the name of a field. So, the following retrieves the list of all the fields included in the primary key:
SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS CC, all_constraints C
where CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
and C.TABLE_NAME= 'My_Table'
and C.CONSTRAINT_TYPE='P'

That's all folks !
Hope this helps, comments are welcome!

Code generation with .net

2004-12-28T09:56:00.000-08:00

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





          }

    }

}

Change all querytable in an Excel File

2004-12-17T03:40:00.000-08:00

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!

My Wiki contributions

2004-12-16T09:47:00.000-08:00

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

have A look!

WIKIWIKI

2004-12-07T10:16:00.000-08:00

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.

Connect a .Net application to an Oracle Database 1

2004-12-07T06:18:00.001-08:00

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.

Mandelbrot in C# , the sources!

2004-11-26T03:42:00.000-08:00

Hello everybody,
yesterday I posted a link to mandelbrot in c# on the microsoft.public.dotnet.framework.drawing usenet group . Morten Wennevik reply to me and argued that a lot of code were missing. So, today I post the complete sources. He also said that Visual studio isn't mandatory to build a .Net Windows application. He is right! So I will also give the way to compile it without Visual Studio .net, just with the framework:

Step 1 Create a directory for the project
Step 2 In this directory put a text file named fractalform.cs (personnaly I used a UTF-8 format with Notepad) containing the following:

using System;
using System.Drawing;
using System.Windows.Forms;

namespace mandelbrot
{

/// <summary>
/// Description résumée de Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
Complex C1,C2,C3;
private System.Windows.Forms.Button trace;
private System.Windows.Forms.PictureBox BoiteImage;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.TextBox hgx;
private System.Windows.Forms.TextBox hgy;
private System.Windows.Forms.TextBox bdy;
private System.Windows.Forms.TextBox bdx;
private System.Windows.Forms.Label label2;
/// <summary>
/// Variable nécessaire au concepteur.
/// </summary>
private System.ComponentModel.Container components = null;

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

//
// TODO : ajoutez le code du constructeur après l'appel à InitializeComponent
//
}

/// <summary>
/// Nettoyage des ressources utilisées.
/// </summary>
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.trace = new System.Windows.Forms.Button();
this.BoiteImage = new System.Windows.Forms.PictureBox();
this.hgx = new System.Windows.Forms.TextBox();
this.hgy = new System.Windows.Forms.TextBox();
this.bdy = new System.Windows.Forms.TextBox();
this.bdx = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.SuspendLayout();
//
// trace
//
this.trace.Location = new System.Drawing.Point(512, 8);
this.trace.Name = "trace";
this.trace.Size = new System.Drawing.Size(80, 32);
this.trace.TabIndex = 12;
this.trace.Text = "trace";
this.trace.Click += new System.EventHandler(this.button1_Click);
//
// BoiteImage
//
this.BoiteImage.Location = new System.Drawing.Point(24, 56);
this.BoiteImage.Name = "BoiteImage";
this.BoiteImage.Size = new System.Drawing.Size(584, 416);
this.BoiteImage.TabIndex = 13;
this.BoiteImage.TabStop = false;
//
// hgx
//
this.hgx.Location = new System.Drawing.Point(32, 24);
this.hgx.Name = "hgx";
this.hgx.Size = new System.Drawing.Size(80, 20);
this.hgx.TabIndex = 14;
this.hgx.Text = "-1,5";
//
// hgy
//
this.hgy.Location = new System.Drawing.Point(128, 24);
this.hgy.Name = "hgy";
this.hgy.Size = new System.Drawing.Size(80, 20);
this.hgy.TabIndex = 15;
this.hgy.Text = "-1,5";
//
// bdy
//
this.bdy.Location = new System.Drawing.Point(304, 24);
this.bdy.Name = "bdy";
this.bdy.Size = new System.Drawing.Size(80, 20);
this.bdy.TabIndex = 16;
this.bdy.Text = "1,5";
//
// bdx
//
this.bdx.Location = new System.Drawing.Point(216, 24);
this.bdx.Name = "bdx";
this.bdx.Size = new System.Drawing.Size(80, 20);
this.bdx.TabIndex = 17;
this.bdx.Text = "1,5";
//
// label1
//
this.label1.Location = new System.Drawing.Point(88, 0);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(64, 23);
this.label1.TabIndex = 19;
this.label1.Text = "hautgauche";
//
// label2
//
this.label2.Location = new System.Drawing.Point(272, 0);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(64, 16);
this.label2.TabIndex = 20;
this.label2.Text = "BasDroite";
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(632, 470);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.bdx);
this.Controls.Add(this.bdy);
this.Controls.Add(this.hgy);
this.Controls.Add(this.hgx);
this.Controls.Add(this.BoiteImage);
this.Controls.Add(this.trace);
this.Name = "Form1";
this.Text = "Form1";
this.ResumeLayout(false);

}
#endregion

/// <summary>
/// Point d'entrée principal de l'application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}

private void button1_Click(object sender, System.EventArgs e)
{
int nbPix=1000;
double diagDest;

int x,y;
Bitmap Imag;
Color col;
int iDiv;
Complex c= new Complex();

//define the complex plan zone
Complex cHG = new Complex(Convert.ToDouble( this.hgx.Text),Convert.ToDouble( this.hgy.Text));
Complex cBD = new Complex(Convert.ToDouble(this.bdx.Text),Convert.ToDouble(this.bdy.Text));
diagDest=Convert.ToDouble( this.hgx.Text)-Convert.ToDouble( this.bdx.Text);

//instantiate the bitmap
Imag = new Bitmap(nbPix ,nbPix);

//scan the bitmap
for(x=0;x<Imag.Size.Width;x++)
for(y=0;y<Imag.Size.Height;y++)
{
//convert the pixel coordinate into a complex number
c = new Complex(diagDest*x/nbPix,diagDest*y/nbPix)-cHG;

//calculate divergence
iDiv = c.diverg();

//choose the color of the pixel
if (iDiv >= 255)
//if it converge it's black
col = System.Drawing.Color.FromArgb(255,0,0,0);
else //if it is not it depend of the speed
col = System.Drawing.Color.FromArgb(128,iDiv,iDiv,iDiv);

//set the pixel color
Imag.SetPixel(x,y,col );
}
//draw the image
BoiteImage.SizeMode = PictureBoxSizeMode.StretchImage ;
BoiteImage.ClientSize = new Size(Imag.Size.Width, Imag.Size.Height);
BoiteImage.Image = (Image) Imag ;

}

}
}

Step 3Put a second file named cplx.cs
containing the following

using System;// to include the basic libraries

namespace mandelbrot//a namespace groups classes working together,
{
// The main class
class Complex
{
//constants
const int IMAX_ITER = 255 ;// number of iterations to estimate the divergence
const double MODUL_LIMIT = 2;//

public double x;//pretty close to c++ syntax,
//"public" indicates that the users of the class can access this property...
public double y;

//////////////////////////
//constructors

//the basic one , do nothing but is mandatory for operators overloading
public Complex()
{}

//the main constructor
public Complex(double i, double j)
{
x = i;
y = j;
}

//////////////////////////
//Operators overloading

//plus
public static Complex operator +(Complex c1,Complex c2)
{
Complex temp = new Complex();
temp.x = (c1.x)+(c2.x);
temp.y = c1.y+c2.y;
return temp;
}
//minus
public static Complex operator -(Complex c1,Complex c2)
{
Complex temp = new Complex();
temp.x = (c1.x)-(c2.x);
temp.y = c1.y-c2.y;
return temp;
}
// multiplication, a little more complicated
public static Complex operator *(Complex c1,Complex c2)
{
Complex temp = new Complex();
temp.x = c1.x*c2.x-c1.y*c2.y;
temp.y = c1.y*c2.x+c1.x*c2.y;
return temp;
}

/////////////////////
//methods

//Module, used to measure the complex number
public double Module()
{
double temp;
temp = System.Math.Sqrt( x*x+y*y);
return temp ;
}
//use to measure the divergence of the complex number
//the number return is use to define the color of the affix's point
public int diverg()
{
int i=0 ;
Complex c = new Complex(x,y) ;

while (i<IMAX_ITER && c.Module()<MODUL_LIMIT)
{
c = c*c+this;//yep "*" and "+" are the operator we've just overload.
i++;
}
return i;//the number of iteration is return to set the color
}
}

}

Step 4 Now open a command line window on the directory you've just create.
type the folowing:
C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\csc.exe /target:winexe /recurse:*.*
(You may have to replace C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\ by your computer's c# compiler path.)

That's all folks, thanks to Morten Wennevik!

Comments are welcome!

Mandelbrot in C#

2004-11-24T09:04:00.000-08:00

Last week I talked a lot about Oracle and SQL, Mr Gates may be jealous ;-), let’s have a look to the .Net platform. Today I’m just going to introduce C# and a few stuff of the winform namespace (connection to Oracle will come later, be patient).

First of all, I work with Visual Studio .Net. If you want to understand something to this article you need this tool.

At school I learned Object Oriented Programming with C++ and Java. C# syntax is very close to those languages. The three languages have in common “{“, inheritance, encapsulation, a lot of keywords and something like a cultural background.
At school I also made a Mandelbrot set explorer in C++ as an OOP exercise. I find myself interesting to demonstrate C# coding with those fractal objects.

To do this I will present an object oriented construction to make calculus with complex numbers and the Mandelbrot specific method . I will then show how to use the Complex class into a simple WinForm to design Mandelbrot‘s set pictures.

OK, I won’t make a mathematical demonstration about Mandelbrot’s set here.
You just have to know that it deals with complex numbers and that you can make strange and sometimes beautiful pictures with it.

The idea is that when you multiply a complex number with itself and adds another complex number, this many times; it can grows up infinitely or converge to zero.
If it converges to zero it is a part of the set, when it grows up it is not. And you can measure at what “speed” it grows up.

So, here is my complex numbers class:

using System;// to include the basic libraries

namespace WindowsApplication1//a namespace groups classes working together,
{
// The main class
class Complex
{
//constants
const int IMAX_ITER = 255 ;// number of iterations to estimate the divergence
const double MODUL_LIMIT = 2;//

public double x;//pretty close to c++ syntax,
//"public" indicates that the users of the class can access this property...
public double y;

//////////////////////////
//constructors

//the basic one , does nothing but is mandatory for operators overloading
public Complex()
{}

//the main constructor
public Complex(double i, double j)
{
x = i;
y = j;
}

//////////////////////////
//Operators overloading, we define here basic operations on complex number

//minus
public static Complex operator -(Complex c1,Complex c2)
{
Complex temp = new Complex();
temp.x = (c1.x)-(c2.x);
temp.y = c1.y-c2.y;
return temp;
}
//plus
public static Complex operator +(Complex c1,Complex c2)
{
Complex temp = new Complex();
temp.x = (c1.x)+(c2.x);
temp.y = c1.y+c2.y;
return temp;
}
// multiplication, a little more complicated
public static Complex operator *(Complex c1,Complex c2)
{
Complex temp = new Complex();
temp.x = c1.x*c2.x-c1.y*c2.y;
temp.y = c1.y*c2.x+c1.x*c2.y;
return temp;
}

/////////////////////
//methods

//Module, used to measure the complex number
public double Module()
{
double temp;
temp = System.Math.Sqrt( x*x+y*y);
return temp ;
}
//use to measure the divergence of the complex number
//the number return is use to define the color of the affix's point
public int diverg()
{
int i=0 ;
Complex c = new Complex(x,y) ;

while (i<imax_iter&&c.Module()<MODUL_LIMIT)
{
c=c*c+this;//yes * and + are the operator we've just overload
}
return i;
}
}
}

So we have all the stuffs we need to decide if a complex number is a member of Mandelbrot’s set or not.
Now let’s use it in a Winform.
5 steps :

Step 1 In Visual Studio .net, create a new C# “Windows Application” project.
Step 2 Add a new object to the project (project window), a class(.cs file) names "complex". Then replace all the code of the file by the preceding complex class code.
Step 3 In the design view of the form1.cs add:
- A picture box named BoiteImage
- Four text boxes named respectively:
hgx(left and top X coordinate) default value:-0.5
hgy(left and top Y coordinate) default value:-1
bdx(right and bottom X coordinate) default value: 1.5
bdy (right and bottom Y coordinate) default value: 1
- A button named trace

If your not familiar with Visual studio: you will find all those stuff in the “tool box” window You can change the names and default values by right clicking + properties over the created objects. Then find the “name “ or “defaultvalue” properties in the “properties” window.

Step 4 In the project window double click on the ”trace” button. You are now in the Onclick event implementation method.
Put the following code into the “{ }”:

int nbPix=1000;
double diagDest;

int x,y;
Bitmap Imag;
Color col;
int iDiv;
Complex c= new Complex();

//define the complex plan zone
Complex cHG = new Complex(Convert.ToDouble( this.hgx.Text),Convert.ToDouble( this.hgy.Text));
Complex cBD = new Complex(Convert.ToDouble(this.bdx.Text),Convert.ToDouble(this.bdy.Text));
diagDest=Convert.ToDouble( this.hgx.Text)-Convert.ToDouble( this.bdx.Text);

//instantiate the bitmap
Imag = new Bitmap(nbPix ,nbPix);

//scan the bitmap
for(x=0;x= 255)
//if it converges it's black
col = System.Drawing.Color.FromArgb(255,0,0,0);
else //if it is not, it depends of the speed
col = System.Drawing.Color.FromArgb(128,iDiv,iDiv,iDiv);

//set the pixel color
Imag.SetPixel(x,y,col );
}
//draws the image
BoiteImage.SizeMode = PictureBoxSizeMode.StretchImage ;
BoiteImage.ClientSize = new Size(Imag.Size.Width, Imag.Size.Height);
BoiteImage.Image = (Image) Imag ;

Step 5 Now Hit F5 key and then click the « trace button » enjoy and change the range to explore the set!

That’s all folks
Sources and .exe on line soon !
Comments are welcome

Special thanks to my schoolmates Benoit and Jérôme .

SQL Analytical : Rollup

2004-11-18T07:13:00.000-08:00

A few days ago, I started to introduce analytical SQL functions.
After "group by grouping sets" and "rank over", let's talk about "Rollup".

Let's suppose you have the following table :
create table SALES(
PRODUCT_NAME VARCHAR2(255 BYTE),
DAY NUMBER(2),
MONTH NUMBER(2),
YEAR NUMBER(4),
AMOUNT NUMBER(9)
)
Now, suppose you want to calculate the total amount for one day, the total amount for one month and for one year. You may use the "grouping sets" syntax. This is a powerfull feature which allow you many combinations. But have a look to this one:

select PRODUCT_NAME,DAY ,MONTH ,YEAR, sum(AMOUNT) from sales group by rollup (YEAR,MONTH,PRODUCT_NAME)

This syntax will return the sale's amount for one month and one product then for all the month , this for each month and then the grand total.
Notice that you just need to specify your grouping level. Imagine your final application is something like a querying tool for instance an ASP .NET web form (I should talk about this in another post). You can propose your user a grouping level option button which modify dynamically the rollup field list, easily.

Now, let's suppose your table is not completed , some MONTH fields contains a NULL value. How can you make the difference between the real null value and the null value which is the mark of a group sum.
There is a solution which help to see the difference for a querying tool, the "grouping" function.

select PRODUCT_NAME,DAY ,MONTH ,YEAR, sum(AMOUNT), grouping(YEAR), grouping(MONTH),grouping(PRODUCT_NAME)
from sales
group by rollup (YEAR,MONTH,PRODUCT_NAME)

For instance the "grouping(YEAR)" column return 1 when the line is a sum for one year and the rest of the time. Then your Web form will be able to detect if the line is aggregate and on which level (you may want to change the font or color of the line) . You can also use it with a "decode" function for instance to put the text "Total" instead of the product_name. Let's try this :

select
decode(grouping(PRODUCT_NAME),1,'all product' ,PRODUCT_NAME),
decode(grouping(DAY),1,'all month',DAY),
decode(grouping(month),1,'all year', MONTH) ,
YEAR,
sum(AMOUNT)
from sales group by rollup (YEAR,MONTH,PRODUCT_NAME)

Ok That's all folks , hope this help !Any comments are welcome!

Just another link today

2004-11-15T10:00:00.000-08:00

Oracle Monster: Forums for Oracle DBAs and developers - Offers forums where you can get help with everything from SQL language and security to database design and optimization.

A few Oracle and Microsoft links

2004-11-12T09:52:00.000-08:00

Today I would like to list a few of my favourites web sites.

here is a good oracle sql reference :
http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/toc.htm

Here is a direct link to Microsoft .Net framework reference on msdn:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/netfxanchor.asp .

here are the google archives of usenet groups on which I made a contribution:
comp.databases.oracle.server
microsoft.public.excel.worksheet.functions
microsoft.public.excel.misc
microsoft.public.excel
sybase.public.sqlanywhere.general
Good WE everybody

Strange and usefull Oracle SQL structure

2004-11-09T01:08:00.000-08:00

hello everybody,

Last week I talked a lot about how to get Oracle's data into Excel. I said that, in my opinion, Excel is not the right tool to re-structure and reformat datas. It is not an OLAP tool.
Today I would like to talk about a few Oracle specific SQL syntax that may be usefull for that: "rank over" and "group by grouping sets"

"rank over"
So what is this?
Here is a sample code:
Just imagine you have a table which contains the monthly turnover of the products: MONTHLY_TURNOVER with the following fields PRODUCT_NAME,MONTH,YEAR, AMOUNT.

select MONTH'/''YEAR', PRODUCT_NAME, Amount, rank() over(partition by MONTH, YEAR order by AMOUNT desc) Month_Rank from MONTHLY_TURNOVER order by YEAR asc, MONTH asc,Month_Rank asc;

This query returns all the products for each month, orders by decreasing amount of turnover with the rank of each product in the month.

This query does not make any filtering action. Instead, there is ordering and creation of information. The"rank()" syntax creates a new piece of information : the rank in the month of the product.

Now imagine you have 500 differents products. You may want to see only the 10 most important of each month. This may be obtained by the following syntax:

select * from (
select MONTH'/''YEAR', PRODUCT_NAME, Amount, rank() over(partition by MONTH, YEAR order by AMOUNT desc) Month_Rank from MONTHLY_TURNOVER order by YEAR asc, MONTH asc,Month_Rank asc)
where Month_Rank<10;

I suggest you to build this as an Oracle view and then to query it by filtering on the year field or by the period you are interested in.

"group by grouping sets"

Did you ever work with MS Access report or Crystal Report? Then you must have seen these features that permit you to make sums, averages or other aggregation functions at different levels in those reporting tools. For instance, you can calculate the sum of all the turnovers of your products year by year, month by month, and then product by product for one month.
You can do the same things directly with Oracle.

Using the same table as in the first paragraph, try the following:

select sum(AMOUNT ) AMOUNT , MONTH,YEAR, PRODUCT_NAME
from (MONTHLY_TURNOVER)
group by grouping sets ((MONTH,YEAR, PRODUCT_NAME),(MONTH,YEAR),(YEAR))
order by year, month, product_name;
you will have the following result assuming you have two products and one year :

AMOUNT MONTH YEAR PRODUCT_NAME
---------------------------------------------------------
10 1 2004 p1
20 1 2004 p2
30 1 2004
15 2 2004 p1
25 2 2004 p2
40 2 2004
. . .

60 12 2004
360 2004

As you can see, the line that sums all the turnovers of one month does not have any value for "product_name". The line that sums all the year does not have any value for "product_name" and "month".

I found this very usefull because if you have to do this kind of query usualy you need to use three "select from where " clauses and you need to repeat three times your "where" clause.
Personnaly, I used it in a view to sum surfaces for different real estate assets. For each building I get the sum of the rented and the free surface, and the total.

Ok that's all folks.

Hope this will help developers as it helps me!
Comments are welcome!

From the datawarehouse to the chart (part 2)

2004-11-02T05:21:00.000-08:00

Last week I introduce the MS Query feature of Excel.
Let's talk today about a few tricks to optimize its utilization with Oracle.

Ok first you may have notice that this a very slow feature. Changes to the source query are uneasy. You need to open the source query. Then to use an old fashion query defintion tool. And then to go back to the Excel file to finally realize that you have to change some criteria in the SQL code.

How to speed up this?

Use your knowledge of the database

Make Views.

It seems to be an easy-to-say thing but, to my point of view, this it is critical. I think that Excel by it self don't have to be a datamart and shouldn't. You should Use Oracle Views to reformat or re-structure your data. There is many standard and Oracle specific SQL statements which can help you for this (that is a good idea for a next post).

Use routine to change SQL.
You are the developper. You know where to get the information in the database.
Most of the time you define your queries in your fovarite Oracle tool and not in Excel or MS Query. So you don't need a wizard to explore the database. Instead you may need, as I, a quick way to seek-and-destroy bugs.

I prefer to use my own a VB statement (which will be not stored into the final file).
Here is the sub I use :

Sub createquery(strODBC, strSQL, strDestinationRange, strNAme)
With ActiveSheet.QueryTables.Add(strODBC, Destination:=Range(strDestinationRange))
.CommandText = strSQL
.Name = strNAme
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

strODBC is the an ODBC string something like :
ODBC;DSN=DSNName;UID=USer_ID;PWD=Password;DBQ=SCHEMA_NAME;"
strSQL is the SQL statement you want to execute here
strDestinationRange is something like "B2" or "$A$12:$H$52"

You should use this statement carefully at your own risq and, off course, fit it to your needs.

Notice that you may change the ODBC string structure, for instance if you omit thePassword excel will prompt you to logon. Notice that

Using parameters.

Parameters allow you to change criteria of a query without any VB code.
Here is the way I use parameters in MS query :
I use my favorite Oracle developpement tool and make a SQL query without parameters for instance :

select name from customers where max_ammount>1000
and min_amount <100

here is the versions with parameters

select name from customers where max_ammount>?
and min_amount < ?

Yes, there is no named parameters. Excel have specifical way to bind those things, just using order.

Try it, for instance with the preceding VB code, or with the MS Query tool.
When refreshing the data, Excel ask you to fill those parameters.

At any time you can specify the source for each parameters:

Right click on the range of data.
Choose "parameters"
Select the parameter you
Then select the option you want for each parameter.

The first option is interesting. It allows you set the text of a question which will be asked to the user for this parameter.

Notice that when you use VB code to create a query with parameter, Excel gives default names to these parameters. If you want set your own names use the following syntax in a vb module :

Dim qt As QueryTable

Set qt = ActiveSheet.QueryTables("MyQuery")

qt.Parameters("Parameter1").Name = ("myFirstParam")

qt.Parameters("Parameter2").Name = ("mySecondParam")

All properties of parameters seems to be reachable by rightclick->parameters but the name which can be change only by code.

Charts

Now you know how to make get data from parameterized query over Oracle into your Excel workbook, I suggest you the following if you need to produce many charts from yout datawarehouse.

I - Let's suppose you have built a View describing the monthly 's turnover of your products. In a new excel sheet type-in the product id of one of your products, build a query like the following :

Select MonthNumber, turnover from v_monthly_turn where product_Id = ? and year = 2004

II - When the wizard prompt you for the parameter's value, select a cell which contains your product's ID and check the box to make this permanent.

III - When the data are returned select the range. Click on the chart button an choose your chart's style and location.

IV - Change your product's ID and refresh the data by right click->refresh data over the data range.

You may customise this:

Instead of a cell based parameter use a parameter whith a question like "What is the product ID?" . You may then set the "Refresh on file open" property to yes :(right click on the range then "properties") your file will then become a little decisional tool. And this without any VB code!

That 's all for today thank you for your attention.

All comments are welcome.

From the datawarehouse to the chart (part 1)

2004-10-29T09:24:00.000-07:00

After Access lets use Excel as a client for Oracle.

I work for a real estate company where assets managers always want to make charts or calculus based on data stored in the Oracle dataWarehouse. Most od the time they want to do this with Excel because well known this tool. Sometimes they want to use Excel just because its easy to format data and then to put it in a Powerpoint document.

The problem is that we do not want to use VB macro in those Excel files . Those files may be sent by e-mail to clients. Any good firewall will destroy such files.

One solution is to generate files on a server. For instance you install Oracle's client and office on a Windows 2003/IIS5 server and implement complex ASP/ASPX pages which generate Excel files .
You may also use a Excel intepreted ASP page. Both of this solutions are hard to set up and need a lot of code.

There is another solution: lets use MS Query!

First of all your users need:
- Oracle connectivity install and MS Excel on there computer.
- A Data Source Name configure on there computer .
- A login and password which they can use to access the database.

I assume you work with Windows NT/XP and got an Oracle Server.
Here is how to get data from Oracle:
- create a new Excel document.
- in the "data" menu choose "External data" then "new query"
-Choose the appropriate Data Source Name
- Logon
- A wizard will guide you to make your select statement.
- choose return to Excel and validate.

surprise you've got your datas into Excel !

That's all folks! Any comments are welcome!

Next time I will try to expose how to fine tune this feature.

Using updatable views in Access

2004-10-28T07:58:00.000-07:00

These "updatable views" feature in Oracle is interesting. You can specify triggers on a view to make it behaves like a table. Its interesting when different processes or applications use the same data but not the same way.
How to make this working with Microsoft Access?

First define your view with a statement like the following:
create view MyView as select .......

then add your trigger(s) :
create or replace trigger MyUpdateTrigger
INSTEAD OF UPDATE ON MyView as
begin
update MyUnderlyingTable ut
set ut.Field = new.ViewField
where ut.keyfield new.keyfield ;
end;

notice the "instead of " statement . You may also add "delete" or "insert" triggers the same way.

Now, how do you make Access 2003, your favorite client side application , works with this?
Open your Access database and link the view as you would for a table.
To do this:

- In the file menu choose "external data" and "link table" . Then choose or create the Data Source Name which map your Oracle Database.

- You will then be prompt to log with your Oracle Profile. You need to be familiar with ODBC and DSN (that is an idea for a new post).

-Then Access display a list of all views and tables available. Depending of your profile you may see system tables and views. Choose "MyView".

- You are then prompt to define the unique identifier of this "table". Choose it regarding the trigger's code you've implement. It should be the logical equivalent of a primary key.

Now your view is available in Access. And it works!

No it dosen't. In fact it works but it is not a practical way to build an application and test it.

If you make a change to your view's definition the linked table in Access is no more available.
So debugging a complex application with this is like a turtle race: you need to pe patient ;-).

Here is a sample VB Access code which may help you :

Sub ConnectTable(strNomTable, strNomtablesource, strODBC, strKEy)
Dim bds As Database, dft As TableDef
Dim strSql
Dim oFi As Field

Set bds = CurrentDb

'delete the existing table
If TableExists(strNomTable) Then
CurrentDb.TableDefs.Delete (strNomTable)
End If

Set dft = bds.CreateTableDef(strNomTable)

dft.Connect = strODBC
dft.SourceTableName = strNomtablesource

bds.TableDefs.Append dft
If Strings.Trim(strKEy) <> "" Then
strSql = "CREATE INDEX " & strNomTable & "_PK" & " ON " & strNomTable & "(" & strKEy & ") WITH PRIMARY"
bds.Execute strSql
End If
fin:
Set dft = Nothing
Set bds = Nothing
End Sub

Put this in a module and call it with the following parameters:

strNomTable is the name of the Access linked table object.
strNomtablesource is the Oracle view 's name, it may include a schema prefix.
strODBC is the ODBC connection string matching you oracle database.
it should be like the following : "ODBC;DSN=DSN_name;DBQ=oracle_schema_name;PWD=Password;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;DATABASE=" notice that here you have to provide a password, the one matching the user name provided in the DSN.
strKey is the coma separated list of fields defining the unique identifier for the view.

That's all folks! Comments are welcome!

Introduction

2004-10-28T06:25:00.000-07:00

Hello,
I'm a french developper. I spend spend most of my time at work searching solutions to problems like :
" How can we get the data( in Oracle database) into the document (Microsoft Office file)"
or
".Net studio is cool use it! You won't use SQL Server as as Database use Oracle for your applications"