public static DataTable GetSubProductByParent(int parentID) { MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand(); DataTable dt = null; try { conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandText = "Select p.ProductDescription, p.ProductCode, p.ProductCategory, p.ManufacturerName, pp.SubProductID from [aquaOne].[dbo].[Product] p, [aquaOne].[dbo].[ProductPartReplacement] pp where p.ProductID=pp.SubProductID and pp.ProductID=@ParentID "; cmd.Parameters.Add("@ParentID", SqlDbType.Int).Value = parentID; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt = new DataTable(); dt.Load(dr); cmd.Dispose(); dr.Close(); } finally { myConn.CloseDB(conn); } return(dt); }
public static int GetAddressID(int addressType, int id) { int addressID = 0; AccountAddress accountAddress = new AccountAddress(); MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr; SqlCommand cmd = null; string sql = "Select addressid from AquaOne.dbo.AccountAddress where AccountID = @AccountID and AddressType = @AddressType"; // Open the connection conn = myConn.OpenDB(); cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@AccountID", SqlDbType.Int).Value = id; cmd.Parameters.Add("@AddressType", SqlDbType.Int).Value = addressType; dr = cmd.ExecuteReader(); if (dr.Read()) { addressID = dr.GetInt32(0); // accountAddress = FillDataRecord(dr); } cmd.Dispose(); //close the connection myConn.CloseDB(conn); return(addressID); }
public static DataTable GetInvoiceByID(int invoiceID) { MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand(); DataTable dt = new DataTable(); try { conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.[GetInvoiceByID]"; cmd.Parameters.Add("@InvoiceID", SqlDbType.Int).Value = invoiceID; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt.Load(dr); } finally { cmd.Dispose(); dr.Close(); myConn.CloseDB(conn); } return(dt); }
public static int DeactivateEmployee(Employee employee) { int result; MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); try { conn = myConn.OpenDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.TerminateEmployee"; cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = employee.EmployeeID; cmd.Parameters.Add("@IsEmployed", SqlDbType.Bit).Value = employee.IsEmployed; cmd.Parameters.Add("@TerminatedDate", SqlDbType.DateTime).Value = employee.TerminatedDate; cmd.Parameters.Add("@ModifiedBy", SqlDbType.VarChar).Value = employee.ModifiedBy; cmd.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = employee.ModifiedDate; DbParameter returnValue = cmd.CreateParameter(); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue); cmd.ExecuteNonQuery(); result = Convert.ToInt32(returnValue.Value); } finally { myConn.CloseDB(conn); } return(result); }
public static AuthorizedMemberList GetListByAccountID(int accountID) { AuthorizedMemberList aMemberList = null; MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr; SqlCommand cmd = null; string sql = "Select * from AquaOne.dbo.AuthorizedMember where AccountID = @AccountID"; // Open the connection conn = myConn.OpenDB(); cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@AccountID", SqlDbType.Int).Value = accountID; dr = cmd.ExecuteReader(); if (dr.HasRows) { aMemberList = new AuthorizedMemberList(); while (dr.Read()) { aMemberList.Add(FillDataRecord(dr)); } } cmd.Dispose(); myConn.CloseDB(conn); return(aMemberList); }
public static EmployeeList GetList() { EmployeeList eList = null; MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr; SqlCommand cmd = null; string sql = "Select * from AquaOne.dbo.Employee"; try { // Open the connection conn = myConn.OpenDB(); cmd = new SqlCommand(sql, conn); dr = cmd.ExecuteReader(); if (dr.HasRows) { eList = new EmployeeList(); while (dr.Read()) { eList.Add(FillDataRecord(dr)); } } } finally { cmd.Dispose(); myConn.CloseDB(conn); } return(eList); }
public static DataTable GetActiveEmployeeList() { MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand(); DataTable dt = new DataTable(); try { conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.GetActiveEmployeeList"; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt.Load(dr); } finally { cmd.Dispose(); myConn.CloseDB(conn); } return(dt); }
public static int Delete(int stg_servID) { int result; MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); try { conn = myConn.OpenDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.[DeleteSTG_ServiceWork]"; cmd.Parameters.Add("@STG_servID", SqlDbType.Int).Value = stg_servID; DbParameter returnValue = cmd.CreateParameter(); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue); cmd.ExecuteNonQuery(); result = Convert.ToInt32(returnValue.Value); cmd.Dispose(); } finally { myConn.CloseDB(conn); } return(result); }
public static WaterTreatmentEquipment GetByWTEID(int wte_id) { WaterTreatmentEquipment wte = new WaterTreatmentEquipment(); MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr; SqlCommand cmd = null; string sql = "Select * from AquaOne.dbo.WaterTreatmentEquipment where wte_id = @wte_id"; try { // Open the connection conn = myConn.OpenDB(); cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@wte_id", SqlDbType.Int).Value = wte_id; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (dr.Read()) { wte = FillDataRecord(dr); } dr.Close(); cmd.Dispose(); } finally { //close the connection myConn.CloseDB(conn); } return(wte); }
public static DataTable GetLowStock(string sortExpression, string sortDirection) { MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand(); DataTable dt = null; try { conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.GetProductsBelowReorderLevelWithFilter"; cmd.Parameters.Add("@sortExpression", SqlDbType.VarChar).Value = sortExpression; cmd.Parameters.Add("@sortDirection", SqlDbType.VarChar).Value = sortDirection; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt = new DataTable(); dt.Load(dr); cmd.Dispose(); dr.Close(); } finally { myConn.CloseDB(conn); } return(dt); }
public static int UpdateUnitsOnHand(InvoiceLineItem lineItem, string operation) { int result; MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); try { conn = myConn.OpenDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.[UpdateProductUnitsOnHand]"; cmd.Parameters.Add("@updateQty", SqlDbType.Int).Value = lineItem.Quantity; cmd.Parameters.Add("@productID", SqlDbType.Int).Value = lineItem.ProductID; cmd.Parameters.Add("@operationtype", SqlDbType.VarChar).Value = operation; cmd.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = lineItem.CreatedDate; cmd.Parameters.Add("@ModifiedBy", SqlDbType.VarChar).Value = lineItem.CreatedBy; DbParameter returnValue = cmd.CreateParameter(); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue); cmd.ExecuteNonQuery(); result = Convert.ToInt32(returnValue.Value); cmd.Dispose(); } finally { myConn.CloseDB(conn); } return(result); }
public static ProductList GetTop5ProductsByCategory(string productCategory) { ProductList aList = null; MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand(); try { conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandText = "Select top 5 * from Product where ProductCategory = @ProductCategory order by newID();"; cmd.Parameters.Add("@ProductCategory", SqlDbType.VarChar).Value = productCategory; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (dr.HasRows) { aList = new ProductList(); while (dr.Read()) { aList.Add(FillDataRecord(dr)); } } cmd.Dispose(); dr.Close(); } finally { myConn.CloseDB(conn); } return(aList); }
public static int DeleteSubPart(ProductPartReplacement subProduct) { int result; MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); try { conn = myConn.OpenDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.DeleteSubPart"; cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = subProduct.ProductID; cmd.Parameters.Add("@SubProductID", SqlDbType.Int).Value = subProduct.SubProductID; DbParameter returnValue = cmd.CreateParameter(); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue); cmd.ExecuteNonQuery(); result = Convert.ToInt32(returnValue.Value); } finally { myConn.CloseDB(conn); } return(result); }
public static int Reactivate(Product product) { int result; MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); try { conn = myConn.OpenDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.ReactivateProduct"; cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = product.ProductID; cmd.Parameters.Add("@IsDiscontinued", SqlDbType.Bit).Value = product.IsDiscontinued; cmd.Parameters.Add("@ModifiedBy", SqlDbType.VarChar).Value = product.ModifiedBy; cmd.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = product.ModifiedDate; DbParameter returnValue = cmd.CreateParameter(); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue); cmd.ExecuteNonQuery(); result = Convert.ToInt32(returnValue.Value); } finally { myConn.CloseDB(conn); } return(result); }
public static Account GetAccountByID(int id) { Account account = new Account(); MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr; SqlCommand cmd = null; string sql = "Select * from AquaOne.dbo.Account where AccountID = @AccountID"; try { // Open the connection conn = myConn.OpenDB(); cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@AccountID", SqlDbType.Int).Value = id; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (dr.Read()) { account = FillDataRecord(dr); } dr.Close(); cmd.Dispose(); } finally { //close the connection myConn.CloseDB(conn); } return(account); }
public static int DeleteWTEByID(int wteID) { int result; MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); try { conn = myConn.OpenDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "Delete from Aquaone.dbo.WaterTreatmentEquipment where wte_id=@wteID"; cmd.Parameters.Add("@wteID", SqlDbType.Int).Value = wteID; DbParameter returnValue = cmd.CreateParameter(); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue); cmd.ExecuteNonQuery(); result = Convert.ToInt32(returnValue.Value); } finally { myConn.CloseDB(conn); } return(result); }
public static Employee GetEmployeeByID(int id) { Employee employee = new Employee(); MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr; SqlCommand cmd = null; try { string sql = "Select * from AquaOne.dbo.Employee where EmployeeID = @EmployeeID"; // Open the connection conn = myConn.OpenDB(); cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = id; dr = cmd.ExecuteReader(); if (dr.Read()) { employee = FillDataRecord(dr); } } finally { cmd.Dispose(); //close the connection myConn.CloseDB(conn); } return(employee); }
public static DataTable GetTopTenServiceWorkFromToday() { DataTable dt = new DataTable(); MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr; SqlCommand cmd = null; try { // Open the connection conn = myConn.OpenDB(); cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.[GetTopTenServiceWorkFromToday]"; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt.Load(dr); cmd.Dispose(); dr.Close(); } finally { myConn.CloseDB(conn); } return(dt); }
public static List <string> GetTechnicians() { List <string> list = new List <string>(); MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr; SqlCommand cmd = new SqlCommand(); try { // Open the connection conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.getTechnicians"; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (dr.HasRows) { while (dr.Read()) { list.Add(dr.GetString(0)); } } } finally { cmd.Dispose(); myConn.CloseDB(conn); } return(list); }
public static DataTable GetServiceWorkDetailsByID(int serviceWorkID) { DataTable dt = new DataTable(); MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr; SqlCommand cmd = null; try { // Open the connection conn = myConn.OpenDB(); cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.[GetServiceWorkDetailsByID]"; cmd.Parameters.Add("@serviceWorkID", SqlDbType.Int).Value = serviceWorkID; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt.Load(dr); cmd.Dispose(); dr.Close(); } finally { myConn.CloseDB(conn); } return(dt); }
public static DataTable GetEmployeeWithAddress() { MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand(); DataTable dt = new DataTable(); try { conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.GetEmployeesWithAddress"; //cmd.Parameters.Add("@SearchCriteria", SqlDbType.VarChar).Value = searchBy; //cmd.Parameters.Add("@Input", SqlDbType.VarChar).Value = searchString; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt.Load(dr); } finally { cmd.Dispose(); myConn.CloseDB(conn); } return(dt); }
public static DataTable GetUsagePerHour() { MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand(); DataTable dt = null; try { conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.GetWaterUsagePerHourCurrentDay"; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt = new DataTable(); dt.Load(dr); cmd.Dispose(); dr.Close(); } finally { myConn.CloseDB(conn); } return(dt); }
public static int GetBottleTypeByName(string name) { int bottleType = 0; MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr; SqlCommand cmd = null; string sql = "Select typeid from AquaOne.dbo.PrepaidBottleType where name = @name"; try { // Open the connection conn = myConn.OpenDB(); cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = name; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (dr.Read()) { bottleType = dr.GetInt32(0); } dr.Close(); cmd.Dispose(); } finally { //close the connection myConn.CloseDB(conn); } return(bottleType); }
//[GetAutocompleteSearchListForAccount] public static List <string> GetAutoCompleteList(string searchBy, string searchString) { List <string> searchList = new List <string>(); MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand(); try { conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.GetAutocompleteSearchListForAccount"; cmd.Parameters.Add("@SearchCriteria", SqlDbType.VarChar).Value = searchBy; cmd.Parameters.Add("@input", SqlDbType.VarChar).Value = searchString; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (dr.HasRows) { while (dr.Read()) { searchList.Add(dr.GetValue(0).ToString()); } } cmd.Dispose(); dr.Close(); } finally { myConn.CloseDB(conn); } return(searchList); }
public static int AddAccountAddress(AccountAddress newAccountAddress) { int result; MyDBConnection dbconn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); try { conn = dbconn.OpenDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.AddAccountAddress"; cmd.Parameters.Add("@AccountID", SqlDbType.Int).Value = newAccountAddress.AccountID; cmd.Parameters.Add("@AddressID", SqlDbType.Int).Value = newAccountAddress.AddressID; cmd.Parameters.Add("@AddressType", SqlDbType.Int).Value = newAccountAddress.AddressType; cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = newAccountAddress.CreatedDate; cmd.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = newAccountAddress.ModifiedDate; cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = newAccountAddress.CreatedBy; cmd.Parameters.Add("@ModifiedBy", SqlDbType.VarChar).Value = newAccountAddress.ModifiedBy; result = cmd.ExecuteNonQuery(); } finally { dbconn.CloseDB(conn); } return(result); }
public static DataTable GetAccountWithAddress(int accountID) { MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand(); DataTable dt = null; try { conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "Select * from aquaone.dbo.vw_accountWithAddress where accountID=@accountID"; cmd.Parameters.Add("@accountID", SqlDbType.Int).Value = accountID; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt = new DataTable(); dt.Load(dr); cmd.Dispose(); dr.Close(); } finally { myConn.CloseDB(conn); } return(dt); }
internal static DataTable GetInvoiceBySearchCriteria(string searchBy, string searchString) { MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand(); DataTable dt = null; try { conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.getInvoiceBySearchCriteria"; cmd.Parameters.Add("@SearchCriteria", SqlDbType.VarChar).Value = searchBy; cmd.Parameters.Add("@Input", SqlDbType.VarChar).Value = searchString; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt = new DataTable(); dt.Load(dr); cmd.Dispose(); dr.Close(); } finally { myConn.CloseDB(conn); } return(dt); }
public static int Activate(Account account) { int result; MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); try { conn = myConn.OpenDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.ActivateAccount"; cmd.Parameters.Add("@AccountID", SqlDbType.Int).Value = account.AccountID; cmd.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = account.ModifiedDate; cmd.Parameters.Add("@ModifiedBy", SqlDbType.VarChar).Value = account.ModifiedBy; DbParameter returnValue = cmd.CreateParameter(); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue); cmd.ExecuteNonQuery(); result = Convert.ToInt32(returnValue.Value); cmd.Dispose(); } finally { myConn.CloseDB(conn); } return(result); }
public static string GetDescriptionByID(int locationID) { string description = ""; AccountAddress accountAddress = new AccountAddress(); MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr; SqlCommand cmd = null; string sql = "Select description from AquaOne.dbo.Ref_WaterSourceLocation where LocationID = @LocationID"; // Open the connection conn = myConn.OpenDB(); cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@LocationID", SqlDbType.Int).Value = locationID; dr = cmd.ExecuteReader(); if (dr.Read()) { description = dr.GetString(0); } cmd.Dispose(); //close the connection myConn.CloseDB(conn); return(description); }
public static DataTable GetProductsByCategory(string productCategory, int ProductID) { MyDBConnection myConn = new MyDBConnection(); SqlConnection conn = new SqlConnection(); SqlDataReader dr = null; SqlCommand cmd = new SqlCommand(); DataTable dt = null; try { conn = myConn.OpenDB(); cmd.Connection = conn; cmd.CommandText = "Select * from Product where ProductCategory = @ProductCategory and IsSubProduct=1 and ProductID <> @ProductID and ProductID not in (select SubProductID from ProductPartReplacement pp, Product p where pp.ProductID=@ProductID and p.ProductID=pp.SubProductID) order by ProductCategory"; cmd.Parameters.Add("@ProductCategory", SqlDbType.VarChar).Value = productCategory; cmd.Parameters.Add("@ProductID", SqlDbType.VarChar).Value = ProductID; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt = new DataTable(); dt.Load(dr); cmd.Dispose(); dr.Close(); } finally { myConn.CloseDB(conn); } return(dt); }