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;
private string emffield;
private string tablename;
private string mainfield;
private int fieldlength;
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;
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:
Addingrec: In this situation we use
only the newvalue and we set it to the value of the mainfield the user want to
insert.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 recordUpdatingrec: 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.Updatedrec: If the programmer does not
set cancel to true, the form fires Updatedrec event with the same arguments as
UpdatingrecDeletingrec: 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 itDeletedrec: 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.
