基于 SQL Server 存储过程填充饼图
本文关键字:填充 存储过程 SQL Server 基于 | 更新日期: 2023-09-27 18:37:14
我有以下存储过程,我必须从中获取数据:
EXECUTE [dbo].[StationHealthStatusSummary2]
@LineId varchar(100), // 5,9,10
@MeasurementDt datetime, //2012/06/06
@Ntotal Int output,
@N0 int output,
@N1 int output,
@N2 int output,
@N3 int output,
@N4 int output,
@N5 int output,
@N6 int output,
@N7 int output,
@N8 int output,
@N9 int output,
@N10 int output,
@N11 int output,
@N12 int output,
@N13 int output,
@N14 int output,
@N15 int output,
@N16 int output
GO
现在我可以将参数发送到LineID
和Measurement
日期,如下所示:
SqlConnection sql = new SqlConnection(@"Data Source=(local)'SQLEXPRESS;Initial Catalog=iComs;Persist Security Info=True;User ID=sa;Password=Password);
SqlCommand getData = new SqlCommand("StationHealthStatusSummary2", sql);
SqlDataAdapter da = new SqlDataAdapter(getData);
getData.CommandType = CommandType.StoredProcedure;
getData.Parameters.Add(new SqlParameter("@LineId", Lines));
getData.Parameters.Add(new SqlParameter("@MeasurementDt", date1));
SqlParameter ParamaterNtotal = new SqlParameter();
ParamaterNtotal.ParameterName = "@Ntotal";
ParamaterNtotal.SqlDbType = SqlDbType.Int;
ParamaterNtotal.Direction = ParameterDirection.Output;
getData.Parameters.Add(ParamaterNtotal);
sql.Open();
getData.ExecuteNonQuery();
现在我可以获取 NTotal
的值并将其分配给 Teechart(PieSlice),但是我如何获得@N0..@N16
的值?
有一些代码,可能会让你了解我想要完成的任务。
int NTotal = int.Parse(getData.Parameters["@Ntotal"].Value.ToString());
if (GetVariantVariableI(getData.Parameters[0].Value) = 0)
{
for (c = 1; c <= 18; c++)
{
Nvl = GetVariantVariableI(getData.Parameters[2+c].Value);
switch(c)
{
case 1:
NTotal = Nvl;
break;
case 2:
case 3:
case 4:
case 5:
case 6:
case 7:
case 8:
case 9:
case 10:
case 11:
case 12:
case 13:
case 14:
case 15:
case 16:
case 17:
case 18:
case 19:
case 20:
cstatus = c-2;
NPieValues[cstatus] = Nvl;
break;
}
string SliceName;
int NUsed;
NUsed = 0;
for(i=0;i<=16;i++)
{
NUsed = NUsed + NPieValues[i];
}
if (NUsed < NTotal)
{
Chart1.Series[0].Add(NTotal - NUsed);
slice1.Title = "Not Connected";
slice1.Add();
slice1.Color = System.Drawing.Color.Silver;
}
if (NUsed > NTotal)
{
NPieValues[7] = NPieValues[7]-(NUsed-NTotal);
}
for (i=0;i<=16;i++)
{
if (NPieValues[i]>0)
{
switch(i)
{
case 0: SliceName ="Green";
break;
case 1: SliceName ="Yellow";
break;
case 2: SliceName ="Orange";
break;
case 3: SliceName ="Red";
break;
case 4: SliceName ="Broken Rail";
break;
case 5: SliceName ="Buckling Rail";
break;
case 6: SliceName ="Maintenance required";
break;
case 7: SliceName ="Station(s) Off";
break;
case 8: SliceName ="Rail Differential kN";
break;
case 9: SliceName ="Left Rail Sensor Faulty";
break;
case 10: SliceName ="Right Rail Sensor Faulty";
break;
case 11: SliceName ="Temperature Rail Sensor Faulty";
break;
case 12: SliceName ="Calibration Required";
break;
case 13: SliceName ="Station Vandalised";
break;
case 14: SliceName ="Station uninstalled";
break;
case 15: SliceName ="Gauges removed for Maintenance";
break;
case 16: SliceName ="No GSM Coverage";
break;
default:
SliceName ="?";
}
switch(i)
{
case 0: clr = "System.Drawing.Color.Green";
break;
case 1:clr = "System.Drawing.Color.Yellow";
break;
case 2:clr = "System.Drawing.Color.Orrange";
break;
case 3:clr = "System.Drawing.Color.Red";
break;
case 4:
case 5:
case 8:clr = "System.Drawing.Color.Purple";
break;
case 6:clr = "System.Drawing.Color.Black";
break;
case 7:clr = "System.Drawing.Color.Gray";
break;;
case 9:clr = "System.Drawing.ColorTranslator.FromHtml('#E0671F')";
break;
case 10:clr = "System.Drawing.ColorTranslator.FromHtml('#BA4EC2')";
break;
case 11:clr = "System.Drawing.ColorTranslator.FromHtml('#FF8000')";
break;
case 12:clr = "System.Drawing.ColorTranslator.FromHtml('#BF4093')";
break;
case 13:clr = "System.Drawing.Color.SkyBlue";
break;
case 14:clr = "System.Drawing.Color.Aqua";
break;
case 15:clr = "System.Drawing.ColorTranslator.FromHtml('#BFBFFF')";
break;
case 16:clr = "System.Drawing.Color.MedGray";
break;
default : clr = "System.Drawing.Color.White";
break;
}
slice1.Add(NPieValues[i],SliceName,clr);
}
}
}
}
}
现在,在获得所有这些值之后,它必须填充饼图。
拜托,任何帮助将不胜感激,如果我太含糊了,请告诉我。
谢谢
您已经添加了@Ntotal
作为输出参数,为什么不添加所有其他参数?
SqlParameter n = new SqlParameter();
n.ParameterName = "@N0";
n.SqlDbType = SqlDbType.Int;
n.Direction = ParameterDirection.Output;
getData.Parameters.Add(n);
然后,您可以像检索@Ntotal
.此外,如果您愿意,可以按名称检索值,而不是循环访问它们。
谢谢。我想通了...在我发送参数如下之后:
SqlParameter Paramater0 = new SqlParameter();
Paramater0.ParameterName = "@N0";
Paramater0.SqlDbType = SqlDbType.Int;
Paramater0.Direction = ParameterDirection.Output;
getData.Parameters.Add(Paramater0);
然后我获取值并将其声明为 var,如下所示:
int N0 = int.Parse(getData.Parameters["@N0"].Value.ToString());
最后将该 int 声明为图表上的切片,如下所示:
slice1.Add(N0, "Green", System.Drawing.Color.Green);
感谢您的帮助@mgnoonan :)