Sunday, August 10, 2025

Connecting Database Using ADO.NET BCA Semester 5 or IT Students

Prepared By : Prof. Uday Shah (HOD - IT)


Connecting Database Using ADO.NET 

1) Connecting Database Using ADO.NET

  • ADO.NET connects your ASP.NET app to relational databases (e.g., SQL Server, MySQL) using provider-specific classes.

  • Core steps: create a connection string, open a SqlConnection, create a SqlCommand, execute it, then close/dispose resources.

  • Always use the using statement to automatically dispose SqlConnection/SqlCommand objects and free pooled connections.

  • Enable connection pooling (on by default) to reuse physical connections and improve performance.

  • Prefer parameterized queries (@Id, @Name) to prevent SQL injection and handle data types safely.

  • Use ExecuteReader for result sets, ExecuteScalar for a single value, and ExecuteNonQuery for INSERT/UPDATE/DELETE.

  • Wrap multiple related commands in a SqlTransaction to ensure atomicity and consistency.

  • Handle exceptions with try/catch; surface friendly messages while logging detailed errors server-side.

  • For long-running tasks, use async/await (OpenAsync, ExecuteReaderAsync) to keep threads free and responsive.

  • Store connection strings securely in Web.config (or user secrets) and avoid hard-coding credentials.

  • Validate and sanitize user inputs even with parameters; also set command timeouts to avoid hanging requests.

2) ADO.NET Architecture (Connection, Command, DataReader, DataAdapter, DataSet, DataColumn, DataRow, DataView, etc.)

  • Connection (SqlConnection, OleDbConnection) manages the session with the database using a connection string.

  • Command (SqlCommand) represents a SQL statement or stored procedure with parameters and timeout settings.

  • DataReader (SqlDataReader) is fast, forward-only, read-only access to rows; ideal for streaming results.

  • DataAdapter bridges commands and a DataSet, handling Fill (read) and Update (write-back) logic.

  • DataSet is an in-memory, disconnected cache of data, containing one or more DataTables.

  • DataTable holds rows and columns; supports constraints, primary keys, and relations.

  • DataColumn defines a column’s name, data type, nullability, default value, and expression.

  • DataRow represents a record with state tracking (Added/Modified/Deleted/Unchanged) for updates.

  • DataView provides filtered/sorted views of a DataTable without copying data; useful for UI binding.

  • CommandBuilder can auto-generate INSERT/UPDATE/DELETE for simple schemas; otherwise write commands manually.

  • Parameters map strongly-typed values to queries; prevent injection and handle nulls with DBNull.Value.

  • The model supports both connected (DataReader) and disconnected (DataSet) paradigms depending on needs.

3) Data Binding

  • Data binding links UI controls to data sources (collections, DataTables, readers) to display and, optionally, update data.

  • ASP.NET Web Forms supports declarative (markup) and programmatic (code-behind) binding.

  • Two common patterns: one-way display with Eval() and two-way edit/insert with Bind().

  • Controls like GridView, Repeater, ListView, DropDownList are data-bound; set DataSource or DataSourceID.

  • Call DataBind() to evaluate <%# %> expressions when binding programmatically.

  • Use ObjectDataSource, SqlDataSource, or custom classes to decouple UI from data logic.

  • Support for format strings (e.g., {0:C}, {0:dd-MMM-yyyy}) improves display without changing data.

  • Handle events such as DataBinding, RowDataBound, or ItemDataBound to customize output per item/row.

  • For performance, prefer DataReader for read-only lists; use DataSet when you need sorting/filtering offline.

  • Implement model classes (POCOs) and bind to strongly-typed collections for cleaner, testable code.

  • Manage ViewState judiciously—disable for read-only lists to reduce page weight when possible.

4) The GridView Control

  • GridView displays tabular data with built-in support for paging, sorting, selection, and editing.

  • Columns can be auto-generated or explicitly defined using BoundField, TemplateField, HyperLinkField, etc.

  • Enable editing with AutoGenerateEditButton="True" or custom Edit/Update/Cancel buttons in TemplateFields.

  • Use DataKeyNames to store primary keys for updates/deletes without displaying them.

  • Handle events: RowDataBound (formatting), RowEditing, RowUpdating, RowDeleting, Sorting, PageIndexChanging.

  • Combine with SqlDataSource/ObjectDataSource for declarative CRUD; or handle CRUD in code-behind.

  • Use TemplateField with <ItemTemplate>/<EditItemTemplate> for custom layouts, validators, and widgets.

  • Enable paging (AllowPaging="True") and sorting (AllowSorting="True") for large datasets.

  • Apply conditional formatting (e.g., highlight negative values) in RowDataBound.

  • Keep ViewState only when needed; bind on every request for lighter pages if data is cheap to re-fetch.

  • For performance at scale, page data at the SQL level (ROW_NUMBER/OFFSET-FETCH) rather than in memory.

