Accessing Databases with DataSource Controls in ASP.NET: A Comprehensive Guide
This guide explores how to access various databases using DataSource controls in ASP.NET. Learn about the different types of DataSource controls including AccessDataSource for Microsoft Access databases and SqlDataSource for SQL Server databases. Understand how to set up connection strings, utilize the .APP_DATA folder for SQL Server database files, and manage database connections. We also discuss filtering data using SelectParameters and provide example code snippets for practical implementation. This resource is essential for developers working with database connections in ASP.NET.
Accessing Databases with DataSource Controls in ASP.NET: A Comprehensive Guide
E N D
Presentation Transcript
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
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/index-085163.html • MySQL • http://www.mysql.com/downloads/connector/net/ • http://dev.mysql.com/tech-resources/articles/dotnet/ Data source controls in .NET
Asp:SqlDataSourceexamles <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; UserId=anders; Password=Secret12;"> </asp:SqlDataSource> (local) is sometimeswritten . (”dot”) Data source controls in .NET
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
Connectionstring 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
Data Source Mode Property • <asp:SqlDataSource… DataSourceMode=… > • DataSourceModecan have twovalues • DataSet • Read-Write, forward/backwards • Default value for the DataSourceModeproperty • Allowsfiltering, sorting, and paging • DataReader • Read-only, forward-only • Fastest, most effecient Data source controls in .NET
Filtering data usingSelectParameters <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:QueryStringParameterName="memberID" QueryStringField="memberID" Type="String" /> </SelectParameters> </asp:SqlDataSource> Data source controls in .NET
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 EvjenProfessional ASP.NET 4 in C# and VB, Wrox/Wiley 2010 • Chapter 7 Data Binding, page 237-253 • MSDN AccessDataSourceclass • http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.accessdatasource.aspx • MSDN SqlDataSourceclass • http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.aspx Data source controls in .NET
