Can I convert IList to a DataSet, DataTable or DataView?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a code that returns data in IList. My webGrid doesn't allow me to
sort with IList returned, it say it only suports DataView, DataTable and
DataSet, not IEnumerable. I don't know how to return the DataSet type when
using the following code:

======== this is my interface ======================
namespace WareHouse.DataLayer.DataObjects
{
/// <summary>
/// Defines methods to access categories and products.
/// This is a database-independent interface. The implementations will
/// be database specific.
/// </summary>
public interface IProductDao
{
/// <summary>
/// Gets a product.
/// </summary>
/// <param name="productId">Unique product identifier.</param>
/// <returns>Product.</returns>
//IList<Product> SelectProductsAll();

Product SelectProductsAll();

}
}


================ this is where I create my IList ==============
namespace WareHouse.DataLayer.DataObjects.SqlServer
{
class SqlServerProductDao : IProductDao
{
public IList<Product> SelectProductsAll()
{

StringBuilder sql = new StringBuilder();
sql.Append("usp_SelectProductsAll");

DataTable dt = Db.GetDataTable(sql.ToString());

IList<Product> list = new List<Product>();
foreach (DataRow row in dt.Rows)
{
string productname = row["P_ProductName"].ToString();
list.Add(new Product(productname));
}
return list;
}


================ This is where I populate the dataset ========
/// <summary>
/// Populates a DataSet according to a Sql statement.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <returns>Populated DataSet.</returns>
public static DataSet GetDataSet(string sql)
{
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;

using (DbCommand command = factory.CreateCommand())
{
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = sql;

using (DbDataAdapter adapter =
factory.CreateDataAdapter())
{
adapter.SelectCommand = command;

DataSet ds = new DataSet();
adapter.Fill(ds);

return ds;
}
}
}
}

/// <summary>
/// Populates a DataTable according to a Sql statement.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <returns>Populated DataTable.</returns>
public static DataTable GetDataTable(string sql)
{
return GetDataSet(sql).Tables[0];
}

/// <summary>
/// Populates a DataRow according to a Sql statement.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <returns>Populated DataRow.</returns>
public static DataRow GetDataRow(string sql)
{
DataRow row = null;

DataTable dt = GetDataTable(sql);
if (dt.Rows.Count > 0)
{
row = dt.Rows[0];
}

return row;
}


I tried to return a dataset instead of IList, but it doesnt' seem to work.
Any suggestions?
 
The problem is that if I use IList, I can't sort my grid when I click there
sort header hyperlink on the grid

thanks
Nick
 
Nick said:
The problem is that if I use IList, I can't sort my grid when I click
there
sort header hyperlink on the grid

thanks
Nick
Click to expand...

from:
http://www.asp.net/QUICKSTART/aspnet/doc/ctrlref/data/objectdatasource.aspx

Like SqlDataSource, the ObjectDataSource control supports sorting when the
SelectMethod returns a DataSet, DataView, or DataTable object. Internally,
the ObjectDataSource relies on the DataView.Sort property to perform sorting
in this case. ObjectDataSource also supports custom sorting in the
SelectMethod implementation, which is useful if the method doesn't return a
DataSet, DataView, or DataTable. Custom sorting is configured by setting
SortParameterName property to the name of a method parameter that accepts
the SortExpression from the data source. When the SelectMethod is called,
ObjectDataSource will pass this expression to your method and you can
implement your own sorting logic using this expression. The preceding
example demonstrates custom a custom sorting implementation in the
AuthorsComponent class.

---------

So it is possible, it will just require a bit of work :)

Now for how to return a datatable:

public DataTable SelectProductsAll()
{

StringBuilder sql = new StringBuilder();
sql.Append("usp_SelectProductsAll");

DataTable dt = Db.GetDataTable(sql.ToString());
dt.Columns.Item["P_ProductName"].ColumnName = "productname";

return dt;
}

Since the datatable has an underlying dataview the built in sort command
should work.
And you should be done :)
 
Excellent, although I am having problems with the following line because
there is no "Items" in the dt.Columns:

dt.Columns.Item["P_ProductName"].ColumnName = "productname";

thanks
Nick



Mischa Kroon said:
Nick said:
The problem is that if I use IList, I can't sort my grid when I click
there
sort header hyperlink on the grid

thanks
Nick
Click to expand...

from:
http://www.asp.net/QUICKSTART/aspnet/doc/ctrlref/data/objectdatasource.aspx

Like SqlDataSource, the ObjectDataSource control supports sorting when the
SelectMethod returns a DataSet, DataView, or DataTable object. Internally,
the ObjectDataSource relies on the DataView.Sort property to perform sorting
in this case. ObjectDataSource also supports custom sorting in the
SelectMethod implementation, which is useful if the method doesn't return a
DataSet, DataView, or DataTable. Custom sorting is configured by setting
SortParameterName property to the name of a method parameter that accepts
the SortExpression from the data source. When the SelectMethod is called,
ObjectDataSource will pass this expression to your method and you can
implement your own sorting logic using this expression. The preceding
example demonstrates custom a custom sorting implementation in the
AuthorsComponent class.

---------

So it is possible, it will just require a bit of work :)

Now for how to return a datatable:

public DataTable SelectProductsAll()
{

StringBuilder sql = new StringBuilder();
sql.Append("usp_SelectProductsAll");

DataTable dt = Db.GetDataTable(sql.ToString());
dt.Columns.Item["P_ProductName"].ColumnName = "productname";

return dt;
}

Since the datatable has an underlying dataview the built in sort command
should work.
And you should be done :)
Click to expand...
 
The most reusable solution is to actually inherit from the gridview and
write a little bit of code. I've recoded the gridview to take the
object data source and perform all the sorting internally through a
little bit of reflection. I'll probably post something soon once I have
it a bit more finalized. It covers using the update / delete / select /
paging / sorting for the gridview and has the same interface for the
most part. All I do is grab the datasource, pull in the data, clear the
datasourceID and store that internally and then through reflection sort
the data as well as call the update / delete methods with a little
reflection. I am just validating the performance of it at this point.
The beauty of this type of solution is you don't need to write code for
every single ODS like most people suggest.
 
Back
Top