articles

Home / DeveloperSection / Articles / SqlDataSource in ASP.Net

SqlDataSource in ASP.Net

Pushpendra Singh 12193 02-Nov-2010

SqlDataSource is intended to replace the ADO.NET code you would normally write in a page to create a connection and command to query a database. Because the data queries are specified directly as properties of the data source control, this is sometimes called a two-tier model, because the data queries are still maintained in page code.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>

BindData in GridView using SqlDataSource:

.aspx page

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
</asp:GridView>   
<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="insert_reg" ConnectionString="<%$ ConnectionStrings:mycon %>" SelectCommandType="StoredProcedure">
</asp:SqlDataSource>

Insert_reg is a StoredProcedure created in backend.which described below.In ConnectionString mycon is an object created in Web.config.

mycon is connection object created in web.config which is described below.

 

SqlDataSource in ASP.Net

SqlDataSource in ASP.Net

 

.aspx code

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:mycon  %>"
            SelectCommand="insert_reg" UpdateCommand="update_reg" UpdateCommandType="StoredProcedure"
            InsertCommand="insert into regform(id,pass,name) values(@id,@pass,@name)" DeleteCommand="del_reg"
            DeleteCommandType="StoredProcedure">
            <DeleteParameters>
                <asp:ControlParameter Name="id" ControlID="GridView2" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:FormParameter Name="id" FormField="TextBox6" />
                <asp:FormParameter Name="pass" FormField="txtbx1" />
                <asp:FormParameter Name="name" FormField="TextBox2" />
            </UpdateParameters>
            <InsertParameters>
                <asp:FormParameter Name="id" FormField="TextBox3" />
                <asp:FormParameter Name="pass" FormField="TextBox4" />
                <asp:FormParameter Name="name" FormField="TextBox5" />
            </InsertParameters>
        </asp:SqlDataSource>
  <asp:GridView ID="GridView2" HeaderStyle-BackColor="dodgerblue" HeaderStyle-ForeColor="white" runat="server" DataKeyNames="id" DataSourceID="SqlDataSource2" ShowFooter="True"AllowPaging="True" EnableModelValidation="True" OnRowCommand="GridView2_RowCommand" OnRowUpdating="GridView2_RowUpdating" OnRowEditing="GridView2_RowEditing" OnRowCancelingEdit="GridView2_RowCancelingEdit"AutoGenerateColumns="False">
 
 <Columns>
 <asp:TemplateField>
 <ItemTemplate>
 <asp:Label ID="lbl2" runat="server" Text="User ID" Style="font-family: Calibri; font-size: small"></asp:Label>&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:Label ID="Label1" runat="server" Text='<%#Eval("id") %>' Style="font-family: Calibri;
                            font-size: small"></asp:Label>&nbsp;&nbsp;
  <asp:Label ID="Label7" runat="server" Text="Password" Style="font-family: Calibri;font-size: small"></asp:Label>&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:Label ID="Label2" runat="server" Text='<%#Eval("pass") %>' Style="font-family: Calibri;
                            font-size: small"></asp:Label>&nbsp;&nbsp;
 <asp:Label ID="Label8" runat="server" Text="User Name" Style="font-family: Calibri;
      font-size: small"></asp:Label>&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:Label ID="Label6" runat="server" Text='<%#Eval("name ") %>' Style="font-family: Calibri;
                            font-size: small"></asp:Label>
                        <asp:LinkButton ID="LinkButton3" runat="server" CommandName="delete" OnClick="LinkButton3_Click">DELETE</asp:LinkButton>
                        <asp:LinkButton ID="LinkButton1" runat="server" CommandName="edit">edit</asp:LinkButton>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox6" runat="server" Text='<%#Eval("id")%>'>'></asp:TextBox>
                        <asp:TextBox ID="txtbx1" runat="server" Text='<%#Eval("pass")%>'>'></asp:TextBox>
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%#Eval("name")%>'>'></asp:TextBox>
                        <asp:LinkButton ID="LinkButton4" runat="server" CommandName="cancel">cancel</asp:LinkButton>
                        <asp:LinkButton ID="LinkButton1" runat="server" CommandName="update">update</asp:LinkButton>
                    </EditItemTemplate>
  <FooterTemplate>
  <asp:Label ID="Label3" runat="server" Text=" User ID " Style="font-family: Calibri;font-size: small"></asp:Label>&nbsp;&nbsp;&nbsp;&nbsp;
  <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
         <br />
  <asp:Label ID="Label4" runat="server" Text=" Password " Style="font-family: Calibri;font-size: small"></asp:Label>&nbsp;
  <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
        <br />
 <asp:Label ID="Label5" runat="server" Text="User Name" Style="font-family: Calibri; font-size: small"></asp:Label>
 <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
                        <asp:LinkButton ID="LinkButton2" runat="server" CommandName="ADD" Style="font-family: Calibri;
                            font-size: small">Add</asp:LinkButton>
                    </FooterTemplate>
                </asp:TemplateField>
            </Columns>
            <HeaderStyle BackColor="DodgerBlue" ForeColor="White"></HeaderStyle>
        </asp:GridView>

 

