public DataTable MBR1000ReturnAdjustment(String tablename, String riid, String scheduleid, string runid) { var plainQuery = ReturnAdjustmentQB.GetQuery(tablename, ReturnAdjustmentQB.QueryType.SELECT); // //Check if runid is null or empty or white space // bool runidStatus = String.IsNullOrWhiteSpace(runid); this.CommandText = ((runid == "0" || runid.Equals("0")) || runidStatus == true) ? String.Concat(plainQuery, String.Format(" WHERE (ri_id = {0} AND schedule_id = {1}) AND run_id IS NULL", riid, scheduleid)) : String.Concat(plainQuery, String.Format(" WHERE (ri_id = {0} AND schedule_id = {1}) AND run_id = {2}", riid, scheduleid, runid)); using (SqlConnection con = new SqlConnection(ConnectionString.GetConnectionString())) { using (SqlCommand cmd = new SqlCommand(this.CommandText, con)) { cmd.CommandType = CommandType.Text; if (con.State == ConnectionState.Closed) { con.Open(); } SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); con.Close(); return(dataTable); } } }
public int SaveReturnAdjustment(GridView grid300, GridView grid1000, string tablename300, string tablename1000, string scheduleid, string runid, string analystcomment) { int rows = 0; using (TransactionScope sc = new TransactionScope(TransactionScopeOption.Required)) { using (SqlConnection con = new SqlConnection(ConnectionString.GetConnectionString())) { if (con.State == ConnectionState.Closed) { con.Open(); } String commandText1 = "INSERT INTO t_rpt_computation_rule_adjustment (schedule_id, run_id, analyst_comment) VALUES (@schedule_id, @run_id, @analyst_comment)"; using (SqlCommand cmd = new SqlCommand(commandText1, con)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@schedule_id", scheduleid); cmd.Parameters.AddWithValue("@run_id", runid); cmd.Parameters.AddWithValue("@analyst_comment", analystcomment); rows = cmd.ExecuteNonQuery(); } String commandText2 = ""; foreach (GridViewRow grdview in grid300.Rows) { //Generate the SQL query use to insert data commandText2 = ReturnAdjustmentQB.GetQuery(tablename300, ReturnAdjustmentQB.QueryType.INSERT); using (SqlCommand cmd2 = new SqlCommand(commandText2, con)) { cmd2.CommandType = CommandType.Text; //cmd2.Parameters.AddWithValue("@id", grdview.Cells[1].Text); cmd2.Parameters.AddWithValue("@schedule_id", grdview.Cells[2].Text); cmd2.Parameters.Add("@work_collection_date", SqlDbType.DateTime).SqlValue = Convert.ToDateTime(grdview.Cells[3].Text); cmd2.Parameters.AddWithValue("@ri_id", grdview.Cells[4].Text); cmd2.Parameters.AddWithValue("@ri_code", grdview.Cells[5].Text); cmd2.Parameters.AddWithValue("@ri_name", grdview.Cells[6].Text); cmd2.Parameters.AddWithValue("@item_code", grdview.Cells[7].Text); if (tablename300.Equals("dh.t_rtn_submission_mdhr300")) { cmd2.Parameters.AddWithValue("@item_description", grdview.Cells[8].Text); //cmd2.Parameters.AddWithValue("@amount_1", grdview.Cells[9].Text.Replace("₦", "").Replace(",","")); //cmd2.Parameters.AddWithValue("@amount_2", grdview.Cells[10].Text.Replace("₦", "").Replace(",", "")); cmd2.Parameters.Add("@amount_1", SqlDbType.Decimal).SqlValue = grdview.Cells[9].Text.Replace("₦", "").Replace(",", ""); cmd2.Parameters.Add("@amount_2", SqlDbType.Decimal).SqlValue = grdview.Cells[10].Text.Replace("₦", "").Replace(",", ""); cmd2.Parameters.AddWithValue("@submission_by", grdview.Cells[11].Text); cmd2.Parameters.Add("@submission_date", SqlDbType.DateTime).SqlValue = Convert.ToDateTime(grdview.Cells[12].Text); cmd2.Parameters.AddWithValue("@adj_reason", grdview.Cells[13].Text); cmd2.Parameters.AddWithValue("@run_id", runid); //14 } else { cmd2.Parameters.AddWithValue("@item_desc", grdview.Cells[8].Text); cmd2.Parameters.Add("@amt_lcy", SqlDbType.Decimal).SqlValue = grdview.Cells[9].Text.Replace("₦", "").Replace(",", ""); cmd2.Parameters.AddWithValue("@submission_by", grdview.Cells[10].Text); cmd2.Parameters.Add("@submission_date", SqlDbType.DateTime).SqlValue = Convert.ToDateTime(grdview.Cells[11].Text); cmd2.Parameters.AddWithValue("@adj_reason", grdview.Cells[12].Text); cmd2.Parameters.AddWithValue("@run_id", runid); //13 } this.CommandText = cmd2.CommandText; rows += cmd2.ExecuteNonQuery(); } } String commandText3 = ""; foreach (GridViewRow grdview in grid1000.Rows) { commandText3 = ReturnAdjustmentQB.GetQuery(tablename1000, ReturnAdjustmentQB.QueryType.INSERT); using (SqlCommand cmd3 = new SqlCommand(commandText3, con)) { cmd3.CommandType = CommandType.Text; //cmd3.Parameters.AddWithValue("@id", grdview.Cells[1].Text); cmd3.Parameters.AddWithValue("@schedule_id", grdview.Cells[2].Text); cmd3.Parameters.Add("@work_collection_date", SqlDbType.DateTime).SqlValue = Convert.ToDateTime(grdview.Cells[3].Text); cmd3.Parameters.AddWithValue("@ri_id", grdview.Cells[4].Text); cmd3.Parameters.AddWithValue("@ri_code", grdview.Cells[5].Text); cmd3.Parameters.AddWithValue("@ri_name", grdview.Cells[6].Text); cmd3.Parameters.AddWithValue("@item_code", grdview.Cells[7].Text); if (tablename1000.Equals("cb.t_rtn_submission_mbr1000")) { cmd3.Parameters.AddWithValue("@item", grdview.Cells[8].Text); //cmd3.Parameters.AddWithValue("@latest_moth_lcy", grdview.Cells[9].Text.Replace("₦", "").Replace(",", "")); //cmd3.Parameters.AddWithValue("@year_to_dt_lcy", grdview.Cells[10].Text.Replace("₦", "").Replace(",", "")); cmd3.Parameters.Add("@latest_moth_lcy", SqlDbType.Decimal).SqlValue = grdview.Cells[9].Text.Replace("₦", "").Replace(",", ""); cmd3.Parameters.Add("@year_to_dt_lcy", SqlDbType.Decimal).SqlValue = grdview.Cells[10].Text.Replace("₦", "").Replace(",", ""); } else if (tablename1000.Equals("dh.t_rtn_submission_mdhr1000")) { cmd3.Parameters.AddWithValue("@item_desc", grdview.Cells[8].Text); cmd3.Parameters.Add("@latest_month", SqlDbType.Decimal).SqlValue = grdview.Cells[9].Text.Replace("₦", "").Replace(",", ""); cmd3.Parameters.Add("@year_to_date_ngn", SqlDbType.Decimal).SqlValue = grdview.Cells[10].Text.Replace("₦", "").Replace(",", ""); } else if (tablename1000.Equals("nib.t_rtn_submission_mnbr1000")) { cmd3.Parameters.AddWithValue("@item", grdview.Cells[8].Text); cmd3.Parameters.Add("@latest_month", SqlDbType.Decimal).SqlValue = grdview.Cells[9].Text.Replace("₦", "").Replace(",", ""); cmd3.Parameters.Add("@year_to_date_ngn", SqlDbType.Decimal).SqlValue = grdview.Cells[10].Text.Replace("₦", "").Replace(",", ""); } cmd3.Parameters.AddWithValue("@submission_by", grdview.Cells[11].Text); cmd3.Parameters.Add("@submission_date", SqlDbType.DateTime).SqlValue = Convert.ToDateTime(grdview.Cells[12].Text); cmd3.Parameters.AddWithValue("@adj_reason", grdview.Cells[13].Text); cmd3.Parameters.AddWithValue("@run_id", runid); //14 rows += cmd3.ExecuteNonQuery(); } } sc.Complete(); con.Close(); return(rows); } } }