public static int CreateOne <T>(T models)
{
Type type = models.GetType();
string insertStr1 = "";
string insertStr2 = "";
PropertyInfo[] propArray = type.GetProperties();
List <SqlParameter> paramslist = new List <SqlParameter>();
foreach (PropertyInfo pi in propArray)
{
if (String.Compare(pi.Name, "ID", true) == 0 || pi.GetValue(models, null) == null)
{
continue;
}
insertStr1 += "[" + pi.Name + "],";
insertStr2 += "@" + pi.Name + ",";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(models, null)));
}
insertStr1 = insertStr1.TrimEnd(',');
insertStr2 = insertStr2.TrimEnd(',');
String tableName = "Tb_" + type.Name;
string sql = string.Format("insert into {0}({1}) values({2})", tableName, insertStr1, insertStr2);
SqlParameter[] parameters = paramslist.ToArray();
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
/// <summary>
/// 更新一系列数据
/// </summary>
/// <typeparam name="T">数据库模型</typeparam>
/// <param name="model">模型实例对象</param>
/// <param name="target">数据查询条件</param>
/// <returns>影响行数</returns>
public static int ChangeSome <T>(T model, string target)
{
Utility.SQLHelper db = new Utility.SQLHelper();
Type type = model.GetType();
string updateStr1 = "";
string updateStr2 = "";
PropertyInfo[] propArray = type.GetProperties();
PropertyInfo lastPi = type.GetProperty(target);
List <SqlParameter> paramslist = new List <SqlParameter>();
foreach (PropertyInfo pi in propArray)
{
if ((String.Compare(pi.Name, target) == 0) || (String.Compare(pi.Name, "ID", true) == 0) || pi.GetValue(model, null) == null)
{
continue;
}
updateStr1 += "[" + pi.Name + "]=@" + pi.Name + ",";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(model, null)));
}
updateStr1 = updateStr1.TrimEnd(',');
updateStr2 = "[" + target + "]=@" + target;
paramslist.Add(new SqlParameter(target, lastPi.GetValue(model, null)));
string sql = string.Format("update {0} set {1} where {2}", Utility.Tool.ModelNameToTableName(type.Name), updateStr1, updateStr2);
SqlParameter[] parameters = paramslist.ToArray();
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
public static int Insert(Models.DB.User user)
{
string sql = string.Format("insert into Tb_User(Name,Password,RoleID,Enable) values(@Name,@Password,@RoleID,@Enable);select @id=SCOPE_IDENTITY()");
SqlParameter[] parameters =
{
new SqlParameter("Name", SqlDbType.NVarChar, 255),
new SqlParameter("Password", user.Password),
new SqlParameter("RoleID", SqlDbType.Int),
new SqlParameter("Enable", SqlDbType.Bit),
new SqlParameter("id", SqlDbType.Int)
};
parameters[0].Value = user.Name;
parameters[1].Value = user.Password;
parameters[2].Value = user.RoleId;
parameters[3].Value = user.Enable;
parameters[4].Direction = ParameterDirection.Output;
// int temp = Convert.ToInt32(parameters[4].Value);
Utility.SQLHelper db = new Utility.SQLHelper();
db.ExecuteNonQuery(sql, parameters, CommandType.Text);
int id = Convert.ToInt32(parameters[4].Value);
return(id);
}
/// <summary>
/// 删除指点字段的记录
/// </summary>
public static int Work <T>(T model, string target)
{
Utility.SQLHelper db = new Utility.SQLHelper();
Type type = model.GetType();
string selectStr = "";
PropertyInfo[] propArray = type.GetProperties();
List <SqlParameter> paramslist = new List <SqlParameter>();
foreach (PropertyInfo pi in propArray)
{
if (pi.Name != target || pi.GetValue(model, null) == null)
{
continue;
}
selectStr += "[" + pi.Name + "]=@" + pi.Name + " and ";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(model, null)));
}
selectStr = selectStr.Substring(0, selectStr.LastIndexOf(" and ", StringComparison.Ordinal));
string sql = string.Format("delete from {0} where {1}", Utility.Tool.ModelNameToTableName(type.Name), selectStr);
SqlParameter[] parameters = paramslist.ToArray();
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
public static int UpdateRemark(int ID, String Remark, String TableName)
{
string sql = String.Format("update {0} set [Remark] = @Remark where [ID]=@ID", TableName);
SqlParameter[] parameters =
{
new SqlParameter("@Remark", Remark),
new SqlParameter("@ID", ID)
};
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
public static int UpdatePaperUrl(int ID, String Url)
{
string sql = String.Format("update Tb_CupProjectModel set [PaperDoc] = @PaperDoc where [ID]=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@PaperDoc", Url),
new SqlParameter("@ID", ID)
};
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
public static int Work(string tableName, int id)
{
Utility.SQLHelper db = new Utility.SQLHelper();
string sql = string.Format("delete from {0} where ID=@ID", tableName);
SqlParameter[] parameters =
{
new SqlParameter("ID", id)
};
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
public static int UpdateProjectStatus(String Status, String TableName, int ID)
{
Utility.SQLHelper db = new Utility.SQLHelper();
String sql = String.Format("Update {0} set [Statu]=@Statu,[DeclarationDate]=@DeclarationDate where [ID]=@ID", TableName);
SqlParameter[] parameters =
{
new SqlParameter("@Statu", Status),
new SqlParameter("@DeclarationDate", System.DateTime.Now),
new SqlParameter("@ID", ID)
};
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
/// <summary>
/// 更新一系列数据
/// </summary>
/// <typeparam name="T">数据库模型</typeparam>
/// <param name="model">模型实例对象</param>
/// <param name="targets">数据查询条件</param>
/// <returns>影响行数</returns>
public static int ChangeSome <T>(T model, string[] targets)
{
Utility.SQLHelper db = new Utility.SQLHelper();
Type type = model.GetType();
string updateStr1 = "";
string updateStr2 = "";
PropertyInfo[] propArray = type.GetProperties();
List <PropertyInfo> lastPis = new List <PropertyInfo>();
List <SqlParameter> paramslist = new List <SqlParameter>();
foreach (string target in targets)
{
lastPis.Add(type.GetProperty(target));
}
foreach (PropertyInfo pi in propArray)
{
if (pi.Name == "ID" || pi.GetValue(model, null) == null)
{
continue;
}
if (lastPis.Contains(pi))
{
continue;
}
updateStr1 += "[" + pi.Name + "]=@" + pi.Name + ",";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(model, null)));
}
foreach (PropertyInfo lastPi in lastPis.ToArray())
{
updateStr2 = "[" + lastPi.Name + "]=@" + lastPi.Name + " and ";
paramslist.Add(new SqlParameter(lastPi.Name, lastPi.GetValue(model, null)));
}
updateStr1 = updateStr1.TrimEnd(',');
updateStr2 = updateStr2.Substring(0, updateStr2.LastIndexOf(" and ", StringComparison.Ordinal));
string sql = string.Format("update {0} set {1} where {2}", Utility.Tool.ModelNameToTableName(type.Name), updateStr1, updateStr2);
SqlParameter[] parameters = paramslist.ToArray();
return(db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text));
}
/// <summary>
/// 插入一列数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Models"></param>
/// <returns></returns>
public static int CreateList <T>(List <T> models)
{
if (models.Count <= 0)
{
return(0);
}
List <string> SqlList = new List <string>();
List <SqlParameter[]> ParameterList = new List <SqlParameter[]>();
for (int i = 0; i < models.Count; i++)
{
Type type = models[0].GetType();
string insertStr1 = "";
string insertStr2 = "";
PropertyInfo[] propArray = type.GetProperties();
List <SqlParameter> paramslist = new List <SqlParameter>();
foreach (PropertyInfo pi in propArray)
{
if (String.Compare(pi.Name, "ID", true) == 0 || pi.GetValue(models[i], null) == null)
{
continue;
}
insertStr1 += "[" + pi.Name + "],";
insertStr2 += "@" + pi.Name + ",";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(models[i], null)));
}
insertStr1 = insertStr1.TrimEnd(',');
insertStr2 = insertStr2.TrimEnd(',');
String tableName = "Tb_" + type.Name;
string sql = string.Format("insert into {0}({1}) values({2})", tableName, insertStr1, insertStr2);
SqlParameter[] parameters = paramslist.ToArray();
SqlList.Add(sql);
ParameterList.Add(parameters);
}
Utility.SQLHelper db = new Utility.SQLHelper();
return(db.ExecuteNonQuery(SqlList, ParameterList));
}
public static int WorkList(List <String> TableList, int ProjectID)
{
if (TableList.Count <= 0)
{
return(0);
}
Utility.SQLHelper db = new Utility.SQLHelper();
List <SqlParameter[]> ParameterList = new List <SqlParameter[]>();
List <String> SqlList = new List <String>();
for (int i = 0; i < TableList.Count; i++)
{
SqlParameter[] parameters =
{
new SqlParameter("ProjectID", ProjectID)
};
String sql = String.Format("delete from {0} where ProjectID=@ProjectID", TableList[i]);
ParameterList.Add(parameters);
SqlList.Add(sql);
}
return(db.ExecuteNonQuery(SqlList, ParameterList));
}
public static int CreateOneReturnID <T>(T models)
{
Type type = models.GetType();
string insertStr1 = "";
string insertStr2 = "";
PropertyInfo[] propArray = type.GetProperties();
List <SqlParameter> paramslist = new List <SqlParameter>();
foreach (PropertyInfo pi in propArray)
{
if (String.Compare(pi.Name, "ID", true) == 0 || pi.GetValue(models, null) == null)
{
continue;
}
insertStr1 += "[" + pi.Name + "],";
insertStr2 += "@" + pi.Name + ",";
paramslist.Add(new SqlParameter(pi.Name, pi.GetValue(models, null)));
}
paramslist.Add(new SqlParameter("id", SqlDbType.Int));
insertStr1 = insertStr1.TrimEnd(',');
insertStr2 = insertStr2.TrimEnd(',');
String tableName = "Tb_" + type.Name;
string sql = string.Format("insert into {0}({1}) values({2});select @id=SCOPE_IDENTITY()", tableName, insertStr1, insertStr2);
SqlParameter[] parameters = paramslist.ToArray();
parameters[parameters.Length - 1].Direction = ParameterDirection.Output;
Utility.SQLHelper db = new Utility.SQLHelper();
db.ExecuteNonQuery(sql, parameters, System.Data.CommandType.Text);
int id = Convert.ToInt32(parameters[parameters.Length - 1].Value);
return(id);
}