C# Web控件与数据感应之填充 HtmlTable - ToB企服应用市场:ToB评测及商务社交产业平台

CREATE TABLE [dbo].[pub_ChinaPay](
[value] [char](4) NOT NULL,
[text] [nvarchar](14) NOT NULL,
[sortid] [smallint] NULL,
CONSTRAINT [PK_pub_ChinaPay] PRIMARY KEY CLUSTERED
(
[value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

复制代码

insert into pub_ChinaPay(value,text,sortid) values('****','默认状态',1)
insert into pub_ChinaPay(value,text,sortid) values(' ','待支付',2)
insert into pub_ChinaPay(value,text,sortid) values('1001','消费交易成功',3)
insert into pub_ChinaPay(value,text,sortid) values('1003','退款提交成功',4)
insert into pub_ChinaPay(value,text,sortid) values('1005','退款撤销成功',5)
insert into pub_ChinaPay(value,text,sortid) values('0003','交易失败',6)
insert into pub_ChinaPay(value,text,sortid) values('6666','微信退款申请成功',7)

复制代码

int RowsCount = 0;
int ErrorNum = 0;
string ErrorMessage = "";
public object[,] GetReaderData(string DbServerType,string strConn,string _sql,ArrayList paras,bool hastitle,CommandType ct)
{
if((DbServerType.ToLower()=="")||(DbServerType.ToLower()=="sqlserver"))
{
SqlConnection Conn = new SqlConnection(strConn );
SqlCommand Cmd = new SqlCommand();
Cmd.CommandType=ct;
Cmd.Connection = Conn;
SqlDataReader myDr;
Cmd.CommandText =_sql;
if(paras!=null)
{
for(int i=0;i<paras.Count;i++)
{
Cmd.Parameters.Add((SqlParameter)paras[i]);
}
}
try
{
Conn.Open();
myDr = Cmd.ExecuteReader();
ArrayList rowdata = new ArrayList();
int _fieldcount=myDr.FieldCount;
Object[] colvalues = new Object[_fieldcount];
for(int i=0;i<_fieldcount;i++)
{
colvalues[i]=myDr.GetName(i);
}
if(hastitle)
rowdata.Add(colvalues);
while(myDr.Read())
{
Object[] values = new Object[_fieldcount];
myDr.GetValues(values);
rowdata.Add(values);
RowsCount++;
}
myDr.Close();
object[,] rv=new object[rowdata.Count,_fieldcount];
for(int i=0;i<rowdata.Count;i++)
{
for(int j=0;j<_fieldcount;j++)
{
rv[i,j]=((object[])rowdata[i])[j];
}
}
return rv;
}
catch (SqlException e)
{
ErrorMessage=e.Message;
return null;
}
finally
{
Conn.Close();
Conn.Dispose();
}
}//sqlserver
if(DbServerType.ToLower()=="oracle")
{
OracleConnection Conn = new OracleConnection(strConn );
OracleCommand Cmd = new OracleCommand();
Cmd.Connection = Conn;
OracleDataReader myDr;
Cmd.CommandText =_sql;
if(paras!=null)
{
for(int i=0;i<paras.Count;i++)
{
Cmd.Parameters.Add((OracleParameter)paras[i]);
}
}
try
{
Conn.Open();
myDr = Cmd.ExecuteReader();
ArrayList rowdata = new ArrayList();
int _fieldcount=myDr.FieldCount;
Object[] colvalues = new Object[_fieldcount];
for(int i=0;i<_fieldcount;i++)
{
colvalues[i]=myDr.GetName(i);
}
if(hastitle)
rowdata.Add(colvalues);
while(myDr.Read())
{
Object[] values = new Object[_fieldcount];
myDr.GetValues(values);
rowdata.Add(values);
RowsCount++;
}
myDr.Close();
object[,] rv=new object[rowdata.Count,_fieldcount];
for(int i=0;i<rowdata.Count;i++)
{
for(int j=0;j<_fieldcount;j++)
{
rv[i,j]=((object[])rowdata[i])[j];
}
}
return rv;
}
catch (OracleException e)
{
ErrorMessage=e.Message;
return null;
}
finally
{
Conn.Close();
Conn.Dispose();
}
}//oracle
if (DbServerType.ToLower() == "dm8")
{
DmConnection Conn = new DmConnection(strConn);
DmCommand Cmd = new DmCommand();
Cmd.CommandType = ct;
Cmd.Connection = Conn;
DmDataReader myDr;
Cmd.CommandText = _sql;
if (paras != null)
{
for (int i = 0; i < paras.Count; i++)
{
Cmd.Parameters.Add((DmParameter)paras[i]);
}
}
try
{
Conn.Open();
myDr = Cmd.ExecuteReader() as DmDataReader;
ArrayList rowdata = new ArrayList();
int _fieldcount = myDr.FieldCount;
Object[] colvalues = new Object[_fieldcount];
for (int i = 0; i < _fieldcount; i++)
{
colvalues[i] = myDr.GetName(i);
}
if (hastitle)
rowdata.Add(colvalues);
while (myDr.Read())
{
Object[] values = new Object[_fieldcount];
myDr.GetValues(values);
rowdata.Add(values);
RowsCount++;
}
myDr.Close();
object[,] rv = new object[rowdata.Count, _fieldcount];
for (int i = 0; i < rowdata.Count; i++)
{
for (int j = 0; j < _fieldcount; j++)
{
rv[i, j] = ((object[])rowdata[i])[j];
}
}
return rv;
}
catch (DmException e)
{
ErrorMessage = e.Message;
return null;
}
finally
{
Conn.Close();
Conn.Dispose();
}
}//dm8
return null;
}//getreaddata

复制代码

ArrayList paras=new ArrayList();
string refSql="";
bool hastitle=false;
System.Data.CommandType ct=System.Data.CommandType.Text;
public void FillTable(HtmlTable tb, FillType ft, bool SpaceInEmptyCell, int MinClearRowsCount, int refStartRowId,int refCopyRowId,bool allowToHTML)
{
object[,] ReaderData = GetReaderData("SqlServer","您的连接串","",paras,hastitle,ct);
if (ReaderData == null && RowsCount==0)
{
while (tb.Rows.Count > MinClearRowsCount)
{
tb.Rows.RemoveAt(tb.Rows.Count - 1);
}
return;
}
if (ft == FillType.Automatic || ft==FillType.DynamicRows || ft==FillType.StaticRows)
{
if (ft == FillType.Automatic)
{
int addcells = ReaderData.GetLength(1) - tb.Rows[0].Cells.Count;
for (int i = 0; i < tb.Rows.Count; i++)
{
for (int j = 0; j < addcells; j++)
{
HtmlTableCell tc = new HtmlTableCell();
if (allowToHTML == true)
{
tc.Attributes.Add("ByHTML", "true");
}
tb.Rows[i].Cells.Add(tc);
}
}
if (tb.Rows.Count == 2)
{
for (int j = 0; j < ReaderData.GetLength(1); j++)
{
string _fieldname = ReaderData[0, j].ToString();
tb.Rows[0].Cells[j].InnerText = _fieldname;
}
}
}
int startRowID = tb.Rows.Count - 1;
int copyRowID = 0;
if (ft == FillType.StaticRows)
{
if (HasTitle == true)
{
startRowID = tb.Rows.Count > 1 ? 1 : 0;
}
else
{
startRowID = 0;
}
}
if (refStartRowId != -1) startRowID = refStartRowId;
if (refCopyRowId != -1) copyRowID = refCopyRowId;
for (int i = (HasTitle == true ? 1 : 0); i < ReaderData.GetLength(0); i++)
{
if (startRowID>tb.Rows.Count-1) break;
for (int j = 0; j < ReaderData.GetLength(1); j++)
{
if (j > tb.Rows[copyRowID].Cells.Count - 1) break;
HtmlTableCell td = tb.Rows[startRowID].Cells[j];
if (td.Attributes["ByHTML"] == null)
{
td.InnerText = ReaderData[i, j].ToString();
}
else
{
td.InnerHtml = allowToHTML == true ? (ReaderData[i, j].ToString()) : ReaderData[i, j].ToString();;
}
if (td.InnerText == "" && SpaceInEmptyCell == true) td.InnerHtml=" ";
}
if (i == ReaderData.GetLength(0)-1) break;
if (ft == FillType.Automatic || ft == FillType.DynamicRows)
{
HtmlTableRow tr = new HtmlTableRow();
tb.Rows.Add(tr);
for (int k = 0; k < tb.Rows[copyRowID].Cells.Count; k++)
{
HtmlTableCell prv_tc = tb.Rows[startRowID].Cells[k];
HtmlTableCell tc = new HtmlTableCell();
tr.Cells.Add(tc);
CloneStyles(prv_tc, tc);
}
}
startRowID ++;
}
}
}//fill table
void CloneStyles(HtmlControl obj, HtmlControl newobj)
{
IEnumerator keys = obj.Style.Keys.GetEnumerator();
while (keys.MoveNext())
{
String key = (String)keys.Current; newobj.Style[key] = obj.Style[key];
}
IEnumerator keys2 = obj.Attributes.Keys.GetEnumerator();
while (keys2.MoveNext())
{
String key = (String)keys2.Current; newobj.Attributes[key] = obj.Attributes[key];
}
}

复制代码

<table id="tjTable" runat="server" align="center" border="1" style="width: 700px; border:1px solid #000000;border-collapse:collapse">
<tr><td>标题</td></tr>
<tr>
<td>
 </td>
</tr>
</table>

复制代码

string refSQL = "select value,text,sortid from pub_chinaPay order by sortid";
FillTable(tjTable, FillType.Automatic, true, 0, 1, 1);

复制代码

<table id="tjTable" runat="server" align="center" border="1" style="width: 700px; border:1px solid #000000;border-collapse:collapse">
<tr>
<td>
状态代码</td>
<td>
状态名称</td>
<td>
排序号</td>
</tr>
<tr>
<td>
 </td>
<td>
 </td>
<td>
 </td>
</tr>
</table>

复制代码

string refSQL = "select value,text,sortid from pub_chinaPay order by sortid";
FillTable(tjTable, FillType.DynamicRows, true, 0, 1, 1);

复制代码

<table id="tjTable" runat="server" align="center" border="1" style="width: 700px; border:1px solid #000000;border-collapse:collapse">
<tr>
<td>
状态代码</td>
<td>
状态名称</td>
<td>
排序号</td>
</tr>
<tr>
<td>
 </td>
<td>
 </td>
<td>
 </td>
</tr>
<tr>
<td>
 </td>
<td>
 </td>
<td>
 </td>
</tr>
<tr>
<td>
 </td>
<td>
 </td>
<td>
 </td>
</tr>
<tr>
<td>
 </td>
<td>
 </td>
<td>
 </td>
</tr>
<tr>
<td>
 </td>
<td>
 </td>
<td>
 </td>
</tr>
<tr>
<td>
 </td>
<td>
 </td>
<td>
 </td>
</tr>
<tr>
<td>
 </td>
<td>
 </td>
<td>
 </td>
</tr>
<tr>
<td>
 </td>
<td>
 </td>
<td>
 </td>
</tr>
</table>

复制代码

string refSQL = "select value,text,sortid from pub_chinaPay order by sortid";
FillTable(tjTable,FillType.StaticRows, true, 0, 1, 1);

复制代码