ASP.NET Tutorial/ADO.net Database/SqlDataAdapter
Содержание
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>