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