ASP.NET Tutorial/ADO.net Database/DataTable — различия между версиями

Материал из .Net Framework эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Версия 15:30, 26 мая 2010

Build a DataTable

<%@ 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">
    <div id="container">
        <asp:ListBox ID="lboxTable" runat="server" 
           DataTextField="FullName"
           DataValueField="Id" />         
      
    </div>
    </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();
   }
}


Create and display in-memory calculated fields

<%@ 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>
    <div id="pageContent">
        <form id="form1" runat="server">
            <h2>Find Customers" Cities</h2>
            <hr />
            <asp:DropDownList runat="server" ID="CityList" Width="230px">
            </asp:DropDownList>            
            <asp:Button ID="CityButton" runat="server" Text="Get cities..." OnClick="CityButton_Click" />
            <hr />
        </form>
    </div>
</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();
    }
}


How to load a DataTable from a DataReader (C#)

<%@ 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">
    <div>
        <asp:GridView ID="gvCustomers" runat="server">
        </asp:GridView>    
    </div>
    </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>


How to load a DataTable from a DataReader (VB)

<%@ 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">
    <div>
        <asp:GridView ID="gvCustomers" runat="server">
        </asp:GridView>    
    </div>
    </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>


The DataTable object represents an in-memory database table.

<%@ 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">
    <div>
    <h1>Products</h1>
    <asp:GridView
        id="grdProducts"
        Runat="server" />
    </div>
    </form>
</body>
</html>
Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/DataTable&oldid=2661»