« previous next »
Pages: [1]

Topic: The Database display revisted  (Read 1479 times)

0 Members and 1 Guest are viewing this topic.

Offline toasty0

  • Application.Quit();
  • Captain
  • *
  • Posts: 8045
  • Gender: Male
The Database display revisted
« on: March 13, 2005, 11:41:34 pm »
Stephen,

You'll remember that we displayed a collection/database of book titles in a datagrid control and edited the display narrowing it down to sub-catagories using button controls. Our code looked something like the following:

Code: [Select]
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here

string DbPath = Server.MapPath("bin/BookTest.mdb");
//string ConnStr;
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr);
string SQL= "SELECT * FROM Books ORDER BY Title";
OleDbCommand Commandobj = new OleDbCommand( SQL,MyOleDbConn);
MyOleDbConn.Open();
dgTestIt.DataSource= Commandobj.ExecuteReader();
dgTestIt.DataBind();
MyOleDbConn.Close();
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);

//Set the properties of dgTestIt datagrid control
dgTestIt.AlternatingItemStyle.BackColor=System.Drawing.Color.LightBlue;
dgTestIt.ItemStyle.BackColor=System.Drawing.Color.LightGreen;
dgTestIt.AllowSorting=false;
dgTestIt.ShowFooter=true;
dgTestIt.ShowHeader=true;
dgTestIt.Width=800;
dgTestIt.BackColor=System.Drawing.Color.LightGray;
dgTestIt.BorderColor=System.Drawing.Color.Green;
dgTestIt.BorderStyle=BorderStyle.Solid;
dgTestIt.BorderWidth=3;
dgTestIt.Font.Size=8;

btnSQL.BackColor=System.Drawing.Color.LightGreen;
btnSQL.BorderColor=System.Drawing.Color.ForestGreen;
btnSQL.BorderStyle=BorderStyle.Ridge;

btnAll.BackColor=System.Drawing.Color.LightGreen;
btnAll.BorderColor=System.Drawing.Color.ForestGreen;
btnAll.BorderStyle=BorderStyle.Ridge;

btnASP.BackColor=System.Drawing.Color.LightGreen;
btnASP.BorderColor=System.Drawing.Color.ForestGreen;
btnASP.BorderStyle=BorderStyle.Ridge;

btnCSharp.BackColor=System.Drawing.Color.LightGreen;
btnCSharp.BorderColor=System.Drawing.Color.ForestGreen;
btnCSharp.BorderStyle=BorderStyle.Ridge;

btnVB.BackColor=System.Drawing.Color.LightGreen;
btnVB.BorderColor=System.Drawing.Color.ForestGreen;
btnVB.BorderStyle=BorderStyle.Ridge;

btnGameProgramming.BackColor=System.Drawing.Color.LightGreen;
btnGameProgramming.BorderColor=System.Drawing.Color.ForestGreen;
btnGameProgramming.BorderStyle=BorderStyle.Ridge;

btnDirectX.BackColor=System.Drawing.Color.LightGreen;
btnDirectX.BorderColor=System.Drawing.Color.ForestGreen;
btnDirectX.BorderStyle=BorderStyle.Ridge;

btnCplusplus.BackColor=System.Drawing.Color.LightGreen;
btnCplusplus.BorderColor=System.Drawing.Color.ForestGreen;
btnCplusplus.BorderStyle=BorderStyle.Ridge;

lblLibrary.BackColor=System.Drawing.Color.LightGreen;
lblLibrary.BorderColor=System.Drawing.Color.ForestGreen;
lblLibrary.BorderStyle=BorderStyle.Ridge;
lblLibrary.Font.Size=12;
lblLibrary.Text="Welcome to My Programming Library. Please click one of the buttons below to choose which library you would like to view.";

}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{   
this.btnSQL.Click += new System.EventHandler(this.btnSQL_Click);
this.btnAll.Click += new System.EventHandler(this.btnAll_Click);
this.btnASP.Click += new System.EventHandler(this.btnASP_Click);
this.btnCSharp.Click += new System.EventHandler(this.btnCSharp_Click);
this.btnCplusplus.Click += new System.EventHandler(this.btnCplusplus_Click);
this.btnDirectX.Click += new System.EventHandler(this.btnDirectX_Click);
this.btnGameProgramming.Click += new System.EventHandler(this.btnGameProgramming_Click);
this.btnVB.Click += new System.EventHandler(this.btnVB_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
private void btnAll_Click(object sender, System.EventArgs e)
{
string DbPath = Server.MapPath("bin/LibBooksProg.mdb");
//string ConnStr;
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr);
string SQL= "SELECT * FROM ASPNET UNION SELECT * FROM VB UNION SELECT * FROM Cpp UNION SELECT * FROM Game UNION SELECT * FROM CSharp UNION SELECT * FROM SeQuil UNION SELECT * FROM DX ORDER BY Title ";
OleDbCommand Commandobj = new OleDbCommand( SQL,MyOleDbConn);
MyOleDbConn.Open();
dgTestIt.DataSource= Commandobj.ExecuteReader();
//dgTestIt.PageSize=8;
dgTestIt.DataBind();
MyOleDbConn.Close();
}

private void btnASP_Click(object sender, System.EventArgs e)
{
string DbPath = Server.MapPath("bin/LibBooksProg.mdb");
//string ConnStr;
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr);
string SQL= "SELECT * FROM ASPNET ORDER BY Title";
OleDbCommand Commandobj = new OleDbCommand( SQL,MyOleDbConn);
MyOleDbConn.Open();
dgTestIt.DataSource= Commandobj.ExecuteReader();
dgTestIt.DataBind();
MyOleDbConn.Close();
}

