.NET Framework Class Library
SqlDataSource Class

Represents an SQL database to data-bound controls.

Namespace:   System.Web.UI.WebControls
Assembly:  System.Web (in System.Web.dll)
Syntax
<[%$TOPIC/cd0cb3we_en-us_VS_110_2_0_0_0_0%](GetType(SqlDataSource))> _
Public Class SqlDataSource _
	Inherits [%$TOPIC/cd0cb3we_en-us_VS_110_2_0_0_0_1%]
[[%$TOPIC/cd0cb3we_en-us_VS_110_2_0_1_0_0%](typeof(SqlDataSource))]
public class SqlDataSource : [%$TOPIC/cd0cb3we_en-us_VS_110_2_0_1_0_1%]
[[%$TOPIC/cd0cb3we_en-us_VS_110_2_0_2_0_0%](typeof(SqlDataSource))]
public ref class SqlDataSource : public [%$TOPIC/cd0cb3we_en-us_VS_110_2_0_2_0_1%]
[<[%$TOPIC/cd0cb3we_en-us_VS_110_2_0_3_0_0%](typeof(SqlDataSource))>]
type SqlDataSource =  
    class 
        inherit [%$TOPIC/cd0cb3we_en-us_VS_110_2_0_3_0_1%] 
    end
<asp:SqlDataSource />

The SqlDataSource type exposes the following members.

Constructors
  NameDescription
Public method SqlDataSourceInitializes a new instance of the SqlDataSource class.
Public method SqlDataSource(String, String)Initializes a new instance of the SqlDataSource class with the specified connection string and Select command.
Public method SqlDataSource(String, String, String)Initializes a new instance of the SqlDataSource class with the specified connection string and Select command.
Top
Properties
  NameDescription
