ASP.NET Tutorial/ADO.net Database/SqlDataAdapter

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

FillLoadOption property of the Fill method on data adapters.

   <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>Fill Load Options</title>

</head> <body>

       <form id="form1" runat="server">
  ORIGINAL CURRENT
BEFORE
                           <asp:Label runat="server" ID="BeforeLoadOrig" text="Michela" />
                           <asp:Label runat="server" ID="BeforeLoadCurr" text="Sylvia" />
AFTER
                           <asp:Label runat="server" ID="AfterLoadOrig" />
                           <asp:Label runat="server" ID="AfterLoadCurr" />
               

<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" />

       </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 {

 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 = "";
 }

}</source>


Inline binding DataSet

   <source lang="csharp">

<%@ 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>
           
City: <asp:Label id="lblCity" runat="server" text="<%# DataBinder.Eval (myDataSet.Tables["Publisher"].Rows[0], "[PublisherCity]") %>"> </asp:Label>
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>
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></source>


Using an object of SqlDataAdapter to fill a DataTable (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;
           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">
       <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>


Using an object of SqlDataAdapter to fill a DataTable (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 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">
       <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>