Transcript Chapter 9
Chapter 9 Using the SqlDataSource Control References http://aspnet.4guysfromrolla.com/articles/02 2206-1.aspx The SqlDataSpource Control The SqlDataSource control enables you to quickly and easily represent a SQL database in a web page. The SqlDataSource control is built on top of ADO.NET. Under the covers, theSqlDataSource uses ADO.NET objects such as the DataSet, DataReader, and Command objects. Because the SqlDataSource control is a control, it enables you to use these ADO.NET objects declaratively, rather than programmatically. Connecting to Microsoft SQL Server Be default, the SqlDataSource control is configured to connect to Microsoft SQL Server. The default provider used by the SqlDataSource control is the ADO.NET provider to Microsoft SQL Server. SqlConnectionBuilder The .NET Framework includes a utility class names the SqlConnectionBuilder class that you can use when working with SQL connection strings. Thes class automatically converts any connection string into a canonical representation. It also exposes properties for extracting and modifying individual connection string parameters such as the Password parameters. SqlConnectionStringBuilder Storing Connection Strings in the Web Configuration File Storing connection string in your pages is a bad idea for three reasons • Not a good practice from the perspective of security • Adding a connection string to every page makes it difficult to manage a website. • Storing a connection string in a page can potentially hurt the performance of your application Executing Database Commands Executing Inline SQL Statements The SqlDataSource control supports • SelectCommand • InsertCommand • UpdateCommand • DeleteCommand SelectCommand <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:totlebenConnectionString %>" SelectCommand="SELECT [customer_id], [lname], [fname] FROM [customer]"> </asp:SqlDataSource> Executing Stored Procedures The SqlDataSource control can represent SQL stored procedures just as easily as it can represent inline SQL commands. You can indicate that a command represents a stored procedure by assigning the value • • • • StoredProcedure to SelectCommandType InsertCommandType UpdateCommandType DeleteCommandType Executing Stored Procedure <asp:SqlDataSource ID="SqlDataSource1" runat="server "ConnectionString="<%$ ConnectionStrings:totlebenConnectionString %>" SelectCommand="sp_getCustomer" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> Data returned from the underlying database by the SqlDataSource control • DataReaders - a DataReader offers forwardonly, read-only access to a database, and requires an active connection. More efficient than a DataSets/DataTables/DataViews. • DataSet/DataTable/DataView - a DataTable offers a random access, editable representation of a query. A DataSet is a collection of DataTables. DataViews can be used to retrieve a filtered or sorted subset of records in a DataTable. Handling SQL Command Execution Errors You can handle errors thrown by the SqlDataSource control by handling any or all of the following • Deleted • Inserted • Selected • Updated Each is passed an EventArgs parameter Using Parameters with the SqlDataSource Control • Parameter-Represents an arbitrary static value • ControlParameter-Represents the value of a control or page property • CookieParameter-Represents the value of a browser cookie • FormParamter-Represents the value of an HTML form field • ProfileParameter-Represents the value of a Profile property • QueryStringParameter-Represents the value of a query string field • SessionParameter-Represents the value of an item stored in Session state ASP.Net Parameter Object Properties Include • ConvertEmptyStringToNull-When true, if a parameter represents an empty string then the empty string is converted to the value Nothing(null) before the associated command is executed ASP.Net Parameter Object • DefaultValue-When a parameter has the value Nothing(null) the DefaultValue is used for the value of the parameter • Direction-Indicates the direction of the parameter. Possible value are Input, InputOutput, Output, and ReturnValue ASP.Net Parameter Object • Name-Indicates the name of the parameter. • Size-Indicates the data size of the parameter • Type-Indicates the .Net Framework type of parameter. You can assign any value from the TypeCode enumeration to this property.