CODEWITHSUNDEEP
c#datatabledataset
Melanie
Melanie

Reputation: 3111

Modifying DataTable which is part of DataSet in C# - changes not showing in DataSet

I'm working in C# and I have a DataSet and a DataTable. I've added the DataTable to the DataSet. Then I populate the DataTable with a SQL query. In the debugger, I can see data in my DataTable. I see my DataTable in the DataSet's list of tables, but it's a null table (i.e., no columns, no data). Why is the data not showing up? Here's my code:

DataSet ds = new DataSet();
DataTable dt = new DataTable("BaseData");
ds.Tables.Add(dt);
List<SqlParameter> paramz = new List<SqlParameter>();
paramz.Add(new SqlParameter("@LitHoldDetailsID", litHoldDetailsID));
dt = LHClassLibrary.LHDataAccessLayer.ExecuteSelect("usp_GetLitHoldDetails_A", paramz);

I've tried

ds.AcceptChanges();

but that doesn't help. Do I really have to to define all the columns in the DataTable ahead of time? This is a query that returns a large number of columns, so I'm hoping I can skip that step.

Upvotes: 0

Views: 3546

Answers (3)

Steve
Steve

Reputation: 216303

I will try to change the order of your code execution in this way

DataSet ds = new DataSet();
List<SqlParameter> paramz = new List<SqlParameter>();
paramz.Add(new SqlParameter("@LitHoldDetailsID", litHoldDetailsID));
DataTable dt = LHClassLibrary.LHDataAccessLayer.ExecuteSelect("usp_GetLitHoldDetails_A", paramz);
dt.TableName = "BaseData";
if(dt.DataSet != null) dt.DataSet.Tables.Remove(dt);
ds.Tables.Add(dt);

I suppose that the ExecuteSelect method will initialize and returns a DataTable with all the columns and rows returned by your stored procedure.

Only at this point the DataTable is added to your DataSet, not before.
In your code, the variable dt is assigned to a DataTable returned by ExecuteSelect but this is not the same reference to the DataTable created before and thus your DataSet remains with an empty table.

Upvotes: 2

Joshua Volearix
Joshua Volearix

Reputation: 313

I always do it this way; hope this helps! 

using (SqlConnection con = new SqlConnection(SqlConString))
{
    string command = "Your Query Here...";
    using (SqlCommand cmd = new SqlCommand(command, con))
    {
        cmd.Parameters.AddWithValue("@Param", SqlDbType.Type).Value = YourParameter;

        con.Open();
        using (SqlDataAdapter da = cmd.ExecuteNonQuery())
        {
            da.Fill(dt);
        }
    }
}

Upvotes: 0

bkmtan
bkmtan

Reputation: 101

You probably need to use SqlAdaptor.Fill(DataTable) like such:

string sql = @"Data Source=.;Initial Catalog=test;Integrated Security=True";
SqlConnection conn = new SqlConnection(sql);
conn.Open();
SqlDataAdapter adaptor = new SqlDataAdapter("<sql query>", conn);
DataTable dt = new DataTable();
adaptor.Fill(dt);

Upvotes: 3

Related Questions