ASP.Net/ADO.net Database/ObjectDataSource
Содержание
- 1 Binding to a LINQ to SQL Query
- 2 Binding to a Web Service
- 3 Concurrency and the ObjectDataSource Control, ConflictDetection: CompareAllValues / OverwriteChanges
- 4 Creating a Custom ObjectDataSource Control
- 5 Creating a Page Property Parameter
- 6 Creating Custom Parameter Objects
- 7 Define your own collection for ObjectDataSource
- 8 Filtering Data
- 9 GridView with ObjectDataSource
- 10 Handling Method Errors
- 11 Insert Product
- 11.1 Handling ObjectDataSource Control Events
- 11.2 Handling the Object Creating Event
- 11.3 ObjectDataSource and backend database
- 11.4 ObjectDataSource based on XML
- 11.5 objectdatasource with control parameter
- 11.6 ObjectDataSource with selectmethod, deletemethod, updatemethod, insertmethod
- 11.7 Passing Objects as Parameters
- 11.8 Using Different Parameter Types
- 11.9 Using Parameters with the ObjectDataSource Control
Binding to a LINQ to SQL Query
<source lang="csharp">
File: Employee.cs using System.Collections.Generic; using System.Linq; using System.Data.Linq;
public class Employee { public string _firstName; public string _lastName; public bool _retired; public string FirstName { get { return _firstName; } } public string LastName { get { return _lastName; } } public bool Retired { get { return _retired; } } public Employee(string firstName, string lastName, bool retired) { _firstName = firstName; _lastName = lastName; _retired = retired; } public static IEnumerable<Employee> Select() { EmployeesDataContext db = new EmployeesDataContext(); return db.Employees.OrderBy( e=>e.LastName ); } }
File: Default.aspx <%@ Page Language="C#" %> <!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 runat="server">
<title>Show LINQ</title>
</head> <body>
<form id="form1" runat="server">
<asp:GridView id="grdEmployees" DataSourceID="srcEmployees" runat="server" /> <asp:ObjectDataSource id="srcEmployees" TypeName="Employee" SelectMethod="Select" Runat="server" />
</form>
</body> </html>
</source>
Binding to a Web Service
<source lang="csharp">
File: TimeService.asmx <%@ WebService Language="C#" Class="TimeService" %> using System; using System.Web; using System.Web.Services; using System.Web.Services.Protocols; [WebService(Namespace = "http://www.nfex.ru/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] public class TimeService : System.Web.Services.WebService {
[WebMethod] public DateTime GetServerTime() { return DateTime.Now; }
} File: Default.aspx <%@ Page Language="C#" %> <html xmlns="http://www.w3.org/1999/xhtml" > <body>
<form id="form1" runat="server">
<asp:FormView id="frmServerTime" DataSourceID="srcServerTime" CssClass="serverTime" Runat="server"> <ItemTemplate> The remote server date and time is: <%# Container.DataItem %> </ItemTemplate> </asp:FormView> <asp:ObjectDataSource id="srcServerTime" TypeName="TimeService" SelectMethod="GetServerTime" Runat="server" />
</form>
</body> </html>
</source>
Concurrency and the ObjectDataSource Control, ConflictDetection: CompareAllValues / OverwriteChanges
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
protected void srcProducts_Updated(object sender, ObjectDataSourceStatusEventArgs e) { if (e.Exception != null) { e.ExceptionHandled = true; lblError.Text = "Could not update record"; } }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Show Conflict Detection</title>
</head> <body>
<form id="form1" runat="server">
<asp:Label id="lblError" EnableViewState="false" CssClass="error" Runat="server" /> <asp:GridView id="grdProducts" DataSourceID="srcProducts" DataKeyNames="Id" AutoGenerateEditButton="true" Runat="server" /> <asp:ObjectDataSource id="srcProducts" ConflictDetection="CompareAllValues" OldValuesParameterFormatString="original_{0}" TypeName="ConflictedProducts" SelectMethod="GetProducts" UpdateMethod="UpdateProduct" OnUpdated="srcProducts_Updated" Runat="server" />
</form>
</body> </html> File: ConflictedProducts.cs using System; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; public class ConflictedProducts {
private static readonly string _conString; public static SqlDataReader GetProducts() { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "SELECT Id,Title,Director FROM Products"; con.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } public static void UpdateProduct(string title, string director, string original_title, string original_director, int original_id) { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "UPDATE Products SET Title=@Title,Director=@Director" + " WHERE Id=@original_Id AND Title=@original_Title AND Director=@original_Director"; cmd.Parameters.AddWithValue("@Title", title); cmd.Parameters.AddWithValue("@Director", director); cmd.Parameters.AddWithValue("@original_Id", original_id); cmd.Parameters.AddWithValue("@original_Title", original_title); cmd.Parameters.AddWithValue("@original_Director", original_director); using (con) { con.Open(); int rowsAffected = cmd.ExecuteNonQuery(); if (rowsAffected == 0) throw new Exception("Could not update Product record"); } } static ConflictedProducts() { _conString = WebConfigurationManager.ConnectionStrings["Products"]. ConnectionString; }
} File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration>
</source>
Creating a Custom ObjectDataSource Control
<source lang="csharp">
File: ProductDataSource.cs using System; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; using System.Web.UI.WebControls; namespace CustomControl.Samples {
public class ProductDataSource : ObjectDataSource { public ProductDataSource() { this.TypeName = "CustomControl.Samples.ProductsComponent"; this.SelectMethod = "GetProducts"; } } public class ProductsComponent { private readonly string _conString; public SqlDataReader GetProducts() { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "SELECT Title,Director,DateReleased FROM Products"; // Execute command con.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } public ProductsComponent() { _conString = WebConfigurationManager.ConnectionStrings["Products"]. ConnectionString; } }
} File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration>
File: Default.aspx
<%@ Page Language="C#" %> <%@ Register TagPrefix="custom" Namespace="CustomControl.Samples" %> <!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 id="Head1" runat="server">
<title>Show Product DataSource</title>
</head> <body>
<form id="form1" runat="server">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" Runat="server" /> <custom:ProductDataSource id="srcProducts" Runat="server" />
</form>
</body> </html>
</source>
Creating a Page Property Parameter
<source lang="csharp">
using System; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace MyControls {
public class PagePropertyParameter : Parameter { private string _propertyName; protected override object Evaluate(HttpContext context, Control control) { return DataBinder.Eval(control.Page, PropertyName); } public string PropertyName { get { return _propertyName; } set { _propertyName = value; } } }
}
File: ShowPagePropertyParameter.aspx <%@ Page Language="C#" %> <%@ Register TagPrefix="custom" Namespace="MyControls" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
Public ReadOnly Property CurrentUsername() As String Get Return User.Identity.Name End Get End Property
</script> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server">
<title>Show Page Property Parameter</title>
</head> <body>
<form id="form1" runat="server"> <asp:FormView id="frmGuestbook" DataSourceID="srcGuestbook" DefaultMode="Insert" Runat="server"> <InsertItemTemplate> <asp:Label ID="lblComment" Text="Comment:" AssociatedControlID="txtComment" Runat="server" /> <asp:TextBox id="txtComment" Text="<%# Bind("comment") %>" TextMode="MultiLine" Runat="server" /> <asp:Button id="btnInsert" Text="Add Entry" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView> <asp:GridView id="grdGuestbook" DataSourceID="srcGuestbook" CssClass="guestbook" Runat="server" /> <asp:ObjectDataSource id="srcGuestbook" TypeName="YourDataSource" SelectMethod="Getter" Runat="server"> <InsertParameters> <custom:PagePropertyParameter Name="Username" PropertyName="CurrentUsername" /> </InsertParameters> </asp:ObjectDataSource> </form>
</body> </html>
</source>
Creating Custom Parameter Objects
<source lang="csharp">
File: UsernameParameter.cs using System; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace MyControls {
public class UsernameParameter : Parameter { protected override object Evaluate(HttpContext context, Control control) { if (context != null) return context.User.Identity.Name; else return null; } }
}
File: Default.aspx <%@ Page Language="C#" %> <%@ Register TagPrefix="custom" Namespace="MyControls" %> <!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 id="Head1" runat="server">
<title>Show Username Parameter</title>
</head> <body>
<form id="form1" runat="server"> <asp:FormView id="frmGuestbook" DataSourceID="srcGuestbook" DefaultMode="Insert" Runat="server"> <InsertItemTemplate> <asp:Label ID="lblComment" Text="Comment:" AssociatedControlID="txtComment" Runat="server" /> <asp:TextBox id="txtComment" Text="<%# Bind("comment") %>" TextMode="MultiLine" Columns="50" Rows="4" Runat="server" /> <asp:Button id="btnInsert" Text="Add Entry" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView> <asp:GridView id="grdGuestbook" DataSourceID="srcGuestbook" CssClass="guestbook" Runat="server" /> <asp:ObjectDataSource id="srcGuestbook" TypeName="YourDataSource" SelectMethod="Getter" Runat="server"> <InsertParameters> <custom:UsernameParameter name="username" /> </InsertParameters> </asp:ObjectDataSource> </form>
</body> </html>
</source>
Define your own collection for ObjectDataSource
<source lang="csharp">
<%@ page language="C#" %> <%@ import namespace="System" %> <%@ import namespace="System.Web" %> <%@ import namespace="System.Collections.Generic" %> <script runat="server" language="c#"> public class Person {
private int id; private string firstname; private string lastname; public Person(int id, string firstname, string lastname) { this.id = id; this.firstname = firstname; this.lastname = lastname; } public int Id { get { return this.id; } set { this.id = value; } } public string Firstname { get { return this.firstname; } set { this.firstname = value; } } public string Lastname { get { return this.lastname; } set { this.lastname = value; } }
} public class PersonCollection : List<Person> {
public void Remove(int id) { Person person = this.FindPersonById(id); if (person != null) { base.Remove(person); } } public Person FindPersonById(int id) { foreach (Person person in this) { if (person.Id.Equals(id)) { return person; } } return null; }
} public class PersonManager {
private const string personsKey = "persons"; public PersonCollection SelectPersons() { HttpContext context = HttpContext.Current; if (context.Application[personsKey] == null) { PersonCollection persons = new PersonCollection(); persons.Add(new Person(0, "A", "B")); persons.Add(new Person(1, "C", "D")); persons.Add(new Person(2, "E", "F")); context.Application[personsKey] = persons; } return (context.Application[personsKey] as PersonCollection); }
} </script> <html> <head runat="server">
<title>Untitled Page</title>
</head> <body>
<form runat="server"> <asp:gridview id="GridView1" runat="server" datasourceid="ObjectDataSource1"> <alternatingrowstyle backcolor="Red"> </alternatingrowstyle> <pagerstyle forecolor="Black" font-italic="False" font-bold="False" horizontalalign="Center" backcolor="#999999"> </pagerstyle> <selectedrowstyle forecolor="White" backcolor="#008A8C" font-italic="False" font-bold="True"> </selectedrowstyle> <rowstyle forecolor="Black" backcolor="#EEEEEE" font-italic="False" font-bold="False"> </rowstyle> <headerstyle forecolor="White" backcolor="#000084" font-italic="False" font-bold="True"> </headerstyle> <footerstyle forecolor="Black" backcolor="#CCCCCC" font-italic="False" font-bold="False"> </footerstyle> </asp:gridview> <asp:objectdatasource id="ObjectDataSource1" runat="server" typename="PersonManager" selectmethod="SelectPersons"> </asp:objectdatasource> </form>
</body> </html>
</source>
Filtering Data
<source lang="csharp">
<%@ Page Language="C#" %> <!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 id="Head1" runat="server">
<title>Show Filtered Products</title>
</head> <body>
<form id="form1" runat="server"> <asp:DropDownList id="ddlProductCategory" DataSourceID="srcProductCategories" DataTextField="Name" DataValueField="Id" Runat="server" /> <asp:Button id="btnSelect" Text="Select" Runat="server" /> <asp:GridView id="grdProducts" DataSourceID="srcProducts" AutoGenerateColumns="false" Runat="server"> <Columns> <asp:BoundField DataField="Title" HeaderText="Product Title" /> <asp:BoundField DataField="Director" HeaderText="Product Director" /> </Columns> </asp:GridView> <asp:ObjectDataSource id="srcProductCategories" TypeName="FilterProducts" SelectMethod="GetProductCategories" EnableCaching="true" CacheDuration="Infinite" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="FilterProducts" SelectMethod="GetProducts" EnableCaching="true" CacheDuration="Infinite" FilterExpression="CategoryID={0}" Runat="server"> <FilterParameters> <asp:ControlParameter Name="Category" ControlID="ddlProductCategory" /> </FilterParameters> </asp:ObjectDataSource> </form>
</body> </html>
File: FilterProducts.cs using System; using System.Web; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; public class FilterProducts {
private readonly string _conString; public DataSet GetProducts() { SqlConnection con = new SqlConnection(_conString); string commandText = "SELECT Title,Director,CategoryId FROM Products"; SqlDataAdapter dad = new SqlDataAdapter(commandText, con); DataSet dstProducts = new DataSet(); using (con) { dad.Fill(dstProducts); } return dstProducts; } public DataSet GetProductCategories() { SqlConnection con = new SqlConnection(_conString); string commandText = "SELECT Id,Name FROM ProductCategories"; SqlDataAdapter dad = new SqlDataAdapter(commandText, con); DataSet dstCategories = new DataSet(); using (con) { dad.Fill(dstCategories); } return dstCategories; } public FilterProducts() { _conString = WebConfigurationManager.ConnectionStrings["Products"]. ConnectionString; }
}
</source>
GridView with ObjectDataSource
<source lang="csharp">
<%@ page language="C#" %> <%@ import namespace="System" %> <%@ import namespace="System.Web" %> <%@ import namespace="System.Collections.Generic" %> <script runat="server" language="c#"> public class PersonManager {
private const string personsKey = "persons"; public PersonCollection SelectPersons() { HttpContext context = HttpContext.Current; if (context.Application[personsKey] == null) { PersonCollection persons = new PersonCollection(); persons.Add(new Person(0, "A", "B")); persons.Add(new Person(1, "C", "D")); persons.Add(new Person(2, "E", "F")); context.Application[personsKey] = persons; } return (context.Application[personsKey] as PersonCollection); } public Person SelectPerson(int id) { return this.SelectPersons().FindPersonById(id); } public void DeletePerson(int Id) { HttpContext context = HttpContext.Current; PersonCollection persons = (context.Application[personsKey] as PersonCollection); persons.Remove(Id); } public void Update(int Id, string Firstname, string Lastname) { HttpContext context = HttpContext.Current; PersonCollection persons = (context.Application[personsKey] as PersonCollection); Person person = persons.FindPersonById(Id); if (person != null) { person.Firstname = Firstname; person.Lastname = Lastname; } }
}
public class PersonCollection : List<Person> {
public void Remove(int id) { Person person = this.FindPersonById(id); if (person != null) { base.Remove(person); } } public Person FindPersonById(int id) { foreach (Person person in this) { if (person.Id.Equals(id)) { return person; } } return null; }
}
public class Person {
private int id; private string firstname; private string lastname; public Person(int id, string firstname, string lastname) { this.id = id; this.firstname = firstname; this.lastname = lastname; } public int Id { get { return this.id; } set { this.id = value; } } public string Firstname { get { return this.firstname; } set { this.firstname = value; } } public string Lastname { get { return this.lastname; } set { this.lastname = value; } }
} </script> <html> <head id="Head1" runat="server">
<title>Untitled Page</title>
</head> <body>
<form id="Form1" runat="server"> <asp:gridview id="GridView1" runat="server" datasourceid="ObjectDataSource1" autogeneratecolumns="False" datakeynames="Id"> <alternatingrowstyle backcolor="Red" font-bold="False"> </alternatingrowstyle> <pagerstyle forecolor="Black" font-italic="False" font-bold="False" horizontalalign="Center" backcolor="#999999"> </pagerstyle> <columnfields> <asp:boundfield datafield="Id" readonly="True" headertext="ID"> </asp:boundfield> <asp:boundfield datafield="Firstname" headertext="Firstname"> </asp:boundfield> <asp:boundfield datafield="Lastname" headertext="Lastname"> </asp:boundfield> <asp:commandfield showeditbutton="True"> </asp:commandfield> <asp:commandfield showdeletebutton="True"> </asp:commandfield> </columnfields> <summarytitlestyle borderwidth="1px" borderstyle="None" bordercolor="#999999" backcolor="White"> </summarytitlestyle> <selectedrowstyle forecolor="White" backcolor="#008A8C" font-italic="False" font-bold="True"> </selectedrowstyle> <detailtitlestyle borderwidth="1px" borderstyle="None" bordercolor="#999999" backcolor="White"> </detailtitlestyle> <rowstyle forecolor="Black" backcolor="#EEEEEE" font-italic="False" font-bold="False"> </rowstyle> <headerstyle forecolor="White" backcolor="#000084" font-italic="False" font-bold="True"> </headerstyle> <footerstyle forecolor="Black" backcolor="#CCCCCC" font-italic="False" font-bold="False"> </footerstyle> </asp:gridview> <asp:objectdatasource id="ObjectDataSource1" runat="server" typename="PersonManager" selectmethod="SelectPersons" updatemethod="Update" deletemethod="DeletePerson"> </asp:objectdatasource> </form>
</body> </html>
</source>
Handling Method Errors
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
protected void srcProducts_Inserted(object sender, ObjectDataSourceStatusEventArgs e) { if (e.Exception != null) { e.ExceptionHandled = true; lblError.Text = "Could not insert Product"; } }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> </head> <body>
<form id="form1" runat="server"> <asp:Label id="lblError" EnableViewState="false" CssClass="error" Runat="server" />
Insert Product
<asp:DetailsView id="dtlProducts" DataSourceID="srcProducts" DefaultMode="Insert" AutoGenerateInsertButton="true" AutoGenerateRows="false" CssClass="insertForm" GridLines="None" Runat="server"> <Fields> <asp:BoundField DataField="Title" HeaderText="Title:"/> <asp:BoundField DataField="Director" HeaderText="Director:" /> </Fields> </asp:DetailsView> <asp:ObjectDataSource id="srcProducts" TypeName="InsertProduct" InsertMethod="Insert" Runat="server" OnInserted="srcProducts_Inserted" /> </div> </form>
</body> </html>
File: InsertProduct.cs using System; using System.Web; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; public class InsertProduct {
private static readonly string _conString; public static SqlDataReader GetProducts() { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "SELECT Id,Title,Director FROM Products"; con.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } public static void Insert(string title, string director) { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "INSERT Products (Title,Director)" + " VALUES (@Title,@Director)"; cmd.Parameters.AddWithValue("@Title", title); cmd.Parameters.AddWithValue("@Director", director); using (con) { con.Open(); cmd.ExecuteNonQuery(); } } static InsertProduct() { _conString = WebConfigurationManager.ConnectionStrings["Products"]. ConnectionString; }
}
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration>
</source>
Handling ObjectDataSource Control Events
<source lang="csharp">
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
protected void srcGuestbook_Inserting(object sender, ObjectDataSourceMethodEventArgs e) { e.InputParameters.Add("IPAddress", Request.UserHostAddress); }
</script> <html xmlns="http://www.w3.org/1999/xhtml"> <body>
<form id="form1" runat="server">
<asp:FormView id="frmGuestbook" DataSourceID="srcGuestbook" DefaultMode="Insert" Runat="server"> <InsertItemTemplate> <asp:Label ID="lblComment" Text="Comment:" AssociatedControlID="txtComment" Runat="server" /> <asp:TextBox id="txtComment" Text="<%# Bind("comment") %>" TextMode="MultiLine" Columns="50" Rows="4" Runat="server" /> <asp:Button id="btnInsert" Text="Add Entry" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView> <asp:GridView id="grdGuestbook" DataSourceID="srcGuestbook" CssClass="guestbook" Runat="server" /> <asp:ObjectDataSource id="srcGuestbook" TypeName="Guestbook" SelectMethod="GetEntries" InsertMethod="AddEntry" OnInserting="srcGuestbook_Inserting" Runat="server" />
</form>
</body> </html> File: Guestbook.cs using System; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; public class Guestbook {
private string _conString; public SqlDataReader GetEntries() { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "SELECT Id,IPAddress,Comment,EntryDate FROM Guestbook"; con.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } public void AddEntry(string IPAddress, string comment) { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "INSERT Guestbook (IPAddress,Comment)" + " VALUES (@IPAddress, @Comment)"; cmd.Parameters.AddWithValue("@IPAddress", IPAddress); cmd.Parameters.AddWithValue("@Comment", comment); using (con) { con.Open(); cmd.ExecuteNonQuery(); } } public Guestbook() { _conString = WebConfigurationManager.ConnectionStrings["Guestbook"]. ConnectionString; }
} File: Web.config <configuration>
<connectionStrings> <add name="Guestbook" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration>
</source>
Handling the Object Creating Event
<source lang="csharp">
File: ProductsByCategory.cs using System; using System.Web; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; public class ProductsByCategory {
private readonly string _conString; private readonly string _ProductCategory; public SqlDataReader GetProducts() { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "SELECT Title,Director,DateReleased FROM Products" + " JOIN ProductCategories ON Products.CategoryId=ProductCategories.Id" + " WHERE ProductCategories.Name=@CategoryName"; cmd.Parameters.AddWithValue("@CategoryName", _ProductCategory); con.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } public ProductsByCategory(string ProductCategory) { _ProductCategory = ProductCategory; _conString = WebConfigurationManager.ConnectionStrings["Products"]. ConnectionString; }
} File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration> File: Default.aspx <%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">
protected void srcProducts_ObjectCreating(object sender, ObjectDataSourceEventArgs e) { ProductsByCategory Products = new ProductsByCategory("Adventure"); e.ObjectInstance = Products; }
</script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Adventure Products</title>
</head> <body>
<form id="form1" runat="server"> <asp:GridView id="grdProducts" DataSourceID="srcProducts" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="ProductsByCategory" SelectMethod="GetProducts" OnObjectCreating="srcProducts_ObjectCreating" Runat="server" /> </div> </form>
</body> </html>
</source>
ObjectDataSource and backend database
<source lang="csharp">
<%@ Page Language="VB" %> <!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"> <asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"> <Columns> <asp:CommandField ShowEditButton="True" /> </Columns> </asp:GridView> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="getAuthors" TypeName="Authors" UpdateMethod="updateAuthors"> <UpdateParameters> <asp:Parameter Name="au_id" Type="String" /> <asp:Parameter Name="au_fname" Type="String" /> <asp:Parameter Name="au_lname" Type="String" /> </UpdateParameters> </asp:ObjectDataSource> </div> </form>
</body> </html> File: ~\App_Code\Authors.cs Imports System.Data.SqlClient Imports System.Data Imports Microsoft.VisualBasic Public Class Authors
Public Function getAuthors() As DataSet Dim conn As New SqlConnection( ConfigurationManager.ConnectionStrings("pubsConnectionString").ConnectionString) Dim adapter As New SqlDataAdapter( "SELECT au_id, au_fname, au_lname FROM Authors", conn) Dim ds As New DataSet adapter.Fill(ds, "Authors") Return ds End Function Public Sub updateAuthors(ByVal au_id As String, ByVal au_fname As String, ByVal au_lname As String) Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings ("pubsConnectionString").ConnectionString) Dim adapter As New SqlDataAdapter( "SELECT au_id, au_fname, au_lname " & "FROM Authors WHERE au_id=@au_id", conn) Dim ds As New DataSet adapter.SelectCommand.Parameters.Add( "@au_id", SqlDbType.NVarChar, 11).Value = au_id adapter.Fill(ds, "Authors") With ds.Tables(0).Rows(0) .Item("au_fname") = au_fname .Item("au_lname") = au_lname End With Dim cb As New SqlCommandBuilder(adapter) adapter.Update(ds, "Authors") End Sub
End Class
</source>
ObjectDataSource based on XML
<source lang="csharp">
<%@ Page Language="C#" %> <!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 runat="server">
<title>Articles</title>
</head> <body>
<form id="form1" runat="server"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ArticlesODS"> <Columns> <asp:BoundField DataField="Year" HeaderText="Year" SortExpression="Year" /> <asp:BoundField DataField="Month" HeaderText="Month" SortExpression="Month" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" /> <asp:BoundField DataField="Content" HeaderText="Content" SortExpression="Content" /> </Columns> </asp:GridView> <asp:ObjectDataSource ID="ArticlesODS" runat="server" SelectMethod="GetArticles" TypeName="Articles"> <SelectParameters> <asp:QueryStringParameter DefaultValue="2006" Name="year" QueryStringField="year" Type="String" /> <asp:QueryStringParameter DefaultValue="01" Name="month" QueryStringField="month" Type="String" /> </SelectParameters> </asp:ObjectDataSource> </form>
</body> </html> File: ArticleData.cs using System; using System.Collections.Generic; class ArticleData {
public void GetArticles(List<Article> articles, string year, string month) { DataSet dsArticles = new DataSet(); dsArticles.ReadXml(HttpContext.Current.Server.MapPath("Data.xml")); DataView dvArticles = new DataView(dsArticles.Tables["article"]); dvArticles.RowFilter = "year = "" + year + "" " + "and month = "" + month + """; Article currArticle = null; IEnumerator articleRows = dvArticles.GetEnumerator(); while (articleRows.MoveNext()) { DataRowView articleRow = (DataRowView)articleRows.Current; currArticle = new Article( (string)articleRow["year"], (string)articleRow["month"], (string)articleRow["title"], (string)articleRow["content"]); articles.Add(currArticle); } }
}
class Article {
private string m_year; public string Year { get { return m_year; } set { m_year = value; } } private string m_month; public string Month { get { return m_month; } set { m_month = value; } } private string m_title; public string Title { get { return m_title; } set { m_title = value; } } private string m_content; public string Content { get { return m_content; } set { m_content = value; } } public Article(string year, string month, string title, string content) { Year = year; Month = month; Title = title; Content = content; }
} public class Articles : List<Article> {
public List<Article> GetArticles(string year, string month) { ArticleData dal = new ArticleData(); dal.GetArticles(this, year, month); return this; }
} File: Data.xml <?xml version="1.0" encoding="utf-8" ?> <articles>
<article> <year>2005</year> <month>05</month> <title>Title6</title> <content>This is the text of Title6.</content> </article> <article> <year>2005</year> <month>06</month> <title>Title7</title> <content>This is the text of Title7.</content> </article>
</articles>
</source>
objectdatasource with control parameter
<source lang="csharp">
<%@ page language="C#" %>
<%@ import namespace="System" %> <%@ import namespace="System.Web" %> <%@ import namespace="System.Collections.Generic" %> <script runat="server" language="c#"> public class Person {
private int id; private string firstname; private string lastname; public Person(int id, string firstname, string lastname) { this.id = id; this.firstname = firstname; this.lastname = lastname; } public int Id { get { return this.id; } set { this.id = value; } } public string Firstname { get { return this.firstname; } set { this.firstname = value; } } public string Lastname { get { return this.lastname; } set { this.lastname = value; } }
}
public class PersonCollection : List<Person> {
public void Remove(int id) { Person person = this.FindPersonById(id); if (person != null) { base.Remove(person); } } public Person FindPersonById(int id) { foreach (Person person in this) { if (person.Id.Equals(id)) { return person; } } return null; }
}
public class PersonManager {
private const string personsKey = "persons"; public PersonCollection SelectPersons() { HttpContext context = HttpContext.Current; if (context.Application[personsKey] == null) { PersonCollection persons = new PersonCollection(); persons.Add(new Person(0, "A", "B")); persons.Add(new Person(1, "C", "D")); persons.Add(new Person(2, "E", "F")); context.Application[personsKey] = persons; } return (context.Application[personsKey] as PersonCollection); } public Person SelectPerson(int id) { return this.SelectPersons().FindPersonById(id); }
}
</script> <html> <head runat="server">
<title>Untitled Page</title>
</head> <body>
<form runat="server"> ID: <asp:textbox id="TextBox1" runat="server">0</asp:textbox> <asp:button id="Button1" runat="server" text="Show Person" /> <asp:detailsview id="DetailsView1" runat="server" datasourceid="ObjectDataSource1"> </asp:detailsview> <asp:objectdatasource id="ObjectDataSource1" runat="server" typename="PersonManager" selectmethod="SelectPerson"> <selectparameters> <asp:controlparameter name="id" propertyname="Text" type="Int32" controlid="TextBox1"> </asp:controlparameter> </selectparameters> </asp:objectdatasource> </form>
</body> </html>
</source>
ObjectDataSource with selectmethod, deletemethod, updatemethod, insertmethod
<source lang="csharp">
<%@ page language="C#" %> <%@ import namespace="System" %> <%@ import namespace="System.Web" %> <%@ import namespace="System.Collections.Generic" %>
<script runat="server" language="c#"> public class Person {
private int id; private string firstname; private string lastname; public Person(int id, string firstname, string lastname) { this.id = id; this.firstname = firstname; this.lastname = lastname; } public int Id { get { return this.id; } set { this.id = value; } } public string Firstname { get { return this.firstname; } set { this.firstname = value; } } public string Lastname { get { return this.lastname; } set { this.lastname = value; } }
}
public class PersonManager {
private const string personsKey = "persons"; public PersonCollection SelectPersons() { HttpContext context = HttpContext.Current; if (context.Application[personsKey] == null) { PersonCollection persons = new PersonCollection(); persons.Add(new Person(0, "A", "B")); persons.Add(new Person(1, "C", "D")); persons.Add(new Person(2, "E", "F")); context.Application[personsKey] = persons; } return (context.Application[personsKey] as PersonCollection); } public Person SelectPerson(int id) { return this.SelectPersons().FindPersonById(id); } public void DeletePerson(int Id) { HttpContext context = HttpContext.Current; PersonCollection persons = (context.Application[personsKey] as PersonCollection); persons.Remove(Id); } public void Update(int Id, string Firstname, string Lastname) { HttpContext context = HttpContext.Current; PersonCollection persons = (context.Application[personsKey] as PersonCollection); Person person = persons.FindPersonById(Id); if (person != null) { person.Firstname = Firstname; person.Lastname = Lastname; } } public void Insert(int Id, string Firstname, string Lastname) { HttpContext context = HttpContext.Current; PersonCollection persons = (context.Application[personsKey] as PersonCollection); persons.Add(new Person(Id, Firstname, Lastname)); }
} public class PersonCollection : List<Person> {
public void Remove(int id) { Person person = this.FindPersonById(id); if (person != null) { base.Remove(person); } } public Person FindPersonById(int id) { foreach (Person person in this) { if (person.Id.Equals(id)) { return person; } } return null; }
} </script>
<html> <head id="Head1" runat="server">
<title>Untitled Page</title>
</head> <body>
<form id="Form1" runat="server"> <asp:gridview id="GridView1" runat="server" allowpaging="True" datasourceid="ObjectDataSource1" allowsorting="True" datakeynames="Id" autogeneratecolumns="False"> <alternatingrowstyle font-italic="False" font-bold="False"> </alternatingrowstyle> <pagerstyle forecolor="#003399" font-italic="False" font-bold="False" horizontalalign="Left" backcolor="#99CCCC"> </pagerstyle> <columnfields> <asp:boundfield datafield="Id" readonly="True"> </asp:boundfield> <asp:boundfield datafield="Firstname"> </asp:boundfield> <asp:boundfield datafield="Lastname"> </asp:boundfield> <asp:commandfield showselectbutton="True" showdeletebutton="True" showeditbutton="True"> </asp:commandfield> </columnfields> <summarytitlestyle borderwidth="1px" borderstyle="None" bordercolor="#3366CC" backcolor="White"> </summarytitlestyle> <selectedrowstyle forecolor="#CCFF99" backcolor="#009999" font-italic="False" font-bold="True"> </selectedrowstyle> <detailtitlestyle borderwidth="1px" borderstyle="None" bordercolor="#3366CC" backcolor="White"> </detailtitlestyle> <rowstyle forecolor="#003399" backcolor="White" font-italic="False" font-bold="False"> </rowstyle> <headerstyle forecolor="#CCCCFF" backcolor="#003399" font-italic="False" font-bold="True"> </headerstyle> <footerstyle forecolor="#003399" backcolor="#99CCCC" font-italic="False" font-bold="False"> </footerstyle> </asp:gridview> <asp:objectdatasource id="ObjectDataSource1" runat="server" typename="PersonManager" selectmethod="SelectPersons" deletemethod="DeletePerson" updatemethod="Update" insertmethod="Insert"> <insertparameters> <asp:parameter name="Id" type="Int32"> </asp:parameter> </insertparameters> </asp:objectdatasource> <asp:detailsview id="DetailsView1" runat="server" datasourceid="ObjectDataSource1" defaultmode="Insert" autogeneraterows="False" datakeynames="Id"> <rowfields> <asp:boundfield datafield="Id" headertext="ID:"> </asp:boundfield> <asp:boundfield datafield="Firstname" headertext="Fn:"> </asp:boundfield> <asp:boundfield datafield="Lastname" headertext="Ln:"> </asp:boundfield> <asp:commandfield showinsertbutton="True"> </asp:commandfield> </rowfields> </asp:detailsview> </form>
</body> </html>
</source>
Passing Objects as Parameters
<source lang="csharp">
File: EmployeeData.cs using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Web.Configuration; public class EmployeeData {
string _connectionString; public void UpdateEmployee(CompanyEmployee employeeToUpdate) { SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand(); cmd.rumandText = "UPDATE Employees SET FirstName=@FirstName," + "LastName=@LastName,Phone=@Phone WHERE Id=@Id"; cmd.Connection = con; cmd.Parameters.AddWithValue("@Id", employeeToUpdate.Id); cmd.Parameters.AddWithValue("@FirstName", employeeToUpdate.FirstName); cmd.Parameters.AddWithValue("@LastName", employeeToUpdate.LastName); cmd.Parameters.AddWithValue("@Phone", employeeToUpdate.Phone); using (con) { con.Open(); cmd.ExecuteNonQuery(); } } public List<CompanyEmployee> GetEmployees() { List<CompanyEmployee> employees = new List<CompanyEmployee>(); SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand(); cmd.rumandText = "SELECT Id,FirstName,LastName,Phone FROM Employees"; cmd.Connection = con; using (con) { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { CompanyEmployee newEmployee = new CompanyEmployee(); newEmployee.Id = (int)reader["Id"]; newEmployee.FirstName = (string)reader["FirstName"]; newEmployee.LastName = (string)reader["LastName"]; newEmployee.Phone = (string)reader["Phone"]; employees.Add(newEmployee); } } return employees; } public EmployeeData() { _connectionString = WebConfigurationManager.ConnectionStrings["Employees"]. ConnectionString; }
} public class CompanyEmployee {
private int _id; private string _firstName; private string _lastName; private string _phone; public int Id { get { return _id; } set { _id = value; } } public string FirstName { get { return _firstName; } set { _firstName = value; } } public string LastName { get { return _lastName; } set { _lastName = value; } } public string Phone { get { return _phone; } set { _phone = value; } }
}
File: Web.config <configuration>
<connectionStrings> <add name="Employees" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration> File: Default.aspx <%@ Page Language="C#" %> <!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 id="Head1" runat="server">
<title>Update Employees</title>
</head> <body>
<form id="form1" runat="server">
<asp:DetailsView ID="DetailsView1" DataSourceID="srcEmployees" DataKeyNames="Id" AutoGenerateRows="True" AutoGenerateEditButton="True" AllowPaging="true" Runat="server" /> <asp:ObjectDataSource id="srcEmployees" TypeName="EmployeeData" DataObjectTypeName="CompanyEmployee" SelectMethod="GetEmployees" UpdateMethod="UpdateEmployee" Runat="server" />
</form>
</body> </html>
</source>
Using Different Parameter Types
<source lang="csharp">
<%@ Page Language="C#" %> <!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" > <body>
<form id="form1" runat="server"> <asp:DropDownList id="ddlProductCategory" DataSourceID="srcProductCategories" DataTextField="Name" DataValueField="Id" ToolTip="Product Category" Runat="server" /> <asp:Button id="btnSelect" Text="Select" Runat="server" /> <asp:GridView id="grdProducts" DataSourceID="srcProducts" CssClass="Products" GridLines="None" Runat="server" /> <asp:ObjectDataSource id="srcProductCategories" TypeName="ProductCategories" SelectMethod="GetCategories" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="ProductCategories" SelectMethod="GetProducts" Runat="server"> <SelectParameters> <asp:ControlParameter Name="CategoryId" ControlID="ddlProductCategory" /> </SelectParameters> </asp:ObjectDataSource> </form>
</body> </html>
File: ProductCategories.cs using System; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; public class ProductCategories {
private readonly string _conString; public SqlDataReader GetProducts(int categoryId) { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "SELECT Id,Title,Director,DateReleased " + " FROM Products WHERE CategoryId=@CategoryId"; cmd.Parameters.AddWithValue("@CategoryId", categoryId); con.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } public SqlDataReader GetCategories() { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "SELECT Id,Name FROM ProductCategories"; con.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } public ProductCategories() { _conString = WebConfigurationManager.ConnectionStrings["Products"]. ConnectionString; }
} File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration>
</source>
Using Parameters with the ObjectDataSource Control
<source lang="csharp">
using System; using System.Data; using System.Data.SqlClient; using System.Web.Configuration; public class Products {
private readonly string _conString; public void UpdateProduct(int id, string title, string director, DateTime dateReleased) { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "UPDATE Products SET Title=@Title,Director=@Director,DateReleased= cmd.Parameters.AddWithValue("@Title", title); cmd.Parameters.AddWithValue("@Director", director); cmd.Parameters.AddWithValue("@DateReleased", dateReleased); cmd.Parameters.AddWithValue("@Id", id); using (con) { con.Open(); cmd.ExecuteNonQuery(); } } public SqlDataReader GetProducts() { SqlConnection con = new SqlConnection(_conString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.rumandText = "SELECT Id,Title,Director,DateReleased FROM Products"; con.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } public Products() { _conString = WebConfigurationManager.ConnectionStrings["Products"]. ConnectionString; }
}
File: Web.config <configuration>
<connectionStrings> <add name="Products" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" /> </connectionStrings>
</configuration> File: Default.aspx <%@ Page Language="C#" %> <!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 id="Head1" runat="server">
<title>Show Products</title>
</head> <body>
<form id="form1" runat="server">
<asp:GridView id="grdProducts" DataSourceID="srcProducts" DataKeyNames="Id" AutoGenerateEditButton="true" Runat="server" /> <asp:ObjectDataSource id="srcProducts" TypeName="Products" SelectMethod="GetProducts" UpdateMethod="UpdateProduct" Runat="server"/>
</form>
</body> </html>
</source>