///<summary> /// Update the Typed CustomerCustomerDemo Entity with modified mock values. ///</summary> public static void UpdateMockInstance(TransactionManager tm, CustomerCustomerDemo mock) { CustomerCustomerDemoTest.UpdateMockInstance_Generated(tm, mock); // make any alterations necessary // (i.e. for DB check constraints, special test cases, etc.) SetSpecialTestData(mock); }
///<summary> /// Returns a Typed CustomerCustomerDemo Entity with mock values. ///</summary> public static CustomerCustomerDemo CreateMockInstance(TransactionManager tm) { // get the default mock instance CustomerCustomerDemo mock = CustomerCustomerDemoTest.CreateMockInstance_Generated(tm); // make any alterations necessary // (i.e. for DB check constraints, special test cases, etc.) SetSpecialTestData(mock); // return the modified object return mock; }
///<summary> /// Update the Typed CustomerCustomerDemo Entity with modified mock values. ///</summary> public static void UpdateMockInstance_Generated(TransactionManager tm, CustomerCustomerDemo mock) { //OneToOneRelationship CustomerDemographics mockCustomerDemographicsByCustomerTypeId = CustomerDemographicsTest.CreateMockInstance(tm); DataRepository.CustomerDemographicsProvider.Insert(tm, mockCustomerDemographicsByCustomerTypeId); mock.CustomerTypeId = mockCustomerDemographicsByCustomerTypeId.CustomerTypeId; //OneToOneRelationship Customers mockCustomersByCustomerId = CustomersTest.CreateMockInstance(tm); DataRepository.CustomersProvider.Insert(tm, mockCustomersByCustomerId); mock.CustomerId = mockCustomersByCustomerId.CustomerId; }
///<summary> /// Returns a Typed Region Entity with mock values. ///</summary> public static Region CreateMockInstance_Generated(TransactionManager tm) { Region mock = new Region(); mock.RegionId = TestUtility.Instance.RandomNumber(); mock.RegionDescription = TestUtility.Instance.RandomString(24, false);; // create a temporary collection and add the item to it TList<Region> tempMockCollection = new TList<Region>(); tempMockCollection.Add(mock); tempMockCollection.Remove(mock); return (Region)mock; }
///<summary> /// Returns a Typed Categories Entity with mock values. ///</summary> public static Categories CreateMockInstance_Generated(TransactionManager tm) { Categories mock = new Categories(); mock.CategoryName = TestUtility.Instance.RandomString(6, false);; mock.Description = TestUtility.Instance.RandomString(2, false);; mock.Picture = new byte[] { TestUtility.Instance.RandomByte() }; // create a temporary collection and add the item to it TList<Categories> tempMockCollection = new TList<Categories>(); tempMockCollection.Add(mock); tempMockCollection.Remove(mock); return (Categories)mock; }
///<summary> /// Returns a Typed Orders Entity with mock values. ///</summary> public static Orders CreateMockInstance_Generated(TransactionManager tm) { Orders mock = new Orders(); mock.OrderDate = TestUtility.Instance.RandomDateTime(); mock.RequiredDate = TestUtility.Instance.RandomDateTime(); mock.ShippedDate = TestUtility.Instance.RandomDateTime(); mock.Freight = TestUtility.Instance.RandomShort(); mock.ShipName = TestUtility.Instance.RandomString(19, false);; mock.ShipAddress = TestUtility.Instance.RandomString(29, false);; mock.ShipCity = TestUtility.Instance.RandomString(6, false);; mock.ShipRegion = TestUtility.Instance.RandomString(6, false);; mock.ShipPostalCode = TestUtility.Instance.RandomString(10, false);; mock.ShipCountry = TestUtility.Instance.RandomString(6, false);; int count0 = 0; TList<Customers> _collection0 = DataRepository.CustomersProvider.GetPaged(tm, 0, 10, out count0); //_collection0.Shuffle(); if (_collection0.Count > 0) { mock.CustomerId = _collection0[0].CustomerId; } int count1 = 0; TList<Employees> _collection1 = DataRepository.EmployeesProvider.GetPaged(tm, 0, 10, out count1); //_collection1.Shuffle(); if (_collection1.Count > 0) { mock.EmployeeId = _collection1[0].EmployeeId; } int count2 = 0; TList<Shippers> _collection2 = DataRepository.ShippersProvider.GetPaged(tm, 0, 10, out count2); //_collection2.Shuffle(); if (_collection2.Count > 0) { mock.ShipVia = _collection2[0].ShipperId; } // create a temporary collection and add the item to it TList<Orders> tempMockCollection = new TList<Orders>(); tempMockCollection.Add(mock); tempMockCollection.Remove(mock); return (Orders)mock; }
///<summary> /// Returns a Typed CustomerCustomerDemo Entity with mock values. ///</summary> public static CustomerCustomerDemo CreateMockInstance_Generated(TransactionManager tm) { CustomerCustomerDemo mock = new CustomerCustomerDemo(); //OneToOneRelationship CustomerDemographics mockCustomerDemographicsByCustomerTypeId = CustomerDemographicsTest.CreateMockInstance(tm); DataRepository.CustomerDemographicsProvider.Insert(tm, mockCustomerDemographicsByCustomerTypeId); mock.CustomerTypeId = mockCustomerDemographicsByCustomerTypeId.CustomerTypeId; //OneToOneRelationship Customers mockCustomersByCustomerId = CustomersTest.CreateMockInstance(tm); DataRepository.CustomersProvider.Insert(tm, mockCustomersByCustomerId); mock.CustomerId = mockCustomersByCustomerId.CustomerId; // create a temporary collection and add the item to it TList<CustomerCustomerDemo> tempMockCollection = new TList<CustomerCustomerDemo>(); tempMockCollection.Add(mock); tempMockCollection.Remove(mock); return (CustomerCustomerDemo)mock; }
///<summary> /// Returns a Typed Suppliers Entity with mock values. ///</summary> public static Suppliers CreateMockInstance_Generated(TransactionManager tm) { Suppliers mock = new Suppliers(); mock.CompanyName = TestUtility.Instance.RandomString(19, false);; mock.ContactName = TestUtility.Instance.RandomString(14, false);; mock.ContactTitle = TestUtility.Instance.RandomString(14, false);; mock.Address = TestUtility.Instance.RandomString(29, false);; mock.City = TestUtility.Instance.RandomString(6, false);; mock.Region = TestUtility.Instance.RandomString(6, false);; mock.PostalCode = TestUtility.Instance.RandomString(10, false);; mock.Country = TestUtility.Instance.RandomString(6, false);; mock.Phone = TestUtility.Instance.RandomString(11, false);; mock.Fax = TestUtility.Instance.RandomString(11, false);; mock.HomePage = TestUtility.Instance.RandomString(2, false);; // create a temporary collection and add the item to it TList<Suppliers> tempMockCollection = new TList<Suppliers>(); tempMockCollection.Add(mock); tempMockCollection.Remove(mock); return (Suppliers)mock; }
///<summary> /// Returns a Typed Territories Entity with mock values. ///</summary> public static Territories CreateMockInstance_Generated(TransactionManager tm) { Territories mock = new Territories(); mock.TerritoryId = TestUtility.Instance.RandomString(9, false);; mock.TerritoryDescription = TestUtility.Instance.RandomString(24, false);; int count0 = 0; TList<Region> _collection0 = DataRepository.RegionProvider.GetPaged(tm, 0, 10, out count0); //_collection0.Shuffle(); if (_collection0.Count > 0) { mock.RegionId = _collection0[0].RegionId; } // create a temporary collection and add the item to it TList<Territories> tempMockCollection = new TList<Territories>(); tempMockCollection.Add(mock); tempMockCollection.Remove(mock); return (Territories)mock; }
///<summary> /// Returns a Typed Employees Entity with mock values. ///</summary> public static Employees CreateMockInstance_Generated(TransactionManager tm) { Employees mock = new Employees(); mock.LastName = TestUtility.Instance.RandomString(9, false);; mock.FirstName = TestUtility.Instance.RandomString(10, false);; mock.Title = TestUtility.Instance.RandomString(14, false);; mock.TitleOfCourtesy = TestUtility.Instance.RandomString(11, false);; mock.BirthDate = TestUtility.Instance.RandomDateTime(); mock.HireDate = TestUtility.Instance.RandomDateTime(); mock.Address = TestUtility.Instance.RandomString(29, false);; mock.City = TestUtility.Instance.RandomString(6, false);; mock.Region = TestUtility.Instance.RandomString(6, false);; mock.PostalCode = TestUtility.Instance.RandomString(10, false);; mock.Country = TestUtility.Instance.RandomString(6, false);; mock.HomePhone = TestUtility.Instance.RandomString(11, false);; mock.Extension = TestUtility.Instance.RandomString(4, false);; mock.Photo = new byte[] { TestUtility.Instance.RandomByte() }; mock.Notes = TestUtility.Instance.RandomString(2, false);; mock.PhotoPath = TestUtility.Instance.RandomString(126, false);; int count0 = 0; TList<Employees> _collection0 = DataRepository.EmployeesProvider.GetPaged(tm, 0, 10, out count0); //_collection0.Shuffle(); if (_collection0.Count > 0) { mock.ReportsTo = _collection0[0].EmployeeId; } // create a temporary collection and add the item to it TList<Employees> tempMockCollection = new TList<Employees>(); tempMockCollection.Add(mock); tempMockCollection.Remove(mock); return (Employees)mock; }
///<summary> /// Returns a Typed Products Entity with mock values. ///</summary> public static Products CreateMockInstance_Generated(TransactionManager tm) { Products mock = new Products(); mock.ProductName = TestUtility.Instance.RandomString(19, false);; mock.QuantityPerUnit = TestUtility.Instance.RandomString(9, false);; mock.UnitPrice = TestUtility.Instance.RandomShort(); mock.UnitsInStock = TestUtility.Instance.RandomShort(); mock.UnitsOnOrder = TestUtility.Instance.RandomShort(); mock.ReorderLevel = TestUtility.Instance.RandomShort(); mock.Discontinued = TestUtility.Instance.RandomBoolean(); int count0 = 0; TList<Categories> _collection0 = DataRepository.CategoriesProvider.GetPaged(tm, 0, 10, out count0); //_collection0.Shuffle(); if (_collection0.Count > 0) { mock.CategoryId = _collection0[0].CategoryId; } int count1 = 0; TList<Suppliers> _collection1 = DataRepository.SuppliersProvider.GetPaged(tm, 0, 10, out count1); //_collection1.Shuffle(); if (_collection1.Count > 0) { mock.SupplierId = _collection1[0].SupplierId; } // create a temporary collection and add the item to it TList<Products> tempMockCollection = new TList<Products>(); tempMockCollection.Add(mock); tempMockCollection.Remove(mock); return (Products)mock; }
///<summary> /// Returns a Typed OrderDetails Entity with mock values. ///</summary> public static OrderDetails CreateMockInstance_Generated(TransactionManager tm) { OrderDetails mock = new OrderDetails(); mock.UnitPrice = TestUtility.Instance.RandomShort(); mock.Quantity = TestUtility.Instance.RandomShort(); mock.Discount = (float)TestUtility.Instance.RandomNumber(); //OneToOneRelationship Orders mockOrdersByOrderId = OrdersTest.CreateMockInstance(tm); DataRepository.OrdersProvider.Insert(tm, mockOrdersByOrderId); mock.OrderId = mockOrdersByOrderId.OrderId; //OneToOneRelationship Products mockProductsByProductId = ProductsTest.CreateMockInstance(tm); DataRepository.ProductsProvider.Insert(tm, mockProductsByProductId); mock.ProductId = mockProductsByProductId.ProductId; // create a temporary collection and add the item to it TList<OrderDetails> tempMockCollection = new TList<OrderDetails>(); tempMockCollection.Add(mock); tempMockCollection.Remove(mock); return (OrderDetails)mock; }
/// <summary> /// Gets All rows from the DataSource. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="start">Row number at which to start reading.</param> /// <param name="pageLength">Number of rows to return.</param> /// <param name="count">out. The number of rows that match this query.</param> /// <remarks></remarks> /// <returns>Returns a typed collection of Northwind.Entities.OrderDetails objects.</returns> /// <exception cref="System.Exception">The command could not be executed.</exception> /// <exception cref="System.Data.DataException">The <paramref name="transactionManager"/> is not open.</exception> /// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception> public override TList<OrderDetails> GetAll(TransactionManager transactionManager, int start, int pageLength, out int count) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_OrderDetails_Get_List", _useStoredProcedure); IDataReader reader = null; //Create Collection TList<OrderDetails> rows = new TList<OrderDetails>(); try { //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "GetAll", rows)); if (transactionManager != null) { reader = Utility.ExecuteReader(transactionManager, commandWrapper); } else { reader = Utility.ExecuteReader(database, commandWrapper); } Fill(reader, rows, start, pageLength); count = -1; if(reader.NextResult()) { if(reader.Read()) { count = reader.GetInt32(0); } } //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "GetAll", rows)); } finally { if (reader != null) reader.Close(); commandWrapper = null; } return rows; }
/// <summary> /// Returns rows from the DataSource that meet the parameter conditions. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="parameters">A collection of <see cref="SqlFilterParameter"/> objects.</param> /// <param name="orderBy">Specifies the sort criteria for the rows in the DataSource (Name ASC; BirthDay DESC, Name ASC);</param> /// <param name="start">Row number at which to start reading.</param> /// <param name="pageLength">Number of rows to return.</param> /// <param name="count">out. The number of rows that match this query.</param> /// <returns>Returns a typed collection of Northwind.Entities.OrderDetails objects.</returns> public override TList<OrderDetails> Find(TransactionManager transactionManager, IFilterParameterCollection parameters, string orderBy, int start, int pageLength, out int count) { SqlFilterParameterCollection filter = null; if (parameters == null) filter = new SqlFilterParameterCollection(); else filter = parameters.GetParameters(); SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_OrderDetails_Find_Dynamic", typeof(OrderDetailsColumn), filter, orderBy, start, pageLength); SqlFilterParameter param; for ( int i = 0; i < filter.Count; i++ ) { param = filter[i]; database.AddInParameter(commandWrapper, param.Name, param.DbType, param.GetValue()); } TList<OrderDetails> rows = new TList<OrderDetails>(); IDataReader reader = null; try { //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows)); if ( transactionManager != null ) { reader = Utility.ExecuteReader(transactionManager, commandWrapper); } else { reader = Utility.ExecuteReader(database, commandWrapper); } Fill(reader, rows, 0, int.MaxValue); count = rows.Count; if ( reader.NextResult() ) { if ( reader.Read() ) { count = reader.GetInt32(0); } } //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows)); } finally { if ( reader != null ) reader.Close(); commandWrapper = null; } return rows; }
/// <summary> /// Returns rows meeting the whereClause condition from the DataSource. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param> /// <param name="start">Row number at which to start reading.</param> /// <param name="pageLength">Number of rows to return.</param> /// <param name="count">out. The number of rows that match this query.</param> /// <remarks>Operators must be capitalized (OR, AND).</remarks> /// <returns>Returns a typed collection of Northwind.Entities.OrderDetails objects.</returns> public override TList<OrderDetails> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count) { count = -1; if (whereClause.IndexOf(";") > -1) return new TList<OrderDetails>(); SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_OrderDetails_Find", _useStoredProcedure); bool searchUsingOR = false; if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."? searchUsingOR = true; database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR); database.AddInParameter(commandWrapper, "@OrderId", DbType.Int32, DBNull.Value); database.AddInParameter(commandWrapper, "@ProductId", DbType.Int32, DBNull.Value); database.AddInParameter(commandWrapper, "@UnitPrice", DbType.Currency, DBNull.Value); database.AddInParameter(commandWrapper, "@Quantity", DbType.Int16, DBNull.Value); database.AddInParameter(commandWrapper, "@Discount", DbType.Single, DBNull.Value); // replace all instances of 'AND' and 'OR' because we already set searchUsingOR whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ; string[] clauses = whereClause.ToLower().Split('|'); // Here's what's going on below: Find a field, then to get the value we // drop the field name from the front, trim spaces, drop the '=' sign, // trim more spaces, and drop any outer single quotes. // Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'" char[] equalSign = {'='}; char[] singleQuote = {'\''}; foreach (string clause in clauses) { if (clause.Trim().StartsWith("orderid ") || clause.Trim().StartsWith("orderid=")) { database.SetParameterValue(commandWrapper, "@OrderId", clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("productid ") || clause.Trim().StartsWith("productid=")) { database.SetParameterValue(commandWrapper, "@ProductId", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("unitprice ") || clause.Trim().StartsWith("unitprice=")) { database.SetParameterValue(commandWrapper, "@UnitPrice", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("quantity ") || clause.Trim().StartsWith("quantity=")) { database.SetParameterValue(commandWrapper, "@Quantity", clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("discount ") || clause.Trim().StartsWith("discount=")) { database.SetParameterValue(commandWrapper, "@Discount", clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause); } IDataReader reader = null; //Create Collection TList<OrderDetails> rows = new TList<OrderDetails>(); try { //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows)); if (transactionManager != null) { reader = Utility.ExecuteReader(transactionManager, commandWrapper); } else { reader = Utility.ExecuteReader(database, commandWrapper); } Fill(reader, rows, start, pageLength); if(reader.NextResult()) { if(reader.Read()) { count = reader.GetInt32(0); } } //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows)); } finally { if (reader != null) reader.Close(); commandWrapper = null; } return rows; }
/// <summary> /// Deletes a row from the DataSource. /// </summary> /// <param name="_orderId">. Primary Key.</param> /// <param name="_productId">. Primary Key.</param> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <remarks>Deletes based on primary key(s).</remarks> /// <returns>Returns true if operation suceeded.</returns> /// <exception cref="System.Exception">The command could not be executed.</exception> /// <exception cref="System.Data.DataException">The <paramref name="transactionManager"/> is not open.</exception> /// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception> public override bool Delete(TransactionManager transactionManager, System.Int32 _orderId, System.Int32 _productId) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_OrderDetails_Delete", _useStoredProcedure); database.AddInParameter(commandWrapper, "@OrderId", DbType.Int32, _orderId); database.AddInParameter(commandWrapper, "@ProductId", DbType.Int32, _productId); //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "Delete")); int results = 0; if (transactionManager != null) { results = Utility.ExecuteNonQuery(transactionManager, commandWrapper); } else { results = Utility.ExecuteNonQuery(database,commandWrapper); } //Stop Tracking Now that it has been updated and persisted. if (DataRepository.Provider.EnableEntityTracking) { string entityKey = EntityLocator.ConstructKeyFromPkItems(typeof(OrderDetails) ,_orderId,_productId); EntityManager.StopTracking(entityKey); } //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Delete")); commandWrapper = null; return Convert.ToBoolean(results); }
/// <summary> /// Gets a page of rows from the DataSource. /// </summary> /// <param name="start">Row number at which to start reading.</param> /// <param name="pageLength">Number of rows to return.</param> /// <param name="count">Number of rows in the DataSource.</param> /// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param> /// <param name="orderBy">Specifies the sort criteria for the rows in the DataSource (Name ASC; BirthDay DESC, Name ASC);</param> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <remarks></remarks> /// <returns>Returns a typed collection of Northwind.Entities.OrderDetails objects.</returns> public override TList<OrderDetails> GetPaged(TransactionManager transactionManager, string whereClause, string orderBy, int start, int pageLength, out int count) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_OrderDetails_GetPaged", _useStoredProcedure); if (commandWrapper.CommandType == CommandType.Text && commandWrapper.CommandText != null) { commandWrapper.CommandText = commandWrapper.CommandText.Replace(SqlUtil.PAGE_INDEX, string.Concat(SqlUtil.PAGE_INDEX, Guid.NewGuid().ToString("N").Substring(0, 8))); } database.AddInParameter(commandWrapper, "@WhereClause", DbType.String, whereClause); database.AddInParameter(commandWrapper, "@OrderBy", DbType.String, orderBy); database.AddInParameter(commandWrapper, "@PageIndex", DbType.Int32, start); database.AddInParameter(commandWrapper, "@PageSize", DbType.Int32, pageLength); IDataReader reader = null; //Create Collection TList<OrderDetails> rows = new TList<OrderDetails>(); try { //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "GetPaged", rows)); if (transactionManager != null) { reader = Utility.ExecuteReader(transactionManager, commandWrapper); } else { reader = Utility.ExecuteReader(database, commandWrapper); } Fill(reader, rows, 0, int.MaxValue); count = rows.Count; if(reader.NextResult()) { if(reader.Read()) { count = reader.GetInt32(0); } } //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "GetPaged", rows)); } catch(Exception) { throw; } finally { if (reader != null) reader.Close(); commandWrapper = null; } return rows; }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Northwind.Entities.Categories object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Northwind.Entities.Categories> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Categories"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("CategoryID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("CategoryName", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Description", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("Picture", typeof(System.Byte[])); col3.AllowDBNull = true; bulkCopy.ColumnMappings.Add("CategoryID", "CategoryID"); bulkCopy.ColumnMappings.Add("CategoryName", "CategoryName"); bulkCopy.ColumnMappings.Add("Description", "Description"); bulkCopy.ColumnMappings.Add("Picture", "Picture"); foreach(Northwind.Entities.Categories entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["CategoryID"] = entity.CategoryId; row["CategoryName"] = entity.CategoryName; row["Description"] = entity.Description; row["Picture"] = entity.Picture; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Northwind.Entities.Categories entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Gets Employees objects from the datasource by TerritoryID in the /// EmployeeTerritories table. Table Employees is related to table Territories /// through the (M:N) relationship defined in the EmployeeTerritories table. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="_territoryId"></param> /// <param name="start">Row number at which to start reading.</param> /// <param name="pageLength">Number of rows to return.</param> /// <param name="count">out parameter to get total records for query.</param> /// <returns>Returns a <c>TList</c> of Employees objects.</returns> public override TList<Employees> GetByTerritoryIdFromEmployeeTerritories(TransactionManager transactionManager, System.String _territoryId, int start, int pageLength, out int count) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_Employees_GetByTerritoryIdFromEmployeeTerritories", _useStoredProcedure); database.AddInParameter(commandWrapper, "@TerritoryId", DbType.String, _territoryId); IDataReader reader = null; // Create collection and fill TList<Employees> rows = new TList<Employees>(); try { // Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "GetByTerritoryIdFromEmployeeTerritories", rows)); if (transactionManager != null) { reader = Utility.ExecuteReader(transactionManager, commandWrapper); } else { reader = Utility.ExecuteReader(database, commandWrapper); } Fill(reader, rows, start, pageLength); count = -1; if(reader.NextResult()) { if(reader.Read()) { count = reader.GetInt32(0); } } // Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "GetByTerritoryIdFromEmployeeTerritories", rows)); } finally { if (reader != null) reader.Close(); commandWrapper = null; } return rows; }
/// <summary> /// Returns rows meeting the whereClause condition from the DataSource. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param> /// <param name="start">Row number at which to start reading.</param> /// <param name="pageLength">Number of rows to return.</param> /// <param name="count">out. The number of rows that match this query.</param> /// <remarks>Operators must be capitalized (OR, AND).</remarks> /// <returns>Returns a typed collection of Northwind.Entities.Employees objects.</returns> public override TList<Employees> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count) { count = -1; if (whereClause.IndexOf(";") > -1) return new TList<Employees>(); SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_Employees_Find", _useStoredProcedure); bool searchUsingOR = false; if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."? searchUsingOR = true; database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR); database.AddInParameter(commandWrapper, "@EmployeeId", DbType.Int32, DBNull.Value); database.AddInParameter(commandWrapper, "@LastName", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@FirstName", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@Title", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@TitleOfCourtesy", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@BirthDate", DbType.DateTime, DBNull.Value); database.AddInParameter(commandWrapper, "@HireDate", DbType.DateTime, DBNull.Value); database.AddInParameter(commandWrapper, "@Address", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@City", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@Region", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@PostalCode", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@Country", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@HomePhone", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@Extension", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@Photo", DbType.Binary, DBNull.Value); database.AddInParameter(commandWrapper, "@Notes", DbType.String, DBNull.Value); database.AddInParameter(commandWrapper, "@ReportsTo", DbType.Int32, DBNull.Value); database.AddInParameter(commandWrapper, "@PhotoPath", DbType.String, DBNull.Value); // replace all instances of 'AND' and 'OR' because we already set searchUsingOR whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ; string[] clauses = whereClause.ToLower().Split('|'); // Here's what's going on below: Find a field, then to get the value we // drop the field name from the front, trim spaces, drop the '=' sign, // trim more spaces, and drop any outer single quotes. // Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'" char[] equalSign = {'='}; char[] singleQuote = {'\''}; foreach (string clause in clauses) { if (clause.Trim().StartsWith("employeeid ") || clause.Trim().StartsWith("employeeid=")) { database.SetParameterValue(commandWrapper, "@EmployeeId", clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("lastname ") || clause.Trim().StartsWith("lastname=")) { database.SetParameterValue(commandWrapper, "@LastName", clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("firstname ") || clause.Trim().StartsWith("firstname=")) { database.SetParameterValue(commandWrapper, "@FirstName", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("title ") || clause.Trim().StartsWith("title=")) { database.SetParameterValue(commandWrapper, "@Title", clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("titleofcourtesy ") || clause.Trim().StartsWith("titleofcourtesy=")) { database.SetParameterValue(commandWrapper, "@TitleOfCourtesy", clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("birthdate ") || clause.Trim().StartsWith("birthdate=")) { database.SetParameterValue(commandWrapper, "@BirthDate", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("hiredate ") || clause.Trim().StartsWith("hiredate=")) { database.SetParameterValue(commandWrapper, "@HireDate", clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("address ") || clause.Trim().StartsWith("address=")) { database.SetParameterValue(commandWrapper, "@Address", clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("city ") || clause.Trim().StartsWith("city=")) { database.SetParameterValue(commandWrapper, "@City", clause.Trim().Remove(0,4).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("region ") || clause.Trim().StartsWith("region=")) { database.SetParameterValue(commandWrapper, "@Region", clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("postalcode ") || clause.Trim().StartsWith("postalcode=")) { database.SetParameterValue(commandWrapper, "@PostalCode", clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("country ") || clause.Trim().StartsWith("country=")) { database.SetParameterValue(commandWrapper, "@Country", clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("homephone ") || clause.Trim().StartsWith("homephone=")) { database.SetParameterValue(commandWrapper, "@HomePhone", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("extension ") || clause.Trim().StartsWith("extension=")) { database.SetParameterValue(commandWrapper, "@Extension", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("photo ") || clause.Trim().StartsWith("photo=")) { database.SetParameterValue(commandWrapper, "@Photo", clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("notes ") || clause.Trim().StartsWith("notes=")) { database.SetParameterValue(commandWrapper, "@Notes", clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("reportsto ") || clause.Trim().StartsWith("reportsto=")) { database.SetParameterValue(commandWrapper, "@ReportsTo", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("photopath ") || clause.Trim().StartsWith("photopath=")) { database.SetParameterValue(commandWrapper, "@PhotoPath", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause); } IDataReader reader = null; //Create Collection TList<Employees> rows = new TList<Employees>(); try { //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows)); if (transactionManager != null) { reader = Utility.ExecuteReader(transactionManager, commandWrapper); } else { reader = Utility.ExecuteReader(database, commandWrapper); } Fill(reader, rows, start, pageLength); if(reader.NextResult()) { if(reader.Read()) { count = reader.GetInt32(0); } } //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows)); } finally { if (reader != null) reader.Close(); commandWrapper = null; } return rows; }
/// <summary> /// Update an existing row in the datasource. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">Northwind.Entities.Employees object to update.</param> /// <remarks> /// After updating the datasource, the Northwind.Entities.Employees object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> /// <returns>Returns true if operation is successful.</returns> /// <exception cref="System.Exception">The command could not be executed.</exception> /// <exception cref="System.Data.DataException">The <paramref name="transactionManager"/> is not open.</exception> /// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception> public override bool Update(TransactionManager transactionManager, Northwind.Entities.Employees entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_Employees_Update", _useStoredProcedure); database.AddInParameter(commandWrapper, "@EmployeeId", DbType.Int32, entity.EmployeeId ); database.AddInParameter(commandWrapper, "@LastName", DbType.String, entity.LastName ); database.AddInParameter(commandWrapper, "@FirstName", DbType.String, entity.FirstName ); database.AddInParameter(commandWrapper, "@Title", DbType.String, entity.Title ); database.AddInParameter(commandWrapper, "@TitleOfCourtesy", DbType.String, entity.TitleOfCourtesy ); database.AddInParameter(commandWrapper, "@BirthDate", DbType.DateTime, (entity.BirthDate.HasValue ? (object) entity.BirthDate : System.DBNull.Value) ); database.AddInParameter(commandWrapper, "@HireDate", DbType.DateTime, (entity.HireDate.HasValue ? (object) entity.HireDate : System.DBNull.Value) ); database.AddInParameter(commandWrapper, "@Address", DbType.String, entity.Address ); database.AddInParameter(commandWrapper, "@City", DbType.String, entity.City ); database.AddInParameter(commandWrapper, "@Region", DbType.String, entity.Region ); database.AddInParameter(commandWrapper, "@PostalCode", DbType.String, entity.PostalCode ); database.AddInParameter(commandWrapper, "@Country", DbType.String, entity.Country ); database.AddInParameter(commandWrapper, "@HomePhone", DbType.String, entity.HomePhone ); database.AddInParameter(commandWrapper, "@Extension", DbType.String, entity.Extension ); database.AddInParameter(commandWrapper, "@Photo", DbType.Binary, entity.Photo ); database.AddInParameter(commandWrapper, "@Notes", DbType.String, entity.Notes ); database.AddInParameter(commandWrapper, "@ReportsTo", DbType.Int32, (entity.ReportsTo.HasValue ? (object) entity.ReportsTo : System.DBNull.Value) ); database.AddInParameter(commandWrapper, "@PhotoPath", DbType.String, entity.PhotoPath ); int results = 0; //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "Update", entity)); if (transactionManager != null) { results = Utility.ExecuteNonQuery(transactionManager, commandWrapper); } else { results = Utility.ExecuteNonQuery(database,commandWrapper); } //Stop Tracking Now that it has been updated and persisted. if (DataRepository.Provider.EnableEntityTracking) { EntityManager.StopTracking(entity.EntityTrackingKey); } entity.AcceptChanges(); //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Update", entity)); return Convert.ToBoolean(results); }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Northwind.Entities.Employees object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Northwind.Entities.Employees> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Employees"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("EmployeeID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("LastName", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("FirstName", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Title", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("TitleOfCourtesy", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("BirthDate", typeof(System.DateTime)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("HireDate", typeof(System.DateTime)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("Address", typeof(System.String)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("City", typeof(System.String)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("Region", typeof(System.String)); col9.AllowDBNull = true; DataColumn col10 = dataTable.Columns.Add("PostalCode", typeof(System.String)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("Country", typeof(System.String)); col11.AllowDBNull = true; DataColumn col12 = dataTable.Columns.Add("HomePhone", typeof(System.String)); col12.AllowDBNull = true; DataColumn col13 = dataTable.Columns.Add("Extension", typeof(System.String)); col13.AllowDBNull = true; DataColumn col14 = dataTable.Columns.Add("Photo", typeof(System.Byte[])); col14.AllowDBNull = true; DataColumn col15 = dataTable.Columns.Add("Notes", typeof(System.String)); col15.AllowDBNull = true; DataColumn col16 = dataTable.Columns.Add("ReportsTo", typeof(System.Int32)); col16.AllowDBNull = true; DataColumn col17 = dataTable.Columns.Add("PhotoPath", typeof(System.String)); col17.AllowDBNull = true; bulkCopy.ColumnMappings.Add("EmployeeID", "EmployeeID"); bulkCopy.ColumnMappings.Add("LastName", "LastName"); bulkCopy.ColumnMappings.Add("FirstName", "FirstName"); bulkCopy.ColumnMappings.Add("Title", "Title"); bulkCopy.ColumnMappings.Add("TitleOfCourtesy", "TitleOfCourtesy"); bulkCopy.ColumnMappings.Add("BirthDate", "BirthDate"); bulkCopy.ColumnMappings.Add("HireDate", "HireDate"); bulkCopy.ColumnMappings.Add("Address", "Address"); bulkCopy.ColumnMappings.Add("City", "City"); bulkCopy.ColumnMappings.Add("Region", "Region"); bulkCopy.ColumnMappings.Add("PostalCode", "PostalCode"); bulkCopy.ColumnMappings.Add("Country", "Country"); bulkCopy.ColumnMappings.Add("HomePhone", "HomePhone"); bulkCopy.ColumnMappings.Add("Extension", "Extension"); bulkCopy.ColumnMappings.Add("Photo", "Photo"); bulkCopy.ColumnMappings.Add("Notes", "Notes"); bulkCopy.ColumnMappings.Add("ReportsTo", "ReportsTo"); bulkCopy.ColumnMappings.Add("PhotoPath", "PhotoPath"); foreach(Northwind.Entities.Employees entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["EmployeeID"] = entity.EmployeeId; row["LastName"] = entity.LastName; row["FirstName"] = entity.FirstName; row["Title"] = entity.Title; row["TitleOfCourtesy"] = entity.TitleOfCourtesy; row["BirthDate"] = entity.BirthDate.HasValue ? (object) entity.BirthDate : System.DBNull.Value; row["HireDate"] = entity.HireDate.HasValue ? (object) entity.HireDate : System.DBNull.Value; row["Address"] = entity.Address; row["City"] = entity.City; row["Region"] = entity.Region; row["PostalCode"] = entity.PostalCode; row["Country"] = entity.Country; row["HomePhone"] = entity.HomePhone; row["Extension"] = entity.Extension; row["Photo"] = entity.Photo; row["Notes"] = entity.Notes; row["ReportsTo"] = entity.ReportsTo.HasValue ? (object) entity.ReportsTo : System.DBNull.Value; row["PhotoPath"] = entity.PhotoPath; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Northwind.Entities.Employees entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Gets rows from the datasource based on the PK_Order_Details index. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="_orderId"></param> /// <param name="_productId"></param> /// <param name="start">Row number at which to start reading.</param> /// <param name="pageLength">Number of rows to return.</param> /// <param name="count">out parameter to get total records for query.</param> /// <returns>Returns an instance of the <see cref="Northwind.Entities.OrderDetails"/> class.</returns> /// <remarks></remarks> /// <exception cref="System.Exception">The command could not be executed.</exception> /// <exception cref="System.Data.DataException">The <paramref name="transactionManager"/> is not open.</exception> /// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception> public override Northwind.Entities.OrderDetails GetByOrderIdProductId(TransactionManager transactionManager, System.Int32 _orderId, System.Int32 _productId, int start, int pageLength, out int count) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_OrderDetails_GetByOrderIdProductId", _useStoredProcedure); database.AddInParameter(commandWrapper, "@OrderId", DbType.Int32, _orderId); database.AddInParameter(commandWrapper, "@ProductId", DbType.Int32, _productId); IDataReader reader = null; TList<OrderDetails> tmp = new TList<OrderDetails>(); try { //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "GetByOrderIdProductId", tmp)); if (transactionManager != null) { reader = Utility.ExecuteReader(transactionManager, commandWrapper); } else { reader = Utility.ExecuteReader(database, commandWrapper); } //Create collection and fill Fill(reader, tmp, start, pageLength); count = -1; if(reader.NextResult()) { if(reader.Read()) { count = reader.GetInt32(0); } } //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "GetByOrderIdProductId", tmp)); } finally { if (reader != null) reader.Close(); commandWrapper = null; } if (tmp.Count == 1) { return tmp[0]; } else if (tmp.Count == 0) { return null; } else { throw new DataException("Cannot find the unique instance of the class."); } //return rows; }
/// <summary> /// Inserts a Northwind.Entities.Categories object into the datasource using a transaction. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">Northwind.Entities.Categories object to insert.</param> /// <remarks> /// After inserting into the datasource, the Northwind.Entities.Categories object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> /// <returns>Returns true if operation is successful.</returns> /// <exception cref="System.Exception">The command could not be executed.</exception> /// <exception cref="System.Data.DataException">The <paramref name="transactionManager"/> is not open.</exception> /// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception> public override bool Insert(TransactionManager transactionManager, Northwind.Entities.Categories entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_Categories_Insert", _useStoredProcedure); database.AddOutParameter(commandWrapper, "@CategoryId", DbType.Int32, 4); database.AddInParameter(commandWrapper, "@CategoryName", DbType.String, entity.CategoryName ); database.AddInParameter(commandWrapper, "@Description", DbType.String, entity.Description ); database.AddInParameter(commandWrapper, "@Picture", DbType.Binary, entity.Picture ); int results = 0; //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "Insert", entity)); if (transactionManager != null) { results = Utility.ExecuteNonQuery(transactionManager, commandWrapper); } else { results = Utility.ExecuteNonQuery(database,commandWrapper); } object _categoryId = database.GetParameterValue(commandWrapper, "@CategoryId"); entity.CategoryId = (System.Int32)_categoryId; entity.AcceptChanges(); //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Insert", entity)); return Convert.ToBoolean(results); }
/// <summary> /// Gets rows from the datasource based on the ProductID index. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="_productId"></param> /// <param name="start">Row number at which to start reading.</param> /// <param name="pageLength">Number of rows to return.</param> /// <param name="count">out parameter to get total records for query.</param> /// <returns>Returns an instance of the <see cref="TList<OrderDetails>"/> class.</returns> /// <remarks></remarks> /// <exception cref="System.Exception">The command could not be executed.</exception> /// <exception cref="System.Data.DataException">The <paramref name="transactionManager"/> is not open.</exception> /// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception> public override TList<OrderDetails> GetByProductId(TransactionManager transactionManager, System.Int32 _productId, int start, int pageLength, out int count) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_OrderDetails_GetByProductId", _useStoredProcedure); database.AddInParameter(commandWrapper, "@ProductId", DbType.Int32, _productId); IDataReader reader = null; TList<OrderDetails> tmp = new TList<OrderDetails>(); try { //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "GetByProductId", tmp)); if (transactionManager != null) { reader = Utility.ExecuteReader(transactionManager, commandWrapper); } else { reader = Utility.ExecuteReader(database, commandWrapper); } //Create collection and fill Fill(reader, tmp, start, pageLength); count = -1; if(reader.NextResult()) { if(reader.Read()) { count = reader.GetInt32(0); } } //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "GetByProductId", tmp)); } finally { if (reader != null) reader.Close(); commandWrapper = null; } return tmp; //return rows; }
/// <summary> /// Update an existing row in the datasource. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">Northwind.Entities.Categories object to update.</param> /// <remarks> /// After updating the datasource, the Northwind.Entities.Categories object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> /// <returns>Returns true if operation is successful.</returns> /// <exception cref="System.Exception">The command could not be executed.</exception> /// <exception cref="System.Data.DataException">The <paramref name="transactionManager"/> is not open.</exception> /// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception> public override bool Update(TransactionManager transactionManager, Northwind.Entities.Categories entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_Categories_Update", _useStoredProcedure); database.AddInParameter(commandWrapper, "@CategoryId", DbType.Int32, entity.CategoryId ); database.AddInParameter(commandWrapper, "@CategoryName", DbType.String, entity.CategoryName ); database.AddInParameter(commandWrapper, "@Description", DbType.String, entity.Description ); database.AddInParameter(commandWrapper, "@Picture", DbType.Binary, entity.Picture ); int results = 0; //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "Update", entity)); if (transactionManager != null) { results = Utility.ExecuteNonQuery(transactionManager, commandWrapper); } else { results = Utility.ExecuteNonQuery(database,commandWrapper); } //Stop Tracking Now that it has been updated and persisted. if (DataRepository.Provider.EnableEntityTracking) { EntityManager.StopTracking(entity.EntityTrackingKey); } entity.AcceptChanges(); //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Update", entity)); return Convert.ToBoolean(results); }
/// <summary> /// Update an existing row in the datasource. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">Northwind.Entities.OrderDetails object to update.</param> /// <remarks> /// After updating the datasource, the Northwind.Entities.OrderDetails object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> /// <returns>Returns true if operation is successful.</returns> /// <exception cref="System.Exception">The command could not be executed.</exception> /// <exception cref="System.Data.DataException">The <paramref name="transactionManager"/> is not open.</exception> /// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception> public override bool Update(TransactionManager transactionManager, Northwind.Entities.OrderDetails entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_OrderDetails_Update", _useStoredProcedure); database.AddInParameter(commandWrapper, "@OrderId", DbType.Int32, entity.OrderId ); database.AddInParameter(commandWrapper, "@OriginalOrderId", DbType.Int32, entity.OriginalOrderId); database.AddInParameter(commandWrapper, "@ProductId", DbType.Int32, entity.ProductId ); database.AddInParameter(commandWrapper, "@OriginalProductId", DbType.Int32, entity.OriginalProductId); database.AddInParameter(commandWrapper, "@UnitPrice", DbType.Currency, entity.UnitPrice ); database.AddInParameter(commandWrapper, "@Quantity", DbType.Int16, entity.Quantity ); database.AddInParameter(commandWrapper, "@Discount", DbType.Single, entity.Discount ); int results = 0; //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "Update", entity)); if (transactionManager != null) { results = Utility.ExecuteNonQuery(transactionManager, commandWrapper); } else { results = Utility.ExecuteNonQuery(database,commandWrapper); } //Stop Tracking Now that it has been updated and persisted. if (DataRepository.Provider.EnableEntityTracking) { EntityManager.StopTracking(entity.EntityTrackingKey); } entity.OriginalOrderId = entity.OrderId; entity.OriginalProductId = entity.ProductId; entity.AcceptChanges(); //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Update", entity)); return Convert.ToBoolean(results); }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Northwind.Entities.OrderDetails object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Northwind.Entities.OrderDetails> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Order Details"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("OrderID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("ProductID", typeof(System.Int32)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("UnitPrice", typeof(System.Decimal)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Quantity", typeof(System.Int16)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("Discount", typeof(System.Single)); col4.AllowDBNull = false; bulkCopy.ColumnMappings.Add("OrderID", "OrderID"); bulkCopy.ColumnMappings.Add("ProductID", "ProductID"); bulkCopy.ColumnMappings.Add("UnitPrice", "UnitPrice"); bulkCopy.ColumnMappings.Add("Quantity", "Quantity"); bulkCopy.ColumnMappings.Add("Discount", "Discount"); foreach(Northwind.Entities.OrderDetails entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["OrderID"] = entity.OrderId; row["ProductID"] = entity.ProductId; row["UnitPrice"] = entity.UnitPrice; row["Quantity"] = entity.Quantity; row["Discount"] = entity.Discount; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Northwind.Entities.OrderDetails entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Inserts a Northwind.Entities.Region object into the datasource using a transaction. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">Northwind.Entities.Region object to insert.</param> /// <remarks> /// After inserting into the datasource, the Northwind.Entities.Region object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> /// <returns>Returns true if operation is successful.</returns> /// <exception cref="System.Exception">The command could not be executed.</exception> /// <exception cref="System.Data.DataException">The <paramref name="transactionManager"/> is not open.</exception> /// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception> public override bool Insert(TransactionManager transactionManager, Northwind.Entities.Region entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_Region_Insert", _useStoredProcedure); database.AddInParameter(commandWrapper, "@RegionId", DbType.Int32, entity.RegionId ); database.AddInParameter(commandWrapper, "@RegionDescription", DbType.StringFixedLength, entity.RegionDescription ); int results = 0; //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "Insert", entity)); if (transactionManager != null) { results = Utility.ExecuteNonQuery(transactionManager, commandWrapper); } else { results = Utility.ExecuteNonQuery(database,commandWrapper); } entity.OriginalRegionId = entity.RegionId; entity.AcceptChanges(); //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Insert", entity)); return Convert.ToBoolean(results); }
///<summary> /// Update the Typed Products Entity with modified mock values. ///</summary> public static void UpdateMockInstance_Generated(TransactionManager tm, Products mock) { mock.ProductName = TestUtility.Instance.RandomString(19, false);; mock.QuantityPerUnit = TestUtility.Instance.RandomString(9, false);; mock.UnitPrice = TestUtility.Instance.RandomShort(); mock.UnitsInStock = TestUtility.Instance.RandomShort(); mock.UnitsOnOrder = TestUtility.Instance.RandomShort(); mock.ReorderLevel = TestUtility.Instance.RandomShort(); mock.Discontinued = TestUtility.Instance.RandomBoolean(); int count0 = 0; TList<Categories> _collection0 = DataRepository.CategoriesProvider.GetPaged(tm, 0, 10, out count0); //_collection0.Shuffle(); if (_collection0.Count > 0) { mock.CategoryId = _collection0[0].CategoryId; } int count1 = 0; TList<Suppliers> _collection1 = DataRepository.SuppliersProvider.GetPaged(tm, 0, 10, out count1); //_collection1.Shuffle(); if (_collection1.Count > 0) { mock.SupplierId = _collection1[0].SupplierId; } }