Need help for run parameterized stored procedure in sql

Question

3.00/5 (1 vote)

See more:

Hi all
I write a program in C# that need to run a stored procedure in SQL.
when i run the project, C# raise an error with the following details:

Could not find stored procedure 'Use Library EXEC MySP_BookInsert 'C++','Herbert','540''.

My codes that i write is:

C#

public Book_SP_Controller()
{
    Con1 = new SqlConnection("DATA SOURCE=(local);INITIAL CATALOG=Library;INTEGRATED SECURITY=true;");
    Con1.Open();

    Com1 = new SqlCommand();
    Com1.Connection = Con1;

}

public void Add(Book b)
{
    SQLCommandText = "USE Library EXEC MySP_BookInsert '" + b.Title + "','" + b.Author + "','" + b.Page + "', '" + b.Date + "'";
    Com1.CommandText = SQLCommandText;
    Com1.CommandType = CommandType.StoredProcedure;
    Com1.ExecuteNonQuery();
}

Please help me. Very thanks

Posted 30-Apr-11 19:31pm

NaderC

Updated 30-Apr-11 19:57pm

Kim Togo

v2

Add a Solution

4 solutions

Solution 4

I would recommend the following solution if your system is a complex one using many stored procedures and using it many times.

1. Use an object class which stores the stored procedure names of your database
This will help you in future of not having any spelling mistakes in calling the stored procedure
This will help you to get a better understanding of your database structure and not coding the same stored
procedure over and over again doing only little changed action.

2. Have a database calling class (object and method separately)
This will help you not write the DB calling code every now and then and will be a central repository to
change anything in respect of database calls.

3. Have a database response object class to receive same type of response from the database for every operation

Example:

Database Object Class named as ODatabase

