Yesterday I clear my first Microsoft certification which has make me MCTS in sql server 2008 BI 70-448 (Paper Code of MS), it was quite tough for me as I have studied sql server after a quite long time, previously I have work immensely in sql server 2000 where we use dts packages and there was no such Strong BI Support at that time, any how this paper is divided into three parts.
SSIS – Sql server integration services
SSAS – Sql server analysis services
SSRS – Sql server reporting services
Difficult level in above three parts I felt most was SSAS,
SSRS and SSIS is not that tough.
In SSAS there are further parts
1.) Developing Cube with dimentions and measures
2.) Data mining
3.) KPIs
In above, the most difficult I found was Data mining Extension (DMX) but thanks to Microsoft it doesn’t include much questions on datamining hardly 2 to 3 questions I saw while attempting paper, the best way to learn DMX is msdn (http://msdn.microsoft.com/en-us/library/ms132058.aspx) .
For most of the preparation I used Microsoft press book for 70-443 and CBT Nuggets, I use to go through both of them twice which has taken almost one month.
Good luck to the guys who are going to attempt this paper in near future.
Wednesday, December 15, 2010
Saturday, October 2, 2010
Automate .Net Winform Data Entry Applications Development
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);
}
}
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);
}
}
Thursday, July 8, 2010
Tips for utlizing search engines
So much information is on the web, its mind boggling. Thankfully we have searchengines to sift through them and catagorize them for us. Unfortunatly, there is still somuch info that even with these search engines, its often a painstakingly slow process(something comparable to death for a hacker) to find exactly what you're looking for.
Lets get right into it.
I use google.com as my primary search engine because it presently tops the charts as far as the sites that it indexes which means more pertinent info per search.
1. Page translation: Just because someone speaks another language doesn't mean they dont have anything useful to say. I use translation tools like the ones found at
http://babelfish.altavista.com/and
http://world.altavista.com/to translate a few key words I am searching for. Be specific and creative because these tools arent the most accurate things on the planet.
2. Directories.These days everything is about $$$. We have to deal/w SEO (search engine optimization) which seems like a good idea on paper until you do a search for toys and get 5 irrelevant sites in the first 10 results. Using a sites directory will eliminate that. You can narrow your search down easily by looking for the info in specific catagories. (PS google DOES have directories, they're at: directory.google.com)
3. Here are some tips that google refers to as "advanced"
A. "xxxx" / will look for the exact phrase. (google isnt case sensitive)B. -x / will search for something excluding a certain termC. filetype:xxx / searches for a particular file extention (exe, mp3, etc)D. -filetype:xxx / excludes a particular file extentionE. allinurl:x / term in the urlF. allintext:x / terms in the text of the pageG. allintitle:x / terms in the html title of that pageH. allinanchor:x / terms in the links
4. OR Self explanatory, one or the other... (ie: binder OR joiner)
5. Numbers in a range. Lets say you're looking for an mp3 player but only want to spend up to $90. Why swim through all the others? MP3 player $0..$90 The 2 periods will set a numeric range to search between. This also works with dates, weights, etc
6. + Ever type in a search and see something like this:"The following words are very common and were not included in your search:"Well, what if those common words are important in your search? You can force google to search through even the common terms by putting a + in front of the denied word.
7. Preferences: It amazes me when I use other peoples PCs that they dont have their google search preferences saved. When you use google as much as I do, who can afford to not have preferences? They're located on the right of the search box, and have several options, though I only find 2 applicable for myself...A. Open results in new browserB. Display 10-100 results per page. (I currently use 50 per page, but thats a resolution preference, and 5X's the default)
8. * Wildcard searches. Great when applied to a previously mentioned method. If you only know the name of a prog, or are looking for ALL of a particular file (ie. you're DLing tunes) something like *.mp3 would list every mp3.
9. Ever see this?"In order to show you the most relevant results, we have omitted some entries very similar to the X already displayed. If you like, you can repeat the search with the omitted results included." The answer is YES. yes yes yes. Did I mention yes? I meant to.
10. Search EVERYWHERE Use the engine to its fullest. If you dont find your answer in the web section, try the group section. Hell, try a whole different search engine. Dont limit yourself, because sometimes engines seem to intentionally leave results out.ex. use google, yahoo, and altavista. search the same terms... pretty close, right?
Example:Want free music? Free games? Free software? Free movies? God bless FTP! Try this search:intitle:"Index of music" "rolling stones" mp3Substitute rolling stones/w your favorite band. No? Try the song name, or another file format. Play with it. Assuming SOMEONE made an FTP and uploaded it, you'll find it.
For example....I wanted to find some Sepultura. If you never heard them before, they're a Brazilian heavy metal band that kicks ass. I started with this:intitle:"Index of music" "Sepultura" mp3 <-- nothingintitle:"Index of música" "Sepultura" mp3 <-- nothingintitle:"Index of musica" "Sepultura" mp3 <-- not good enoughintitle:"Index of music" "Sepultura" * <-- found great stuff, but not enough Sepultura
At this point it occurs to me that I may be missing something, so I try:intitle:"index of *" "sepultura" mp3 <-- BANG!(and thats without searching for spelling errors)Also try inurl:ftp
I find that * works better for me than trying to guess other peoples mis-spellings.
The same method applies for ebooks, games, movies, SW, anything that may be on an FTP site.
I hope you enjoyed this tutorial, and I saw that recently a book and an article was written on the very same topic. I havn't read them as of yet, but check em out, and get back to me if you feel I missed something important and should include anything else.
intitle:"index of" "google hacks" ebook
Lets get right into it.
I use google.com as my primary search engine because it presently tops the charts as far as the sites that it indexes which means more pertinent info per search.
1. Page translation: Just because someone speaks another language doesn't mean they dont have anything useful to say. I use translation tools like the ones found at
http://babelfish.altavista.com/and
http://world.altavista.com/to translate a few key words I am searching for. Be specific and creative because these tools arent the most accurate things on the planet.
2. Directories.These days everything is about $$$. We have to deal/w SEO (search engine optimization) which seems like a good idea on paper until you do a search for toys and get 5 irrelevant sites in the first 10 results. Using a sites directory will eliminate that. You can narrow your search down easily by looking for the info in specific catagories. (PS google DOES have directories, they're at: directory.google.com)
3. Here are some tips that google refers to as "advanced"
A. "xxxx" / will look for the exact phrase. (google isnt case sensitive)B. -x / will search for something excluding a certain termC. filetype:xxx / searches for a particular file extention (exe, mp3, etc)D. -filetype:xxx / excludes a particular file extentionE. allinurl:x / term in the urlF. allintext:x / terms in the text of the pageG. allintitle:x / terms in the html title of that pageH. allinanchor:x / terms in the links
4. OR Self explanatory, one or the other... (ie: binder OR joiner)
5. Numbers in a range. Lets say you're looking for an mp3 player but only want to spend up to $90. Why swim through all the others? MP3 player $0..$90 The 2 periods will set a numeric range to search between. This also works with dates, weights, etc
6. + Ever type in a search and see something like this:"The following words are very common and were not included in your search:"Well, what if those common words are important in your search? You can force google to search through even the common terms by putting a + in front of the denied word.
7. Preferences: It amazes me when I use other peoples PCs that they dont have their google search preferences saved. When you use google as much as I do, who can afford to not have preferences? They're located on the right of the search box, and have several options, though I only find 2 applicable for myself...A. Open results in new browserB. Display 10-100 results per page. (I currently use 50 per page, but thats a resolution preference, and 5X's the default)
8. * Wildcard searches. Great when applied to a previously mentioned method. If you only know the name of a prog, or are looking for ALL of a particular file (ie. you're DLing tunes) something like *.mp3 would list every mp3.
9. Ever see this?"In order to show you the most relevant results, we have omitted some entries very similar to the X already displayed. If you like, you can repeat the search with the omitted results included." The answer is YES. yes yes yes. Did I mention yes? I meant to.
10. Search EVERYWHERE Use the engine to its fullest. If you dont find your answer in the web section, try the group section. Hell, try a whole different search engine. Dont limit yourself, because sometimes engines seem to intentionally leave results out.ex. use google, yahoo, and altavista. search the same terms... pretty close, right?
Example:Want free music? Free games? Free software? Free movies? God bless FTP! Try this search:intitle:"Index of music" "rolling stones" mp3Substitute rolling stones/w your favorite band. No? Try the song name, or another file format. Play with it. Assuming SOMEONE made an FTP and uploaded it, you'll find it.
For example....I wanted to find some Sepultura. If you never heard them before, they're a Brazilian heavy metal band that kicks ass. I started with this:intitle:"Index of music" "Sepultura" mp3 <-- nothingintitle:"Index of música" "Sepultura" mp3 <-- nothingintitle:"Index of musica" "Sepultura" mp3 <-- not good enoughintitle:"Index of music" "Sepultura" * <-- found great stuff, but not enough Sepultura
At this point it occurs to me that I may be missing something, so I try:intitle:"index of *" "sepultura" mp3 <-- BANG!(and thats without searching for spelling errors)Also try inurl:ftp
I find that * works better for me than trying to guess other peoples mis-spellings.
The same method applies for ebooks, games, movies, SW, anything that may be on an FTP site.
I hope you enjoyed this tutorial, and I saw that recently a book and an article was written on the very same topic. I havn't read them as of yet, but check em out, and get back to me if you feel I missed something important and should include anything else.
intitle:"index of" "google hacks" ebook
Subscribe to:
Posts (Atom)