Protected property AdapterGets the browser-specific adapter for the control. (Inherited from Control.)
Public property AppRelativeTemplateSourceDirectoryGets or sets the application-relative virtual directory of the Page or UserControl object that contains this control. (Inherited from Control.)
Public property BindingContainerInfrastructure. Gets the control that contains this control's data binding. (Inherited from Control.)
Public property CacheDurationGets or sets the length of time, in seconds, that the data source control caches data that is retrieved by the Select method.
Public property CacheExpirationPolicyGets or sets the cache expiration behavior that, when combined with the duration, describes the behavior of the cache that the data source control uses.
Public property CacheKeyDependencyGets or sets a user-defined key dependency that is linked to all data cache objects that are created by the data source control. All cache objects are explicitly expired when the key is expired.
Public property CancelSelectOnNullParameterGets or sets a value indicating whether a data retrieval operation is canceled when any parameter that is contained in the SelectParameters collection evaluates to .
Protected property ChildControlsCreatedGets a value that indicates whether the server control's child controls have been created. (Inherited from Control.)
Public property ClientIDGets the server control identifier generated by ASP.NET. (Inherited from DataSourceControl.)
Public property ClientIDModeThis property is not used for data source controls. (Inherited from DataSourceControl.)
Protected property ClientIDSeparatorGets a character value representing the separator character used in the ClientID property. (Inherited from Control.)
Public property ConflictDetectionGets or sets the value indicating how the SqlDataSource control performs updates and deletes when data in a row in the underlying database changes during the time of the operation.
Public property ConnectionStringGets or sets the ADO.NET provider–specific connection string that the SqlDataSource control uses to connect to an underlying database.
Protected property ContextGets the HttpContext object associated with the server control for the current Web request. (Inherited from Control.)
Public property ControlsGets a ControlCollection object that represents the child controls for a specified server control in the UI hierarchy. (Inherited from DataSourceControl.)
Public property DataItemContainerGets a reference to the naming container if the naming container implements IDataItemContainer. (Inherited from Control.)
Public property DataKeysContainerGets a reference to the naming container if the naming container implements IDataKeysControl. (Inherited from Control.)
Public property DataSourceModeGets or sets the data retrieval mode that the SqlDataSource control uses to fetch data.
Public property DeleteCommandGets or sets the SQL string that the SqlDataSource control uses to delete data from the underlying database.
Public property DeleteCommandTypeGets or sets a value indicating whether the text in the DeleteCommand property is an SQL statement or the name of a stored procedure.
Public property DeleteParametersGets the parameters collection that contains the parameters that are used by the DeleteCommand property from the SqlDataSourceView object that is associated with the SqlDataSource control.
Protected property DesignModeGets a value indicating whether a control is being used on a design surface. (Inherited from Control.)
Public property EnableCachingGets or sets a value indicating whether the SqlDataSource control has data caching enabled.
Public property EnableThemingGets a value indicating whether this control supports themes. (Inherited from DataSourceControl.)
Public property EnableViewStateGets or sets a value indicating whether the server control persists its view state, and the view state of any child controls it contains, to the requesting client. (Inherited from Control.)
Protected property EventsGets a list of event handler delegates for the control. This property is read-only. (Inherited from Control.)
Public property FilterExpressionGets or sets a filtering expression that is applied when the Select method is called.
Public property FilterParametersGets a collection of parameters that are associated with any parameter placeholders that are in the FilterExpression string.
Protected property HasChildViewStateGets a value indicating whether the current server control's child controls have any saved view-state settings. (Inherited from Control.)
Public property IDGets or sets the programmatic identifier assigned to the server control. (Inherited from Control.)
Protected property IdSeparatorInfrastructure. Gets the character used to separate control identifiers. (Inherited from Control.)
Public property InsertCommandGets or sets the SQL string that the SqlDataSource control uses to insert data into the underlying database.
Public property InsertCommandTypeGets or sets a value indicating whether the text in the InsertCommand property is an SQL statement or the name of a stored procedure.
Public property InsertParametersGets the parameters collection that contains the parameters that are used by the InsertCommand property from the SqlDataSourceView object that is associated with the SqlDataSource control.
Protected property IsChildControlStateClearedGets a value indicating whether controls contained within this control have control state. (Inherited from Control.)
Protected property IsTrackingViewStateGets a value that indicates whether the server control is saving changes to its view state. (Inherited from Control.)
Protected property IsViewStateEnabledGets a value indicating whether view state is enabled for this control. (Inherited from Control.)
Protected property LoadViewStateByIDGets a value indicating whether the control participates in loading its view state by ID instead of index. (Inherited from Control.)
Public property NamingContainerGets a reference to the server control's naming container, which creates a unique namespace for differentiating between server controls with the same ControlID property value. (Inherited from Control.)
Public property OldValuesParameterFormatStringGets or sets a format string to apply to the names of any parameters that are passed to the Delete or Update method.
Public property PageGets a reference to the Page instance that contains the server control. (Inherited from Control.)
Public property ParentGets a reference to the server control's parent control in the page control hierarchy. (Inherited from Control.)
Public property ProviderNameGets or sets the name of the .NET Framework data provider that the SqlDataSource control uses to connect to an underlying data source.
Public property RenderingCompatibilityGets a value that specifies the ASP.NET version that rendered HTML will be compatible with. (Inherited from Control.)
Public property SelectCommandGets or sets the SQL string that the SqlDataSource control uses to retrieve data from the underlying database.
Public property SelectCommandTypeGets or sets a value indicating whether the text in the SelectCommand property is an SQL query or the name of a stored procedure.
Public property SelectParametersGets the parameters collection that contains the parameters that are used by the SelectCommand property from the SqlDataSourceView object that is associated with the SqlDataSource control.
Public property SiteGets information about the container that hosts the current control when rendered on a design surface. (Inherited from Control.)
Public property SkinIDGets the skin to apply to the DataSourceControl control. (Inherited from DataSourceControl.)
Public property SortParameterNameGets or sets the name of a stored procedure parameter that is used to sort retrieved data when data retrieval is performed using a stored procedure.
Public property SqlCacheDependencyGets or sets a semicolon-delimited string that indicates which databases and tables to use for the Microsoft SQL Server cache dependency.
Public property TemplateControlGets or sets a reference to the template that contains this control. (Inherited from Control.)
Public property TemplateSourceDirectoryGets the virtual directory of the Page or UserControl that contains the current server control. (Inherited from Control.)
Public property UniqueIDGets the unique, hierarchically qualified identifier for the server control. (Inherited from Control.)
Public property UpdateCommandGets or sets the SQL string that the SqlDataSource control uses to update data in the underlying database.
Public property UpdateCommandTypeGets or sets a value indicating whether the text in the UpdateCommand property is an SQL statement or the name of a stored procedure.
Public property UpdateParametersGets the parameters collection that contains the parameters that are used by the UpdateCommand property from the SqlDataSourceView control that is associated with the SqlDataSource control.
Public property ValidateRequestModeGets or sets a value that indicates whether the control checks client input from the browser for potentially dangerous values. (Inherited from Control.)
Protected property ViewStateGets a dictionary of state information that allows you to save and restore the view state of a server control across multiple requests for the same page. (Inherited from Control.)
Protected property ViewStateIgnoresCaseGets a value that indicates whether the StateBag object is case-insensitive. (Inherited from Control.)
Public property ViewStateModeGets or sets the view-state mode of this control. (Inherited from Control.)
Public property VisibleGets or sets a value indicating whether the control is visually displayed. (Inherited from DataSourceControl.)
Top
Methods
  NameDescription
