how to write c# code for below stored procedure

Question

1.00/5 (1 vote)

See more:

Can Anyone Give c# code for following stored procedure....

SQL

create procedure proc1 @pro_id varchar(50),@pro_name varchar(50),@description varchar(50),@priority int,@pro_img varchar(50),@search_img1 varchar(50),@search_img2 varchar(50),@search_img3 varchar(50),@search_img4 varchar(50),@search_img5 varchar(50),@keyword1 varchar(50),@keyword2 varchar(50),@keyword3 varchar(50),@keyword4 varchar(50),@keyword5 varchar(50),@y_url varchar(50),@type varchar(50),@price float,@nType int
--@option varchar(50)
AS
BEGIN
if @nType='Submit'
begin
insert into product(pro_id,pro_name,description,priority,pro_img,search_img1,search_img2,search_img3,search_img4,search_img5,keyword1,keyword2,keyword3,keyword4,keyword5,y_url,type,price) values(@pro_id,@pro_name,@description,@priority,@pro_img,@search_img1,@search_img2,@search_img3,@search_img4,@search_img5,@keyword1,@keyword2,@keyword3,@keyword4,@keyword5,@y_url,@type,@price)
end
else if @nType='Update'
begin
update product set pro_id=@pro_id,pro_name=@pro_name,description=@description,priority=@priority,pro_img=@pro_img,search_img1=@search_img1,search_img2=@search_img2,search_img3=@search_img3,search_img4=@search_img4,search_img5=@search_img5,keyword1=@keyword1,keyword2=@keyword2,keyword3=@keyword3,keyword4=@keyword4,keyword5=@keyword5,y_url=@y_url,type=@type,price=@price where pro_id=@pro_id
end
END

Posted 18-Dec-11 19:30pm

M.Narmatha

Add a Solution

4 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)

Mehdi Gholam · Answer 1 · 2011-12-18T19:35:00

Solution 1

If the stored procedure had logic in it then it would be convertible to c# but since the code is doing data manipulation then you can't convert it to c# as it makes no sense.

Posted 18-Dec-11 19:35pm

Mehdi Gholam

LaxmikantYadav · Answer 2 · 2011-12-18T19:40:00

Better way to call stored procedure from C#.

try 
{
       string connect = System.Configuration.ConfigurationManager.AppSettings["conn"]; // Connection string
       SqlConnection scn = new SqlConnection(connect);
       string sp = "SpName";

       SqlCommand spcmd = new SqlCommand(sp, scn);
       spcmd.CommandType = CommandType.StoredProcedure
 
       SqlParameter theOrderID = new SqlParameter("@Parameter", SqlDbType.Int);
       ID = Convert.ToInt32(this.txt1.Text); // Parameter

       SqlDataReader dr;
       spcmd.Parameters.Add(ID); // Adding Parameter
       MessageBox.Show(spcmd.Parameters["@Parameter"].Value.ToString());
 
       scn.Open();
       dr = spcmd.ExecuteReader(); //SP call
       scn.Close();
       dr.Close();
}
catch (SqlException x)
{
       MessageBox.Show(x.Message.ToString());
}

Er. Dinesh Sharma · Answer 3 · 2011-12-18T19:56:00

You have one mistake in S.P your last parameter @nType is declared as int and you use as varchar in later S.P.

You can call S.P From C#.

C#

sqlcommand sqlobject=new sqlcommand();
sqlobject.CommandType=CommandType.StoredProcedure;
sqlobject.Commandtext="SpName";
sqlobject.commandParameter.Clear();
sqlobject.Paramaters.Addwithvalue("@Parmeter",paramavalue);
--
---
--so on for all paramters
at last according to your operation like insert/update you to send one parameters
if(Operation=="Insert")
{
   sqlobject.Paramaters.AddWithValue("@nType","insert");
}
else
{
   sqlobject.Paramaters.AddWithValue("@nType","update");
}
sqlobject.ExcuteNonQuery();

above code is dummy syntax are not checking.

thatraja · Answer 4 · 2011-12-18T19:54:00

Solution 3

Check this
C# ADO.NET Tutorial - Using Stored Procedures[^]
I think this will save your time
C# Code Generator for Stored Procedures[^]

Posted 18-Dec-11 19:54pm

thatraja

how to write c# code for below stored procedure

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0