I am writing an application where I have some objects like customer, supplier, product, etc.
I have written a class for the object 'supplier' and wanted to ask if this is a good design. I have put in the class the object itself, some methods for saving and deleting, ... and some static methods for example to return all suppliers or for example delete a specific supplier other than the current object.
This is my class:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MyApplication.Administration.Utility;
using Microsoft.SharePoint;
namespace MyApplication.Administration.Model
{
public class Supplier
{
public int id { get; set; }
public string name { get; set; }
public int su_id { get; set; }
public string su_name { get; set; }
public string portal { get; set; }
public string sort_order { get; set; }
public bool active { get; set; }
public Supplier() {
}
public static Supplier GetSupplier(int supplierId)
{
Supplier supplier = new Supplier();
Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(delegate ()
{
string connString = Factories.Database.GetConnectionString();
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT supplier.*, supplier_status.name AS su_name FROM supplier INNER JOIN supplier_status ON supplier.su_id = supplier_status.id WHERE supplier.id = @id";
cmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = supplierId;
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
// Hier ggf. nur das erste Element nehmen
while (dr.Read())
{
supplier.id = SqlReaderHelper.GetValue<int>(dr, "id");
supplier.name = SqlReaderHelper.GetValue<string>(dr, "name");
supplier.su_id = SqlReaderHelper.GetValue<int>(dr, "su_id");
supplier.su_name = SqlReaderHelper.GetValue<string>(dr, "su_name");
supplier.portal = SqlReaderHelper.GetValue<string>(dr, "portal");
supplier.sort_order = SqlReaderHelper.GetValue<string>(dr, "sort_order");
supplier.active = SqlReaderHelper.GetValue<bool>(dr, "active");
}
}
else
{
supplier = null;
}
}
}
});
return supplier;
}
public static List<Supplier> GetSuppliers()
{
List<Supplier> supplierList = new List<Supplier>();
Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(delegate ()
{
string connString = Factories.Database.GetConnectionString();
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT supplier.*, supplier_status.name AS su_name FROM supplier INNER JOIN supplier_status ON supplier.su_id = supplier_status.id";
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
Supplier supplier = new Supplier();
supplier.id = SqlReaderHelper.GetValue<int>(dr, "id");
supplier.name = SqlReaderHelper.GetValue<string>(dr, "name");
supplier.su_id = SqlReaderHelper.GetValue<int>(dr, "su_id");
supplier.su_name = SqlReaderHelper.GetValue<string>(dr, "su_name");
supplier.portal = SqlReaderHelper.GetValue<string>(dr, "portal");
supplier.sort_order = SqlReaderHelper.GetValue<string>(dr, "sort_order");
supplier.active = SqlReaderHelper.GetValue<bool>(dr, "active");
supplierList.Add(supplier);
}
}
else
{
supplierList = null;
}
}
}
});
return supplierList;
}
/// <summary>
/// Create new Supplier.
/// </summary>
public void Save()
{
Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(delegate ()
{
string connString = Factories.Database.GetConnectionString();
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO supplier (name, su_id, portal, sort_order, active) VALUES (@name, @su_id, @portal, @sort_order, @active);";
cmd.Parameters.AddWithValue("@name", SqlDbType.NVarChar).Value = name;
cmd.Parameters.AddWithValue("@su_id", SqlDbType.Int).Value = su_id;
cmd.Parameters.AddWithValue("@portal", SqlDbType.NVarChar).Value = portal;
cmd.Parameters.AddWithValue("@sort_order", SqlDbType.NVarChar).Value = sort_order;
cmd.Parameters.AddWithValue("@active", SqlDbType.Bit).Value = active;
conn.Open();
cmd.ExecuteNonQuery();
}
});
}
/// <summary>
/// Delete the Supplier itself.
/// </summary>
public void Delete()
{
Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(delegate ()
{
string connString = Factories.Database.GetConnectionString();
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM supplier WHERE id = @id";
cmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = id;
conn.Open();
cmd.ExecuteNonQuery();
}
});
}
/// <summary>
/// Delete a Supplier by id.
/// </summary>
/// <param name="supplierId"></param>
public static void Delete(int supplierId)
{
Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(delegate ()
{
string connString = Factories.Database.GetConnectionString();
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM supplier WHERE id = @id";
cmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = supplierId;
conn.Open();
cmd.ExecuteNonQuery();
}
});
}
/// <summary>
/// Update a Supplier.
/// </summary>
public void Update()
{
Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(delegate ()
{
string connString = Factories.Database.GetConnectionString();
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE supplier SET name = @name, su_id = @su_id, portal = @portal, sort_order = @sort_order, active = @active WHERE id = @id";
cmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = id;
cmd.Parameters.AddWithValue("@name", SqlDbType.NVarChar).Value = name;
cmd.Parameters.AddWithValue("@su_id", SqlDbType.Int).Value = su_id;
cmd.Parameters.AddWithValue("@portal", SqlDbType.NVarChar).Value = portal;
cmd.Parameters.AddWithValue("@sort_order", SqlDbType.NVarChar).Value = sort_order;
cmd.Parameters.AddWithValue("@active", SqlDbType.Bit).Value = active;
conn.Open();
cmd.ExecuteNonQuery();
}
});
}
}
}
Is this a good design? How could I optimize this? What would you prefer?
SqlCommand
requires ausing
too. \$\endgroup\$ – t3chb0t Jun 23 '16 at 11:34