/// <summary>
/// 获取查询结果的数量,传递一个SQL语句参数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int getResultCount(string sql)
{
reader = DBHelper.GetReader(sql);
int i = 0;
while (reader.Read())
{
i++;
}
return(i);
}
public static T_User GetT_UserByUsername(string username)
{
string sql = "SELECT * FROM T_User WHERE Username = @Username";
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Username", username));
if (reader.Read())
{
T_User t_User = new T_User();
try{
t_User.User_id = (int)reader["user_id"];
}catch
{}
try{
t_User.Username = (string)reader["username"];
}catch
{}
try{
t_User.Password = (string)reader["password"];
}catch
{}
try{
t_User.Role_id = (int)reader["role_id"];
}catch
{}
try{
t_User.Status = (bool)reader["status"];
}catch
{}
try{
t_User.Create_date = (DateTime)reader["create_date"];
}catch
{}
reader.Close();
return t_User;
}
else
{
reader.Close();
return null;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
/// <summary>
/// 获取查询结果的条目数量
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int getResultCount(string sql, params SqlParameter[] values)
{
reader = DBHelper.GetReader(sql, values);
int i = 0;
while (reader.Read())
{
i++;
}
DBHelper.ColseReader();
return(i);
}
public static List <int> FindByNameArr(string name)
{
List <int> listid = new List <int>();
string sql = string.Format("select * from projectinfo where projectName like '%{0}%'", name);
OleDbDataReader odr = DBHelper.GetReader(sql);
while (odr.Read())
{
listid.Add(Convert.ToInt32(odr["projectid"]));
}
odr.Close();
return(listid);
}
public static List <int> FindByNameArr(string name)
{
List <int> userid = new List <int>();
string sql = string.Format("select * from exceptionstype where typename like '%{0}%'", name);
OleDbDataReader odr = DBHelper.GetReader(sql);
while (odr.Read())
{
userid.Add(Convert.ToInt32(odr["typeid"]));
}
odr.Close();
return(userid);
}
/// <summary>
/// 返回类型ID
/// </summary>
/// <param name="typeName"></param>
/// <returns></returns>
public static string getIDbyName(string typeName)
{
string typid = "";
string sql = "select typeid from exceptionstype where typename='" + typeName + "'";
OleDbDataReader odr = DBHelper.GetReader(sql);
while (odr.Read())
{
typid = odr["typeid"].ToString();
}
odr.Close();
return(typid);
}
/// <summary>
/// 返回记录总数
/// </summary>
/// <param name="whereStr">查询条件</param>
/// <returns></returns>
public static int returnCount(string whereStr)
{
int count = 0;
string sql = "select count (*) as counts from exceptionsinfo where " + whereStr;
OleDbDataReader odr = DBHelper.GetReader(sql);
while (odr.Read())
{
count = Convert.ToInt32(odr["counts"]);
}
odr.Close();
return(count);
}
public static T_Menu GetT_MenuById(int id)
{
string sql = "SELECT * FROM T_Menu WHERE Id = @Id";
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Id", id));
if (reader.Read())
{
T_Menu t_Menu = new T_Menu();
try{
t_Menu.Id = (int)reader["Id"];
}catch
{}
try{
t_Menu.Menu_name = (string)reader["menu_name"];
}catch
{}
try{
t_Menu.Menu_url = (string)reader["menu_url"];
}catch
{}
try{
t_Menu.Status = (bool)reader["status"];
}catch
{}
try{
t_Menu.Parent_menu_id = (int)reader["parent_menu_id"];
}catch
{}
reader.Close();
return(t_Menu);
}
else
{
reader.Close();
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static T_Accessory GetT_AccessoryById(int id)
{
string sql = "SELECT * FROM T_Accessories WHERE Id = @Id";
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Id", id));
if (reader.Read())
{
T_Accessory t_Accessory = new T_Accessory();
try{
t_Accessory.Id = (int)reader["Id"];
}catch
{}
try{
t_Accessory.Acc_category = (string)reader["acc_category"];
}catch
{}
try{
t_Accessory.Acc_name = (string)reader["acc_name"];
}catch
{}
try{
t_Accessory.Acc_url = (string)reader["acc_url"];
}catch
{}
try{
t_Accessory.Status = (bool)reader["status"];
}catch
{}
reader.Close();
return(t_Accessory);
}
else
{
reader.Close();
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
/// <summary>
/// 根据用户的名字获取用户的所有信息,保存在User类中,返回至调用者,如果没有查到返回null
/// </summary>
/// <param name="_user_Name"></param>
/// <returns></returns>
public static User getUser(string _user_Name)
{
User user = new User();
string sql = "select * from [User] where [User_Name] ='" + _user_Name + "'";
SqlDataReader reader = DBHelper.GetReader(sql);
while (reader.Read())
{
user.User_Id = (int)reader["User_Id"];
user.User_Name = (string)reader["User_Name"];
user.User_Pwd = (string)reader["User_Pwd"];
user.User_Admin = (int)reader["User_Admin"];
}
DBHelper.ColseReader();
return(user);
}
public static List <ProjectInfo> getValue(string sql)
{
List <ProjectInfo> list = new List <ProjectInfo>();
OleDbDataReader odr = DBHelper.GetReader(sql);
while (odr.Read())
{
ProjectInfo pi = new ProjectInfo();
pi.ProjectID = Convert.ToInt32(odr["ProjectID"]);
pi.ProjectName = odr["ProjectName"].ToString();
pi.Province = odr["Province"].ToString();
pi.Remarks = odr["remarks"].ToString();
list.Add(pi);
}
odr.Close();
return(list);
}
public static List <ExceptionsType> getValue(string sql)
{
List <ExceptionsType> list = new List <ExceptionsType>();
OleDbDataReader odr = DBHelper.GetReader(sql);
while (odr.Read())
{
ExceptionsType et = new ExceptionsType();
et.TypeID = Convert.ToInt32(odr["TypeID"]);
et.TypeName = odr["TypeName"].ToString();
et.Category = odr["Category"].ToString();
et.Remarks = odr["remarks"].ToString();
list.Add(et);
}
odr.Close();
return(list);
}
public static string findByID(int id)
{
string name = "";
string sql = "select * from ExceptionsType where TypeID=@ID";
OleDbParameter[] para = new OleDbParameter[]
{
new OleDbParameter("@ID", id)
};
OleDbDataReader odr = DBHelper.GetReader(sql, para);
if (odr.Read())
{
name = odr["typeName"].ToString();
odr.Close();
}
return(name);
}
public static List <UserInfo> getValue(string sql)
{
List <UserInfo> list = new List <UserInfo>();
OleDbDataReader odr = DBHelper.GetReader(sql);
while (odr.Read())
{
UserInfo ui = new UserInfo();
ui.UserID = Convert.ToInt32(odr["userid"]);
ui.UserName = odr["username"].ToString();
ui.Province = odr["Province"].ToString();
ui.Phone = odr["phone"].ToString();
ui.Remarks = odr["remarks"].ToString();
list.Add(ui);
}
odr.Close();
return(list);
}
public static T_Department GetT_DepartmentById(int id)
{
string sql = "SELECT * FROM T_Department WHERE Id = @Id";
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Id", id));
if (reader.Read())
{
T_Department t_Department = new T_Department();
try{
t_Department.Id = (int)reader["Id"];
}catch
{}
try{
t_Department.Depart_name = (string)reader["depart_name"];
}catch
{}
try{
t_Department.Depart_addr = (string)reader["depart_addr"];
}catch
{}
try{
t_Department.Depart_status = (bool)reader["depart_status"];
}catch
{}
reader.Close();
return(t_Department);
}
else
{
reader.Close();
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static T_District GetT_DistrictById(int id)
{
string sql = "SELECT * FROM T_District WHERE Id = @Id";
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Id", id));
if (reader.Read())
{
T_District t_District = new T_District();
try{
t_District.Id = (int)reader["Id"];
}catch
{}
try{
t_District.District_name = (string)reader["district_name"];
}catch
{}
try{
t_District.District_level = (int)reader["district_level"];
}catch
{}
try{
t_District.Parent_district = (int)reader["parent_district"];
}catch
{}
reader.Close();
return(t_District);
}
else
{
reader.Close();
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static T_BaseInfo GetT_BaseInfoById(int id)
{
string sql = "SELECT * FROM T_BaseInfo WHERE Id = @Id";
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Id", id));
if (reader.Read())
{
T_BaseInfo t_BaseInfo = new T_BaseInfo();
try{
t_BaseInfo.Id = (int)reader["Id"];
}catch
{}
try{
t_BaseInfo.Config_name = (string)reader["config_name"];
}catch
{}
try{
t_BaseInfo.Config_value = (string)reader["config_value"];
}catch
{}
try{
t_BaseInfo.Config_status = (bool)reader["config_status"];
}catch
{}
reader.Close();
return(t_BaseInfo);
}
else
{
reader.Close();
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static int FindByName(string name)
{
//string sql = "select * from userinfo where userName='******'";
int userid = -1;
string sql = "select * from userinfo where userName=@userName";
OleDbParameter[] para = new OleDbParameter[]
{
new OleDbParameter("@userName", name)
};
OleDbDataReader odr = DBHelper.GetReader(sql, para);
if (odr.Read())
{
userid = Convert.ToInt32(odr["userid"]);
odr.Close();
}
return(userid);
}
/// <summary>
/// 根据新闻的Id获取新闻类
/// </summary>
/// <param name="_newId"></param>
/// <returns></returns>
public static New getNew(int _newId)
{
New news = new MDL.New();
string sql = "select * from [New] where [New_Id]='" + _newId + "'";
SqlDataReader reader = DBHelper.GetReader(sql);
while (reader.Read())
{
news.Create_Time = (DateTime)reader["Create_Time"];
news.Is_Show = (int)reader["Is_Show"];
news.New_Content = (string)reader["New_Content"];
news.New_Id = _newId;
news.New_Image = (string)reader["New_Image"];
news.New_Title = (string)reader["New_Title"];
news.New_Type_Id = (int)reader["New_Type_Id"];
}
DBHelper.ColseReader();
return(news);
}
/// <summary>
///查询最大New_Id的第_order个的新闻类型为_newType 的New类
/// </summary>
/// <param name="_order"></param>
/// <param name="_newType"></param>
/// <returns></returns>
public static New selNewOrder(int _order, int _newType)
{
New news = new New();
string sql = "select * from New where New_Type_Id='" + _newType + "' Order By New_Id desc";
SqlDataReader reader = DBHelper.GetReader(sql);
for (int i = 0; i < _order; i++)
{
reader.Read();
}
news.Create_Time = (DateTime)reader["Create_Time"];
news.Is_Show = (int)reader["Is_Show"];
news.New_Content = (string)reader["New_Content"];
news.New_Id = (int)reader["New_Id"];
news.New_Image = (string)reader["New_Image"];
news.New_Title = (string)reader["New_Title"];
news.New_Type_Id = (int)reader["New_Type_Id"];
DBHelper.ColseReader();
return(news);
}
public static T_Role GetT_RoleById(int id)
{
string sql = "SELECT * FROM T_Role WHERE Id = @Id";
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Id", id));
if (reader.Read())
{
T_Role t_Role = new T_Role();
try{
t_Role.Id = (int)reader["Id"];
}catch
{}
try{
t_Role.Role_name = (string)reader["role_name"];
}catch
{}
try{
t_Role.Role_pris = (bool)reader["role_pris"];
}catch
{}
reader.Close();
return(t_Role);
}
else
{
reader.Close();
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static T_RoleMenuRelation GetT_RoleMenuRelationById(int id)
{
string sql = "SELECT * FROM T_RoleMenuRelations WHERE Id = @Id";
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Id", id));
if (reader.Read())
{
T_RoleMenuRelation t_RoleMenuRelation = new T_RoleMenuRelation();
try{
t_RoleMenuRelation.Id = (int)reader["Id"];
}catch
{}
try{
t_RoleMenuRelation.Role_id = (int)reader["role_id"];
}catch
{}
try{
t_RoleMenuRelation.Menu_id = (int)reader["menu_id"];
}catch
{}
reader.Close();
return(t_RoleMenuRelation);
}
else
{
reader.Close();
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
private static List <ExceptionsInfo> GetValue(string sql)
{
List <ExceptionsInfo> list = new List <ExceptionsInfo>();
OleDbDataReader odr = DBHelper.GetReader(sql);
while (odr.Read())
{
ExceptionsInfo ei = new ExceptionsInfo();
ei.ID = Convert.ToInt32(odr["ID"]);
ei.UserID = odr["userid"].ToString();
ei.ProjectID = odr["projectid"].ToString();
ei.TypeID = odr["typeid"].ToString();
ei.ExcepitionID = odr["exceptionid"].ToString();
ei.ExcepitionName = odr["ExceptionName"].ToString();
ei.ExcepitionDescri = odr["ExceptionDescription"].ToString();
ei.Solution = odr["solution"].ToString();
ei.Remarks = odr["remarks"].ToString();
list.Add(ei);
}
odr.Close();
return(list);
}
public static T_Investigate GetT_InvestigateByBuilding_name(string building_name)
{
string sql = "SELECT * FROM T_Investigates WHERE Building_name = @Building_name";
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Building_name", building_name));
if (reader.Read())
{
T_Investigate t_Investigate = new T_Investigate();
try{
t_Investigate.Id = (int)reader["id"];
}catch
{}
try{
t_Investigate.Building_id = (string)reader["building_id"];
}catch
{}
try{
t_Investigate.Building_name = (string)reader["building_name"];
}catch
{}
try{
t_Investigate.Building_owner = (string)reader["building_owner"];
}catch
{}
try{
t_Investigate.Building_user = (string)reader["building_user"];
}catch
{}
try{
t_Investigate.Start_date = (DateTime)reader["start_date"];
}catch
{}
try{
t_Investigate.Finish_date = (DateTime)reader["finish_date"];
}catch
{}
try{
t_Investigate.Design_corp = (string)reader["design_corp"];
}catch
{}
try{
t_Investigate.Construct_corp = (string)reader["construct_corp"];
}catch
{}
try{
t_Investigate.Total_area = (int)reader["total_area"];
}catch
{}
try{
t_Investigate.Floors = (int)reader["floors"];
}catch
{}
try{
t_Investigate.Struct_type = (string)reader["struct_type"];
}catch
{}
try{
t_Investigate.Building_purpose = (string)reader["building_purpose"];
}catch
{}
try{
t_Investigate.Contacts = (string)reader["contacts"];
}catch
{}
try{
t_Investigate.Contact_numbers = (string)reader["contact_numbers"];
}catch
{}
try{
t_Investigate.Problems = (string)reader["problems"];
}catch
{}
try{
t_Investigate.Checkup_suggestions = (string)reader["checkup_suggestions"];
}catch
{}
try{
t_Investigate.Rectifications = (string)reader["rectifications"];
}catch
{}
try{
t_Investigate.Remarks = (string)reader["remarks"];
}catch
{}
try{
t_Investigate.Create_user = (string)reader["create_user"];
}catch
{}
try{
t_Investigate.Create_datetime = (DateTime)reader["create_datetime"];
}catch
{}
try{
t_Investigate.Update_user = (string)reader["update_user"];
}catch
{}
try{
t_Investigate.Update_time = (DateTime)reader["update_time"];
}catch
{}
try{
t_Investigate.Investigate_category = (string)reader["investigate_category"];
}catch
{}
reader.Close();
return(t_Investigate);
}
else
{
reader.Close();
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static T_Checkup GetT_CheckupById(int id)
{
string sql = "SELECT * FROM T_Checkups WHERE Id = @Id";
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Id", id));
if (reader.Read())
{
T_Checkup t_Checkup = new T_Checkup();
try{
t_Checkup.Id = (int)reader["Id"];
}catch
{}
try{
t_Checkup.Investigate_id = (int)reader["investigate_id"];
}catch
{}
try{
t_Checkup.Off_grades = (string)reader["off_grades"];
}catch
{}
try{
t_Checkup.Rank_scores = (string)reader["rank_scores"];
}catch
{}
try{
t_Checkup.Anti_seismics = (string)reader["anti_seismics"];
}catch
{}
try{
t_Checkup.Repairables = (string)reader["repairables"];
}catch
{}
try{
t_Checkup.Checkup_suggestions = (string)reader["checkup_suggestions"];
}catch
{}
try{
t_Checkup.Evaluate_suggestions = (string)reader["evaluate_suggestions"];
}catch
{}
try{
t_Checkup.Solutions = (string)reader["solutions"];
}catch
{}
try{
t_Checkup.Remakes = (string)reader["remakes"];
}catch
{}
try{
t_Checkup.Create_user = (string)reader["create_user"];
}catch
{}
try{
t_Checkup.Create_time = (DateTime)reader["create_time"];
}catch
{}
try{
t_Checkup.Update_user = (string)reader["update_user"];
}catch
{}
try{
t_Checkup.Update_time = (DateTime)reader["update_time"];
}catch
{}
try{
t_Checkup.Status = (bool)reader["status"];
}catch
{}
try{
t_Checkup.Checkup_category = (string)reader["checkup_category"];
}catch
{}
reader.Close();
return(t_Checkup);
}
else
{
reader.Close();
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
