如何在 C# 中使用数据读取器

本文关键字：数据 读取 | 更新日期: 2023-09-27 18:32:25

我犯错的地方请告诉我....
当我单击插入按钮时，它向我显示此消息

注册号已经存在尝试另一个。

但我每次都尝试新的注册号。

private void button1_Click(object sender, RoutedEventArgs e)   
{  
cmd.Connection = cn;
cmd.CommandText = "select count(*) from studentreg where registrationno='" + txtrgno.Text + "'";
cn.Open();
dr = cmd.ExecuteReader();
if (dr.Read())  
{  
MessageBox.Show("Registration No. Already Exists Try Another", "Error", MessageBoxButton.OK,     MessageBoxImage.Error);
txtrgno.Text = "";
txtrgno.Focus();
if ((dr != null))  
{  
dr.Close();
}  
return;  
}  
//cn.Close();  
try  
{  
string gen="";  
if(rdb1_m.IsChecked==true)  
{  
gen="Male";  
}  
else if(rdb2_f.IsChecked==true)  
{  
gen="Female";  
}  
string filename = "";  
FileStream fs;  
byte[] x=null;  
if (string.IsNullOrEmpty(filename) == false)  
{  
fs = new FileStream(filename, FileMode.Open, FileAccess.Read);  
BinaryReader br = new BinaryReader(fs);  
x = br.ReadBytes((int)(fs.Length));  
}  
cmd.Connection = cn;
SqlParameter p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18,     p19, p20, p21, p22, p23, p24, p25, p26, p27, p28, p29, p30, p31, p32, p33;
cmd.CommandText = @"insert into studentreg values(@rgno1, @sname1, @dob1, @bloodgroup1, @gender1, @imark11, @imark12, @photo1, @fname1, @fq1, @fo1, @mname1, @mq1, @mo1, @mton1, @religion1, @nationality1, @state1, @distric1, @mobile1, @email1, @caddr1, @paddr1, @academicy1, @adclass1, @adsec1, @addate1, @lschool1, @lclass1, @pyear1, @estudent1, @eclass1, @esec1)";  
p1 = cmd.Parameters.Add("@rgno1", SqlDbType.VarChar, 100);  
p2 = cmd.Parameters.Add("@sname1", SqlDbType.VarChar, 100);  
p3 = cmd.Parameters.Add("@dob1", SqlDbType.Date);  
p4 = cmd.Parameters.Add("@bloodgroup1", SqlDbType.VarChar, 10);  
p5 = cmd.Parameters.Add("@gender1", SqlDbType.VarChar, 10);  
p6 = cmd.Parameters.Add("@imark11", SqlDbType.VarChar, 100);  
p7 = cmd.Parameters.Add("@imark12", SqlDbType.VarChar, 100);  
p8 = cmd.Parameters.Add("@photo1", SqlDbType.Image);  
p9 = cmd.Parameters.Add("@fname1", SqlDbType.VarChar, 100);  
p10 = cmd.Parameters.Add("@fq1", SqlDbType.VarChar, 100);  
p11 = cmd.Parameters.Add("@fo1", SqlDbType.VarChar, 100);
p12 = cmd.Parameters.Add("@mname1", SqlDbType.VarChar, 100);  
p13 = cmd.Parameters.Add("@mq1", SqlDbType.VarChar, 100);  
p14 = cmd.Parameters.Add("@mo1", SqlDbType.VarChar, 100);  
p15 = cmd.Parameters.Add("@mton1", SqlDbType.VarChar, 50);  
p16 = cmd.Parameters.Add("@religion1", SqlDbType.VarChar, 50);  
p17 = cmd.Parameters.Add("@nationality1", SqlDbType.VarChar, 50);  
p18 = cmd.Parameters.Add("@state1", SqlDbType.VarChar, 100);  
p19 = cmd.Parameters.Add("@distric1", SqlDbType.VarChar, 100);  
p20 = cmd.Parameters.Add("@mobile1", SqlDbType.BigInt);  
p21 = cmd.Parameters.Add("@email1", SqlDbType.VarChar, 100);  
p22 = cmd.Parameters.Add("@caddr1", SqlDbType.VarChar);  
p23 = cmd.Parameters.Add("@paddr1", SqlDbType.VarChar);  
p24 = cmd.Parameters.Add("@academicy1", SqlDbType.VarChar, 50);  
p25 = cmd.Parameters.Add("@adclass1", SqlDbType.VarChar, 15);  
p26 = cmd.Parameters.Add("@adsec1", SqlDbType.VarChar, 15);  
p27 = cmd.Parameters.Add("@addate1", SqlDbType.Date);
p28 = cmd.Parameters.Add("@lschool1", SqlDbType.VarChar, 100);  
p29 = cmd.Parameters.Add("@lclass1", SqlDbType.VarChar, 15);  
p30 = cmd.Parameters.Add("@pyear1", SqlDbType.VarChar, 50);
p31 = cmd.Parameters.Add("@estudent1", SqlDbType.VarChar, 100);  
p32 = cmd.Parameters.Add("@eclass1", SqlDbType.VarChar, 15);  
p33 = cmd.Parameters.Add("@esec1", SqlDbType.VarChar, 15);  
cn.Open();  
p1.Value = txtrgno.Text;  
p2.Value = txt_sname.Text;  
p3.Value = dtp_dob.Text;  
p4.Value = cmbx1_bloodgrp.Text;  
p5.Value = gen;  
p6.Value = txt_idm1.Text;  
p7.Value = txt_idm2.Text;  
p8.Value = x;  
p9.Value = txt_fname.Text;  
p10.Value = txt_fq.Text;  
p11.Value = txt_fo.Text;  
p12.Value = txt_mname.Text;  
p13.Value = txt_mq.Text;  
p14.Value = txt_mo.Text;  
p15.Value = txt_tongue.Text;  
p16.Value = txt_religion.Text;  
p17.Value = txt_nationality.Text;  
p18.Value = txt_state.Text;  
p19.Value = txt_dist.Text;  
p20.Value = txt_mobile.Text;  
p21.Value = txt_email.Text;  
p22.Value = txt_caddr.Text;  
p23.Value = txt_paddr.Text;  
p24.Value = cmbx2_academicyear.Text;  
p25.Value = cmbx3_class.Text;  
p26.Value = cmbx4_section.Text;  
p27.Value = dtp_admissiondate.Text;  
p28.Value = txt_pschool.Text;  
p29.Value = cmbx7_pclass.Text;  
p30.Value = cmbx8_pyear.Text;  
p31.Value = txt_esname.Text;  
p32.Value = cmbx5_eclass.Text;  
p33.Value = cmbx6_esec.Text;  
cmd.ExecuteNonQuery();  
cn.Close();  
MessageBox.Show("Record Successfully Inserted");  
}  
catch(SqlException ex)   
{  
MessageBox.Show(ex.Message);  
}  
cn.Close();   
}