Protected method AddedControlCalled after a child control is added to the Controls collection of the Control object. (Inherited from Control.)
Protected method AddParsedSubObjectNotifies the server control that an element, either XML or HTML, was parsed, and adds the element to the server control's ControlCollection object. (Inherited from Control.)
Public method ApplyStyleSheetSkinApplies the style properties that are defined in the page style sheet to the control. (Inherited from DataSourceControl.)
Protected method BeginRenderTracingBegins design-time tracing of rendering data. (Inherited from Control.)
Protected method BuildProfileTreeInfrastructure. Gathers information about the server control and delivers it to the Trace property to be displayed when tracing is enabled for the page. (Inherited from Control.)
Protected method ClearCachedClientIDInfrastructure. Sets the cached ClientID value to . (Inherited from Control.)
Protected method ClearChildControlStateDeletes the control-state information for the server control's child controls. (Inherited from Control.)
Protected method ClearChildStateDeletes the view-state and control-state information for all the server control's child controls. (Inherited from Control.)
Protected method ClearChildViewStateDeletes the view-state information for all the server control's child controls. (Inherited from Control.)
Protected method ClearEffectiveClientIDModeInfrastructure. Sets the ClientIDMode property of the current control instance and of any child controls to Inherit. (Inherited from Control.)
Protected method CreateChildControlsCalled by the ASP.NET page framework to notify server controls that use composition-based implementation to create any child controls they contain in preparation for posting back or rendering. (Inherited from Control.)
Protected method CreateControlCollectionCreates a collection to store child controls. (Inherited from DataSourceControl.)
Protected method CreateDataSourceViewCreates a data source view object that is associated with the data source control.
Public method DataBindBinds a data source to the invoked server control and all its child controls. (Inherited from Control.)
Protected method DataBind(Boolean)Binds a data source to the invoked server control and all its child controls with an option to raise the DataBinding event. (Inherited from Control.)
Protected method DataBindChildrenBinds a data source to the server control's child controls. (Inherited from Control.)
Public method DeletePerforms a delete operation using the DeleteCommand SQL string and any parameters that are in the DeleteParameters collection.
Public method DisposeEnables a server control to perform final clean up before it is released from memory. (Inherited from Control.)
Protected method EndRenderTracingEnds design-time tracing of rendering data. (Inherited from Control.)
Protected method EnsureChildControlsDetermines whether the server control contains child controls. If it does not, it creates child controls. (Inherited from Control.)
Protected method EnsureIDCreates an identifier for controls that do not have an identifier assigned. (Inherited from Control.)
Public method Equals(Object)Determines whether the specified object is equal to the current object. (Inherited from Object.)
Protected method FinalizeAllows an object to try to free resources and perform other cleanup operations before it is reclaimed by garbage collection. (Inherited from Object.)
Public method FindControl(String)Searches the current naming container for a server control with the specified id parameter. (Inherited from DataSourceControl.)
Protected method FindControl(String, Int32)Infrastructure. Searches the current naming container for a server control with the specified id and an integer, specified in the pathOffset parameter, which aids in the search. You should not override this version of the FindControl method. (Inherited from Control.)
Public method FocusSets input focus to the control. (Inherited from DataSourceControl.)
Protected method GetDbProviderFactoryReturns the DbProviderFactory object that is associated with the ADO.NET provider that is identified by the ProviderName property.
Protected method GetDesignModeStateInfrastructure. Gets design-time data for a control. (Inherited from Control.)
Public method GetHashCodeServes as the default hash function. (Inherited from Object.)
Public method GetRouteUrl(Object)Gets the URL that corresponds to a set of route parameters. (Inherited from Control.)
Public method GetRouteUrl(RouteValueDictionary)Gets the URL that corresponds to a set of route parameters. (Inherited from Control.)
Public method GetRouteUrl(String, Object)Gets the URL that corresponds to a set of route parameters and a route name. (Inherited from Control.)
Public method GetRouteUrl(String, RouteValueDictionary)Gets the URL that corresponds to a set of route parameters and a route name. (Inherited from Control.)
Public method GetTypeGets the Type of the current instance. (Inherited from Object.)
Public method GetUniqueIDRelativeToReturns the prefixed portion of the UniqueID property of the specified control. (Inherited from Control.)
Protected method GetViewGets the named data source view that is associated with the data source control. (Overrides DataSourceControlGetView(String).)
Protected method GetViewNamesGets a collection of names representing the list of view objects that are associated with the SqlDataSource control. (Overrides DataSourceControlGetViewNames.)
Public method HasControlsDetermines if the server control contains any child controls. (Inherited from DataSourceControl.)
Protected method HasEventsReturns a value indicating whether events are registered for the control or any child controls. (Inherited from Control.)
Public method InsertPerforms an insert operation using the InsertCommand SQL string and any parameters that are in the InsertParameters collection.
Protected method IsLiteralContentInfrastructure. Determines if the server control holds only literal content. (Inherited from Control.)
Protected method LoadControlStateRestores control-state information from a previous page request that was saved by the SaveControlState method. (Inherited from Control.)
Protected method LoadViewStateInfrastructure. Loads the state of the properties in the SqlDataSource control that need to be persisted. (Overrides ControlLoadViewState(Object).)
Protected method MapPathSecureRetrieves the physical path that a virtual path, either absolute or relative, maps to. (Inherited from Control.)
Protected method MemberwiseCloneCreates a shallow copy of the current Object. (Inherited from Object.)
Protected method OnBubbleEventDetermines whether the event for the server control is passed up the page's UI server control hierarchy. (Inherited from Control.)
Protected method OnDataBindingRaises the DataBinding event. (Inherited from Control.)
Protected method OnInitAdds a LoadComplete event handler to the Page control that contains the SqlDataSource control. (Overrides ControlOnInit(EventArgs).)
Protected method OnLoadRaises the Load event. (Inherited from Control.)
Protected method OnPreRenderRaises the PreRender event. (Inherited from Control.)
Protected method OnUnloadRaises the Unload event. (Inherited from Control.)
Protected method OpenFileGets a Stream used to read a file. (Inherited from Control.)
Protected method RaiseBubbleEventAssigns any sources of the event and its information to the control's parent. (Inherited from Control.)
Protected method RaiseDataSourceChangedEventRaises the DataSourceChanged event. (Inherited from DataSourceControl.)
Protected method RemovedControlCalled after a child control is removed from the Controls collection of the Control object. (Inherited from Control.)
Protected method RenderSends server control content to a provided HtmlTextWriter object, which writes the content to be rendered on the client. (Inherited from Control.)
Protected method RenderChildrenOutputs the content of a server control's children to a provided HtmlTextWriter object, which writes the content to be rendered on the client. (Inherited from Control.)
Public method RenderControl(HtmlTextWriter)Outputs server control content to a provided HtmlTextWriter object and stores tracing information about the control if tracing is enabled. (Inherited from DataSourceControl.)
Protected method RenderControl(HtmlTextWriter, ControlAdapter)Outputs server control content to a provided HtmlTextWriter object using a provided ControlAdapter object. (Inherited from Control.)
Protected method ResolveAdapterGets the control adapter responsible for rendering the specified control. (Inherited from Control.)
Public method ResolveClientUrlGets a URL that can be used by the browser. (Inherited from Control.)
Public method ResolveUrlConverts a URL into one that is usable on the requesting client. (Inherited from Control.)
Protected method SaveControlStateSaves any server control state changes that have occurred since the time the page was posted back to the server. (Inherited from Control.)
Protected method SaveViewStateInfrastructure. Saves the current view state of the SqlDataSource control. (Overrides ControlSaveViewState.)
Public method SelectRetrieves data from the underlying database by using the SelectCommand SQL string and any parameters that are in the SelectParameters collection.
Protected method SetDesignModeStateSets design-time data for a control. (Inherited from Control.)
Public method SetRenderMethodDelegateInfrastructure. Assigns an event handler delegate to render the server control and its content into its parent control. (Inherited from Control.)
Public method SetTraceData(Object, Object)Sets trace data for design-time tracing of rendering data, using the trace data key and the trace data value. (Inherited from Control.)
Public method SetTraceData(Object, Object, Object)Sets trace data for design-time tracing of rendering data, using the traced object, the trace data key, and the trace data value. (Inherited from Control.)
Public method ToStringReturns a string that represents the current object. (Inherited from Object.)
Protected method TrackViewStateInfrastructure. Tracks view state changes to the SqlDataSource control so that the changes can be stored in the System.Web.UIStateBag object for the control. (Overrides ControlTrackViewState.)
Public method UpdatePerforms an update operation using the UpdateCommand SQL string and any parameters that are in the UpdateParameters collection.
Top
Events
  NameDescription