private void btnCSharp_Click(object sender, System.EventArgs e)
{
string DbPath = Server.MapPath("bin/LibBooksProg.mdb");
//string ConnStr;
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr);
string SQL= "SELECT * FROM CSharp ORDER BY Title";
OleDbCommand Commandobj = new OleDbCommand( SQL,MyOleDbConn);
MyOleDbConn.Open();
dgTestIt.DataSource= Commandobj.ExecuteReader();
dgTestIt.DataBind();
MyOleDbConn.Close();
}

private void btnCplusplus_Click(object sender, System.EventArgs e)
{
string DbPath = Server.MapPath("bin/LibBooksProg.mdb");
//string ConnStr;
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr);
string SQL= "SELECT * FROM Cpp ORDER BY Title";
OleDbCommand Commandobj = new OleDbCommand( SQL,MyOleDbConn);
MyOleDbConn.Open();
dgTestIt.DataSource= Commandobj.ExecuteReader();
dgTestIt.DataBind();
MyOleDbConn.Close();
}

private void btnDirectX_Click(object sender, System.EventArgs e)
{
string DbPath = Server.MapPath("bin/LibBooksProg.mdb");
//string ConnStr;
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr);
string SQL= "SELECT * FROM DX ORDER BY Title";
OleDbCommand Commandobj = new OleDbCommand( SQL,MyOleDbConn);
MyOleDbConn.Open();
dgTestIt.DataSource= Commandobj.ExecuteReader();
dgTestIt.DataBind();
MyOleDbConn.Close();
}

private void btnGameProgramming_Click(object sender, System.EventArgs e)
{
string DbPath = Server.MapPath("bin/LibBooksProg.mdb");
//string ConnStr;
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr);
string SQL= "SELECT * FROM Game";
OleDbCommand Commandobj = new OleDbCommand( SQL,MyOleDbConn);
MyOleDbConn.Open();
dgTestIt.DataSource= Commandobj.ExecuteReader();
dgTestIt.DataBind();
MyOleDbConn.Close();
}

private void btnVB_Click(object sender, System.EventArgs e)
{
string DbPath = Server.MapPath("bin/LibBooksProg.mdb");
//string ConnStr;
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr);
string SQL= "SELECT * FROM VB ORDER BY Title";
OleDbCommand Commandobj = new OleDbCommand( SQL,MyOleDbConn);
MyOleDbConn.Open();
dgTestIt.DataSource= Commandobj.ExecuteReader();
dgTestIt.DataBind();
MyOleDbConn.Close();
}

private void btnSQL_Click(object sender, System.EventArgs e)
{
string DbPath = Server.MapPath("bin/LibBooksProg.mdb");
//string ConnStr;
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr);
string SQL= "SELECT * FROM SeQuil ORDER BY Title";
OleDbCommand Commandobj = new OleDbCommand( SQL,MyOleDbConn);
MyOleDbConn.Open();
dgTestIt.DataSource= Commandobj.ExecuteReader();
dgTestIt.DataBind();
MyOleDbConn.Close();
}
Effective but messay as hell to maintian and a real nightmare for someone else to read and understand quickly. In other words, this code is uglier than a 90 year old whore's butt in winter.

In my next post I'll show how we cleaned up this mess and reduced the number of lines of code needed by approx. 60%.

Logged
MCTS: SQL Server 2005 | MCP: Windows Server 2003 | MCTS: Microsoft Certified Technology Specialist | MCT: Microsoft Certified Trainer | MOS: Microsoft Office Specialist 2003 | VSP: VMware Sales Professional | MCTS: Vista

Offline toasty0

  • Application.Quit();
  • Captain
  • *
  • Posts: 8045
  • Gender: Male
