Solved

Call Store Procedure from C#

Posted on 2011-02-28
18
653 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
  • 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
 
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 250 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 250 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

20 Experts available now in Live!

Get 1:1 Help Now