RowEditing event is used to enable editing in GridView

protected void GridView2_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView2.EditIndex = e.NewEditIndex;
}

 

RowCancelingEdit event is used to cancel edit mode.

   

protected void GridView2_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView2.EditIndex = -1;
Response.Redirect("Default3.aspx");
}

The zero-based index of the row to edit. EditIndex = -1, which indicates that no row is being edited.

Add , updateand Delete data in GridView using SqlDataSource

 

The RowCommand event is raised whenever any button associated with a row in the GridView is clicked. This provides for programmatically determining which specific command button is clicked and take appropriate action.

Here this event is used for Inserting data

protected void GridView2_RowCommand(object sender, GridViewCommandEventArgse)
{
      string strid = (TextBox) GridView2.FooterRow.FindControl("TextBox3")).Text;
 
string strpd = ((TextBox)GridView2.FooterRow.FindControl ("TextBox4")).Text;
 
string strname = ((TextBox)GridView2.FooterRow.FindControl ("TextBox5")).Text;
 
SqlDataSource2.InsertParameters["id"].DefaultValue = strid;
SqlDataSource2.InsertParameters["pass"].DefaultValue = strpd;
SqlDataSource2.InsertParameters["name"].DefaultValue = strname;
 
SqlDataSource2.Insert();       
}

SqlDataSource in ASP.Net

Row updating event is used for updating data

protected void GridView2_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
string strFirstName = ((TextBox) GridView2.Rows[e.RowIndex].FindControl("TextBox6")).Text;
 
string strLastName = ((TextBox) GridView2.Rows[e.RowIndex].FindControl("txtbx1")).Text;
 
string strDepartment = ((TextBox) GridView2.Rows[e.RowIndex].FindControl("TextBox2")).Text;
 
SqlDataSource2.UpdateParameters["id"].DefaultValue = strFirstName;
SqlDataSource2.UpdateParameters["pass"].DefaultValue = strLastName;
SqlDataSource2.UpdateParameters["name"].DefaultValue = strDepartment;
           
SqlDataSource2.Update();
}

SqlDataSource in ASP.Net

Row Deleting event is used to delete the data

protected void GridView2_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
SqlDataSource2.Delete();
}

Insert User Id, Password and User Name and then click Add when you click Add button, inserted data will add to the database.

SqlDataSource in ASP.Net

web.config:

<connectionStrings>
<add name="mycon" connectionString="Data Source=(local);Initial Catalog=my; User Id=sa; Password=sa" providerName="System.Data.SqlClient"/>
</connectionStrings>

Stored procedure:
This procedure is used to select data

Create procedure insert_reg
 
as
begin
select * from regform
end


This procedure is used to Delete data

create procedure del_reg
@id varchar(50)
as
begin
delete  from regform where id=@id
end


This procedure is used to Update  data

create procedure [dbo].[update_reg]
@id varchar(50),
@pass varchar(50),
@name varchar(50)
as
begin
update regform set pass=@pass,name=@name where id=@id
end

Updated 04-Mar-2020

Leave Comment

Comments

Liked By