Public event DataBindingOccurs when the server control binds to a data source. (Inherited from Control.)
Public event DeletedOccurs when a delete operation has completed.
Public event DeletingOccurs before a delete operation.
Public event DisposedOccurs when a server control is released from memory, which is the last stage of the server control lifecycle when an ASP.NET page is requested. (Inherited from Control.)
Public event FilteringOccurs before a filter operation.
Public event InitOccurs when the server control is initialized, which is the first step in its lifecycle. (Inherited from Control.)
Public event InsertedOccurs when an insert operation has completed.
Public event InsertingOccurs before an insert operation.
Public event LoadOccurs when the server control is loaded into the Page object. (Inherited from Control.)
Public event PreRenderOccurs after the Control object is loaded but prior to rendering. (Inherited from Control.)
Public event SelectedOccurs when a data retrieval operation has completed.
Public event SelectingOccurs before a data retrieval operation.
Public event UnloadOccurs when the server control is unloaded from memory. (Inherited from Control.)
Public event UpdatedOccurs when an update operation has completed.
Public event UpdatingOccurs before an update operation.
Top
Extension Methods
  NameDescription
Public Extension Method FindDataSourceControlReturns the data source that is associated with the data control for the specified control. (Defined by DynamicDataExtensions.)
Public Extension Method FindFieldTemplateReturns the field template for the specified column in the specified control's naming container. (Defined by DynamicDataExtensions.)
Public Extension Method FindMetaTableReturns the metatable object for the containing data control. (Defined by DynamicDataExtensions.)
Top
Explicit Interface Implementations
  NameDescription
