public int Update(OrdersDataSet dataSet)
{
if (dataSet != null)
{
try
{
int updatedRowCount = 0;
foreach(OrdersDataSet.OrdersRow row in dataSet.Orders)
{
switch (row.RowState)
{
case DataRowState.Added:
{
OnOrdersUpdating(new OrdersEventArgs(row, StatementType.Insert)) ;
_command = this.GetCommand();
_command.CommandText = @"
INSERT INTO [Orders] (
[UserId],
[OrderDate],
[ShipAddr1],
[ShipAddr2],
[ShipCity],
[ShipState],
[ShipZip],
[ShipCountry],
[BillAddr1],
[BillAddr2],
[BillCity],
[BillState],
[BillZip],
[BillCountry],
[Courier],
[TotalPrice],
[BillToFirstName],
[BillToLastName],
[ShipToFirstName],
[ShipToLastName],
[AuthorizationNumber],
[Locale]
) VALUES (
@UserId,
@OrderDate,
@ShipAddr1,
@ShipAddr2,
@ShipCity,
@ShipState,
@ShipZip,
@ShipCountry,
@BillAddr1,
@BillAddr2,
@BillCity,
@BillState,
@BillZip,
@BillCountry,
@Courier,
@TotalPrice,
@BillToFirstName,
@BillToLastName,
@ShipToFirstName,
@ShipToLastName,
@AuthorizationNumber,
@Locale
)";
_command.Parameters.Add(this.CreateParameter("@UserId", DbType.AnsiString, row.IsUserIdNull() ? (object)DBNull.Value : (object)row.UserId));
_command.Parameters.Add(this.CreateParameter("@OrderDate", DbType.DateTime, row.IsOrderDateNull() ? (object)DBNull.Value : (object)row.OrderDate));
_command.Parameters.Add(this.CreateParameter("@ShipAddr1", DbType.AnsiString, row.IsShipAddr1Null() ? (object)DBNull.Value : (object)row.ShipAddr1));
_command.Parameters.Add(this.CreateParameter("@ShipAddr2", DbType.AnsiString, row.IsShipAddr2Null() ? (object)DBNull.Value : (object)row.ShipAddr2));
_command.Parameters.Add(this.CreateParameter("@ShipCity", DbType.AnsiString, row.IsShipCityNull() ? (object)DBNull.Value : (object)row.ShipCity));
_command.Parameters.Add(this.CreateParameter("@ShipState", DbType.AnsiString, row.IsShipStateNull() ? (object)DBNull.Value : (object)row.ShipState));
_command.Parameters.Add(this.CreateParameter("@ShipZip", DbType.AnsiString, row.IsShipZipNull() ? (object)DBNull.Value : (object)row.ShipZip));
_command.Parameters.Add(this.CreateParameter("@ShipCountry", DbType.AnsiString, row.IsShipCountryNull() ? (object)DBNull.Value : (object)row.ShipCountry));
_command.Parameters.Add(this.CreateParameter("@BillAddr1", DbType.AnsiString, row.IsBillAddr1Null() ? (object)DBNull.Value : (object)row.BillAddr1));
_command.Parameters.Add(this.CreateParameter("@BillAddr2", DbType.AnsiString, row.IsBillAddr2Null() ? (object)DBNull.Value : (object)row.BillAddr2));
_command.Parameters.Add(this.CreateParameter("@BillCity", DbType.AnsiString, row.IsBillCityNull() ? (object)DBNull.Value : (object)row.BillCity));
_command.Parameters.Add(this.CreateParameter("@BillState", DbType.AnsiString, row.IsBillStateNull() ? (object)DBNull.Value : (object)row.BillState));
_command.Parameters.Add(this.CreateParameter("@BillZip", DbType.AnsiString, row.IsBillZipNull() ? (object)DBNull.Value : (object)row.BillZip));
_command.Parameters.Add(this.CreateParameter("@BillCountry", DbType.AnsiString, row.IsBillCountryNull() ? (object)DBNull.Value : (object)row.BillCountry));
_command.Parameters.Add(this.CreateParameter("@Courier", DbType.AnsiString, row.IsCourierNull() ? (object)DBNull.Value : (object)row.Courier));
_command.Parameters.Add(this.CreateParameter("@TotalPrice", DbType.Decimal, row.IsTotalPriceNull() ? (object)DBNull.Value : (object)row.TotalPrice));
_command.Parameters.Add(this.CreateParameter("@BillToFirstName", DbType.AnsiString, row.IsBillToFirstNameNull() ? (object)DBNull.Value : (object)row.BillToFirstName));
_command.Parameters.Add(this.CreateParameter("@BillToLastName", DbType.AnsiString, row.IsBillToLastNameNull() ? (object)DBNull.Value : (object)row.BillToLastName));
_command.Parameters.Add(this.CreateParameter("@ShipToFirstName", DbType.AnsiString, row.IsShipToFirstNameNull() ? (object)DBNull.Value : (object)row.ShipToFirstName));
_command.Parameters.Add(this.CreateParameter("@ShipToLastName", DbType.AnsiString, row.IsShipToLastNameNull() ? (object)DBNull.Value : (object)row.ShipToLastName));
_command.Parameters.Add(this.CreateParameter("@AuthorizationNumber", DbType.Int32, row.IsAuthorizationNumberNull() ? (object)DBNull.Value : (object)row.AuthorizationNumber));
_command.Parameters.Add(this.CreateParameter("@Locale", DbType.AnsiString, row.IsLocaleNull() ? (object)DBNull.Value : (object)row.Locale));
this.OpenConnection();
_command.ExecuteNonQuery();
OnOrdersUpdated(new OrdersEventArgs(row, StatementType.Insert)) ;
updatedRowCount++;
break;
}
case DataRowState.Modified:
{
OnOrdersUpdating(new OrdersEventArgs(row, StatementType.Update)) ;
_command = this.GetCommand();
_command.CommandText = @"
UPDATE [Orders] SET
[UserId] = @UserId,
[OrderDate] = @OrderDate,
[ShipAddr1] = @ShipAddr1,
[ShipAddr2] = @ShipAddr2,
[ShipCity] = @ShipCity,
[ShipState] = @ShipState,
[ShipZip] = @ShipZip,
[ShipCountry] = @ShipCountry,
[BillAddr1] = @BillAddr1,
[BillAddr2] = @BillAddr2,
[BillCity] = @BillCity,
[BillState] = @BillState,
[BillZip] = @BillZip,
[BillCountry] = @BillCountry,
[Courier] = @Courier,
[TotalPrice] = @TotalPrice,
[BillToFirstName] = @BillToFirstName,
[BillToLastName] = @BillToLastName,
[ShipToFirstName] = @ShipToFirstName,
[ShipToLastName] = @ShipToLastName,
[AuthorizationNumber] = @AuthorizationNumber,
[Locale] = @Locale
WHERE
[OrderId] = @OrderId
";
_command.Parameters.Add(this.CreateParameter("@OrderId", DbType.Int32, row.IsOrderIdNull() ? (object)DBNull.Value : (object)row.OrderId));
_command.Parameters.Add(this.CreateParameter("@UserId", DbType.AnsiString, row.IsUserIdNull() ? (object)DBNull.Value : (object)row.UserId));
_command.Parameters.Add(this.CreateParameter("@OrderDate", DbType.DateTime, row.IsOrderDateNull() ? (object)DBNull.Value : (object)row.OrderDate));
_command.Parameters.Add(this.CreateParameter("@ShipAddr1", DbType.AnsiString, row.IsShipAddr1Null() ? (object)DBNull.Value : (object)row.ShipAddr1));
_command.Parameters.Add(this.CreateParameter("@ShipAddr2", DbType.AnsiString, row.IsShipAddr2Null() ? (object)DBNull.Value : (object)row.ShipAddr2));
_command.Parameters.Add(this.CreateParameter("@ShipCity", DbType.AnsiString, row.IsShipCityNull() ? (object)DBNull.Value : (object)row.ShipCity));
_command.Parameters.Add(this.CreateParameter("@ShipState", DbType.AnsiString, row.IsShipStateNull() ? (object)DBNull.Value : (object)row.ShipState));
_command.Parameters.Add(this.CreateParameter("@ShipZip", DbType.AnsiString, row.IsShipZipNull() ? (object)DBNull.Value : (object)row.ShipZip));
_command.Parameters.Add(this.CreateParameter("@ShipCountry", DbType.AnsiString, row.IsShipCountryNull() ? (object)DBNull.Value : (object)row.ShipCountry));
_command.Parameters.Add(this.CreateParameter("@BillAddr1", DbType.AnsiString, row.IsBillAddr1Null() ? (object)DBNull.Value : (object)row.BillAddr1));
_command.Parameters.Add(this.CreateParameter("@BillAddr2", DbType.AnsiString, row.IsBillAddr2Null() ? (object)DBNull.Value : (object)row.BillAddr2));
_command.Parameters.Add(this.CreateParameter("@BillCity", DbType.AnsiString, row.IsBillCityNull() ? (object)DBNull.Value : (object)row.BillCity));
_command.Parameters.Add(this.CreateParameter("@BillState", DbType.AnsiString, row.IsBillStateNull() ? (object)DBNull.Value : (object)row.BillState));
_command.Parameters.Add(this.CreateParameter("@BillZip", DbType.AnsiString, row.IsBillZipNull() ? (object)DBNull.Value : (object)row.BillZip));
_command.Parameters.Add(this.CreateParameter("@BillCountry", DbType.AnsiString, row.IsBillCountryNull() ? (object)DBNull.Value : (object)row.BillCountry));
_command.Parameters.Add(this.CreateParameter("@Courier", DbType.AnsiString, row.IsCourierNull() ? (object)DBNull.Value : (object)row.Courier));
_command.Parameters.Add(this.CreateParameter("@TotalPrice", DbType.Decimal, row.IsTotalPriceNull() ? (object)DBNull.Value : (object)row.TotalPrice));
_command.Parameters.Add(this.CreateParameter("@BillToFirstName", DbType.AnsiString, row.IsBillToFirstNameNull() ? (object)DBNull.Value : (object)row.BillToFirstName));
_command.Parameters.Add(this.CreateParameter("@BillToLastName", DbType.AnsiString, row.IsBillToLastNameNull() ? (object)DBNull.Value : (object)row.BillToLastName));
_command.Parameters.Add(this.CreateParameter("@ShipToFirstName", DbType.AnsiString, row.IsShipToFirstNameNull() ? (object)DBNull.Value : (object)row.ShipToFirstName));
_command.Parameters.Add(this.CreateParameter("@ShipToLastName", DbType.AnsiString, row.IsShipToLastNameNull() ? (object)DBNull.Value : (object)row.ShipToLastName));
_command.Parameters.Add(this.CreateParameter("@AuthorizationNumber", DbType.Int32, row.IsAuthorizationNumberNull() ? (object)DBNull.Value : (object)row.AuthorizationNumber));
_command.Parameters.Add(this.CreateParameter("@Locale", DbType.AnsiString, row.IsLocaleNull() ? (object)DBNull.Value : (object)row.Locale));
this.OpenConnection();
_command.ExecuteNonQuery();
OnOrdersUpdated(new OrdersEventArgs(row, StatementType.Update)) ;
updatedRowCount++;
break;
}
case DataRowState.Deleted:
{
OnOrdersUpdating(new OrdersEventArgs(row, StatementType.Delete)) ;
_command = this.GetCommand();
_command.CommandText = @"
DELETE FROM [Orders]
WHERE
[OrderId] = @OrderId
";
_command.Parameters.Add(this.CreateParameter("@OrderId", DbType.Int32, row[dataSet.Orders.OrderIdColumn, DataRowVersion.Original]));
this.OpenConnection();
_command.ExecuteNonQuery();
OnOrdersUpdated(new OrdersEventArgs(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 FillByOrderId(OrdersDataSet dataSet,
int orderId
)
{
try
{
int recordcount = 0;
_command = this.GetCommand();
_command.CommandText = @"
SELECT
[OrderId]
FROM
[Orders]
WHERE
[OrderId] = @OrderId
";
_command.Parameters.Add(this.CreateParameter("@OrderId", DbType.Int32, orderId));
this.OpenConnection();
_reader = _command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);
while (_reader.Read())
{
OrdersDataSet.OrdersRow row = dataSet.Orders.NewOrdersRow();
this.PopulateOrdersDataRow(_reader, row);
dataSet.Orders.AddOrdersRow(row);
recordcount++;
}
dataSet.AcceptChanges();
return recordcount;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
return 0;
}
finally
{
this.Cleanup();
}
}
public int Fill(OrdersDataSet dataSet, string[] columns, string[] values, DbType[] types)
{
try
{
int recordcount = 0;
_command = this.GetCommand();
_command.CommandText = @"
SELECT
[OrderId],
[UserId],
[OrderDate],
[ShipAddr1],
[ShipAddr2],
[ShipCity],
[ShipState],
[ShipZip],
[ShipCountry],
[BillAddr1],
[BillAddr2],
[BillCity],
[BillState],
[BillZip],
[BillCountry],
[Courier],
[TotalPrice],
[BillToFirstName],
[BillToLastName],
[ShipToFirstName],
[ShipToLastName],
[AuthorizationNumber],
[Locale]
FROM
[Orders]
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())
{
OrdersDataSet.OrdersRow row = dataSet.Orders.NewOrdersRow();
this.PopulateOrdersDataRow(_reader, row);
dataSet.Orders.AddOrdersRow(row);
recordcount++;
}
dataSet.AcceptChanges();
return recordcount;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
return 0;
}
finally
{
this.Cleanup();
}
}
public int Fill(OrdersDataSet dataSet)
{
try
{
int recordcount = 0;
_command = this.GetCommand();
_command.CommandText = @"
SELECT
[OrderId],
[UserId],
[OrderDate],
[ShipAddr1],
[ShipAddr2],
[ShipCity],
[ShipState],
[ShipZip],
[ShipCountry],
[BillAddr1],
[BillAddr2],
[BillCity],
[BillState],
[BillZip],
[BillCountry],
[Courier],
[TotalPrice],
[BillToFirstName],
[BillToLastName],
[ShipToFirstName],
[ShipToLastName],
[AuthorizationNumber],
[Locale]
FROM
[Orders]";
this.OpenConnection();
_reader = _command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);
while (_reader.Read())
{
OrdersDataSet.OrdersRow row = dataSet.Orders.NewOrdersRow();
this.PopulateOrdersDataRow(_reader, row);
dataSet.Orders.AddOrdersRow(row);
recordcount++;
}
dataSet.AcceptChanges();
return recordcount;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
return 0;
}
finally
{
this.Cleanup();
}
}
public int Fill(OrdersDataSet dataSet, int orderId)
{
try
{
_command = this.GetCommand();
_command.CommandText = @"
SELECT
[OrderId],
[UserId],
[OrderDate],
[ShipAddr1],
[ShipAddr2],
[ShipCity],
[ShipState],
[ShipZip],
[ShipCountry],
[BillAddr1],
[BillAddr2],
[BillCity],
[BillState],
[BillZip],
[BillCountry],
[Courier],
[TotalPrice],
[BillToFirstName],
[BillToLastName],
[ShipToFirstName],
[ShipToLastName],
[AuthorizationNumber],
[Locale]
FROM
[Orders]
WHERE
[OrderId] = @OrderId
";
_command.Parameters.Add(this.CreateParameter("@OrderId", DbType.Int32, orderId));
this.OpenConnection();
_reader = _command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult | CommandBehavior.SingleRow);
if (_reader.Read())
{
OrdersDataSet.OrdersRow row = dataSet.Orders.NewOrdersRow();
this.PopulateOrdersDataRow(_reader, row);
dataSet.Orders.AddOrdersRow(row);
dataSet.AcceptChanges();
return 1;
}
else
{
throw new OrdersNotFoundException();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
return 0;
}
finally
{
this.Cleanup();
}
}