/// <summary> /// 返回作业区符合条件的非Soil表 /// </summary> /// <param name="tablename"></param> /// <param name="datetime"></param> /// <param name="cropcode"></param> /// <returns></returns> public static DataTable Return_TownTable(string tablename, string datetime, string cropcode) { string sqlstr = ""; switch (tablename) { case "CHLOROPHYLLRETRIEVAL": sqlstr = "select TOWNCODE,MONITORTIME,CROP_CODE,CHLOROPHYLLVALUE FROM CHLOROPHYLLRETRIEVAL_TOWN where MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "'"; break; case "CROPYIELD": sqlstr = "select TOWNCODE,MONITORTIME,CROP_CODE,CROP_YIELD FROM CROPYIELD_TOWN where MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "'"; break; case "WATERRETRIEVAL": sqlstr = "select TOWNCODE,MONITORTIME,CROP_CODE,WATERVALUE FROM WATERRETRIEVAL_TOWN where MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "'"; break; case "MATUREPERIOD": sqlstr = "select TOWNCODE,MONITORTIME,CROP_CODE,MATURE_PERIOD FROM MATUREPERIOD_TOWN where MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "'"; break; } SqlConnection con = DataBaseOperate.getSqlCon(); SqlDataAdapter sa = new SqlDataAdapter(sqlstr, con); DataTable dt = new DataTable(); sa.Fill(dt); return(Convert_TableValue(Convert_TableName(dt))); }
public static DataTable get_VillPlotMulitData(string strsql) { SqlConnection con = DataBaseOperate.getSqlCon(); SqlDataAdapter sa = new SqlDataAdapter(strsql, con); DataTable dt = new DataTable(); sa.Fill(dt); return(convert_PlotTableValue(return_CHPlotTable(dt))); }
/// <summary> /// 通用的执行Adapter的sql语句返回DataTable /// </summary> /// <param name="strsql"></param> /// <returns></returns> public static DataTable return_DataTable(string strsql) { SqlConnection con = DataBaseOperate.getSqlCon(); SqlDataAdapter sa = new SqlDataAdapter(strsql, con); DataTable dt = new DataTable(); sa.Fill(dt); return(dt); }
/// <summary> /// 获取农场Code /// </summary> /// <param name="code"></param> /// <returns></returns> public static int getCountyCount() { string strsql = "select count(distinct QXCode) from COUNTY"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); int count = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); return(count); }
/// <summary> /// 获取NutrientCount /// </summary> /// <param name="plotid"></param> /// <returns></returns> public static int get_NutrientCount() { List <string> list = new List <string>(); string strsql = "select count(distinct NUTRIENT_CODE) from SOILNUTRIENT_CODE where DELFLAG='1'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); int count = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); return(count); }
/// <summary> /// 获取CropCount /// </summary> /// <param name="plotid"></param> /// <returns></returns> public static int get_CropCount() { List <string> list = new List <string>(); string strsql = "select count(distinct CropCode) from CROPINFO where DELFLAG=1"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); int count = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); return(count); }
/// <summary> /// 获取TownCount /// </summary> /// <param name="plotid"></param> /// <returns></returns> public static int get_TownCount() { List <string> list = new List <string>(); string strsql = "select count(distinct GLQ) from PLOT_DKINFO"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); int count = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); return(count); }
/// <summary> /// 汇总到Town_SOILNUTRIENT /// </summary> /// <param name="VILLAGECODE"></param> /// <param name="MONITORTIME"></param> /// <param name="CROP_CODE"></param> /// <param name="SOIL_NUTRIENT"></param> /// <returns></returns> public static int insert_County_SOILNUTRIENT(String procedure_name, SqlParameter[] param) { SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(procedure_name, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(param); int reslut = cmd.ExecuteNonQuery(); con.Close(); return(reslut); }
/// <summary> /// 数据入库 /// </summary> /// <param name="strsql"></param> /// <param name="param"></param> public static int InsertDatabase(string sqlProcedure, SqlParameter[] param) { SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(sqlProcedure, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(param); int result = cmd.ExecuteNonQuery(); con.Close(); return(result); }
/// <summary> /// 获取处在某一时间段内的时间个数,用于循环汇总 /// </summary> /// <param name="strsql"></param> /// <param name="param"></param> /// <returns></returns> public static int getIncludeTimeCount(string strsql, SqlParameter[] param) { //string strsql = "select count(MONITORTIME) from MONITORTIME between @time1 and @time2"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); cmd.Parameters.AddRange(param); int count = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); cmd.Parameters.Clear(); return(count); }
/// <summary> /// 获取到County的汇总值 /// </summary> /// <param name="procedure_name"></param> /// <param name="param"></param> /// <returns></returns> public static string get_CountyValue(String procedure_name, SqlParameter[] param) { SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(procedure_name, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(param); cmd.ExecuteNonQuery(); string value = param[3].Value.ToString(); con.Close(); return(value); }
public static DataTable get_VillPlotData(SqlParameter[] param) { string strsql = "select * from VI_PLOT where PLOTID=@name and VI_TYPE=@VI_TYPE and VI_STATYPE=@VI_STATYPE and MONITORTIME between @date1 and @date2 and SENSORTYPE=@SENSORTYPE order by MONITORTIME asc"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); cmd.Parameters.AddRange(param); SqlDataAdapter sa = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sa.Fill(dt); return(convert_PlotTableValue(return_CHPlotTable(dt))); }
public static List <string> ReturnMultiValue(string sql) { List <string> nodeName = new List <string>(); SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(sql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { nodeName.Add(reader[0].ToString()); } con.Close(); return(nodeName); }
/// <summary> /// 通过Reader方法获取数据列表 /// </summary> /// <param name="strsql"></param> /// <returns></returns> public static List <string> return_DataList(string strsql) { List <string> list_table = new List <string>(); SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { list_table.Add(reader[0].ToString()); } con.Close(); return(list_table); }
/// <summary> /// 非Soil图片入库 /// </summary> /// <param name="list_picname"></param> public static void InsertPicture(string[] list_picname) { byte[] buffer = Return_Buffer(); //string strSql = "insert into MONITOR_PICTURE(INDICATOR_NAME,CROP_CODE,MONITORTIME,PIC_MAP) values(@INDICATOR_NAME,@CROP_CODE,@MONITORTIME,@PIC_MAP)"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = new SqlCommand("insert_Picture", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@INDICATOR_NAME", list_picname[0]); cmd.Parameters.AddWithValue("@CROP_CODE", DataBaseOperate.getCrop_Code(list_picname[1])); cmd.Parameters.AddWithValue("@MONITORTIME", Convert.ToDateTime(list_picname[2]).ToShortDateString()); cmd.Parameters.AddWithValue("@PIC_MAP", buffer); cmd.ExecuteNonQuery(); }
/// <summary> /// 获取NutrientCode /// </summary> /// <param name="plotid"></param> /// <returns></returns> public static List <string> get_NutrientCode() { List <string> list = new List <string>(); string strsql = "select distinct NUTRIENT_CODE from SOILNUTRIENT_CODE where DELFLAG='1'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { list.Add(reader[0].ToString()); } con.Close(); return(list); }
/// <summary> /// 指标类型源 /// </summary> /// <returns></returns> public static List <string> get_chartIndexType() { List <string> list = new List <string>(); SqlConnection con = DataBaseOperate.getSqlCon(); string strsql = "select distinct VINAME from VITYPEINFO"; SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader read = cmd.ExecuteReader(); while (read.Read()) { list.Add(read[0].ToString()); } con.Close(); return(list); }
/// <summary> /// 根据作物名称获取crop_code /// </summary> /// <param name="name"></param> /// <returns></returns> public static string get_CropCode(string name) { string value = ""; string strsql = "select CropCode from CROPINFO where [CropName]='" + name + "' and DELFLAG=1"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { value = reader[0].ToString(); } con.Close(); return(value); }
/// <summary> /// 获取养分类型 /// </summary> /// <param name="Crop_Code"></param> /// <param name="date1"></param> /// <param name="date2"></param> /// <returns></returns> public static List <string> Get_NuterientCount(string Crop_Code, DateTime date1, DateTime date2) { List <string> list = new List <string>(); string sqlstr = "select distinct NUTRIENT_CODE FROM SOILNUTRIENT_VILLAGE where CROP_CODE='" + Crop_Code + "' and " + "MONITORTIME between '" + date1 + "' and '" + date2 + "'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(sqlstr, con); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { list.Add(dr["NUTRIENT_CODE"].ToString()); } con.Close(); return(list); }
public static string get_TreeTownCode(string name, string upname) { string result = ""; string strsql = string.Format("select TowCode from TOWN where TownName='{0}' and QXCode='{1}'", name, upname); SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader read = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (read.Read()) { result = read[0].ToString(); } con.Close(); return(result); }
/// <summary> /// 获取作物代码和名称,并绑定数据源 /// </summary> /// <returns></returns> public static Dictionary <string, string> setCropSource() { Dictionary <string, string> source = new Dictionary <string, string>(); string strsql = "select [CropCode],[CropName] from CROPINFO where DELFLAG=1"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { source.Add(reader["CropCode"].ToString(), reader["CropName"].ToString()); } con.Close(); return(source); }
/// <summary> /// 获取CountyCode /// </summary> /// <param name="plotid"></param> /// <returns></returns> public static List <string> get_CountyCode() { List <string> list = new List <string>(); string strsql = "select distinct QXCode from COUNTY"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { list.Add(reader[0].ToString()); } con.Close(); return(list); }
/// <summary> /// 获取结果 /// </summary> /// <param name="strsql"></param> /// <param name="param"></param> /// <returns></returns> public static string getResult(string strsql, SqlParameter param) { string result = ""; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); cmd.Parameters.Add(param); SqlDataReader read = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (read.Read()) { result = read[0].ToString(); } con.Close(); return(result); }
/// <summary> /// 返回作业站符合条件的Soil表 /// </summary> /// <param name="tablename"></param> /// <param name="datetime"></param> /// <param name="cropcode"></param> /// <returns></returns> public static DataTable Return_VillageSoilTable(string datetime, string cropcode, string nuterient) { string sqlstr = "select VILLAGECODE,MONITORTIME,CROP_CODE,NUTRIENT_CODE,SOIL_NUTRIENT FROM SOILNUTRIENT_VILLAGE where MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "' and NUTRIENT_CODE='" + nuterient + "'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlDataAdapter sa = new SqlDataAdapter(sqlstr, con); DataTable dt = new DataTable(); sa.Fill(dt); if (dt.Rows.Count != 0) { return(Convert_TableValue(Convert_TableName(dt))); } else { return(null); } }
/// <summary> /// 返回执行添加、删除情况 /// </summary> /// <param name="strsql"></param> /// <returns></returns> public static int return_OperateSucess(string strsql) { SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); //SqlTransaction trans = con.BeginTransaction(); //cmd.Transaction = trans; //try //{ int count = cmd.ExecuteNonQuery(); return(count); //} //catch //{ // trans.Rollback(); // return 0; //} }
/// <summary> /// 获取处在某一时间段内的具体时间,用于汇总传参 /// </summary> /// <param name="strsql"></param> /// <param name="param"></param> /// <returns></returns> public static List <string> getIncludeTime(string strsql, SqlParameter[] param) { List <string> list = new List <string>(); //string strsql = "select MONITORTIME from MONITORTIME between @time1 and @time2"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); cmd.Parameters.AddRange(param); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { list.Add(reader[0].ToString()); } con.Close(); cmd.Parameters.Clear(); return(list); }
/// <summary> /// 将图片流输出为文件 /// </summary> /// <param name="tablename"></param> public static string ExportSoilPicture(string type, string datetime, string cropcode, string nurient) { byte[] imagebytes; string datetimeforamt = datetime.Substring(0, datetime.LastIndexOf(" ")).Replace('/', '-'); string cropName = DataBaseOperate.get_CropCHName(cropcode); string nutrientName = DataBaseOperate.get_NutrientCHName(nurient); string pathname = Application.StartupPath + @"\Image\" + type + "_" + datetimeforamt + "_" + cropName + "_" + nutrientName + ".jpg"; string strSql = "select PIC_MAP from MONITOR_PICTURE where INDICATOR_NAME='" + type + "' and MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "' and NUTRIENT_CODE='" + nurient + "'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = new SqlCommand(strSql, con); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); string value_path = ""; while (reader.Read()) { //获取图片数据 imagebytes = (byte[])reader["PIC_MAP"]; //将内存流格式化为位图 if (imagebytes.Length > 0) { MemoryStream stream = new MemoryStream(imagebytes); if (File.Exists(pathname)) { File.Delete(pathname); } Image image = Image.FromStream(stream); image.Save(pathname); stream.Close(); image.Dispose(); value_path = pathname; } else { value_path = ""; } } con.Close(); return(value_path); }
/// <summary> /// 获取作物类型 /// </summary> /// <param name="tablename"></param> /// <param name="date1"></param> /// <param name="date2"></param> /// <returns></returns> public static List <string> Get_CropCount(string rank, string tablename, DateTime date1, DateTime date2) { List <string> list = new List <string>(); string sqlstr = ""; switch (tablename) { case "CHLOROPHYLLRETRIEVAL": sqlstr = "select distinct CROP_CODE FROM CHLOROPHYLLRETRIEVAL_" + rank + " where MONITORTIME between '" + date1 + "' and '" + date2 + "'"; break; case "CROPYIELD": sqlstr = "select distinct CROP_CODE FROM CROPYIELD_" + rank + " where MONITORTIME between '" + date1 + "' and '" + date2 + "'"; break; case "WATERRETRIEVAL": sqlstr = "select distinct CROP_CODE FROM WATERRETRIEVAL_" + rank + " where MONITORTIME between '" + date1 + "' and '" + date2 + "'"; break; case "MATUREPERIOD": sqlstr = "select distinct CROP_CODE FROM MATUREPERIOD_" + rank + " where MONITORTIME between '" + date1 + "' and '" + date2 + "'"; break; case "SOILNUTRIENT": sqlstr = "select distinct CROP_CODE FROM SOILNUTRIENT_" + rank + " where MONITORTIME between '" + date1 + "' and '" + date2 + "'"; break; } SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(sqlstr, con); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { list.Add(dr["CROP_CODE"].ToString()); } con.Close(); return(list); }
public static Dictionary <string, string> get_VillPlotChartData(SqlParameter[] param) { Dictionary <string, string> dicts = new Dictionary <string, string>(); string strsql = "select CONVERT(varchar(100), MONITORTIME, 111),VI_VALUE from VI_PLOT where PLOTID=@name and VI_TYPE=@VI_TYPE and VI_STATYPE=@VI_STATYPE and MONITORTIME between @date1 and @date2 and SENSORTYPE=@SENSORTYPE order by MONITORTIME asc"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); cmd.Parameters.AddRange(param); SqlDataReader reader = cmd.ExecuteReader(); try { while (reader.Read()) { dicts.Add(reader[0].ToString(), reader[1].ToString()); } return(dicts); } catch (Exception ex) { MessageBox.Show("同一时间段内,存在两条记录!"); return(null); } }