Call Stored Procedure

sugitha sugi

8y1kVisual Studio

How to call Storedprocedure from SQL server in console application how to connect

20 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramesh Palanivel8y ago
Accepted answer
Hi Sugitha,

print 'Employee Id = ' + cast(@EmpId as nvarchar(2)) What it says abt.

Take @EmpId value is 123 ,

The abouve line will going to print "Employee Id = 12"

Because the nvarchar has set it max length 2. If your employee id 123, the length will be three, it will take first two characters and will display 12 wil concatenation of "Employee id =" text.

Then "cast(@EmpId as nvarchar(2)) converting @empid value as varchar data type.
sugitha sugi8y ago
Hi Ramesh

Please explain this line of code

print 'Employee Id = ' + cast(@EmpId as nvarchar(2)) What it says abt.
Ramesh Palanivel8y ago
Hi sugitha,

CAST is a function which is used for type conversion.

Ex : If you want convert decimal value into int you can use cast,

CAST(12.34 AS INT) // The ourput will be 12

CAST(12.34 AS VARCHAR) //The output will be '12.34'
sugitha sugi8y ago
What is the use of CAST in Stored procedure
Manav Pandya8y ago
Hello

Use like this :
SqlConnection con=new SqlConnection( // your conn string);

// and than

con.Open();
You should use new operator to initialize connection
Thanks
sugitha sugi8y ago
Please Find the scrnshot and help in this facing with some errors
Manav Pandya8y ago
Hello

Just go to Sql server or Server Explorer [Visual studio]

Than right-click > properties

Copy and paste connection string

That's it
sugitha sugi8y ago
Hi
Yes i know i cant access you databse .i am asking about wat to be passed inside the new conn
Manav Pandya8y ago
You can paste your own connction string

You can not access my database , try your own

Thanks
sugitha sugi8y ago
Need SQL Connection String to call stored procedure
sugitha sugi8y ago
Thank you ..
Manav Pandya8y ago
Hello

As per your requirement data will be publish to console as well

find complete snippet below :
class Program
    {
        string ConnectionString = @"Your conn string here";
        static void Main(string[] args)
        {

            String id = "";
            Program p = new Program();
            DataTable newDt = new DataTable();
            newDt = p.AddRecord("Manav", "Pandya");
            for (int j = 0; j < newDt.Columns.Count; j++)
            {
                Console.Write(newDt.Columns[j].ToString() + "\t");
            }
            Console.WriteLine();
            for (int i = 0; i < newDt.Rows.Count; i++)
            {
                Console.Write(newDt.Rows[i]["Id"].ToString() + "\t");
                Console.Write(newDt.Rows[i]["Fname"].ToString() + "\t");
                Console.Write(newDt.Rows[i]["Lname"].ToString() + "\n");
            }
            Console.Read();
        }

        public DataTable AddRecord(string Fname, string Lname)
        {
            SqlConnection con = null;
            DataTable dt = new DataTable();
            try
            {
                con = new SqlConnection(ConnectionString);
                SqlCommand cmd = new SqlCommand("DemoProcedure", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Fname", Fname);
                cmd.Parameters.AddWithValue("@Lname", Lname);
                SqlDataAdapter ad = new SqlDataAdapter();
                ad.SelectCommand = cmd;
                ad.Fill(dt);
                con.Close();
                return dt;
            }
            catch (Exception e)
            {
                return dt;
            }
        }
    }
And stored procedure is :
ALTER PROCEDURE [dbo].[DemoProcedure]
    @Fname nvarchar(10)='' , @Lname nvarchar(10)=''
AS
BEGIN
    insert into StudentDetail values(@Fname,@Lname)
    select * from StudentDetail
END

GO
Output will be like :
Thanks
sugitha sugi8y ago
Hi
I need to return the whole table data is it possible to print the whole table and return it in the console
Manav Pandya8y ago
Hello

Yes i have seen , check my snippet and run it

You just need to return data whatever you want and result will be printed to console

Just try it , Thanks
sugitha sugi8y ago
Hi Manav
Have you seen my SP By that i need to develop a console application where the table details to be displayed in command prompt screen .hope you got my point

Thanks
Sugitha
Manav Pandya8y ago
Hello

I have developed complate example for this

Please find below snippet :
class Program
    {
        string ConnectionString = @" Your Connection String ";
        static void Main(string[] args)
        {
            String id = "";
            Program p = new Program();
            id = p.AddRecord("Manav", "Pandya");
            Console.WriteLine("Student Id Is :-" + id);
            Console.Read();
        }

        public string AddRecord(string Fname, string Lname)
        {
            SqlConnection con = null;
            string result = "";
            try
            {
                con = new SqlConnection(ConnectionString);
                SqlCommand cmd = new SqlCommand("DemoProcedure", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Fname", Fname);
                cmd.Parameters.AddWithValue("@Lname", Lname);
                con.Open();
                result = cmd.ExecuteNonQuery().ToString();
                con.Close();
                return result;
            }
            catch (Exception e)
            {
                return "Something Went Wrong !!!";
            }
        }
    }
And stored procedure Part Is :
ALTER PROCEDURE [dbo].[DemoProcedure]
    @Fname nvarchar(10)='' , @Lname nvarchar(10)=''
AS
BEGIN
    insert into StudentDetail values(@Fname,@Lname)
    Return SCOPE_IDENTITY()
END

GO

Declare @Stid int
execute  @Stid = [dbo].DemoProcedure
         @Fname = 'Manav' ,
         @Lname = 'Pandya'
print 'Student Id Is :-' + cast( @Stid as nvarchar(5))
Hope you getting me
Thanks
sugitha sugi8y ago
i have tried all the link which shared by you ppl
sugitha sugi8y ago
Hi
Any other Procedure to call the stored procedure .
i have created the stored procedure for employee details that stored procedure name to be called in the console application please provide me with the exact code . How to start with this .
Manav Pandya8y ago
Hello

Please follow article link given below :

http://www.c-sharpcorner.com/uploadfile/mahesh/executing-a-stored-procedure-programmatically/

http://csharp-station.com/Tutorial/AdoDotNet/Lesson07

Thanks
Ramesh Palanivel8y ago
Hi Sugitha,

Please look the below URL,

http://www.c-sharpcorner.com/uploadfile/mahesh/executing-a-stored-procedure-programmatically/