Marc Boizeau's blog

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!