Setting Up Update
-
Hi, I need to send changes to database in web service sitting on another machine with DB. I was thinking of using the following code in order to do the update:
SqlConnection myCon = new SqlConnection(sCon1); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = @"INSERT INTO MidnightReadings " + "(MR_Date, Coal_Feeder_Tot, Coal_Silo_Level_Prct, Coal_Barn_Inv_Prct, Limestone_Feed_Total, LS_Silo_Level_Prct, Ammonia_Totalizer, " + "Prop_to_Burn_Totalizer, TwentyFourHr_SO2_Reduct, Limestone_Received, Ammonia_Received, Prop_Received, Est_hours_Dry_Oper_Hours, " + "Est_hours_Dry_Oper_Mins, LAB_Sulfur_Content_Prct, LAB_High_Heat_Val, CEMS_SO2_Daily_Avg, SO2_Daily, Raw_Water_Pump_Meter, " + "Reservoir_Level, Comments, Date_Rec_Added) " + "VALUES(@MR_Date, @Coal_Feeder_Tot, @Coal_Silo_Level_Prct, @Coal_Barn_Inv_Prct, @Limestone_Feed_Total, @LS_Silo_Level_Prct, " + "@Ammonia_Totalizer, @Prop_to_Burn_Totalizer, @TwentyFourHr_SO2_Reduct, @Limestone_Received, @Ammonia_Received, @Prop_Received, " + "@Est_hours_Dry_Oper_Hours, @Est_hours_Dry_Oper_Mins, @LAB_Sulfur_Content_Prct, @LAB_High_Heat_Val, @CEMS_SO2_Daily_Avg, " + "@SO2_Daily, @Raw_Water_Pump_Meter, @Reservoir_Level, @Comments, @Date_Rec_Added); " + " SELECT MR_ID, MR_Date, Coal_Feeder_Tot, Coal_Silo_Level_Prct, Coal_Barn_Inv_Prct, Limestone_Feed_Total, LS_Silo_Level_Prct, " + " Ammonia_Totalizer, Prop_to_Burn_Totalizer, TwentyFourHr_SO2_Reduct, Limestone_Received, Ammonia_Received, Prop_Received, " + " Est_hours_Dry_Oper_Hours, Est_hours_Dry_Oper_Mins, LAB_Sulfur_Content_Prct, LAB_High_Heat_Val, CEMS_SO2_Daily_Avg, " + " SO2_Daily, Raw_Water_Pump_Meter, Reservoir_Level, Comments, Date_Rec_Added FROM MidnightReadings WHERE (MR_ID = @@IDENTITY)"; SqlCommandBuilder cb = new SqlCommandBuilder(da); da.Update(dsDiff,"MidnightReadings"); myCon.Close();Does the code look correct for building my update? Heath had said this is the most efficient way in order to update db. As you can see my insert statement is big. Is this common to have such a big sql statement or is there a short version I should be using? Thanks, JJ -
Hi, I need to send changes to database in web service sitting on another machine with DB. I was thinking of using the following code in order to do the update:
SqlConnection myCon = new SqlConnection(sCon1); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = @"INSERT INTO MidnightReadings " + "(MR_Date, Coal_Feeder_Tot, Coal_Silo_Level_Prct, Coal_Barn_Inv_Prct, Limestone_Feed_Total, LS_Silo_Level_Prct, Ammonia_Totalizer, " + "Prop_to_Burn_Totalizer, TwentyFourHr_SO2_Reduct, Limestone_Received, Ammonia_Received, Prop_Received, Est_hours_Dry_Oper_Hours, " + "Est_hours_Dry_Oper_Mins, LAB_Sulfur_Content_Prct, LAB_High_Heat_Val, CEMS_SO2_Daily_Avg, SO2_Daily, Raw_Water_Pump_Meter, " + "Reservoir_Level, Comments, Date_Rec_Added) " + "VALUES(@MR_Date, @Coal_Feeder_Tot, @Coal_Silo_Level_Prct, @Coal_Barn_Inv_Prct, @Limestone_Feed_Total, @LS_Silo_Level_Prct, " + "@Ammonia_Totalizer, @Prop_to_Burn_Totalizer, @TwentyFourHr_SO2_Reduct, @Limestone_Received, @Ammonia_Received, @Prop_Received, " + "@Est_hours_Dry_Oper_Hours, @Est_hours_Dry_Oper_Mins, @LAB_Sulfur_Content_Prct, @LAB_High_Heat_Val, @CEMS_SO2_Daily_Avg, " + "@SO2_Daily, @Raw_Water_Pump_Meter, @Reservoir_Level, @Comments, @Date_Rec_Added); " + " SELECT MR_ID, MR_Date, Coal_Feeder_Tot, Coal_Silo_Level_Prct, Coal_Barn_Inv_Prct, Limestone_Feed_Total, LS_Silo_Level_Prct, " + " Ammonia_Totalizer, Prop_to_Burn_Totalizer, TwentyFourHr_SO2_Reduct, Limestone_Received, Ammonia_Received, Prop_Received, " + " Est_hours_Dry_Oper_Hours, Est_hours_Dry_Oper_Mins, LAB_Sulfur_Content_Prct, LAB_High_Heat_Val, CEMS_SO2_Daily_Avg, " + " SO2_Daily, Raw_Water_Pump_Meter, Reservoir_Level, Comments, Date_Rec_Added FROM MidnightReadings WHERE (MR_ID = @@IDENTITY)"; SqlCommandBuilder cb = new SqlCommandBuilder(da); da.Update(dsDiff,"MidnightReadings"); myCon.Close();Does the code look correct for building my update? Heath had said this is the most efficient way in order to update db. As you can see my insert statement is big. Is this common to have such a big sql statement or is there a short version I should be using? Thanks, JJAs far as the length of the command is concerned, the more information you get into one command is better, since you don't have to reopen the sql connection, and execute multiple commands after each successive command is executed. So, its perfectly fine to do so.
-
Hi, I need to send changes to database in web service sitting on another machine with DB. I was thinking of using the following code in order to do the update:
SqlConnection myCon = new SqlConnection(sCon1); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = @"INSERT INTO MidnightReadings " + "(MR_Date, Coal_Feeder_Tot, Coal_Silo_Level_Prct, Coal_Barn_Inv_Prct, Limestone_Feed_Total, LS_Silo_Level_Prct, Ammonia_Totalizer, " + "Prop_to_Burn_Totalizer, TwentyFourHr_SO2_Reduct, Limestone_Received, Ammonia_Received, Prop_Received, Est_hours_Dry_Oper_Hours, " + "Est_hours_Dry_Oper_Mins, LAB_Sulfur_Content_Prct, LAB_High_Heat_Val, CEMS_SO2_Daily_Avg, SO2_Daily, Raw_Water_Pump_Meter, " + "Reservoir_Level, Comments, Date_Rec_Added) " + "VALUES(@MR_Date, @Coal_Feeder_Tot, @Coal_Silo_Level_Prct, @Coal_Barn_Inv_Prct, @Limestone_Feed_Total, @LS_Silo_Level_Prct, " + "@Ammonia_Totalizer, @Prop_to_Burn_Totalizer, @TwentyFourHr_SO2_Reduct, @Limestone_Received, @Ammonia_Received, @Prop_Received, " + "@Est_hours_Dry_Oper_Hours, @Est_hours_Dry_Oper_Mins, @LAB_Sulfur_Content_Prct, @LAB_High_Heat_Val, @CEMS_SO2_Daily_Avg, " + "@SO2_Daily, @Raw_Water_Pump_Meter, @Reservoir_Level, @Comments, @Date_Rec_Added); " + " SELECT MR_ID, MR_Date, Coal_Feeder_Tot, Coal_Silo_Level_Prct, Coal_Barn_Inv_Prct, Limestone_Feed_Total, LS_Silo_Level_Prct, " + " Ammonia_Totalizer, Prop_to_Burn_Totalizer, TwentyFourHr_SO2_Reduct, Limestone_Received, Ammonia_Received, Prop_Received, " + " Est_hours_Dry_Oper_Hours, Est_hours_Dry_Oper_Mins, LAB_Sulfur_Content_Prct, LAB_High_Heat_Val, CEMS_SO2_Daily_Avg, " + " SO2_Daily, Raw_Water_Pump_Meter, Reservoir_Level, Comments, Date_Rec_Added FROM MidnightReadings WHERE (MR_ID = @@IDENTITY)"; SqlCommandBuilder cb = new SqlCommandBuilder(da); da.Update(dsDiff,"MidnightReadings"); myCon.Close();Does the code look correct for building my update? Heath had said this is the most efficient way in order to update db. As you can see my insert statement is big. Is this common to have such a big sql statement or is there a short version I should be using? Thanks, JJ -
Hi, With the way I have this setup do I also have to add parameters to the DataAdapter for each of the values? JJ