public string strcon = "Integrated Security=SSPI;Initial Catalog='db123';Data Source='Ghost-1383119';User ID='sa';Password='sa';Connect Timeout=0";
try
{
SqlConnection conn = new SqlConnection(strcon);
conn.Open();
strSQL = "select id,title,keyword from web where id<120 and id>98 order by id asc";
SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
da.SelectCommand.CommandTimeout = 60000; da.Fill(ds, "web");
conn.Close();
n = ds.Tables["web"].Rows.Count;
foreach (DataRow dr in ds.Tables["web"].Rows)
{
titleid = dr["id"].ToString();
if (titleid != null)
{
titleid = titleid.ToString();
}
long id = long.Parse(inputid);
string strNextSQL = "select top 1 id,title,time from web where keyword='" + keyword + "' and id>" + id + " order by id asc";
SqlConnection con = new SqlConnection(strcon);
con.Open();
SqlDataAdapter datar = new SqlDataAdapter(strPrevSQL, con);
DataSet ds = new DataSet();
datar.Fill(ds);
previd = ds.Tables[0].Rows[1]["id"].ToString();
prevtitle = ds.Tables[0].Rows[1]["title"].ToString();
ds = null;
con.Close();SqlConnection mycon = new SqlConnection(strcon);
mycon.Open(); long id = long.Parse(inputid);
string strSQL = "select * from web where id=" + id;
SqlDataReader dr = null;
SqlCommand mycommand = new SqlCommand(strSQL, mycon);
dr = mycommand.ExecuteReader();
while (dr.Read())
{
titleid = dr["id"].ToString();
title = dr["title"].ToString();
}
dr.Close();
mycommand.CommandText = "update web set show_path=1 where id=" + long.Parse(inputid);
mycommand.ExecuteNonQuery();
mycon.Close(); }
追踪了一下,结果逻辑读取这么大。
Audit Logout cpu:0 reads:29116676 writes 32730 duration 13
……
Audit Logout cpu:0 reads:29116671 writes 32730 duration 16
谁帮我看一下那写的不合理,麻烦给个合理的代码最好。
程序在xp系统sql2005开发版中就正常,在win2003系统sql2005企业版就出上面的问题
try
{
SqlConnection conn = new SqlConnection(strcon);
conn.Open();
strSQL = "select id,title,keyword from web where id<120 and id>98 order by id asc";
SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
da.SelectCommand.CommandTimeout = 60000; da.Fill(ds, "web");
conn.Close();
n = ds.Tables["web"].Rows.Count;
foreach (DataRow dr in ds.Tables["web"].Rows)
{
titleid = dr["id"].ToString();
if (titleid != null)
{
titleid = titleid.ToString();
}
long id = long.Parse(inputid);
string strNextSQL = "select top 1 id,title,time from web where keyword='" + keyword + "' and id>" + id + " order by id asc";
SqlConnection con = new SqlConnection(strcon);
con.Open();
SqlDataAdapter datar = new SqlDataAdapter(strPrevSQL, con);
DataSet ds = new DataSet();
datar.Fill(ds);
previd = ds.Tables[0].Rows[1]["id"].ToString();
prevtitle = ds.Tables[0].Rows[1]["title"].ToString();
ds = null;
con.Close();SqlConnection mycon = new SqlConnection(strcon);
mycon.Open(); long id = long.Parse(inputid);
string strSQL = "select * from web where id=" + id;
SqlDataReader dr = null;
SqlCommand mycommand = new SqlCommand(strSQL, mycon);
dr = mycommand.ExecuteReader();
while (dr.Read())
{
titleid = dr["id"].ToString();
title = dr["title"].ToString();
}
dr.Close();
mycommand.CommandText = "update web set show_path=1 where id=" + long.Parse(inputid);
mycommand.ExecuteNonQuery();
mycon.Close(); }
追踪了一下,结果逻辑读取这么大。
Audit Logout cpu:0 reads:29116676 writes 32730 duration 13
……
Audit Logout cpu:0 reads:29116671 writes 32730 duration 16
谁帮我看一下那写的不合理,麻烦给个合理的代码最好。
程序在xp系统sql2005开发版中就正常,在win2003系统sql2005企业版就出上面的问题
连接字符串,改成这样
public string strcon = @"Integrated Security=SSPI;Initial Catalog=db123;Data Source=Ghost-1383119;User ID=sa;Password=sa;Connect Timeout=0";
2、sql语句改成
strSQL = "select [id],title,keyword from web where (id<120 and id>98) order by id";//默认asc
后面的sql语句也一样
3、
string strSQL = "select * from web where id=" + id;把字段写全
4、你看看你一个函数进行多少个操作数据库,打开又关闭,关闭又打开。
这应该是主要原因
2、sqlconnection中的超时时间太短,如果是远程连接的话,可能会出问题。
写一个方法,传递一个参数sql语句
protected DataSet QueryData(string sqlstring)
{
public string strcon = "Integrated Security=SSPI;Initial Catalog='db123';Data Source='Ghost-1383119';User ID='sa';Password='sa';Connect Timeout=0";//当然这个最好放在webconfig或者App.config
DataSet ds= new DataSet();
try
{
SqlConnection conn = new SqlConnection(strcon);
conn.Open();
strSQL = "select id,title,keyword from web where id<120 and id>98 order by id asc";
SqlDataAdapter da = new SqlDataAdapter(sqlstring, conn);
da.SelectCommand.CommandTimeout = 60000;
da.Fill(ds, "web");
conn.Close();
return ds;
}
我不知道你要实现什么需求,非得操作数据库这么多次