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");
}
}
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)
{
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 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();
}
}
