Return value in sql statement

Question

i have below in vb.net but works very well but when i convert to c# i dont get the return value wherever i call the object

Public Function SetPatient(ByVal ConnString As String, ByVal tPatient As PatientType) As ReturnType

Dim result As New ReturnType

If tPatient Is Nothing Then

Throw New ArgumentNullException("tPatient is null")

Else

Using conn As New SqlConnection(ConnString)

Using Command As SqlCommand = conn.CreateCommand()

conn.Open()

If tPatient.patient_id = 0 Then

Command.CommandText = "INSERT INTO patients (patient_no,age,blood_group,dob,location,height,contact,marital_status,other_names,sex,sirname,weight,date_of_admision,checked,patient_type_id) VALUES (@patient_no,@age,@blood_group,@dob,@location,@height,@contact,@marital_status,@other_names,@sex,@sirname,@weight,@date_of_admision,@checked,@patient_type_id) SET @patient_id = (SELECT SCOPE_IDENTITY());INSERT INTO next_of_kin (patient_id,contact_name,contact_phone) values (@patient_id,@contact_name,@contact_phone)"

Else

Command.CommandText = "UPDATE patients set patient_no=@patient_no,age=@age,blood_group=@blood_group,dob=@dob,location=@location,height=@height,contact=@contact,marital_status=@marital_status,other_names=@other_names, sex=@sex,sirname=@sirname,weight=@weight,date_of_admision=@date_of_admision,checked=@checked,patient_type_id=@patient_type_id WHERE patient_id=@patient_id;if (select count(*) from next_of_kin where patient_id=@patient_id )>0 begin update next_of_kin set contact_name=@contact_name,contact_phone=@contact_phone WHERE patient_id=@patient_id end else begin INSERT INTO next_of_kin (patient_id,contact_name,contact_phone) values (@patient_id,@contact_name,@contact_phone) end"

End If

Dim objParameter As New SqlClient.SqlParameter("@Patient_id", SqlDbType.Int)

objParameter.Direction = ParameterDirection.InputOutput

objParameter.Value = tPatient.patient_id

Command.Parameters.Add(objParameter)

'If tPatient.patient_id = 0 Then

' Command.Parameters.Add("@patient_id", SqlDbType.Int, ParameterDirection.Input).Value = DBNull.Value

'Else

' Command.Parameters.Add("@patient_id", SqlDbType.Int, ParameterDirection.Input).Value = tPatient.patient_id

'End If

