C#做批量更新出现“当传递具有新行的 DataRow 集合时，Update 要求有效的 InsertCommand。”问

Question

using (OracleConnection Conn = new OracleConnection(connNew)) //连接数据库
{
DateTime dts = DateTime.Now;
Conn.Open();
OracleDataAdapter sd = new OracleDataAdapter();
OracleTransaction myTrans = Conn.BeginTransaction();
//sd.SelectCommand = new OracleCommand("select * from T_ZH_GRID_FORECAST where ddatetime = to_date('" + Convert.ToDateTime(newgrid[1][0].DDATETIME).ToString("yyyy-MM-dd HH:mm:ss") + "', 'yyyy-mm-dd hh24:mi:ss')", Conn);
sd.SelectCommand = new OracleCommand("select R24H,MAXTEMP,MINTEMP,R6H,T2M,SLP,WSPD10M,WDIR10M,RHSFC,RAIN,VISI,CLCT,GUST,RNPH,YBSX,DDATETIME,VENUEID,RECID from T_ZH_GRID_FORECAST where rownum<=287", Conn);
//OracleCommandBuilder sqlBulider = new OracleCommandBuilder(sd);
//sd.UpdateCommand = sqlBulider.GetUpdateCommand();
sd.AcceptChangesDuringFill = false;
sd.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataSet dataset = new DataSet();
sd.Fill(dataset, "T_ZH_GRID_FORECAST"); //加载表数据

                sd.UpdateCommand = new OracleCommand("update T_ZH_GRID_FORECAST "
                + " set R24H =@R24H,MAXTEMP =@MAXTEMP,MINTEMP =@MINTEMP,R6H=@R6H,T2M=@T2M,SLP=@SLP,WSPD10M=@WSPD10M,WDIR10M=@WDIR10M,RHSFC=@RHSFC,RAIN=@RAIN,VISI=@VISI,CLCT=@CLCT,GUST=@GUST,RNPH=@RNPH" +
                " where YBSX=@YBSX and DDATETIME=@DDATETIME and VENUEID=@VENUEID and RECID =@OLDID", Conn);
                sd.UpdateCommand.Parameters.Add("@R24H", OracleDbType.Double, 20, "R24H");
                sd.UpdateCommand.Parameters.Add("@MAXTEMP", OracleDbType.Double, 20, "MAXTEMP");
                sd.UpdateCommand.Parameters.Add("@MINTEMP", OracleDbType.Double, 20, "MINTEMP");
                sd.UpdateCommand.Parameters.Add("@R6H", OracleDbType.Double, 20, "R6H");
                sd.UpdateCommand.Parameters.Add("@T2M", OracleDbType.Double, 20, "T2M");
                sd.UpdateCommand.Parameters.Add("@SLP", OracleDbType.Double, 20, "SLP");
                sd.UpdateCommand.Parameters.Add("@WSPD10M", OracleDbType.Double, 20, "WSPD10M");
                sd.UpdateCommand.Parameters.Add("@WDIR10M", OracleDbType.Double, 20, "WDIR10M");
                sd.UpdateCommand.Parameters.Add("@RHSFC", OracleDbType.Double, 20, "RHSFC");
                sd.UpdateCommand.Parameters.Add("@RAIN", OracleDbType.Double, 20, "RAIN");
                sd.UpdateCommand.Parameters.Add("@VISI", OracleDbType.Double, 20, "VISI");
                sd.UpdateCommand.Parameters.Add("@CLCT", OracleDbType.Double, 20, "CLCT");
                sd.UpdateCommand.Parameters.Add("@GUST", OracleDbType.Double, 20, "GUST");
                sd.UpdateCommand.Parameters.Add("@RNPH", OracleDbType.Double, 20, "RNPH");

                sd.UpdateCommand.Parameters.Add("@YBSX", OracleDbType.Int32, 10, "YBSX");
                sd.UpdateCommand.Parameters.Add("@DDATETIME", OracleDbType.Date, 20, "DDATETIME");
                sd.UpdateCommand.Parameters.Add("@VENUEID", OracleDbType.Int32, 10, "VENUEID");
                sd.UpdateCommand.Parameters.Add("@RECID", OracleDbType.Int32, 20, "RECID");
                //OracleParameter parameter = sd.UpdateCommand.Parameters.Add("@OLDID", OracleDbType.Int32, 20, "RECID");
                //parameter.SourceVersion = DataRowVersion.Original;
                sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
                sd.UpdateBatchSize = 0;

                for (int count = 0; count < dataAll.Count;)
                {
                    for (int i = 0; i < 287; i++, count++)
                    {
                        dataset.Tables[0].Rows[i].BeginEdit();
                        dataset.Tables[0].Rows[i]["R24H"] = dataAll[count].R24H;
                        dataset.Tables[0].Rows[i]["MAXTEMP"] = dataAll[count].MAXTEMP;
                        dataset.Tables[0].Rows[i]["MINTEMP"] = dataAll[count].MINTEMP;
                        dataset.Tables[0].Rows[i]["R6H"] = dataAll[count].R6H;
                        dataset.Tables[0].Rows[i]["T2M"] = dataAll[count].T2M;
                        dataset.Tables[0].Rows[i]["SLP"] = dataAll[count].SLP;
                        dataset.Tables[0].Rows[i]["WSPD10M"] = dataAll[count].WSPD10M;
                        dataset.Tables[0].Rows[i]["WDIR10M"] = dataAll[count].WDIR10M;
                        dataset.Tables[0].Rows[i]["RHSFC"] = dataAll[count].RHSFC;
                        dataset.Tables[0].Rows[i]["RAIN"] = dataAll[count].RAIN;
                        dataset.Tables[0].Rows[i]["VISI"] = dataAll[count].VISI;
                        dataset.Tables[0].Rows[i]["CLCT"] = dataAll[count].CLCT;
                        dataset.Tables[0].Rows[i]["GUST"] = dataAll[count].GUST;
                        dataset.Tables[0].Rows[i]["RNPH"] = dataAll[count].RNPH;
                        dataset.Tables[0].Rows[i]["YBSX"] = dataAll[count].YBSX;
                        dataset.Tables[0].Rows[i]["DDATETIME"] = dataAll[count].DDATETIME;
                        dataset.Tables[0].Rows[i]["VENUEID"] = dataAll[count].VENUEID;
                        dataset.Tables[0].Rows[i]["RECID"] = dataAll[count].RECID;

                        dataset.Tables[0].Rows[i].EndEdit();
                    }
                    sd.Update(dataset.Tables[0]);
                    dataset.AcceptChanges();
                }
                DateTime dte = DateTime.Now;
                TimeSpan st = dte.Subtract(dts);

                dataset.Tables[0].Clear();
                sd.Dispose();
                dataset.Dispose();
                Conn.Close();

1. }

Answer 1

把更新条件拿出来查询一下，如果数据库有数据再执行更新操作，不存在就执行插入操作