A tool for editing tables with two fields

• Download source - 18.4 Kb

Introduction

I usually work with some companies and I build custom software for them. In most of the projects I have worked in I faced some common problems in the management of some tables in the db. Specially, in all of the projects I had to deal with some tables that have two fields. Let’s say we have a form with a combobox that the user have to insert the name of a city, if we want to store some cities, so the user can select from them, we usually do this by creating a table with two fields (ID, city). Also always the user wants a way that can manage this table (add, modify, delete records). So in every project I had to deal and spend some time to create forms for the management of this tables. I decided to create a tool that automates the interaction with this table.

There were some problems and specially that some times I use sql server databases and some other access databases and I didn’t want to create two components for this thing. The tool that finally I have created is a class derived from System.Windows.Forms.Form that needs only the name of the table you want to deal with, the name of the field in this table and the length of it, also it needs a System.Data.IDbConnection resolving in this way the problem of the multiple dbs. Also it has three properties named AllowAdd ,

AllowUpdate, AllowDelete

in case someone that use the form doesn’t want to allow the user to add, update or delete any of the records, and in the end the form has a pair of events for any of the three operations (add, update, delete),

Addingrec ,Addedrec, Updatingrec,
Updatedrec

, Deletingrec and Deletedrec so the developer can add any business logic in any user’s action (allow or cancel any of the operations the user perform, or make some special validation). This form supports localization and works in (English(Default), Italian, French, Spanish, Greek). In future editions the form will also support direct print of the table without any coding from the part of the developer.

The only “special” code in the project is the code that detect the type of the connection (SqlConnection, OledbConnection, OdbcConnection) in the runtime and initialize the DataAdapters and the Commands accordingly. This is the form (in English):

The class

Here are the members of the class:

public class twofieldform : System.Windows.Forms.Form
{
    private System.Windows.Forms.Button addbut;
    private System.Windows.Forms.Button updatebut;
    private System.Windows.Forms.Button delbut;
    private System.Windows.Forms.Button printbut;
    private System.Windows.Forms.Label textlabel;
    private System.Windows.Forms.Label originallabel;
    private System.Windows.Forms.Label titlelabel;
    private System.Windows.Forms.TextBox maintext;
    private System.Windows.Forms.DataGrid maingrid; 
    private string emftitlos; // The text of the form
    private string emffield; 
     // What is written in the the textlabel
    private string tablename; 
      // the name of the table that we manage
    private string mainfield; 
     // the name of the text field we 
     // want to manage within the table
    private int fieldlength; // the length of the field
    private System.Data.IDbConnection maincon;
    private System.Data.IDbDataAdapter mada;
    private System.Data.IDbCommand inscom;
    private System.Data.IDbCommand updcom;
    private System.Data.IDbCommand delcom;
    private System.Data.IDbCommand findid;
    private System.Data.DataSet tempset;
    private System.Data.DataTable maintable;

    //private printingdatagrids.SetTableSettings tableset;

    public event dbforms.twofieldformEventHandler Addingrec;
    public event dbforms.twofieldformEventHandler Addedrec;
    public event dbforms.twofieldformEventHandler Updatingrec;
    public event dbforms.twofieldformEventHandler Updatedrec;
    public event dbforms.twofieldformEventHandler Deletingrec;
    public event dbforms.twofieldformEventHandler Deletedrec;

