ASP.NET Tutorial/ADO.net Database/DataTable — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 14:56, 26 мая 2010
Содержание
Build a DataTable
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="UsingDataTable" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Using DataTable</title>
</head> <body>
<form id="form1" runat="server">
<asp:ListBox ID="lboxTable" runat="server" DataTextField="FullName" DataValueField="Id" />
</form>
</body> </html> File: Default.aspx.cs using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class UsingDataTable : System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e) { DataTable table = new DataTable(); DataColumn idCol = new DataColumn(); idCol.ColumnName = "Id"; idCol.DataType = typeof(Int32); idCol.AllowDBNull = false; idCol.Unique = true; idCol.AutoIncrement = true; DataColumn firstNameCol = new DataColumn("FirstName", typeof(string)); DataColumn lastNameCol = new DataColumn("LastName", typeof(string)); DataColumn phoneCol = new DataColumn("Phone", typeof(string)); DataColumn nameCol = new DataColumn("FullName", typeof(string)); nameCol.Expression = "LastName + ", " + FirstName"; table.Columns.Add(idCol); table.Columns.Add(firstNameCol); table.Columns.Add(lastNameCol); table.Columns.Add(phoneCol); table.Columns.Add(nameCol); DataRow r1 = table.NewRow(); r1[1] = "A"; r1[2] = "B"; r1[3] = "123-4567"; table.Rows.Add(r1); DataRow r2 = table.NewRow(); r2["FirstName"] = "C"; r2["LastName"] = "D"; r2["Phone"] = "564-7823"; table.Rows.Add(r2); lboxTable.DataSource = table; lboxTable.DataBind(); }
}</source>
Create and display in-memory calculated fields
<source lang="csharp">
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Cities and Customers</title>
</head> <body>
<form id="form1" runat="server">
Find Customers" Cities
<asp:DropDownList runat="server" ID="CityList" Width="230px"> </asp:DropDownList> <asp:Button ID="CityButton" runat="server" Text="Get cities..." OnClick="CityButton_Click" />
</form>
</body> </html> File: Default.aspx.cs using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls;
public partial class Default : System.Web.UI.Page {
protected void CityButton_Click(object sender, EventArgs e) { string connString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString; string cmdText = "SELECT DISTINCT country, city FROM customers"; DataTable data = new DataTable(); SqlDataAdapter adapter = new SqlDataAdapter(cmdText, connString); adapter.Fill(data); data.Columns.Add("CityCountry", typeof(string), "city + " ("+ country + ")""); CityList.DataTextField = "CityCountry"; CityList.DataSource = data; CityList.DataBind(); }
}</source>
How to load a DataTable from a DataReader (C#)
<source lang="csharp">
<%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Configuration" %> <script runat="server">
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack ) { SqlConnection MyConnection; SqlCommand MyCommand; DataTable MyDataTable; SqlDataReader MyReader; SqlParameter CityParam; MyConnection = new SqlConnection(); MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString; MyCommand = new SqlCommand(); MyCommand.rumandText = " SELECT * FROM CUSTOMERS WHERE CITY = @CITY "; MyCommand.rumandType = CommandType.Text; MyCommand.Connection = MyConnection; CityParam = new SqlParameter(); CityParam.ParameterName = "@CITY"; CityParam.SqlDbType = SqlDbType.VarChar; CityParam.Size = 15; CityParam.Direction = ParameterDirection.Input; CityParam.Value = "London"; MyCommand.Parameters.Add(CityParam); MyCommand.Connection.Open(); MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection); MyDataTable = new DataTable(); MyDataTable.Load(MyReader); gvCustomers.DataSource = MyDataTable; gvCustomers.DataBind(); MyDataTable.Dispose(); MyCommand.Dispose(); MyConnection.Dispose(); } }
</script> <html> <body>
<form id="form1" runat="server">
<asp:GridView ID="gvCustomers" runat="server"> </asp:GridView>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="DSN_Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
</configuration></source>
How to load a DataTable from a DataReader (VB)
<source lang="csharp">
<%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Configuration" %> <script runat="server">
Protected Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) If Not Page.IsPostBack Then Dim MyConnection As SqlConnection Dim MyCommand As SqlCommand Dim MyDataTable As DataTable Dim MyReader As SqlDataReader Dim CityParam As SqlParameter MyConnection = New SqlConnection() MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString MyCommand = New SqlCommand() MyCommand.rumandText = " SELECT * FROM CUSTOMERS WHERE CITY = @CITY " MyCommand.rumandType = CommandType.Text MyCommand.Connection = MyConnection CityParam = New SqlParameter() CityParam.ParameterName = "@CITY" CityParam.SqlDbType = SqlDbType.VarChar CityParam.Size = 15 CityParam.Direction = ParameterDirection.Input CityParam.Value = "London" MyCommand.Parameters.Add(CityParam) MyCommand.Connection.Open() MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection) MyDataTable = New DataTable() MyDataTable.Load(MyReader) gvCustomers.DataSource = MyDataTable gvCustomers.DataBind() MyDataTable.Dispose() MyCommand.Dispose() MyConnection.Dispose() End If End Sub
</script> <html> <body>
<form id="form1" runat="server">
<asp:GridView ID="gvCustomers" runat="server"> </asp:GridView>
</form>
</body> </html> File: Web.config <configuration>
<connectionStrings> <add name="DSN_Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
</configuration></source>
The DataTable object represents an in-memory database table.
<source lang="csharp">
<%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">
void Page_Load() { DataTable newDataTable = new DataTable(); newDataTable.Columns.Add("Id", typeof(int)); newDataTable.Columns.Add("ProductName", typeof(string)); newDataTable.Columns.Add("ProductPrice", typeof(decimal)); newDataTable.Columns["Id"].AutoIncrement = true; for (int i = 1; i < 11; i++) { DataRow newRow = newDataTable.NewRow(); newRow["ProductName"] = "Product " + i.ToString(); newRow["ProductPrice"] = 12.34m; newDataTable.Rows.Add(newRow); } grdProducts.DataSource = newDataTable; grdProducts.DataBind(); }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show DataTable Programmatically</title>
</head> <body>
<form id="form1" runat="server">
Products
<asp:GridView id="grdProducts" Runat="server" />
</form>
</body> </html></source>