为什么此代码不返回值 [英] Why is this code not returning a value
问题描述
Hi All
调试输出时< g class =" gr_ gr_97 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace"数据-GR-ID =" 97" ID = QUOT; 97"> PARAMATERS< / g取代;正在返回< g class =" gr_ gr_94 gr-alert
gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep"数据-GR-ID =" 94" ID = QUOT; 94">空< / g取代;串。 SQL StoredProc中的所有变量名都与您在代码中看到的相同。
执行存储过程的工作原理为< g class = " gr_ gr_222 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace"数据-GR-ID =" 222" ID = QUOT; 222"> intened< / g取代;来自SQL Server。
private bool AuthenticateUser（string username，string password）
;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; {
// ConfigurationManager类位于System.Configuration命名空间中
串CS = ConfigurationManager.ConnectionStrings [" MSC-UFCFUD-60-M"]。ConnectionString.ToString（）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; // SqlConnection位于System.Data.SqlClient命名空间中
using（SqlConnection conn = new SqlConnection（CS））
{
使用（的SqlCommand CMD =新的SqlCommand（QUOT; usp_AuthenticateUser_Select" ;,康恩））
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP; {
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; cmd.CommandType = CommandType.StoredProcedure;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP; cmd.Parameters.AddWithValue（QUOT; @ zUserName" ;,用户名）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; cmd.Parameters.AddWithValue（" @ zPassword"，password）;
;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; cmd.Parameters.Add（QUOT; @ zEndUserType" ;, SqlDbType.NVarChar，30）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; cmd.Parameters [" @ zEndUserType"]方向= ParameterDirection.Output;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; cmd.Parameters.Add（QUOT; @ blnUserMatch" ;, SqlDbType.Bit）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; cmd.Parameters [" @ blnUserMatch"]方向= ParameterDirection.Output;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; // cmd.Parameters.Add（" @ zEndUserType"，System.Data.SqlDbType.NVarChar，30）.Direction = System.Data.ParameterDirection.ReturnValue;
conn.Open（）;&
&NBSP; cmd.ExecuteReader（）;
&NBSP;串zEndUserTypeName = Convert.ToString（cmd.Parameters [" @ zEndUserType"]。值）的ToString（）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;串zEndUserTypeName2 = Convert.ToString（cmd.Parameters [" @ zEndUserType"]。值）的ToString（）;
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; conn.Close（）;
$
&NBSP; }
;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
< p style ="margin：0px 0px 10.66px"> 如果存储过程没有
SELECT 返回行，然后尝试 ExecuteNonQuery 而不是
ExecuteReader 。
否则，
"如果您的Command包含输出参数或返回值，则在DataReader关闭之前它们将不可用" [ https://docs.microsoft.com/en-us/dotnet/framework /数据/ ADONET /取回数据-使用-A-DataReader的 ]。
因此，读取数据，关闭阅读器（使用'使用（var dr = cmd.ExecuteReader（）） ... '），
然后读取参数。
Hi All
When debugging output <g class="gr_ gr_97 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="97" id="97">paramaters</g> are returning <g class="gr_ gr_94 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="94" id="94">empty</g> string. All the variable names in SQL StoredProc are the same as what you see in the code.
Executing Stored Procedure works as <g class="gr_ gr_222 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="222" id="222">intened</g> from SQL Server.
private bool AuthenticateUser(string username, string password)
{
// ConfigurationManager class is in System.Configuration namespace
string CS = ConfigurationManager.ConnectionStrings["MSC-UFCFUD-60-M"].ConnectionString.ToString();
// SqlConnection is in System.Data.SqlClient namespace
using (SqlConnection conn = new SqlConnection(CS))
{
using (SqlCommand cmd = new SqlCommand("usp_AuthenticateUser_Select", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@zUserName", username);
cmd.Parameters.AddWithValue("@zPassword", password);
cmd.Parameters.Add("@zEndUserType", SqlDbType.NVarChar, 30);
cmd.Parameters["@zEndUserType"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@blnUserMatch", SqlDbType.Bit);
cmd.Parameters["@blnUserMatch"].Direction = ParameterDirection.Output;
// cmd.Parameters.Add("@zEndUserType", System.Data.SqlDbType.NVarChar, 30).Direction = System.Data.ParameterDirection.ReturnValue;
conn.Open();
cmd.ExecuteReader();
string zEndUserTypeName = Convert.ToString(cmd.Parameters["@zEndUserType"].Value).ToString();
string zEndUserTypeName2 = Convert.ToString(cmd.Parameters["@zEndUserType"].Value).ToString();
conn.Close();
}
If the stored procedure does not have a SELECT that returns rows, then try ExecuteNonQuery instead of ExecuteReader.
Otherwise, "if your Command contains output parameters or return values, they will not be available until the DataReader is closed" [https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader]. Therefore, read the data, close the reader (using ‘using(var dr = cmd.ExecuteReader()) …’), then read the parameters.
这篇关于为什么此代码不返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
