ASP.Net/ADO.net Database/SqlDataReader

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

Bind SqlDataReader to GridView

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="Default_aspx" %>
<!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>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     <asp:GridView ID="GridView1" runat="server">
     </asp:GridView>
    </div>
    </form>
</body>
</html>
File: Default.aspx.cs
using System;
using System.Data;
using System.Configuration;
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;
using System.Data.SqlClient;
public partial class Default_aspx : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
     string connectionString = "Data Source=LocalHost;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=sa_0001";
     string commandString = "Select * from Customers";
     SqlConnection conn = new SqlConnection(connectionString);
     SqlCommand command = new SqlCommand(commandString);
     conn.Open();
     command.Connection = conn;
     SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
     GridView1.DataSource = reader;
     GridView1.DataBind();
    }
}



Get data from SqlDataReader

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
   <title>Try-Catch-Finally Example</title>
   <head>
      <script runat="server">
         Sub Page_Load()
     "    Dim ConnStr As String = "Data Source=(local)\NetSDK;" & _
      "      "Initial Catalog=Pubs;Trusted_Connection=True;"
            Dim ConnStr As String = "Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=nfexuser;Password="password";"
            Dim SQL As String = "SELECT ID, FirstName FROM Employee " & _ 
               "WHERE ID IS NOT NULL"
            Dim PubsConn As New SqlConnection(ConnStr)
            Dim TitlesCmd As New SqlCommand(SQL, PubsConn)
            Dim Titles As SqlDataReader
            Try
               PubsConn.Open()
               Titles = TitlesCmd.ExecuteReader()
               Output.Text = "<table>"
               While Titles.Read()
                  Output.Text &= "<tr>"
                  Output.Text &= "<td>" & Titles.GetString(0) & "</td>"
"                  Output.Text &= "<td>$" & _
 "                    Format(Titles.GetDecimal(1), "##0.00") & "</td>"
                  Output.Text &= "</tr>"
               End While
               Output.Text &= "</table>"
            Catch sqlEx As SqlException
               Response.Write("A SqlException has occurred.")
            Catch ex As Exception
               Response.Write("An Exception has occurred.")
            Finally
               If Not Titles Is Nothing Then
                  If Not Titles.IsClosed Then
                     Titles.Close()
                  End If
               End If
               If PubsConn.State = ConnectionState.Open Then
                  PubsConn.Close()
               End If
            End Try
            Response.Write("<br/>The current connection state is: " & _
               PubsConn.State.ToString() & ".")
         End Sub
      </script>
   </head>
<body>
   <h1>SqlDataReader Example</h1>
   <asp:label id="Output" runat="server"/>
</body>
</html>



SqlDataReader Demo

<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.SqlClient" %>
<html>
  <head>
    <title>Data Reader</title>
  </head>
  <body>
    <h1>Data Reader</h1>
    Total Products listed = 
    <asp:DataGrid id="dgCount" runat="server"
                  BorderWidth=0 ShowHeader=False />
    <br/>
    <asp:DataGrid id="dgData" runat="server" />
  </body>
</html>
<script language="VB" runat="server">
Sub Page_Load(Source As Object, E As EventArgs)
  Dim strConnection As String = "Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=nfexuser;Password="password";"
  Dim objConnection As New SqlConnection(strConnection)
  objConnection.Open()
  Dim strSQLcount As String
  strSQLcount = "SELECT COUNT(FirstName) FROM Employee"
  Dim objCommand As New SqlCommand(strSQLcount, objConnection)
  Dim objReader As SqlDataReader = objCommand.ExecuteReader()
  dgCount.DataSource = objReader
  dgCount.DataBind()
  objReader.Close()
  Dim strProcName As String
  strProcName = "SELECT * FROM Employee"
  objCommand.rumandText = strProcName
  objReader = objCommand.ExecuteReader()
  dgData.DataSource = objReader
  dgData.DataBind()
  objConnection.Close()
End Sub
</script>
Источник — «http://nfex.ru/index.php?title=ASP.Net/ADO.net_Database/SqlDataReader&oldid=1665»