SqlDataAdapter的使​​用关键字 [英] SqlDataAdapter with using keyword

问题描述

这是下面的code健康吗？或者，我并不需要使用使用关键字为的SqlDataAdapter 将处理关闭连接？

 公共静态数据集取（串SP，的SqlParameter [] PRM）
{
    使用（SqlConnection的CON =新的SqlConnection（构造））
    {
        使用（CMD的SqlCommand = con.CreateCommand（））
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = SP;
            cmd.Parameters.AddRange（PRM）;

            使用（SqlDataAdapter的DTA =新的SqlDataAdapter（CMD））
            {
                数据集DST =新的DataSet（）;
                dta.Fill（DST）;

                返回DST;
            }
        }
    }
}
 

---

---

@MarkGravell我这里需要建议，我真的很期待使用的DataReader ，但我一直在寻找所有的时间通过使用关键字确保关闭连接。凡与的DataReader 我们不能使用它，因为它会关闭连接，如果我们想要返回的DataReader 返回一些方法。 那么你认为以下技术是罚款与的DataReader 和使用关键字：

 公共静态SqlDataReader对象获取（字符串SP，的SqlParameter [] PRM）
{
    的SqlCommand CMD =新的SqlConnection（构造）.CreateCommand（）;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = SP;
    cmd.Parameters.AddRange（PRM）;
    cmd.Connection.Open（）;

    返回cmd.ExecuteReader（CommandBehavior.CloseConnection）;
}

使用（SqlDataReader的dtrPrize = Sql.Fetch（SelectPrize，新的SqlParameter [] {新的SqlParameter（ID，ID）}））
{
    dtrPrize.Read（）;

    奖奖金=新奖（）;
    prize.id =（INT）dtrPrize [dtrPrize.GetOrdinal（ID）;
    prize.artitle =（字符串）dtrPrize [dtrPrize.GetOrdinal（artitle）];
    prize.entitle =（字符串）dtrPrize [dtrPrize.GetOrdinal（授权）;
    prize.ardetail =（字符串）dtrPrize [dtrPrize.GetOrdinal（ardetail）];
    prize.endetail =（字符串）dtrPrize [dtrPrize.GetOrdinal（endetail）];
    prize.image =（字符串）dtrPrize [dtrPrize.GetOrdinal（图像）;
    prize.theme =（字符串）dtrPrize [dtrPrize.GetOrdinal（主题）;
    prize.price =（INT）dtrPrize [dtrPrize.GetOrdinal（代价）;
    prize.audience =（INT）dtrPrize [dtrPrize.GetOrdinal（观众）;
    prize.type =（字节）dtrPrize [dtrPrize.GetOrdinal（类型）];
    prize.status =（字节）dtrPrize [dtrPrize.GetOrdinal（状态）;
    prize.voucher =（字符串）dtrPrize [dtrPrize.GetOrdinal（凭单）;
    prize.supplierid =（int）的dtrPrize [dtrPrize.GetOrdinal（供应商ID）];
    prize.created =（DateTime的）dtrPrize [dtrPrize.GetOrdinal（创建）;
    prize.updated =（DateTime的）dtrPrize [dtrPrize.GetOrdinal（更新）;

    回报奖;
}
 

解决方案

健康十岁上下;我个人想说的不健康位就是它使用数据集和 DataAdapter的的位，但也许是只是我个人的偏见。

是的，你应该在这里处理适配器等（这是什么使用为你做，很明显）。

作为一个平凡的毫无意义的整洁，可以叠加的使用的S - 只是使它有点不太详细的：

 使用（SqlConnection的CON =新的SqlConnection（构造））
使用（CMD的SqlCommand = con.CreateCommand（））
{
 

Is this the following code healthy? Or I don't need to use the using keyword as the SqlDataAdapter will handle closing the connection?

public static DataSet Fetch(string sp, SqlParameter [] prm)
{
    using (SqlConnection con = new SqlConnection(ConStr))
    {
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sp;
            cmd.Parameters.AddRange(prm);

            using (SqlDataAdapter dta = new SqlDataAdapter(cmd))
            {
                DataSet dst = new DataSet();
                dta.Fill(dst);

                return dst;
            }
        }
    }
}

---

---

@MarkGravell I need a suggestions here, I am really looking to use DataReader, but I was looking all the time to use the using keyword to ensure closing the connections. Where with DataReader we can not use it because it will close the connection if we want to return the DataReader back to some method. So do you think the following technique is fine with DataReader and the using keyword:

public static SqlDataReader Fetch(string sp, SqlParameter [] prm)
{
    SqlCommand cmd = new SqlConnection(ConStr).CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = sp;
    cmd.Parameters.AddRange(prm);
    cmd.Connection.Open();

    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}

using (SqlDataReader dtrPrize = Sql.Fetch("SelectPrize", new SqlParameter[] { new SqlParameter("id", id) }))
{
    dtrPrize.Read();

    Prize prize = new Prize();
    prize.id = (int)dtrPrize[dtrPrize.GetOrdinal("id")];
    prize.artitle = (string)dtrPrize[dtrPrize.GetOrdinal("artitle")];
    prize.entitle = (string)dtrPrize[dtrPrize.GetOrdinal("entitle")];
    prize.ardetail = (string)dtrPrize[dtrPrize.GetOrdinal("ardetail")];
    prize.endetail = (string)dtrPrize[dtrPrize.GetOrdinal("endetail")];
    prize.image = (string)dtrPrize[dtrPrize.GetOrdinal("image")];
    prize.theme = (string)dtrPrize[dtrPrize.GetOrdinal("theme")];
    prize.price = (int)dtrPrize[dtrPrize.GetOrdinal("price")];
    prize.audience = (int)dtrPrize[dtrPrize.GetOrdinal("audience")];
    prize.type = (byte)dtrPrize[dtrPrize.GetOrdinal("type")];
    prize.status = (byte)dtrPrize[dtrPrize.GetOrdinal("status")];
    prize.voucher = (string)dtrPrize[dtrPrize.GetOrdinal("voucher")];
    prize.supplierid = (int)dtrPrize[dtrPrize.GetOrdinal("supplierid")];
    prize.created = (DateTime)dtrPrize[dtrPrize.GetOrdinal("created")];
    prize.updated = (DateTime)dtrPrize[dtrPrize.GetOrdinal("updated")];

    return prize;
}

解决方案

Healthy-ish; personally I'd say the unhealthy bit is the bit where it makes use of DataSet and DataAdapter, but that is perhaps just my personal bias.

Yes, you should dispose the adapter etc here (which is what the using does for you, obviously).

As a trivial pointless tidy, you can stack the usings - just makes it a little less verbose:

using (SqlConnection con = new SqlConnection(ConStr))
using (SqlCommand cmd = con.CreateCommand())
{

这篇关于SqlDataAdapter的使​​用关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！