c# filtering out NULL rows from Database and Updating existig rows

Question

In a table of my database I want to create new entries that don't exist and update them if they do exist. I got a table that links the UserID LernfeldID and Schoolyear to the Marks. There should only be a database insert when there is no match for the UserID which means that the entry does not exist. If there is an entry it should get updated.

I worked with the Datatable object since it seemed fitting from what I've read. I wanted to check with the DBNull.Value but it doesn't work. My database only receives the SELECT statement. If I update the DataTable with a for loop and send it back to database it works. But I still need to do the "entry exists" check. Could you please help me with that?

My code looks like this:

    public static void DB_GetMarks()
    {
        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            try
            {
                SqlCommand cmdQueryMarks = new SqlCommand();
                cmdQueryMarks.Connection = con;
                cmdQueryMarks.CommandText = "SELECT UserID, LernfeldID, SchuljahrID, Note "
                                           + "FROM UsersToLernfelder "
                                           + "WHERE USERID = @usrID "
                                           + "AND SchuljahrID = @YearID "
                                           + "ORDER BY LernfeldID ASC";
                cmdQueryMarks.Parameters.AddWithValue("@usrID", _UserID);
                cmdQueryMarks.Parameters.AddWithValue("@YearID", _YearID);

                SqlDataAdapter da = new SqlDataAdapter(cmdQueryMarks);

                DataTableMapping dtm = da.TableMappings.Add("Table", "UsersToLernfelder");
                dtm.ColumnMappings.Add("UserID", "User");
                dtm.ColumnMappings.Add("LernfeldID", "Lernfeld");
                dtm.ColumnMappings.Add("SchuljahrID", "Schuljahr");

                DataTable tbl = new DataTable();
                da.Fill(tbl);

                int i = 0;

                foreach (DataRow row in tbl.Rows)
                {
                    object value = row["UserID"];

                    if (value == DBNull.Value)
                    {
                        DataRow newEntry = tbl.NewRow();
                        newEntry["UserID"] = UserID;
                        newEntry["Lernfeld"] = i + 1;
                        newEntry["Schuljahr"] = YearID;
                        newEntry["Note"] = MarksArr[i];
                        tbl.Rows.Add(newEntry);
                    }
                    else
                    {
                        row["marks"] = MarksArr[i];
                    }

                    i++;
                }

                SqlCommandBuilder cmb = new SqlCommandBuilder(da);
                da.Update(tbl);
            }
            catch (Exception ex)
            {
                _Err = ex.Message;
            }
        }
    }

PS. This is my first Post so please excuse any possible mistakes

Answer 1

You shoud look Merge command. Also this answer will help you.

Answer 2

You can't get Null Rows, because you have already filtered the records with @UserID

If you want to update UsersToLernfelder with a non-existing user, then you should have a list of Users and Marks.

   public static void DB_GetMarks(int _UserID, int _YearID)
    {

        Dictionary<int, double> MarksArr = new Dictionary<int, double>();

        MarksArr.Add(1, 50);
        MarksArr.Add(2, 49);
        MarksArr.Add(3, 48);
        using (SqlConnection con = new SqlConnection(""))
        {
            try
            {
                SqlCommand cmdQueryMarks = new SqlCommand();
                cmdQueryMarks.Connection = con;
                cmdQueryMarks.CommandText = "SELECT UserID, LernfeldID, SchuljahrID, Note "
                                           + "FROM UsersToLernfelder "
                                           + "WHERE USERID = @usrID "
                                           + "AND SchuljahrID = @YearID "
                                           + "ORDER BY LernfeldID ASC";
                cmdQueryMarks.Parameters.AddWithValue("@usrID", _UserID);
                cmdQueryMarks.Parameters.AddWithValue("@YearID", _YearID);

                SqlDataAdapter da = new SqlDataAdapter(cmdQueryMarks);

                DataTableMapping dtm = da.TableMappings.Add("Table", "UsersToLernfelder");
                dtm.ColumnMappings.Add("UserID", "User");
                dtm.ColumnMappings.Add("LernfeldID", "Lernfeld");
                dtm.ColumnMappings.Add("SchuljahrID", "Schuljahr");

                DataTable tbl = new DataTable();
                da.Fill(tbl);

                int i = 0;

                foreach (var item in MarksArr)
                {
                    DataRow Result = (from DataRow dr in tbl.Rows where Convert.ToInt32(dr["USERID"]) == item.Key select dr).FirstOrDefault();
                    if (Result == null)
                    {
                        DataRow newEntry = tbl.NewRow();
                        newEntry["UserID"] = item.Key;
                        newEntry["Lernfeld"] = i + 1;
                        newEntry["Schuljahr"] = _YearID;
                        newEntry["Note"] = item.Value;
                        tbl.Rows.Add(newEntry);
                    }
                    else
                    {
                        foreach (DataRow _dr in tbl.Rows)
                        {
                            if (Convert.ToInt32(_dr["UserID"]) == item.Key)
                            {
                                _dr["marks"] = item.Value;
                            }
                        }
                    }
                    i++;
                }

                SqlCommandBuilder cmb = new SqlCommandBuilder(da);
                da.Update(tbl);
            }
            catch (Exception ex)
            {
                _Err = ex.Message;
            }
        }
    }