//static object LockObject = new object();
public static bool AddInfo(int typeID, InfoClass info, ProductClass product, Dictionary <int, string> keyvalues)
{
string sql = "insert into T_Info (Title, TypeID) values ('" + info.Title + "', " + info.TypeId.ToString() + ")";
SqlCommand cmd = SqlDAL.Connection.CreateCommand();
cmd.Transaction = SqlDAL.Connection.BeginTransaction(IsolationLevel.RepeatableRead);
bool insert = SqlDAL.ExecuteNonQuery(sql, cmd);
if (insert)
{
int infoID = SqlDAL.GetMaxID("ID", "T_Info");
List <KeyClass> keys = GetKeys(infoID, false);
foreach (KeyClass key in keys)
{
string sql1 = "insert into T_KeyValue (KeyId, Value) values (@KeyId, @Value)";
cmd.Parameters.AddWithValue("@KeyId", key.Id);
cmd.Parameters.AddWithValue("@Value", keyvalues[key.Id]);
SqlDAL.ExecuteNonQuery(sql1, cmd);
}
string sql2 = "insert into T_Product (Picture) values (@Picture)";
cmd.Parameters.AddWithValue("@Picture", GetBytesFromImageList(product.Picture));
bool insert1 = SqlDAL.ExecuteNonQuery(sql2, cmd);
if (insert1)
{
SqlDAL.CommitTransaction(cmd);
return(true);
}
}
return(false);
}
public static Dictionary <KeyClass, ValueClass> GetKeyValues(int infoId)
{
int typeId = GetTypeIdOfInfo(infoId);
Dictionary <KeyClass, ValueClass> keyvalues = new Dictionary <KeyClass, ValueClass>();
string parentIds = GetAllParentIds(typeId);
if (parentIds != "")
{
parentIds = typeId.ToString() + "," + parentIds;
}
else
{
parentIds = typeId.ToString();
}
DataTable key = SqlDAL.GetRecords("select * from T_KeyInfo where TypeId in (" + parentIds + ")");
if (key != null && key.Rows.Count > 0)
{
for (int i = 0; i < key.Rows.Count; i++)
{
DataTable value = SqlDAL.GetRecords("select * from T_KeyValue where KeyId=" + key.Rows[i]["ID"].ToString());
ValueClass vc = new ValueClass();
if (value != null && value.Rows.Count > 0)
{
vc = new ValueClass((int)value.Rows[0]["ID"], (int)value.Rows[0]["InfoId"], (int)value.Rows[0]["KeyId"], value.Rows[0]["Value"].ToString());
}
keyvalues.Add(new KeyClass((int)key.Rows[i]["ID"], (int)key.Rows[i]["TypeId"], key.Rows[i]["KeyName"].ToString(), key.Rows[i]["FriendName"].ToString(), key.Rows[i]["Unit"].ToString()), vc);
}
}
return(keyvalues);
}
public static bool UpdateProduct(SqlParameter[] set, string where)
{
if (set.Length > 0)
{
StringBuilder sb = new StringBuilder();
foreach (SqlParameter para in set)
{
if (sb.Length == 0)
{
sb.Append(para.ParameterName + "=@" + para.ParameterName);
}
else
{
sb.Append("," + para.ParameterName + "=@" + para.ParameterName);
}
}
string sql = "update T_Product set " + sb.ToString();
if (!string.IsNullOrEmpty(where))
{
where = where.Trim().ToLower();
if (where.StartsWith("and "))
{
where = where.Substring(4);
}
sql += " where (1=1) and " + where;
}
return(SqlDAL.ExecuteNonQuery(sql, set));
}
else
{
throw new Exception("set参数为空，更新失败！");
}
}
public static List <KeyClass> GetKeys(int typeId, bool privated = false)
{
List <KeyClass> keys = new List <KeyClass>();
string parentIds = "";
if (privated)
{
parentIds = typeId.ToString();
}
else
{
parentIds = GetAllParentIds(typeId);
if (parentIds != "")
{
parentIds = typeId.ToString() + "," + parentIds;
}
else
{
parentIds = typeId.ToString();
}
}
DataTable dt = SqlDAL.GetRecords("select * from T_KeyInfo where TypeId in (" + parentIds + ")");
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
keys.Add(new KeyClass((int)dt.Rows[i]["ID"], (int)dt.Rows[i]["TypeId"], dt.Rows[i]["KeyName"].ToString(), dt.Rows[i]["FriendName"].ToString(), dt.Rows[i]["Unit"].ToString()));
}
}
return(keys);
}
public static KeyClass GetKey(int id)
{
DataTable dt = SqlDAL.GetRecords("select * from T_KeyInfo where ID=" + id);
if (dt != null && dt.Rows.Count > 0)
{
return(new KeyClass((int)dt.Rows[0]["ID"], (int)dt.Rows[0]["TypeId"], dt.Rows[0]["KeyName"].ToString(), dt.Rows[0]["FriendName"].ToString(), dt.Rows[0]["Unit"].ToString()));
}
return(null);
}
public static ValueClass GetValue(int infoID, string keyName)
{
DataTable dt = SqlDAL.GetRecords("select * from T_KeyValue where InfoId=" + infoID + " and KeyId=(select ID from T_KeyInfo where KeyName='" + keyName + "'");
if (dt != null && dt.Rows.Count > 0)
{
return(new ValueClass((int)dt.Rows[0]["ID"], (int)dt.Rows[0]["InfoId"], (int)dt.Rows[0]["KeyId"], dt.Rows[0]["Value"].ToString()));
}
return(null);
}
public static bool UpdateType(int id, string typeName)
{
if (ExistsType(GetParentType(id).Id, typeName))
{
return(false);
}
string sql = "update T_Type set TypeName='" + typeName + "' where Id=" + id;
return(SqlDAL.ExecuteNonQuery(sql));
}
public static TypeClass GetType(int id)
{
string sql = "select * from T_Type where ID = " + id.ToString();
DataTable ti = SqlDAL.GetRecords(sql);
if (ti != null && ti.Rows.Count > 0)
{
TypeClass type = new TypeClass(id, ti.Rows[0]["TypeName"].ToString(), ti.Rows[0]["Description"].ToString(), (int)ti.Rows[0]["ParentID"]);
return(type);
}
return(null);
}
public static List <TypeClass> GetSubTypes(int parentID)
{
string sql = "select * from T_Type where ParentID=" + parentID.ToString();
DataTable types = SqlDAL.GetRecords(sql);
List <TypeClass> ts = new List <TypeClass>();
for (int i = 0; i < types.Rows.Count; i++)
{
TypeClass tc = new TypeClass((int)types.Rows[i]["ID"], types.Rows[i]["TypeName"].ToString(), types.Rows[i]["Description"].ToString(), (int)types.Rows[i]["ParentID"]);
ts.Add(tc);
}
return(ts);
}
public static InfoClass GetInfo(int id)
{
string sql = "select * from T_Info where Id=" + id;
InfoClass info = new InfoClass();
DataTable infos = SqlDAL.GetRecords(sql);
if (infos != null && infos.Rows.Count > 0)
{
info = new InfoClass((int)infos.Rows[0]["ID"], infos.Rows[0]["Title"].ToString(), (int)infos.Rows[0]["TypeID"], bool.Parse(infos.Rows[0]["Visible"].ToString()), DateTime.Parse(infos.Rows[0]["SysTime"].ToString()));
return(info);
}
return(info);
}
public static bool AddType(string typeName, string description, int parentID)
{
//lock (LockObject)
//{
if (ExistsType(parentID, typeName))
{
return(false);
}
string sql = "insert into T_Type (TypeName, Description, ParentID) values ('" + typeName + "', '" + description + "', " + parentID.ToString() + ")";
return(SqlDAL.ExecuteNonQuery(sql));
//}
}
public static int GetMaxID(string idFieldName, string tableName)
{
object obj = SqlDAL.GetOneValue("select max(" + idFieldName + ") from " + tableName);
if (obj != null && obj != DBNull.Value)
{
return((int)obj);
}
else
{
return(0);
}
}
public static bool AddKeyInfo(int typeId, string keyName, string friendName, string unit)
{
//lock (LockObject)
//{
if (ExistsKey(typeId, keyName))
{
return(false);
}
string sql = "insert into T_KeyInfo (TypeId, KeyName, FriendName, Unit) values (" + typeId.ToString() + ", '" + keyName + "', '" + friendName + "', '" + unit + "')";
return(SqlDAL.ExecuteNonQuery(sql));
//}
}
private static bool ExistsType(int parentID, string typeName)
{
string sql = "select * from T_Type where ParentID=" + parentID.ToString() + " and TypeName='" + typeName + "'";
DataTable dt = SqlDAL.GetRecords(sql);
if (dt != null && dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool UpdateInfoVisible(bool visible, string where)
{
string sql = "update T_Info set Visible=" + (visible ? "1" : "0");
if (!string.IsNullOrEmpty(where))
{
where = where.Trim().ToLower();
if (where.StartsWith("and "))
{
where = where.Substring(4);
}
sql += " where (1=1) and " + where;
}
return(SqlDAL.ExecuteNonQuery(sql));
}
public static bool DeleteType(string where)
{
string sql = "dalete from T_Type";
if (!string.IsNullOrEmpty(where))
{
where = where.Trim().ToLower();
if (where.StartsWith("and "))
{
where = where.Substring(4);
}
sql += " where (1=1) and " + where;
}
return(SqlDAL.ExecuteNonQuery(sql));
}
public static bool UpdateInfoTitle(string title, string where)
{
string sql = "update T_Info set Title='" + title + "'";
if (!string.IsNullOrEmpty(where))
{
where = where.Trim().ToLower();
if (where.StartsWith("and "))
{
where = where.Substring(4);
}
sql += " where (1=1) and " + where;
}
return(SqlDAL.ExecuteNonQuery(sql));
}
public static bool UpdateInfoType(int typeId, string where)
{
string sql = "update T_Info set TypeId=" + typeId.ToString();
if (!string.IsNullOrEmpty(where))
{
where = where.Trim().ToLower();
if (where.StartsWith("and "))
{
where = where.Substring(4);
}
sql += " where (1=1) and " + where;
}
return(SqlDAL.ExecuteNonQuery(sql));
}
public static bool CopyKeyInfoTo(int id, int typeId)
{
KeyClass key = GetKey(id);
if (key.TypeId != typeId)
{
if (ExistsKey(typeId, key.KeyName))
{
return(false);
}
string sql = "insert into T_KeyInfo (TypeId, KeyName, FriendName, Unit) values (" + typeId + ", '" + key.KeyName + "', '" + key.FriendName + "', '" + key.Unit + "')";
return(SqlDAL.ExecuteNonQuery(sql));
}
else
{
return(false);
}
}
public static List <ValueClass> GetValues(int infoID)
{
List <ValueClass> values = new List <ValueClass>();
List <KeyClass> keys = GetKeys(GetTypeIdOfInfo(infoID), false);
if (keys != null && keys.Count > 0)
{
foreach (KeyClass key in keys)
{
DataTable dt = SqlDAL.GetRecords("select * from T_KeyValue where InfoId=" + infoID + " and KeyId=" + key.Id);
ValueClass vc = new ValueClass();
if (dt != null && dt.Rows.Count > 0)
{
vc = new ValueClass((int)dt.Rows[0]["ID"], (int)dt.Rows[0]["InfoId"], key.Id, dt.Rows[0]["Value"].ToString());
}
values.Add(vc);
}
}
return(values);
}
public static bool UpdateKeyValue(string set, string where)
{
if (!string.IsNullOrEmpty(set))
{
string sql = "update T_KeyValue set " + set;
if (!string.IsNullOrEmpty(where))
{
where = where.Trim().ToLower();
if (where.StartsWith("and "))
{
where = where.Substring(4);
}
sql += " where (1=1) and " + where;
}
return(SqlDAL.ExecuteNonQuery(sql));
}
else
{
throw new Exception("set参数为空，更新失败！");
}
}
public static List <InfoClass> GetInfos(string where)
{
string sql = "select * from T_Info";
if (!string.IsNullOrEmpty(where))
{
where = where.Trim().ToLower();
if (where.StartsWith("and "))
{
where = where.Substring(4);
}
sql += " where (1=1) and " + where;
}
DataTable infos = SqlDAL.GetRecords(sql);
List <InfoClass> Is = new List <InfoClass>();
for (int i = 0; i < infos.Rows.Count; i++)
{
InfoClass ic = new InfoClass((int)infos.Rows[i]["ID"], infos.Rows[i]["Title"].ToString(), (int)infos.Rows[i]["TypeID"], bool.Parse(infos.Rows[i]["Visible"].ToString()), DateTime.Parse(infos.Rows[i]["SysTime"].ToString()));
Is.Add(ic);
}
return(Is);
}
public static List <TypeClass> GetTypes(string where)
{
string sql = "select * from T_Type";
if (!string.IsNullOrEmpty(where))
{
where = where.Trim().ToLower();
if (where.StartsWith("and "))
{
where = where.Substring(4);
}
sql += " where (1=1) and " + where;
}
DataTable types = SqlDAL.GetRecords(sql);
List <TypeClass> ts = new List <TypeClass>();
for (int i = 0; i < types.Rows.Count; i++)
{
TypeClass tc = new TypeClass((int)types.Rows[i]["ID"], types.Rows[i]["TypeName"].ToString(), types.Rows[i]["Description"].ToString(), (int)types.Rows[i]["ParentID"]);
ts.Add(tc);
}
return(ts);
}
public static ProductClass GetLastOneProduct()
{
ProductClass product = null;
DataTable last = SqlDAL.GetRecords("select top 1 * from T_Product order by ID desc");
if (last != null && last.Rows.Count > 0)
{
int infoID = (int)last.Rows[0]["InfoID"];
string typeLink = GetTypeNameOfInfo(infoID);
List <Image> images = new List <Image>();
try
{
images = GetImageFromDb((byte[])last.Rows[0]["Picture"]);
}
catch// (Exception e)
{
typeLink += "(图片有损)";
//throw e;
}
Dictionary <KeyClass, ValueClass> keyvalues = GetKeyValues(infoID);
product = new ProductClass((int)last.Rows[0]["ID"], infoID, GetInfo(infoID).Title, typeLink, keyvalues, images);
}
return(product);
}
public static ProductClass GetProduct(int infoID)
{
string sql = "select * from T_Product where InfoID = " + infoID.ToString();
DataTable pi = SqlDAL.GetRecords(sql);
if (pi != null && pi.Rows.Count > 0)
{
string typeLink = GetTypeNameOfInfo(infoID);
List <Image> images = new List <Image>();
try
{
images = GetImageFromDb((byte[])pi.Rows[0]["Picture"]);
}
catch// (Exception e)
{
typeLink += "(图片有损)";
//throw e;
}
Dictionary <KeyClass, ValueClass> keyvalues = GetKeyValues(infoID);
ProductClass product = new ProductClass((int)pi.Rows[0]["ID"], infoID, GetInfo(infoID).Title, typeLink, keyvalues, images);
return(product);
}
return(null);
}
public static bool ExistsKey(int typeId, string keyName)
{
string parentIds = GetAllParentIds(typeId);
if (parentIds != "")
{
parentIds = typeId.ToString() + "," + parentIds;
}
else
{
parentIds = typeId.ToString();
}
string sql = "select * from T_KeyInfo where TypeId in (" + parentIds + ") and KeyName='" + keyName + "'";
DataTable dt = SqlDAL.GetRecords(sql);
if (dt != null && dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public static int GetLastTypeId()
{
return(SqlDAL.GetMaxID("ID", "T_Type"));
}
public static bool UpdateKeyInfo(int id, string friendName, string unit)
{
string sql = "update T_KeyInfo set FriendName='" + friendName + "', Unit='" + unit + "' where ID=" + id;
return(SqlDAL.ExecuteNonQuery(sql));
}
public static int GetLastKeyId()
{
return(SqlDAL.GetMaxID("ID", "T_KeyInfo"));
}
public static bool MoveKeyInfoTo(int id, int typeId)
{
string sql = "update T_KeyInfo set TypeId=" + typeId + " where ID=" + id;
return(SqlDAL.ExecuteNonQuery(sql));
}
