Dave Dave - 1 month ago 8
ASP.NET (C#) Question

Using stored procedures in C# ASP.NET

I have very basic c# knowledge and have not used it in over a year, so I have come up with a small project that I would like to work through. I require some help with the stored procedures however:

I have a table which has already been set up as well as the connection to the DB

Table to store horses:

o ID
o Name(must be unique)
o Age (Age can only be 1 - 17)
o Member_ID


And I would like to perform the following stored procedures-

Create stored procedures to:

o Insert Horse
o Update Horse
o Get Horse


At the moment this page is still empty:

horse.aspx
:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Horse.aspx.cs" Inherits="Horse" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Edit Horse Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>Name</td>
<td><input type="text" name="Name" /></td>
</tr>
<tr>
<td>Age</td>
<td><input type="text" name="Age" /></td>
</tr>
</table>
<input type="submit" value="Submit" />
</div>
</form>
</body>
</html>


horse.aspx.cs
:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Horse : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
}


I was thinking that the best way to display and edit the table would be to use a gridview but was not sure

What I would like to know is how to perform the different actions using the stored procedures

I will appreciate any input and thank you in advance

Answer

You can create your stored procedure in SQL Server as:

CREATE PROC procTest
(
    @flag VARCHAR(1),
    @parameter1 VARCHAR(50),
    @paramter2 VARCHAR(25)  
)
AS
BEGIN
    IF(@flag='I')
    BEGIN
        -- insert statement
    END
    ELSE IF(@flag='U')
    BEGIN
        -- update statement
    END
    ELSE IF(@flag='D')
    BEGIN
        -- delete statement
    END
    ELSE
        BEGIN
            -- select statement
        END
END

In your code behind create a method as below and call it from where you want :

public void Crud(string flag, string parameter1, string parameter2) 
 {
        // take ConnectionString as defined in your web.config or as per your requirement.
        SqlConnection cn = new SqlConnection("ConnectionString");

        SqlCommand cmd = new SqlCommand("StoredProcedure");
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@flag", SqlDbType.VarChar, 1).Value = flag; // I for Insert/ U for Update/ D for delete/ S for select
        cmd.Parameters.Add("@Parameter1", SqlDbType.VarChar, 50).Value = parameter1;
        cmd.Parameters.Add("@Parameter2", SqlDbType.VarChar, 25).Value = parameter2;

        cn.Open();

        if (flag != "S")
        {
            cmd.ExecuteNonQuery();
        }
        else
        {
            // If you have grid view you can bind it here.
            gridView.DataSource = cmd.ExecuteReader();
        }

        cn.Close();
    }
Comments