C# InvalidOperationException When Creating A New SqlDataAdapter

I`ve written some code to establish a connection to SQL Server, then execute select procedure to take all data from my data table in SQL server, but it throw a InvalidOperationExce

Solution 1:

You should:

cmd.CommandText = "your Stored Procedure here.";
cmd.CommandType = CommandType.StoredProcedure;
//input parameter of cmd
if (para != null)
cmd.Parameters.AddRange(para);
//create dataAdapter object

Put your con.Open(); above

or

Just take a look at this steps

 DataTable dt = new DataTable();
 using (SqlConnection con = new SqlConnection(connectionString))
 {
     con.Open();
      using (SqlCommand cmd = con.CreateCommand())
      {
       //sample stored procedure with parameter:
       // "exec yourstoredProcedureName '" + param1+ "','" + param2+ "'";
       cmd.CommandText = "Your Stored Procedure Here";
       cmd.CommandType =CommandType.StoredProcedure;
       using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
        {
            adp.Fill(dt);
             return dt;
        }
      }
  }

Solution 2:

Cleaning up your code a bit here:

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;

        }