Programming Journal C#, Java, SQL and to a lesser extent HTML, CSS, XML, and regex. I made this so other programmers could benefit from my experience.

Thursday, July 3, 2008

Using a GridView with Edit and Delete that use stored procedures

Here is how to use a GridView with Edit and Delete that use stored procedures for update and delete. First, the aspx:

<asp:GridView ID="gvEmail" runat="server" AutoGenerateDeleteButton="True"
AutoGenerateEditButton="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
AllowPaging="True" OnRowDeleting="gvEmail_RowDeleting" PageSize="99" OnRowUpdating="gvEmail_OnRowUpdating">
<Columns>
<asp:BoundField DataField="email" HeaderText="email" SortExpression="email" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [email] FROM [vw_Email]"
DeleteCommand="sproc_DeleteEmail" DeleteCommandType="StoredProcedure"
UpdateCommand="sproc_UpdateEmail" UpdateCommandType="StoredProcedure"
>
<DeleteParameters><asp:Parameter Name="email" Type="String" /></DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="email" Type="String" />
<asp:Parameter Name="newEmail" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
Now the code behind.

protected void gvEmail_RowDeleting(object sender, GridViewDeleteEventArgs e) //DELETE
{
SqlConnection conn = new SqlConnection(SqlDataSource1.ConnectionString);
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("sproc_DeleteEmail", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
string email = string.Empty;
DataControlFieldCell cell = gvEmail.Rows[e.RowIndex].Cells[1] as DataControlFieldCell;
gvEmail.Columns[0].ExtractValuesFromCell(
e.Keys,
cell,
DataControlRowState.Normal,
true);
email = e.Keys[0].ToString();
if (!string.IsNullOrEmpty(email))
{
command.Parameters.AddWithValue("@email", email);
command.ExecuteNonQuery();
}
command.Parameters.Clear();
conn.Close();
}
protected void gvEmail_OnRowUpdating(object sender, GridViewUpdateEventArgs e)
{
SqlConnection conn = new SqlConnection(SqlDataSource1.ConnectionString);
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("sproc_UpdateEmail", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
string newEmail = e.NewValues[0] as string;
string email = e.OldValues[0] as string;
if (!string.IsNullOrEmpty(email))
{
command.Parameters.AddWithValue("@email", email);
command.Parameters.AddWithValue("@newEmail", newEmail);
command.ExecuteNonQuery();
}
command.Parameters.Clear();
conn.Close();
}
Note: Exception handling left off for brevity.
Reference: http://www.developerfusion.co.uk/show/91/7/

6 comments:

sanjeet said...

how i delete and update in the gridview with the help of stored procedure in disconnected mode

Anonymous said...

Furaguryila [url=http://wiki.openqa.org/display/~buy-zithromax-without-no-prescription-online]Buy Zithromax without no prescription online[/url] [url=http://wiki.openqa.org/display/~buy-flomax-without-no-prescription-online]Buy Flomax without no prescription online[/url]

Anonymous said...

My friend and I were recently talking about the ubiquitousness of technology in our daily lives. Reading this post makes me think back to that debate we had, and just how inseparable from electronics we have all become.


I don't mean this in a bad way, of course! Ethical concerns aside... I just hope that as technology further innovates, the possibility of uploading our memories onto a digital medium becomes a true reality. It's one of the things I really wish I could see in my lifetime.


(Posted on Nintendo DS running [url=http://www.leetboss.com/video-games/r4i-r4-sdhc-nintendo-ds]R4i SDHC[/url] DS FFOpera)

ashwini said...

can show how did u write the store procedure for this

Anonymous said...

Hey i am suuper boy

Anonymous said...

My developer is trying to convince me to move to .net from PHP.
I have always disliked the idea because of the costs.
But he's tryiong none the less. I've been using Movable-type on several websites for about a year and am nervous about switching to another platform.
I have heard great things about blogengine.
net. Is there a way I can transfer all my wordpress posts into it?

Any help would be really appreciated!
my web site > funnymariogames