如何在.NET项目中应用DataTable对象-技术开发专区

如何在.NET项目中应用DataTable对象

作者：ZDNET 编辑：晓徐 2006-06-21 22:52

一旦建立了DataTable，它就包含了SQL服务器查询的结果。表A中的C#代码就是按照这些步骤来建立的DataTable对象。一旦DataTable对象被建立，就用一个DataRowCollection对象来处理由查询返回的数据行。（表B包含对应的VB.NET代码。）

表A

<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<script language="C#" runat="server">
private SqlConnectionconn = null;
private SqlCommandcmd = null;
private SqlDataAdapterda = null;
private DataTabledt = null;
private DataRowCollectiondr = null;
private String connString;
private String strSQL;
private void Page_Load(object sender, System.EventArgs e) ...{
if (!IsPostBack) ...{
connString = "data source=localhost;uid=test;pwd=test;initial catalog=Northwind";
strSQL = "SELECT LastName, FirstName, Title, HireDate FROM dbo.Employees";
try ...{
conn = new SqlConnection(connString);
cmd = new SqlCommand(strSQL, conn);
da = new SqlDataAdapter();
da.SelectCommand = cmd;
dt = new DataTable();
conn.Open();
da.Fill(dt);
dr = dt.Rows;
for (inti=0; i<=(dr.Count-1); i++) ...{
Response.Write(dr[i]["FirstName"].ToString()
+ " " + dr[i]["LastName"].ToString() + " - " + dr[i]["Title"].ToString() + "<br>");
}
conn.Close();
} catch (SqlException ex) ...{
Response.Write("Error accessing database: " + ex.ToString());
} catch (Exception ex) ...{
Response.Write("Exception: " + ex.ToString());
} finally ...{
if (conn.State == ConnectionState.Open) ...{
conn.Close();
}
conn.Dispose();
} } }
</script>

表B


<%@ Page Language="VB" Debug="true" %>

<%@ Import Namespace =Namespace="System.Data.SqlClient" %>
<%@ Import Namespace =Namespace="System.Data" %>
<script language="VB" runat="server">
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim dt As DataTable
Dim dr As DataRowCollection
Dim connString As String
Dim strSQL As String
Dim i As Integer
Sub Page_Load()Sub Page_Load()
If Not (IsPostBack) Then
connString = "data source=localhost;uid=test;pwd=test;initial catalog=Northwind"
strSQL = "SELECT LastName, FirstName, Title, HireDate FROM dbo.Employees"
Try
conn = new SqlConnection(connString)
cmd = new SqlCommand(strSQL, conn)
da = new SqlDataAdapter()
da.SelectCommand = cmd
dt = new DataTable()
conn.Open()
da.Fill(dt)
dr = dt.Rows
For i = 0 to (dr.Count-1)
Response.Write(dr(i)("FirstName").ToString()
+ " " + dr(i)("LastName").ToString() + " - " + dr(i)("Title").ToString() + "<br>")
Next i
conn.Close()
Catch ex As SqlException
Response.Write("Error accessing database: " + ex.ToString())
Catch ex As Exception
Response.Write("Exception: " + ex.ToString())
Finally
If (conn.State = ConnectionState.Open) Then
conn.Close()
End If
conn.Dispose()
End Try
End If
End Sub
</script>

    你会注意到，结果通过DataRowCollection对象的给定值来访问，而单独的列则通过它们的名称（或是以零开始的给定值来访问。DataRowCollection对象的单个元素用来显示某个员工的姓名与头衔。

    注意，你可将DataTable对象用于数据绑定，这意味着你可以用它作DataGrid,Repeater或其它数据对象的数据源。表C中的例子应用DataTable对象作一个简单的DataGrid对象的数据源。（表D中为对应的VB.NET代码。）

    表C

 <%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<script language="C#" runat="server">
private SqlConnectionconn = null;
private SqlCommandcmd = null;
private SqlDataAdapterda = null;
private DataTabledt = null;
private DataRow[] dr = null;
private String connString;
private String strSQL;
private void Page_Load(object sender, System.EventArgs e) ......{
if (!IsPostBack) ......{
connString = "data source=localhost;uid=test;pwd=test;initial catalog=Northwind";
strSQL = "SELECT LastName, FirstName, Title, HireDate FROM dbo.Employees";
try ......{
conn = new SqlConnection(connString);
cmd = new SqlCommand(strSQL, conn);
da = new SqlDataAdapter();
da.SelectCommand = cmd;
dt = new DataTable();
conn.Open();
da.Fill(dt);
dgTest.DataSource = dt;
dgTest.DataBind();
conn.Close();
} finally ......{
if (conn.State == ConnectionState.Open) ......{
conn.Close();
}
conn.Dispose();
} } }
</script>
<asp:DataGrid id="dgTest" runat="Server"></asp:DataGrid><

