logo
down
shadow

Stored Procedure works in DB but not in C# code


Stored Procedure works in DB but not in C# code

Content Index :

Stored Procedure works in DB but not in C# code
Tag : chash , By : KingGuppy
Date : November 23 2020, 01:01 AM

This might help you This sounds like it could be an issue with "parameter sniffing" (http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx), which means that the execution plan that is cached for your procedure could be optimized for different parameter values based on prior execution of the procedure. This cached plan could be efficient for parameter value x, but not for parameter value y. I have run into this same issue before, where when I run a query in SSMS it runs immediately, but if I run from my app, it "hangs". This is because the query text when i run in SSMS is slightly different than the query text that comes from the app, so it uses a different cached execution plan.
One workaround to fix this issue is to create a locally scoped variables within your proc, which act as a copy of your procedure parameters.
ALTER PROC dbo.usp_YourProcName
    @pi_PId                 INT
,   @pi_returnOnly1stRow    BIT
,   @pi_returnExtAttr       BIT
AS
BEGIN 
    --these local variables are used to address "parameter sniffing" issues which may cause an ineffient plan cache.
    --Use these local variables below instead of the direct parameter values.
    DECLARE @local_pi_PId             INT = @pi_PId
    ,   @local_pi_returnOnly1stRow    BIT = @pi_returnOnly1stRow
    ,   @local_pi_returnExtAttr       BIT = @pi_returnExtAttr
    ;

    IF(@local_pi_returnOnly1stRow=1)
        BEGIN
            SELECT TOP 1 NULL section_name,header_mvoc.text Attr_Name, body_mvoc.Text Attr_Value,cds_mspecee.DisplayOrder
            FROM   cds_mspecee 
            JOIN   cds_mvocee header_mvoc ON (cds_mspecee.hdrid = header_mvoc.id)
            JOIN   cds_mvocee body_mvoc  ON (cds_mspecee.bodyid = body_mvoc.id)
            JOIN   cds_prod ON (cds_Prod.prodid = cds_mspecee.prodid)
            JOIN   ProductVariant revpro  On (revpro.ManufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            UNION ALL
            SELECT section_evoc.Text section_name, header_evoc.text Attr_Name, body_evoc.Text Attr_Value, cds_Especee.DisplayOrder
            FROM   cds_Especee  
            JOIN   cds_Evocee section_evoc  ON (cds_Especee.SectID = section_evoc.ID)
            JOIN   cds_Evocee header_evoc  ON (cds_Especee.hdrid = header_evoc.id)
            JOIN   cds_Evocee body_evoc  ON (cds_Especee.bodyid = body_evoc.id)
            JOIN   cds_prod  ON (cds_Prod.prodid = cds_especee.prodid)
            JOIN   ProductVariant revpro  On (revpro.ManufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            AND    @local_pi_returnExtAttr = 1
            ORDER BY section_name,displayorder ASC
        END
    ELSE
        BEGIN
            SELECT NULL section_name ,header_mvoc.text Attr_Name, body_mvoc.Text Attr_Value,cds_mspecee.DisplayOrder
            FROM   cds_mspecee  
            JOIN   cds_mvocee header_mvoc  ON (cds_mspecee.hdrid = header_mvoc.id)
            JOIN   cds_mvocee body_mvoc  ON (cds_mspecee.bodyid = body_mvoc.id)
            JOIN   cds_prod  ON (cds_Prod.prodid = cds_mspecee.prodid)
            JOIN   productVariant revpro  On (revpro.manufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            UNION ALL
            SELECT section_evoc.Text section_name,header_evoc.text Attr_Name, body_evoc.Text Attr_Value,cds_Especee.DisplayOrder
            FROM   cds_Especee  
            JOIN   cds_Evocee section_evoc  ON (cds_Especee.sectid = section_evoc.id)
            JOIN   cds_Evocee header_evoc  ON (cds_Especee.hdrid = header_evoc.id)
            JOIN   cds_Evocee body_evoc  ON (cds_Especee.bodyid = body_evoc.id)
            JOIN   cds_prod  ON (cds_Prod.prodid = cds_especee.prodid)
            JOIN   productVariant revpro  On (revpro.manufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            AND    @local_pi_returnExtAttr = 1
            ORDER BY section_name,displayorder ASC
        END
END

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

T-SQL stored procedure returns null in code, but works in console


Tag : sql-server , By : Andrew Bailey
Date : March 29 2020, 07:55 AM
To fix this issue I am sorry to have to say, but this might be due to using the Odbc
I tried this using out SqlClient database object and it returned the values as expected.
SqlCommand command = new SqlCommand("ZZZ_get_unique_identifier");
command.CommandType = CommandType.StoredProcedure;

SqlParameter return_param = new SqlParameter("@RETURN_VALUE",SqlDbType.BigInt);
return_param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(return_param);

SqlConnection con = new SqlConnection(dbM.DefaultConnectionString);
con.Open();

command.Connection = con;
command.ExecuteNonQuery();

int i = Convert.ToInt32(command.Parameters["@RETURN_VALUE"].Value.ToString());
con.Close();

Stored procedure not returning value when executed from code, works fine from management studio


Tag : chash , By : lili
Date : March 29 2020, 07:55 AM
this will help Your sample works if you populate a DataSet instead of a DataTable.
Here is a copy of your source with the minimum changes required. Note that when you're using a DataSet you should add code to check whether any tables were returned, and whether there are rows in the first table available, etc.
SqlParameter[] Parameters = new SqlParameter[1];
Parameters[0] = new SqlParameter();
Parameters[0].ParameterName = "@TestId";
Parameters[0].Value = TestId;
Parameters[0].SqlDbType = SqlDbType.Int;
Parameters[0].Size = 50;

DataSet data = ExecuteDataSet("ExportTestAsXML", Parameters);

// Read First table (Tables[0]), First Row (Rows[0]), First Column of that Row (Rows[0][0])
System.Diagnostics.Debug.Write(data.Tables[0].Rows[0][0]);
private DataSet ExecuteDataSet(string storedProcName, SqlParameter[] parameters)
{
    SqlCommand command = new SqlCommand();
    command.CommandText = storedProcName;
    command.Parameters.AddRange(parameters);
    command.CommandType = CommandType.StoredProcedure;
    command.Connection = (SqlConnection)dcMUPView.Connection;

    command.Connection.Open();
    command.Prepare();

    SqlDataAdapter adapter = new SqlDataAdapter(command);
    DataSet ds = new DataSet ();
    adapter.Fill(ds);

    command.Connection.Close();
    return ds;
}

Stored Procedure works in Mysql workbench but not in C# code


Tag : chash , By : CrimsonGore
Date : March 29 2020, 07:55 AM
I hope this helps you . I have a stored procedure which I want to delete and then want to re-create it. It is working from Mysql workbench but not working from c# code. , I found a solution and it is working perfectly.
C# Code:
string myConnectionString = FetchDbConnectionString(environment, item);
MySqlConnection mySqlConnection = new MySqlConnection(myConnectionString);

const string sqlQuery ="DELIMITER $$ DELIMITER $$ DROP PROCEDURE IF EXISTS AccountGetAccountOpeningClosingStock; $$ CREATE PROCEDURE AccountGetAccountOpeningClosingStock(IN _endDate datetime) BEGIN Select * From tutorials_tbl Where CreateDate <= _endDate order by CreateDate asc; END$$ $$  DELIMITER; DELIMITER;";
MySqlScript script = new MySqlScript();
script.Connection = mySqlConnection;
script.Query = sqlQuery;
script.Execute();
mySqlConnection.Close();

Getting out parameter of stored procedure with C# only works when I put a breakpoint in my code


Tag : chash , By : kgw
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , Output parameters are not available until the return result has been read completely, or the underlying DbDataReader is closed.
See the Parameters section of ObjectContext.ExecuteFunction, and the second sentence of this section on DataReaders
public static Guid AddProfesseur(string prenom, string nom)
{
        using (ExamenProjetIntegrationEntities db = new ExamenProjetIntegrationEntities())
        {
            ObjectParameter objParam = new ObjectParameter("identity", typeof(Guid));
            var resultToReturn = db.AjouterProfesseur(prenom, nom, objParam).Count();
            return  Guid.Parse(objParam.Value.ToString());
        }
}

Stored procedure keeps running - code works fine executed outside of the stored procedure


Tag : sql , By : Joe Sweeney
Date : March 29 2020, 07:55 AM
this will help Global temporary tables are visible from multiple sessions, but they have locking just like regular tables. So sessions can't read each other's uncommitted changes. This includes uncommitted DDL, like CREATE TABLE or SELECT ... INTO.
What you have is a un-detectable deadlock between your sessions, equivalent to:
begin transaction
create table ##t(id int)
exec xp_cmdshell 'bcp "select * from ##t" queryout "c:\temp\foo.csv" -S "localhost" -T'
Related Posts Related QUESTIONS :
  • CSV string handling
  • What is the best way to do unit testing for ASP.NET 2.0 web pages?
  • High availability
  • What to use for Messaging with C#
  • Accessing a Dictionary.Keys Key through a numeric index
  • ConfigurationManager.AppSettings Performance Concerns
  • What Are Some Good .NET Profilers?
  • Is this a good way to determine OS Architecture?
  • How to create a tree-view preferences dialog type of interface in C#?
  • Searching directories for tons of files?
  • Can I have a method returning IEnumerator<T> and use it in a foreach loop?
  • Why can't I have abstract static methods in C#?
  • Displaying ad content from Respose.WriteFile()/ Response.ContentType
  • Convert integers to written numbers
  • Absolute path back to web-relative path
  • How can we generate getters and setters in Visual Studio?
  • Bringing Window to the Front in C# using Win32 API
  • Possible to "spin off" several GUI threads? (Not halting the system at Application.Run)
  • Reading a C/C++ data structure in C# from a byte array
  • How should I translate from screen space coordinates to image space coordinates in a WinForms PictureBox?
  • Setting Objects to Null/Nothing after use in .NET
  • Converting ARBG to RGB with alpha blending
  • Is it better to create Model classes or stick with generic database utility class?
  • Passing enum type to Converter with integer value
  • Pool of objects with objects that are already on the scene in advance
  • StatusBar text fade-out when binding using Caliburn.Micro
  • Queryfilter on ApplicationUser in OnModelCreating in ApplicationDbContext creates StackOverflowException
  • How to get record form a different table based on a value from first table with linq expression?
  • Show data in Grid from returned model
  • Using Attributes to Override Data Model Conventions
  • Basic OOP console calculator, result Error
  • Compositon and Repository pattern
  • Multiple using statements with if condition
  • How do i increase a number by 1 in every line that contain the number 1
  • Add binding to elements that are created in codebehind
  • How to add a column in an existing AspNetUsers table
  • Order a list of elements with another list of doubles
  • How to setup a NuGet package to copy content files to output build directory?
  • In SignalR Core using ChannelWriter: Do I need to call TryComplete twice if there's an exception?
  • C# GetProcessesByName: issue with colon
  • c# wpf | create complex object with user-defined name to Serialize into JSON
  • How can I get a instance of a generic list with reflection?
  • WPF XAML - Design time and visibility of textbox
  • EF Core and MySql query is too slow
  • Getting Registered App Display Name from an App Id
  • How to get all variables from a string
  • Delete entity with all childs connected
  • Azure Build agent cant´t find class library referance
  • Initialize Nested Dictionaries in c#
  • .Net Core Binding
  • Generic event test method, preventing code duplication
  • How do I keep the ellipses in the center when the screen is resized
  • How to require a property using JsonSchema.NET?
  • C# XDocument Element/Elements returns null
  • Autofac keyed service with IEnumerable relationship type
  • Installing EntityFramework via NuGet manager
  • Always Check if there is Internet Connection Xamarin forms
  • WCF OneWay service slows down when aspNetCompatibilityEnabled is set to false
  • Can we use JsonRequestBehavior.AllowGet with [HttpPost] attribute?
  • How to customize the Setup wizard with custom forms in Visual Studio setup project
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com