c＃ado.net sqlparameter失败 [英] c# ado.net sqlparameter fails

问题描述

我从c＃传递本地化语言（波斯语）中的sqlparameter但没有行检索。数据库已经整理为persioan_100_ci_ai，表格为collat​​e database_default

 SqlCommand cmd =  new  SqlCommand（）; 
 DataTable dt =  new  DataTable（）; 
 SqlDataReader dr =  default （SqlDataReader）; 
 dt.TableName =   temp; 
 尝试 {
  if （！（conn.State == ConnectionState.Closed） ））
 conn.Close（）; 
  if （conn.State == ConnectionState.Closed）
 conn.Open（）; 
 cmd.Connection = conn; 
  string  qry =  从用户中选择* WHERE [Name] = @ UserName AND [Pwd] = @ Password; 
 cmd.commandtext = qry; 
 cmd.Parameters.Add（  @ UserName，SqlDbType.NVarChar， 50 ）。值=  ادمین; 
 cmd.Parameters.Add（  @ Password，SqlDbType.NVarChar， 50 ）。值=  ادمین; 
 dr = cmd.ExecuteReader（CommandBehavior.CloseConnection）; 
  if （dr.HasRows）{
 dt.Load（dr）; 
} 
 
  return  dt; 
}  catch （例外情况）{
  return  空; 
} 最后 {
 dt =  null ; 
 cmd.Connection =  null ; 
 cmd.Parameters.Clear（）; 
 cmd.Dispose（）; 
} 

适用于SSMS

 声明  @ UserName   nvarchar （ 50 ）= ' ادمين' 
 声明  @密码  nvarchar （ 50 ）= ' ادمين' 
 选择 * 来自用户其中 [name] = @ UserName 和 [Pwd] =  @ Password  

当我在查询中嵌入变量而不是参数时它甚至可以工作

 SqlCommand cmd =  new  SqlCommand（）; 
 DataTable dt =  new  DataTable（）; 
 SqlDataReader dr =  default （SqlDataReader）; 
  string  pLoginName =  ادمین; 
  string  pPassword =  ادمین; 
 dt.TableName =   temp; 
 尝试 {
  if （！（conn.State == ConnectionState.Closed） ））
 conn.Close（）; 
  if （conn.State == ConnectionState.Closed）
 conn.Open（）; 
 cmd.Connection = conn; 
  string  qry =  从用户中选择* WHERE [Name] =' + pLoginName +  'AND [Pwd] =' + pPassword +  '; 
 cmd.CommandText = qry; 
 dr = cmd.ExecuteReader（CommandBehavior.CloseConnection）; 
  if （dr.HasRows）{
 dt.Load（dr）; 
} 
 
  return  dt; 
}  catch （例外情况）{
  return  空; 
} 最后 {
 dt =  null ; 
 cmd.Connection =  null ; 
 cmd.Parameters.Clear（）; 
 cmd.Dispose（）; 
} 

无法弄清楚我错在哪里。如果有人可以指出的话，请指出。

解决方案

我在想这是：



