我已经编写了一些代码来建立与SQL Server的连接,然后执行选择过程以从SQL Server中的数据表中获取所有数据,但是在声明新的SqlDataAdapter的命令中抛出InvalidOperationException,请帮助我解决此问题错误。

public class dBConnect
{
    //create SQLconnection variable
    private SqlConnection con;
    //create default constructor that passing a string to con
    public dBConnect()
    {
        try
        {
            con = new SqlConnection(@"Server=Trump\SQLEXPRESS;
        Database=Demo;User Id=sa;Password = stevejobs;");
        }
        catch(Exception exCon)
        {
            Console.WriteLine("Unable to connect to database: {0}", exCon);
        }
    }
    //create Select method to Pour the data into the DataTable
    public DataTable SelectAll(string procName, SqlParameter[] para = null)
    {
        //create a DataTable to store Data from DB
        DataTable dt = new DataTable();
        //create SQLCommand
        SqlCommand cmd = new SqlCommand(procName, con);
        //declare that cmdType is sp
        cmd.CommandType = CommandType.StoredProcedure;
        //input parameter of cmd
        if (para != null)
            cmd.Parameters.AddRange(para);
        //create dataAdapter object

        //InvalidOperationException was thrown at here
        SqlDataAdapter da = new SqlDataAdapter(cmd);

        //declare that cmd is select command of da
        da.SelectCommand = cmd;
        //use try/catch/finally to establish a connection
        try
        {
            con.Open();
            da.Fill(dt);
        }
        catch(Exception sqlEx)
        {
            Console.WriteLine(@":Unable to establish a connection: {0}", sqlEx);
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
        return dt;
    }
}


}

最佳答案

在这里清理代码:

public DataTable SelectAll(string procName, SqlParameter[] para = null)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection con = new SqlConnection(connString))
                {
                    con.Open();

                    using (SqlCommand cmd = new SqlCommand(procName, con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        if (para != null)
                            cmd.Parameters.AddRange(para);

                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {

                            da.Fill(dt);
                        }
                    }
                }
            }
            catch (Exception sqlEx)
            {
                Console.WriteLine(@":Unable to establish a connection: {0}", sqlEx);
            }

            return dt;

        }

关于c# - 创建新的SqlDataAdapter时出现C#InvalidOperationException,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/42708359/

10-13 07:39