Explicit interface implemetation Private property IControlBuilderAccessorControlBuilderFor a description of this member, see IControlBuilderAccessorControlBuilder. (Inherited from Control.)
Explicit interface implemetation Private method IControlDesignerAccessorGetDesignModeStateFor a description of this member, see IControlDesignerAccessorGetDesignModeState. (Inherited from Control.)
Explicit interface implemetation Private method IControlDesignerAccessorSetDesignModeStateFor a description of this member, see IControlDesignerAccessorSetDesignModeState. (Inherited from Control.)
Explicit interface implemetation Private method IControlDesignerAccessorSetOwnerControlInfrastructure. For a description of this member, see IControlDesignerAccessorSetOwnerControl. (Inherited from Control.)
Explicit interface implemetation Private property IControlDesignerAccessorUserDataFor a description of this member, see IControlDesignerAccessorUserData. (Inherited from Control.)
Explicit interface implemetation Private property IDataBindingsAccessorDataBindingsFor a description of this member, see IDataBindingsAccessorDataBindings. (Inherited from Control.)
Explicit interface implemetation Private property IDataBindingsAccessorHasDataBindingsFor a description of this member, see IDataBindingsAccessorHasDataBindings. (Inherited from Control.)
Explicit interface implemetation Private event IDataSourceDataSourceChangedInfrastructure. Occurs when a data source control has changed in a way that affects data-bound controls. (Inherited from DataSourceControl.)
Explicit interface implemetation Private method IDataSourceGetViewInfrastructure. Gets the named DataSourceView object associated with the DataSourceControl control. Some data source controls support only one view, while others support more than one. (Inherited from DataSourceControl.)
Explicit interface implemetation Private method IDataSourceGetViewNamesInfrastructure. Gets a collection of names, representing the list of DataSourceView objects associated with the DataSourceControl control. (Inherited from DataSourceControl.)
Explicit interface implemetation Private property IExpressionsAccessorExpressionsFor a description of this member, see IExpressionsAccessorExpressions. (Inherited from Control.)
Explicit interface implemetation Private property IExpressionsAccessorHasExpressionsFor a description of this member, see IExpressionsAccessorHasExpressions. (Inherited from Control.)
Explicit interface implemetation Private property IListSourceContainsListCollectionInfrastructure. Indicates whether the data source control is associated with one or more lists of data. (Inherited from DataSourceControl.)
Explicit interface implemetation Private method IListSourceGetListInfrastructure. Gets a list of data source controls that can be used as sources of lists of data. (Inherited from DataSourceControl.)
Explicit interface implemetation Private method IParserAccessorAddParsedSubObjectFor a description of this member, see IParserAccessorAddParsedSubObject. (Inherited from Control.)
Top
Remarks

In this topic:

  • Introduction

  • Data Connections

  • Performing Data Operations

  • Data Provider

  • Caching

  • Additional Features

  • Data Source View

  • Declarative Syntax

Introduction

The SqlDataSource data source control represents data in an SQL relational database to data-bound controls. You can use the SqlDataSource control in conjunction with a data-bound control to retrieve data from a relational database and to display, edit, and sort data on a Web page with little or no code.

Data Connections

To connect to a database, you must set the ConnectionString property to a valid connection string. The SqlDataSource can support any SQL relational database that can be connected to using an ADO.NET provider, such as the SqlClient, OleDb, Odbc, or OracleClient provider. For information about how to secure connection strings, see How To: Secure Connection Strings when Using Data Source Controls.

To retrieve data from an underlying database, set the SelectCommand property with an SQL query. If the database that the SqlDataSource is associated with supports stored procedures, you can set the SelectCommand property to the name of a stored procedure. The SQL query that you specify can also be a parameterized query. You can add Parameter objects that are associated with a parameterized query to the SelectParameters collection. For more information about parameterized SQL queries and their syntax, see Using Parameters with Data Source Controls for Filtering.

The SqlDataSource control retrieves data whenever the Select method is called. This method provides programmatic access to the method that is specified by SelectMethod property. The Select method is automatically called by controls that are bound to the SqlDataSource when their DataBind method is called. If you set the DataSourceID property of a data-bound control, the control automatically binds to data from the data source, as required. Setting the DataSourceID property is the recommended method for binding an ObjectDataSource control to a data-bound control. Alternatively, you can use the DataSource property, but then you must explicitly call the DataBind method of the data-bound control. Some examples of data-bound controls that can use SqlDataSource are DataGrid, DetailsView, DataList, and DropDownList. You can call the Select method programmatically at any time to retrieve data from the underlying database.

In declarative and programmatic ASP.NET scenarios, you can set the DataSourceID property of the data-bound control to the ID of the SqlDataSource control. You can also assign an instance of the SqlDataSource class to the DataSource property of the data-bound control. For more information about binding data-bound control to data source controls, see ASP.NET Data Access Options.

Performing Data Operations

Depending on the capabilities of the underlying database product and the configuration of the instance of the SqlDataSource class, you can perform data operations, such as updates, inserts, and deletes. To perform these data operations, set the appropriate command text and any associated parameters for the operation that you want to perform. For example, for an update operation, set the UpdateCommand property to an SQL string or the name of a stored procedure and add any required parameters to the UpdateParameters collection. The update is performed when the Update method is called, either explicitly by your code or automatically by a data-bound control. The same general pattern is followed for Delete and Insert operations.

The SQL queries and commands that you use in the SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties can be parameterized. This means that the query or command can use placeholders instead of literal values and bind the placeholders to application or user-defined variables. You can bind parameters in SQL queries to Session variables, values that are passed on the query string for a Web Forms page, the property values of other server controls, and more. For more information about how to use parameters in SQL queries with the SqlDataSource, see Using Parameters with Data Source Controls for Filtering and Using Parameters with the SqlDataSource Control.

