public void UseOfDataSet()
{
SqlDatabase sqlDb = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(GetConnectionString());
//IDataReader reader = sqlDb.ExecuteReader(CommandType.Text, "Select * from Syncada.Location");
DbCommand command = new System.Data.SqlClient.SqlCommand();
command.CommandText = "select * from Syncada.Location";
command.CommandType = CommandType.Text;
DataSet locationSet = new DataSet();
sqlDb.LoadDataSet(command, locationSet, "Location");
//var tb = locationSet.Tables[0].AsEnumerable().Select(field => field);
var tb1 = from loc in locationSet.Tables[0].AsEnumerable().Select(field => field) select new { LocationID = loc["LocationID"].ToString(), Street1 = loc["Street1"].ToString() };
foreach (var item in tb1)
{
Console.WriteLine(item.LocationID);
}
}
private static void UpdateUsingDataSet(SqlDatabase database)
{
var productsDataSet = new DataSet();
var cmd = database.GetSqlStringCommand("Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate From Products");
// Retrieve the initial data.
database.LoadDataSet(cmd, productsDataSet, "Products");
// Get the table that will be modified.
var productsTable = productsDataSet.Tables["Products"];
// Add a new product to existing DataSet.
productsTable.Rows.Add(new object[] { DBNull.Value, "New product", 11, 25 });
// Modify an existing product.
productsTable.Rows[0]["ProductName"] = "Modified product";
// Establish the Insert, Delete, and Update commands.
var insertCommand = database.GetStoredProcCommand("AddProduct");
database.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
database.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);
database.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current);
var deleteCommand = database.GetStoredProcCommand("DeleteProduct");
database.AddInParameter(deleteCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);
var updateCommand = database.GetStoredProcCommand("UpdateProduct");
database.AddInParameter(updateCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);
database.AddInParameter(updateCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
database.AddInParameter(updateCommand, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Current);
// Submit the DataSet, capturing the number of rows that were affected.
var rowsAffected = database.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand, deleteCommand,
UpdateBehavior.Standard);
}
