public int AddUser(Users_Model usermodel)
{
string sql = "insert into users values(@pwd,@nick,@sex,@age,@face,@friend,@name,@star,@blood)";
SqlParameter[] pars =
{
new SqlParameter("@pwd", SqlDbType.VarChar, 20),
new SqlParameter("@nick", SqlDbType.VarChar, 20),
new SqlParameter("@sex", SqlDbType.Char, 2),
new SqlParameter("@age", SqlDbType.Int),
new SqlParameter("@face", SqlDbType.Int),
new SqlParameter("@friend", SqlDbType.Int),
new SqlParameter("@name", SqlDbType.VarChar, 20),
new SqlParameter("@star", SqlDbType.Int),
new SqlParameter("@blood", SqlDbType.Int)
};
pars[0].Value = usermodel.LoginPwd;
pars[1].Value = usermodel.NickName;
pars[2].Value = usermodel.Sex;
pars[3].Value = usermodel.Age;
pars[4].Value = usermodel.FaceId;
pars[5].Value = usermodel.Friend;
pars[6].Value = usermodel.Name;
pars[7].Value = usermodel.StarId;
pars[8].Value = usermodel.BloodId;
return(DbHelperSql.ExecuteNonQuery(sql, CommandType.Text, pars));
}
public int UpdateUser(Users_Model usermodel)
{
string sql = "update users set loginpwd=@pwd,NickName=@nick,sex=@sex,age=@age,faceid=@face,FriendShipPolicyId=@friend,Name=@name,StarId=@star,BloodTypeId=@blood where id=@id";
SqlParameter[] pars =
{
new SqlParameter("@pwd", SqlDbType.VarChar, 20),
new SqlParameter("@nick", SqlDbType.VarChar, 20),
new SqlParameter("@sex", SqlDbType.Char, 2),
new SqlParameter("@age", SqlDbType.Int),
new SqlParameter("@face", SqlDbType.Int),
new SqlParameter("@friend", SqlDbType.Int),
new SqlParameter("@name", SqlDbType.VarChar, 20),
new SqlParameter("@star", SqlDbType.Int),
new SqlParameter("@blood", SqlDbType.Int),
new SqlParameter("@id", SqlDbType.Int)
};
pars[0].Value = usermodel.LoginPwd;
pars[1].Value = usermodel.NickName;
pars[2].Value = usermodel.Sex;
pars[3].Value = usermodel.Age;
pars[4].Value = usermodel.FaceId;
pars[5].Value = usermodel.Friend;
pars[6].Value = usermodel.Name;
pars[7].Value = usermodel.StarId;
pars[8].Value = usermodel.BloodId;
pars[9].Value = usermodel.Id;
return(DbHelperSql.ExecuteNonQuery(sql, CommandType.Text, pars));
}
/// <summary>
/// 通用更新操作 暂时只支持单条件且条件为相等
/// </summary>
/// <param name="ht">key->更新字段名称；value->更新字段值</param>
/// <param name="tableName">操作更新表名</param>
/// <param name="whereFiledName">条件字段名称</param>
/// <param name="whereFiledValue">条件字段值</param>
/// <param name="whereFiledType">条件字段类型：1数字、2字符（字符串、时间、GUID）</param>
/// <returns>执行状态 成功>0；失败返回0；异常-1；参数为空返回-2</returns>
public static int DynamicUpdate(Hashtable ht, string tableName, string whereFiledName, int whereFiledValue, int whereFiledType)
{
if (ht == null || string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(whereFiledName))
{
return(-2);
}
StringBuilder sbRet = new StringBuilder("UPDATE [" + tableName + "] SET ");
foreach (var itemKey in ht.Keys)
{
sbRet.Append(itemKey.ToString() + "=@" + itemKey.ToString() + ",");
}
sbRet = new StringBuilder(sbRet.ToString().TrimEnd(','));
switch (whereFiledType)
{
case 1:
sbRet.AppendFormat(" where {0}={1}", whereFiledName, whereFiledValue);
break;
case 2:
default:
sbRet.AppendFormat(" where {0}='{1}'", whereFiledName, whereFiledValue);
break;
}
string safeSql = sbRet.ToString();
SqlParameter[] parameters = new SqlParameter[ht.Keys.Count];
int index = 0;
IDictionaryEnumerator ienum = ht.GetEnumerator();
while (ienum.MoveNext())
{
parameters[index] = new SqlParameter("@" + ienum.Key, ienum.Value);
index++;
}
try
{
int result = DbHelperSql.ExecuteNonQuery(safeSql, parameters);
return(result);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(-1);
}
}
public int DeleteUser(int id)
{
string sql = "delete from users where id=@id";
return(DbHelperSql.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter("@id", id)));
}