Transcript Data Binding in .NET

DataSource controls in .NET
How to access databases
Where is that database?
• The physical database might be in many places
– On another server
• IP address + port number required
– On the same server, as the ASP.NET application
• If you work with a Microsoft SQL Server database put
the database file (somename.mdf) in an APP_DATA
folder in your ASP.NET project
Data source controls in .NET
2
DataSource Controls
• A DataSource control has access to a database
• AccessDataSource control
– Access to a Microsoft Access Database
• SqlDataSource control
– Access to a Microsoft SQL Server database
• Oracle
– http://www.oracle.com/technetwork/topics/dotnet/index085163.html
• MySQL
– http://www.mysql.com/downloads/connector/net/
– http://dev.mysql.com/tech-resources/articles/dotnet/
Data source controls in .NET
3
Asp:SqlDataSource examles
<asp:SqlDataSource ID="SqlDataSource1" runat="server“
SelectCommand="select * from member"
ConnectionString="Data Source=(local)\SQLEXPRESS;
Initial Catalog=myfirst;
Integrated Security=SSPI;">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2“ runat="server"
SelectCommand="select * from member"
ConnectionString="Data Source=(local)\SQLEXPRESS;
User Id=anders; Password=Secret12;">
</asp:SqlDataSource>
(local) is sometimes written . (”dot”)
Data source controls in .NET
4
Connection string
• Information needed to connect to the database
– DataSource=ServerName\ServerInstance
• Server name can be
– .
– (local)
– Localhost
» 127.0.0.1 didn’t work for me
– The name of the server machine
• Server instance
– Necessary if you have more than one instance of SQL Server on the server machine
» Example: SQLEXPRESS
– Catalog=myfirst
• The name of the database to connect to
– Security
• Integrated Security: Use Windows authentication
• Provide Username/password
• More information
– http://www.connectionstrings.com/sql-server-2008
Data source controls in .NET
5
Connection string in web.config
<configuration>
<connectionStrings>
…
<add name="myfirstConnectionString"
connectionString="Data Source=(local)\SQLEXPRESS;Initial Catalog=myfirst;
Integrated Security=SSPI;"
providerName="System.Data.SqlClient" />
</connectionStrings>
<asp:SqlDataSource ID="SqlDataSource4" runat="server"
SelectCommand="SELECT * FROM [member]"
ConnectionString="<%$ ConnectionStrings:myfirstConnectionString %>">
</asp:SqlDataSource>
Data source controls in .NET
6
Data Source Mode Property
• <asp:SqlDataSource … DataSourceMode=… >
• DataSourceMode can have two values
– DataSet
• Read-Write, forward/backwards
• Default value for the DataSourceMode property
• Allows filtering, sorting, and paging
– DataReader
• Read-only, forward-only
• Fastest, most effecient
Data source controls in .NET
7
Filtering data using SelectParameters
<asp:SqlDataSource ID="SqlDataSource3" runat="server"
SelectCommand="select * from member where memberID=@memberID“
ConnectionString="Data Source=(local)\SQLEXPRESS;
Initial Catalog=myfirst; Integrated Security=SSPI;">
<SelectParameters>
<asp:QueryStringParameter Name="memberID"
QueryStringField="memberID" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Data source controls in .NET
8
References
• George Shepherd ASP.NET 4 Step by Step, Microsoft Press 2010
– Chapter 10 Data Binding, page 221-226
• Imar Spaanjaars Beginning ASP.NET 4 in C# and VB, Wrox/Wiley 2010
– Appendix B Configuring SQL Server 2008, page 757-775
• Bill Evjen Professional ASP.NET 4 in C# and VB, Wrox/Wiley 2010
– Chapter 7 Data Binding, page 237-253
• MSDN AccessDataSource class
– http://msdn.microsoft.com/enus/library/system.web.ui.webcontrols.accessdatasource.aspx
• MSDN SqlDataSource class
– http://msdn.microsoft.com/enus/library/system.web.ui.webcontrols.sqldatasource.aspx
Data source controls in .NET
9