使用存储过程最优雅的方法是什么？

I'm re-writing the inline SQL in my repository class to use stored procedures instead (security requirement). After using Fluent NHibernate and Linq2Sql in the past I'm finding it to be extremely unwieldy and inelegant.

我正在我的存储库类中重写内联SQL以使用存储过程(安全性要求)。在过去使用Fluent NHibernate和Linq2Sql之后,我发现它非常笨重且不优雅。

EDIT: To clarify, I'm not looking for an ORM solution that works with stored procs. I just want some advice on a nice way to write the code below.

编辑:澄清一下,我不是在寻找适用于存储过程的ORM解决方案。我只是想要一些很好的方法来编写下面的代码。

Are there any strategies for making this sort of code as elegant as possible?

是否有任何策略可以使这种代码尽可能优雅?

        string commandText = "dbo.Save";

        using (SqlConnection sql = new SqlConnection(_connString.ConnectionString))
        using (SqlCommand cmd = sql.CreateCommand())
        {
            cmd.CommandText = commandText;
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter idParam = new SqlParameter("identity", item.Identity);
            idParam.Direction = ParameterDirection.Input;

            SqlParameter nameParam = new SqlParameter("name", item.Name);
            nameParam.Direction = ParameterDirection.Input;

            SqlParameter descParam = new SqlParameter("desc", item.Description);
            descParam.Direction = ParameterDirection.Input;

            SqlParameter titleParam = new SqlParameter("title", item.)
            descParam.Direction = ParameterDirection.Input;

            //SNIP More parameters

            cmd.Parameters.Add(idParam);
            cmd.Parameters.Add(descParam);
            cmd.Parameters.Add(titleParam);
            //SNIP etc

            sql.Open();

            cmd.ExecuteNonQuery();

            //Get out parameters
        }

        return item;

11 个解决方案

#1

Within our internal applications we generally use the SqlHelper class which can be found at the following link (download and description): http://www.microsoft.com/downloads/details.aspx?familyid=f63d1f0a-9877-4a7b-88ec-0426b48df275&displaylang=en

在我们的内部应用程序中,我们通常使用SqlHelper类,可以在以下链接(下载和描述)中找到:http://www.microsoft.com/downloads/details.aspx?familyid = f63d1f0a-9877-4a7b-88ec- 0426b48df275&displaylang = EN

Essentially the SqlHelper class takes away some of the need to declare connection objects, commands etc and allows you to call methods to return objects such as DataSet

本质上,SqlHelper类消除了声明连接对象,命令等的一些需要,并允许您调用方法来返回DataSet等对象

You might then use SqlHelper as such:

然后您可以使用SqlHelper:

public static int UpdateItem(int parameter1, int parameter2, string parameter3)
    {
        SqlParameter[] arParam = new SqlParameter[3];
        arParam[0] = new SqlParameter("@Parameter1", lotId);
        arParam[1] = new SqlParameter("@Parameter2", saleId);
        arParam[2] = new SqlParameter("@Parameter3", lotNumber);


        return int.Parse(SqlHelper.ExecuteScalar(connString, CommandType.StoredProcedure, "spName", arParam).ToString(), CultureInfo.InvariantCulture);
    }

Hope this helps :)

希望这可以帮助 :)

#2

Grab a copy of Enterprise Library. It is a nice wrapper around ADO. For example:

获取企业库的副本。它是围绕ADO的一个很好的包装器。例如:

using System.Data.Common;
using System.Globalization;
using Microsoft.Practices.EnterpriseLibrary.Data;

Database db = DatabaseFactory.CreateDatabase(DatabaseType.MyDatabase.ToString());

using (DbCommand dbCommand = db.GetStoredProcCommand("dbo.MyStoredProc")) {
    db.AddInParameter(dbCommand, "identity", DbType.Int32, item.Identity);
    db.AddInParameter(dbCommand, "name", DbType.String, item.Name);
    db.AddInParameter(dbCommand, "desc", DbType.String, item.Description);
    db.AddInParameter(dbCommand, "title", DbType.String, item.Title);

    db.ExecuteNonQuery(dbCommand);
} // using dbCommand

#3

