System.IndexOutOfRangeException: Cannot find table 0.5
本文关键字：table find Cannot IndexOutOfRangeException System | 更新日期: 2023-09-27 18:10:06
我正在asmx中制作一个webmethod，其中我从不同的表中获取数据…并填充数据集中的行。但我怎么能填补我的数据集，因为我从不同的表获得数据??请帮助我:
[WebMethod] public DataSet getblancesheet(string baranchcode, string fromdate, string todate)
{
// List<datalist4> data = new List<datalist4>();
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataTable dt3 = new DataTable();
DataTable dt4 = new DataTable();
DataTable finaldata = new DataTable();
Webservice.databaseops dbo = new Webservice.databaseops();
DataSet ds = new DataSet("Ledger");
SqlDataAdapter adp = new SqlDataAdapter();
//adp.Fill(ds);
bool sheet1, sheet2, sheet3;
sheet1 = sheet2 = sheet3 = true;
decimal sheet1bal, sheet2bal, sheet3bal;
sheet1bal = sheet2bal = sheet3bal = 0;
dt1 = dbo.getdata("SELECT Bcode,Bdesc FROM Gl_bSheet1 where Compcode='" + baranchcode + "'");
if (dt1.Rows.Count > 0)
{
foreach (DataRow dr1 in dt1.Rows)
{
// sheet1
dt2 = dbo.getdata("SELECT Bncode,Bndesc FROM Gl_bSheet2 where Compcode='" + baranchcode + "' and Bcode='" + dr1[0] + "'");
if (dt2.Rows.Count > 0)
{
foreach (DataRow dr2 in dt2.Rows)
{
//sheet2
dt3 = dbo.getdata("SELECT Bnicode,Bnidesc FROM Gl_bSheet3 where Compcode='" + baranchcode + "' and Bcode='" + dr1[0] + "' and Bncode='" + dr2[0] + "'");
if (dt3.Rows.Count > 0)
{
foreach (DataRow dr3 in dt3.Rows)
{
//sheet3
dt4 = dbo.getdata("SELECT gpls.Accountno,gd.Acct_Desc FROM Gl_bSheetdetail gpls,GL_Detail gd where gpls.Compcode='" + baranchcode + "' and gpls.Bcode='" + dr1[0] + "' and gpls.Bncode='" + dr2[0] + "' and gpls.Bnicode='" + dr3[0] + "' and gpls.Accountno=gd.AccountNo");
if (dt4.Rows.Count > 0)
{
foreach (DataRow dr4 in dt4.Rows)
{
//detialsheet
finaldata = dbo.getdata("SELECT SUM(Dr_Amount)-SUM(Cr_Amount) from gl_transaction where Accountno='" + dr4[0] + "' and Value_Date between '" + fromdate + "' and '" + todate + "'");
if (finaldata.Rows[0].ItemArray[0].ToString().Length > 0)
{
if (sheet1)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DataRow dd = ds.Tables[0].NewRow();
dd[0] = dr1[1].ToString();
dd[1] = "sheet1";
dd[2] = "";
dd[3] = "";
ds.Tables[0].Rows.Add(dd);
sheet1 = false;
// data.Add(new datalist4(dr1[1].ToString(), "sheet1", "", ""));
// sheet1 = false;
}
}
if (sheet2)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DataRow dd = ds.Tables[0].NewRow();
dd[0] = dr2[1].ToString();
dd[1] = "sheet2";
dd[2] = "";
dd[3] = "";
ds.Tables[0].Rows.Add(dd);
sheet2 = false;
//data.Add(new datalist4(dr2[1].ToString(), "sheet2", "", ""));
//sheet2 = false;
}
}
if (sheet3)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DataRow dd = ds.Tables[0].NewRow();
dd[0] = dr3[1].ToString();
dd[1] = "sheet3";
dd[2] = "";
dd[3] = "";
ds.Tables[0].Rows.Add(dd);
//data.Add(new datalist4(dr3[1].ToString(), "sheet3", "", ""));
//sheet3 = false;
sheet3 = false;
}
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
DataRow dd = ds.Tables[0].NewRow();
dd[0] = "-";
dd[1] = dr4[0].ToString().Trim();
dd[2] = dr4[1].ToString().Trim();
dd[3] = dbo.valueparser(decimal.Parse(finaldata.Rows[0].ItemArray[0].ToString()), false);
ds.Tables[0].Rows.Add(dd);
}
//data.Add(new datalist4("-", dr4[0].ToString().Trim(), dr4[1].ToString().Trim(), dbo.valueparser(decimal.Parse(finaldata.Rows[0].ItemArray[0].ToString()), false)));
sheet1bal = sheet1bal + decimal.Parse(finaldata.Rows[0].ItemArray[0].ToString());
sheet2bal = sheet2bal + decimal.Parse(finaldata.Rows[0].ItemArray[0].ToString());
sheet3bal = sheet2bal + decimal.Parse(finaldata.Rows[0].ItemArray[0].ToString());
}
}
}
if (!sheet3)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DataRow dd = ds.Tables[0].NewRow();
dd[0] = "sheet3end";
dd[1] = "SubTotal";
dd[2] = dr3[1].ToString();
dd[3] = dbo.valueparser(sheet3bal, false);
ds.Tables[0].Rows.Add(dd);
//data.Add(new datalist4("sheet3end", "Subtotal:", dr3[1].ToString(), dbo.valueparser(sheet3bal, false)));
sheet3 = true;
sheet3bal = 0;
}
}
}
}
if (!sheet2)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DataRow dd = ds.Tables[0].NewRow();
dd[0] = "sheet2end";
dd[1] = "SubTotal:";
dd[2] = dr2[1].ToString();
dd[3] = dbo.valueparser(sheet2bal, false);
ds.Tables[0].Rows.Add(dd);
// data.Add(new datalist4("sheet2end", "Subtotal:", dr2[1].ToString(), dbo.valueparser(sheet2bal, false)));
sheet2 = true;
sheet2bal = 0;
}
}
}
}
if (!sheet1)
foreach (DataRow dr in ds.Tables[0].Rows)
{
DataRow dd = ds.Tables[0].NewRow();
dd[0] = "sheet1end";
dd[1] = "SubTotal:";
dd[2] = dr1[1].ToString();
dd[3] = dbo.valueparser(sheet2bal, false);
ds.Tables[0].Rows.Add(dd);
//data.Add(new datalist4("sheet1end", "Subtotal:", dr1[1].ToString(), dbo.valueparser(sheet1bal, false)));
sheet1 = true;
sheet1bal = 0;
}
}
}
return ds;
}
//do
我对你的代码做了一些重构，我想我已经为你找到了一个合理的解决方案。尝试这样做来获取数据:
var data =
from DataRow dr1 in dbo.getdata("SELECT Bcode,Bdesc FROM Gl_bSheet1 where Compcode='" + baranchcode + "'").Rows
let Bcode = (string)dr1[0]
select new
{
Bcode,
Bdesc = (string)dr1[1],
sheet2s =
from DataRow dr2 in dbo.getdata("SELECT Bncode,Bndesc FROM Gl_bSheet2 where Compcode='" + baranchcode + "' and Bcode='" + Bcode + "'").Rows
let Bncode = (string)dr2[0]
select new
{
Bncode,
Bndesc = (string)dr2[1],
sheet3s =
from DataRow dr3 in dbo.getdata("SELECT Bnicode,Bnidesc FROM Gl_bSheet3 where Compcode='" + baranchcode + "' and Bcode='" + Bcode + "' and Bncode='" + Bncode + "'").Rows
let Bnicode = (string)dr3[0]
select new
{
Bnicode,
Bnidesc = (string)dr3[1],
dataSheets =
from DataRow dr4 in dbo.getdata("SELECT gpls.Accountno,gd.Acct_Desc FROM Gl_bSheetdetail gpls,GL_Detail gd where gpls.Compcode='" + baranchcode + "' and gpls.Bcode='" + Bcode + "' and gpls.Bncode='" + Bncode + "' and gpls.Bnicode='" + Bnicode + "' and gpls.Accountno=gd.AccountNo").Rows
let Accountno = (string)dr4[0]
let finaldata = (string)(dbo.getdata("SELECT SUM(Dr_Amount)-SUM(Cr_Amount) from gl_transaction where Accountno='" + Accountno + "' and Value_Date between '" + fromdate + "' and '" + todate + "'").Rows[0].ItemArray[0])
where !String.IsNullOrEmpty(finaldata)
select new
{
Accountno,
Acct_Desc = (string)dr4[1],
value = decimal.Parse(finaldata)
}
}
}
};
然后用以下代码生成行:
foreach (var s1 in data)
{
var dd = ds.Tables[0].NewRow();
dd[0] = s1.Bdesc;
dd[1] = "sheet1";
dd[2] = "";
dd[3] = "";
ds.Tables[0].Rows.Add(dd);
foreach (var s2 in s1.sheet2s)
{
//Add Sheet2 Opening Row Here
foreach (var s3 in s2.sheet3s)
{
//Add Sheet3 Opening Row Here
//Add Data Row Here
var s3balance = s3.dataSheets
.Select(z => z.value)
.Sum();
//Add Sheet3 Closing Row Here
}
var s2balance = s2.sheet3s
.SelectMany(y => y.dataSheets)
.Select(z => z.value)
.Sum();
//Add Sheet2 Closing Row Here
}
var s1balance = s1.sheet2s
.SelectMany(x => x.sheet3s)
.SelectMany(y => y.dataSheets)
.Select(z => z.value)
.Sum();
//Add Sheet1 Closing Row Here
}
让查询远离写数据的代码通常是个好主意。