<br />
private static string _DBServer = "ProcessingServer";<br />
private static string _DatabaseName = "ProcessingDatabase";<br />
private static string _UserName = "loginusername";<br />
private static string _Password = "password";<br />
<br />
public static string ConnectionString<br />
{<br />
<br />
    get { return "Data Source=" + _DBServer + ";Initial Catalog=" + _DatabaseName + ";User Id=" + _UserName + ";Password=" + _Password + ";"<br />
<br />
}<br />

Database Response Object Class names as ODatabaseResponse

<br />
<br />
 public class ODatabaseResponse<br />
    {<br />
        private StringBuilder _DBAError;<br />
        private int _DBCallStatus; // 1 = Success | 0 = Fail<br />
        private DataSet _ResultSet;<br />
<br />
        public ODatabaseResponse()<br />
        {<br />
            _DBAError = new StringBuilder();<br />
        }<br />
<br />
        public string DBAError<br />
        {<br />
            get { return _DBAError.ToString(); }<br />
            set { _DBAError = _DBAError.Append(value); }<br />
        }<br />
<br />
        public int DBCallStatus<br />
        {<br />
            get { return _DBCallStatus; }<br />
            set { _DBCallStatus = value; }<br />
        }<br />
<br />
        public DataSet ResultSet<br />
        {<br />
            get { return _ResultSet; }<br />
            set { _ResultSet = value; }<br />
        }<br />
    }<br />
<br />

Database Call Method Class named as MDatabase

<br />
<br />
public class MDatabase<br />
    {<br />
        private SqlConnection Connection;<br />
        private SqlCommand Command;<br />
        private SqlDataAdapter DataAdapter;<br />
        private string SPName = "";<br />
        private string Parameters = "";<br />
        private string[] ParametersBank;<br />
        private int NumberOfParameters;<br />
        ODatabaseResponse DBAResponse;<br />
<br />
        public MDatabase(string StoredProcedureName)<br />
        {<br />
            SPName = StoredProcedureName;<br />
            ODatabase dbAccess = new ODatabase();<br />
<br />
            Connection = new SqlConnection();<br />
            Connection.ConnectionString = dbAccess.ConnectionString;<br />
<br />
            Command = new SqlCommand();<br />
            Command.Connection = Connection;<br />
            Command.CommandType = System.Data.CommandType.StoredProcedure;<br />
            Command.CommandText = SPName;<br />
            Command.CommandTimeout = 10000;<br />
<br />
            DataAdapter = new SqlDataAdapter();<br />
            DataAdapter.SelectCommand = Command;<br />
<br />
            ParametersBank = new string[50];<br />
<br />
            DBAResponse = new ODatabaseResponse();<br />
        }<br />
<br />
        public void AddParameters(string ParameterName, string ParameterValue, string ParameterType)<br />
        {<br />
            Parameters = Parameters + ParameterName + "∞" + ParameterValue + "∞" + ParameterType + "≡"; //Parameters name, value and type sepearted by ALT + 2540 (∞) <br />
                                                                                                        //Parameters by itself seperated by ALT + 2544 (≡)<br />
        }<br />
<br />
        public ODatabaseResponse ExecuteDBRequest()<br />
        {<br />
<br />
            SplitParameters();<br />
            AssignParameters();<br />
            if (DBAResponse.DBCallStatus == 1)<br />
            {<br />
                DBAResponse.ResultSet = ProcessRequest();<br />
            }<br />
            return DBAResponse;<br />
        }<br />
<br />
        private DataSet ProcessRequest()<br />
        {<br />
            DataSet DBResponse = new DataSet();<br />
<br />
            try<br />
            {<br />
                Connection.Open();<br />
                DataAdapter.Fill(DBResponse);<br />
                Connection.Close();<br />
            }<br />
            catch (Exception ex)<br />
            {<br />
                DBAResponse.DBAError = "Error occured while database operation." + System.Environment.NewLine + System.Environment.NewLine + ex.ToString();<br />
                DBAResponse.DBCallStatus = 0;<br />
            }<br />
<br />
            return DBResponse;<br />
        }<br />
<br />
        private void AssignParameters()<br />
        {<br />
            try<br />
            {<br />
<br />
                for (int i = 0; i < NumberOfParameters; i++)<br />
                {<br />
                    SqlParameter DBParameter = new SqlParameter();<br />
                    string[] ActualParameter = new string[3];<br />
                    ActualParameter = ParametersBank[i].Split('∞');<br />
<br />
                    if (ActualParameter[2].ToString() == "STRING")<br />
                    {<br />
                        DBParameter.ParameterName = ActualParameter[0].ToString();<br />
                        DBParameter.Value = Convert.ToString(ActualParameter[1].ToString());<br />
                        Command.Parameters.Add(DBParameter);<br />
                    }<br />
                    if (ActualParameter[2].ToString() == "INT")<br />
                    {<br />
                        DBParameter.ParameterName = ActualParameter[0].ToString();<br />
                        DBParameter.Value = Convert.ToInt32(ActualParameter[1].ToString());<br />
                        Command.Parameters.Add(DBParameter);<br />
                    }<br />
                    if (ActualParameter[2].ToString() == "DECIMAL")<br />
                    {<br />
                        DBParameter.ParameterName = ActualParameter[0].ToString();<br />
                        DBParameter.Value = Convert.ToDecimal(ActualParameter[1].ToString());<br />
                        Command.Parameters.Add(DBParameter);<br />
                    }<br />
                    if (ActualParameter[2].ToString() == "DATETIME")<br />
                    {<br />
                        DBParameter.ParameterName = ActualParameter[0].ToString();<br />
                        DBParameter.Value = Convert.ToDateTime(ActualParameter[1].ToString());<br />
                        Command.Parameters.Add(DBParameter);<br />
                    }<br />
                    if (ActualParameter[2].ToString() == "BOOLEAN")<br />
                    {<br />
                        DBParameter.ParameterName = ActualParameter[0].ToString();<br />
                        DBParameter.Value = Convert.ToBoolean(ActualParameter[1].ToString());<br />
                        Command.Parameters.Add(DBParameter);<br />
                    }<br />
                }<br />
            }<br />
            catch (Exception ex)<br />
            {<br />
                DBAResponse.DBCallStatus = 0;<br />
                DBAResponse.DBAError = "Error occured while Assigning Parameters to Database Request" + System.Environment.NewLine + System.Environment.NewLine + ex.ToString();<br />
            }<br />
<br />
        }<br />
<br />
        private void SplitParameters()<br />
        {<br />
            try<br />
            {<br />
                NumberOfParameters = 0;<br />
                ParametersBank = Parameters.Split('≡');<br />
<br />
                for (int i = 0; i < 50; i++)<br />
                {<br />
                    if (ParametersBank[i].ToString() != "")<br />
                        NumberOfParameters = NumberOfParameters + 1;<br />
                    else<br />
                        break;<br />
                }<br />
                DBAResponse.DBCallStatus = 1;<br />
            }<br />
            catch (Exception ex)<br />
            {<br />
                DBAResponse.DBCallStatus = 0;<br />
                DBAResponse.DBAError = "Error occured while Spliting Database Request Parameters" + System.Environment.NewLine + System.Environment.NewLine + ex.ToString();<br />
            }<br />
<br />
        }<br />
    }<br />
<br />

Stored procedure object class named as OStoredProcedures

<br />
private string _UserFindProcedure = "UserFindProcess";<br />
// All your stored procedures goes here<br />
<br />
<br />
public string UserFindProcedure<br />
{<br />
    get { return _UserFindProcedure; }<br />
}<br />

Actual application method calling the stored procedure
Example: User Object Class containing username and password and User Method Class calling the stored procedure

<br />
    public ODatabaseResponse FindUser(ObjectUser User)<br />
    {<br />
        ODatabaseResponse dbresponse = new ODatabaseResponse();<br />
        MDatabase dbaccess = new MDatabase(OStoredProcedures.UserFindProcedure);<br />
        dbaccess.AddParameters("UserName", User.UserName, "STRING");<br />
        dbaccess.AddParameters("Password", User.Password, "STRING");<br />
        dbresponse = dbaccess.ExecuteRequest();<br />
        return dbresponse;<br />
       <br />
    }<br />

I can understand that the code would be bit heavy to understand, but, once you implement this, it would be more and more simpler to use it over the whole application.

--Nayan

Posted 3-May-11 15:42pm

Nayan Ambaliya

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)

