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