public int Update(ProductDataSet dataSet)
{
if (dataSet != null)
{
try
{
int updatedRowCount = 0;
foreach(ProductDataSet.ProductRow row in dataSet.Product)
{
switch (row.RowState)
{
case DataRowState.Added:
{
OnProductUpdating(new ProductEventArgs(row, StatementType.Insert)) ;
_command = this.GetCommand();
_command.CommandText = @"
INSERT INTO [Product] (
[ProductId],
[CategoryId],
[Name],
[Descn],
[Image]
) VALUES (
@ProductId,
@CategoryId,
@Name,
@Descn,
@Image
)";
_command.Parameters.Add(this.CreateParameter("@ProductId", DbType.AnsiString, row.IsProductIdNull() ? (object)DBNull.Value : (object)row.ProductId));
_command.Parameters.Add(this.CreateParameter("@CategoryId", DbType.AnsiString, row.IsCategoryIdNull() ? (object)DBNull.Value : (object)row.CategoryId));
_command.Parameters.Add(this.CreateParameter("@Name", DbType.AnsiString, row.IsNameNull() ? (object)DBNull.Value : (object)row.Name));
_command.Parameters.Add(this.CreateParameter("@Descn", DbType.AnsiString, row.IsDescnNull() ? (object)DBNull.Value : (object)row.Descn));
_command.Parameters.Add(this.CreateParameter("@Image", DbType.AnsiString, row.IsImageNull() ? (object)DBNull.Value : (object)row.Image));
this.OpenConnection();
_command.ExecuteNonQuery();
OnProductUpdated(new ProductEventArgs(row, StatementType.Insert)) ;
updatedRowCount++;
break;
}
case DataRowState.Modified:
{
OnProductUpdating(new ProductEventArgs(row, StatementType.Update)) ;
_command = this.GetCommand();
_command.CommandText = @"
UPDATE [Product] SET
[CategoryId] = @CategoryId,
[Name] = @Name,
[Descn] = @Descn,
[Image] = @Image
WHERE
[ProductId] = @ProductId
";
_command.Parameters.Add(this.CreateParameter("@ProductId", DbType.AnsiString, row.IsProductIdNull() ? (object)DBNull.Value : (object)row.ProductId));
_command.Parameters.Add(this.CreateParameter("@CategoryId", DbType.AnsiString, row.IsCategoryIdNull() ? (object)DBNull.Value : (object)row.CategoryId));
_command.Parameters.Add(this.CreateParameter("@Name", DbType.AnsiString, row.IsNameNull() ? (object)DBNull.Value : (object)row.Name));
_command.Parameters.Add(this.CreateParameter("@Descn", DbType.AnsiString, row.IsDescnNull() ? (object)DBNull.Value : (object)row.Descn));
_command.Parameters.Add(this.CreateParameter("@Image", DbType.AnsiString, row.IsImageNull() ? (object)DBNull.Value : (object)row.Image));
this.OpenConnection();
_command.ExecuteNonQuery();
OnProductUpdated(new ProductEventArgs(row, StatementType.Update)) ;
updatedRowCount++;
break;
}
case DataRowState.Deleted:
{
OnProductUpdating(new ProductEventArgs(row, StatementType.Delete)) ;
_command = this.GetCommand();
_command.CommandText = @"
DELETE FROM [Product]
WHERE
[ProductId] = @ProductId
";
_command.Parameters.Add(this.CreateParameter("@ProductId", DbType.AnsiString, row[dataSet.Product.ProductIdColumn, DataRowVersion.Original]));
this.OpenConnection();
_command.ExecuteNonQuery();
OnProductUpdated(new ProductEventArgs(row, StatementType.Delete)) ;
updatedRowCount++;
break;
}
}
}
dataSet.AcceptChanges();
return updatedRowCount;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
return 0;
}
finally
{
this.Cleanup();
}
}
else
{
this.Cleanup();
throw new ArgumentException("DataSet null");
}
}
private void PopulateProductDataRow(IDataReader reader, ProductDataSet.ProductRow row)
{
if (!reader.IsDBNull(0)) row.ProductId = reader.GetString(0);
if (!reader.IsDBNull(1)) row.CategoryId = reader.GetString(1);
if (!reader.IsDBNull(2)) row.Name = reader.GetString(2);
if (!reader.IsDBNull(3)) row.Descn = reader.GetString(3);
if (!reader.IsDBNull(4)) row.Image = reader.GetString(4);
}
public int Fill(ProductDataSet dataSet)
{
try
{
int recordcount = 0;
_command = this.GetCommand();
_command.CommandText = @"
SELECT
[ProductId],
[CategoryId],
[Name],
[Descn],
[Image]
FROM
[Product]";
this.OpenConnection();
_reader = _command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);
while (_reader.Read())
{
ProductDataSet.ProductRow row = dataSet.Product.NewProductRow();
this.PopulateProductDataRow(_reader, row);
dataSet.Product.AddProductRow(row);
recordcount++;
}
dataSet.AcceptChanges();
return recordcount;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
return 0;
}
finally
{
this.Cleanup();
}
}
public int FillByProductId(ProductDataSet dataSet,
string productId
)
{
try
{
int recordcount = 0;
_command = this.GetCommand();
_command.CommandText = @"
SELECT
[ProductId]
FROM
[Product]
WHERE
[ProductId] = @ProductId
";
_command.Parameters.Add(this.CreateParameter("@ProductId", DbType.AnsiString, productId));
this.OpenConnection();
_reader = _command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);
while (_reader.Read())
{
ProductDataSet.ProductRow row = dataSet.Product.NewProductRow();
this.PopulateProductDataRow(_reader, row);
dataSet.Product.AddProductRow(row);
recordcount++;
}
dataSet.AcceptChanges();
return recordcount;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
return 0;
}
finally
{
this.Cleanup();
}
}
public int Fill(ProductDataSet dataSet, string[] columns, string[] values, DbType[] types)
{
try
{
int recordcount = 0;
_command = this.GetCommand();
_command.CommandText = @"
SELECT
[ProductId],
[CategoryId],
[Name],
[Descn],
[Image]
FROM
[Product]
WHERE ";
for(int i = 0;i < columns.Length; i++)
{
_command.CommandText += columns[i] + " = " + (types[i] == DbType.AnsiString ? "'" + values[i] + "'" : values[i]);
if(i < columns.Length - 1)
_command.CommandText += " AND ";
}
for(int i = 0;i < columns.Length; i++)
_command.Parameters.Add(this.CreateParameter("@" + columns[i], types[i], columns[i]));
this.OpenConnection();
_reader = _command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);
while (_reader.Read())
{
ProductDataSet.ProductRow row = dataSet.Product.NewProductRow();
this.PopulateProductDataRow(_reader, row);
dataSet.Product.AddProductRow(row);
recordcount++;
}
dataSet.AcceptChanges();
return recordcount;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
return 0;
}
finally
{
this.Cleanup();
}
}
public int Fill(ProductDataSet dataSet, string productId)
{
try
{
_command = this.GetCommand();
_command.CommandText = @"
SELECT
[ProductId],
[CategoryId],
[Name],
[Descn],
[Image]
FROM
[Product]
WHERE
[ProductId] = @ProductId
";
_command.Parameters.Add(this.CreateParameter("@ProductId", DbType.AnsiString, productId));
this.OpenConnection();
_reader = _command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult | CommandBehavior.SingleRow);
if (_reader.Read())
{
ProductDataSet.ProductRow row = dataSet.Product.NewProductRow();
this.PopulateProductDataRow(_reader, row);
dataSet.Product.AddProductRow(row);
dataSet.AcceptChanges();
return 1;
}
else
{
throw new ProductNotFoundException();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
return 0;
}
finally
{
this.Cleanup();
}
}
public int Fill(ProductDataSet dataSet, DataRow dataRecord)
{
return Fill(dataSet, ((string)(dataRecord["ProductId"])));
}
public ProductEventArgs(ProductDataSet.ProductRow row, StatementType statementType)
{
_dataRow = row;
_statementType = statementType;
}
static void Main(string[] args)
{
// PetShop
OrdersDataSet cds = new OrdersDataSet();
OrdersDataAdapter cda = new OrdersDataAdapter();
ProductDataSet pds = new ProductDataSet();
ProductDataAdapter pda = new ProductDataAdapter();
pda.FillByCategoryId(pds, "BIRDS");
Console.Out.WriteLine(cds.Orders.Rows.Count);
IEnumerator en = pds.Product.GetEnumerator();
while (en.MoveNext())
{
ProductDataSet.ProductRow row = (ProductDataSet.ProductRow)en.Current;
Console.Out.WriteLine(row.ProductId + " " + row.CategoryId + " " + row.Name + " " + row.Descn);
}
// fill all the records from the permission table.
string[] columns = { "ShipCity", "Courier" };
string[] values = { "Nowhere", "Me" };
DbType[] types = { DbType.AnsiString, DbType.AnsiString };
cda.Fill(cds, columns, values, types);
Console.Out.WriteLine(cds.Orders.Rows.Count);
Console.Out.WriteLine(cds.Orders.FindByOrderId(1) != null ? cds.Orders.FindByOrderId(1).OrderId.ToString() : "Nope");
Console.Out.WriteLine(cds.Orders.FindByOrderId(4) != null ? cds.Orders.FindByOrderId(4).OrderId.ToString() : "Nope");
Console.In.Read();
cds.Clear();
cda.Fill(cds);
OrdersDataSet.OrdersRow newRow = cds.Orders.NewOrdersRow();
newRow.OrderDate = DateTime.Now;
newRow.ShipAddr1 = "2001 Nowhere";
newRow.ShipCity = "Nowhere";
newRow.ShipState = "Tx";
newRow.ShipZip = "12345";
newRow.UserId = "joe";
newRow.ShipCountry = "USA";
newRow.BillAddr1 = "2001 UHUH";
newRow.BillCity = "Nowhere";
newRow.BillState = "Tx";
newRow.BillZip = "12345";
newRow.UserId = "yoyDu";
newRow.BillCountry = "USA";
newRow.Courier = "Me";
newRow.TotalPrice = 12.12M;
newRow.BillToFirstName = "Yaba";
newRow.BillToLastName = "Daba";
newRow.ShipToFirstName = "Yoko";
newRow.ShipToLastName = "Ono";
newRow.AuthorizationNumber = 123;
newRow.Locale = "Here";
cds.Orders.AddOrdersRow(newRow);
Console.In.Read();
// make some changes and save
//OrdersDataSet.OrdersRow editRow = cds.Orders.FindByOrderId(19);
//editRow.BillZip = "33333";
//editRow.Courier = "USPS";
cda.Update(cds);
//Console.In.Read();
//// reset changes and save
//Console.In.Read();
//// Delete row and update
//OrdersDataSet.OrdersRow deleteRow = cds.Orders.FindByOrderId(20);
//deleteRow.Delete();
//cda.Update(cds);
//Console.In.Read();
}