Is it possible to update/insert data into dataset with SqlCommand?

Question

I use this code to update data in database table. Can reuse same code to update a dataset? Thanks.

 using (SqlConnection cn =  new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
        {
            string sql = "UPDATE tbh_Categories SET Title = @Title, 
                           Description = @Description 
                           WHERE CategoryID = @CategoryID";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = category.ID;
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = category.Title;
            cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = category.Description;

            cn.Open();
            int ret = cmd.ExecuteNonQuery();

            return (ret == 1);
        }

Answer 1

The same exact code? No. SqlConnection (and SqlCommand) is specific to SqlServer. DataSet exists outside of that context, so you would have to rewrite your code to accomodate updating a DataSet.

Answer 2

No, I don't think so. Either loop over the rows of your DataTable, or update the database and reload.

Answer 3

The answer is no. But, you can use DataTable.Select to identify the rows in the DataTable that you want to update. But then you will have to modify the actual table itself "by hand".

I must ask what you are trying to do ... are you trying, for example, to cache some data using an updatable DataSet? Or are you trying to avoid extra database trips? There may be a better way to do what you are trying to do if you let us know. If you want an In Memory Database, there are lots out there.

Per comment: Check out SQLite. There are .NET Wrappers that might let you do what you want.

Answer 4

You must slightly change your code. You should use an instance of SqlDataAdapter and use it's Update method. A small code sample from the MSDN :

public DataSet CreateCmdsAndUpdate(string connectionString,
    string queryString) 
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        adapter.SelectCommand = new OleDbCommand(queryString, connection);
        OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

        connection.Open();

        DataSet customers = new DataSet();
        adapter.Fill(customers);

        //code to modify data in dataset here

        adapter.Update(customers);

        return customers;
    }
}