NoteNote

By default, if one of the parameters is when you execute a Select command, no data will be returned and no exception will be thrown. You can change this behavior by setting the CancelSelectOnNullParameter property to false.

Data Provider

By default, the SqlDataSource control works with the .NET Framework Data Provider for SQL Server, but SqlDataSource is not Microsoft SQL Server–specific. You can connect the SqlDataSource control with any database product for which there is a managed ADO.NET provider. When used with the System.Data.OleDb provider, the SqlDataSource can work with any OLE DB-compliant database. When used with the System.Data.Odbc provider, the SqlDataSource can be used with any ODBC driver and database, including IBM DB2, MySQL, and PostgreSQL. When used with the System.Data.OracleClient provider, the SqlDataSource can work with Oracle 8.1.7 databases and later. The list of allowable providers is registered in the DbProviderFactories section of the configuration file, either in the Machine.config or Web.config file. For more information, see Selecting Data Using the SqlDataSource Control.

Caching

If you display data on your page using a SqlDataSource control, you can increase the performance of the page by using the data caching capabilities of the data source control. Caching reduces the processing load on the database servers at the expense of memory on the Web server; in most cases, this is a good trade-off. The SqlDataSource automatically caches data when the EnableCaching property is set to true and the CacheDuration property is set to the number of seconds that the cache stores data before the cache entry is discarded. You can also specify a CacheExpirationPolicy and an optional SqlCacheDependency value.

Additional Features

The SqlDataSource provides additional capabilities, as listed in the following table.

Capability

Requirements

Caching

Set the DataSourceMode property to the DataSet value, the EnableCaching property to true, and the CacheDuration and CacheExpirationPolicy properties according to the caching behavior you want for your cached data.

Deleting

Set the DeleteCommand property to an SQL statement used to delete data. This statement is typically parameterized.

Filtering

Set the DataSourceMode property to the DataSet value. Set the FilterExpression property to a filtering expression used to filter the data when the Select method is called.

Inserting

Set the InsertCommand property to an SQL statement used to insert data. This statement is typically parameterized.

Paging

Not currently supported by the SqlDataSource, however some data-bound controls, such as GridView, support paging when you set the DataSourceMode property to the DataSet value.

Selecting

Set the SelectCommand property to an SQL statement used to retrieve data.

Sorting

Set the DataSourceMode property to DataSet.

Updating

Set the UpdateCommand property to an SQL statement used to update data. This statement is typically parameterized.

Data Source View

As with all data source controls, the SqlDataSource control is associated with a data source view class. The SqlDataSource control has only one associated SqlDataSourceView, and it is always named Table.

There is no visual rendering of the SqlDataSource control; it is implemented as a control so that you can create it declaratively and, optionally, to allow it to participate in state management. As a result, the SqlDataSource does not support visual features, such as the ones that are provided by the EnableTheming or SkinID property.

Declarative Syntax

<asp:SqlDataSource
    CacheDuration="string|Infinite"
    CacheExpirationPolicy="Absolute|Sliding"
    CacheKeyDependency="string"
    CancelSelectOnNullParameter="True|False"
    ConflictDetection="OverwriteChanges|CompareAllValues"
    ConnectionString="string"
    DataSourceMode="DataReader|DataSet"
    DeleteCommand="string"
    DeleteCommandType="Text|StoredProcedure"
    EnableCaching="True|False"
    EnableTheming="True|False"
    EnableViewState="True|False"
    FilterExpression="string"
    ID="string"
    InsertCommand="string"
    InsertCommandType="Text|StoredProcedure"
    OldValuesParameterFormatString="string"
    OnDataBinding="DataBinding event handler"
    OnDeleted="Deleted event handler"
    OnDeleting="Deleting event handler"
    OnDisposed="Disposed event handler"
    OnFiltering="Filtering event handler"
    OnInit="Init event handler"
    OnInserted="Inserted event handler"
    OnInserting="Inserting event handler"
    OnLoad="Load event handler"
    OnPreRender="PreRender event handler"
    OnSelected="Selected event handler"
    OnSelecting="Selecting event handler"
    OnUnload="Unload event handler"
    OnUpdated="Updated event handler"
    OnUpdating="Updating event handler"
    ProviderName="string|System.Data.Odbc|System.Data.OleDb|
        System.Data.OracleClient|System.Data.SqlClient|
        Microsoft.SqlServerCe.Client"
    runat="server"
    SelectCommand="string"
    SelectCommandType="Text|StoredProcedure"
    SkinID="string"
    SortParameterName="string"
    SqlCacheDependency="string"
    UpdateCommand="string"
    UpdateCommandType="Text|StoredProcedure"
    Visible="True|False"