如何在 C# 中使用数据读取器

您正在使用cmd.ExecuteReader();它将为您的SELECT语句返回一行，因为它使用 COUNT(*) .现在，这可以返回0计数或多个计数。

您需要调用ExecuteScalar，然后检查返回的值。

像这样：

int count = Convert.ToInt32(cmd.ExecuteScalar());
if(count > 0)
{
   // Already exists
}

但更重要的是，您还应该参数化您的SELECT查询。还要确保将你的SqlConnection和SqlCommand对象括在using语句中。阅读有关 SQL 注入的信息，因为您的第一个查询基于字符串串联，并且容易出现 SQL 注入。

你的第一部分代码应该是这样的：

using (SqlConnection cn = new SqlConnection("ConnectionString"))
{
    using (
        SqlCommand cmd =
            new SqlCommand("select count(*) from studentreg where registrationno = @registrationNumber", cn))
    {
        cmd.Parameters.Add(new SqlParameter("@registrationNumber", SqlDbType.VarChar) {Value = txtrgno.Text});
        //or
        //cmd.Parameters.AddWithValue("@registrationNumber", txtregno.Text);
        cn.Open();
        int count = Convert.ToInt32(cmd.ExecuteScalar);
        if (count > 0)
        {
            // Already exists
            MessageBox.Show("Registration No. Already Exists Try Another", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            txtrgno.Text = "";
            txtrgno.Focus();
        }
    }
}
相关文章：