/// <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>
/// 获取养分类型
/// </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);
}
/// <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>
/// 返回执行添加、删除情况
/// </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>
/// <returns></returns>
public static List <string> initConnect()
{
string strcon = "server=localhost;Initial Catalog=master;Integrated Security=true;";
List <string> list_table = new List <string>();
SqlConnection con = new SqlConnection(strcon);
con.Open();
string strsql = "select Name from SysDatabases Order by Name";
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>
/// 获取作物类型
/// </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);
}
}
