multiple stored procedure in succession in a program segment

Question

0.00/5 (No votes)

See more:

I have a program segment with the undernoted source, however I want to split the

functionaliy in the program so that I will have 3 stored procedures in place of the

current one. so that on pressing the button the 3 stored procedures will be

performed in succession.

Please note the sending parameters are the same for all the 3 stored procedures to be run in succession on the selection of a button.

How do I accomplish this ?

C#

try
{
    sqlCon.Open();
    SqlCommand SqlCmd = new SqlCommand("sp_ProcessSimpleConvent", sqlCon);
    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
    
    //Create and supply the output parameters
    
    SqlCmd.Parameters.AddWithValue("@CMONTH", SqlDbType.Int).Value = txt_Period.Text;
    SqlCmd.Parameters.AddWithValue("@CYEAR", SqlDbType.Int).Value  = txt_Year.Text;
    
    
    
    SqlCmd.Parameters.Add("@RETURN", System.Data.SqlDbType.VarChar, 12);
    SqlCmd.Parameters["@RETURN"].Direction = System.Data.ParameterDirection.Output;
    
    SqlCmd.ExecuteNonQuery();

Assuming the stored procedures to be performed are

1. sp_simple

2. sp_Convent

3. sp_None

Please note the sending parameters are the same for all the 3 stored procedures to be run in succession on the selection of a button.

Thanks

Posted 1-Sep-14 12:51pm

Member 10744248

Updated 1-Sep-14 19:26pm

George Jonsson

v2

Add a Solution

Comments

[no name] 1-Sep-14 18:58pm

What do you mean how do you accomplish this? Call sp_simple, then call sp_Convent then call sp_None.

Member 10744248 1-Sep-14 19:13pm

Please site an example.

[no name] 1-Sep-14 19:18pm

You are kidding right? Refer back to your code for an example of how to call a stored procedure.

syed shanu 1-Sep-14 23:56pm

Why do you want to call 3 SP with same param.What are you doing in 3 SP explain bit more about your requirement

George Jonsson 2-Sep-14 1:34am

Do you have a problem with splitting the stored procedure into three smaller procedures or how to call three SP in sequence?
Your question is not really clear.

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)

ClimerChinna · Answer 1 · 2014-09-01T20:34:00

hi,

check below code i have printed a message on label each time when a procedure gets executed.

SqlConnection conn = new SqlConnection(your connection string);
        conn.Open();
        string[] proc = { "sp_simple", "sp_Convent", "sp_None" };
        for (int i = 0; i < proc.Length; i++)
        {
            SqlCommand cmd = new SqlCommand(proc[i], conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CMONTH", txt_Period.Text);
            cmd.Parameters.AddWithValue("@@CYEAR", txt_Year.Text);
            cmd.Parameters.AddWithValue("@RETURN", 12);
            cmd.Parameters["@RETURN"].Direction = System.Data.ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            label2.Text += proc[i] + "is executed.\n";
        }
        conn.Close();

but its better to write all the functionalities with in one procedure is better

Gihan Liyanage · Answer 2 · 2014-09-01T18:30:00

I didnt Test this code, Because today I dont have VS on my mechine, Please try to get the idea first. And then write your own code is better.

C#

main()
{
Thread t1 = new Thread(new ThreadStart(ThreadProc1));
Thread t2 = new Thread(new ThreadStart(ThreadProc2));
Thread t3 = new Thread(new ThreadStart(ThreadProc3));
t1.Start();
t2.Start();
t3.Start();
}

void ThreadProc1()
{
using (SqlConnection connection = new SqlConnection(
               yourConnectionString))
    {
        SqlCommand command = new SqlCommand("sp_simple", connection);
        command.Connection.Open();
        command.Connection.Open.CommandType = CommandType.StoredProcedure;
        SqlParameter par1= new SqlParameter("@CMONTHID", SqlDbType.NVarChar);
        SqlParameter par2 = new SqlParameter("@CYEAR", SqlDbType.NVarChar);
        command.Parameters.Add(par1,txt_Period.Text);
        command.Parameters.Add(par2,txt_Year.Text);
        command.ExecuteNonQuery();
    }
}

void ThreadProc2()
{
using (SqlConnection connection = new SqlConnection(
               yourConnectionString))
    {
        SqlCommand command = new SqlCommand("sp_Convent", connection);
        //Same As ThreadProc1
    }
}
void ThreadProc3()
{
using (SqlConnection connection = new SqlConnection(
               yourConnectionString))
    {
        SqlCommand command = new SqlCommand("sp_None", connection);

        //Same As ThreadProc1
    }
}

multiple stored procedure in succession in a program segment

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0