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.
.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>
<asp:Label ID="Label1" runat="server" Text='<%#Eval("id") %>' Style="font-family: Calibri;
font-size: small"></asp:Label>
<asp:Label ID="Label7" runat="server" Text="Password" Style="font-family: Calibri;font-size: small"></asp:Label>
<asp:Label ID="Label2" runat="server" Text='<%#Eval("pass") %>' Style="font-family: Calibri;
font-size: small"></asp:Label>
<asp:Label ID="Label8" runat="server" Text="User Name" Style="font-family: Calibri;
font-size: small"></asp:Label>
<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>
<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>
<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();
}
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();
}
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.
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
Leave Comment