5) The Repeater Control

  • Repeater is a lightweight, fully templated list control with no built-in paging or editing UI.

  • Define layout using HeaderTemplate, ItemTemplate, AlternatingItemTemplate, SeparatorTemplate, FooterTemplate.

  • Ideal for custom HTML markup (cards, grids, media lists) when you need full control over rendering.

  • Binds to any enumerable data source (DataReader, DataTable, List, LINQ query).

  • Use ItemDataBound to access e.Item.DataItem and format or compute values per item.

  • Combine with validators inside templates to validate inputs (for insert/edit scenarios you build).

  • Implement custom paging/sorting manually in code or via the data source (SQL queries).

  • Minimal ViewState footprint; great for large lists that are rebound each request.

  • Supports command bubbling via LinkButton/Button inside items and ItemCommand handler.

  • Suitable when GridView’s table structure is too restrictive for your design.

  • Works well with CSS frameworks to achieve responsive, accessible layouts.

6) Binding Data to DataBound Controls

  • Set DataSource (object, DataTable, DataReader) and call DataBind(), or set DataSourceID to a data source control.

  • For declarative binding, use SqlDataSource, ObjectDataSource, LinqDataSource in markup and point the control’s DataSourceID.

  • For programmatic binding, fetch data in code-behind (e.g., in Page_Load with !IsPostBack) and call DataBind().

  • Use DataKeyNames to carry keys through postbacks for update/delete operations.

  • Leverage format strings and HtmlEncode settings in BoundFields; prefer TemplateField for complex UI.

  • When binding large data, enable paging and fetch only a page of rows from the database.

  • Apply caching (Output/DataCache) where appropriate to reduce database round trips.

  • Handle empty data gracefully using EmptyDataTemplate (GridView) or EmptyDataText.

  • Use ValidationGroup with buttons in edit templates to ensure validators fire correctly.

  • Rebind after CRUD operations to reflect changes and keep the UI consistent.

  • Consider ObjectDataSource with a business layer for testability and separation of concerns.

7) Displaying Data in a webpage using SqlDataSource Control

  • SqlDataSource lets you declare connection and commands (SELECT/INSERT/UPDATE/DELETE) in markup—no code-behind required.

  • Configure ConnectionString, SelectCommand, and optional InsertCommand, UpdateCommand, DeleteCommand.

  • Use parameters (<SelectParameters>) with ControlParameter, QueryStringParameter, SessionParameter, etc.

  • Set DataSourceMode="DataReader" for streaming read-only scenarios; DataSet when you need sorting/paging in memory.

  • Pair with GridView/DetailsView/FormView for rapid CRUD scaffolding.

  • Handle events like Selecting, Selected, Updating to inspect or modify parameters and handle errors.

  • Ensure parameterization to avoid SQL injection; never concatenate raw user input into commands.

  • Centralize the connection string in Web.config and reference via <%$ ConnectionStrings:MyDb %>.

  • Useful for prototypes/admin pages; for larger apps, prefer ObjectDataSource or repository/service layers.

  • Combine with caching (EnableCaching, CacheDuration) for frequently read data.

  • Monitor performance and security; keep least-privilege DB accounts and limit exposed operations.

8) DataBinding Expression

  • ASP.NET evaluates binding expressions written as <%# ... %> during DataBind().

  • Use Eval("FieldName") for one-way (read-only) binding in display templates.

  • Use Bind("FieldName") for two-way binding; supports update/insert in editable templates.

  • Apply format strings: Eval("Price", "{0:C}"), Eval("Date", "{0:dd-MMM-yyyy}") for user-friendly output.

  • Access nested properties: Eval("Customer.Address.City") when the data item is an object graph.

  • In templates, Container.DataItem refers to the current item; cast for custom logic in ItemDataBound.

  • Combine with conditional rendering via <%# (Eval("Qty") as int?) > 0 ? "In Stock" : "Out of Stock" %>.

  • Remember expressions run on DataBind; ensure you call DataBind() when binding programmatically.

  • For HTML-encoded output, use BoundFields; for raw HTML, set HtmlEncode="False" or render via Literal controls carefully.

  • Use Validation controls alongside Bind() in edit templates to enforce input rules before updates.

  • Keep expressions simple; move complex logic to code-behind or view models for maintainability.


:: Best of Luck ::
Posted by at
Labels: , , , , , , , , , , , , , , , , ,