public DataSet GetReportSingle(string name = "")
{
string cmd = "SELECT * FROM TestData where Name = @Name ORDER BY ID DESC ";
DataSet ds = SQLiteHelper.ExecuteDataSet(cmd, new SQLiteParameter("@Name", name));
return(ds);
}
//根据名称，型号，查询与一个杆件相关的一行所有记录，返回一个datatab
public DataTable SearchAll(string Name, string Model)
{
string sqlSearch = "select fitting_id,name,model,specifications,material,the_weight from tb_materialkind where name='" + Name + "' and model='" + Model + "'";
SQLiteConnection conn = sqliteConnectionBase.connectToDatabase();
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sqlSearch, null).Tables[0];
return(dt);
}
//这个函数用来查询风压值不为空的城市
public DataTable SearchCity(string province)
{
SQLiteConnection conn = _connBase.connectToDatabase();
string sql = "select con_city from tb_windpress where w0 not null and con_province='" + province + "'";
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sql, null).Tables[0];
return(dt);
}
public DataTable GetAnchorModelFromTubeCharacter()
{
SQLiteConnection conn = _connBase.connectToDatabase();
string sql = "select model from tb_tubeCharacter where model not null";
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sql, null).Tables[0];
return(dt);
}
public DataTable GetAnchorModelFromJSteel()
{
SQLiteConnection conn = _connBase.connectToDatabase();
string sql = "select model from tb_jsteel ";
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sql, null).Tables[0];
return(dt);
}
/// <summary>
/// 查钢管
/// </summary>
/// <param name="anchorModel"></param>
/// <returns></returns>
public DataTable SearchAnchorFromTube(string anchorModel)
{
SQLiteConnection conn = new SQLiteConnectionBase().connectToDatabase();
string sql = "select A , radius from tb_tubeCharacter where model ='" + anchorModel + "'";
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sql, null).Tables[0];
return(dt);
}
public DataSet GetreportLimitN(int n)
{
List <Tuple <string, string> > datas = new List <Tuple <string, string> >();
//string cmd = "SELECT * FROM report ORDER BY ID DESC LIMIT 0," + n.ToString();
string cmd = "SELECT ID, Name, Path, CreateTime,ModifyTime,AccessTime FROM TestData ORDER BY ID DESC LIMIT 0," + n.ToString();
DataSet ds = SQLiteHelper.ExecuteDataSet(cmd);
return(ds);
}
//这是根据“立杆”“横杆”等名称查询可选杆件类型的函数，返回一个dataset
//一定执行成功
public DataTable SearchModel(string name)
{
string str = string.Format("select model from tb_materialkind where name = '{0}'", name.Trim());//trim防止空格出现
SQLiteConnection conn = sqliteConnectionBase.connectToDatabase();
DataTable dt = null;
dt = SQLiteHelper.ExecuteDataSet(conn, str, null).Tables[0];//给定的string表是写死的，只要写界面的人脑洞不大，一定可以执行成功
return(dt);
}
/// <summary>
/// 查询连墙件fi值，钢管
/// </summary>
/// <param name="tens"></param>
/// <param name="units"></param>
/// <returns></returns>
public double GetAnchorTubeFi(int tens, int units)
{
string sqlCmd = "select [" + units + "] from tb_q235fi where λ=" + tens;
SQLiteConnection conn = new SQLiteConnectionBase().connectToDatabase();
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sqlCmd, null).Tables[0];
double fi = (double)dt.Rows[0][0];
return(fi);
}
/// <summary>
/// 根据ID号查询记录，返回一个dataTable，成功返回有数据的表，失败返回空表
/// </summary>
/// <returns></returns>
public DataTable SearchInfo(int pro_Id)
{
string sqlSearchInfo = "select pro_name,pro_type,con_province,con_city,unit,con_unit,sup_unit,con_area,con_height,des_unit from tb_projinfo"
+ " where pro_id = " + pro_Id;
SQLiteConnection conn = _connBase.connectToDatabase();
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sqlSearchInfo, null).Tables[0];
return(dt);
}
public DataTable SearchPara(int pro_Id)
{
string sqlSearch = "select sca_type,con_layers,act_layers,soil_types,rough_level,cha_value,pad_area,anchor_style,anchor_type,anchor_model,anchor_connect,sca_situation,bui_status,bui_distance ,per_brace,per_level,per_set,la,lb,h,fast_num,fitting_model,step_num from tb_scaffoldPara"
+ " where pro_id = " + pro_Id;
SQLiteConnection conn = con.connectToDatabase();
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sqlSearch, null).Tables[0];
return(dt);
}
public DataTable SearchMateriallibRec(int pro_Id)
{
string sql = "select lb.[fitting_id], m.[name],m.[model],m.[specifications],m.[material],m.[the_weight],lb.[inventory] " +
" from tb_materialkind m,tb_materiallib lb " +
" where m.[fitting_id] = lb.[fitting_id] and pro_id = " + pro_Id;
SQLiteConnection conn = sqliteConnectionBase.connectToDatabase();
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sql, null).Tables[0];
return(dt);
}
//这个函数用来查询tb_windpress中的省份
public DataTable SearchProvince()
{
SQLiteConnection conn = _connBase.connectToDatabase();
//以con_province分组的原因是存在很多正常冗余的数据
//以rowid大小排序的原因是要复现“省”一列在规范内出现的顺序
string sqlSearchProvince = "select con_province from tb_windpress group by con_province order by rowid";
//搜索不进行参数化
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sqlSearchProvince, null).Tables[0];
//检查表，一定能执行正确
return(dt);
}
public DataTable SearchLog()
{
//这儿用grupby可以暂时实现保存文件时冲掉已有工程文件，在最近文件里旧的工程文件也被冲掉
//groupby会按文件名和文件路径将数据库中冗余的内容压缩、
//但压缩后无论order by如何，均显示rec_time最晚的一条记录
//利用max（rec_time）保证压缩后的时间是最晚的
string sqlSearchLog = "select pro_name,sto_path,max(rec_time) from tb_projlog group by sto_path order by rec_time desc limit 0,5";
SQLiteConnection conn = con.connectToDatabase();
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sqlSearchLog, null).Tables[0];
return(dt);
}
/// <summary>
/// 封装在TFS_
/// </summary>
/// <param name="province">省份</param>
/// <param name="city">城市</param>
/// <returns>KN/M2</returns>
public double Searchω0(string province, string city)
{
double _ω0 = -1;
string sqlCmd = "select w0 from tb_windpress where con_province='" + province + "' and con_city='" + city + "'";
SQLiteConnection conn = new SQLiteConnectionBase().connectToDatabase();
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sqlCmd, null).Tables[0];
if (dt.Columns.Count > 0)
{
_ω0 = (double)dt.Rows[0][0];
}
return(_ω0);
}
/// <summary>
/// 查询材料质量以及其特性的函数
/// </summary>
/// <param name="la">mm</param>
/// <param name="lb">mm</param>
/// <param name="h">mm</param>
/// <param name="fitting_model"></param>
/// <returns></returns>
public DataTable SearchFitting(int la, int lb, int h, string fitting_model)
{
string sqlCmd = " select m.[name],m.[model],m.[specifications],m.[material],m.[the_weight],c.[A],c.[I],c.[W],c.[radius] " +
" from tb_materialkind m,tb_tubeCharacter c " +
" where m.[model] like '" + fitting_model + "%' and ( " +
" (m.[name] = '立杆' and m.[model] like '%-" + h + "') or " +
" (m.[name] = '竖向斜杆' and m.[model] like '%-" + la + "×" + h + "') or " +
" (m.[name] = '水平斜杆' and m.[model] like '%-" + lb + "×" + la + "') or " +
" (m.[name] = '横向水平杆' and m.[model] like '%-" + lb + "') or " +
" (m.[name] = '纵向水平杆' and m.[model] like '%-" + la + "')" +
" ) and m.[character_id] = c.[character_id]";
SQLiteConnection conn = new SQLiteConnectionBase().connectToDatabase();
DataTable dt = SQLiteHelper.ExecuteDataSet(conn, sqlCmd, null).Tables[0];
return(dt);
}
public List <byte[]> GetReport()
{
List <byte[]> list = new List <byte[]>();
string cmd = "SELECT * FROM report ";
DataSet ds = SQLiteHelper.ExecuteDataSet(cmd);
if (ds == null || ds.Tables.Count == 0)
{
return(null);
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
var b = ds.Tables[0].Rows[i]["Content"] as byte[];
list.Add(b);
}
return(list);
}