SQL Server 2005引发System.InvalidOperationException [英] System.InvalidOperationException thrown by SQL server 2005

问题描述

当我写了7000行时
存储在dssetparameter数据集中
我的连接字符串
string conString =数据源=.\\ SQLEXPRESS; AttachDbFileName = C:\ Documents and Settings \ admin \ Desktop \ testing \ testing.mdf;集成安全性= true; Connect Timeout = 1000";



if(dssetparameter！= null || dssetparameter.Tables [0] .Rows.Count！= 0)
{
foreach(dssetparameter中的DataRow行.Tables[0] .Rows)
{
selectquery + ="select""+ row [" itemID] +""as unit_param_id，""+ row [" paramvalue] +"''as param_value，" + row ["date_logged"] + "作为date_logged，""+ row [" date_logged1] +"作为OPC-date，""+ row [" Quality;] +"''作为param_quality Union ALL"+ Environment.NewLine;
}
如果(selectquery！=";)
{
selectquery = selectquery.Remove(selectquery.LastIndexOf("Union ALL"));
insertquery + =插入到" +表名+" + Environment.NewLine;
insertquery + ="unit_param_id，param_value，date_logged，OPC-date，param_quality)" + Environment.NewLine;
insertquery + =" + selectquery +";
如果(con2.State == ConnectionState.Closed)
con2.Open();
cmd2 =新的OdbcCommand(insertquery，con2);
cmd2.CommandTimeout = 120;
cmd2.ExecuteNonQuery();
如果(con2.State == ConnectionState.Open)
con2.Close();
}

使用此查询，应用程序抛出以下错误

超时已过期

[System.InvalidOperationException] = {由于先前的函数评估超时，因此禁用了函数评估.您必须继续执行才能重新启用功能评估.}

ExecuteNonQuery需要一个开放且可用的连接.连接当前状态为关闭.

在此先感谢

when i have write the 7000 row
stored in the dssetparameter dataset
my connection string
string conString ="Data Source = .\\SQLEXPRESS;AttachDbFileName = C:\Documents and Settings\admin\Desktop\testing\testing.mdf;Integrated Security =true;Connect Timeout=1000";



if (dssetparameter != null || dssetparameter.Tables[0].Rows.Count != 0)
{
foreach (DataRow row in dssetparameter.Tables[0].Rows)
{
selectquery += " select ''" + row["itemID"] + "'' as unit_param_id ,''" + row["paramvalue"] +"'' as param_value,''" + row["date_logged"] + ''" as date_logged ,''" + row["date_logged1"] + ''" as OPC-date,''" + row["Quality";] + "'' as param_quality Union ALL" + Environment.NewLine;
}
if (selectquery != "";)
{
selectquery = selectquery.Remove(selectquery.LastIndexOf("Union ALL"));
insertquery += "insert into" + tablename + " " + Environment.NewLine;
insertquery += "unit_param_id,param_value,date_logged, OPC-date,param_quality)" + Environment.NewLine;
insertquery += " " + selectquery + "";
if (con2.State == ConnectionState.Closed)
con2.Open();
cmd2 = new OdbcCommand(insertquery, con2);
cmd2.CommandTimeout = 120;
cmd2.ExecuteNonQuery();
if (con2.State == ConnectionState.Open)
con2.Close();
}

using this query the below error thrown by application

timeout expired

[System.InvalidOperationException] = {Function evaluation disabled because a previous function evaluation timed out. You must continue execution to reenable function evaluation.}

ExecuteNonQuery requires an open and available Connection. The connections current state is closed.

thanks in advance

推荐答案

您能删除所有联合并仅使用联合吗?

我认为这是由于全部并集"而引发的超时过期异常.

Can you please remove Union All and Use only Union.

I think it is throwing timeout expired exception because of Union All.

增加CommandTimeOut值.

您可以将表转换为XML格式，然后对xml进行XML sql操作以插入数据.

看下面的文章

插入XML格式的数据 [ http://www.sql-server-performance.com/2007/xml-data-2005/ [

Increase the CommandTimeOut value.

You can convert the table to XML format and operat XML sql operation on the xml to insert the data.

Look on the below article

Inserting XML formatted data[^] and http://www.sql-server-performance.com/2007/xml-data-2005/[^]

要提高查询的执行速度，
请尝试使用批量插入(Transact-SQL) [MSDN-批量插入]

您还可以尝试

To increase the execution speed of your query,
please try to use BULK INSERT (Transact-SQL)[MSDN - BULK INSERT]

you can also try How to use BULK INSERT

这篇关于SQL Server 2005引发System.InvalidOperationException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！