Asp.net Sql DataSource control

SqlDataSource Control is a data source control that is used to connect to the relational database.

SqlDataSource Control is a data source control that is used to connect to the relational database. You can also connect to Oracle or any other data source which can be accessed through the OLEDB and ODBC, there are two properties used to connect the connection string and provider names to the database. Specify a connection string first and specify another provider name. By default the provider is System.Data.SqlClient, that means connect to the SQL Server database.

Following are some important properties that are very useful.

Properties for Configuring Data Source
InsertCommand, InsertParameters, InsertCommandType	Gets or sets the SQL Statement, parameter and type of command (text or stored procedure) to insert a record.
DeleteCommand, DeleteParameters, DeleteCommandType	Gets or sets the SQL Statement, parameters and type of the command (text or stored procedure) to delete a record.
UpdateCommand, UpdateParameters, UpdateCommandType	Gets or sets the SQL Statement, parameters and type of the command (text or stored procedure) to update a record.
SelectCommand, SelectParameters, SelectCommandType	Gets or sets the SQL Statement, parameters and type of the command (text or stored procedure) to select record(s).
Parameter Types of DataSource Controls
ControlParameter	Gets the value from any public property of the server control.
FormParameter	Gets the value of any input field from the form.
QueryStringParameter	Gets the value from specified querystring.
Parameter	Gets the parameter value assigned in the code.
Other Properties of SqlDataSource Control
ProviderName	Gets or sets the .NET ProviderName name.
DataSourceMode	DataSet/DataReader. Used to specify the data source mode. In case of DataReader paging is not supported in the target control (GridView etc.).
ConnectionString	Gets or sets the connection string to connect to the database.
ConflictDetection	CompareAllValues/OverWriteChanges. Used to determine how conflict will be handled in case of updation or deletion of the records.
Caching Properties of SqlDataSource Control
EnableCaching	true/false. Used to indicate whether enable caching or not.
CacheDuration	Used to indicate number of seconds the data shoud be maintained in the cache.
CacheExpirationPolicy	Absolute/Sliding. Used to indicate if the cache policy is absolute or sliding. Absolute: The data is removed after specified duration. Sliding: The data is removed if it is not used for specified duration.
EnableCaching	true/false. Used to indicate whether enable caching or not.

This is for select data from a database.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:VendorConnectionString %>" SelectCommand="SELECT [VendorId], [VendorFName], [VendorLName], [VendorCity], [VendorState], [VendorCountry], [PostedDate], [VendorDescription] FROM [Vendor]"></asp:SqlDataSource>

This is for update data into a database:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:VendorConnectionString %>"

SelectCommand="SELECT [VendorId], [VendorFName], [VendorLName], [VendorCity], [VendorState], [VendorCountry], [PostedDate], [VendorDescription] FROM [Vendor]"

UpdateCommand="UPDATE Vendor SET VendorFName = @VendorFName, VendorLName = @VendorLName, VendorCity = @VendorCity, VendorState = @VendorState, VendorCountry = @VendorCountry, VendorDescription = @VendorDescription WHERE VendorId = @VendorId">          

            <UpdateParameters>            <asp:FormParameter Name="VendorId" FormField="VendorId" />            <asp:FormParameter Name="VendorFName" FormField="VendorFName" />            <asp:FormParameter Name="VendorLName" FormField="VendorLName" />            <asp:FormParameter Name="VendorCity" FormField="VendorCity" />            <asp:FormParameter Name="VendorState" FormField="VendorState" />           <asp:FormParameter Name="VendorCountry" FormField="VendorCountry" />            <asp:FormParameter Name="VendorDescription" FormField="VendorFName" />             
            </UpdateParameters>           
        </asp:SqlDataSource>    

This is for insert data into a database:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:VendorConnectionString %>"           

SelectCommand="SELECT [VendorId], [VendorFName], [VendorLName], [VendorCity], [VendorState], [VendorCountry], [PostedDate], [VendorDescription] FROM [Vendor

InsertCommand="INSERT INTO Vendor(VendorFName, VendorLName, VendorCIty, VendorState, VendorCountry, VendorDescription) VALUES (@VendorFName, @VendorLName, @VendorCity, @VendorState, @VendorCountry, @VendorDescription)"         

            <InsertParameters>            <asp:FormParameter Name="VendorFName" FormField="VendorFName" />            <asp:FormParameter Name="VendorLName" FormField="VendorLName" />            <asp:FormParameter Name="VendorCity" FormField="VendorCity" />            <asp:FormParameter Name="VendorState" FormField="VendorState" />            <asp:FormParameter Name="VendorCountry" FormField="VendorCountry" />            <asp:FormParameter Name="VendorDescription" FormField="VendorFName" />            
            </InsertParameters>            
        </asp:SqlDataSource>      

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
            AutoGenerateColumns="False" BackColor="White" BorderColor="#E7E7FF" 
            BorderStyle="None" BorderWidth="1px" CellPadding="3" DataKeyNames="VendorId" 
            DataSourceID="SqlDataSource1" GridLines="Horizontal">            <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />            <Columns>                <asp:BoundField DataField="VendorId" HeaderText="VendorId" 
                    InsertVisible="False" ReadOnly="True" SortExpression="VendorId" />                <asp:BoundField DataField="VendorFName" HeaderText="VendorFName" 
                    SortExpression="VendorFName" />                <asp:BoundField DataField="VendorLName" HeaderText="VendorLName" 
                    SortExpression="VendorLName" />                <asp:BoundField DataField="VendorCity" HeaderText="VendorCity" 
                    SortExpression="VendorCity" />                <asp:BoundField DataField="VendorState" HeaderText="VendorState" 
                    SortExpression="VendorState" />                <asp:BoundField DataField="VendorCountry" HeaderText="VendorCountry" 
                    SortExpression="VendorCountry" />                <asp:BoundField DataField="PostedDate" HeaderText="PostedDate" 
                    SortExpression="PostedDate" />                <asp:BoundField DataField="VendorDescription" HeaderText="VendorDescription" 
                    SortExpression="VendorDescription" />            </Columns>            <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />            <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />            <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />            <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />            <AlternatingRowStyle BackColor="#F7F7F7" />        </asp:GridView>

Share on Google Plus

About It E Research

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.