So how many times have you heard this question ” Can you help me? ” I was asked recently to help on a project that is to be turned over to the client by the end of October. My tasking was to take this page and make it cleaner, add an email process and add dynamic headers to the gridview which is populated by an uploaded EXCEL Spreadsheet – seems simply enough right. Well this is part of what I started with – mind you this code is already LIVE, we are turning it over so another contractor can maintain it:
The first thing I notice is the number of private methods do the same thing, with one minor change – the name of the stored procedure being called:
private void insertFundingWhereProjectExists()
{
conn = new SqlConnection(ConfigurationManager.AppSettings[“DB”].ToString());
command = new SqlCommand(“dbo.sp_insert_funding_where_project_exists”, conn);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
private void insertFinancialImport()
{
conn = new SqlConnection(ConfigurationManager.AppSettings[“DB”].ToString());
command = new SqlCommand(“dbo.insertFinancialImport”, conn);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
private void updateProjectTopicNoField()
{
conn = new SqlConnection(ConfigurationManager.AppSettings[“DB”].ToString());
command = new SqlCommand(“dbo.updateProjectTopicNoField”, conn);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
private void import_financial_obligated_funds()
{
conn = new SqlConnection(ConfigurationManager.AppSettings[“DB”].ToString());
command = new SqlCommand(“dbo.import_financial_obligated_funds”, conn);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
private void import_financial_planned_funds()
{
conn = new SqlConnection(ConfigurationManager.AppSettings[“DB”].ToString());
command = new SqlCommand(“dbo.import_financial_planned_funds”, conn);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
private void truncateFinancialImportData()
{
conn = new SqlConnection(ConfigurationManager.AppSettings[“DB”].ToString());
command = new SqlCommand(“dbo.sp_truncateFinancialImportData”, conn);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
Now I am asked to improve the import process itself. They would like the import to work and not be dependent on the EXCEL Headers…this is what I started with:
private void insertXLSData()
{ DataSet ds = (DataSet)Session[“ds”];
conn = new SqlConnection(ConfigurationManager.AppSettings[“DB”].ToString());
command = new SqlCommand(“sp_insertFinancialImportData”, conn);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlParameter TopicNo = command.Parameters.Add(“@TopicNo”,SqlDbType.VarChar);
SqlParameter Topic_Title = command.Parameters.Add(“@Topic_Title”, SqlDbType.VarChar);
SqlParameter TPOC_Code = command.Parameters.Add(“@TPOC_Code”, SqlDbType.VarChar);
SqlParameter Primary_Sponsor = command.Parameters.Add(“@Primary_Sponsor”, SqlDbType.VarChar);
SqlParameter Seconardary_Sponsor = command.Parameters.Add(“@Seconardary_Sponsor”, SqlDbType.VarChar);
SqlParameter Endorsing_PMA = command.Parameters.Add(“@Endorsing_PMA”, SqlDbType.VarChar);
SqlParameter TPOC_Name = command.Parameters.Add(“@TPOC_Name”, SqlDbType.VarChar);
SqlParameter TPOC_Phone = command.Parameters.Add(“@TPOC_Phone”, SqlDbType.VarChar);
SqlParameter TPOC_Email = command.Parameters.Add(“@TPOC_Email”, SqlDbType.VarChar);
SqlParameter FY08_Obligated = command.Parameters.Add(“@FY08_Obligated”, SqlDbType.VarChar);
SqlParameter FY08_Planned = command.Parameters.Add(“@FY08_Planned”, SqlDbType.VarChar);
SqlParameter FY09_Obligated = command.Parameters.Add(“@FY09_Obligated”, SqlDbType.VarChar);
SqlParameter FY09_Planned = command.Parameters.Add(“@FY09_Planned”, SqlDbType.VarChar);
SqlParameter FY10_Planned = command.Parameters.Add(“@FY10_Planned”, SqlDbType.VarChar);
SqlParameter FY11_Planned = command.Parameters.Add(“@FY11_Planned”, SqlDbType.VarChar);
//int i = 0;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
TopicNo.Value = ds.Tables[0].Rows[i][“TopicNo”].ToString();
Topic_Title.Value = ds.Tables[0].Rows[i][“Topic Title”].ToString();
TPOC_Code.Value = ds.Tables[0].Rows[i][“TPOC Code”].ToString();
Primary_Sponsor.Value = ds.Tables[0].Rows[i][“Primary Sponsor”].ToString();
Seconardary_Sponsor.Value = ds.Tables[0].Rows[i][“Seconardary Sponsor”].ToString();
Endorsing_PMA.Value = ds.Tables[0].Rows[i][“Endorsing PMA”].ToString();
TPOC_Name.Value = ds.Tables[0].Rows[i][“TPOC Name”].ToString();
TPOC_Phone.Value = ds.Tables[0].Rows[i][“TPOC Phone”].ToString();
TPOC_Email.Value = ds.Tables[0].Rows[i][“TPOC Email”].ToString();
FY08_Obligated.Value = ds.Tables[0].Rows[i][“FY08 Obligated”].ToString();
FY08_Planned.Value = ds.Tables[0].Rows[i][“FY08 Planned”].ToString();
FY09_Obligated.Value = ds.Tables[0].Rows[i][“FY09 Obligated”].ToString();
FY09_Planned.Value = ds.Tables[0].Rows[i][“FY09 Planned”].ToString();
FY10_Planned.Value = ds.Tables[0].Rows[i][“FY10 Planned”].ToString();
FY11_Planned.Value = ds.Tables[0].Rows[i][“FY11 Planned”].ToString();
command.ExecuteNonQuery();
}
conn.Close();
}
If you are releasing code like this, please stop – find a local user group in your area. Take advantage of the free training available at Code Camps and the videos on ASP.NET
For clarification, the individual who wrote this has only been working in .NET for about 3 year. I know this person and like him/her, s/he was rushed when putting this together, then removed him/her before s/he could refactor. My purpose is not to embarrass this individual and I hope that if s/he reads this, s/he understands that I am writing this for educational purposes.