public int LogIn(string userName, string password)
{
string connString = DALUtility.GetSQLConnection("OSConnection");
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "select employeeID from employee where username = @username and password = @password";
cmd.Parameters.AddWithValue("@username", userName);
cmd.Parameters.AddWithValue("@password", password);
int userID;
conn.Open();
userID = Convert.ToInt32(cmd.ExecuteScalar());
if (userID > 0)
{
return(userID);
}
else
{
return(-1);
}
}
}
}
public int PlaceOrder(string xmlOrder)
{
string connString = DALUtility.GetSQLConnection("OSConnection");
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "up_PlaceOrder";
SqlParameter inParameter = new SqlParameter();
inParameter.ParameterName = "@xmlOrder";
inParameter.Value = xmlOrder;
inParameter.DbType = DbType.String;
inParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(inParameter);
SqlParameter returnParameter = new SqlParameter();
returnParameter.ParameterName = "@orderid";
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnParameter);
int orderNo;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
orderNo = (int)cmd.Parameters["@orderid"].Value;
return(orderNo);
}
}
}
public DataSet GetProductInfo()
{
DataSet _dsProducts;
string connString = DALUtility.GetSQLConnection("OSConnection");
using (SqlConnection _conn = new SqlConnection(connString))
{
_dsProducts = new DataSet("Products");
string strSQL = "Select CategoryId, Name, Description from Category";
using (SqlCommand cmdSelCategory = new SqlCommand(strSQL, _conn))
{
using (SqlDataAdapter daCategory = new SqlDataAdapter(cmdSelCategory))
{
daCategory.Fill(_dsProducts, "Category");
}
}
String strSQL2 = "select ProductId, CategoryId, Name, Description, UnitCost from Product";
using (SqlCommand cmdSelProduct = new SqlCommand(strSQL2, _conn))
{
using (SqlDataAdapter daProduct = new SqlDataAdapter(cmdSelProduct))
{
daProduct.Fill(_dsProducts, "Product");
}
}
}
DataRelation drCategoryToProduct = new DataRelation("drCategoryToProduct", _dsProducts.Tables["Category"].Columns["CategoryId"], _dsProducts.Tables["Product"].Columns["CategoryId"], false);
_dsProducts.Relations.Add(drCategoryToProduct);
return(_dsProducts);
}