c# - 如何防止在 C# ASP.NET 中将重复数据插入到 Gridview 中
问题描述
- 我有一个如下的表格视图:
EQP LOT_ID ACTIVITY MSG MODIFIED_DATE 001 WM31960 LOAD_LOT Load lot success. 9/29/2020 11:11:17 AM 001 WM31799 UNLOAD_LOT Unload lot success. 9/29/2020 11:10:48 AM 001 WM31799 LOAD_LOT Load lot success. 9/29/2020 6:03:50 AM 001 WM31964 UNLOAD_LOT Unload lot success. 9/29/2020 6:02:42 AM 002 WM32295 LOAD_LOT Load lot success. 9/29/2020 11:23:22 AM 002 WM32344 UNLOAD_LOT Unload lot success. 9/29/2020 11:22:54 AM 002 WM32344 LOAD_LOT Load lot success 9/29/2020 8:18:18 AM 002 WM32314 UNLOAD_LOT Unload lot success. 9/29/2020 8:17:55 AM
2. 我想让这个表变成每个 EQP 只有一行,我将 LOT_ID 求和如下: (计算) TotalLOT_ID (SUM) = if LOT_ID =WM31960 and MSG=Load lot success。
EQP TotalLOT_ID 001 2 002 2
- 这是我的编码,但我无法获得我想要的Gridview 输出。
public void btn_ClickSubmit(object sender, EventArgs e)
{
string oradb1 = "Data Source=(DESCRIPTION =" +
"(ADDRESS = (PROTOCOL=TCP)(HOST = vpngprdrmstmp)(PORT = xxxx))" +
"(CONNECT_DATA =" +
"(SERVER = DEDICATED)" +
"(SERVICE_NAME = xxxxxx)" +
")" +
");User ID=xxxxxx;Password=xxxxx;";
DateTime StartDate = Convert.ToDateTime(datepickerstart.Text);
string Date = StartDate.ToString("dd-MMM-yyyy");
DateTime EndDate = Convert.ToDateTime(datepicker.Text);
string dt2 = EndDate.ToString("dd-MMM-yyyy");
string query = "SELECT DISTINCT RMSTMP_PNG.ota_activity.EQP from RMSTMP_PNG.ota_activity FULL OUTER JOIN RMSTMP_PNG.ota_activity_rescreen ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID FULL OUTER JOIN RMSTMP_PNG.ota_activity_lotinfo ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE >= to_date('" + Date + "')AND RMSTMP_PNG.ota_activity.MODIFIED_DATE < to_date('" + dt2 + "') + 1 ORDER BY RMSTMP_PNG.ota_activity.EQP ASC";
DataTable dt = new DataTable();
using (OracleConnection con = new OracleConnection(oradb1))
{
using (OracleCommand cmd = new OracleCommand(query))
{
using (OracleDataAdapter sda = new OracleDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(dt);
}
DataTable da = new DataTable();
da.Columns.Add("TEST", typeof(System.String));
foreach (DataRow sd in dt.Rows)
{
var EQP_ID = sd["EQP"].ToString();
using (OracleConnection conn = new OracleConnection(oradb1))
{
using (OracleCommand cmd1 = new OracleCommand("SELECT RMSTMP_PNG.ota_activity.EQP,RMSTMP_PNG.ota_activity.LOT_ID, RMSTMP_PNG.ota_activity.ACTIVITY, RMSTMP_PNG.ota_activity.MSG,RMSTMP_PNG.ota_activity.MODIFIED_DATE FROM RMSTMP_PNG.ota_activity FULL OUTER JOIN RMSTMP_PNG.ota_activity_rescreen ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID FULL OUTER JOIN RMSTMP_PNG.ota_activity_lotinfo ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE >= to_date('" + Date + "')AND RMSTMP_PNG.ota_activity.MODIFIED_DATE < to_date('" + dt2 + "') + 1 AND EQP ='" + EQP_ID + "' GROUP BY RMSTMP_PNG.ota_activity.LOT_ID,RMSTMP_PNG.ota_activity.ACTIVITY,RMSTMP_PNG.ota_activity.MSG, RMSTMP_PNG.ota_activity.MODIFIED_DATE, RMSTMP_PNG.ota_activity.ID ORDER BY RMSTMP_PNG.ota_activity.EQP,RMSTMP_PNG.ota_activity.MODIFIED_DATE DESC"))
{
using (OracleDataAdapter sda1 = new OracleDataAdapter())
{
cmd1.CommandType = CommandType.Text;
cmd1.Connection = conn;
sda1.SelectCommand = cmd1;
sda1.Fill(da);
}
}
DataRow prevRow = null;
foreach (DataRow sda2 in da.Rows)
{
var eq = sda2["EQP"].ToString();
if (prevRow != null)
{
if ( prevRow.Field<string>("EQP") != eq)
{
var eq1= sda2["EQP"].ToString();
sda2["TEST"] = eq1;
}
else
{
var eq2 = sda2["EQP"].ToString();
}
}
prevRow = sda2;
}
GridView2.DataSource = da;
GridView2.DataBind();
}
}
}
}
}
有人能给我一些提示或我可以尝试什么吗?
最新更新的代码如下(在里面添加了一些评论,希望你能理解我的意思):
public void btn_ClickSubmit(object sender, EventArgs e) { string oradb1 = "Data Source=(DESCRIPTION =" +
“(广告
DRESS = (PROTOCOL=TCP)(HOST = vpngprdrmstmp)(PORT = 1548))" + "(CONNECT_DATA =" + "(SERVER = DEDICATED)" + "(SERVICE_NAME = rmstmpdb)" + ")" + ");User ID=OTA_VIEW;Password=0taV1ew;"; DateTime StartDate = Convert.ToDateTime(datepickerstart.Text); string Date = StartDate.ToString("dd-MMM-yyyy"); DateTime EndDate = Convert.ToDateTime(datepicker.Text); string dt2 = EndDate.ToString("dd-MMM-yyyy"); //Get the list of EQP use DISTINCT string query = "SELECT DISTINCT RMSTMP_PNG.ota_activity.EQP from RMSTMP_PNG.ota_activity FULL OUTER JOIN RMSTMP_PNG.ota_activity_rescreen ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID FULL OUTER JOIN RMSTMP_PNG.ota_activity_lotinfo ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE >= to_date('" + Date + "')AND RMSTMP_PNG.ota_activity.MODIFIED_DATE < to_date('" + dt2 + "') + 1 ORDER BY RMSTMP_PNG.ota_activity.EQP_ID ASC"; DataTable dt = new DataTable(); using (OracleConnection con = new OracleConnection(oradb1)) { using (OracleCommand cmd = new OracleCommand(query)) { using (OracleDataAdapter sda = new OracleDataAdapter()) { cmd.CommandType = CommandType.Text; cmd.Connection = con; sda.SelectCommand = cmd; sda.Fill(dt); } DataTable da = new DataTable(); da.Columns.Add("EQP", typeof(string)); da.Columns.Add("TotalLOT_ID", typeof(string)); foreach (DataRow sd in dt.Rows) { //Once get the EQP, then run the query with many data column. var EQP_ID = sd["EQP"].ToString(); using (OracleConnection conn = new OracleConnection(oradb1)) { using (OracleCommand cmd1 = new OracleCommand("SELECT RMSTMP_PNG.ota_activity.EQP,RMSTMP_PNG.ota_activity.LOT_ID, RMSTMP_PNG.ota_activity.ACTIVITY, RMSTMP_PNG.ota_activity.MESSAGE,LISTAGG (RMSTMP_PNG.ota_activity_lotinfo.KEY || ': ' || RMSTMP_PNG.ota_activity_lotinfo.VALUE, ','||chr(13)||chr(10)) WITHIN GROUP (ORDER BY RMSTMP_PNG.ota_activity_lotinfo.KEY,RMSTMP_PNG.ota_activity_lotinfo.VALUE) AS names,RMSTMP_PNG.ota_activity.MODIFIED_DATE,TO_CHAR (DATE '1900-01-01' + ABS ( LAG(RMSTMP_PNG.ota_activity.MODIFIED_DATE,1)OVER(ORDER BY RMSTMP_PNG.ota_activity.MODIFIED_DATE DESC) - RMSTMP_PNG.ota_activity.MODIFIED_DATE), 'HH24:MI:SS') AS Time_Different FROM RMSTMP_PNG.ota_activity FULL OUTER JOIN RMSTMP_PNG.ota_activity_rescreen ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID FULL OUTER JOIN RMSTMP_PNG.ota_activity_lotinfo ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE >= to_date('" + Date + "')AND RMSTMP_PNG.ota_activity.MODIFIED_DATE < to_date('" + dt2 + "') + 1 AND EQP ='" + EQP_ID + "' GROUP BY RMSTMP_PNG.ota_activity.LOT_ID, RMSTMP_PNG.ota_activity_lotinfo.KEY,RMSTMP_PNG.ota_activity_lotinfo.VALUE,RMSTMP_PNG.ota_activity.ACTIVITY,RMSTMP_PNG.ota_activity.MESSAGE, RMSTMP_PNG.ota_activity.MODIFIED_DATE, RMSTMP_PNG.ota_activity.EQP_ID ORDER BY RMSTMP_PNG.ota_activity.EQP_ID,RMSTMP_PNG.ota_activity.MODIFIED_DATE DESC")) { using (OracleDataAdapter sda1 = new OracleDataAdapter()) { cmd1.CommandType = CommandType.Text; cmd1.Connection = conn; sda1.SelectCommand = cmd1; sda1.Fill(da); } //Once get the first EQP then loop to display data in GRIDVIEW foreach (DataRow sda2 in da.Rows) { var eqp = sda2["EQP"].ToString(); var lotid = sda2["LOT_ID"].ToString(); var msg = sda2["MSG"].ToString(); if (lotid.Equals(lotid) && msg.Equals("Load lot success.")) { string query1 = string.Format("EQP='{0}'", eqp.Replace(@"'", "''")); var rows = da.Select(query1); if (rows.Length > 0) { var rowIndex = da.Rows.IndexOf(rows[0]); da.Rows[rowIndex]["TotalLOT_ID"] = int.Parse(da.Rows[rowIndex]["TotalLOT_ID"].ToString()) + 1; } else { if (lotid.Equals(lotid) && msg.Equals("Load lot success.")) { da.Rows.Add(eqp, 1); } } } } GridView2.DataSource = da; GridView2.DataBind(); } } } } } }
(更新:我更改了查询,我的表格视图如下所示。)
EQP LOT ACTIVITY MSG MODIFIED_DATE 001 WM34163 LOAD_LOT Load lot success. 10/01/2020 11:11:11 001 WM34163 LOAD_LOT Load lot success. 10/01/2020 11:11:11 001 WM34163 LOAD_LOT Load lot success. 10/01/2020 11:11:11 001 WM34163 LOAD_LOT Load lot success. 10/01/2020 11:11:11 001 WM34163 CANCEL_LOT CANCEL. 10/01/2020 12:11:11 001 WM34160 UNLOAD_LOT Unload lot success. 10/01/2020 13:10:12 001 WM34160 UNLOAD_LOT Unload lot success. 10/01/2020 13:10:12 002 WM32439 LOAD_LOT Load lot success. 10/01/2020 15:11:12 002 WM32439 LOAD_LOT Load lot success. 10/01/2020 15:11:12 002 WM32439 LOAD_LOT Load lot success. 10/01/2020 15:11:12
(我想只得到 1= WM34163 和 WM32439 =1 ,因为它们是重复的 Lot_id,但现在它算作 4 )一旦我运行后端代码,它显示如下。
ID Total_LOTID 001 4 002 3
这是我最新的编码。我不知道在哪里应用我的 foreach 循环(总和)以避免被删除。
foreach (DataRow sda2 in da.Rows)
{
var totalsum = 0;
DataRow prevRow = null;
var eqp = sda2["EQP_ID"].ToString();
var lot = sda2["T"].ToString();
var lotid = sda2["LOT_ID"].ToString();
int sum = 0;
foreach (DataRow sa in da.Rows)
{
if (prevRow != null)
{
var display = prevRow["LOT_ID"].ToString();
if (lotid != prevRow["LOT_ID"].ToString() && lotid.Equals("1"))
{
sum++;
}
}
prevRow = sa;
}
totalsum = sum;
if (lot.Equals(lot))
{
string query2 = string.Format("EQP='{0}'", eqp.Replace(@"'", "''"));
var rows = dx.Select(query2);
if (rows.Length > 0)
{
var rowIndex = dx.Rows.IndexOf(rows[0]);
dx.Rows[rowIndex]["TotalLOT_ID"] = totalsum;
}
else
{
if (lot.Equals(lot))
{
dx.Rows.Add(eqp, 1);
}
}
}
}
解决方案
public void btn_ClickSubmit(object sender, EventArgs e)
{
using (OracleCommand cmd = new OracleCommand(query))
{
using (OracleDataAdapter sda = new OracleDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(dt);
}
DataTable da = new DataTable();
// Create another DT to store final result
DataTable dx = new DataTable();
dx.Columns.Add("EQP", typeof(string));
dx.Columns.Add("TotalLOT_ID", typeof(string));
foreach (DataRow sd in dt.Rows)
{
//Once get the EQP, then run the query with many data column.
var EQP_ID = sd["EQP"].ToString();
using (OracleConnection conn = new OracleConnection(oradb1))
{
using (OracleCommand cmd1 = new OracleCommand("SELECT RMSTMP_PNG.ota_activity.EQP,RMSTMP_PNG.ota_activity.LOT_ID, RMSTMP_PNG.ota_activity.ACTIVITY, RMSTMP_PNG.ota_activity.MESSAGE,LISTAGG (RMSTMP_PNG.ota_activity_lotinfo.KEY || ': ' || RMSTMP_PNG.ota_activity_lotinfo.VALUE, ','||chr(13)||chr(10)) WITHIN GROUP (ORDER BY RMSTMP_PNG.ota_activity_lotinfo.KEY,RMSTMP_PNG.ota_activity_lotinfo.VALUE) AS names,RMSTMP_PNG.ota_activity.MODIFIED_DATE,TO_CHAR (DATE '1900-01-01' + ABS ( LAG(RMSTMP_PNG.ota_activity.MODIFIED_DATE,1)OVER(ORDER BY RMSTMP_PNG.ota_activity.MODIFIED_DATE DESC) - RMSTMP_PNG.ota_activity.MODIFIED_DATE), 'HH24:MI:SS') AS Time_Different FROM RMSTMP_PNG.ota_activity FULL OUTER JOIN RMSTMP_PNG.ota_activity_rescreen ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID FULL OUTER JOIN RMSTMP_PNG.ota_activity_lotinfo ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE >= to_date('" + Date + "')AND RMSTMP_PNG.ota_activity.MODIFIED_DATE < to_date('" + dt2 + "') + 1 AND EQP ='" + EQP_ID + "' GROUP BY RMSTMP_PNG.ota_activity.LOT_ID, RMSTMP_PNG.ota_activity_lotinfo.KEY,RMSTMP_PNG.ota_activity_lotinfo.VALUE,RMSTMP_PNG.ota_activity.ACTIVITY,RMSTMP_PNG.ota_activity.MESSAGE, RMSTMP_PNG.ota_activity.MODIFIED_DATE, RMSTMP_PNG.ota_activity.EQP_ID ORDER BY RMSTMP_PNG.ota_activity.EQP_ID,RMSTMP_PNG.ota_activity.MODIFIED_DATE DESC"))
{
using (OracleDataAdapter sda1 = new OracleDataAdapter())
{
cmd1.CommandType = CommandType.Text;
cmd1.Connection = conn;
sda1.SelectCommand = cmd1;
sda1.Fill(da);
}
}
foreach (DataRow sda2 in da.Rows)
{
var eqp = sda2["EQP"].ToString();
var lotid = sda2["LOT_ID"].ToString();
var msg = sda2["MSG"].ToString();
if (lotid.Equals("WM31960") && msg.Equals("Load lot success."))
{
string query2 = string.Format("EQP='{0}'", eqp.Replace(@"'", "''"));
var rows = dx.Select(query2);
if (rows.Length > 0)
{
var rowIndex = dx.Rows.IndexOf(rows[0]);
dx.Rows[rowIndex]["TotalLOT_ID"] = int.Parse(dx.Rows[rowIndex]["TotalLOT_ID"].ToString()) + 1;
}
else
{
if (lotid.Equals("WM31960") && msg.Equals("Load lot success."))
{
dx.Rows.Add(eqp, 1);
}
}
}
}
}
}
GridView2.DataSource = dx;
GridView2.DataBind();
}
}
}
推荐阅读
- php - 包含新的 page.php 并删除其余内容
- angular - Angular 8 - JSON 对象未在模板中呈现
- flutter - 如何在颤振中使用人脸识别来跟踪用户面部
- c# - 当前上下文中不存在名称“i”
- css - 我如何不让 CSS 文件在 React 中继承?
- laravel - laravel vue 这条路线不支持 post 方法
- firebase - 当我们有许多用户阅读同一个集合时如何减少 Firestore 文档的读取
- c - 使用 libsox 更改音频文件音高而不更改速度
- nebular - Nebular 5.0.0 nb-select - 在编辑寄存器中不起作用
- javascript - 为什么 slice 方法在这段代码中包含 end 参数?