如何在使用存储过程的地方传递ID [英] how to pass id for where using stored procedure

问题描述

在c#中使用MySql存储过程时，如何传递where条件的id.

存放程序:

How to pass id for where condition when using MySql stored procedure in c#.

store procedure :

drop procedure if exists Update_MT_data;

delimiter #
create procedure Update_MT_data
(In Accountname varchar(200), Transfilename varchar(200))

begin
Update mt 
set

Filename = Filename,
Physician=Physician,
Lastname=Lastname,
Firstname=Firstname,
Middlename=Middlename,
PatientID=PatientID,
MRNO=MRNO,
dateofadmission=dateofadmission,
Dateofservice=Dateofservice,
RPhysician=RPhysician,
dateofbirth=dateofbirth,
dictateddatesdifferent=dictateddatesdifferent,
Remarks=Remarks,
dateofdictation=dateofdictation,
ccphy=ccphy,
ccphyinvalid=ccphyinvalid,
worktype=worktype

where Accountname=Accountname and Transfilename=Transfilename 
;

end #

delimiter ; 

类别:

Class :

public int UpdateMTdata(string Accountname, string Transfilename, string Filename, DateTime dateofdictation, string Lastname, string Firstname, string Middlename, string Dateofservice, string dateofadmission, string MRNO, string Physician, string Remarks, string PatientID, string RPhysician, string dateofbirth, string worktype, string ccphy, string ccphyinvalid, string dictateddatesdifferent)
       {
           MySqlConnection con = new MySqlConnection("SERVER=192.168.0.10;DATABASE=anju2;UID=heman;PASSWORD=harjeet;");
           AddEditor aedit = new AddEditor();

           try
           {

               con.Open();

               MySqlCommand command = new MySqlCommand("Update_MT_data", con);
               command.CommandType = CommandType.StoredProcedure;
               command.Parameters.Add(new MySqlParameter("?Accountname", MySql.Data.MySqlClient.MySqlDbType.VarChar, 200));
               command.Parameters.Add(new MySqlParameter("?Transfilename", MySql.Data.MySqlClient.MySqlDbType.VarChar, 200));
               command.Parameters.Add(new MySqlParameter("?Filename", MySql.Data.MySqlClient.MySqlDbType.VarChar, 200));
                                        
               command.Parameters.Add(new MySqlParameter("?dateofdictation", MySql.Data.MySqlClient.MySqlDbType.Date));
               
               command.Parameters.Add(new MySqlParameter("?Lastname", MySql.Data.MySqlClient.MySqlDbType.VarChar, 200));
               command.Parameters.Add(new MySqlParameter("?Firstname", MySql.Data.MySqlClient.MySqlDbType.VarChar, 200));
               command.Parameters.Add(new MySqlParameter("?Middlename", MySql.Data.MySqlClient.MySqlDbType.VarChar, 250));
               command.Parameters.Add(new MySqlParameter("?Dateofservice", MySql.Data.MySqlClient.MySqlDbType.VarChar, 200));
               command.Parameters.Add(new MySqlParameter("?dateofadmission", MySql.Data.MySqlClient.MySqlDbType.VarChar, 200));
               
              
               command.Parameters.Add(new MySqlParameter("?MRNO", MySql.Data.MySqlClient.MySqlDbType.VarChar, 200));
                            
               command.Parameters.Add(new MySqlParameter("?Physician", MySql.Data.MySqlClient.MySqlDbType.VarChar, 250));
               command.Parameters.Add(new MySqlParameter("?Remarks", MySql.Data.MySqlClient.MySqlDbType.VarChar, 250));
               command.Parameters.Add(new MySqlParameter("?PatientID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 250));
             
               command.Parameters.Add(new MySqlParameter("?RPhysician", MySql.Data.MySqlClient.MySqlDbType.VarChar, 250));
               command.Parameters.Add(new MySqlParameter("?dateofbirth", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255));
               command.Parameters.Add(new MySqlParameter("?worktype", MySql.Data.MySqlClient.MySqlDbType.VarChar, 250));
               
               command.Parameters.Add(new MySqlParameter("?ccphy", MySql.Data.MySqlClient.MySqlDbType.VarChar, 1000));
               command.Parameters.Add(new MySqlParameter("?ccphyinvalid", MySql.Data.MySqlClient.MySqlDbType.VarChar, 1000));
              
               command.Parameters.Add(new MySqlParameter("?dictateddatesdifferent", MySql.Data.MySqlClient.MySqlDbType.VarChar, 200));
               
               



               command.Parameters[0].Value = Accountname;
               command.Parameters[1].Value = Transfilename;
               command.Parameters[2].Value = Filename;
               command.Parameters[3].Value = dateofdictation;
               command.Parameters[4].Value = Lastname;
               command.Parameters[5].Value = Firstname;
               command.Parameters[6].Value = Middlename;
               command.Parameters[7].Value = Dateofservice;
               command.Parameters[8].Value = dateofadmission;
               command.Parameters[9].Value = MRNO;
               command.Parameters[10].Value = Physician;
               command.Parameters[11].Value = Remarks;
               command.Parameters[12].Value = PatientID;
               command.Parameters[13].Value = RPhysician;
               command.Parameters[14].Value = dateofbirth;
               command.Parameters[15].Value = worktype;
               command.Parameters[16].Value = ccphy;
               command.Parameters[17].Value = ccphyinvalid;
               command.Parameters[18].Value = dictateddatesdifferent;
              


               //command.ExecuteNonQuery(); 

               int updatestr = (Int32)command.ExecuteScalar();

               con.Close();
               return updatestr;


           }
           catch (Exception ex)
           {
               string str = ex.ToString();
               return 0;
               
           }
           finally
           {
               con.Close();
           }
       }

背后的代码:

Code behind :

ae.UpdateMTdata(d.Account_Name, dgvDistribution[11, dgvDistribution.CurrentRow.Index].Value.ToString(), dgvDistribution[1, dgvDistribution.CurrentRow.Index].Value.ToString(), Convert.ToDateTime(d.dateofdictation), d.Lastname, d.Firstname, d.Middlename, d.Dateofservice, d.dateofadmission, d.MRNO, d.DictatorName, d.Remarks, d.PatientID, d.RPhysician, d.dateofbirth, d.worktype, d.ccphy, d.ccphyinvalid, d.dictateddatesdifferent);

代码格式为

Code formatted

推荐答案

SqlCommand cmd = new SqlCommand();
//... other code
cmd.Parameters.AddWithValue("@id", myIDValue);
cmd.ExecuteQuery();

只需将VoiceID添加到:
-存储过程参数
-MySqlCommand参数
-UpdateMTdata方法参数
-并在调用后面的代码中

因此，就像所有其他参数一样，您只是在WHERE子句中使用它，而不是SET子句.

Just add the VoiceID to:
- stored procedure parameters
- MySqlCommand Parameters
- UpdateMTdata method parameters
- and in the code behind call

So it''s just like all other parameters, you''re just using it in the WHERE clause instead of the SET clause.

这篇关于如何在使用存储过程的地方传递ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！