CODEWITHSUNDEEP
c#asp.netdatabasestored-proceduresbutton
Soner Gönül
Soner Gönül

Reputation: 98810

Database Insert and Delete with Stored Procedure

In my .aspx page, i have two textbox and one add button and one delete button.

I want simply, when entering data textboxes and click add button, adding to database with stored procedure.

When entering data textboxes and click delete button, delete from database with stored procedure.

How can I do that?

Simply I need 4 code part, add_click(), delete_click(), sp_add, sp_delete

How can I develop this 4 function?

Upvotes: 0

Views: 33515

Answers (1)

marc_s
marc_s

Reputation: 754963

Since you're giving us any hint what your table(s) look like and what your textboxes will be, this is just a "template" like approach.

BTW: you should not prefix your stored procedures with sp_ - that prefix is reserved for Microsoft.

proc_delete

Idea: you just pass in the primary key (usually an ID of some sort) to be deleted:

CREATE PROCEDURE dbo.proc_delete(@PrimaryKey INT)
AS
   DELETE FROM dbo.YourTable
   WHERE ID = @PrimaryKey

proc_add

You don't really tell us much about what you want to do ..... so here is one possibility

CREATE PROCEDURE dbo.proc_add(@value1 VARCHAR(50), @value2 VARCHAR(50))
AS 
    INSERT INTO dbo.YourTable(Value1, Value2)
    VALUES(@value1, @value2)

From your ASP.NET code, you would have to do the following:

  • create a SqlConnection to the database
  • create a suitable SqlCommand to execute the command you want
  • set up the parameters and fill their values
  • execute the proc

So in the case of the delete_click(), you'd have something like:

public void delete_click()
{
    using(SqlConnection _con = new SqlConnection(-your-connection-string-here-))
    using(SqlCommand _cmdDelete = new SqlCommand(_con, "dbo.proc_delete"))
    {
        _cmdDelete.CommandType = CommandType.StoredProcedure;

        // add parameter
        _cmdDelete.Parameters.Add("@PrimaryKey", SqlDbType.Int);
        _cmdDelete.Parameters["@PrimaryKey"].Value = (your key value here);

        // open connection, execute command, close connection
        _con.Open();
        _cmdDelete.ExecuteNonQuery();
        _con.Close();
    }
}

That would be the rough outline of how to do it - I'll leave the add_click() up to you!

Upvotes: 8

Related Questions