Have Geek Brain

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

I am using this code to update data in a database table. Can I use the 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);
        }
+1


a source to share


4 answers


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 have to modify the actual table "manually" yourself.

I have to ask, what are you trying to do ... trying, for example, to cache some data using an updatable DataSet? Or are you trying to avoid additional trips to the database? Maybe the best way to do what you are trying to do is if you let us know. If you need an in-memory database, there are many out there .



In the comments: SQLite . There are .NET Wrappers that can let you do what you want.

0


a source


The same exact code? No. SqlConnection (and SqlCommand) is SqlServer specific. The DataSet exists outside of this context, so you will have to rewrite your code to update the DataSet.



0


a source


No, I do not think so. Either go through the lines of yours DataTable

, or update the database and reload.

0


a source


You have to change your code a little. You have to use an instance of SqlDataAdapter and use Refresh . Small sample code from 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;
    }
}
0


a source






More articles:


All Articles