>
        <DeleteParameters>
                <asp:ControlParameter
                    ControlID="string"
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    PropertyName="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:CookieParameter
                    ConvertEmptyStringToNull="True|False"
                    CookieName="string"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:FormParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    FormField="string"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:Parameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:ProfileParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    PropertyName="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:QueryStringParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    QueryStringField="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:SessionParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    SessionField="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
        </DeleteParameters>
        <FilterParameters>
                <asp:ControlParameter
                    ControlID="string"
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    PropertyName="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:CookieParameter
                    ConvertEmptyStringToNull="True|False"
                    CookieName="string"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:FormParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    FormField="string"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:Parameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:ProfileParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    PropertyName="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:QueryStringParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    QueryStringField="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:SessionParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    SessionField="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
        </FilterParameters>
        <InsertParameters>
                <asp:ControlParameter
                    ControlID="string"
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    PropertyName="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:CookieParameter
                    ConvertEmptyStringToNull="True|False"
                    CookieName="string"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:FormParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    FormField="string"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:Parameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:ProfileParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    PropertyName="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:QueryStringParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    QueryStringField="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:SessionParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    SessionField="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
        </InsertParameters>
        <SelectParameters>
                <asp:ControlParameter
                    ControlID="string"
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    PropertyName="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:CookieParameter
                    ConvertEmptyStringToNull="True|False"
                    CookieName="string"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:FormParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    FormField="string"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:Parameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:ProfileParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    PropertyName="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:QueryStringParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    QueryStringField="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:SessionParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    SessionField="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
        </SelectParameters>
        <UpdateParameters>
                <asp:ControlParameter
                    ControlID="string"
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    PropertyName="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:CookieParameter
                    ConvertEmptyStringToNull="True|False"
                    CookieName="string"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:FormParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    FormField="string"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:Parameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:ProfileParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    PropertyName="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:QueryStringParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    QueryStringField="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
                <asp:SessionParameter
                    ConvertEmptyStringToNull="True|False"
                    DefaultValue="string"
                    Direction="Input|Output|InputOutput|ReturnValue"
                    Name="string"
                    SessionField="string"
                    Size="integer"
                    Type="Empty|Object|DBNull|Boolean|Char|SByte|
                        Byte|Int16|UInt16|Int32|UInt32|Int64|UInt64|
                        Single|Double|Decimal|DateTime|String"
                />
        </UpdateParameters>
</asp:SqlDataSource>
Examples

A Visual Studio Web site project with source code is available to accompany this topic: Download.

This section contains four code examples:

  • The first code example demonstrates how to display data from SQL Server in a GridView control using declarative syntax.

  • The second code example demonstrates how to display data from an ODBC-compliant database in a GridView control using declarative syntax.

  • The third code example demonstrates how to display and update data in a GridView control.

  • The fourth code example demonstrates how to display and update data in a DropDownList control.

    NoteNote

    These examples show how to use declarative syntax for data access. For information about how to access data by using code instead of markup, see Accessing Data in Visual Studio.

The following code example demonstrates how to use a SqlDataSource control declaratively to retrieve data from SQL Server and display it in a GridView control.

<%@ Page Language="VB" %>

<!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>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          DataSourceMode="DataReader"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT FirstName, LastName, Title FROM Employees">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          DataSourceID="SqlDataSource1">
      </asp:GridView>

    </form>
  </body>
</html>
<%@ 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>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          DataSourceMode="DataReader"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT FirstName, LastName, Title FROM Employees">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          DataSourceID="SqlDataSource1">
      </asp:GridView>

    </form>
  </body>
</html>

The following code example demonstrates how to use a SqlDataSource control declaratively to retrieve data from an ODBC-compliant database and display it in a GridView control. The ProviderName property is the name of the .NET Framework Data Provider for ODBC, which is System.Data.Odbc.

<%@Page  Language="VB" %>
<!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>ASP.NET Example</title>
</head>
<body>
    <!-- This example uses a Northwind database that is hosted by an ODBC-compliant
         database. To run this sample, create an ODBC DSN to any database that hosts
         the Northwind database, including Microsoft SQL Server or Microsoft Access,
         change the name of the DSN in the ConnectionString, and view the page.
    -->
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ProviderName="System.Data.Odbc"
          DataSourceMode="DataReader"
          ConnectionString="dsn=myodbc3dsn;"
          SelectCommand="SELECT FirstName, LastName, Title FROM Employees">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          DataSourceID="SqlDataSource1">
      </asp:GridView>

    </form>
  </body>
</html>
<%@ 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>ASP.NET Example</title>
</head>
<body>
    <!-- This example uses a Northwind database that is hosted by an ODBC-compliant
         database. To run this sample, create an ODBC DSN to any database that hosts
         the Northwind database, including Microsoft SQL Server or Microsoft Access,
         change the name of the DSN in the ConnectionString, and view the page.
    -->
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ProviderName="System.Data.Odbc"
          DataSourceMode="DataReader"
          ConnectionString="dsn=myodbc3dsn;"
          SelectCommand="SELECT FirstName, LastName, Title FROM Employees">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          DataSourceID="SqlDataSource1">
      </asp:GridView>

    </form>
  </body>
</html>

The following code example demonstrates a common display and update scenario with the GridView control. As with the previous examples, data from the Northwind database is displayed in the GridView control. Additionally, because an UpdateCommand property is specified and the AutoGenerateEditButton property is set to true, you can edit and update the records with no additional code. The GridView control automatically handles adding parameters to the UpdateParameters collection and calls the Update method when the Update button in the GridView control is clicked.