Command.Parameters.Add("@patient_no", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.patient_no

Command.Parameters.Add("@age", SqlDbType.Int, ParameterDirection.Input).Value = tPatient.age

Command.Parameters.Add("@blood_group", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.blood_group

Command.Parameters.Add("@dob", SqlDbType.Date, ParameterDirection.Input).Value = tPatient.dob

Command.Parameters.Add("@date_of_admision", SqlDbType.Date, ParameterDirection.Input).Value = tPatient.date_of_admision

Command.Parameters.Add("@location", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.location

Command.Parameters.Add("@height", SqlDbType.Int, ParameterDirection.Input).Value = tPatient.height

Command.Parameters.Add("@contact", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.contact

Command.Parameters.Add("@marital_status", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.marital_status

Command.Parameters.Add("@other_names", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.other_names

Command.Parameters.Add("@sex", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.sex

Command.Parameters.Add("@sirname", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.sirname

Command.Parameters.Add("@weight", SqlDbType.Int, ParameterDirection.Input).Value = tPatient.weight

Command.Parameters.Add("@patient_type_id", SqlDbType.Int, ParameterDirection.Input).Value = tPatient.patient_type_id

Command.Parameters.Add("@contact_name", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.contact_name

Command.Parameters.Add("@contact_phone", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.contact_phone

If tPatient.checked Then

Command.Parameters.Add("@checked", SqlDbType.Char, 1, ParameterDirection.Input).Value = "T"

Else

Command.Parameters.Add("@checked", SqlDbType.Char, 1, ParameterDirection.Input).Value = "F"

End If

'Try

result.return_code = Command.ExecuteNonQuery()

If result.return_code = 1 Then

result.return_identity_id = Command.Parameters("@Patient_id").Value

result.return_message = "Patient Process succesfull"

Else

End If

'Catch ex As Exception

' result.return_message = "PatientRegistration Status already exists"

'End Try

End Using

End Using

End If

Return result

End Function

below is converted which does not return value how can i do it

public ReturnType SetPatient(string ConnString, PatientType tPatient)

{

ReturnType result = new ReturnType();

if (tPatient == null) {

throw new ArgumentNullException("tPatient is null");

} else {

using (SqlConnection conn = new SqlConnection(ConnString)) {

using (SqlCommand Command = conn.CreateCommand()) {

conn.Open();

if (tPatient.patient_id == 0) {

Command.CommandText = "INSERT INTO patients (patient_no,age,blood_group,dob,location,height,contact,marital_status,other_names,sex,sirname,weight,date_of_admision,checked,patient_type_id) VALUES (@patient_no,@age,@blood_group,@dob,@location,@height,@contact,@marital_status,@other_names,@sex,@sirname,@weight,@date_of_admision,@checked,@patient_type_id) SET @patient_id = (SELECT SCOPE_IDENTITY());INSERT INTO next_of_kin (patient_id,contact_name,contact_phone) values (@patient_id,@contact_name,@contact_phone)";

} else {

Command.CommandText = "UPDATE patients set patient_no=@patient_no,age=@age,blood_group=@blood_group,dob=@dob,location=@location,height=@height,contact=@contact,marital_status=@marital_status,other_names=@other_names, sex=@sex,sirname=@sirname,weight=@weight,date_of_admision=@date_of_admision,checked=@checked,patient_type_id=@patient_type_id WHERE patient_id=@patient_id;if (select count(*) from next_of_kin where patient_id=@patient_id )>0 begin update next_of_kin set contact_name=@contact_name,contact_phone=@contact_phone WHERE patient_id=@patient_id end else begin INSERT INTO next_of_kin (patient_id,contact_name,contact_phone) values (@patient_id,@contact_name,@contact_phone) end";

}

SqlClient.SqlParameter objParameter = new SqlClient.SqlParameter("@Patient_id", SqlDbType.Int);

objParameter.Direction = ParameterDirection.InputOutput;

objParameter.Value = tPatient.patient_id;

Command.Parameters.Add(objParameter);

//If tPatient.patient_id = 0 Then

// Command.Parameters.Add("@patient_id", SqlDbType.Int, ParameterDirection.Input).Value = DBNull.Value

//Else

// Command.Parameters.Add("@patient_id", SqlDbType.Int, ParameterDirection.Input).Value = tPatient.patient_id

//End If

Command.Parameters.Add("@patient_no", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.patient_no;

Command.Parameters.Add("@age", SqlDbType.Int, ParameterDirection.Input).Value = tPatient.age;

Command.Parameters.Add("@blood_group", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.blood_group;

Command.Parameters.Add("@dob", SqlDbType.Date, ParameterDirection.Input).Value = tPatient.dob;

Command.Parameters.Add("@date_of_admision", SqlDbType.Date, ParameterDirection.Input).Value = tPatient.date_of_admision;

Command.Parameters.Add("@location", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.location;

Command.Parameters.Add("@height", SqlDbType.Int, ParameterDirection.Input).Value = tPatient.height;

Command.Parameters.Add("@contact", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.contact;

Command.Parameters.Add("@marital_status", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.marital_status;

Command.Parameters.Add("@other_names", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.other_names;

Command.Parameters.Add("@sex", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.sex;

Command.Parameters.Add("@sirname", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.sirname;

Command.Parameters.Add("@weight", SqlDbType.Int, ParameterDirection.Input).Value = tPatient.weight;

Command.Parameters.Add("@patient_type_id", SqlDbType.Int, ParameterDirection.Input).Value = tPatient.patient_type_id;

Command.Parameters.Add("@contact_name", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.contact_name;

Command.Parameters.Add("@contact_phone", SqlDbType.VarChar, 255, ParameterDirection.Input).Value = tPatient.contact_phone;

if (tPatient.@checked) {

Command.Parameters.Add("@checked", SqlDbType.Char, 1, ParameterDirection.Input).Value = "T";

} else {

Command.Parameters.Add("@checked", SqlDbType.Char, 1, ParameterDirection.Input).Value = "F";

}

//Try

result.return_code = Command.ExecuteNonQuery();

if (result.return_code == 1) {

result.return_identity_id = Command.Parameters("@Patient_id").Value;

result.return_message = "Patient Process succesfull";

} else {

}

//Catch ex As Exception

// result.return_message = "PatientRegistration Status already exists"

//End Try

}

}

}

return result;

}