public string GetFieldNameByVariableId(string variableId) { string commandText = @" select b.Formula from tz_Material as a,material_MaterialDetail as b where a.KeyID=B.KeyID and a.OrganizationID=@organizationId and b.VariableId=@variableName"; VariableParams vp = new VariableParams(variableId); string fieldName = ""; using (SqlConnection connection = new SqlConnection(this.connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); command.CommandText = commandText; command.Parameters.Add(new SqlParameter("organizationId", vp.OrganizationId)); command.Parameters.Add(new SqlParameter("variableName", vp.VariableName)); using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read() == true) { fieldName = reader[0].ToString().Trim(); } return(fieldName); } } }
/// <summary> /// 获取数据 /// </summary> /// <param name="variableId"></param> /// <param name="startTime"></param> /// <param name="stopTime"></param> /// <param name="timeSpanInMin"></param> /// <returns></returns> public IDictionary <string, decimal> GetData(string variableId, DateTime startTime, DateTime stopTime, int timeSpanInMin) { DataTable dt = new DataTable(); VariableParams vp = new VariableParams(variableId); // 获取变量对应的列名 string fieldName = GetFieldNameByVariableId(variableId); using (SqlConnection connection = new SqlConnection(this.connectionString)) { SqlCommand command = connection.CreateCommand(); command.CommandText = string.Format(COMMAND_FORMAT, fieldName, timeSpanInMin, FACTORY_DATABASE); command.Parameters.Add(new SqlParameter("startTime", startTime)); command.Parameters.Add(new SqlParameter("stopTime", stopTime)); //command.Parameters.Add(new SqlParameter("organizationId", vp.OrganizationId)); //command.Parameters.Add(new SqlParameter("variableName", vp.VariableName)); using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { adapter.Fill(dt); } } return(Utility.ConvertData(dt)); }
/// <summary> /// /// </summary> /// <param name="variableId"></param> /// <returns>[0]:数据库名 [1]:表格名 [2]:列名</returns> private string[] GetTableNameAndFieldNameByVariableId(string variableId) { string commandFormat = @" SELECT [TableName], [FieldName] FROM [zc_nxjc_byc_byf_dcs01].[dbo].[EnergyConsumptionContrast] WHERE [OrganizationID] = @organizationId AND [VariableName] = @variableName"; VariableParams vp = new VariableParams(variableId); using (SqlConnection connection = new SqlConnection(this.connectionString)) { connection.Open(); foreach (var database in DCS_DATABASES) { SqlCommand command = connection.CreateCommand(); command.CommandText = string.Format(commandFormat, database); command.Parameters.Add(new SqlParameter("organizationId", vp.OrganizationId)); command.Parameters.Add(new SqlParameter("variableName", vp.VariableName)); using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read() == false) { continue; } // [0]:数据库名 [1]:表格名 [2]:列名 return(new string[] { database, reader[0].ToString().Replace("Realtime", "History"), reader[1].ToString() }); } } } throw new ArgumentException("所提供的VariableID:" + variableId + "在DCS数据库中无对应项,请检查对照表配置是否正确。"); }