Check if a string ID NO exists in the database or not?

Question

How can I check if the string value ID NO exists in the database or not , Before I used this code to check if integer value exists in the database , how to change same code to check string id no exist or not :

This is the code :

public int? GetPatientID(int Patient_id)
        {
            DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
            DataTable dt = new DataTable();
            SqlParameter[] Param = new SqlParameter[1];
            Param[0] = new SqlParameter("@Patient_id", SqlDbType.Int);
            Param[0].Value = Patient_id;
            dt = DAL.SelectData("GET_PATIENT_IDNO", Param);
            DAL.close();

            // if there is at least one row
            if (dt.Rows.Count > 0)
            {
                DataRow row = dt.Rows[0];
                int? patientIDNumber = row.Field<int>("Patient_id");
                return patientIDNumber;
            }

            // return null otherwise
            return null;
        }

This is the stored procedure :

create proc [dbo].[GET_PATIENT_IDNO]
@Patient_id varchar(50)
as 
select Patient_id from Patients
where Patient_id = @Patient_id

And finally this is the validation code :

private void textIDNO_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                if (textIDNO.Text == null || textIDNO.Text == "0" )
                {
                    txtpcfileno.Focus();
                }
                else
                {
                    var patientIDNumber = order.GetPatientID(Convert.ToInt32(textIDNO.Text)); // int?
                    bool patientExists = patientIDNumber.HasValue;
                    if (patientExists == true)
                    {
                        MessageBox.Show("id no used before ", "ID exist", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                    }
                }
                
                           
            }
        }

please any help how to change the code to check string value id no exists or not I am using c# and microsoft sql server ?

Answer 1

This way also working :

public string GetPatientID(string Patient_id)
        {
            DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
            DataTable dt = new DataTable();
            SqlParameter[] Param = new SqlParameter[1];
            Param[0] = new SqlParameter("@Patient_id", SqlDbType.VarChar,50);
            Param[0].Value = Patient_id;
            dt = DAL.SelectData("GET_PATIENT_IDNO", Param);
            DAL.close();

            // if there is at least one row
            if (dt.Rows.Count > 0)
            {
                DataRow row = dt.Rows[0];
                string patientIDNumber = row.Field<string>("Patient_id");
                return patientIDNumber;
            }

            // return null otherwise
            return null;
        }

private void textIDNO_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                if (textIDNO.Text == null || textIDNO.Text == "0" )
                {
                    txtpcfileno.Focus();
                }
                else
                {
                    var patientIDNumber = order.GetPatientID(textIDNO.Text); 
                
                    if (patientIDNumber != null && !string.IsNullOrWhiteSpace(patientIDNumber))
                    {
                        MessageBox.Show("ID NO used before ", " EXIST ID ", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                    }
                }
                
         
                
            }
        }

Answer 2

try this:

public bool IfPatientExists(string Patient_id)
        {
            
            DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
            var param=  new SqlParameter("@Patient_id", SqlDbType.VarChar,50);
            param.Value = Patient_id;
            var dt = DAL.SelectData("GET_PATIENT_IDNO", new SqlParameter[]{param});
            DAL.close();
             return  dt.Rows.Count > 0; 
        }

and textIDNO_KeyDown code

var patientExists = order.IfPatientExists(textIDNO.Text); 
 if (patientExists)
{
...your code
}