CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
Maattt
Maattt

Reputation: 177

How to create a stored procedure that inserts data into a table from a Visual Studio Application

The code I have given is what I have now in my button click event, but instead of directly inputting the SQL statement I need to use a stored procedure. So basically I need to convert this code from Visual Studio 2010 into a stored procedure in SQL Server 2008.

command.Connection = conn;
        command.CommandType = CommandType.Text;
        command.CommandText = **"INSERT INTO Customer VALUES(" + txtCustID.Text + ",'"+        txtFirstName.Text + "', '" + txtSurname.Text + "', " + txtAge.Text + ")";**
        command.Connection.Open();

        adapter.InsertCommand = command;
        adapter.InsertCommand.ExecuteNonQuery();

        command.Connection.Close();
        Clear();

Upvotes: 0

Views: 7463

Answers (3)

logixologist
logixologist

Reputation: 3834

Here is what you need to do. Note I have not tested this code out but you should be close. You might need to tweak it with intellisense.

1) In SQL Server Management Studio open a New Query Editor Window by doing CTRL-N

2) Start each procedure with the following

CREATE Procedure ProcedureName

--Add Any Parameters that you will be passing in this way:
@parameterName datatype
--Add any additional parameters as needed by adding a comma at the end of the previous line
--And adding a new one : @parameterName datatype, @parameterName2 datatype

AS

BEGIN
--Here is where your write your stored procedure:
INSERT INTO Customer (custId, firstname, surname, age) 
VALUES(@custId,@firstname, @surname, @age)
END

3) In SQL Server Management Studio, execute the script. Your stored procedure is now saved in SQL You can always call it using exec sprocName parameter1, parameter 2.... If you need to pull it back up again to edit it, you simply go into Programmability --> Stored Procedures, find the procedure and right click and MODIFY. If you hit execute it will make the changes you made.

4) in your .NET Code do this:

    command.Connection = conn;
    command.CommandType = CommandType.StoredProcedure; //Look up the actual code using intellisense as I dont recall what CommandType a stored Procedure is.
    command.paramaters.addwithvalue (@age, txtAge.text)  //using this as an example. Youw ill actually need to put in the same variables declared in SQL and where they correspond to on the screen.

 command.Connection.Open();

    adapter.InsertCommand = command;
    adapter.InsertCommand.ExecuteNonQuery();

    command.Connection.Close();
    Clear();

If you notice, there is a parameters.addwithvalue part. This is the best way to pass parameters to the stored procedure. No reason to worry about apostrophes (you still need commas of course). Its far safer and will reduce risk of SQL Injection Attack (a way for people to enter code into your app and make it do really bad things to SQL Server)

I hope this helps... good luck!

Upvotes: 3

Rashedul.Rubel
Rashedul.Rubel

Reputation: 3584

You can try one of the following ways

At first Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration and then execute this procedure from your application as EXEC procedureName

Or

instead create your stored procedure using sql server management studio and execute the procedure from your application like 

exec storedProcedureName

Thanks

Upvotes: -1

ARA
ARA

Reputation: 1316

You simply create a stored procedure by writing your procedure code in a 'New Query' window of Sql MAnagement Studio. For example you code could be:

    -- drop the procedure if it exists
    -- in order to recreate it
    if object_id('usp_newCustomer') is not null
        drop procedure usp_newCustomer
    go
    -- procedure new customer 
    create procedure usp_NewCustomer 
            -- parameters of the procedure
            @custID integer,
            @firstName nvarchar(32),
            @surName nvarchar(32),
            @age integer
    as
    -- code of the procedure
    begin
        -- insert into cutomer the values passed as parameters
        insert into Customer( custId, firstName, surName, age )
            values (@custId, @firstNAme, @surName, @age );
    end

and in your application instead of doing the insert (in command.text) you can call your procedure with:

exec usp_NewCustomer 1, 'Johny', 'BeGood', 85

Upvotes: -1

Related Questions