I usually use some variation of the following example, depending on the environment of course:

我通常使用以下示例的一些变体,具体取决于当然的环境:

My basic helper methods that I call throughout my code

我在整个代码中调用的基本辅助方法

public static SqlCommand CreateStoredProcCmd(string name, SqlConnection con)
{
    var cmd = new SqlCommand(name, con);
    cmd.CommandType = CommandType.StoredProcedure;
    return cmd;
}

public static void AddParams(this SqlCommand cmdObject, Params SqlParameter[] parameters)
{
  foreach(SqlParameter param in parameters)
  {
    cmdObject.Parameters.add(param);
  }
}

/* Any overloaded methods to create params receiving my param definitions 
in any manner that the usual new SqlParameter() constructor doesn't handle */
public static SqlParameter CreateSqlParam(string ParamName,
                                          SqlDbType ParamType,
                                          object value)
{
    return CreateSqlParam(ParamName, ParamType, ParameterDirection.Input, value);
}

public static SqlParameter CreateSqlParam(string ParamName, 
                                          SqlDbType ParamType, 
                                          ParameterDirection ParamDir)
{
    return CreateSqlParam(ParamName, ParamType, ParamDir, null;
}                          

public static SqlParameter CreateSqlParam(string ParamName, 
                                          SqlDbType ParamType, 
                                          ParameterDirection ParamDir,
                                          object value)
{
    var parm = new SqlParameter(ParamName, ParamType);
    parm.Direction = ParamDir;
    parm.Value = value;
    return parm;
}

Now here's how I set up my stored procs and add all my parameters elegantly

现在,这是我如何设置我的存储过程并优雅地添加所有参数

public static string DoStuff()
{
    using (var oCon = new SqlConnection("MyConnectionString"))
    {
        oCon.Open();
        var oCmd = CreateStoredProcCmd("sp_Name", oCon).AddParams(
            CreateSqlParam("Param1", SqlDBType.Int, 3),
            CreateSqlParam("Param2", SqlDBType.VarChar, "Hello World"),
            CreateSqlParam("Param3", SqlDBType.VarChar, ParameterDirection.Output)
        );
        oCmd.Prepare();
        oCmd.ExecuteNonQuery();
        object outVal = oCmd.Parameters["Param3"];
        return null != outVal ? outVal.ToString() : String.Empty;
    }
}

#4

You could use SubSonic as an ORM layer between your class and the stored procedures. Here's a basic example. Phil Haack has a good article on it as well.

您可以使用SubSonic作为类和存储过程之间的ORM层。这是一个基本的例子。 Phil Haack也有一篇很好的文章。

There's some good info in this other question.

在另一个问题上有一些很好的信息。

EDIT: Since you've updated your question to indicate you don't want to use an ORM, SubSonic is not for you. However, I'll leave the answer here for other folks who use stored procs. :) You should also take a look at if there's even a possibility you can use it.

编辑:由于您已更新您的问题以表明您不想使用ORM,SubSonic不适合您。但是,我会在这里为其他使用存储过程的人留下答案。 :)你还应该看看是否有可能你可以使用它。

#5

I would recommend using the Microsoft Application Blocks SqlHelper object.

我建议使用Microsoft应用程序块SqlHelper对象。

For a statement like you have listed above, I can do the following.

对于您上面列出的声明,我可以执行以下操作。

SqlHelper.ExecuteNonQuery(_connectionString, "MyProcName", 1, "NameValue", "Description", "Title");

Basically SQL Helper takes a few parameters.

基本上SQL Helper需要一些参数。

1. The Connection String to connect to the db

连接到数据库的连接字符串

2. The name of the stored procedure

存储过程的名称

3. An array of parameter values, in the order in which they appear in the Stored Procedure.

参数值数组,按它们在存储过程中出现的顺序排列。

There is a VERY slight performance drawback with this method over explicitly creating each parameter, but the time savings usually outbalances it since it is so small.

这种方法在明确创建每个参数方面存在非常轻微的性能缺陷,但节省时间通常会使其失去平衡,因为它非常小。

#6

You could halve the linecount by deriving your own InputSqlParameter from SqlParameter and setting the direction to Input in the constructor.

