Solved

SqlParameter without parameter names

Posted on 2013-11-16
7
246 Views
Last Modified: 2013-11-22
I need an example of using SqlParameter  in a C# program that does not specify the Parameter name when calling a stored procedure.
0
Comment
Question by:metro156
  • 4
  • 2
7 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39653619
I wander if it can be done using SqlParameter.

One thing for sure, you can do it by calling your SP in a plain query:

exec dbo.YourSPName Param1Value
0
 

Author Comment

by:metro156
ID: 39653625
exec dbo.YourSPName Param1Value can be executed in SQL Server studio code window.
I am asking for an example in a C# program.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39653635
does your SP returns a resultset?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39653646
here is an example:

try
{
    // Create and Open the SQL server connection object
    using (SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=HedgeFund_PRD;Integrated Security=SSPI;"))
    {
        con.Open();
        ////  Create a command object and specify the Stored Procedure name and connection as well
        SqlCommand cmd = new SqlCommand("exec dbo.spTest 1, 1, '1960-01-01', '2013-12-31'", con);
        //  Set the command object
        cmd.CommandType = CommandType.Text;
        // Execute the command
        using (SqlDataReader rd = cmd.ExecuteReader())
        {
            int count = 0;
            while (rd.Read())
            {
                count++;
            }
            MessageBox.Show(string.Format("There are {0} rows.", count));
        }
    }
}
catch (Exception e)
{
    MessageBox.Show(e.Message);
}
0
 

Author Comment

by:metro156
ID: 39668844
To clarify, I am looking for positional specification with SQLParameter.
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 400 total points
ID: 39668872
if you look at http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx, you will find that all the overloads of the SQLParameter constructor requires the name of the parameter, none allows the position.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39669124
In the SP call, you can use parameter names you define yourself, like @P1 and following, and then use the self-defined names for filling a SQLParameter collection.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now