using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
namespace LZserverList
{
public partial class LZList : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection("server=.;database=ShifanRoadLZ0818;uid=sa;pwd=123456;");
//处罚表查询
string sqlcf = @"select DJB_CF.CaseDistrict, DJB_CF.CaseYear,DJB_CF.CaseMonth, DJB_CF.CaseMoney,DJB_CF.TreeType from DJB_CF";
//赔补偿表查询
string sqlpbc = @"select DJB_PBC.CaseDistrict,DJB_PBC.CaseYear,DJB_PBC.CaseMonth, DJB_PBC.CaseMoney,DJB_PBC.TreeType from DJB_PBC";
//强制表查询
string sqlqz = @"select DJB_QZ.CaseDistrict,DJB_QZ.CaseYear,DJB_QZ.CaseMonth, DJB_QZ.CaseMoney,DJB_QZ.TreeType from DJB_QZ";
//许可表查询
string sqlxk = @"select DJB_XK.CaseDistrict as cd,DJB_XK.CaseYear as cy,DJB_XK.CaseMonth as cm, DJB_XK.CaseMoney as cmy,DJB_XK.XKType as ct from DJB_XK";
protected void Page_Load(object sender, EventArgs e)
{
Bind();
}
//绑定repeater
public void Bind()
{
//string Month = "";
//string Year = "";
retdf.DataSource = DataTablelist();
retdf.DataBind();
}
//处罚表
public DataTable tablecf()
{
SqlDataAdapter sda = new SqlDataAdapter(sqlcf, conn);
DataTable dt = new DataTable();
sda.Fill(dt);
dt.Dispose();
return dt;
}
//赔补偿表
public DataTable tablepbc()
{
SqlDataAdapter sda = new SqlDataAdapter(sqlpbc, conn);
DataTable dt = new DataTable();
sda.Fill(dt);
dt.Dispose();
return dt;
}
//强制表
public DataTable tableqz()
{
SqlDataAdapter sda = new SqlDataAdapter(sqlqz, conn);
DataTable dt = new DataTable();
sda.Fill(dt);
dt.Dispose();
return dt;
}
//许可表
public DataTable tablexk()
{
SqlDataAdapter sda = new SqlDataAdapter(sqlxk, conn);
DataTable dt = new DataTable();
sda.Fill(dt);
dt.Dispose();
return dt;
}
//三个相同的表集合在一个表中
public DataTable table3list()
{
DataTable cf = (DataTable)tablecf();
DataTable newDataTable = cf.Clone();
object[] obj = new object[newDataTable.Columns.Count];
//添加处罚表的数据
for (int i = 0; i < cf.Rows.Count; i++)
{
cf.Rows[i].ItemArray.CopyTo(obj, 0);
newDataTable.Rows.Add(obj);
}
//添加赔补偿表的数据
DataTable pbc = (DataTable)tablepbc();
for (int i = 0; i < pbc.Rows.Count; i++)
{
pbc.Rows[i].ItemArray.CopyTo(obj, 0);
newDataTable.Rows.Add(obj);
}
//添加强制表的数据
DataTable qz = (DataTable)tableqz();
for (int i = 0; i < qz.Rows.Count; i++)
{
qz.Rows[i].ItemArray.CopyTo(obj, 0);
newDataTable.Rows.Add(obj);
}
return newDataTable;
}
/// <summary>
/// 将两个列不同(结构不同)的DataTable合并成一个新的DataTable
/// </summary>
/// <param name="DataTable1">表1</param>
/// <param name="DataTable2">表2</param>
/// <param name="DTName">合并后新的表名</param>
/// <returns>合并后的新表</returns>
public DataTable DataTablelist()
{
string DTName = "";
DataTable list3 = (DataTable)table3list();
DataTable xk = (DataTable)tablexk();
//克隆DataTable1的结构
DataTable newDataTable = list3.Clone();
for (int i = 0; i < xk.Columns.Count; i++)
{
//再向新表中加入DataTable2的列结构
newDataTable.Columns.Add(xk.Columns[i].ColumnName);
}
object[] obj = new object[newDataTable.Columns.Count];
//添加DataTable1的数据
for (int i = 0; i < list3.Rows.Count; i++)
{
list3.Rows[i].ItemArray.CopyTo(obj, 0);
newDataTable.Rows.Add(obj);
}
if (list3.Rows.Count >= xk.Rows.Count)
{
for (int i = 0; i < xk.Rows.Count; i++)
{
for (int j = 0; j < xk.Columns.Count; j++)
{
newDataTable.Rows[i][j + list3.Columns.Count] = xk.Rows[i][j].ToString();
}
}
}
else
{
DataRow dr3;
//向新表中添加多出的几行
for (int i = 0; i < xk.Rows.Count - list3.Rows.Count; i++)
{
dr3 = newDataTable.NewRow();
newDataTable.Rows.Add(dr3);
}
for (int i = 0; i < xk.Rows.Count; i++)
{
for (int j = 0; j < xk.Columns.Count; j++)
{
newDataTable.Rows[i][j + list3.Columns.Count] = xk.Rows[i][j].ToString();
}
}
}
newDataTable.TableName = DTName; //设置DT的名字
return newDataTable;
}
}
}