C# SQL Server update

Question

I try to update a sqldatabase server. I did this earlier with the buildin "Local Database" in visual studio. B I did this (in short)

System.Data.SqlServerCe.SqlCeCommandBuilder cb;
cb = new System.Data.SqlServerCe.SqlCeCommandBuilder(da);
cb.DataAdapter.Update(ds1, "Alarmen");

But the builtin database did not meet the requirements. I had to use Microsoft SQL server 2005. I tried to do this also, but that did not work. First i fill a database set from the database with the following commands:\

fillDataSet()
{
    SqlConnection _con = new SqlConnection("server=(local)\\SQLExpress;database=Alarmen;integrated Security=SSPI;");
    string sql = "SELECT * FROM tbl_alarmen";

    try
    {
        _con.Open();
    }
    catch (Exception ex)
    {
        log.Info("Database kon niet geopend worden " + ex);
    }

    DataSet ds1 = new DataSet();

    try
    {
        sql = "SELECT * FROM dbo.tbl_alarmen";

        _cmd = new SqlCommand(sql, _con);
        _dap = new SqlDataAdapter(_cmd);

        _dap.Fill(ds1, "Alarmen");

        foreach (DataRow drow in ds1.Tables["Alarmen"].Rows)
        {
            log.Info("Test");
        }

    }
    catch (Exception err)
    {
        log.Info("Database lezen mislukt " + err);
    }

    try
    {
        _con.Close();
    }
    catch (Exception ex)
    {
        log.Info("Database sluiten mislukt " + ex);
    }
        _dap.Fill(ds1, "Alarmen");

        foreach (DataRow drow in ds1.Tables["Alarmen"].Rows)
        {
            log.Info("Test");
        }

    }
    catch (Exception err)
    {
        log.Info("Database lezen mislukt " + err);
    }

    try
    {
        _con.Close();
    }
    catch (Exception ex)
    {
        log.Info("Database sluiten mislukt " + ex);
    }
}

Then i add rows and modify items in the dataset and try op update the changes in the database.

UpdateDatabase()
{
    SqlConnection _con = new SqlConnection("server=(local)\\SQLExpress;database=Alarmen;integrated Security=SSPI;");
    string sql = "SELECT * FROM tbl_alarmen";

    try
    {
        _con.Open();
    }
    catch (Exception ex)
    {
        log.Info("Database kon niet geopend worden " + ex);
    }

    try
    {
        var con = new SqlConnection(connectionString);
        var adapter = new SqlDataAdapter("SELECT * FROM tbl_alarmen", con);
        new SqlCommandBuilder(adapter);

        //
        // Fill the DataAdapter with the values in the DataTable.
        //
 adapter.Fill(ds1);
        //
        // Insert the data table into the SQL database.
        //
adapter.Update(ds1);             
    }
    catch (Exception e)
    {
       log.Info("Fill database failed " + e);
    }

    try
    {
        _con.Close();
    }
    catch (Exception ex)
    {
        log.Info("Database sluiten mislukt " + ex);
    }
}

I do not get any errors, but the database keeps being empty.

I now i should not use the "SELECT *" for security issues. I need to change that in the future.

I see i did not aks a real question. What did i do wrong in my code and how could i fix it.

Well, the first anti-pattern I can see in this code is that, if an exception is thrown when opening a connection, you report the error and then **carry on** attempting to use the connection. — Damien_The_Unbeliever, Jun 12 '13 at 08:02
Thank you for the input. This is something I can use. But i use the try-catch principal to see if there are errors. The problem is the errors are not trown. So i assume they do not give any errors. — Gertjan Gielen, Jun 12 '13 at 08:12
Also, consider using `using` clause when opening a `SqlConnection` which will make handling of connections easier in case problems arise. See this [tutorial](http://www.dotnetperls.com/sqlconnection). Besides, you're using `try/catch` blocks only to log exception information without doing anything else. This is not a good practice - only catch an exception if you plan to do something about it (e.g. rollback). A better, cleaner way would be to use `AppDomain.UnhandledException`. Example: [link](http://stackoverflow.com/questions/3133199/net-global-exception-handler-in-console-application). — w128, Jun 12 '13 at 08:23

score 0 · Accepted Answer · answered Jun 12 '13 at 08:11

0

Try

adapter.Fill(ds1, "Alarmen")

and then

adapter.Update(ds1, "Alarmen");

See this MS link for more info

answered Jun 12 '13 at 08:11

Paul Zahra

9,522
8
54
76

Thank you very much for your answer. Now i got an other error. But i think it is unrelated to my question. – Gertjan Gielen Jun 12 '13 at 08:50

C# SQL Server update

1 Answers1