<%@ Page Language="VB" %>

<!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>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          DataSourceMode="DataSet"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees"
          UpdateCommand="Update Employees SET FirstName=@FirstName,LastName=@LastName,Title=@Title WHERE EmployeeID=@EmployeeID">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          AutoGenerateColumns="False"
          DataKeyNames="EmployeeID"
          AutoGenerateEditButton="True"
          DataSourceID="SqlDataSource1">
          <columns>
              <asp:BoundField HeaderText="First Name" DataField="FirstName" />
              <asp:BoundField HeaderText="Last Name" DataField="LastName" />
              <asp:BoundField HeaderText="Title" DataField="Title" />
          </columns>

      </asp:GridView>
    </form>
  </body>
</html>
<%@ 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>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          DataSourceMode="DataSet"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees"
          UpdateCommand="Update Employees SET FirstName=@FirstName,LastName=@LastName,Title=@Title WHERE EmployeeID=@EmployeeID">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          AutoGenerateColumns="False"
          DataKeyNames="EmployeeID"
          AutoGenerateEditButton="True"
          DataSourceID="SqlDataSource1">
          <columns>
              <asp:BoundField HeaderText="First Name" DataField="FirstName" />
              <asp:BoundField HeaderText="Last Name" DataField="LastName" />
              <asp:BoundField HeaderText="Title" DataField="Title" />
          </columns>

      </asp:GridView>

    </form>
  </body>
</html>

The following code example demonstrates a common display and update scenario with the DropDownList and TextBox controls. The DropDownList control does not automatically add update parameters to the UpdateParameters collection nor call the Update method, so you must do so. The update parameters are specified declaratively and you can add an event handler to perform the Update operation when an event is raised.

Security noteSecurity Note

This example includes a text box that accepts user input, which is a potential security threat. By default, ASP.NET Web pages validate that user input does not include script or HTML elements. For more information, see Script Exploits Overview.

<%@Page  Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

 Sub On_Click(ByVal source As Object, ByVal e As EventArgs)
    Try
        SqlDataSource1.Update()
    Catch except As Exception
        ' Handle the Exception.
    End Try

    Label2.Text="The record was updated successfully!"

 End Sub 'On_Click
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">
      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT EmployeeID, LastName, Address FROM Employees"
          UpdateCommand="UPDATE Employees SET Address=@Address WHERE EmployeeID=@EmployeeID">
          <UpdateParameters>
              <asp:ControlParameter Name="Address" ControlId="TextBox1" PropertyName="Text"/>
              <asp:ControlParameter Name="EmployeeID" ControlId="DropDownList1" PropertyName="SelectedValue"/>
          </UpdateParameters>
      </asp:SqlDataSource>

      <asp:DropDownList
          id="DropDownList1"
          runat="server"
          DataTextField="LastName"
          DataValueField="EmployeeID"
          DataSourceID="SqlDataSource1">
      </asp:DropDownList>

      <br />
      <asp:Label id="Label1" runat="server" Text="Enter a new address for the selected user."
        AssociatedControlID="TextBox1" />
      <asp:TextBox id="TextBox1" runat="server" />
      <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />

      <br /><asp:Label id="Label2" runat="server" Text="" />
    </form>
  </body>
</html>
<%@Page  Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
 private void On_Click(Object source, EventArgs e) {
    try {
        SqlDataSource1.Update();
    }
    catch (Exception except) {
        // Handle the Exception.
    }

    Label2.Text="The record was updated successfully!";
 }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">
      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT EmployeeID, LastName, Address FROM Employees"
          UpdateCommand="UPDATE Employees SET Address=@Address WHERE EmployeeID=@EmployeeID">
          <UpdateParameters>
              <asp:ControlParameter Name="Address" ControlId="TextBox1" PropertyName="Text"/>
              <asp:ControlParameter Name="EmployeeID" ControlId="DropDownList1" PropertyName="SelectedValue"/>
          </UpdateParameters>
      </asp:SqlDataSource>

      <asp:DropDownList
          id="DropDownList1"
          runat="server"
          DataTextField="LastName"
          DataValueField="EmployeeID"
          DataSourceID="SqlDataSource1">
      </asp:DropDownList>

      <br />
      <asp:Label id="Label1" runat="server" Text="Enter a new address for the selected user."
        AssociatedControlID="TextBox1" />
      <asp:TextBox id="TextBox1" runat="server" />
      <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />

      <br /><asp:Label id="Label2" runat="server" Text="" />

    </form>
  </body>
</html>
Version Information

.NET Framework

Supported in: 4.5, 4, 3.5, 3.0, 2.0
Platforms

Windows 8.1, Windows Server 2012 R2, Windows 8, Windows Server 2012, Windows 7, Windows Vista SP2, Windows Server 2008 (Server Core Role not supported), Windows Server 2008 R2 (Server Core Role supported with SP1 or later; Itanium not supported)

The .NET Framework does not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.

Thread Safety
Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.