转换的查询参数化查询asp.net [英] Convert query to parametrized asp.net query

问题描述

我将如何让这个参数化？！

 查询字符串=;            查询+ =SELECT DistID向分销商;
            查询+ =其中username ='+ username_id.Text +';
            查询+ =和密码='+ password.Text +';            GeneralFunctions.GetData（查询）;
 

是否可以在这里完成或将有GetData方法里面做什么？

下面是两种方法：

 公共静态数据表中的GetData（查询字符串）
{
    SqlDataAdapter的DataAdapter的;
    数据表的表;    尝试
    {
        DataAdapter的=新SqlDataAdapter的（查询，GetConnectionString（））;
        表=新的DataTable（）;        DataAdapter.Fill方法（表）;
        返回表;
    }
    赶上（异常前）
    {
    }
    最后
    {
        DataAdapter的= NULL;
        表= NULL;
    }    返回表;
}公共静态字符串GetConnectionString（）
{
    字符串的ConnectionString = ConfigurationManager.ConnectionStrings [CAPortalConnectionString] .ConnectionString;    返回的connectionString;
}
 

解决方案

我建议你设计特定的方法来查询数据库，如：

 公共静态诠释？ GetDistID（用户名字符串，字符串密码）
{
    使用（VAR康恩=新的SqlConnection（GetConnectionString（）））
    使用（VAR CMD = conn.CreateCommand（））
    {
        conn.Open（）;
        cmd.CommandText =
        @选择
              DistID
          从
              经销商
          哪里
              用户名= @用户名
          和
              密码= @密码;
        cmd.Parameters.AddWithValue（@用户名的用户名）;
        cmd.Parameters.AddWithValue（@密码，密码）;
        使用（VAR读卡器= cmd.ExecuteReader（））
        {
            如果（！reader.Read（））
            {
                // 未找到结果
                返回null;
            }
            返回reader.GetInt32（reader.GetOrdinal（DistID））;
        }
    }
}
 

和则：

  VAR distId = GeneralFunctions.GetDistID（username_id.Text，password.Text）;
 

没有必要的DataTable /套/适配器。与强类型的对象。

How would I make this parametrized ?!

string query = "";

            query += " SELECT DistID FROM Distributor";
            query += " WHERE Username = '" + username_id.Text + "'";
            query += " AND Password = '" + password.Text + "'";

            GeneralFunctions.GetData( query );

Can it be done here or would it have to be done inside the GetData method?

Here are the two methods:

public static DataTable GetData ( string query )
{
    SqlDataAdapter dataAdapter;
    DataTable table;

    try
    {
        dataAdapter = new SqlDataAdapter( query, GetConnectionString() );
        table = new DataTable();

        dataAdapter.Fill( table );
        return table;
    }
    catch ( Exception ex )
    {
    }
    finally
    {
        dataAdapter = null;
        table = null;
    }

    return table;
}

public static string GetConnectionString ()
{
    string connectionString = ConfigurationManager.ConnectionStrings[ "CAPortalConnectionString" ].ConnectionString;

    return connectionString;
}

解决方案

I'd recommend you designing specific methods to query your database, like this:

public static int? GetDistID(string username, string password)
{
    using (var conn = new SqlConnection(GetConnectionString()))
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = 
        @"SELECT 
              DistID 
          FROM 
              Distributor
          WHERE 
              Username = @username 
          AND 
              Password = @password";
        cmd.Parameters.AddWithValue("@username", username);
        cmd.Parameters.AddWithValue("@password", password);
        using (var reader = cmd.ExecuteReader())
        {
            if (!reader.Read())
            {
                // no results found
                return null;
            }
            return reader.GetInt32(reader.GetOrdinal("DistID"));
        }
    }
}

and then:

var distId = GeneralFunctions.GetDistID(username_id.Text, password.Text);

No need of DataTables/Sets/Adapters. Work with strongly typed objects.

这篇关于转换的查询参数化查询asp.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！