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

Материал из .Net Framework эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (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>

Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/DataTable&oldid=2662»