努力编写SQL查询以更新数据库 [英] Struggling with writing SQL query to update database

问题描述

大家好，

我是编程的新手.我应该设置一个每天更新的sql数据库.我设法编写了C#中的后端代码.我的桌子如下所示

Hi All,

I am a complete novice at programming. I am supposed to setup a sql database which is updated on a daily basis. I have managed to write the back end code which is in C#. My table looks like following

ID   Date             Value         Cum  
168  1/1/2011          0.2          0.2
290  1/1/2011          0.1          0.3 
300  1/1/2011          0.5          0.5
168  1/2/2011          0.1
290  1/2/2011          0.5
450  1/2/2011          0.9

我从网络上获取ID，日期和值的值并将其插入数据库中.我从jan的1月1日开始维护数据库.因此Jan1的所有值的总和与它们的值相同，但从2月份开始，则ID匹配的任何地方都应该是累加的值.(例如168,1/2/2011的Cum应该为0.2 + 0.1 = 0.3)，如果不匹配，则还应在value列中插入数字Cum列.(对于450,1/2/2011，Cum = 0.9).

任何人都可以建议该进程和sql查询来执行此操作吗?我正在附上代码片段.

I get the values of ID,date and value from the web and these are inserted into the database.I am maintaining the database from the 1st of jan.So the cum of all values of Jan1 are the same as their values but from the 2nd, wherever the IDs match the cumulative should be the added values.(Eg for 168,1/2/2011 Cum should 0.2+0.1 =0.3) and if they don''t match the number in the value column should also be inserted into the Cum column.(For 450,1/2/2011,Cum = 0.9).

Can a anyone suggest the process and sql query to do this? I am attaching a snippet of the code.

string YYYY = startDate.Year.ToString();
      string MM = startDate.Month.ToString();
      string DD = startDate.Day.ToString();
      if (MM.Length == 1) { MM = "0" + MM; }
      if (DD.Length == 1) { DD = "0" + DD; }
      File.Copy(@"C:\WebFireModel\weatherData\nws_precip_1day_observed_" + YYYY + "" + MM + "" + DD + ".dbf", @"C:\WebFireModel\weatherData\" + YYYY + "" + MM + "" + DD + ".dbf");

      String ConnectionString;
      ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\WebFireModel\\weatherData;Extended Properties=dBase IV";
      System.Data.OleDb.OleDbConnection dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
      dBaseConnection.Open();

      System.Data.OleDb.OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand(" SELECT * FROM C:\\WebFireModel\\weatherData\\" + YYYY + "" + MM + "" + DD + ".dbf as US,C:\\WebFireModel\\weatherData\\se_hrap.dbf as se where US.Id = se.Id ", dBaseConnection);
      //System.Data.OleDb.OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand(" SELECT * FROM C:\\WebFireModel\\weatherData\\" + YYYY + "" + MM + "" + DD + ".dbf", dBaseConnection);
      DataGrid DataGrid1 = new DataGrid();
      OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

      // Pass the Select command to the adapter.
      objAdapter1.SelectCommand = dBaseCommand;

      DataSet objDataset1 = new DataSet();

      // Fill the DataSet with the information from the worksheet.
      objAdapter1.Fill(objDataset1, "XLData");

      // Bind data to DataGrid control.
      DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
      DataGrid1.DataBind();


      dBaseConnection.Close();

      string your_connectionstring = ConfigurationManager.ConnectionStrings["MyDbConn1"].ToString();
      //double cumm = 0;
      foreach (DataGridItem dgi in DataGrid1.Items)
      {
          int Id = Convert.ToInt32(dgi.Cells[0].Text.Trim());
          int Hrapx = Convert.ToInt32(dgi.Cells[1].Text.Trim());
          int Hrapy = Convert.ToInt32(dgi.Cells[2].Text.Trim());
          double Lat = Convert.ToDouble(dgi.Cells[3].Text.Trim());
          double Lon = Convert.ToDouble(dgi.Cells[4].Text.Trim());
          double pre = Convert.ToDouble(dgi.Cells[5].Text.Trim());

          SqlConnection conn = new SqlConnection(your_connectionstring);
          conn.Open();
          //SqlCommand cumulative = new SqlCommand(" select sum(value) from precipitation where id =''" + Id + "''", conn);
          //SqlDataReader cumm = cumulative.ExecuteReader();
          //cumm = pre + cumm;



          //SqlCommand comm = new SqlCommand("insert into dbo.Location(Id,hrapx,hrapy,lat,long) values (" + Id + "," + Hrapx + "," + Hrapy + "," + Lat + "," + Lon + " ) ", conn);
          //comm.ExecuteNonQuery();

          SqlCommand comm1 = new SqlCommand("insert into dbo.Precipitation(Id,Value,date) values (" + Id + "," + pre + ", " + "''" + MM + "/" + DD + "/" + YYYY + "''"+")", conn);
          comm1.ExecuteNonQuery();

推荐答案

SqlCommand cumulative = new SqlCommand(" select sum(value) from precipitation where id ='" + Id + "'", conn);
SqlDataReader cumm = cumulative.ExecuteReader(SingleResult);
double cum=0.0
while(cumm.CanRead())
   cum = cumm.GetDouble(0);

if(cum != 0.0)
   cum+= newValue
else
   cum = newValue

//this will give u the cumulative value. Now you also need to change uour insert query to insert cum in database.As per now its just inserting three values.

这篇关于努力编写SQL查询以更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！