public int Updateworkorder(BO.AssetInventoryTracking.workorder workorder)
{
int RowsAffected = -1;
string query = "UPDATE dbo.[workorder] SET date_created = @date_created,date_completed = @date_completed,status = @status,inventoryID = @inventoryID WHERE workorderID=@workorderID";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
if ((workorder.workorderID == -1))
{
cmd.Parameters.AddWithValue("@workorderID", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@workorderID", workorder.workorderID);
}
cmd.Parameters.AddWithValue("@date_created", workorder.date_created);
cmd.Parameters.AddWithValue("@date_completed", workorder.date_completed);
cmd.Parameters.AddWithValue("@status", workorder.status);
if ((workorder.inventoryID == -1))
{
cmd.Parameters.AddWithValue("@inventoryID", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@inventoryID", workorder.inventoryID);
}
conn.Open();
RowsAffected = cmd.ExecuteNonQuery();
}
}
return(RowsAffected);
}
public List <BO.AssetInventoryTracking.workorder> GetAllworkorder()
{
List <BO.AssetInventoryTracking.workorder> xworkorderList = new List <BO.AssetInventoryTracking.workorder>();
string query = "SELECT [workorderID],[date_created],[date_completed],[status],[inventoryID],[date_modified] FROM dbo.[workorder]";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
conn.Open();
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
while (reader.Read())
{
BO.AssetInventoryTracking.workorder xworkorder = new BO.AssetInventoryTracking.workorder();
if (!object.ReferenceEquals(reader["workorderID"], DBNull.Value))
{
xworkorder.workorderID = int.Parse(reader["workorderID"].ToString());
}
if (!object.ReferenceEquals(reader["date_created"], DBNull.Value))
{
xworkorder.date_created = DateTime.Parse(reader["date_created"].ToString());
}
if (!object.ReferenceEquals(reader["date_completed"], DBNull.Value))
{
xworkorder.date_completed = DateTime.Parse(reader["date_completed"].ToString());
}
if (!object.ReferenceEquals(reader["status"], DBNull.Value))
{
xworkorder.status = reader["status"].ToString();
}
if (!object.ReferenceEquals(reader["inventoryID"], DBNull.Value))
{
xworkorder.inventoryID = int.Parse(reader["inventoryID"].ToString());
}
if (!object.ReferenceEquals(reader["date_modified"], DBNull.Value))
{
xworkorder.date_modified = DateTime.Parse(reader["date_modified"].ToString());
}
xworkorderList.Add(xworkorder);
}
}
}
}
return(xworkorderList);
}
public BO.AssetInventoryTracking.workorder GetByIDworkorder(int workorderID)
{
BO.AssetInventoryTracking.workorder xworkorder = new BO.AssetInventoryTracking.workorder();
string query = "SELECT [workorderID],[date_created],[date_completed],[status],[inventoryID],[date_modified] FROM dbo.[workorder] WHERE workorderID=@workorderID";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
cmd.Parameters.AddWithValue("@workorderID", workorderID);
conn.Open();
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
if (reader.Read())
{
if (!object.ReferenceEquals(reader["workorderID"], DBNull.Value))
{
xworkorder.workorderID = int.Parse(reader["workorderID"].ToString());
}
if (!object.ReferenceEquals(reader["date_created"], DBNull.Value))
{
xworkorder.date_created = DateTime.Parse(reader["date_created"].ToString());
}
if (!object.ReferenceEquals(reader["date_completed"], DBNull.Value))
{
xworkorder.date_completed = DateTime.Parse(reader["date_completed"].ToString());
}
if (!object.ReferenceEquals(reader["status"], DBNull.Value))
{
xworkorder.status = reader["status"].ToString();
}
if (!object.ReferenceEquals(reader["inventoryID"], DBNull.Value))
{
xworkorder.inventoryID = int.Parse(reader["inventoryID"].ToString());
}
if (!object.ReferenceEquals(reader["date_modified"], DBNull.Value))
{
xworkorder.date_modified = DateTime.Parse(reader["date_modified"].ToString());
}
}
}
}
}
return(xworkorder);
}
public int Addworkorder(BO.AssetInventoryTracking.workorder workorder)
{
int workorderID = 0;
string query = "INSERT INTO dbo.[workorder] ([date_created],[date_completed],[status],[inventoryID],[date_modified]) VALUES (@date_created, @date_completed, @status, @inventoryID,@date_modified) ";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
cmd.Parameters.AddWithValue("@date_created", workorder.date_created);
if (workorder.date_completed == null)
{
cmd.Parameters.AddWithValue("@date_completed", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@date_completed", workorder.date_completed);
}
if (workorder.date_modified == null)
{
cmd.Parameters.AddWithValue("@date_modified", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@date_modified", workorder.date_modified);
}
cmd.Parameters.AddWithValue("@status", workorder.status);
if ((workorder.inventoryID == -1))
{
cmd.Parameters.AddWithValue("@inventoryID", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@inventoryID", workorder.inventoryID);
}
conn.Open();
cmd.ExecuteScalar();
}
}
return(workorderID);
}