Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Call Store Procedure from C#

Posted on 2011-02-28
18
Medium Priority
?
666 Views
Last Modified: 2012-05-11
I'm trying to find a standard and simple way to call store procedure from C#, including
- No records return    (like insert and update)
- with records return (like select statement)
- error message return or error with severity level return (like raiseerror)

Thanks.
0
Comment
Question by:minglelinch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +2
18 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34999872
well, you start by defining a connection and creating a command object that specifys that you are calling a stored procedure:
SqlConnection cn = new SqlConnection("yourconnectionstring");
SqlCommand cmd = new SqlCommand("SomeSPName", cn);
cmd.CommandType = CommandType.StoredProcedure;

Open in new window

To catch errors you wrap the actual code that opens the connection in a try...catch block. You then use one of two methods for commiting or reading data:
try
{
     cn.Open();

     // if we are only writing or deleting data we use:
     cmd.ExecuteNonQuery();

     // to read, in its simplest form, we use a datareader;
     SqlDataReader reader = cmd.ExecuteReader();

     // call Read() on the datareader until it returns false, indicating it has run out of records
     while (reader.Read())
     {
           // do something with the data
     }
}
catch (SqlException ex)
{
      // something went wrong. "ex" will contain the details
}
finally
{
     // using a "finally" block means we close the connection cleanly even if an exception is raised

     if (cn.State != ConnectionState.Closed)
     {
           cn.Close();
     }
}

Open in new window

0
 
LVL 28

Expert Comment

by:strickdd
ID: 34999879
0
 
LVL 1

Author Comment

by:minglelinch
ID: 35000375
Thanks for the inputs. I still have questions. I imbeded my questions in the following code -

try
{
     cn.Open();
     cmd.ExecuteNonQuery();
     SqlDataReader reader = cmd.ExecuteReader();

     // For INSERT or UPDATE, will it still need SqlDataReader? and just reader.Read() is empty, right?
     // (I think my question is that if we use the same call  structure like this for both type store
     // procedure calls INSERT and SELECT)
     while (reader.Read())
     {
           // Here is only for data returned from store procedures, such as SELECT or OUTPUT parameter
           // data, right? If no returned data like INSERT, just leave this block and do nothing, right?
     }
}
catch (SqlException ex)
{
      // Will everything in RaiseError be catched here ?
      // How to get error messages and seiverity level ?
}
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:starlite551
ID: 35000390
Try this code :
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;

partial class _Default : System.Web.UI.Page
{


	protected void Button1_Click(object sender, System.EventArgs e)
	{
            try
            {   
		using (SqlConnection cn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=DatabaseDemo;Integrated Security=true;")) {
			cn.Open();
			SqlCommand cm = new SqlCommand("sp_getData", cn);
			cm.CommandType = CommandType.StoredProcedure;
			//If the procedure is just for select query..'
			SqlDataAdapter da = new SqlDataAdapter(cm);
			DataSet ds = new DataSet();
			da.Fill(ds);
			DataTable dt = new DataTable();
			dt = ds.Tables[0];
			GridView1.DataSource = dt;
			GridView1.DataBind();
		}
          }
          catch(Exception ex)
          {
              throw ex;
          }  
     }
}

Open in new window

0
 
LVL 12

Expert Comment

by:starlite551
ID: 35000417
0
 
LVL 8

Expert Comment

by:WesWilson
ID: 35000511
cmd.ExecuteNonQuery() is for Updates and Inserts. All you get back is the number of rows affected.
cmd.ExecuteScalar() is when you are going after a single value (or just the first column of the first row returned.)
cmd.ExecuteReader() is for returning one or more rows of data.
0
 
LVL 8

Accepted Solution

by:
WesWilson earned 1000 total points
ID: 35000595
Answers in bold below.

try
{
     cn.Open();
     cmd.ExecuteNonQuery(); //use for Inserts or Updates
     SqlDataReader reader = cmd.ExecuteReader(); //Use for Select statements

     // For INSERT or UPDATE, will it still need SqlDataReader? No, only use the SqlDataReader if you have rows from a SELECT to read. and just reader.Read() is empty, right?
     // (I think my question is that if we use the same call  structure like this for both type store
     // procedure calls INSERT and SELECT)
     while (reader.Read())
     {
           // Here is only for data returned from store procedures, such as SELECT or OUTPUT parameter
           // data, right? If no returned data like INSERT, just leave this block and do nothing, right?
           //Don't use this block unless you are calling ExecuteReader for a SELECT statement.
     }
}
catch (SqlException ex)
{
      // Will everything in RaiseError be catched here ? This block would catch only errors of type SqlException
      // How to get error messages and seiverity level ? ex.Message gives you just the message without the stack trace. This is a great place to write an error to a log file if appropriate.
}
catch (Exception otherException)
{
     //this block catches all other errors
}
finally
{
     cn.Close(); //remember to close your connection no matter what happens. Or you can create your connection in a using statement so that it is automatically closed and disposed of when you leave the using block.
}
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 1000 total points
ID: 35000611
INSERT, DELETE and UPDATE generally only needs ExecuteNonQuery().
ExecuteScalar() is useful if you are only expecting a single value back from the database.
ExecuteReader() is used when you are expecting a full row, or multiple rows to be returned by your stored procedure.

