C#.net Windows 应用程序 - 插入数据库之前检查输入数据

本文关键字：检查 输入 数据 数据库 插入 net Windows 应用程序 | 更新日期: 2023-09-27 18:37:06

我正在制作一个预订系统。

在一系列数据插入数据库之前，我无法弄清楚一系列数据的验证算法。主键将由系统自动生成的预订ID。

我需要验证 bdate、btime 和 sname。（bdate=预订时间，btime=预订时间，sname=员工姓名）

如果是 bdate，btime 和 sname 与客户端输入的内容相同。 系统将提醒其副本，因为工作人员已经在同一日期和时间预订。

请在下面找到我的插入查询，感谢您为我指出正确的方法。

 private void btn_save_Click(object sender, EventArgs e)
    {
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;
        //query for duplicate
        cmd.CommandText = "select count(*) from Booking where sname = @newName and bdate = @newDate and btime = @newTime";
       // cmd.Parameters.Add("@newName", OleDbType.VarChar).Value = txt_cname.Text;
        //cmd.Parameters.Add("@newDate", OleDbType.DBDate).Value = dtp_bdate.Value.Date;
       // cmd.Parameters.Add("@newTime", OleDbType.VarChar).Value = dtp_btime.Value.ToString("hh:mm tt");
        cmd.CommandText = "insert into Booking(cname, bdate, btime, ccontact, sname) Values('" + txt_cname.Text + "','" + dtp_bdate.Value.Date + "','" + dtp_btime.Value.ToString("hh:mm tt") + "','" + txt_ccontact.Text + "','" + txt_sname.Text + "')";
        cmd.Connection = myCon;
        myCon.Open();
        int recordCount = Convert.ToInt32(cmd.ExecuteScalar());
        myCon.Close();
        if (recordCount>0)
        {
            // handle duplicates
            MessageBox.Show("Duplicated", "My Application",
            MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk);
        }
       // cmd.Connection = myCon;
        //myCon.Open();
       //cmd.ExecuteNonQuery();
        //myCon.Close();
        //MessageBox.Show(dtp_bdate.Value.ToString());
        //MessageBox.Show("Booking completed", "My Application",
       // MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk);
    }

C#.net Windows 应用程序 - 插入数据库之前检查输入数据

    private bool RecordExists(string name, DateTime date, string time)
    {
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;
        //query for duplicate
        cmd.CommandText = "select count(*) from Booking where sname = @newName and bdate = @newDate and btime = @newTime";
        cmd.Parameters.Add("@newName", OleDbType.VarChar).Value = txt_cname.Text;
        cmd.Parameters.Add("@newDate", OleDbType.DBDate).Value = dtp_bdate.Value.Date;
        cmd.Parameters.Add("@newTime", OleDbType.VarChar).Value = dtp_btime.Value.ToString("hh:mm tt");
        myCon.Open();
        int recordCount = Convert.ToInt32(cmd.ExecuteScalar());
        myCon.Close();
        return recordCount > 0;
    }
    private void btn_save_Click(object sender, EventArgs e)
    {
        if (RecordExists(txt_cname.Text, dtp_bdate.Value.Date, dtp_btime.Value.ToString("hh:mm tt"))
        {
            MessageBox.Show("Duplicated", "My Application", MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk);
            return;
        }
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into Booking(cname, bdate, btime, ccontact, sname) Values(@newName, @newDate, @newTime, @newContact, @newSName)";
        cmd.Parameters.Add("@newName", OleDbType.VarChar).Value = txt_cname.Text;
        cmd.Parameters.Add("@newDate", OleDbType.DBDate).Value = dtp_bdate.Value.Date;
        cmd.Parameters.Add("@newTime", OleDbType.VarChar).Value = dtp_btime.Value.ToString("hh:mm tt");
        cmd.Parameters.Add("@newContact", OleDbType.VarChar).Value = txt_ccontact.Text;
        cmd.Parameters.Add("@newSName", OleDbType.VarChar).Value = txt_sname.Text;
        cmd.Connection = myCon;
        myCon.Open();
        cmd.ExecuteNonQuery();
        myCon.Close();
        MessageBox.Show(dtp_bdate.Value.ToString());
        MessageBox.Show("Booking completed", "My Application", MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk);
    }
您需要

在执行插入之前检查预订是否存在，因此您需要添加额外的步骤：

OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select count(*) from booking where cname = @newName and bdate = @newDate and ctime = @newTime";
cmd.Parameters.Add("@newName", OleDbType.VarChar).Value = txt_cname.Text;
cmd.Parameters.Add("@newDate", OleDbType.DBDate).Value = dtp_bdate.Value.Date;
cmd.Parameters.Add("@newTime", OleDbType.VarChar).Value = dtp_btime.Value.ToString("hh:mm tt");
cmd.Connection = myCon;
myCon.Open();
int recordCount = Convert.ToInt32(cmd.ExecuteScalar());
myCon.Close();
if (recordCount>0)
{
    // handle duplicates
}

当你执行这个时，它将返回匹配的行数，如果这是 1 或更多，那么你应该调用你的重复逻辑。

编辑以更正代码

要检查是否存在现有字段，您可以创建一个Select，然后比较：

bool InfoRepeated()
    {
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = string.Format("SELECT cname FROM yourTable;");
        cmd.Connection = myCon;
        myCon.Open();
        try
        {
            OleDbDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                if (txt_cname.Text.Equals((rdr[0].ToString())))
                {
                    myCon.Close();
                    return true;
                }
            }
            myCon.Close();
            return false;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
            myCon.Close();
            return false;
        }
    }

让我知道它是否有效或您遇到的错误。

有用的代码试试这个

BOMaster obj_data = new BOMaster();
obj_data.productid = tempid;
obj_data.categoryid =int.Parse(cmbcategory.SelectedValue.ToString());
obj_data.productcode = txtproductcode.Text;
obj_data.productname = txtproductname.Text;
obj_data.mqty = decimal.Parse(txtmqty.Text.ToString());
OleDbCommand mycmd = new OleDbCommand("select * from productmaster where productname=?", new OleDbConnection(Common.cnn));
BOMaster obj_datan = new BOMaster();
mycmd.Parameters.Add(new OleDbParameter("productname", txtproductname.Text));
mycmd.Connection.Open();
OleDbDataReader myreader = mycmd.ExecuteReader(CommandBehavior.CloseConnection);
if (myreader.HasRows == true)
{
    // savestutus = "false";
    MessageBox.Show("Product Name Already Exist", "Product", MessageBoxButtons.OK, MessageBoxIcon.Information);
    txtproductname.Focus();
    return;
}
mycmd.Connection.Close();
ProductDAL obj_dal = new ProductDAL();
if (obj_dal.Save(obj_data))
{
    Clear();
}
相关文章：