Re: The Database display revisted
« Reply #1 on: March 13, 2005, 11:45:27 pm »
In this instance we used a DropDownList control to replace all the button control. Not only does the code run faster but it is easier to follow and manain.

Give it whirl, Stephen. I think you'll find this control very powerful and versatile.

Code: [Select]
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);

//Set the properties of dgTestIt datagrid control
dgTestIt.AlternatingItemStyle.BackColor=System.Drawing.Color.LightBlue;
dgTestIt.ItemStyle.BackColor=System.Drawing.Color.LightGreen;
dgTestIt.AllowSorting=false;
dgTestIt.ShowFooter=true;
dgTestIt.ShowHeader=true;
dgTestIt.Width=800;
dgTestIt.BackColor=System.Drawing.Color.LightGray;
dgTestIt.BorderColor=System.Drawing.Color.Green;
dgTestIt.BorderStyle=BorderStyle.Solid;
dgTestIt.BorderWidth=3;

lblLibrary.BackColor=System.Drawing.Color.LightGreen;
lblLibrary.BorderColor=System.Drawing.Color.ForestGreen;
lblLibrary.BorderStyle=BorderStyle.Ridge;
lblLibrary.Font.Size=12;
lblLibrary.Text="Welcome to My Programming Library. Please make a selection from the drop down list below to view a library of your choice.";

ddlLibrarySelection.BackColor=System.Drawing.Color.LightGreen;
ddlLibrarySelection.BorderColor=System.Drawing.Color.ForestGreen;
ddlLibrarySelection.BorderStyle=BorderStyle.Solid;
ddlLibrarySelection.BorderWidth=1;
ddlLibrarySelection.Font.Size=12;

}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnHome_library.Click += new System.EventHandler(this.btnHome_Click);
this.btnCoding_library.Click += new System.EventHandler(this.btnCoding_library_Click);
this.btnProfiler.Click += new System.EventHandler(this.btnProfiler_Click);
this.btnProgLibrary.Click += new System.EventHandler(this.btnProgLibrary_Click);
this.btnDotNetGroup.Click += new System.EventHandler(this.btnDotNetGroup_Click);
this.btnEmail.Click += new System.EventHandler(this.btnEmail_Click);
this.ddlLibrarySelection.SelectedIndexChanged += new System.EventHandler(this.ddlLibrarySelection_SelectedIndexChanged);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

private void ddlLibrarySelection_SelectedIndexChanged(object sender, System.EventArgs e)
{
if(IsPostBack)
{
string SQL="";
switch(ddlLibrarySelection.SelectedItem.Text)
{
case"The Whole Library":
SQL=ddlLibrarySelection.SelectedValue.ToString();
break;
case"ASP.NET":
SQL=ddlLibrarySelection.SelectedValue.ToString();
break;
case"C#":
SQL=ddlLibrarySelection.SelectedValue.ToString();
break;
case"C++":
SQL=ddlLibrarySelection.SelectedValue.ToString();
break;
case"DirectX":
SQL=ddlLibrarySelection.SelectedValue.ToString();
break;
case"Game Programming":
SQL=ddlLibrarySelection.SelectedValue.ToString();
break;
case"SQL":
SQL=ddlLibrarySelection.SelectedValue.ToString();
break;
case"Visual Basic":
SQL=ddlLibrarySelection.SelectedValue.ToString();
break;
}
string DbPath = Server.MapPath("bin/LibBooksProg.mdb");
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr);
OleDbCommand Commandobj = new OleDbCommand( SQL,MyOleDbConn);
MyOleDbConn.Open();
dgTestIt.DataSource= Commandobj.ExecuteReader();
dgTestIt.DataBind();
MyOleDbConn.Close();
}
}

http://www.toasty0.com/library.aspx

Happy Coding, Bro.

Jerry
Logged
MCTS: SQL Server 2005 | MCP: Windows Server 2003 | MCTS: Microsoft Certified Technology Specialist | MCT: Microsoft Certified Trainer | MOS: Microsoft Office Specialist 2003 | VSP: VMware Sales Professional | MCTS: Vista

Offline Sirgod

  • Whooot Master Cattle Baron
  • Global Moderator
  • Vice Admiral
  • *
  • Posts: 27844
  • Gender: Male
Re: The Database display revisted
« Reply #2 on: March 14, 2005, 07:25:33 am »
Hey thanks Jerry, that second version is alot easier to follow.

Stephen
Logged
"You cannot exaggerate about the Marines. They are convinced to the point of arrogance, that they are the most ferocious fighters on earth - and the amusing thing about it is that they are."- Father Kevin Keaney, Chaplain, Korean War
Pages: [1]
« previous next »