The exception handler will catch any errors relating to the connection, or execution of the store procedure. If you are using RAISERROR in your stored procedure then it needs to be severity 16 or above in order to trigger an exception in the C# code.
0
 
LVL 1

Author Comment

by:minglelinch
ID: 35037676
Thanks for all the great comments. Now I'm clear at C# how to receive data and error message. Now I have a few questions at store procedure side. I have the following store procedure defined.
1. To catch SqlException at C# side, I don't have to imbed BEGIN TRY...END TRY...BEGIN CATCH...END CATCH in store procudure, right? SqlException will automatically throw to C#, correct? Or without imbedding BEGIN TRY...END TRY...BEGIN CATCH...END CATCH in store procudure, no exception can be caught at C#, true or false?
2. If I use RAISERROR in stored procedure with severity lower than 16, C# code cannot catch the error message? and cannot catch the exception? If both yes, then I should not use RAISERROR at all for severity lower than 16. Then what should I use?

CREATE PROCEDURE [dbo].[CheckLoginUser]
        @loginID        varchar(50),
        @passwrd    varchar(50)
AS
       BEGIN TRY
      SELECT userId, loginId, password, cadNo, email, role
                        FROM dbo.LoginUser
                     WHERE loginId = @loginID and password = @passwrd
       END TRY
       BEGIN CATCH
      DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
      Set @ErrMsg = ERROR_MESSAGE()
                     Set @ErrSeverity = ERROR_SEVERITY()
      RAISERROR(@ErrMsg, @ErrSeverity, 1)
       END CATCH
0
 
LVL 8

Expert Comment

by:WesWilson
ID: 35037946
What errors are you concerned about here? Unless you need to roll back previous operations in the stored procedure, I wouldn't use any try/catch blocks in the stored procedure.

Your C# code will catch any SqlException that is thrown, and you can handle it there. For example, if a column name has been changed, or you encounter a key violation, those types of things cause SqlExceptions. In my opinion, the best place to handle most errors is in your C# code, at the application level, not the database level.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35038037
I agree. You should only be catching exceptions in your stored procedure if you need to do something with it at that point. If you just want to handle it at the C# end then you don't need exception handlers in your stored procedure.
0
 
LVL 1

Author Comment

by:minglelinch
ID: 35039591
Great comments. Thanks a lot. Both of you clear away all my questions.
Have a wonderful weekend !
0
 
LVL 1

Author Comment

by:minglelinch
ID: 35061190
What would be the normal way to display ex.Message in C#? Any way to put the error message in an alert box?

I searched on the internet, all used Console.WriteLine("Exception" ). That's not waht I want.
Thanks.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35061454
If it's ASP.Net then a label that you can write the message to for display purposes would be one way to go. If you want a javascript alert instead then that's an option too.
0
 
LVL 1

Author Comment

by:minglelinch
ID: 35083945
Thanks for the comment. I used the following -
catch (SqlException ex)    {
            Response.Write("<script>alert('" + Server.HtmlEncode(ex.Message).Replace("'", "\\'") + "')</script>");
}

a nice message box pops up, but when I click OK on the message box, the aspx page changed a little: buttons on the right side of the page are shifted to the right.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35084345
Probably because you are writing out a "script" element to the middle of your page which may have a knock-on effect to the rest of the markup. Try using thef following instead:
catch (SqlException ex)    
{
            Page.ClientScript.RegisterStartupScript(this.GetType(), "PopupScript", "alert('" + Server.HtmlEncode(ex.Message).Replace("'", "\\'") + "')", true);
}

Open in new window

0
 
LVL 8

Expert Comment

by:WesWilson
ID: 35109377
Minglelinch, please open a new question if you have more questions on displaying text in the UI. That's well outside the scope of the original question which has been answered.

Also, if you ask a new question, your title will be relevant to it, so you will probably get more experts participating.

Thanks!
0
 
LVL 1

Author Closing Comment

by:minglelinch
ID: 35175965
Great Answers !
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

609 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