表D

<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace =Namespace="System.Data.SqlClient" %>
<%@ Import Namespace =Namespace="System.Data" %>
<script language="VB" runat="server">
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim dt As DataTable
Dim dr As DataRowCollection
Dim connString As String
Dim strSQL As String
Dim i As Integer
Sub Page_Load()Sub Page_Load()
If Not (IsPostBack) Then
connString = "data source=localhost;uid=test;pwd=test;initial catalog=Northwind"
strSQL = "SELECT LastName, FirstName, Title, HireDate FROM dbo.Employees"
Try
conn = new SqlConnection(connString)
cmd = new SqlCommand(strSQL, conn)
da = new SqlDataAdapter()
da.SelectCommand = cmd
dt = new DataTable()
conn.Open()
da.Fill(dt)
dgTest.DataSource = dt
dgTest.DataBind()
Finally
If (conn.State = ConnectionState.Open) Then
conn.Close()
End If
conn.Dispose()
End Try
End If
End Sub
</script>
<asp:DataGrid id="dgTest" runat="Server"></asp:DataGrid>

DataTable类的另一强大功能是你拥有处理其数据的能力。这一功能的最好例证是Select方法，它允许你挑选单行或单列的数据。表E中的例子修改了前面的例子，只显示Select方法返回的行。（表F包含对应的VB.NET代码。）

表E

<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<script language="C#" runat="server">
private SqlConnectionconn = null;
private SqlCommandcmd = null;
private SqlDataAdapterda = null;
private DataTabledt = null;
private DataRow[] dr = null;
private String connString;
private String strSQL;
private void Page_Load(object sender, System.EventArgs e) ...{
if (!IsPostBack) ...{
connString = "data source=localhost;uid=test;pwd=test;initial catalog=Northwind";
strSQL = "SELECT LastName, FirstName, Title, HireDate FROM dbo.Employees";
try ...{
conn = new SqlConnection(connString);
cmd = new SqlCommand(strSQL, conn);
da = new SqlDataAdapter();
da.SelectCommand = cmd;
dt = new DataTable();
conn.Open();
da.Fill(dt);
dr = dt.Select("LastName = 'King'");
for (inti=0; i<=dr.GetUpperBound(0); i++) ...{
Response.Write(dr[i]["FirstName"].ToString()
+ " " + dr[i]["LastName"].ToString() + " - " + dr[i]["Title"].ToString() + "<br>");
}
conn.Close();
} catch (SqlException ex) ...{
Response.Write("Error accessing database: " + ex.ToString());
} catch (Exception ex) ...{
Response.Write("Exception: " + ex.ToString());
} finally ...{
if (conn.State == ConnectionState.Open) ...{
conn.Close();
}
conn.Dispose();
} } }
</script>

表F

<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace =Namespace="System.Data.SqlClient" %>
<%@ Import Namespace =Namespace="System.Data" %>
<script language="VB" runat="server">
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim dt As DataTable
Dim dr() As DataRow
Dim connString As String
Dim strSQL As String
Dim i As Integer
Sub Page_Load()Sub Page_Load()
If Not (IsPostBack) Then
connString = "data source=localhost;uid=test;pwd=test;initial catalog=Northwind"
strSQL = "SELECT LastName, FirstName, Title, HireDate FROM dbo.Employees"
Try
conn = new SqlConnection(connString)
cmd = new SqlCommand(strSQL, conn)
da = new SqlDataAdapter()
da.SelectCommand = cmd
dt = new DataTable()
conn.Open()
da.Fill(dt)
dr = dt.Select("LastName = 'King'")
For i = 0 to dr.GetUpperBound(0)
Response.Write(dr(i)("FirstName").ToString() + " " +
dr(i)("LastName").ToString() + " - " + dr(i)("Title").ToString() + "<br>")
Next i
Finally
If (conn.State = ConnectionState.Open) Then
conn.Close()
End If
conn.Dispose()
End Try
End If
End Sub
</script>

    Select方法返回一个与选择标准相匹配的DataRow对象数组。它有四个签名，不带返回所有行数据的参数。你可以传递选择声明并在其中包含一个分类列值和状态值。

    Select方法只是DataTable类的许多方法与属性中的一个。详尽的方法与属性列表并不是本文讨论的内容，欲了解更多信息，请查看相关文件。

    处理数据的另一选择方案

    对每个应用程序来说，数据都是一个重要的元素。.NET框架提供了几种在应用程序中访问并处理数据的方法。DataSet的几个数据对象吸引了大部分的注意力，但其它的对象如DataTable在许多情况下也很有用。DataTable允许你处理单个的表格，与对应的DataSet对象相比，所需的管理费用更少。在下一个项目中，记得使用DataTable对象哦！

第1页：应用DataTable对象(一)第2页：应用DataTable对象(二)

关注我们