< pre lang =c＃>} 最后 {
dt = null ;

（实验已经排除了这一点。）



让我们试着简化你的代码然后 - 并使用使用语句。

 DataTable dt = < span class =code-keyword> null ; 
 使用（SqlCommand cmd = conn.CreateCommand（））
 {
 cmd.commandtext =  从用户选择* WHERE [Name] = @ UserName AND [Pwd] = @ Password; 
 cmd.Parameters.AddWithValue（  @ UserName， ادمین）; 
 cmd.Parameters.AddWithValue（  @ Password， ادمین）; 
  if （conn.State == ConnectionState.Closed）conn.Open（）; 
 使用（SqlDataReader dr = cmd.ExecuteReader（CommandBehavior.CloseConnection））
 {
 dt =  new  DataTable（  temp）; 
 dt.Load（dr）; 
} 
} 
 返回 dt; 

您会注意到我还删除了try / catch，如果需要，您可以添加一个。

而不是查询：

  string  qry =  从用户选择* WHERE [名称] =' + pLoginName +  '和[Pwd] =' + pPassword +  '; 

使用存储过程 [< a href =http://msdn.microsoft.com/en-us/library/bb399407%28v=vs.110%29.aspxtarget =_ blanktitle =New Window> ^ ] 。



详情请见：

如何：执行存储的P.返回行的程序 [ ^ ]

演练：使用a显示数据GridView Web服务器控件中的存储过程 [ ^ ]



为什么要使用存储过程？由于 SQL注入 [ ^ ]。



SQL注入以及如何避免它[[a href =http://blogs.msdn.com/b/tom/archive/2008/05/29/sql-injection-and- how to to-avoid-it.aspxtarget =_ blanktitle =New Window> ^ ]

如何：保护ASP.NET中的SQL注入 [ ^ ]

您提供的代码适合我。但是，第一个例子甚至不会编译，除非你改为这个。



来自

 cmd.commandtext = qry ; 

to

 cmd.CommandText = qry; 

I am passing sqlparameter in localize language (Persian) from c# but no rows retrieves. Database already collate for persioan_100_ci_ai and tables are collate database_default

SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
SqlDataReader dr = default(SqlDataReader);
dt.TableName = "temp";
try {
    if (!(conn.State == ConnectionState.Closed))
        conn.Close();
    if (conn.State == ConnectionState.Closed)
        conn.Open();
    cmd.Connection = conn;
    string qry = "Select * from users WHERE [Name]=@UserName AND [Pwd]=@Password";
    cmd.commandtext = qry;
    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = "ادمین";
    cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50).Value = "ادمین";
    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    if (dr.HasRows) {
        dt.Load(dr);
    }

    return dt;
} catch (Exception ex) {
    return null;
} finally {
    dt = null;
    cmd.Connection = null;
    cmd.Parameters.Clear();
    cmd.Dispose();
}

It works in SSMS

declare @UserName nvarchar(50) = 'ادمين'
declare @Password nvarchar(50)= 'ادمين'
select * from Users where [name]=@UserName and [Pwd] = @Password 

It even works when I am embedding variables in query instead of parameter

SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
SqlDataReader dr = default(SqlDataReader);
string pLoginName = "ادمین";
string pPassword = "ادمین";
dt.TableName = "temp";
try {
    if (!(conn.State == ConnectionState.Closed))
        conn.Close();
    if (conn.State == ConnectionState.Closed)
        conn.Open();
    cmd.Connection = conn;
    string qry = "Select * from users WHERE [Name]='" + pLoginName + "' AND [Pwd]='" + pPassword + "'";
    cmd.CommandText = qry;
    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    if (dr.HasRows) {
        dt.Load(dr);
    }

    return dt;
} catch (Exception ex) {
    return null;
} finally {
    dt = null;
    cmd.Connection = null;
    cmd.Parameters.Clear();
    cmd.Dispose();
}

Cannot figure out where I am wrong. Please, if any one can point out.

解决方案

I'm thinking it's this:

} finally {
    dt = null;

(Experimentation has ruled that out.)

Let's try to simplify your code then -- and use using statements as well.

DataTable dt = null ;
using ( SqlCommand cmd = conn.CreateCommand() )
{
    cmd.commandtext = "Select * from users WHERE [Name]=@UserName AND [Pwd]=@Password";
    cmd.Parameters.AddWithValue("@UserName" , "ادمین" ) ;
    cmd.Parameters.AddWithValue("@Password" , "ادمین" ) ;
    if (conn.State == ConnectionState.Closed) conn.Open();
    using ( SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection) )
    {
        dt = new DataTable("temp");
        dt.Load(dr);
    }
}
return dt;

You'll notice I also removed the try/catch, you can add one back in if you need to.

Instead of such of query:

string qry = "Select * from users WHERE [Name]='" + pLoginName + "' AND [Pwd]='" + pPassword + "'";

use stored procedure[^].

For further information, please see:
How to: Execute a Stored Procedure that Returns Rows[^]
Walkthrough: Displaying Data Using a Stored Procedure in the GridView Web Server Control[^]

Why to use stored procedures? Because of SQL Injection[^].

SQL Injection and how to avoid it[^]
How To: Protect From SQL Injection in ASP.NET[^]

The code you have provided works for me. However, the first example wont even compile unless you change to this.

from

cmd.commandtext = qry;

to

cmd.CommandText = qry;

这篇关于c＃ado.net sqlparameter失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！