我有代码如下:
sSQL = "Select Table_ID,House_Name,STS From House_Table order by Table_ID";
SqlCommand TempCommand = new SqlCommand(sSQL, pTempConn); //创建获取数据集记录
SqlDataReader TempDataReader = TempCommand.ExecuteReader(); //获取数据集记录
while (TempDataReader.Read())
{
sHouseID = TempDataReader["Table_ID"].ToString().Trim();
sHouseName = TempDataReader["House_Name"].ToString().Trim();
sSts = TempDataReader["STS"].ToString().Trim();
sSQL = "Select STS From Pre_Strink Where STS='" + TempDataReader["STS"].ToString().Trim();
SqlCommand TempCommand1 = new SqlCommand(sSQL, pTempConn); //创建获取数据集记录
SqlDataReader TempDataReader1 = TempCommand1.ExecuteReader(); //获取数据集记录
TempCommand1.Dispose();
TempDataReader1.Dispose();
}
TempCommand.DisPose();
TempDataReader.DisPose();出现"已经打开相同的DataReader,要选关闭",但是以上的代码是不能关闭的,要同时打开才么,先说明这两个表是不能用Select来关联的,像上面的操作方式用得很多,如果只能用Select来关联查询的话有些东西很难做到的,有没有什么解决方法??????
sSQL = "Select Table_ID,House_Name,STS From House_Table order by Table_ID";
SqlCommand TempCommand = new SqlCommand(sSQL, pTempConn); //创建获取数据集记录
SqlDataReader TempDataReader = TempCommand.ExecuteReader(); //获取数据集记录
while (TempDataReader.Read())
{
sHouseID = TempDataReader["Table_ID"].ToString().Trim();
sHouseName = TempDataReader["House_Name"].ToString().Trim();
sSts = TempDataReader["STS"].ToString().Trim();
sSQL = "Select STS From Pre_Strink Where STS='" + TempDataReader["STS"].ToString().Trim();
SqlCommand TempCommand1 = new SqlCommand(sSQL, pTempConn); //创建获取数据集记录
SqlDataReader TempDataReader1 = TempCommand1.ExecuteReader(); //获取数据集记录
TempCommand1.Dispose();
TempDataReader1.Dispose();
}
TempCommand.DisPose();
TempDataReader.DisPose();出现"已经打开相同的DataReader,要选关闭",但是以上的代码是不能关闭的,要同时打开才么,先说明这两个表是不能用Select来关联的,像上面的操作方式用得很多,如果只能用Select来关联查询的话有些东西很难做到的,有没有什么解决方法??????
在DataReader没有关闭前,是不可以再使用与其连接的Connection的。
刚学的C#,没用过DataTable,能不能给点代码提示啊????
查看DataSet的帮助也可以。
private void button1_Click(object sender, System.EventArgs e)
{
string strcon;
strcon="";
if (checkBox1.Checked==true )
{
strcon=strcon+"and fbillno="+"'"+comboBox1.Text.ToString()+"'";
}
if (checkBox2.Checked==true)
{
strcon=strcon+" and fdate>='"+dateTimePicker1.Value+"'"+" and fdate<='"+dateTimePicker2.Value+"'" ;
}
if (checkBox3.Checked ==true )
{
if (comboBox5.Text.ToString()!="")
strcon=strcon+" and c.fshortnumber="+"'"+comboBox5.Text.ToString()+"'";
else
strcon=strcon+" and left(c.fshortnumber,3)="+"'"+comboBox2.Text.ToString()+"'";
}
if (checkBox1.Checked==false && checkBox2.Checked ==false && checkBox3.Checked==false )
{
MessageBox.Show("必选一组条件");
return;
}
string mConnString="server=SERVER1;database=database1;uid=sa;pwd=****";
string select="select a.fdate as 单据日期,a.fbillno as 单据编号,c.fshortnumber as 物料代码,"+
"b.FQty as 入库数量,b.fsourcebillno as RT单号,b.forderbillno as PO号 from IcStockBill "+
"a left join icstockbillentry b on a.finterid=b.finterid "+
"left join t_icitem c on b.fitemid=c.fitemid where a.ftrantype=1 and FStatus<>0 "+strcon+"";
SqlConnection conn=new SqlConnection(mConnString);
SqlDataAdapter da=new SqlDataAdapter(select,conn);
DataSet ds=new DataSet(); da.SelectCommand.CommandText=(select);
da.Fill(ds);
this.dataGrid1.DataSource=ds.Tables[0];
}
第二种把访问数据库写成类,然后建立多个连接
private void upbut_Click(object sender, System.EventArgs e)
{
connstr=new conndatabase();
connstrup=new conndatabase();
connname=new conndatabase();
conndatabase connl=new conndatabase();
//connstr.ExecuteReaderSqls Read2;
string sql;
string usql;
string message="确认更新?";
string caption="更新对话框";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result;
result = MessageBox.Show(this, message,caption,buttons); if (result == DialogResult.Yes)
{
sql="select c.fitemid,CONVERT(VARCHAR(30),c.t_fdate,111),convert(numeric(15,5),f.t_fprice) as t_fprice from " +
" (select b.fitemid,max(a.FDate) as t_fdate " +
" from POOrder a left join POOrderentry b on a.finterid=b.finterid " +
" left join t_icitem g on b.fitemid=g.fitemid " +
" where a.fstatus>0 and left(g.fnumber,3)<>'IDM'and left(g.fnumber,3)<>'NDM' and right(g.fshortnumber,2)<>'%C'" +
" group by b.fitemid ) c left join " +
" (select d.fdate,e.fitemid,max(e.fprice*d.FExchangeRate) as t_fprice from POOrder d " +
" left join POOrderentry e on d.finterid=e.finterid where d.fstatus>0 " +
" group by d.fdate,e.fitemid) f " +
" on c.fitemid=f.fitemid and c.t_fdate=f.fdate";
SqlDataReader mRead=connstr.ExecuteReaderSqls(sql);
int countnum=0;
while (mRead.Read())
{
string t_fitemid=mRead[0].ToString();
string t_date=mRead[1].ToString() ;
string t_fprice=mRead[2].ToString();
usql="update t_icitem set fstandardcost="+t_fprice+",f_110='"+t_date+"' where fitemid="+t_fitemid+"";
label3.Text=t_fitemid.ToString()+" "+t_fprice.ToString() ;
connstrup.ExecuteSql(usql);
countnum++;
}
sql="select b.fitemid,d.fauxprice,d.fdate "+
" from POOrder a left join POOrderentry b on a.finterid=b.finterid " +
"left join t_icitem c on b.fitemid=c.fitemid left join " +
"(select e.fitemidname,e.fdate,max(f.fauxprice) as fauxprice from(select FITEMIDNAME,max(fdate) as fdate from vwICBill_1 group by fitemidname) e left join " +
" vwICBill_1 f on e.fitemidname=f.fitemidname and e.fdate=f.fdate group by e.fitemidname,e.fdate ) d " +
" on c.fshortnumber=d.FITEMIDNAME " +
" where b.fprice=0 and a.fstatus=3 ";
SqlDataReader lRead=connname.ExecuteReaderSqls(sql);
while (lRead.Read() )
{
string t_fname=lRead[0].ToString();
string t_price=lRead[1].ToString();
string t_fdate=lRead[2].ToString();
usql="update t_icitem set fstandardcost="+t_price+",f_110='"+t_fdate+"' where fitemid="+t_fname+"";
connl.ExecuteSql(usql);
} string countnumstr="共更新完"+countnum.ToString()+"笔";
MessageBox.Show(countnumstr);
// mRead.Close();
}
类如下:
using System;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms; namespace updatepostandcost
{
/// <summary>
/// conndatabase 的摘要说明。
/// </summary>
public class conndatabase
{
private SqlConnection Connstr;
private SqlDataAdapter ConnAdapter;
private SqlCommand Strcommand;
string mConnString;
public conndatabase()
{
//
// TODO: 在此处添加构造函数逻辑
//
Connstr=new SqlConnection();
mConnString="server=server1;database=database1;uid=sa;pwd=*****";
Connstr.ConnectionString=mConnString;
ConnAdapter=new SqlDataAdapter();
Strcommand=new SqlCommand();
}
public void Open()
{
try
{
if(Connstr.State != ConnectionState.Open)
Connstr.Open();
}
catch(System.Exception e)
{
throw new System.Exception("不能打开" + e.Message);
}
} public bool ExecuteSql(string SQL)
{
try
{
if(Connstr.State!=ConnectionState.Open)
{
Connstr.Open();
}
Strcommand = Connstr.CreateCommand();
Strcommand.CommandText =SQL;
int i=Strcommand.ExecuteNonQuery();
Connstr.Close();
return true;
}
catch(System.Exception)
{
if(Connstr.State!=ConnectionState.Closed)
Connstr.Close();
return false;
}
} public SqlDataReader ExecuteReaderSqls(string SQL)
{
try
{
if (Connstr.State !=ConnectionState.Open)
Connstr.Open();
SqlCommand sqlExectueCommand = new SqlCommand();
sqlExectueCommand.CommandText =SQL;
sqlExectueCommand.Connection = Connstr;
SqlDataReader myDataReader = sqlExectueCommand.ExecuteReader();
return myDataReader;
}
catch(System.Exception e)
{
if(Connstr.State!=ConnectionState.Closed)
Connstr.Close();
throw new SystemException("不能打开"+ e.Message);
}
}
}
}
请在 SqlCommand TempCommand1 = new SqlCommand(sSQL, pTempConn); //创建获取数据集记录 的pTempConn 换成个新的Conn对象就可以了,因为pTempConn已经一直被占用着呢......
http://blog.csdn.net/knight94/archive/2006/04/15/664530.aspx
conn.Open();
string sql = "select * from atable; select * from btable";
OleDbCommand comm = new OleDbCommand(sql, conn);
OleDbDataAdapter ada = new OleDbDataAdapter(comm);
DataSet ds = new DataSet();
ada.Fill(ds);
this.dataGrid1.DataSource = ds;