/// <summary> /// Inserts a PetShop.Business.Product object into the datasource using a transaction. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">PetShop.Business.Product object to insert.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.Product 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, PetShop.Business.Product entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Product_Insert", _useStoredProcedure); database.AddInParameter(commandWrapper, "@ProductId", DbType.AnsiString, entity.ProductId ); database.AddInParameter(commandWrapper, "@CategoryId", DbType.AnsiString, entity.CategoryId ); database.AddInParameter(commandWrapper, "@Name", DbType.AnsiString, entity.Name ); database.AddInParameter(commandWrapper, "@Descn", DbType.AnsiString, entity.Descn ); database.AddInParameter(commandWrapper, "@Image", DbType.AnsiString, entity.Image ); 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.OriginalProductId = entity.ProductId; entity.AcceptChanges(); //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Insert", entity)); return Convert.ToBoolean(results); }
}//end getall #endregion #region GetPaged Methods /// <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 PetShop.Business.Orders objects.</returns> public override TList<Orders> 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.Orders_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<Orders> rows = new TList<Orders>(); 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 PetShop.Business.Orders 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<PetShop.Business.Orders> 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 = "Orders"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("OrderId", typeof(int)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("UserId", typeof(string)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("OrderDate", typeof(System.DateTime)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("ShipAddr1", typeof(string)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("ShipAddr2", typeof(string)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("ShipCity", typeof(string)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("ShipState", typeof(string)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("ShipZip", typeof(string)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("ShipCountry", typeof(string)); col8.AllowDBNull = false; DataColumn col9 = dataTable.Columns.Add("BillAddr1", typeof(string)); col9.AllowDBNull = false; DataColumn col10 = dataTable.Columns.Add("BillAddr2", typeof(string)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("BillCity", typeof(string)); col11.AllowDBNull = false; DataColumn col12 = dataTable.Columns.Add("BillState", typeof(string)); col12.AllowDBNull = false; DataColumn col13 = dataTable.Columns.Add("BillZip", typeof(string)); col13.AllowDBNull = false; DataColumn col14 = dataTable.Columns.Add("BillCountry", typeof(string)); col14.AllowDBNull = false; DataColumn col15 = dataTable.Columns.Add("Courier", typeof(string)); col15.AllowDBNull = false; DataColumn col16 = dataTable.Columns.Add("TotalPrice", typeof(decimal)); col16.AllowDBNull = false; DataColumn col17 = dataTable.Columns.Add("BillToFirstName", typeof(string)); col17.AllowDBNull = false; DataColumn col18 = dataTable.Columns.Add("BillToLastName", typeof(string)); col18.AllowDBNull = false; DataColumn col19 = dataTable.Columns.Add("ShipToFirstName", typeof(string)); col19.AllowDBNull = false; DataColumn col20 = dataTable.Columns.Add("ShipToLastName", typeof(string)); col20.AllowDBNull = false; DataColumn col21 = dataTable.Columns.Add("AuthorizationNumber", typeof(int)); col21.AllowDBNull = false; DataColumn col22 = dataTable.Columns.Add("Locale", typeof(string)); col22.AllowDBNull = false; bulkCopy.ColumnMappings.Add("OrderId", "OrderId"); bulkCopy.ColumnMappings.Add("UserId", "UserId"); bulkCopy.ColumnMappings.Add("OrderDate", "OrderDate"); bulkCopy.ColumnMappings.Add("ShipAddr1", "ShipAddr1"); bulkCopy.ColumnMappings.Add("ShipAddr2", "ShipAddr2"); bulkCopy.ColumnMappings.Add("ShipCity", "ShipCity"); bulkCopy.ColumnMappings.Add("ShipState", "ShipState"); bulkCopy.ColumnMappings.Add("ShipZip", "ShipZip"); bulkCopy.ColumnMappings.Add("ShipCountry", "ShipCountry"); bulkCopy.ColumnMappings.Add("BillAddr1", "BillAddr1"); bulkCopy.ColumnMappings.Add("BillAddr2", "BillAddr2"); bulkCopy.ColumnMappings.Add("BillCity", "BillCity"); bulkCopy.ColumnMappings.Add("BillState", "BillState"); bulkCopy.ColumnMappings.Add("BillZip", "BillZip"); bulkCopy.ColumnMappings.Add("BillCountry", "BillCountry"); bulkCopy.ColumnMappings.Add("Courier", "Courier"); bulkCopy.ColumnMappings.Add("TotalPrice", "TotalPrice"); bulkCopy.ColumnMappings.Add("BillToFirstName", "BillToFirstName"); bulkCopy.ColumnMappings.Add("BillToLastName", "BillToLastName"); bulkCopy.ColumnMappings.Add("ShipToFirstName", "ShipToFirstName"); bulkCopy.ColumnMappings.Add("ShipToLastName", "ShipToLastName"); bulkCopy.ColumnMappings.Add("AuthorizationNumber", "AuthorizationNumber"); bulkCopy.ColumnMappings.Add("Locale", "Locale"); foreach(PetShop.Business.Orders entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["OrderId"] = entity.OrderId; row["UserId"] = entity.UserId; row["OrderDate"] = entity.OrderDate; row["ShipAddr1"] = entity.ShipAddr1; row["ShipAddr2"] = entity.ShipAddr2; row["ShipCity"] = entity.ShipCity; row["ShipState"] = entity.ShipState; row["ShipZip"] = entity.ShipZip; row["ShipCountry"] = entity.ShipCountry; row["BillAddr1"] = entity.BillAddr1; row["BillAddr2"] = entity.BillAddr2; row["BillCity"] = entity.BillCity; row["BillState"] = entity.BillState; row["BillZip"] = entity.BillZip; row["BillCountry"] = entity.BillCountry; row["Courier"] = entity.Courier; row["TotalPrice"] = entity.TotalPrice; row["BillToFirstName"] = entity.BillToFirstName; row["BillToLastName"] = entity.BillToLastName; row["ShipToFirstName"] = entity.ShipToFirstName; row["ShipToLastName"] = entity.ShipToLastName; row["AuthorizationNumber"] = entity.AuthorizationNumber; row["Locale"] = entity.Locale; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(PetShop.Business.Orders entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Deletes a row from the DataSource. /// </summary> /// <param name="_orderId">. 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, int _orderId) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Orders_Delete", _useStoredProcedure); database.AddInParameter(commandWrapper, "@OrderId", DbType.Int32, _orderId); //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(Orders) ,_orderId); EntityManager.StopTracking(entityKey); } //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Delete")); commandWrapper = null; return Convert.ToBoolean(results); }//end Delete
/// <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 PetShop.Business.Orders objects.</returns> public override TList<Orders> 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.Orders_Find_Dynamic", typeof(OrdersColumn), 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<Orders> rows = new TList<Orders>(); 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; }
}//end Delete #endregion #region Find Functions #region Parsed Find Methods /// <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 PetShop.Business.Profiles objects.</returns> public override TList<Profiles> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count) { count = -1; if (whereClause.IndexOf(";") > -1) return new TList<Profiles>(); SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Profiles_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, "@UniqueId", DbType.Int32, DBNull.Value); database.AddInParameter(commandWrapper, "@Username", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@ApplicationName", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@IsAnonymous", DbType.Boolean, DBNull.Value); database.AddInParameter(commandWrapper, "@LastActivityDate", DbType.DateTime, DBNull.Value); database.AddInParameter(commandWrapper, "@LastUpdatedDate", DbType.DateTime, 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("uniqueid ") || clause.Trim().StartsWith("uniqueid=")) { database.SetParameterValue(commandWrapper, "@UniqueId", clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("username ") || clause.Trim().StartsWith("username="******"@Username", clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("applicationname ") || clause.Trim().StartsWith("applicationname=")) { database.SetParameterValue(commandWrapper, "@ApplicationName", clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("isanonymous ") || clause.Trim().StartsWith("isanonymous=")) { database.SetParameterValue(commandWrapper, "@IsAnonymous", clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("lastactivitydate ") || clause.Trim().StartsWith("lastactivitydate=")) { database.SetParameterValue(commandWrapper, "@LastActivityDate", clause.Trim().Remove(0,16).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("lastupdateddate ") || clause.Trim().StartsWith("lastupdateddate=")) { database.SetParameterValue(commandWrapper, "@LastUpdatedDate", clause.Trim().Remove(0,15).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<Profiles> rows = new TList<Profiles>(); 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> /// Inserts a PetShop.Business.Profiles object into the datasource using a transaction. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">PetShop.Business.Profiles object to insert.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.Profiles 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, PetShop.Business.Profiles entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Profiles_Insert", _useStoredProcedure); database.AddOutParameter(commandWrapper, "@UniqueId", DbType.Int32, 4); database.AddInParameter(commandWrapper, "@Username", DbType.AnsiString, entity.Username ); database.AddInParameter(commandWrapper, "@ApplicationName", DbType.AnsiString, entity.ApplicationName ); database.AddInParameter(commandWrapper, "@IsAnonymous", DbType.Boolean, (entity.IsAnonymous.HasValue ? (object) entity.IsAnonymous : System.DBNull.Value)); database.AddInParameter(commandWrapper, "@LastActivityDate", DbType.DateTime, (entity.LastActivityDate.HasValue ? (object) entity.LastActivityDate : System.DBNull.Value)); database.AddInParameter(commandWrapper, "@LastUpdatedDate", DbType.DateTime, (entity.LastUpdatedDate.HasValue ? (object) entity.LastUpdatedDate : System.DBNull.Value)); 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 _uniqueId = database.GetParameterValue(commandWrapper, "@UniqueId"); entity.UniqueId = (System.Int32)_uniqueId; entity.AcceptChanges(); //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Insert", 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 PetShop.Business.Supplier 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<PetShop.Business.Supplier> 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 = "Supplier"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("SuppId", typeof(int)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Name", typeof(string)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("Status", typeof(string)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Addr1", typeof(string)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("Addr2", typeof(string)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("City", typeof(string)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("State", typeof(string)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("Zip", typeof(string)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("Phone", typeof(string)); col8.AllowDBNull = true; bulkCopy.ColumnMappings.Add("SuppId", "SuppId"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("Status", "Status"); bulkCopy.ColumnMappings.Add("Addr1", "Addr1"); bulkCopy.ColumnMappings.Add("Addr2", "Addr2"); bulkCopy.ColumnMappings.Add("City", "City"); bulkCopy.ColumnMappings.Add("State", "State"); bulkCopy.ColumnMappings.Add("Zip", "Zip"); bulkCopy.ColumnMappings.Add("Phone", "Phone"); foreach(PetShop.Business.Supplier entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["SuppId"] = entity.SuppId; row["Name"] = entity.Name; row["Status"] = entity.Status; row["Addr1"] = entity.Addr1; row["Addr2"] = entity.Addr2; row["City"] = entity.City; row["State"] = entity.State; row["Zip"] = entity.Zip; row["Phone"] = entity.Phone; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(PetShop.Business.Supplier entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Inserts a PetShop.Business.Supplier object into the datasource using a transaction. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">PetShop.Business.Supplier object to insert.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.Supplier 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, PetShop.Business.Supplier entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Supplier_Insert", _useStoredProcedure); database.AddInParameter(commandWrapper, "@SuppId", DbType.Int32, entity.SuppId ); database.AddInParameter(commandWrapper, "@Name", DbType.AnsiString, entity.Name ); database.AddInParameter(commandWrapper, "@Status", DbType.AnsiString, entity.Status ); database.AddInParameter(commandWrapper, "@Addr1", DbType.AnsiString, entity.Addr1 ); database.AddInParameter(commandWrapper, "@Addr2", DbType.AnsiString, entity.Addr2 ); database.AddInParameter(commandWrapper, "@City", DbType.AnsiString, entity.City ); database.AddInParameter(commandWrapper, "@State", DbType.AnsiString, entity.State ); database.AddInParameter(commandWrapper, "@Zip", DbType.AnsiString, entity.Zip ); database.AddInParameter(commandWrapper, "@Phone", DbType.AnsiString, entity.Phone ); 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.OriginalSuppId = entity.SuppId; entity.AcceptChanges(); //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Insert", 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 PetShop.Business.LineItem 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<PetShop.Business.LineItem> 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 = "LineItem"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("OrderId", typeof(int)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("LineNum", typeof(int)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("ItemId", typeof(string)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Quantity", typeof(int)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("UnitPrice", typeof(decimal)); col4.AllowDBNull = false; bulkCopy.ColumnMappings.Add("OrderId", "OrderId"); bulkCopy.ColumnMappings.Add("LineNum", "LineNum"); bulkCopy.ColumnMappings.Add("ItemId", "ItemId"); bulkCopy.ColumnMappings.Add("Quantity", "Quantity"); bulkCopy.ColumnMappings.Add("UnitPrice", "UnitPrice"); foreach(PetShop.Business.LineItem entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["OrderId"] = entity.OrderId; row["LineNum"] = entity.LineNum; row["ItemId"] = entity.ItemId; row["Quantity"] = entity.Quantity; row["UnitPrice"] = entity.UnitPrice; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(PetShop.Business.LineItem entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Inserts a PetShop.Business.OrderStatus object into the datasource using a transaction. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">PetShop.Business.OrderStatus object to insert.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.OrderStatus 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, PetShop.Business.OrderStatus entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.OrderStatus_Insert", _useStoredProcedure); database.AddInParameter(commandWrapper, "@OrderId", DbType.Int32, entity.OrderId ); database.AddInParameter(commandWrapper, "@LineNum", DbType.Int32, entity.LineNum ); database.AddInParameter(commandWrapper, "@Timestamp", DbType.DateTime, entity.Timestamp ); database.AddInParameter(commandWrapper, "@Status", DbType.AnsiString, entity.Status ); 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.OriginalOrderId = entity.OrderId; entity.OriginalLineNum = entity.LineNum; 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 FK__LineItem__OrderI__03317E3D key. /// FK__LineItem__OrderI__03317E3D Description: /// </summary> /// <param name="start">Row number at which to start reading.</param> /// <param name="pageLength">Number of rows to return.</param> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="_orderId"></param> /// <param name="count">out parameter to get total records for query</param> /// <remarks></remarks> /// <returns>Returns a typed collection of PetShop.Business.LineItem 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<LineItem> GetByOrderId(TransactionManager transactionManager, int _orderId, int start, int pageLength, out int count) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.LineItem_GetByOrderId", _useStoredProcedure); database.AddInParameter(commandWrapper, "@OrderId", DbType.Int32, _orderId); IDataReader reader = null; TList<LineItem> rows = new TList<LineItem>(); try { //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "GetByOrderId", rows)); if (transactionManager != null) { reader = Utility.ExecuteReader(transactionManager, commandWrapper); } else { reader = Utility.ExecuteReader(database, commandWrapper); } //Create Collection 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, "GetByOrderId", 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">PetShop.Business.Account object to update.</param> /// <remarks> /// After updating the datasource, the PetShop.Business.Account 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, PetShop.Business.Account entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Account_Update", _useStoredProcedure); database.AddInParameter(commandWrapper, "@AccountId", DbType.Int32, entity.AccountId ); database.AddInParameter(commandWrapper, "@UniqueId", DbType.Int32, entity.UniqueId ); database.AddInParameter(commandWrapper, "@Email", DbType.AnsiString, entity.Email ); database.AddInParameter(commandWrapper, "@FirstName", DbType.AnsiString, entity.FirstName ); database.AddInParameter(commandWrapper, "@LastName", DbType.AnsiString, entity.LastName ); database.AddInParameter(commandWrapper, "@Address1", DbType.AnsiString, entity.Address1 ); database.AddInParameter(commandWrapper, "@Address2", DbType.AnsiString, entity.Address2 ); database.AddInParameter(commandWrapper, "@City", DbType.AnsiString, entity.City ); database.AddInParameter(commandWrapper, "@State", DbType.AnsiString, entity.State ); database.AddInParameter(commandWrapper, "@Zip", DbType.AnsiString, entity.Zip ); database.AddInParameter(commandWrapper, "@Country", DbType.AnsiString, entity.Country ); database.AddInParameter(commandWrapper, "@Phone", DbType.AnsiString, entity.Phone ); 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> /// Inserts a PetShop.Business.Account object into the datasource using a transaction. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">PetShop.Business.Account object to insert.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.Account 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, PetShop.Business.Account entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Account_Insert", _useStoredProcedure); database.AddOutParameter(commandWrapper, "@AccountId", DbType.Int32, 4); database.AddInParameter(commandWrapper, "@UniqueId", DbType.Int32, entity.UniqueId ); database.AddInParameter(commandWrapper, "@Email", DbType.AnsiString, entity.Email ); database.AddInParameter(commandWrapper, "@FirstName", DbType.AnsiString, entity.FirstName ); database.AddInParameter(commandWrapper, "@LastName", DbType.AnsiString, entity.LastName ); database.AddInParameter(commandWrapper, "@Address1", DbType.AnsiString, entity.Address1 ); database.AddInParameter(commandWrapper, "@Address2", DbType.AnsiString, entity.Address2 ); database.AddInParameter(commandWrapper, "@City", DbType.AnsiString, entity.City ); database.AddInParameter(commandWrapper, "@State", DbType.AnsiString, entity.State ); database.AddInParameter(commandWrapper, "@Zip", DbType.AnsiString, entity.Zip ); database.AddInParameter(commandWrapper, "@Country", DbType.AnsiString, entity.Country ); database.AddInParameter(commandWrapper, "@Phone", DbType.AnsiString, entity.Phone ); 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 _accountId = database.GetParameterValue(commandWrapper, "@AccountId"); entity.AccountId = (int)_accountId; entity.AcceptChanges(); //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Insert", 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 PetShop.Business.Category 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<PetShop.Business.Category> 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 = "Category"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("CategoryId", typeof(string)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Name", typeof(string)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("Descn", typeof(string)); col2.AllowDBNull = true; bulkCopy.ColumnMappings.Add("CategoryId", "CategoryId"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("Descn", "Descn"); foreach(PetShop.Business.Category entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["CategoryId"] = entity.CategoryId; row["Name"] = entity.Name; row["Descn"] = entity.Descn; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(PetShop.Business.Category entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Inserts a PetShop.Business.Order object into the datasource using a transaction. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">PetShop.Business.Order object to insert.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.Order 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, PetShop.Business.Order entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Orders_Insert", _useStoredProcedure); database.AddOutParameter(commandWrapper, "@OrderId", DbType.Int32, 4); database.AddInParameter(commandWrapper, "@UserId", DbType.AnsiString, entity.UserId ); database.AddInParameter(commandWrapper, "@OrderDate", DbType.DateTime, entity.OrderDate ); database.AddInParameter(commandWrapper, "@ShipAddr1", DbType.AnsiString, entity.ShipAddr1 ); database.AddInParameter(commandWrapper, "@ShipAddr2", DbType.AnsiString, entity.ShipAddr2 ); database.AddInParameter(commandWrapper, "@ShipCity", DbType.AnsiString, entity.ShipCity ); database.AddInParameter(commandWrapper, "@ShipState", DbType.AnsiString, entity.ShipState ); database.AddInParameter(commandWrapper, "@ShipZip", DbType.AnsiString, entity.ShipZip ); database.AddInParameter(commandWrapper, "@ShipCountry", DbType.AnsiString, entity.ShipCountry ); database.AddInParameter(commandWrapper, "@BillAddr1", DbType.AnsiString, entity.BillAddr1 ); database.AddInParameter(commandWrapper, "@BillAddr2", DbType.AnsiString, entity.BillAddr2 ); database.AddInParameter(commandWrapper, "@BillCity", DbType.AnsiString, entity.BillCity ); database.AddInParameter(commandWrapper, "@BillState", DbType.AnsiString, entity.BillState ); database.AddInParameter(commandWrapper, "@BillZip", DbType.AnsiString, entity.BillZip ); database.AddInParameter(commandWrapper, "@BillCountry", DbType.AnsiString, entity.BillCountry ); database.AddInParameter(commandWrapper, "@Courier", DbType.AnsiString, entity.Courier ); database.AddInParameter(commandWrapper, "@TotalPrice", DbType.Decimal, entity.TotalPrice ); database.AddInParameter(commandWrapper, "@BillToFirstName", DbType.AnsiString, entity.BillToFirstName ); database.AddInParameter(commandWrapper, "@BillToLastName", DbType.AnsiString, entity.BillToLastName ); database.AddInParameter(commandWrapper, "@ShipToFirstName", DbType.AnsiString, entity.ShipToFirstName ); database.AddInParameter(commandWrapper, "@ShipToLastName", DbType.AnsiString, entity.ShipToLastName ); database.AddInParameter(commandWrapper, "@AuthorizationNumber", DbType.Int32, entity.AuthorizationNumber ); database.AddInParameter(commandWrapper, "@Locale", DbType.AnsiString, entity.Locale ); 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 _orderId = database.GetParameterValue(commandWrapper, "@OrderId"); entity.OrderId = (int)_orderId; entity.AcceptChanges(); //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Insert", 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">PetShop.Business.Category object to update.</param> /// <remarks> /// After updating the datasource, the PetShop.Business.Category 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, PetShop.Business.Category entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Category_Update", _useStoredProcedure); database.AddInParameter(commandWrapper, "@CategoryId", DbType.AnsiString, entity.CategoryId ); database.AddInParameter(commandWrapper, "@OriginalCategoryId", DbType.AnsiString, entity.OriginalCategoryId); database.AddInParameter(commandWrapper, "@Name", DbType.AnsiString, entity.Name ); database.AddInParameter(commandWrapper, "@Descn", DbType.AnsiString, entity.Descn ); 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.OriginalCategoryId = entity.CategoryId; entity.AcceptChanges(); //Provider Data Requested Command Event OnDataRequested(new CommandEventArgs(commandWrapper, "Update", entity)); return Convert.ToBoolean(results); }
/// <summary> /// Executes the non query. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="storedProcedureName">Name of the stored procedure.</param> /// <param name="parameterValues">The parameter values.</param> /// <returns></returns> public override int ExecuteNonQuery(TransactionManager transactionManager, string storedProcedureName, params object[] parameterValues) { SqlDatabase database = new SqlDatabase(this._connectionString); return database.ExecuteNonQuery(transactionManager.TransactionObject, storedProcedureName, parameterValues); }
/// <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 PetShop.Business.Profiles 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<PetShop.Business.Profiles> 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 = "Profiles"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("UniqueID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Username", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("ApplicationName", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("IsAnonymous", typeof(System.Boolean)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("LastActivityDate", typeof(System.DateTime)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("LastUpdatedDate", typeof(System.DateTime)); col5.AllowDBNull = true; bulkCopy.ColumnMappings.Add("UniqueID", "UniqueID"); bulkCopy.ColumnMappings.Add("Username", "Username"); bulkCopy.ColumnMappings.Add("ApplicationName", "ApplicationName"); bulkCopy.ColumnMappings.Add("IsAnonymous", "IsAnonymous"); bulkCopy.ColumnMappings.Add("LastActivityDate", "LastActivityDate"); bulkCopy.ColumnMappings.Add("LastUpdatedDate", "LastUpdatedDate"); foreach(PetShop.Business.Profiles entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["UniqueID"] = entity.UniqueId; row["Username"] = entity.Username; row["ApplicationName"] = entity.ApplicationName; row["IsAnonymous"] = entity.IsAnonymous.HasValue ? (object) entity.IsAnonymous : System.DBNull.Value; row["LastActivityDate"] = entity.LastActivityDate.HasValue ? (object) entity.LastActivityDate : System.DBNull.Value; row["LastUpdatedDate"] = entity.LastUpdatedDate.HasValue ? (object) entity.LastUpdatedDate : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(PetShop.Business.Profiles entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Executes the non query. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="commandWrapper">The command wrapper.</param> public override void ExecuteNonQuery(TransactionManager transactionManager, DbCommand commandWrapper) { SqlDatabase database = new SqlDatabase(this._connectionString); database.ExecuteNonQuery(commandWrapper, transactionManager.TransactionObject); }
/// <summary> /// Update an existing row in the datasource. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">PetShop.Business.Profiles object to update.</param> /// <remarks> /// After updating the datasource, the PetShop.Business.Profiles 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, PetShop.Business.Profiles entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Profiles_Update", _useStoredProcedure); database.AddInParameter(commandWrapper, "@UniqueId", DbType.Int32, entity.UniqueId ); database.AddInParameter(commandWrapper, "@Username", DbType.AnsiString, entity.Username ); database.AddInParameter(commandWrapper, "@ApplicationName", DbType.AnsiString, entity.ApplicationName ); database.AddInParameter(commandWrapper, "@IsAnonymous", DbType.Boolean, (entity.IsAnonymous.HasValue ? (object) entity.IsAnonymous : System.DBNull.Value) ); database.AddInParameter(commandWrapper, "@LastActivityDate", DbType.DateTime, (entity.LastActivityDate.HasValue ? (object) entity.LastActivityDate : System.DBNull.Value) ); database.AddInParameter(commandWrapper, "@LastUpdatedDate", DbType.DateTime, (entity.LastUpdatedDate.HasValue ? (object) entity.LastUpdatedDate : System.DBNull.Value) ); 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> /// Executes the non query. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="commandType">Type of the command.</param> /// <param name="commandText">The command text.</param> /// <returns></returns> public override int ExecuteNonQuery(TransactionManager transactionManager, CommandType commandType, string commandText) { Database database = transactionManager.Database; return database.ExecuteNonQuery(transactionManager.TransactionObject , commandType, commandText); }
}//end Delete #endregion #region Find Functions #region Parsed Find Methods /// <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 PetShop.Business.Orders objects.</returns> public override TList<Orders> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count) { count = -1; if (whereClause.IndexOf(";") > -1) return new TList<Orders>(); SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Orders_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, "@UserId", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@OrderDate", DbType.DateTime, DBNull.Value); database.AddInParameter(commandWrapper, "@ShipAddr1", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@ShipAddr2", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@ShipCity", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@ShipState", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@ShipZip", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@ShipCountry", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@BillAddr1", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@BillAddr2", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@BillCity", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@BillState", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@BillZip", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@BillCountry", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@Courier", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@TotalPrice", DbType.Decimal, DBNull.Value); database.AddInParameter(commandWrapper, "@BillToFirstName", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@BillToLastName", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@ShipToFirstName", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@ShipToLastName", DbType.AnsiString, DBNull.Value); database.AddInParameter(commandWrapper, "@AuthorizationNumber", DbType.Int32, DBNull.Value); database.AddInParameter(commandWrapper, "@Locale", DbType.AnsiString, 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("userid ") || clause.Trim().StartsWith("userid=")) { database.SetParameterValue(commandWrapper, "@UserId", clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("orderdate ") || clause.Trim().StartsWith("orderdate=")) { database.SetParameterValue(commandWrapper, "@OrderDate", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("shipaddr1 ") || clause.Trim().StartsWith("shipaddr1=")) { database.SetParameterValue(commandWrapper, "@ShipAddr1", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("shipaddr2 ") || clause.Trim().StartsWith("shipaddr2=")) { database.SetParameterValue(commandWrapper, "@ShipAddr2", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("shipcity ") || clause.Trim().StartsWith("shipcity=")) { database.SetParameterValue(commandWrapper, "@ShipCity", clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("shipstate ") || clause.Trim().StartsWith("shipstate=")) { database.SetParameterValue(commandWrapper, "@ShipState", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("shipzip ") || clause.Trim().StartsWith("shipzip=")) { database.SetParameterValue(commandWrapper, "@ShipZip", clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("shipcountry ") || clause.Trim().StartsWith("shipcountry=")) { database.SetParameterValue(commandWrapper, "@ShipCountry", clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("billaddr1 ") || clause.Trim().StartsWith("billaddr1=")) { database.SetParameterValue(commandWrapper, "@BillAddr1", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("billaddr2 ") || clause.Trim().StartsWith("billaddr2=")) { database.SetParameterValue(commandWrapper, "@BillAddr2", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("billcity ") || clause.Trim().StartsWith("billcity=")) { database.SetParameterValue(commandWrapper, "@BillCity", clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("billstate ") || clause.Trim().StartsWith("billstate=")) { database.SetParameterValue(commandWrapper, "@BillState", clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("billzip ") || clause.Trim().StartsWith("billzip=")) { database.SetParameterValue(commandWrapper, "@BillZip", clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("billcountry ") || clause.Trim().StartsWith("billcountry=")) { database.SetParameterValue(commandWrapper, "@BillCountry", clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("courier ") || clause.Trim().StartsWith("courier=")) { database.SetParameterValue(commandWrapper, "@Courier", clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("totalprice ") || clause.Trim().StartsWith("totalprice=")) { database.SetParameterValue(commandWrapper, "@TotalPrice", clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("billtofirstname ") || clause.Trim().StartsWith("billtofirstname=")) { database.SetParameterValue(commandWrapper, "@BillToFirstName", clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("billtolastname ") || clause.Trim().StartsWith("billtolastname=")) { database.SetParameterValue(commandWrapper, "@BillToLastName", clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("shiptofirstname ") || clause.Trim().StartsWith("shiptofirstname=")) { database.SetParameterValue(commandWrapper, "@ShipToFirstName", clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("shiptolastname ") || clause.Trim().StartsWith("shiptolastname=")) { database.SetParameterValue(commandWrapper, "@ShipToLastName", clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("authorizationnumber ") || clause.Trim().StartsWith("authorizationnumber=")) { database.SetParameterValue(commandWrapper, "@AuthorizationNumber", clause.Trim().Remove(0,19).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)); continue; } if (clause.Trim().StartsWith("locale ") || clause.Trim().StartsWith("locale=")) { database.SetParameterValue(commandWrapper, "@Locale", clause.Trim().Remove(0,6).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<Orders> rows = new TList<Orders>(); 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> /// Executes the scalar. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="storedProcedureName">Name of the stored procedure.</param> /// <param name="parameterValues">The parameter values.</param> /// <returns></returns> public override object ExecuteScalar(TransactionManager transactionManager, string storedProcedureName, params object[] parameterValues) { Database database = transactionManager.Database; return database.ExecuteScalar(transactionManager.TransactionObject, storedProcedureName, parameterValues); }
/// <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 PetShop.Business.Orders 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<Orders> GetAll(TransactionManager transactionManager, int start, int pageLength, out int count) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Orders_Get_List", _useStoredProcedure); IDataReader reader = null; //Create Collection TList<Orders> rows = new TList<Orders>(); 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; }//end getall
/// <summary> /// Executes the scalar. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="commandWrapper">The command wrapper.</param> /// <returns></returns> public override object ExecuteScalar(TransactionManager transactionManager, DbCommand commandWrapper) { Database database = transactionManager.Database; return database.ExecuteScalar(commandWrapper, transactionManager.TransactionObject); }
/// <summary> /// Gets rows from the datasource based on the PK__Orders__C3905BCF7F60ED59 index. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="_orderId"></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="PetShop.Business.Orders"/> 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 PetShop.Business.Orders GetByOrderId(TransactionManager transactionManager, int _orderId, int start, int pageLength, out int count) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Orders_GetByOrderId", _useStoredProcedure); database.AddInParameter(commandWrapper, "@OrderId", DbType.Int32, _orderId); IDataReader reader = null; TList<Orders> tmp = new TList<Orders>(); try { //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "GetByOrderId", 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, "GetByOrderId", 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> /// Executes the scalar. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="commandType">Type of the command.</param> /// <param name="commandText">The command text.</param> /// <returns></returns> public override object ExecuteScalar(TransactionManager transactionManager, CommandType commandType, string commandText) { Database database = transactionManager.Database; return database.ExecuteScalar(transactionManager.TransactionObject , commandType, commandText); }
/// <summary> /// Update an existing row in the datasource. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="entity">PetShop.Business.Orders object to update.</param> /// <remarks> /// After updating the datasource, the PetShop.Business.Orders 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, PetShop.Business.Orders entity) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Orders_Update", _useStoredProcedure); database.AddInParameter(commandWrapper, "@OrderId", DbType.Int32, entity.OrderId ); database.AddInParameter(commandWrapper, "@UserId", DbType.AnsiString, entity.UserId ); database.AddInParameter(commandWrapper, "@OrderDate", DbType.DateTime, entity.OrderDate ); database.AddInParameter(commandWrapper, "@ShipAddr1", DbType.AnsiString, entity.ShipAddr1 ); database.AddInParameter(commandWrapper, "@ShipAddr2", DbType.AnsiString, entity.ShipAddr2 ); database.AddInParameter(commandWrapper, "@ShipCity", DbType.AnsiString, entity.ShipCity ); database.AddInParameter(commandWrapper, "@ShipState", DbType.AnsiString, entity.ShipState ); database.AddInParameter(commandWrapper, "@ShipZip", DbType.AnsiString, entity.ShipZip ); database.AddInParameter(commandWrapper, "@ShipCountry", DbType.AnsiString, entity.ShipCountry ); database.AddInParameter(commandWrapper, "@BillAddr1", DbType.AnsiString, entity.BillAddr1 ); database.AddInParameter(commandWrapper, "@BillAddr2", DbType.AnsiString, entity.BillAddr2 ); database.AddInParameter(commandWrapper, "@BillCity", DbType.AnsiString, entity.BillCity ); database.AddInParameter(commandWrapper, "@BillState", DbType.AnsiString, entity.BillState ); database.AddInParameter(commandWrapper, "@BillZip", DbType.AnsiString, entity.BillZip ); database.AddInParameter(commandWrapper, "@BillCountry", DbType.AnsiString, entity.BillCountry ); database.AddInParameter(commandWrapper, "@Courier", DbType.AnsiString, entity.Courier ); database.AddInParameter(commandWrapper, "@TotalPrice", DbType.Decimal, entity.TotalPrice ); database.AddInParameter(commandWrapper, "@BillToFirstName", DbType.AnsiString, entity.BillToFirstName ); database.AddInParameter(commandWrapper, "@BillToLastName", DbType.AnsiString, entity.BillToLastName ); database.AddInParameter(commandWrapper, "@ShipToFirstName", DbType.AnsiString, entity.ShipToFirstName ); database.AddInParameter(commandWrapper, "@ShipToLastName", DbType.AnsiString, entity.ShipToLastName ); database.AddInParameter(commandWrapper, "@AuthorizationNumber", DbType.Int32, entity.AuthorizationNumber ); database.AddInParameter(commandWrapper, "@Locale", DbType.AnsiString, entity.Locale ); 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> /// Gets rows from the datasource based on the IxProduct4 index. /// </summary> /// <param name="transactionManager"><see cref="TransactionManager"/> object</param> /// <param name="_categoryId"></param> /// <param name="_productId"></param> /// <param name="_name"></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<Product>"/> 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<Product> GetByCategoryIdProductIdName(TransactionManager transactionManager, string _categoryId, string _productId, string _name, int start, int pageLength, out int count) { SqlDatabase database = new SqlDatabase(this._connectionString); DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Product_GetByCategoryIdProductIdName", _useStoredProcedure); database.AddInParameter(commandWrapper, "@CategoryId", DbType.AnsiString, _categoryId); database.AddInParameter(commandWrapper, "@ProductId", DbType.AnsiString, _productId); database.AddInParameter(commandWrapper, "@Name", DbType.AnsiString, _name); IDataReader reader = null; TList<Product> tmp = new TList<Product>(); try { //Provider Data Requesting Command Event OnDataRequesting(new CommandEventArgs(commandWrapper, "GetByCategoryIdProductIdName", 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, "GetByCategoryIdProductIdName", tmp)); } finally { if (reader != null) reader.Close(); commandWrapper = null; } return tmp; //return rows; }