您可以通过从SqlParameter派生自己的InputSqlParameter并在构造函数中将方向设置为Input来将行数减半。

That would let you write

那会让你写

    cmd.Parameters.Add(new InputSqlParameter("title", item.title));
    cmd.Parameters.Add(new InputSqlParameter("property", item.property));

This shows a pattern, and lets you setup a list of parameter names and item fields and do the parameter adding in a for loop.

这显示了一个模式,并允许您设置参数名称和项目字段的列表,并在for循环中执行参数添加。

#7

To make the code a little less verbose i've always added parameters using

为了使代码更简洁,我总是使用添加参数

cmd.Parameters.AddWithValue("name", item.Name);
cmd.Parameters.AddWithValue("title", item.Title);
// and so on

#8

Input is the default direction and you can shorten the parameter adding and probably want to declare the SqlDBTypes as well...

输入是默认方向,你可以缩短参数添加,也可能想要声明SqlDBTypes ...

cmd.Parameters.Add("identity", SqlDBType.???).Value = item.Identity;
cmd.Parameters.Add("desc", SqlDbType.???, ?size?).Value = item.Description;
cmd.Parameters.Add("title", SqlDBType.???, ?size?).Value = item.Title;

//Output params generally don't need a value so...
cmd.Parameters.Add("someOut", SqlDBType.???).Direction = ParameterDirection.Output;

#9

Keep each parameter for a given stored procedure in a "data class." Use annotations to specify things like "in" or "out" sproc parameters.

将给定存储过程的每个参数保存在“数据类”中。使用注释指定“in”或“out”sproc参数之类的内容。

Then you can load up the class from client code, then use reflection to build all the parameters for the sproc, execute the sproc, and load the output parameters back into the data class.

然后,您可以从客户端代码加载类,然后使用反射为sproc构建所有参数,执行sproc,并将输出参数加载回数据类。

Slightly more clean: Have one class for inputs and another for outputs (even if some are in/out). That way it's clear (to client code) which parameters need to be filled on the way in and which get returned. Also this obviates the need for those annotations.

稍微干净一点:输入一个类,输出另一个类(即使有些输入/输出)。这样就很清楚(对于客户端代码)需要在路上填充哪些参数以及返回哪些参数。这也避免了对这些注释的需要。

#10

Thats funny I acutally asked this same question. Still looking for a good solution.

多数民众赞成有趣,我实际上问了同样的问题。仍在寻找一个好的解决方案。

Which ORM is the best when using Stored Procedures

使用存储过程时哪种ORM最佳

#11

using (var conn = new SqlConnection(ConnectionString))
using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = "[dbo].[Save]";
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter(
        "Identity", SqlDbType.Int) { Value = item.Identity });

    cmd.Parameters.Add(new SqlParameter(
        "Name", SqlDbType.NVarChar, 50) { Value = item.Name });

    cmd.Parameters.Add(new SqlParameter(
        "Title", SqlDbType.NVarChar, 100) { Value = item.Title });

    conn.Open();
    cmd.ExecuteNonQuery();
}

Here is how it could look like with Ent Lib:

以下是Ent Lib的外观:

// Note, that you don't need to specify connection string here,
// it will be automatically taken from a configuration file
var db = DatabaseFactory.CreateDatabase();

using (var cmd = db.GetStoredProcCommand("[dbo].[Save]"))
{
    db.AddInParameter(cmd, "Identity", DbType.Int32, item.Identity);
    db.AddInParameter(cmd, "Name", DbType.String, item.Name);
    db.AddInParameter(cmd, "Title", DbType.String, item.Title);
    db.ExecuteNonQuery(cmd);
}

You can also use SqlHelper method from Enterprise Library to simplify this syntax.

您还可以使用Enterprise Library中的SqlHelper方法来简化此语法。

SqlHelper.ExecuteNonQuery(connectinString,
     CommandType.StoredProcedure, "[dbo].[Save]", new SqlParameter[]
         {
             new SqlParameter("Identity", item.Identity),
             new SqlParameter("Name", item.Name),
             new SqlParameter("Title", item.Title)
         });