replaced http://codereview.stackexchange.com/ with https://codereview.stackexchange.com/
Now I already have some concerns about the whole conditional thing in the TableAdapters class which I address here. Other than that are there other areas of improvement that you could point out to me?
Now I already have some concerns about the whole conditional thing in the TableAdapters class which I address here. Other than that are there other areas of improvement that you could point out to me?
private void QueryButton_Click(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnectionGetSqlConnection();
string value = this.ValueTextBox.Text;
TableAdapters.FindScript dataSource = new TableAdapters.FindScript();
// SERVER NAMES CENSORED IN THIS POST FOR SECURITY
switch (this.ServerComboBoxFindByComboBox.SelectedItem.ToString())
{
case "SERVER1""Find Scripts By Target VDN":
connectionthis.ConnectionStringFindScriptsDataGrid.DataSource = ConfigurationManagerdataSource.ConnectionStrings["csS1"]FillByTargetVDN(connection, value).ConnectionString;Tables[0];
break;
case "SERVER2""Find Scripts By Skill Group":
connectionthis.ConnectionStringFindScriptsDataGrid.DataSource = ConfigurationManagerdataSource.ConnectionStrings["csS2"]FillBySkillGroup(connection, value).ConnectionString;Tables[0];
break;
}
switch (this.FindByComboBox.SelectedItem.ToString())
{
case "Find Scripts By TargetTranslation VDN"Route Pool":
this.FindScriptsDataGrid.DataSource = dataSource.FillByTargetVDNFillByTranslationRoutePool(connection, value).Tables[0];
break;
case "Find Scripts By Skill Group"Name":
this.FindScriptsDataGrid.DataSource = dataSource.FillBySkillGroupFillByName(connection, value).Tables[0];
break;
case "Find Scripts By Translation Route Pool"Label":
this.FindScriptsDataGrid.DataSource = dataSource.FillByTranslationRoutePoolFillByLabel(connection, value).Tables[0];
break;
}
case "Find Scripts By Name":foreach (DataGridViewRow row in this.FindScriptsDataGrid.Rows)
{
thisrow.FindScriptsDataGridHeaderCell.DataSourceValue = dataSource.FillByName(connection,row.Index value+ 1).Tables[0];ToString();
}
}
// SERVER NAMES CENSORED IN THIS POST FOR break;SECURITY
private SqlConnection GetSqlConnection()
{
SqlConnection connection case= "Findnew ScriptsSqlConnection();
By Label" switch (this.ServerComboBox.SelectedItem.ToString())
{
case "SERVER1":
this.FindScriptsDataGridconnection.DataSourceConnectionString = dataSourceConfigurationManager.FillByLabel(connection, value)ConnectionStrings["csS01"].Tables[0];ConnectionString;
break;
}
case "SERVER2":
foreach (DataGridViewRow row in this connection.FindScriptsDataGridConnectionString = ConfigurationManager.Rows)ConnectionStrings["csS02"].ConnectionString;
{
row.HeaderCell.Valuebreak;
= (row.Index + 1).ToString();}
}return connection;
}
private void QueryButton_Click(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection();
string value = this.ValueTextBox.Text;
TableAdapters.FindScript dataSource = new TableAdapters.FindScript();
// SERVER NAMES CENSORED IN THIS POST FOR SECURITY
switch (this.ServerComboBox.SelectedItem.ToString())
{
case "SERVER1":
connection.ConnectionString = ConfigurationManager.ConnectionStrings["csS1"].ConnectionString;
break;
case "SERVER2":
connection.ConnectionString = ConfigurationManager.ConnectionStrings["csS2"].ConnectionString;
break;
}
switch (this.FindByComboBox.SelectedItem.ToString())
{
case "Find Scripts By Target VDN":
this.FindScriptsDataGrid.DataSource = dataSource.FillByTargetVDN(connection, value).Tables[0];
break;
case "Find Scripts By Skill Group":
this.FindScriptsDataGrid.DataSource = dataSource.FillBySkillGroup(connection, value).Tables[0];
break;
case "Find Scripts By Translation Route Pool":
this.FindScriptsDataGrid.DataSource = dataSource.FillByTranslationRoutePool(connection, value).Tables[0];
break;
case "Find Scripts By Name":
this.FindScriptsDataGrid.DataSource = dataSource.FillByName(connection, value).Tables[0];
break;
case "Find Scripts By Label":
this.FindScriptsDataGrid.DataSource = dataSource.FillByLabel(connection, value).Tables[0];
break;
}
foreach (DataGridViewRow row in this.FindScriptsDataGrid.Rows)
{
row.HeaderCell.Value = (row.Index + 1).ToString();
}
}
private void QueryButton_Click(object sender, EventArgs e)
{
SqlConnection connection = GetSqlConnection();
string value = this.ValueTextBox.Text;
TableAdapters.FindScript dataSource = new TableAdapters.FindScript();
switch (this.FindByComboBox.SelectedItem.ToString())
{
case "Find Scripts By Target VDN":
this.FindScriptsDataGrid.DataSource = dataSource.FillByTargetVDN(connection, value).Tables[0];
break;
case "Find Scripts By Skill Group":
this.FindScriptsDataGrid.DataSource = dataSource.FillBySkillGroup(connection, value).Tables[0];
break;
case "Find Scripts By Translation Route Pool":
this.FindScriptsDataGrid.DataSource = dataSource.FillByTranslationRoutePool(connection, value).Tables[0];
break;
case "Find Scripts By Name":
this.FindScriptsDataGrid.DataSource = dataSource.FillByName(connection, value).Tables[0];
break;
case "Find Scripts By Label":
this.FindScriptsDataGrid.DataSource = dataSource.FillByLabel(connection, value).Tables[0];
break;
}
foreach (DataGridViewRow row in this.FindScriptsDataGrid.Rows)
{
row.HeaderCell.Value = (row.Index + 1).ToString();
}
}
// SERVER NAMES CENSORED IN THIS POST FOR SECURITY
private SqlConnection GetSqlConnection()
{
SqlConnection connection = new SqlConnection();
switch (this.ServerComboBox.SelectedItem.ToString())
{
case "SERVER1":
connection.ConnectionString = ConfigurationManager.ConnectionStrings["csS01"].ConnectionString;
break;
case "SERVER2":
connection.ConnectionString = ConfigurationManager.ConnectionStrings["csS02"].ConnectionString;
break;
}
return connection;
}
Populating DataGrids
I'm building an in house query tool. The main form has a drop down which allows the user to select how they want to find scripts, and a drop down for what server they want to run the query on. There is also a text box to allow the user to populate a specific value which can be left blank. I think I did pretty well here. This is my first desktop WinForms app.
What Happens When The Query Is Run
private void QueryButton_Click(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection();
string value = this.ValueTextBox.Text;
TableAdapters.FindScript dataSource = new TableAdapters.FindScript();
// SERVER NAMES CENSORED IN THIS POST FOR SECURITY
switch (this.ServerComboBox.SelectedItem.ToString())
{
case "SERVER1":
connection.ConnectionString = ConfigurationManager.ConnectionStrings["csS1"].ConnectionString;
break;
case "SERVER2":
connection.ConnectionString = ConfigurationManager.ConnectionStrings["csS2"].ConnectionString;
break;
}
switch (this.FindByComboBox.SelectedItem.ToString())
{
case "Find Scripts By Target VDN":
this.FindScriptsDataGrid.DataSource = dataSource.FillByTargetVDN(connection, value).Tables[0];
break;
case "Find Scripts By Skill Group":
this.FindScriptsDataGrid.DataSource = dataSource.FillBySkillGroup(connection, value).Tables[0];
break;
case "Find Scripts By Translation Route Pool":
this.FindScriptsDataGrid.DataSource = dataSource.FillByTranslationRoutePool(connection, value).Tables[0];
break;
case "Find Scripts By Name":
this.FindScriptsDataGrid.DataSource = dataSource.FillByName(connection, value).Tables[0];
break;
case "Find Scripts By Label":
this.FindScriptsDataGrid.DataSource = dataSource.FillByLabel(connection, value).Tables[0];
break;
}
foreach (DataGridViewRow row in this.FindScriptsDataGrid.Rows)
{
row.HeaderCell.Value = (row.Index + 1).ToString();
}
}
The Queries Being Called
class TableAdapters
{
public class FindScript
{
string conditional = string.Empty;
string query = string.Empty;
DataSet Fill(SqlConnection connection, string query)
{
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataSet dataSet = new DataSet();
dataSet.Tables.Add(new DataTable());
adapter.Fill(dataSet.Tables[0]);
return dataSet;
}
public DataSet FillByTargetVDN(SqlConnection connection, string parameter)
{
if (parameter.Length > 0)
{
conditional = "AND svce.PeripheralNumber= " + parameter + " ";
}
query = "SELECT svce.PeripheralNumber AS Service, "
+ "ms.EnterpriseName AS [Script Name], "
+ "p.EnterpriseName AS Peripheral, "
+ "sg.PeripheralNumber AS [Skill Mapping], "
+ "s.Version AS [Latest Version], "
+ "s.DateTime AS Created, "
+ "s.Author "
+ "FROM Script_Cross_Reference scr "
+ "LEFT OUTER JOIN Script s ON s.ScriptID = scr.ScriptID "
+ "LEFT OUTER JOIN Master_Script ms ON s.MasterScriptID = ms.MasterScriptID "
+ "LEFT OUTER JOIN Service svce ON svce.SkillTargetID = scr.ForeignKey "
+ "LEFT OUTER JOIN Peripheral p ON svce.PeripheralID = p.PeripheralID "
+ "LEFT OUTER JOIN Service_Member sm ON svce.SkillTargetID = sm.ServiceSkillTargetID "
+ "LEFT OUTER JOIN Skill_Group sg ON sg.SkillTargetID = sm.SkillGroupSkillTargetID "
+ "WHERE s.Version = ms.CurrentVersion AND scr.TargetType = 1 "
+ conditional
+ "ORDER BY svce.PeripheralNumber, ms.EnterpriseName, p.EnterpriseName";
return Fill(connection, query);
}
public DataSet FillBySkillGroup(SqlConnection connection, string parameter)
{
if (parameter.Length > 0)
{
conditional = "AND sg.PeripheralNumber = " + parameter + " ";
}
query = "SELECT DISTINCT(sg.PeripheralNumber) AS Skill, "
+ "ms.EnterpriseName AS [Script Name], "
+ "s.Version AS [Latest Version], "
+ "s.DateTime AS Created, "
+ "s.Author "
+ "FROM Script_Cross_Reference scr "
+ "LEFT OUTER JOIN Script s ON s.ScriptID = scr.ScriptID "
+ "LEFT OUTER JOIN Master_Script ms ON s.MasterScriptID = ms.MasterScriptID "
+ "LEFT OUTER JOIN Skill_Group sg ON sg.SkillTargetID = scr.ForeignKey "
+ "WHERE s.Version = ms.CurrentVersion AND scr.TargetType = 2 "
+ conditional
+ "ORDER BY sg.PeripheralNumber, ms.EnterpriseName";
return Fill(connection, query);
}
public DataSet FillByTranslationRoutePool(SqlConnection connection, string parameter)
{
if (parameter.Length > 0)
{
conditional = "AND (tr.EnterpriseName like ('" + parameter + "') "
+ "OR tr.EnterpriseName like ('" + parameter.ToUpper() + "') "
+ "OR tr.EnterpriseName like ('" + parameter.ToLower() + "')) ";
}
query = "SELECT DISTINCT(tr.EnterpriseName) AS Translation_Route, "
+ "ms.EnterpriseName AS [Script Name], "
+ "s.Version AS [Latest Version], "
+ "s.DateTime AS Created, "
+ "s.Author "
+ "FROM Script_Cross_Reference scr "
+ "LEFT OUTER JOIN Script s ON s.ScriptID = scr.ScriptID "
+ "LEFT OUTER JOIN Master_Script ms ON s.MasterScriptID = ms.MasterScriptID "
+ "LEFT OUTER JOIN Translation_Route tr ON scr.ForeignKey = tr.SkillTargetID "
+ "WHERE s.Version = ms.CurrentVersion AND scr.TargetType = 4 "
+ conditional
+ "ORDER BY tr.EnterpriseName, ms.EnterpriseName";
return Fill(connection, query);
}
public DataSet FillByName(SqlConnection connection, string parameter)
{
if (parameter.Length > 0)
{
conditional = "AND (ms.EnterpriseName LIKE ('" + parameter + "') "
+ "OR ms.EnterpriseName LIKE ('" + parameter.ToUpper() + "') "
+ "OR ms.EnterpriseName LIKE ('" + parameter.ToLower() + "')) ";
}
query = "SELECT ms.EnterpriseName AS [Script Name], "
+ "s.Author, "
+ "s.Version, "
+ "s.Valid, "
+ "s.DateTime AS Created "
+ "FROM Script s "
+ "LEFT OUTER JOIN Master_Script ms ON s.MasterScriptID = ms.MasterScriptID "
+ "WHERE s.Version = ms.CurrentVersion "
+ conditional
+ "ORDER BY ms.EnterpriseName";
return Fill(connection, query);
}
public DataSet FillByLabel(SqlConnection connection, string parameter)
{
if (parameter.Length > 0)
{
conditional = "AND l.Label='" + parameter + "' ";
}
query = "SELECT DISTINCT l.Label AS Label, "
+ "ms.EnterpriseName AS [Script Name], "
+ "s.Version AS [Latest Version], "
+ "s.DateTime AS Created, "
+ "s.Author "
+ "FROM Script_Cross_Reference scr "
+ "LEFT OUTER JOIN Script s ON s.ScriptID = scr.ScriptID "
+ "LEFT OUTER JOIN Master_Script ms ON s.MasterScriptID = ms.MasterScriptID "
+ "LEFT OUTER JOIN Label l ON l.LabelID = scr.ForeignKey "
+ "WHERE s.Version = ms.CurrentVersion AND scr.TargetType = 19 "
+ conditional
+ "ORDER BY l.Label, ms.EnterpriseName";
return Fill(connection, query);
}
}
}
Now I already have some concerns about the whole conditional thing in the TableAdapters class which I address here. Other than that are there other areas of improvement that you could point out to me?
lang-cs