尝试在SqlDataAdapter中传递SqlCommand作为参数

问题描述

我已经成功建立了执行选择命令的方法.一切正常.然后，我更改 SqlDataAdapter DA = new SqlDataAdapter();

I've successfully built up my method to execute a select command. It is working fine. Then I change my code for SqlDataAdapter DA = new SqlDataAdapter();

我试图在参数中将 SqlCommand 作为 CommandType.Text 传递，但我无法成功完成.我出错了.有什么办法可以将其作为参数传递的吗?请查看我的代码.

I tried to pass SqlCommand as CommandType.Text in the parameters but I can not do it successfully. I get error. Is there any way if I can pass it as parameters. Please see my code.

运行代码(aspx页面代码)

Running code (aspx page code)

if ((!string.IsNullOrEmpty(user_login.Value)) && (!string.IsNullOrEmpty(user_pass.Value)))
{
        // username & password logic
        DataTable dt = new DataTable();
        string strQuery = "SELECT 1 FROM TBL_USER_INFO WHERE USERNAME = @USERNAME AND PASSWORD = @PASSWORD";

        SqlCommand cmd = new SqlCommand(strQuery);
        cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = user_login.Value.Trim();
        cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = user_pass.Value.Trim();

        DBConnection conn_ = new DBConnection();

        dt = conn_.SelectData(cmd);

        if (conn_.SQL_dt.Rows.Count > 0)
        {
            Response.Redirect("Home.aspx", false);
        }
    }

成功的连接类代码

public DataTable SelectData(SqlCommand command)
{
    try
    {
       conn.Open();

       SqlDataAdapter DA = new SqlDataAdapter();

       command.CommandType = CommandType.Text;
       command.Connection = conn;

       DA.SelectCommand = command;
       DA.Fill(SQL_dt);

       return SQL_dt;
   }
   catch (Exception ex)
   {
       return null;
   }
   finally
   {
       conn.Close();
   }
}

如何传递 CommandType.Text 作为 SqlDataAdapter 的参数?

错误代码

public DataTable SelectData(SqlCommand command)
{
    try
    {
        conn.Open();

        SqlDataAdapter DA = new SqlDataAdapter(command.CommandType.ToString(), conn);

        // command.CommandType = CommandType.Text;
        // command.Connection = conn;
        DA.SelectCommand = command;
        DA.Fill(SQL_dt);

        return SQL_dt;
    }
    catch (Exception ex)
    {
        return null;
    }
    finally
    {
        conn.Close();
    }
}

我收到此错误:

推荐答案

public DataTable SelectData(string query)
        {
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection("Your Connection String here"))
            {
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = query;
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                    {
                        adp.Fill(dt);
                        return dt;
                    }
                }
            }
        }

要使用:

SelectData("select * from yourTable");

这篇关于尝试在SqlDataAdapter中传递SqlCommand作为参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！