data adapter.select command.connection

Question

I am using below code to get data from database.But immediately,when breakpoint goes to any line after var k,exception is raised.

 DataSet ds = new DataSet();
            try
            {
                using (SqlConnection con = new SqlConnection())

                {
                    con.ConnectionString = @"Data Source = sql\db1,5000; Initial Catalog = uatdb; Integrated Security = SSPI";

                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        var k = con.State;

update:
added this line from answer,but not help,still the same error

                        da.selectcommand=new sqlcommand();

                        da.SelectCommand.Connection.ConnectionString= con.ConnectionString;
                        da.SelectCommand.CommandText = "usp_checkstatus";
                        da.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = TextBox1.Text.ToString(); ;
                        da.SelectCommand.CommandType = CommandType.StoredProcedure;


                        //  da.SelectCommand.Parameters.AddWithValue("@buildid", TextBox1.Text);

                        da.Fill(ds);
                        return ds;


                    }
                }
            }

****Exception details:****

object reference not set to an instance of object.Connection state is closed always and DB,connection strings are correct,i am not opening connection,since data adapter will open connection for me.

Answer may be simple,but it ate nearly 4 hours of my time now.Any help is much appreciated.

Note : I could get this working with code in below question using sqlcommand,but i want tto try using data adapter

How to use a DataAdapter with stored procedure and parameter

Answer 1

You should set the SqlConnection created to your SqlDataAdapter. It is not necessary to open it because the Adapter opens it for you if it is closed but it is essential that the Adapter knows about the connection.

DataSet ds = new DataSet();
try
{
    using (SqlConnection con = new SqlConnection(@"Data Source = sql\db1,5000; 
           Initial Catalog = uatdb; Integrated Security = SSPI"))
    using (SqlDataAdapter da = new SqlDataAdapter("usp_checkstatus", con))
    {
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        da.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text);
        da.Fill(ds);
        return ds;
    }
}

Also there is no need to create a specific SqlCommand. When you create the Adapter and pass a stored procedure name a new SqlCommand is automatically created for you, of course you still need to set its parameters and its CommandType. (A note on the parameter, I am not sure if this could lead to wrong results in your sp but it seems to be safe that if you want to pass an integer then convert the input to an integer)

Answer 2

You have to instantiate the SelectCommand property:

 using (SqlDataAdapter da = new SqlDataAdapter())
   {
    da.SelectCommand = new SqlCommand();
    da.SelectCommand.Connection=con;
    ..
   }

Or create SqlCommand object and assign its reference to SelectCommand property.

var cmd = new SqlCommand();
cmd.CommandText = "your-proc-name";
cmd.Connection = con;
.....
adp.SelectCommand = cmd;