please help me to write storedprocedure calling function in c#

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

hi guys,
i created a stored procedure in sql server2008 and executed successfully...
now i want to execute that stored procedure through c# windows application....
i looked some code examples in internet there they took three different stored procedures for insert, update and delete. but i took three operations in a single stored procedure...

the code i written for stored procedure in sql is...

SQL

go
create procedure MasterInsertUpdateDelete
(
@sno integer,
@name varchar(50),
@phone bigint,
@city varchar(50),
@statementType nvarchar(20)=''
)

as
begin
if @statementType= 'Insert'
begin
insert into chaitu (sno, name, phone, city) values(@sno, @name, @phone, @city)
end

if @statementType='Select'
begin
select * from chaitu
end

if @statementType='Update'
begin
update chaitu set name=@name, phone=@phone, city=@city
where sno=@sno
end

else if @statementType='Delete'
begin
delete from chaitu where sno=@sno
end
end

what code should i have to write in c# windows form to call this storedprocedure... and i have three different buttons "Insert", "Update", "Delete".

each of them should perform their respective tasks, using this single stored procedure....
please help me...

Posted 3-Jul-14 20:09pm

chaitanya556

Add a Solution

Comments

Aadyakumar 4-Jul-14 2:27am

Don't use * with select ,if you specify column name it give better performance .

select sno, name, phone, city from [chaitu]

Prasad Avunoori 4-Jul-14 2:35am

Yes, You are right. '*' should be avoided in stored procedures for scalable applications.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad Avunoori · Accepted Answer · 2014-07-03T20:22:00

C#

private void InsertDeletUpdate(string fName,Int64 phoneNumber,string city,string statementType)
        {
            using (SqlConnection con = new SqlConnection("Your Connection string here")
            {
                using (SqlCommand cmd = new SqlCommand("MasterInsertUpdateDelete", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = fName;
                    cmd.Parameters.Add("@phone", SqlDbType.BigInt).Value = phoneNumber;
                    cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = city;
                    cmd.Parameters.Add("@statementType", SqlDbType.NVarChar).Value = statementType;

                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

syed shanu · Accepted Answer · 2014-07-03T20:35:00

Hi,
Check like this .I have made a simple workout for you.

create common function and pass your SP Statement type .

C#

// Import this 
using System.Data;
using System.Data.SqlClient;

// Btn Insert call the function and pass "Insert" as argument
 private void btnInsert_Click(object sender, EventArgs e)
        {
            Execute_SQLSP("Insert");
        }

// Btn Update call the function and pass "Update" as argument
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            Execute_SQLSP("Update");
        }

// Btn Select call the function and pass "Select" as argument
        private void btnDelete_Click(object sender, EventArgs e)
        {
            Execute_SQLSP("Select");
        }


/Function to Execute Sp from your c# Code.
  public void Execute_SQLSP(String SP_statementType)
        {
            try
            {
                SqlConnection myConnection = new SqlConnection(dbConnectionString);
                SqlCommand("MasterInsertUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add(new SqlParameter("@sno ", SqlDbType.Int)).Value = yourIntegersno;
                myCommand.Parameters.Add(new SqlParameter("@name ", SqlDbType.VarChar)).Value = "YOUR_NAME";
                myCommand.Parameters.Add(new SqlParameter("@phone  ", SqlDbType.BigInt)).Value = yourphone;
                myCommand.Parameters.Add(new SqlParameter("@city  ", SqlDbType.VarChar)).Value = "YOUR_city ";
                myCommand.Parameters.Add(new SqlParameter("@statementType   ", SqlDbType.NVarChar)).Value = SP_statementType;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
                mySqlDataAdapter.SelectCommand = myCommand;
                DataSet yourDataSet = new DataSet();
                mySqlDataAdapter.Fill(yourDataSet);
                myConnection.Close();
            }
            catch (SqlException ex)
            {
                
            }
        }

please help me to write storedprocedure calling function in c#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0