Problem with using my stored procedure

综合技术 2018-06-23

I have the following method that is supposed to be a generic "Save to SQL" method for my application.

protected void EjecutarGuardar(string ProcedimientoAlmacenado, object[] Parametros)
        {
            SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            SqlCommand Command = Connection.CreateCommand();
            Command.CommandType = CommandType.StoredProcedure;
            foreach (object X in Parametros)
            {
                Command.Parameters.Add(X);
            }            

            Connection.Open();
            Command.ExecuteNonQuery();
            Connection.Close();

            Connection.Dispose();
        }

I have to pass the NAME of the StoredProcedure and an Array filled with the parameters. I'm kind of lost at this point. Where should I use the NAME of the stored procedure "ProcedimientoAlmacenado"?

I'm thinking maybe Command.Command?????something somethign? But I'm lost there. Any help?

Edit: For simplicities sake let's say a I have a stored procedure called "ABC" in my database. How could I associate it to my SqlCommand "Command" in my code?


Command.CommandText

= ProcedimientoAlmacenado

The parameters must have names too. Does the Parametros array contains SqlParameter objects or generic C# objects?

If the parameters are generic C# objects, is better to pass in a dictionary of names and values:

protected void EjecutarGuardar(string ProcedimientoAlmacenado,
    Dictionary Parametros)
{
    using (SqlConnection Connection = new SqlConnection(...))
    {
        Connection.Open();
        SqlCommand Command = Connection.CreateCommand()
        Command.CommandText = ProcedimientoAlmacenado;
        Command.Connection = Connection;
        Command.CommandType = CommandType.StoredProcedure;
        foreach (string name in Parametros.Keys)
        {
          Command.Parameters.AddWithValue(name, Parametros[name] ?? DBNull.Value);
        }
        Command.ExecuteNonQuery();
    }
}

This is a quick and dirty approach. Note that this approach usually has problems because AddWithValue
will pass in a parameter of type NVARCHAR for a string, not VARCHAR, and with ad-hoc SQL this can cause index SARG-ability problems on VARCHAR columns (because the conversion will be always from VARCHAR to NVARCHAR and not vice-versa). However with stored procedures is not such a problem because procedures have types parameters and thus a force coercion happens to VARCHAR if the procedure was created with parameter type VARCHAR.

You will also have problems around passing NULL parameters, so you'll need to do something like, the parameter has to be DBNull.Value
not null
:

Command.Parameters.AddWithValue(name, Parametros[name] ?? DBNull.Value);

On high performance systems this approach also pollutes the execution cache unnecessarily because the AddWithValue will pass parameters of type NVARCHAR()
, not NVARCHAR()
. So Paramaters.AddWithValue("@name", "John")
and Parameters.AddwithValue("@name", "Doe")
will create two distinct plans in the cache because one is invoked with a parameter of type NVARCHAR(4), the other with a parameter NVARCHAR(3) and they are seen by the SQL plan cache as different types
. This is not a problem on simple projects, but on more complex and high performance ones it is recommended to set the parameter types explicitly.

My recommendation would be to avoid this kind of generic one-size-fits-all procedures and instead write a data access layer with explicit C# wrapper for each database procedure, with properly types parameters. A strongly typed dataset can actually do this, the other alternative (my favorite and what I always use) is to generate the entire data acces slayer from an XML file using an XSLT stylesheet that creates the C# wrappers. The source XML is, of course, extracted from the database meta data itself.

Hello, buddy!

责编内容by:Hello, buddy! (源链)。感谢您的支持!

您可能感兴趣的

C++11之decltype 使用场景 在C++中经常要用到很长的变量名,如果已经有变量和你将使用的变...
《高性能SQL调优精要与案例解析》一书谈SQL调优(SQL TUNING或SQL优化)学习... 《高性能SQL调优精要与案例解析》一书上市发售以来,很多热心读者就该书内容及一些具体问题提出了疑问,因读者众多外加本人日常工作的繁忙 ,在这里就SQL调优学习进...
SQL SERVER – DMV to Get Host Information – sys.dm_... There was a time when SQL Server used to run on only Microsoft Windows. However...
Consolidating SQL Server Instance by Clustering an... NOTES: Windows Failover Clustering comprising two nodes. Two SQ...
Sql Developer Custom Connection String The SQL SERVER 2008 connection string using Java throws ...