Kim Togo · Accepted Answer · 2011-04-30T20:05:00

I think you have to drop the "USE Library" - The "USE" command changes the default database you are working with.

If you look at the connection string, you have already defined the default database to "Library" via "INITIAL CATALOG=Library".

Change

SQLCommandText = "USE Library EXEC MySP_BookInsert '" + b.Title + "','" + b.Author + "','" + b.Page + "', '" + b.Date + "'";

To

SQLCommandText = "EXEC MySP_BookInsert '" + b.Title + "','" + b.Author + "','" + b.Page + "', '" + b.Date + "'";

Actually "USE Library" will never be executed. There is missing a ";" marker.

Sandeep Mewara · Accepted Answer · 2011-04-30T20:12:00

The way you are calling a stored procedure supplying parameters in ADO.NET is not the right way.
Go here:
MSDN: HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual C# .NET[^]
MSDN: HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET[^]
ADO.NET : Using Stored Procedures[^]

CodeHawkz · Accepted Answer · 2011-05-02T23:10:00

Hi there,

Good catch there by Kim Togo.

In addition to that, the way you are calling a SP from SqlCommand is wrong. Please, see the code below.

C#

public Book_SP_Controller()
        {
            Con1 = new SqlConnection("DATA SOURCE=(local);INITIAL CATALOG=Library;INTEGRATED SECURITY=true;");
            Con1.Open();
 
            Com1 = new SqlCommand();
            Com1.Connection = Con1;
 
        }
 
        public void Add(Book b)
        {
            Com1.CommandText = "MySP_BookInsert";
            Com1.CommandType = CommandType.StoredProcedure;

            SqlParameter bookTitleParameter = Com1.CreateParameter();
            // Assuming the parameter name of the book's title is "@BookTitle"
            bookTitleParameter.ParameterName = "@BookTitle";
            bookTitleParameter.Value = b.Title;
            Com1.Parameters.Add(bookTitleParameter);

            // Add your other parameters like this to the command (author, page, num)

            Com1.ExecuteNonQuery();
        }

Note the code in bold

Hope this helps :) Regards

Need help for run parameterized stored procedure in sql

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0