Geek Answers Handbook

The most efficient way to retrieve data from a database in ASP.NET

I am writing a method to return the string "asset" from the database. It contains strings, ints and an array of bytes (this can be an image / movie / document).

Now, to access most strings, I use the following method, which returns a NameValueCollection, since it is a light weighted object, easy to use, and distinguishes between int and strings.

        public static NameValueCollection ReturnNameValueCollection(Database db, DbCommand dbCommand)
    {

        var nvc = new NameValueCollection();

        using (IDataReader dr = db.ExecuteReader(dbCommand))
        {
            if (dr != null)
            {
                 while (dr.Read())
                 {
                     for (int count = 0; count < dr.FieldCount; count++)
                     {
                         nvc[dr.GetName(count)] = dr.GetValue(count).ToString();
                     }
                 }
            }
        }

        dbCommand.Dispose();
        return nvc.Count != 0 ? nvc : null;
    }

Now my apporach for such data access, as a rule, should get a method to return datarow.

       public static DataRow ReturnDataRow(Database db, DbCommand dbCommand)
    {
        var dt = new DataTable();

        using (IDataReader dr = db.ExecuteReader(dbCommand))
            if (dr != null) dt.Load(dr);

        dbCommand.Dispose();
        return dt.Rows.Count != 0 ? dt.Rows[0] : null;
    }

It seems like it's useless to create a DataTable and then return your first datarow.

Is there a better way to do this?

I think maybe a dictionary of objects, which I then manually dropped every member.

, . , - , DataSets (, , ), .

, , , .

+5
5

?

, , ? . , , .

, , . :

public class Product {
    public int ProductID { get; set; }
    public string Name { get; set; }
    public byte[] Image { get; set; }
}

(Collection) :

var collection = new Collection<Product>();

using (var reader = command.ExecuteReader()) {
    while (reader.Read()) {
        var product = new Product();

        int ordinal = reader.GetOrdinal("ProductID");
        if (!reader.IsDBNull(ordinal) {
            product.ProductID = reader.GetInt32(ordinal);
        }

        ordinal = reader.GetOrdinal("Name");
        if (!reader.IsDBNull(ordinal)) {
            product.Name = reader.GetString(ordinal);
        }

        ordinal = reader.GetOrdinal("Image");
        if (!reader.IsDBNull(ordinal)) {
            var sqlBytes = reader.GetSqlBytes(ordinal);
            product.Image = sqlBytes.Value;
        }

        collection.Add(product);
    }
}

, Get x, x - , . Microsoft http://msdn.microsoft.com/en-us/library/haa3afyz.aspx ( ), System.Object .

, ASP.NET, , . , NameValueCollection, (, , ). , , ASP.NET. , NameValueCollection . , .

, , , LINQ to SQL ADO.NET Entity Framework. , .

+7

, , , , , , . , , , / - :

public class MyAsset
{
    public int ID;
    public string Name;
    public string Description;
}

public MyAsset GetAsset(IDBConnection con, Int AssetId)
{
    using (var cmd = con.CreateCommand("sp_GetAsset"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(cmd.CreateParameter("AssetID"));
        using(IDataReader dr = cmd.ExecuteReader())
        {
            if (!dr.Read()) return null;

            return new MyAsset() { 
                ID = dr.GetInt32(0), 
                Name = dr.GetString(1), 
                Description = dr.GetString(2)
            };
        }
    }
}

, KVP...

, , , ...

, , , , , , , , - , , , MyAsset. , - , proc , . , - , , ... , MyAssetInstance.ID, MyAssetInstance.Name, MyAssetInstance.Description ..

+3

, , Primitive Obsession. . ... , -, , . , .

, ORM, . .

+2

, . , , - DataTable DataRow . . , , .

0

. , ORM - , , , MVC- .

, , , , - .

, mnero0429 balabaster . datareader , -. MS mnero0429. - -;)

ADO.

- , , DataSet.Tables [0].Rows [0] [ "bob" ] , - , , !

0

More articles:


All Articles