c#从数据库中创建缺少行的按钮
本文关键字:按钮 创建 数据库 | 更新日期: 2023-09-27 18:03:45
首先,对不起我的英语:)
我想使按钮自动从数据库。在数据库中,每个按钮都有一个ID,我调用这个ID。
我的问题很简单,如果其中一个id丢失(如1,2,4,5),程序在第二次读取后停止。下面是代码:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace e_res
{
public partial class Layout : Form
{
public Layout()
{
InitializeComponent();
}
private void Layout_Load(object sender, EventArgs e)
{
SQLFunctions Lgn = new SQLFunctions();
Lgn.ConnectionToday();
SqlCommand cmd = new SqlCommand();
cmd.Connection = SQLFunctions.conn;
int NumOfButtons = 40;
int i = 1;
cmd.CommandText = "SELECT id FROM Buttons where id='" + i + "'";
int _bId = Int32.Parse(cmd.ExecuteScalar().ToString());
// int counter = 0;
while ( _bId <= NumOfButtons)
{
if (_bId != null)
{
Button btn = new Button();
{
btn.Tag = _bId;
btn.Dock = DockStyle.Fill;
btn.Margin = new Padding(10, 10, 10, 10);
cmd.CommandText = "SELECT bName FROM Buttons where id='" + btn.Tag + "'";
btn.Text = cmd.ExecuteScalar().ToString();
string btn_name = cmd.ExecuteScalar().ToString();
btn.Name = btn_name.ToString();
/* btn.Click += delegate
{
pass_txt.Clear();
username_txt.Text = btn_name;
username_lbl.Text = btn_name;
username_lbl.Visible = true;
pass_txt.ReadOnly = false;
};*/
}
cmd.CommandText = "SELECT col FROM Buttons where id='" + btn.Tag + "'";
int btn_col = Int32.Parse(cmd.ExecuteScalar().ToString());
// MessageBox.Show(btn_col.ToString());
cmd.CommandText = "SELECT row FROM Buttons where id='" + btn.Tag + "'";
int btn_row = Int32.Parse(cmd.ExecuteScalar().ToString());
// MessageBox.Show(btn_row.ToString());
tableLayoutPanel4.Controls.Add(btn, btn_col, btn_row);
_bId++;
}
else
{
_bId++;
}
}
SQLFunctions.conn.Close();
}
private void button2_Click(object sender, EventArgs e)
{
NewButton nw = new NewButton();
nw.Show();
}
}
}
谢谢
这里有一些事情你真的需要看看,我把它从你的代码,你仍然是新的编程,因为你的方式最好的括号。
-
int _bId = Int32.Parse(cmd.ExecuteScalar().ToString());
这是危险的 - 在你的页面布局中,你有一个完整的方法实现,你需要将代码移动到它自己的方法中,并从页面布局中调用它。
- 为什么需要使用数据库自动生成id?你可以自动生成你自己的id后,你检索所有的数据从一个单一的请求,而不是3 @Alex K。提到
-
int NumOfButtons = 40;Int I = 1;cmd。CommandText = "SELECT id FROM Buttons where id='" + i + "'";
你不知道你的数据库中有多少按钮,你不能硬编码这个数字。相反,你可以这样做"SELECT id, bName, col, row FROM Buttons;"
if (_bId != null)
{
// Here.. You need to check if _bId (For ex: 3) exists in database(by making a query), and then proceed with creating button which is rest of your code.
}
注意:你不需要在for循环中一次又一次地查询,你可以使用下面的语句一次检索所有的数据。
SELECT bName, col, row FROM Buttons where id='"+bId+"'
如果这个查询返回一行,//你继续剩下的代码,创建按钮其他的报价+ +;
使用ExecuteReader()查询选择所有按钮。我假设MS Sql server支持TOP()
cmd.CommandText = "SELECT TOP(" + NumOfButtons + ") id, bname,col,row FROM Buttons ORDER BY id";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
// process the button
}
如果我们跳过"你的方法不是真的有效"这一部分,而只是试图使这段代码工作,那么你应该修改它看起来像这样:
SQLFunctions Lgn = new SQLFunctions();
Lgn.ConnectionToday();
SqlCommand cmd = new SqlCommand();
cmd.Connection = SQLFunctions.conn;
int NumOfButtons = 40;
int i = 1;
// int counter = 0;
while ( i <= NumOfButtons)
{
cmd.CommandText = "SELECT id FROM Buttons where id='" + i + "'";
int _bId = Int32.Parse(cmd.ExecuteScalar().ToString());
Button btn = new Button();
{
btn.Tag = _bId;
btn.Dock = DockStyle.Fill;
btn.Margin = new Padding(10, 10, 10, 10);
cmd.CommandText = "SELECT bName FROM Buttons where id='" + btn.Tag + "'";
btn.Text = cmd.ExecuteScalar().ToString();
string btn_name = cmd.ExecuteScalar().ToString();
btn.Name = btn_name.ToString();
/* btn.Click += delegate
{
pass_txt.Clear();
username_txt.Text = btn_name;
username_lbl.Text = btn_name;
username_lbl.Visible = true;
pass_txt.ReadOnly = false;
};*/
}
cmd.CommandText = "SELECT col FROM Buttons where id='" + btn.Tag + "'";
int btn_col = Int32.Parse(cmd.ExecuteScalar().ToString());
// MessageBox.Show(btn_col.ToString());
cmd.CommandText = "SELECT row FROM Buttons where id='" + btn.Tag + "'";
int btn_row = Int32.Parse(cmd.ExecuteScalar().ToString());
// MessageBox.Show(btn_row.ToString());
tableLayoutPanel4.Controls.Add(btn, btn_col, btn_row);
i++;
}
SQLFunctions.conn.Close();
但是,如果我们谈论的代码,我将真正使用来解决你的情况下,我会改变Id
的列类型为int和1个简单的SQL命令首先从表的Id
取NumOfButtons
。下面是一个简单的SQL请求:
SELECT id, col, row ORDER BY id FETCH NEXT (@NumOfButtons) ROWS ONLY
我将使用这个结构体来运行它:
using (var reader = cmd.ExecuteReader($"SELECT id, col, row ORDER BY id FETCH NEXT ({NumOfButtons}) ROWS ONLY"))
{
while (reader.Read())
{
var id = reader.GetInt32(0);
var col = reader.GetString(1);
var row = reader.GetString(2);
// ToDo: Your stuff here
}
}
你知道序列将是什么吗?即它是连续的,也就是说它将从一个数字开始,预计每次增加1 ?如果是这样的话,我可以给你一些伪代码来解决你的问题。我手头没有visualstudio来检查所有的代码。
然后编写linq语句,查找序列中最低的ID。然后编写linq语句,查找序列中最大的ID。
然后使用
int last = lowest;
for (int i = lowest; i <= highest; i++)
{
int curr = (from int btn in collection/datatable where btn.ID == i select btn.ID).first;
if (curr == (last + 1))
{
curr = i;
}
else
{
++last
curr = last;
}
//make the button based on curr or not as needed
++last;
}
}