Guys today I will introduce some great and small piece of code which will make your life heaven in respect of developing winform data entry applications with .net Framework 4.0 Win Forms, have you ever thought of applying functions/classes in application that’s really make life easy and automate your data entry applications development, like what you think of when you come to develop winform application, no.1 how would data would be shown, no.2 how would I clear , enable and disable all controls, no.3 how would I apply validations no.4 how can I make auto insert, update and delete statements with PKFields no .5 how can I make user navigation eaiser , all these questions are answered below with my own developed Utilty Class……
Please note here that I haven’t used any third party control like infragistics, teleric etc, I have tried to use only .net controls, but you can also automate your app with third party controls too by modifiying this class. So here we go…..
//Author : Imran Raza
//UTILITY CLASS is for having common functions that will be used in all data entry forms.
class Utility
{
// method for Enable Disable Controls in winform method
public void EnableDisableControls(object objfrm, bool blnenable)
{
Control frm = (Control)objfrm;
foreach ( Control c in frm.Controls )
{
if (c.Controls.Count > 0 )
EnableDisableControls(c,blnenable);
if (c.Name != string.Empty)
{
if (c is TextBox c is ComboBox c is DateTimePicker)
{
if (c.Name.Substring(0, 2) != "dd")
c.Enabled = blnenable;
else
c.Enabled = false;
}
else if (c is DataGridView && c.Name != "grdMaster")
c.Enabled = blnenable;
}
}
}
// Method for clearing Controls in winform method
public void ClearAllControls(object objfrm)
{
Control frm = (Control)objfrm;
foreach (Control c in frm.Controls)
{
if (c.Controls.Count > 0)
ClearAllControls(c);
if (c is TextBox c is ComboBox)
c.Text = string.Empty;
else if (c is DataGridView && c.Name != "grdMaster")
{
DataGridView grd;
grd = (DataGridView)c;
grd.Rows.Clear();
}
}
}
// Populate Controls of winform with the help of datarow of datatable
public void PopulateControls(Form frm, DataRow dtr)
{
CheckBox chkbox;
foreach (DataColumn Col in dtr.Table.Columns)
{
foreach (Control c in frm.Controls)
{
//Condition for compareing control name (starting from 3rd character)
//for e.g. if control name is "txtName" then it will compare
//with db field name like this (Name==Name)
if (c.Name.Substring(2,c.Name.Length) == Col.ColumnName)
{
if (c is TextBox)
c.Text = dtr[Col].ToString();
else if (c is CheckBox)
{
chkbox = (CheckBox)c;
chkbox.Checked = dtr[Col].ToString().Equals("1");
}
}
}
}
}
// Validating Controls (if starting 2 char are “di” then give msg of validation..
public bool ValidateControls(object objfrm, ref string strError)
{
Control frm = (Control)objfrm;
foreach (Control cnt in frm.Controls)
{
if (cnt.Controls.Count > 0)
ValidateControls(cnt, ref strError);
if (cnt.Name.Substring(0, 2).ToString() == "di" && cnt.Text == string.Empty)
{
strError += Environment.NewLine + "Empty Value not allowed in " + cnt.Tag;
}
}
if (strError.Equals(string.Empty))
return true;
else
return false;
}
//Populating Navigation Grid with Stored Procedure and set Column Header with the help of Control
//Tag Property
public void PopulateGrid(DataGridView grdDetail, Control.ControlCollection Controls,string strStoredPocedureName)
{
DataLayer clsDataLayer = new DataLayer();
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.CommandText=strStoredPocedureName;
dstGrid=clsDataLayer.ExecuteCommand(sqlcmd);
grdDetail.DataSource = dstGrid;
grdDetail.DataMember = "Table";
SetGridColHeaders(grdDetail, Controls);
}
//Setting navigation Grid Column Headers with the help of TAG property of control
private void SetGridColHeaders(DataGridView grdDetail, Control.ControlCollection Controls)
{
foreach (DataGridViewColumn Col in grdDetail.Columns)
{
foreach (Control cnt in Controls)
{
if (cnt.Controls.Count > 0 && cnt.Name !="grdMaster")
SetGridColHeaders(grdDetail, cnt.Controls);
if (cnt.Name != string.Empty)
{
if (Col.DataPropertyName == cnt.Name.Substring(5))
Col.HeaderText = cnt.Tag.ToString();
}
}
}
}
//Setting values from Navigation Grid in Controls of Form
public void SetValuesFromGrid(DataGridView grdDetail, Control.ControlCollection Controls)
{
try
{
foreach (DataGridViewColumn Col in grdDetail.Columns)
{
foreach (Control cnt in Controls)
{
if (cnt.Controls.Count > 0 && cnt.Name != "grdMaster")
SetValuesFromGrid(grdDetail, cnt.Controls);
if (cnt.Name != string.Empty)
{
if (Col.DataPropertyName == cnt.Name.Substring(5))
{
setCntValue(cnt, grdDetail.CurrentRow.Cells[cnt.Name.Substring(5)].Value.ToString());
}
}
}
}
}
catch (Exception ex)
{
return;
}
}
//Setting value according to control type
private void setCntValue(Control cnt,string Value)
{
try
{
if (cnt is CheckBox)
{
CheckBox chkCnt = (CheckBox)cnt;
chkCnt.Checked = Value.Equals("True");
}
else if (cnt is TextBox)
{
cnt.Text = Value;
}
else if (cnt is DateTimePicker)
{
DateTimePicker dtpCnt = (DateTimePicker)cnt;
//CHECK FOR DATETIME PICKER WHETHER ITS MONTH, YEAR OR NORMAL DATE
if (dtpCnt.Tag.ToString() == "Month")
dtpCnt.Value = Convert.ToDateTime("01-" + CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(Convert.ToInt32(Value)) + "-2010");
else if (dtpCnt.Tag.ToString() == "Year")
dtpCnt.Value = Convert.ToDateTime("01-january-" + Value);//DateTime.Now.AddYears(DateTime.Now.Year+1 * -1).AddYears(Convert.ToInt32(Value));
else
dtpCnt.Text = Value;
}
else if (cnt is ComboBox)
{
ComboBox cmbCnt = (ComboBox)cnt;
cmbCnt.SelectedValue = Value;
}
else if (cnt is NumericUpDown)
{
NumericUpDown cmbCnt = (NumericUpDown)cnt;
cmbCnt.Value =Convert.ToDecimal( Value);
}
}
catch (Exception exd
{
return;
}
}
//Public method for saving a single record from single transaction form.
public void SaveSingleRecord(Control.ControlCollection Controls, String TableName, string strpkfieldname,string intpkfieldvalue,DataGridView grdMaster,string GetStoredProcedureName)
{
if (strpkfieldname == string.Empty)
strpkfieldname = "0";
if (blnEdit == true)
{
UpdateSingleRecord(Controls, TableName, strpkfieldname, Int32.Parse( intpkfieldvalue));
PopulateGrid(grdMaster, Controls, GetStoredProcedureName);
grdMaster.Rows[GetCurrentRowIndex(strpkfieldname, Int32.Parse(intpkfieldvalue), grdMaster)].Selected = true;
grdMaster.CurrentCell = grdMaster.SelectedRows[0].Cells[0];
SetValuesFromGrid(grdMaster, Controls);
}
else
{
InsertSingleRecord(Controls, TableName);
PopulateGrid(grdMaster, Controls, GetStoredProcedureName);
grdMaster.Rows[grdMaster.Rows.Count-2].Selected = true;
grdMaster.CurrentCell = grdMaster.SelectedRows[0].Cells[0];
SetValuesFromGrid(grdMaster, Controls);
}
}
//(WITH MULTIPLE PKFIELDS AND PKVALUES ) Public method for saving a single record from single transaction form.
public void SaveSingleRecord(Control.ControlCollection Controls, String TableName, string[] strpkfieldname, string[,] pkfieldvalue, DataGridView grdMaster, string GetStoredProcedureName)
{
//if (strpkfieldname == string.Empty)
// strpkfieldname = "0";
if (blnEdit == true)
{
UpdateSingleRecord(Controls, TableName, strpkfieldname, pkfieldvalue);
PopulateGrid(grdMaster, Controls, GetStoredProcedureName);
grdMaster.Rows[GetCurrentRowIndex(strpkfieldname,pkfieldvalue, grdMaster)].Selected = true;
grdMaster.CurrentCell = grdMaster.SelectedRows[0].Cells[0];
SetValuesFromGrid(grdMaster, Controls);
}
else
{
InsertSingleRecord(Controls, TableName);
PopulateGrid(grdMaster, Controls, GetStoredProcedureName);
grdMaster.Rows[grdMaster.Rows.Count - 2].Selected = true;
grdMaster.CurrentCell = grdMaster.SelectedRows[0].Cells[0];
SetValuesFromGrid(grdMaster, Controls);
}
}
private int GetCurrentRowIndex(string strpkfieldname,int intpkfieldvalue,DataGridView grdMaster)
{
DataRow dr= dstGrid.Tables[0].Select(strpkfieldname + "=" + intpkfieldvalue)[0];
return dstGrid.Tables[0].Rows.IndexOf(dr);
}
private int GetCurrentRowIndex(string[] strPkFieldName, string[,] strpkfieldValue, DataGridView grdMaster)
{
string strFilter = string.Empty;
for (Int16 i = 0; i < strPkFieldName.Length; i++)
{
strFilter += i == 0 ? " " : " And ";
strFilter += strPkFieldName[i] + "=";
//strFilter += strpkfieldValue[i, 0] != "System.Int32" ? "'" : string.Empty;
strFilter += strpkfieldValue[i, 0] == "System.DateTime" ? "#"+ String.Format("{0:MM/dd/yy}", DateTime.ParseExact(strpkfieldValue[i, 1], "dd/MM/yy", null)) + "#": strpkfieldValue[i, 1];
//strFilter += strpkfieldValue[i, 0] != "System.Int32" ? "'" : string.Empty;
//
}
DataRow dr = dstGrid.Tables[0].Select(strFilter)[0];
return dstGrid.Tables[0].Rows.IndexOf(dr);
}
public void DeleteSingleRecord(string TableName, string strpkfieldname,int intpkfieldvalue)
{
string strDeleteQuery;
strDeleteQuery = "Delete from " + TableName;
strDeleteQuery += " Where " + strpkfieldname + "=" + intpkfieldvalue;
DataLayer clsDataLayer = new DataLayer();
clsDataLayer.ExecuteSql(strDeleteQuery);
}
public void DeleteSingleRecord(string TableName, string[] strpkfieldname, string[,] strpkfieldvalue)
{
string strDeleteQuery;
strDeleteQuery = "Delete from " + TableName;
for (Int16 i = 0; i < strpkfieldname.Length; i++)
{
strDeleteQuery += i == 0 ? " Where " : " And ";
strDeleteQuery += strpkfieldname[i] + "=";
strDeleteQuery += strpkfieldvalue[i, 0] != "System.Int32" ? "'" : string.Empty;
strDeleteQuery += strpkfieldvalue[i, 0] == "System.DateTime" ? String.Format("{0:MM/dd/yy}", DateTime.ParseExact(strpkfieldvalue[i, 1], "dd/MM/yy", null)) : strpkfieldvalue[i, 1];
strDeleteQuery += strpkfieldvalue[i, 0] != "System.Int32" ? "'" : string.Empty;
//
}
DataLayer clsDataLayer = new DataLayer();
clsDataLayer.ExecuteSql(strDeleteQuery);
}
private void InsertSingleRecord(Control.ControlCollection Controls, String TableName)
{
string strValues = string.Empty;
string strInsertQuery = string.Empty;
strfields = string.Empty;
GetInsertFields(Controls);
strInsertQuery = " Insert into " + TableName;
strInsertQuery += "(" + strfields.Substring(0,strfields.Length-1) + ")";
strInsertQuery += "Values ";
strfields = string.Empty;
GetInsertFieldsValues(Controls);
strInsertQuery += "(" + strfields.Substring(0, strfields.Length - 1) + ")";
DataLayer clsDataLayer = new DataLayer();
clsDataLayer.ExecuteSql(strInsertQuery);
}
//Get Insert Fields Names for insert statement
private void GetInsertFields(Control.ControlCollection Controls)
{
foreach (Control cnt in Controls)
{
if (cnt.Controls.Count > 0 && cnt.Name != "grdMaster")
GetInsertFields(cnt.Controls);
if (cnt.Name != string.Empty)
{
if ((cnt is TextBox cnt is ComboBox cnt is CheckBox cnt is DateTimePicker cnt is NumericUpDown) && cnt.Name.Substring(0, 2) != "dd")
strfields += cnt.Name.Substring(5) + ",";
}
}
}
//Get Insert Fields Values for insert statement
private void GetInsertFieldsValues(Control.ControlCollection Controls)
{
foreach (Control cnt in Controls)
{
if (cnt.Controls.Count > 0 && cnt.Name != "grdMaster")
GetInsertFieldsValues(cnt.Controls);
if (cnt.Name != string.Empty)
{
if ((cnt is TextBox) && cnt.Name.Substring(0, 2) != "dd")
strfields += "'" + cnt.Text + "',";
else if (cnt is ComboBox)
{
ComboBox cbocnt = (ComboBox)cnt;
strfields += "'" + cbocnt.SelectedValue + "',";
}
else if (cnt is CheckBox)
{
CheckBox chkcnt = (CheckBox)cnt;
strfields += "'" + chkcnt.Checked + "',";
}
else if ((cnt is NumericUpDown) && cnt.Name.Substring(0, 2) != "dd")
{
NumericUpDown Numericcnt = (NumericUpDown)cnt;
strfields += Numericcnt.Value + ",";
}
else if (cnt is DateTimePicker)
{
DateTimePicker dtpcnt = (DateTimePicker)cnt;
//If Datepicker is set to show month then month as int
if (cnt.Tag.ToString() == "Month")
strfields += dtpcnt.Value.Month + ",";
//If Datepicker is set to show Year then Year as int
else if (cnt.Tag.ToString() == "Year")
strfields += dtpcnt.Value.Year + ",";
else
//For Normal Date
strfields += "'" + String.Format("{0:MM/dd/yy}", DateTime.ParseExact(dtpcnt.Text, "dd/MM/yy", null)) + "',";
}
}
}
}
//Update Single for form
private void UpdateSingleRecord(Control.ControlCollection Controls, String TableName,string strPkFieldName,int intpkfieldValue)
{
string strUpdateQuery = string.Empty;
//string strValues = "Values (";
strfields = string.Empty;
GetUpdateFieldsValues(Controls);
strUpdateQuery = " Update " + TableName;
strUpdateQuery += " set " + strfields;
strUpdateQuery = strUpdateQuery.Substring(0, strUpdateQuery.Length - 1);
strUpdateQuery += " Where " + strPkFieldName + "=" + intpkfieldValue;
DataLayer clsDataLayer = new DataLayer();
clsDataLayer.ExecuteSql(strUpdateQuery);
}
private void UpdateSingleRecord(Control.ControlCollection Controls, String TableName, string[] strPkFieldName, string[,] strpkfieldValue)
{
string strUpdateQuery = string.Empty;
//string strValues = "Values (";
strfields = string.Empty;
GetUpdateFieldsValues(Controls);
strUpdateQuery = " Update " + TableName;
strUpdateQuery += " set " + strfields;
strUpdateQuery = strUpdateQuery.Substring(0, strUpdateQuery.Length - 1);
for (Int16 i = 0; i < strPkFieldName.Length; i++)
{
strUpdateQuery += i == 0 ? " Where " : " And ";
strUpdateQuery += strPkFieldName[i] + "=";
strUpdateQuery += strpkfieldValue[i, 0] != "System.Int32" ? "'" : string.Empty;
strUpdateQuery += strpkfieldValue[i, 0] == "System.DateTime" ? String.Format("{0:MM/dd/yy}", DateTime.ParseExact(strpkfieldValue[i, 1], "dd/MM/yy", null)) : strpkfieldValue[i, 1];
strUpdateQuery += strpkfieldValue[i, 0] != "System.Int32" ? "'" : string.Empty;
//
}
DataLayer clsDataLayer = new DataLayer();
clsDataLayer.ExecuteSql(strUpdateQuery);
}
private void GetUpdateFieldsValues(Control.ControlCollection Controls)
{
foreach (Control cnt in Controls)
{
if (cnt.Controls.Count > 0 && cnt.Name != "grdMaster")
GetUpdateFieldsValues(cnt.Controls);
if (cnt.Name != string.Empty)
{
if ((cnt is TextBox) && cnt.Name.Substring(0, 2) != "dd")
strfields += cnt.Name.Substring(5) + "='" + cnt.Text + "',";
else if ((cnt is ComboBox) && cnt.Name.Substring(0, 2) != "dd")
{
ComboBox cbocnt = (ComboBox)cnt;
strfields += cnt.Name.Substring(5) + "='" + cbocnt.SelectedValue + "',";
}
else if (cnt is CheckBox)
{
CheckBox chkcnt = (CheckBox)cnt;
strfields += cnt.Name.Substring(5) + "='" + chkcnt.Checked + "',";
}
else if ((cnt is NumericUpDown) && cnt.Name.Substring(0, 2) != "dd")
{
NumericUpDown NumCnt = (NumericUpDown)cnt;
strfields += cnt.Name.Substring(5) + "=" + NumCnt.Value + ",";
}
else if ((cnt is DateTimePicker)&& cnt.Name.Substring(0, 2) != "dd")
{
DateTimePicker dtpcnt = (DateTimePicker)cnt;
strfields += cnt.Name.Substring(5) + "="; //'" + cnt.Text + "',";
strfields += "'" + String.Format("{0:MM/dd/yy}", DateTime.ParseExact(cnt.Text, "dd/MM/yy", null)) + "',";
}
}
}
}
public void PopulateDetailGrid(string[] PKFieldName, string[,] pkFieldValue, DataGridView grdDetail, string DetailspName)
{
DataLayer clsDataLayer = new DataLayer();
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandType = CommandType.StoredProcedure;
for (Int16 i = 0; i < PKFieldName.Length; i++)
{
sqlcmd.Parameters.Add(PKFieldName[i], pkFieldValue[i, 1]);
}
sqlcmd.CommandText = DetailspName;
DataSet dst= clsDataLayer.ExecuteCommand(sqlcmd);
int intRow;
grdDetail.Rows.Clear();
foreach (DataRow dtRow in dst.Tables[0].Rows)
{
intRow= grdDetail.Rows.Add();
foreach (DataGridViewColumn Col in grdDetail.Columns)
{
foreach (DataColumn dtCol in dst.Tables[0].Columns)
{
if (Col.Name == dtCol.ColumnName)
{
if (grdDetail.Rows[intRow].Cells[Col.Name] is DataGridViewComboBoxCell)
{
DataGridViewComboBoxCell grdCombo = (DataGridViewComboBoxCell)grdDetail.Rows[intRow].Cells[Col.Name];
grdCombo.Value = Convert.ToInt32(dtRow[Col.Name].ToString());
}
else
grdDetail.Rows[intRow].Cells[Col.Name].Value = dtRow[Col.Name].ToString();
}
}
}
//grdDetail.Rows.Insert(
//grdDetail.Rows.Add(1);
}
}