    private System.ComponentModel.Container components = null;
    public bool AllowAdd
    {
       get
       {
           return this.addbut.Enabled;
       }
       set
       {
           this.addbut.Enabled=value;
       }
    }
    public bool AllowUpdate
    {
       get
       {
           return this.updatebut.Enabled;
       }
       set
       {
           this.updatebut.Enabled=value;
       }
    }
    public bool AllowDelete
    {
       get
       {
           return this.delbut.Enabled;
       }
       set
       {
           this.delbut.Enabled=value;
       }
    }
    public bool AllowPrint
    {
       get
       {
           return this.printbut.Enabled;
       }
       set
       {
           this.printbut.Enabled=value;
       }
} …

The first declarations are all of the controls in the form. The originallabel is a special label used only for databinding in a way that the program can understand if the user want to modify a record. After the controls declaration there is emftitlos which is the string that is written in the text property of the form and in the titlelabel, emffield is the text written in the textlabel (the program also adds an “:” in the emffield), tablename is the name of the table in the db that we want to manage, mainfield is the name of the field in the table and the fieldlength is the length of the manfield.

The limitation of the form is that the table in the db must have an ID field which must be an int and autonumber.

After that there is maincon which is the connection the form uses to enter in the db, mada is the dataAdapter used to fill the local datatable with all the fields, inscom, updcom and delcom are the commands used to add, update, delete records from the db, findid is a command used to find the ID of a newly added record so we can add the new record in the local datatable directly without merging tables or reloading the whole table again, tempset is a dataset used temporarily because of limitation in the fill function of the IDbDataAdapter interface and finally maintable is a datatable containing the data of the table. Following there is the declaration of all the events which are of type twofieldformEventHandler which I am going to explain shortly. Also there is the declaration of the properties which are very easy to understand.

There is in the project except the twofieldform.cs a file named twofieldformevent.cs which contains the code for the events:

public class twofieldformEventArgs : System.EventArgs
{
    public bool Cancel;
    public int ID;
    public string newvalue;
    public string oldvalue;
    public string errormessage;
    public twofieldformEventArgs()
    {
       Cancel=false;       
    }
}

public delegate void twofieldformEventHandler(
  object sender, twofieldformEventArgs e);

First of all there is the delegate used in all of the events named twofieldformEventHandler which takes two arguments, sender which is the object who fired the event and the e which is of type twofieldformEventArgs. twofieldformEventArgs is a class with 5 members, there are six events that uses this members in a different way. In the three events Addingrec, Updatingrec, Deletingrec if the developer wants, can cancel the operation by setting the cancel to true and the errormessage to a message that the form will display.Let’s explain all of the situations:

1. Addingrec: In this situation we use only the newvalue and we set it to the value of the mainfield the user want to insert.
2. Addedrec: If the programmer does not set cancel to true, the form adds the new record to the db and set the ID field to the new ID of the record
3. Updatingrec: In this situation the form set oldvalue to the value of the mainfield of the record we want to change and the ID to the ID of it and the newvalue to the value that the user has entered.
4. Updatedrec: If the programmer does not set cancel to true, the form fires Updatedrec event with the same arguments as Updatingrec
5. Deletingrec: Here the form set oldvalue to the value of the mainfield of the record the user wants to delete and the ID to the ID of it
6. Deletedrec: And finally if the programmer does not cancel the operation the form fires Deleterecrec event with the same arguments

After that there is the constructor of the class:

public twofieldform(System.Data.IDbConnection thecon,
 string emfanisimostitlos,string emfanisimopedio,
 string thetablename,string mainpedio,int flength)
{
    welcome();
    InitializeComponent();
    emftitlos = emfanisimostitlos;
    emffield=emfanisimopedio;
    tablename=thetablename;
    mainfield = mainpedio;
    fieldlength=flength;
    maincon=thecon;
    this.Text= this.emftitlos;
    this.titlelabel.Text=this.emftitlos;
this.textlabel.Text=this.emffield+":";
    this.maintext.MaxLength=fieldlength;
    preparecommandsandadapters();
    tempset = new System.Data.DataSet();
    try
    {
       mada.Fill(tempset);
       maintable = tempset.Tables[0];
       maintable.TableName="mt";
    }
    catch (System.Exception a)
    {
       MessageBox.Show(a.Message);
    }
    checkifmainfieldexist();
    System.Data.DataColumn[] pk = new System.Data.DataColumn[1];
    pk[0]=maintable.Columns["ID"];
    maintable.PrimaryKey=pk;
}

The constructor take for parameters the values of

emftitlos,
emffield, tablename, mainfield

and fieldlength plus an IDbConnection which is the connection the developer want to use to connect to the db. In the first line the welcome function is executed which is nothing but a test for the localization (you can omit it if you want), after that is the InitializeComponent() which is the designer’s function. The only thing in this function, that is a bit strange is the position of the originallabel which is under the datagrid so the user cannot see it (bad code but I don’t have time to do it better, sorry J). After that there is some code to set the text properties and UI things and after that preparecommandsandadapters() is executed.

The first part of preparecommandsandadapters is the following:

private void preparecommandsandadapters()
{
    System.Data.IDbDataParameter newpar;
    if (this.maincon is System.Data.SqlClient.SqlConnection)
    {
mada = new System.Data.SqlClient.SqlDataAdapter(
 "SELECT * FROM "+
 this.tablename,(System.Data.SqlClient.SqlConnection)maincon);
 
inscom = new System.Data.SqlClient.SqlCommand(
 "INSERT INTO "+this.tablename+"("+this.mainfield+") 
 VALUES(@mainfield)",(System.Data.SqlClient.SqlConnection)maincon);
 
       newpar = new System.Data.SqlClient.SqlParameter();
       newpar.ParameterName="@mainfield";
       newpar.DbType=System.Data.DbType.String;
       newpar.Size=this.fieldlength;
       inscom.Parameters.Add(newpar);
       updcom = new System.Data.SqlClient.SqlCommand(
 "UPDATE "+this.tablename+" SET "+this.mainfield+
 "=@mainfield WHERE ID=@ID",(System.Data.SqlClient.SqlConnection)maincon);
       newpar = new System.Data.SqlClient.SqlParameter();
       newpar.ParameterName="@mainfield";
       newpar.DbType=System.Data.DbType.String;
       newpar.Size=this.fieldlength;
       updcom.Parameters.Add(newpar);
       newpar = new System.Data.SqlClient.SqlParameter();
       newpar.ParameterName="@ID";
       newpar.DbType=System.Data.DbType.Int32;
       newpar.Size=this.fieldlength;
       updcom.Parameters.Add(newpar);
          
       delcom = new System.Data.SqlClient.SqlCommand(
 "DELETE FROM "+this.tablename+" WHERE ID=@ID",
 (System.Data.SqlClient.SqlConnection)maincon);
       newpar = new System.Data.SqlClient.SqlParameter();
       newpar.ParameterName="@ID";
       newpar.DbType=System.Data.DbType.Int32;
       delcom.Parameters.Add(newpar);
       findid = new System.Data.SqlClient.SqlCommand(
 "SELECT ID FROM "+this.tablename+" WHERE "+this.mainfield+
 "=@mainfield",(System.Data.SqlClient.SqlConnection)maincon);
       newpar = new System.Data.SqlClient.SqlParameter();
       newpar.ParameterName="@mainfield";
       newpar.DbType=System.Data.DbType.String;
       newpar.Size=this.fieldlength;
       findid.Parameters.Add(newpar);
    }
}

After that there is the same code for oledb and odbc. The code detects with the is operator if the maincon is sqlconnection and initializes all of the adapters and commands for sql, another thing is that I haven’t used the Add function of the parameters property of the command to insert in a single line the parameter and I had to initialize an object and set all the properties to do so because in the IdbCommand interface the parameters collection is a simple list and does not have the add function. After preparecommandsandadapters in the constructor I initialize the tempset and I fill it using the Fill function of the IdbDataAdapter, the reason that I use a dataset and not directly my maintable member is that the fill function of the interface supports only this mode and creates a table in the dataset with the name “Table”, after that I get the table in the maintable and I change his name , also with the function checkifmainfieldexist I do some checks about the existence of the mainfield and the ID field in the table and also if the table has 2 columns or more and after that in the constructor I set the primary key:

private void checkifmainfieldexist()
{
    System.Resources.ResourceManager resources = 
     new System.Resources.ResourceManager(typeof(twofieldform));
    bool mainfieldexist = false;
    bool IDexist = false;
    foreach (System.Data.DataColumn a in maintable.Columns)
    {
       if (a.ColumnName == this.mainfield) mainfieldexist = true;
       if (a.ColumnName == "ID") IDexist=true;
    }
    if (mainfieldexist==false) 
    {
       throw new Exception(resources.GetString("error1"));
    }
    if (IDexist==false) 
    {
       throw new Exception(resources.GetString("error2"));
    }
    if (maintable.Columns.Count>2)
    {
       throw new Exception(resources.GetString("error3"));
    }
}

We continue with the twofieldform_Load event:

private void sqltwofieldform_Load(object sender, System.EventArgs e)
{           
    System.Windows.Forms.DataGridTableStyle maintablestyle = 
     new System.Windows.Forms.DataGridTableStyle();
    maintablestyle.MappingName = "mt";
    System.Windows.Forms.DataGridColumnStyle maincolumnstyle = 
     new System.Windows.Forms.DataGridTextBoxColumn();
    maincolumnstyle.HeaderText=this.emffield;
    maincolumnstyle.MappingName=this.mainfield;
    maincolumnstyle.ReadOnly=true;
       maintablestyle.GridColumnStyles.Add(maincolumnstyle);
    maingrid.TableStyles.Add(maintablestyle);
    maingrid.DataSource = maintable;
    maingrid.DataBindings.Add("Tag",maintable,"ID");
       originallabel.DataBindings.Add(
       "Text",maintable,this.mainfield)       
}

In this event I build a table style for the datagrid based on the mainfield and I add it to the tablestyles collection of the datagrid. After that I set the datasource of the maingrid and set various databindings. The originallabel databinding is mainly used to know if the user has made any change in the textbox after selecting an item from the grid.

So now I am going to describe one of the three possible actions the user can perform. I prefer to describe the update which is a bit more complicated, the other two are done in a similar way. For each action I have the button event and a method that actually executes the IdbCommand.

private void updaterec(int ID,string newvalue)
{
    try
    {
       maincon.Open();
 ((System.Data.IDbDataParameter)updcom.Parameters["@id"]).Value=ID;
 ((System.Data.IDbDataParameter)updcom.Parameters[
  "@mainfield"]).Value=newvalue;
       updcom.ExecuteNonQuery();
       maincon.Close();
    }
    catch (System.Exception a)
    {
       MessageBox.Show(a.Message);
    }
}

I think that the function is pretty simple, first I assign values to the parameters of the command and after that I execute it and all this in a try catch statement, I cast the updcom.parameters[“@ID”] because as I have said the IdbCommand.Parameters collection is a simple object collection. Now there is the update button click event:

private void updatebut_Click(object sender, System.EventArgs e)
{
   string newv = maintext.Text.ToString();
    System.Data.DataRow[] exrows= maintable.Select(
this.mainfield+"='"+newv+"'");
    if (exrows.Length>0)
    {
       MessageBox.Show("The record allready exist",
"Message",System.Windows.Forms.MessageBoxButtons.OK);
       return;
    }
    else
    {
       System.Data.DataRow therow= 
maintable.Rows.Find(maingrid.Tag.ToString());
       dbforms.twofieldformEventArgs ea = 
new dbforms.twofieldformEventArgs();
       ea.newvalue=newv;
       ea.oldvalue=therow[this.mainfield].ToString();
       ea.ID=int.Parse(maingrid.Tag.ToString());
       if (this.Updatingrec!=null)
       {
           this.Updatingrec(this,ea);
       }
       if (ea.Cancel==false)
       {
           updaterec(int.Parse(maingrid.Tag.ToString()),newv);  
           if(this.Updatedrec!=null)
           {
              this.Updatedrec(this,ea);
           }
              therow[this.mainfield]=newv;
              maintable.AcceptChanges();
           }
           else
           {
 MessageBox.Show(ea.errormessage,this.emftitlos,
  System.Windows.Forms.MessageBoxButtons.OK);
              return;
           }
    }
}     

First of all newv is a string variable containing the new value of the mainfield, after that I check if this value exists already in the table and then I find the row the user want to change, I use the find method because I have set the ID to be a primary key in the datatable, then I create a twofieldformeventargs class with all the data needed for the update event and I fire it. To fire an event you have to check first because it is a delegate if it is null.if the cancel variable within the class is false I update (using the updaterec method) the db and I fire the next update event, Also I change the local maintable directly. if it is true I cancel the operation and display a message box with the error message string.

Conclusion

I think the above piece of code can be of great help for some programmers who use this auxiliary table often in their applications without having to face the same annoying code each time tables are needed.