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");
}
}
private void PopulateOrdersDataRow(IDataReader reader, OrdersDataSet.OrdersRow row)
{
if (!reader.IsDBNull(0)) row.OrderId = reader.GetInt32(0);
if (!reader.IsDBNull(1)) row.UserId = reader.GetString(1);
if (!reader.IsDBNull(2)) row.OrderDate = reader.GetDateTime(2);
if (!reader.IsDBNull(3)) row.ShipAddr1 = reader.GetString(3);
if (!reader.IsDBNull(4)) row.ShipAddr2 = reader.GetString(4);
if (!reader.IsDBNull(5)) row.ShipCity = reader.GetString(5);
if (!reader.IsDBNull(6)) row.ShipState = reader.GetString(6);
if (!reader.IsDBNull(7)) row.ShipZip = reader.GetString(7);
if (!reader.IsDBNull(8)) row.ShipCountry = reader.GetString(8);
if (!reader.IsDBNull(9)) row.BillAddr1 = reader.GetString(9);
if (!reader.IsDBNull(10)) row.BillAddr2 = reader.GetString(10);
if (!reader.IsDBNull(11)) row.BillCity = reader.GetString(11);
if (!reader.IsDBNull(12)) row.BillState = reader.GetString(12);
if (!reader.IsDBNull(13)) row.BillZip = reader.GetString(13);
if (!reader.IsDBNull(14)) row.BillCountry = reader.GetString(14);
if (!reader.IsDBNull(15)) row.Courier = reader.GetString(15);
if (!reader.IsDBNull(16)) row.TotalPrice = reader.GetDecimal(16);
if (!reader.IsDBNull(17)) row.BillToFirstName = reader.GetString(17);
if (!reader.IsDBNull(18)) row.BillToLastName = reader.GetString(18);
if (!reader.IsDBNull(19)) row.ShipToFirstName = reader.GetString(19);
if (!reader.IsDBNull(20)) row.ShipToLastName = reader.GetString(20);
if (!reader.IsDBNull(21)) row.AuthorizationNumber = reader.GetInt32(21);
if (!reader.IsDBNull(22)) row.Locale = reader.GetString(22);
}
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 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, 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();
}
}
public int Fill(OrdersDataSet dataSet, DataRow dataRecord)
{
return Fill(dataSet, ((int)(dataRecord["OrderId"])));
}
public OrdersEventArgs(OrdersDataSet.OrdersRow 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();
}
