最好的方法来查看一个有* *列的表？ [英] Best way to view a table with *lots* of columns?
本文介绍了最好的方法来查看一个有* *列的表？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
有被降级的风险，我想问一个使用C＃从表中查看数据的最好的机制（最好是明显主观的对于这里固有的习惯违反），很多的列。很多，我的意思是像1000.
现在在你得到所有的点击之前，或者抛出反应像为什么你会有一个表与许多列让我说它实际上是设计要求的一部分。我们正在从1000个数据点尽快收集数据。我们需要尽可能快地存储这些，因此平坦的表。数据需要从SQL Server直接访问，因此数据库（我们使用SQL Compact with table-direct）。
现在，我们已经了解了正确的数据库设计，规范化规则等等，并且只关注一个事实，我有一个1000列的表，我想能够在屏幕上显示数据，以验证数据实际上
我尝试过一个数据网格。它pukes，因为（毫不奇怪）它不是设计来处理这么多列。
我已经尝试在Studio中使用查看器。它pukes后256，再加上最终用户将不会有安装的Studio。
现在结果不需要漂亮，它不需要更新，也不是
相关（或半相关）信息：
p>- 表格有1000列（在点击之前已阅读）
- li>
- 在桌面上运行
- 寻找托管代码答案
解决方案
确定，对我来说是正确的答案是使用 ReportViewer控件，但不以MSDN中记录的任何方式。问题是，我有动态数据，所以我需要一个动态报告，所有的教程等似乎假设你有在设计时知道一切的奢侈，所以你可以点和你的方式通过一个向导。 / p>
解决方案最终需要一对夫妇。首先，我不得不创建代码来动态生成RDLC，ReportViewer用它来描述报告布局和什么数据字段映射到什么。这是我想出的：
public static Stream BuildRDLCStream（
DataSet data，string name，string reportXslPath）
{
using（MemoryStream schemaStream = new MemoryStream（））
{
//将模式保存到流
data.WriteXmlSchema（schemaStream）;
schemaStream.Seek（0，SeekOrigin.Begin）;
//将其加载到Document并设置Name变量
XmlDocument xmlDomSchema = new XmlDocument（）;
xmlDomSchema.Load（schemaStream）;
xmlDomSchema.DocumentElement.SetAttribute（Name，data.DataSetName）;
//加载报表的XSL文件（这是神奇的）
XslCompiledTransform xform = new XslCompiledTransform（）;
xform.Load（reportXslPath）;
//做转换
MemoryStream rdlcStream = new MemoryStream（）;
XmlWriter writer = XmlWriter.Create（rdlcStream）;
xform.Transform（xmlDomSchema，writer）;
writer.Close（）;
rdlcStream.Seek（0，SeekOrigin.Begin）;
//发回RDLC
return rdlcStream;
}
}
第二部分是一个XSL文件，关闭 Dan Shipe的博客。 RDLC代码是相当没有价值，因为它是所有打算用于Web使用，但XSL是纯金。
一旦我有这两个部分，这只是一个问题，创建一个具有ReportViewer控件的窗体，然后使用这一段代码进行设置：
ds.DataSetName = name;
流rdlc = RdlcEngine.BuildRDLCStream（
ds，name，c：\\temp\\rdlc\\\report.xsl）;
reportView.LocalReport.LoadReportDefinition（rdlc）;
reportView.LocalReport.DataSources.Clear（）;
reportView.LocalReport.DataSources.Add（
new ReportDataSource（ds.DataSetName，ds.Tables [0]））;
reportView.RefreshReport（）;
这里的关键是'ds'是一个DataSet对象，
再次，为了完整性，这里是XSL - 对于大小抱歉：
<？xml version =1.0？>
<！ - 用于创建ReportViewer RDLC文档的样式表 - >
< xsl：stylesheet version =1.0
xmlns：xsl =http://www.w3.org/1999/XSL/Transform
xmlns：msxsl =urn： schemas-microsoft-com：xslt
xmlns：xs =http://www.w3.org/2001/XMLSchema
xmlns：msdata =urn：schemas-microsoft-com：xml- msdata
xmlns：rd =http://schemas.microsoft.com/SQLServer/reporting/reportdesignerxmlns =http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition
>
< xsl：variable name =mvarNameselect =/ xs：schema / @ Name/>
< xsl：variable name =mvarFontSize> 8pt< / xsl：variable>
< xsl：variable name =mvarFontWeight> 500< / xsl：variable>
< xsl：variable name =mvarFontWeightBold> 700< / xsl：variable>
< xsl：template match =/>
< xsl：apply-templates select =/ xs：schema / xs：element / xs：complexType / xs：choice / xs：element / xs：complexType / xs：sequence>
< / xsl：apply-templates>
< / xsl：template>
< xsl：template match =xs：sequence>
< Report xmlns：rd =http://schemas.microsoft.com/SQLServer/reporting/reportdesignerxmlns =http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition >
< BottomMargin> 1in< / BottomMargin>
< RightMargin> 1in< / RightMargin>
< LeftMargin> 1in< / LeftMargin>
< TopMargin> 1in< / TopMargin>
< InteractiveHeight> 11in< / InteractiveHeight>
< InteractiveWidth> 8.5in< / InteractiveWidth>
< Width> 6.5in< / Width>
< Language> zh-CN< / Language>
< rd：DrawGrid> true< / rd：DrawGrid>
< rd：SnapToGrid> true< / rd：SnapToGrid>
< rd：ReportID> 7358b654-3ca3-44a0-8677-efe0a55c7c45< / rd：ReportID>
< xsl：call-template name =BuildDataSource>
< / xsl：call-template>
< xsl：call-template name =BuildDataSet>
< / xsl：call-template>
< Body>
< Height> 0.50in< / Height>
< ReportItems>
< Table Name =table1>
< DataSetName>< xsl：value -of select =$ mvarName/>< / DataSetName>
< Top> 0.5in< / Top>
< Height> 0.50in< / Height>
< Header>
< TableRows>
< TableRow>
< Height> 0.25in< / Height>
< TableCells>
< xsl：apply-templates select =xs：elementmode =HeaderTableCell>
< / xsl：apply-templates>
< / TableCells>
< / TableRow>
< / TableRows>
< / Header>
<详细>
< TableRows>
< TableRow>
< Height> 0.25in< / Height>
< TableCells>
< xsl：apply-templates select =xs：elementmode =DetailTableCell>
< / xsl：apply-templates>
< / TableCells>
< / TableRow>
< / TableRows>
< / Details>
< TableColumns>
< xsl：apply-templates选择=xs：elementmode =TableColumn>
< / xsl：apply-templates>
< / TableColumns>
< / Table>
< / ReportItems>
< / Body>
< / Report>
< / xsl：template>
< xsl：template name =BuildDataSource>
< DataSources>
< DataSource Name =DummyDataSource>
< ConnectionProperties>
< ConnectString />
< DataProvider> SQL< / DataProvider>
< / ConnectionProperties>
< rd：DataSourceID> 84635ff8-d177-4a25-9aa5-5a921652c79c< / rd：DataSourceID>
< / DataSource>
< / DataSources>
< / xsl：template>
< xsl：template name =BuildDataSet>
< DataSets>
< DataSet Name ={$ mvarName}>
< Query>
< rd：UseGenericDesigner> true< / rd：UseGenericDesigner>
< CommandText />
< DataSourceName> DummyDataSource< / DataSourceName>
< / Query>
< Fields>
< xsl：apply-templates select =xs：elementmode =Field>
< / xsl：apply-templates>
< / Fields>
< / DataSet>
< / DataSets>
< / xsl：template>
< xsl：template match =xs：elementmode =Field>
< xsl：variable name =varFieldName>
< xsl：value-of select =@ name/>
< / xsl：variable>
< xsl：variable name =varDataType>
< xsl：choose>
< xsl：when test =@ type ='xs：int'> System.Int32< / xsl：when>
< xsl：when test =@ type ='xs：string'> System.String< / xsl：when>
< xsl：when test =@ type ='xs：dateTime'> System.DateTime< / xsl：when>
< xsl：when test =@ type ='xs：boolean'> System.Boolean< / xsl：when&
< / xsl：choose>
< / xsl：variable>
< Field Name ={$ varFieldName}>
< rd：TypeName>< xsl：value-of select =$ varDataType/>< / rd：TypeName&
< DataField>< xsl：value -of select =$ varFieldName/>< / DataField>
< / Field>
< / xsl：template>
< xsl：template match =xs：elementmode =HeaderTableCell>
< xsl：variable name =varFieldName>
< xsl：value-of select =@ name/>
< / xsl：variable>
< TableCell>
< ReportItems>
< Textbox Name =textbox {position（）}>
< rd：DefaultName> textbox< xsl：value-of select =position（）/>
< / rd：DefaultName>
< Value>< xsl：value -of select =$ varFieldName/>< / Value>
< CanGrow> true< / CanGrow>
< ZIndex> 7< / ZIndex>
< Style>
< TextAlign>中心< / TextAlign>
< PaddingLeft> 2pt< / PaddingLeft>
< PaddingBottom> 2pt< / PaddingBottom>
< PaddingRight> 2pt< / PaddingRight>
< PaddingTop> 2pt< / PaddingTop>
< FontSize>< xsl：value -of select =$ mvarFontSize/>< / FontSize>
< FontWeight>< xsl：value-of select =$ mvarFontWeightBold/>< / FontWeight>
< BackgroundColor>＃000000< / BackgroundColor>
< Color>＃ffffff< / Color>
< BorderColor>
<默认>＃ffffff< / Default>
< / BorderColor>
< BorderStyle>
<默认>实体< /默认>
< / BorderStyle>
< / Style>
< / Textbox>
< / ReportItems>
< / TableCell>
< / xsl：template>
< xsl：template match =xs：elementmode =DetailTableCell>
< xsl：variable name =varFieldName>
< xsl：value-of select =@ name/>
< / xsl：variable>
< TableCell>
< ReportItems>
< Textbox Name ={$ varFieldName}>
< rd：DefaultName>< xsl：value -of select =$ varFieldName/>< / rd：DefaultName>
< Value> = Fields！< xsl：value -of select =$ varFieldName/> .Value< / Value>
< CanGrow> true< / CanGrow>
< ZIndex> 7< / ZIndex>
< Style>
< TextAlign>左< / TextAlign>
< PaddingLeft> 2pt< / PaddingLeft>
< PaddingBottom> 2pt< / PaddingBottom>
< PaddingRight> 2pt< / PaddingRight>
< PaddingTop> 2pt< / PaddingTop>
< FontSize>< xsl：value -of select =$ mvarFontSize/>< / FontSize>
< FontWeight>< xsl：value -of select =$ mvarFontWeight/>< / FontWeight>
< BackgroundColor>＃e0e0e0< / BackgroundColor>
< Color>＃000000< / Color>
< BorderColor>
<默认>＃ffffff< / Default>
< / BorderColor>
< BorderStyle>
<默认>实体< /默认>
< / BorderStyle>
< / Style>
< / Textbox>
< / ReportItems>
< / TableCell>
< / xsl：template>
< xsl：template match =xs：elementmode =TableColumn>
< TableColumn>
< Width> 0.75in< / Width>
< / TableColumn>
< / xsl：template>
< xsl：template name =replace-string>
< xsl：param name =text/>
< xsl：param name =from/>
< xsl：param name =to/>
< xsl：choose>
< xsl：when test =contains（$ text，$ from）>
< xsl：variable name =beforeselect =substring-before（$ text，$ from）/>
< xsl：variable name =afterselect =substring-after（$ text，$ from）/>
< xsl：variable name =prefixselect =concat（$ before，$ to）/>
< xsl：value-of select =$ before/>
< xsl：value-of select =$ to/>
< xsl：call-template name =replace-string>
< xsl：with-param name =textselect =$ after/>
< xsl：with-param name =fromselect =$ from/>
< xsl：with-param name =toselect =$ to/>
< / xsl：call-template>
< / xsl：when>
< xsl：otherwise>
< xsl：value-of select =$ text/>
< / xsl：otherwise>
< / xsl：choose>
< / xsl：template>
< / xsl：stylesheet>
At the risk of being downmodded, I want to ask what the best mechanism (best is obviously subjective for the practice violation inherent here) for viewing data from a table, using C#, with a lot of columns. By a lot, I mean something like 1000.
Now before you get all click happy, or throw out responses like "why the hell would you ever have a table with that many columns" let me say that it's actually part of a design requirement. We are collecting data as fast as we can from 1000 data points. We need to store these as fast as possible, hence the flat table. The data needs to be directly accessible from SQL Server, hence the database (we're using SQL Compact with table-direct).
So let's forget, for now, all that we've learned about proper database design, the rules of normalization, etc. and just focus on the fact that I have a table with 1000 columns and I want to be able to display the data on screen to verify that the data is actually going in there.
I've tried a data grid. It pukes because (not surprisingly) it's not designed to handle that many columns.
I've tried using the viewer in Studio. It pukes after 256, plus the end user won't have Studio installed anyway.
For now the result need not be pretty, it need not be updateable, nor does it need to be sensitive to data changes - just a static snapshot of data in the table at a given point in time.
Relevant (or semi-relevant) info:
- Table has 1000 columns (read above before getting click happy)
- Using SQL Compact version 3.5
- Running on the desktop
- Looking for a managed-code answer
解决方案
Ok, what turned out to be the right answer for me was to use the ReportViewer control, but not in any manner documented in MSDN. The problem is that I have dynamic data, so I need a dynamic report, and all of the tutorials, etc. seem to assume you have the luxury of knowing everything at design time so you can point and click your way through a Wizard.
The solution ended up requiring a couple pieces. First, I had to create code to dynamically generate the RDLC that the ReportViewer uses to describe the report layout and what data fields map to what. This is what I came up with:
public static Stream BuildRDLCStream(
DataSet data, string name, string reportXslPath)
{
using (MemoryStream schemaStream = new MemoryStream())
{
// save the schema to a stream
data.WriteXmlSchema(schemaStream);
schemaStream.Seek(0, SeekOrigin.Begin);
// load it into a Document and set the Name variable
XmlDocument xmlDomSchema = new XmlDocument();
xmlDomSchema.Load(schemaStream);
xmlDomSchema.DocumentElement.SetAttribute("Name", data.DataSetName);
// load the report's XSL file (that's the magic)
XslCompiledTransform xform = new XslCompiledTransform();
xform.Load(reportXslPath);
// do the transform
MemoryStream rdlcStream = new MemoryStream();
XmlWriter writer = XmlWriter.Create(rdlcStream);
xform.Transform(xmlDomSchema, writer);
writer.Close();
rdlcStream.Seek(0, SeekOrigin.Begin);
// send back the RDLC
return rdlcStream;
}
}
The second piece is an XSL file that I took right off of Dan Shipe's blog. The RDLC code there was pretty worthless as it was all intended for Web use, but the XSL is pure gold. I've put it at the bottom of this post for completeness in case that blog ever goes offline.
Once I has those two pieces, it was simply a matter of creating a Form with a ReportViewer control on it, then using this bit of code to set it up:
ds.DataSetName = name;
Stream rdlc = RdlcEngine.BuildRDLCStream(
ds, name, "c:\\temp\\rdlc\\report.xsl");
reportView.LocalReport.LoadReportDefinition(rdlc);
reportView.LocalReport.DataSources.Clear();
reportView.LocalReport.DataSources.Add(
new ReportDataSource(ds.DataSetName, ds.Tables[0]));
reportView.RefreshReport();
The key here is that 'ds' is a DataSet object with a single DataTable in it with the data to be displayed.
Again, for completeness, here's the XSL - sorry about the size:
<?xml version="1.0"?>
<!-- Stylesheet for creating ReportViewer RDLC documents -->
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>
<xsl:variable name="mvarName" select="/xs:schema/@Name"/>
<xsl:variable name="mvarFontSize">8pt</xsl:variable>
<xsl:variable name="mvarFontWeight">500</xsl:variable>
<xsl:variable name="mvarFontWeightBold">700</xsl:variable>
<xsl:template match="/">
<xsl:apply-templates select="/xs:schema/xs:element/xs:complexType/xs:choice/xs:element/xs:complexType/xs:sequence">
</xsl:apply-templates>
</xsl:template>
<xsl:template match="xs:sequence">
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition">
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<LeftMargin>1in</LeftMargin>
<TopMargin>1in</TopMargin>
<InteractiveHeight>11in</InteractiveHeight>
<InteractiveWidth>8.5in</InteractiveWidth>
<Width>6.5in</Width>
<Language>en-US</Language>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:ReportID>7358b654-3ca3-44a0-8677-efe0a55c7c45</rd:ReportID>
<xsl:call-template name="BuildDataSource">
</xsl:call-template>
<xsl:call-template name="BuildDataSet">
</xsl:call-template>
<Body>
<Height>0.50in</Height>
<ReportItems>
<Table Name="table1">
<DataSetName><xsl:value-of select="$mvarName" /></DataSetName>
<Top>0.5in</Top>
<Height>0.50in</Height>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<xsl:apply-templates select="xs:element" mode="HeaderTableCell">
</xsl:apply-templates>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<xsl:apply-templates select="xs:element" mode="DetailTableCell">
</xsl:apply-templates>
</TableCells>
</TableRow>
</TableRows>
</Details>
<TableColumns>
<xsl:apply-templates select="xs:element" mode="TableColumn">
</xsl:apply-templates>
</TableColumns>
</Table>
</ReportItems>
</Body>
</Report>
</xsl:template>
<xsl:template name="BuildDataSource">
<DataSources>
<DataSource Name="DummyDataSource">
<ConnectionProperties>
<ConnectString/>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>84635ff8-d177-4a25-9aa5-5a921652c79c</rd:DataSourceID>
</DataSource>
</DataSources>
</xsl:template>
<xsl:template name="BuildDataSet">
<DataSets>
<DataSet Name="{$mvarName}">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText/>
<DataSourceName>DummyDataSource</DataSourceName>
</Query>
<Fields>
<xsl:apply-templates select="xs:element" mode="Field">
</xsl:apply-templates>
</Fields>
</DataSet>
</DataSets>
</xsl:template>
<xsl:template match="xs:element" mode="Field">
<xsl:variable name="varFieldName">
<xsl:value-of select="@name" />
</xsl:variable>
<xsl:variable name="varDataType">
<xsl:choose>
<xsl:when test="@type='xs:int'">System.Int32</xsl:when>
<xsl:when test="@type='xs:string'">System.String</xsl:when>
<xsl:when test="@type='xs:dateTime'">System.DateTime</xsl:when>
<xsl:when test="@type='xs:boolean'">System.Boolean</xsl:when>
</xsl:choose>
</xsl:variable>
<Field Name="{$varFieldName}">
<rd:TypeName><xsl:value-of select="$varDataType"/></rd:TypeName>
<DataField><xsl:value-of select="$varFieldName"/></DataField>
</Field>
</xsl:template>
<xsl:template match="xs:element" mode="HeaderTableCell">
<xsl:variable name="varFieldName">
<xsl:value-of select="@name" />
</xsl:variable>
<TableCell>
<ReportItems>
<Textbox Name="textbox{position()}">
<rd:DefaultName>textbox<xsl:value-of select="position()"/>
</rd:DefaultName>
<Value><xsl:value-of select="$varFieldName"/></Value>
<CanGrow>true</CanGrow>
<ZIndex>7</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<FontSize><xsl:value-of select="$mvarFontSize"/></FontSize>
<FontWeight><xsl:value-of select="$mvarFontWeightBold"/></FontWeight>
<BackgroundColor>#000000</BackgroundColor>
<Color>#ffffff</Color>
<BorderColor>
<Default>#ffffff</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Textbox>
</ReportItems>
</TableCell>
</xsl:template>
<xsl:template match="xs:element" mode="DetailTableCell">
<xsl:variable name="varFieldName">
<xsl:value-of select="@name" />
</xsl:variable>
<TableCell>
<ReportItems>
<Textbox Name="{$varFieldName}">
<rd:DefaultName><xsl:value-of select="$varFieldName"/></rd:DefaultName>
<Value>=Fields!<xsl:value-of select="$varFieldName"/>.Value</Value>
<CanGrow>true</CanGrow>
<ZIndex>7</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<FontSize><xsl:value-of select="$mvarFontSize"/></FontSize>
<FontWeight><xsl:value-of select="$mvarFontWeight"/></FontWeight>
<BackgroundColor>#e0e0e0</BackgroundColor>
<Color>#000000</Color>
<BorderColor>
<Default>#ffffff</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Textbox>
</ReportItems>
</TableCell>
</xsl:template>
<xsl:template match="xs:element" mode="TableColumn">
<TableColumn>
<Width>0.75in</Width>
</TableColumn>
</xsl:template>
<xsl:template name="replace-string">
<xsl:param name="text"/>
<xsl:param name="from"/>
<xsl:param name="to"/>
<xsl:choose>
<xsl:when test="contains($text, $from)">
<xsl:variable name="before" select="substring-before($text, $from)"/>
<xsl:variable name="after" select="substring-after($text, $from)"/>
<xsl:variable name="prefix" select="concat($before, $to)"/>
<xsl:value-of select="$before"/>
<xsl:value-of select="$to"/>
<xsl:call-template name="replace-string">
<xsl:with-param name="text" select="$after"/>
<xsl:with-param name="from" select="$from"/>
<xsl:with-param name="to" select="$to"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>
这篇关于最好的方法来查看一个有* *列的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
