在c＃中从db层调用sprocs

suppose you have some simple sprocs eg

假设你有一些简单的sprocs,例如

AddXYZ(param1, param 2...etc)

AddXYZ(param1,param 2 ......等)

getAllXYZ()

getXYZ(id)

what is the "best practice" way of calling these sprocs from the "db layer"

什么是从“数据库层”调用这些sprocs的“最佳实践”方法

i don't want to use linq. just simple c# static methods on how to do this.

我不想使用linq。只是简单的c#静态方法就如何做到这一点。

im using sqlserver.

即时通讯使用sqlserver。

5 个解决方案

#1

I don't think you really meant static methods, as that would be a pretty non-OO way of going about something like this, and no such facility is built into C#. There are standard ADO.NET classes, however, that will let you do this without any ORM wrapping or using DataSet's and the like.-

我不认为你真的是指静态方法,因为这将是一个非常非OO的方式来做这样的事情,并且C#中没有构建这样的工具。但是,有一些标准的ADO.NET类可以让你在没有任何ORM包装或使用DataSet之类的情况下执行此操作.-

If you truly want to manually invoke the stored proc and get back a set of results without ANY ORM or standardized storage mechanism doing it for you, this would be your best bet:

如果您真的想要手动调用存储过程并在没有任何ORM或标准化存储机制的情况下获取一组结果,那么这将是您最好的选择:

using(System.Data.IDbConnection conn = /*create your connection here*/)
{
    using(System.Data.IDbCommand cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "AddXYZ";

        // add your parameters here using cmd.CreateParameter() and cmd.Parameters.Add()

        using(System.Data.IDbDataReader reader = cmd.ExecuteReader())
        {
            while(reader.Read())
            {
                // read your results row-by-row
            }
        }
    }
}

You didn't specify which database engine you were connecting through, so I used the common interfaces that will abstract you away from that. If you wish (though I generally frown on code that does this) you can use platform-specific classes that make things SLIGHTLY easier, at least in terms of adding parameters (the code isn't as verbose as the interface-based approach)

您没有指定要连接的数据库引擎,因此我使用了通用接口来抽象您。如果你愿意(虽然我通常不会对执行此操作的代码感到不满),你可以使用平台特定的类,使事情变得更加容易,至少在添加参数方面(代码不像基于接口的方法那么冗长)

#2

Is this what you are looking for?

这是你想要的?

SqlCommand cmd  = new SqlCommand("AddXYZ", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@param1", someValue));

#3

First, you create a single place in the data layer for getting connection information. This might be a private member if your data layer is confined to a single class, or internal item if the layer encompasses an entire assembly. It could return a connection string or an actual connection object itself, but the main thing is that it's not exposed outside the data layer at all:

首先,在数据层中创建一个用于获取连接信息的位置。如果您的数据层仅限于单个类,则可能是私有成员;如果图层包含整个程序集,则可能是内部项。它可以返回一个连接字符串或一个实际的连接对象本身,但主要的是它根本没有暴露在数据层之外:

private static ConnectionString { get { // read from config file once.... return ""; } }

private SqlConnection getConnection()
{
    SqlConnection result = new SqlConnection(ConnectionString);
    result.Open();  // I like to open it in advance, but that's less common
    return result;  // you'll want some error handling code in here as well
}

You then provide public methods in the data layer that match the interface you want to provide to the business layer. In a well-designed app this will generally match the stored procedures, and that sounds like what you're going for, but sometimes it doesn't quite work work out so well. For example, you may need to call multiple procedures from one method.

然后,在数据层中提供与要提供给业务层的接口匹配的公共方法。在一个设计良好的应用程序中,这通常会与存储过程相匹配,这听起来就像你想要的那样,但有时它并不能很好地工作。例如,您可能需要从一个方法调用多个过程。

Whatever you do, the method should accept strongly-typed parameters values for use when calling the procedures. There's some debate about whether the method should return a business object or a datarecord. Personally, I tend to favor returning a datarecord, but providing an additional "layer" where the datarecords are translated to strongly-typed business objects:

无论您做什么,该方法都应接受强类型参数值,以便在调用过程时使用。关于该方法是应该返回业务对象还是数据记录存在争议。就个人而言,我倾向于返回一个datarecord,但提供一个额外的“层”,其中datarecords被转换为强类型的业务对象:

public IDataRecord GetXYZ(int id)
{
    DataTable dt = new DataTable();
    using (var cn = getConnection())
    using (var cmd = new SqlCommand("getXYZ"))
    {
        cmd.CommandType = CommandTypes.StoredProcedure;
        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;

        using (var rdr = cmd.ExecuteReader())
        {
           dt.Load(rdr);
        }
    }

    //obviously put a little more work into your error handling
    if (dt.Rows.Count <= 0)
       throw new Exception("oops");  

    return dt.Rows[0];
}

public class XYZFactory
{
    public static XZY Create(IDataRecord row)
    {
        XYZ result = new XYZ();
        result.id = row["ID"];
        result.otherfield = row["otherfield"];
        return result;
    }
}

#4

In general, then, you'll want to look at the SQLConnection and SQLCommand classes (presuming you're connecting to a SQL db, of course). You'll set the SQLCommand.CommandText property to something like "EXEC AddXYZ @X, @Y, @Z" and then use SQLCommand.Parameters.AddWithValue() for each of @X, @Y, and @Z.

通常,那么,您将需要查看SQLConnection和SQLCommand类(假设您正在连接到SQL db,当然)。您将SQLCommand.CommandText属性设置为“EXEC AddXYZ @X,@ Y,@ Z”,然后对@X,@ Y和@Z使用SQLCommand.Parameters.AddWithValue()。

You'll then call your SQLCommand's appropriate execute method (NonQuery, Scalar, or Reader).

然后,您将调用SQLCommand的相应执行方法(NonQuery,Scalar或Reader)。

#5

Thomas' example is the standard way. I suggest you look at the following for further help:

托马斯的榜样是标准方式。我建议您查看以下内容以获得进一步的帮助:

http://msdn.microsoft.com/en-us/library/aa902662.aspx

Personally, I've written a static class (DataHelper) which exposes a bunch of methods that you're likely to use such as those that return IDataReader objects for when your stored proc returns stuff, and void methods for when they do not, as well as some which return the Connection object in an out param so you can keep it open.

就个人而言,我编写了一个静态类(DataHelper),它暴露了一些你可能会使用的方法,例如那些在你的存储过程返回东西时返回IDataReader对象的方法,以及当它们不返回时返回的方法,如以及一些在out参数中返回Connection对象的内容,以便您可以将其保持打开状态。

The class just wraps the ADO.NET code in try catch blocks for common problem checking and logging/tracing (DNS down, server down, svc down), and uses the using statement to ensure that resources aren't wasted. I also wrap a method which uses a ConnectionStringBuilder to return a connection string so I don't have to keep writing that code.

该类只是将ADO.NET代码包装在try catch块中,用于常见问题检查和日志记录/跟踪(DNS关闭,服务器关闭,svc关闭),并使用using语句确保不浪费资源。我还包装了一个方法,该方法使用ConnectionStringBuilder返回连接字符串,因此我不必继续编写该代码。

I did find that for a big project, coding all my entity 'providers' was repetitive and I should have designed a more reusable configuration driven model.

我确实发现,对于一个大项目,编码所有实体'提供者'是重复的,我应该设计一个更可重用的配置驱动模型。

These days if I had greenfield I'd look at using the EDM.

这些天如果我有绿地,我会看看使用EDM。

http://msdn.microsoft.com/en-us/library/aa697428(VS.80).aspx

#1