ASP.NET Tutorial/ADO.net Database/SqlDataAdapter

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

FillLoadOption property of the Fill method on data adapters.

<%@ 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>Fill Load Options</title>
</head>
<body>
    <div id="pageContent">
        <form id="form1" runat="server">
            <div>
                <table>
                    <tr>
                        <td><b>&nbsp;</b></td>
                        <td><b>ORIGINAL</b></td>
                        <td><b>CURRENT</b></td>
                    </tr>
                    <tr>
                        <td><b>BEFORE</b></td>
                        <td>
                            <asp:Label runat="server" ID="BeforeLoadOrig" text="Michela" />
                        </td>
                        <td>
                            <asp:Label runat="server" ID="BeforeLoadCurr" text="Sylvia" />
                        </td>
                    </tr>
                    <tr>
                        <td><b>AFTER</b></td>
                        <td>
                            <asp:Label runat="server" ID="AfterLoadOrig" />
                        </td>
                        <td>
                            <asp:Label runat="server" ID="AfterLoadCurr" />
                        </td>
                    </tr>
                </table>
                <br /><br />
                <asp:DropDownList ID="LoadOptionList" runat="server" AutoPostBack="True" OnSelectedIndexChanged="LoadOptions_SelectedIndexChanged">
                    <asp:ListItem>PreserveChanges</asp:ListItem>
                    <asp:ListItem>OverwriteChanges</asp:ListItem>
                    <asp:ListItem>Upsert</asp:ListItem>
                </asp:DropDownList>
                <asp:Button ID="RefreshButton" runat="server" Text="Load" OnClick="RefreshButton_Click" />
                <hr />
            </div>
        </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
{
  private DataSet _data;
  private string SelectCmd = "SELECT employeeid, firstname, lastname FROM employees";
  private void InitDataSet()
  {
    _data = new DataSet();    
    SqlDataAdapter adapter = new SqlDataAdapter(SelectCmd,ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    adapter.Fill(_data);
    DataTable t = _data.Tables[0];
    DataRow r = t.Rows[0];
    r["firstname"] = "Michela";
    r.AcceptChanges();
    t = _data.Tables[0];
    r = t.Rows[0];
    r["firstname"] = "Sylvia";
  }
  protected void RefreshButton_Click(object sender, EventArgs e)
  {
    InitDataSet();
    DataRow row = _data.Tables[0].Rows[0];
    BeforeLoadOrig.Text = row["firstname", DataRowVersion.Original].ToString();
    BeforeLoadCurr.Text = row["firstname", DataRowVersion.Current].ToString();
    SqlDataAdapter adapter = new SqlDataAdapter(
      SelectCmd,
            ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
    adapter.FillLoadOption = (LoadOption)Enum.Parse(typeof(LoadOption), LoadOptionList.SelectedValue);
    adapter.Fill(_data);
    AfterLoadOrig.Text = row["firstname", DataRowVersion.Original].ToString();
    AfterLoadCurr.Text = row["firstname", DataRowVersion.Current].ToString();
  }

  protected void LoadOptions_SelectedIndexChanged(object sender, EventArgs e)
  {
    AfterLoadOrig.Text = "";
    AfterLoadCurr.Text = "";
  }
}


Inline binding DataSet

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
    DataSet myDataSet = new DataSet();
    
    void Page_Load(object sender, EventArgs e) {
       string ConnectionString = Convert.ToString(ConfigurationSettings.AppSettings["MSDEConnectString"]);
       string CommandText = "SELECT * FROM Publisher";
    
       SqlConnection myConnection = new SqlConnection(ConnectionString);
       SqlCommand myCommand = new SqlCommand(CommandText, myConnection);
    
       SqlDataAdapter myAdapter = new SqlDataAdapter();
    
       myAdapter.SelectCommand = myCommand;
    
       try {
          myConnection.Open();
          myAdapter.Fill(myDataSet,"Publisher");
       } catch (Exception ex) {
          throw (ex);
       } finally {
          myConnection.Close();
       }
       Page.DataBind();
    
    }
</script>
<html>
<body>
    <form runat="server">
        
            <asp:Label id="lblName" runat="server"> Name
            : <%# DataBinder.Eval (myDataSet.Tables["Publisher"].Rows[0], "[PublisherName]") %> </asp:Label>
            <br />
            City: <asp:Label id="lblCity" runat="server" text="<%# DataBinder.Eval (myDataSet.Tables["Publisher"].Rows[0], "[PublisherCity]") %>"> </asp:Label>
            <br />
            Contact : 
            <asp:HyperLink id="hypEmail" runat="server" NavigateUrl="<%# DataBinder.Eval (myDataSet.Tables["Publisher"].Rows[0], "[PublisherContact_Email]", "mailto:{0}") %>" Text="<%# DataBinder.Eval (myDataSet.Tables["Publisher"].Rows[0], "[PublisherContact_Email]") %>"></asp:HyperLink>
            <br />
            Homesite: 
            <asp:HyperLink id="hypWebsite" runat="server" NavigateUrl="<%# DataBinder.Eval (myDataSet.Tables["Publisher"].Rows[0], "[PublisherWebsite]") %>">
                <%# DataBinder.Eval (myDataSet.Tables["Publisher"].Rows[0], "[PublisherWebsite]") %> 
            </asp:HyperLink>
        
        
            <asp:Label id="lblError" runat="server"></asp:Label>
        
    </form>
</body>
</html>

File: Web.config
<configuration>
    <appSettings>
        <add key="MSDEConnectString" value="server=(local)\YourDatabase;database=Books;uid=YourID;pwd=letmein;" />
    </appSettings>
</configuration>


Using an object of SqlDataAdapter to fill a DataTable (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;
            SqlDataAdapter MyAdapter;
            DataTable MyTable = new DataTable();
            MyConnection = new SqlConnection();
            MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;
            MyCommand = new SqlCommand();
            MyCommand.rumandText = " SELECT TOP 5 * FROM CUSTOMERS ";
            MyCommand.rumandType = CommandType.Text;
            MyCommand.Connection = MyConnection;
            MyAdapter = new SqlDataAdapter();
            MyAdapter.SelectCommand = MyCommand;
            MyAdapter.Fill(MyTable);
            gvCustomers.DataSource = MyTable.DefaultView;
            gvCustomers.DataBind();
            MyAdapter.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>


Using an object of SqlDataAdapter to fill a DataTable (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 MyAdapter As SqlDataAdapter
            Dim MyTable As DataTable = New DataTable()
            MyConnection = New SqlConnection()
            MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString
            MyCommand = New SqlCommand()
            MyCommand.rumandText = " SELECT TOP 5 * FROM CUSTOMERS "
            MyCommand.rumandType = CommandType.Text
            MyCommand.Connection = MyConnection
            MyAdapter = New SqlDataAdapter()
            MyAdapter.SelectCommand = MyCommand
            MyAdapter.Fill(MyTable)
            gvCustomers.DataSource = MyTable.DefaultView
            gvCustomers.DataBind()
            MyAdapter.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>
Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/SqlDataAdapter&oldid=2651»