none
DB Class for CRUD question RRS feed

  • Question

  • Hello,

    I am working on a small program to save information for various things. As I am writing it, I find making functions for data operations (Create, Read, Update, Delete) for each table / item seems redundant.

    I am trying to create a class for these functions and am looking at examples, and none are "generic". They have the specific items to that database / table...

    Here is what I have:

                Dictionary<string, string> tmpD = new Dictionary<string, string>();
                tmpD.Add("@LoginID", "Sabrus");
                tmpD.Add("@RealName", "Mage");
                tmpD.Add("@Password", "ThePassWord_02");
                tmpD.Add("@Role", "user");
                _DB.SaveData(tmpD);
    
    
            public static void SaveData( Dictionary<string, string> ParamList)
            {
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT usersLoginID, usersRealName, usersPassword, usersRole FROM tblPwdUserLogin", "Connection String");
                MySqlConnection conn = da.SelectCommand.Connection;
                da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    
                da.InsertCommand = new MySqlCommand("INSERT INTO tblPwdUserLogin (usersLoginID, usersRealName, usersPassword, usersRole) VALUES (@LoginID, @RealName, @Password, @Role)", conn);
    
                //Set up parameters
                MySqlCommandBuilder cmdBldr = new MySqlCommandBuilder(da);
                //da.InsertCommand = cmdBldr.GetInsertCommand();
    
                MJB.Debug( "cmbBldr", cmdBldr.GetInsertCommand().CommandText + " ----- " + ParamList.Count.ToString());
    
    
                for (int i = 0; i < ParamList.Count; i++)
                {
                    da.InsertCommand.Parameters.AddWithValue(ParamList.Keys.ElementAt(i), ParamList.Values.ElementAt(i));
                }
    
                conn.Open();
                da.InsertCommand.ExecuteNonQuery();
                conn.Close();
            }
    
    

    This works because the adapter InsertCommand is specified.

    However, if I comment the da.InsertCommand = new MySqlCommand("INSERT INTO tblPwdUserLogin (usersLoginID, usersRealName, usersPassword, usersRole) VALUES (@LoginID, @RealName, @Password, @Role)", conn);
    and substitute the   da.InsertCommand = cmdBldr.GetInsertCommand();
    the InsertCommand query is correct, only the value params are @p1, @p2, @p3, @p4 instead. And then program throws exception, "User Role is null..."  I have also tried to change the param names to @p1 - @p4 as well. But that doesn't work either.

    I can't see what I am doing wrong. Please help.

    My idea was to create the class, passing the SelectCommand and parameter items... and it should hopefully work for all my data items.

    Thank you
    Mike

    Thursday, May 2, 2019 2:59 AM

Answers

  • So, I should create the CRUD objects on each page as needed ?

    My idea was pass the Select Query and Params to the function --

    Maybe, you should understand Seperation of Concerns.

    https://en.wikipedia.org/wiki/Separation_of_concerns

    You could also look into using a UI design pattern like MVP.

    https://www.codeproject.com/Articles/228214/Understanding-Basics-of-UI-Design-Pattern-MVC-MVP

    http://polymorphicpodcast.com/shows/mv-patterns/

    You could understand layered architectural style.

    https://docs.microsoft.com/en-us/previous-versions/msp-n-p/ee658117(v=pandp.10)

    https://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET

    .You could use the DAO pattern on a per table basis for CRUD.

    https://en.wikipedia.org/wiki/Data_access_object

    https://www.tutorialspoint.com/design_pattern/data_access_object_pattern.htm

    .

    • Marked as answer by Mike_1369 Friday, May 3, 2019 2:25 PM
    Thursday, May 2, 2019 7:33 PM

All replies

  • I am trying to create a class for these functions and am looking at examples, and none are "generic". They have the specific items to that database / table...

    I can't see what I am doing wrong. Please help.

    CRUD operations are not generic, and each operation should have code dedicated to the particular operation.  

    Thursday, May 2, 2019 4:32 AM
  • Hi Mike_1369,

    Thank you for posting here.

    Based on your description, you want to solve the exception that "User Role is null...".

    I used sql database to test it because my lack of Mysql database. I could not reproduce your problem even if I used the same value paras with you.

    You could check the following code.

     private void Form1_Load(object sender, EventArgs e)
            {
                Dictionary<string, string> tmpD = new Dictionary<string, string>();
                tmpD.Add("@p1", "Sabrus");
                tmpD.Add("@p2", "Mage");
                tmpD.Add("@p3", "ThePassWord_02");
                tmpD.Add("@p4", "user");
                SaveData(tmpD);
                MessageBox.Show("success");
            }
    
            public static void SaveData(Dictionary<string, string> ParamList)
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT usersLoginID, usersRealName, usersPassword, usersRole FROM LoginUser", @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
                SqlConnection conn = da.SelectCommand.Connection;
                da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    
                da.InsertCommand = new SqlCommand("INSERT INTO LoginUser (usersLoginID, usersRealName, usersPassword, usersRole) VALUES (@p1, @p2, @p3, @p4)", conn);
                SqlCommandBuilder cmdBldr = new SqlCommandBuilder(da);
    
                for (int i = 0; i < ParamList.Count; i++)
                {
                    da.InsertCommand.Parameters.AddWithValue(ParamList.Keys.ElementAt(i), ParamList.Values.ElementAt(i));
                }
    
                conn.Open();
                da.InsertCommand.ExecuteNonQuery();
                conn.Close();
            }

    Result:

    Best Regards,

    Jack



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, May 2, 2019 5:54 AM
    Moderator
  • Thank you for trying.

    Yes, if I change everything, to match, all works.

    What doesn't work is if I use the Command Builder object to generate the query string for the insert command...

    MySqlCommandBuilder cmdBldr = new MySqlCommandBuilder(da);
    da.InsertCommand = cmdBldr.GetInsertCommand();
    
    Debug.WriteLine(cmdBldr.GetInsertCommand().CommandText); // gives "INSERT INTO tblPwdUserLogin (usersLoginID, usersRealName, usersPassword, usersRole) VALUES (@p1, @p2, @p3, @p4)"

    Then when I insert the parameters, either with @LoginID or @p1, it gives me the error.

    Do I have to add the parameters a different method when I have the CommandBuilder generate the InsertCommand ?


    Thursday, May 2, 2019 2:46 PM
  • Thank you.

    So, I should create the CRUD objects on each page as needed ?

    My idea was pass the Select Query and Params to the function --

    SaveData(query, params)
    {
      [...]
      SelectCommand = query;
      [...]
    
      // Set params needed for query;
      // Execute Query
      // return result (success or fail)
    }
    


    Thursday, May 2, 2019 2:51 PM
  • So, I should create the CRUD objects on each page as needed ?

    My idea was pass the Select Query and Params to the function --

    Maybe, you should understand Seperation of Concerns.

    https://en.wikipedia.org/wiki/Separation_of_concerns

    You could also look into using a UI design pattern like MVP.

    https://www.codeproject.com/Articles/228214/Understanding-Basics-of-UI-Design-Pattern-MVC-MVP

    http://polymorphicpodcast.com/shows/mv-patterns/

    You could understand layered architectural style.

    https://docs.microsoft.com/en-us/previous-versions/msp-n-p/ee658117(v=pandp.10)

    https://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET

    .You could use the DAO pattern on a per table basis for CRUD.

    https://en.wikipedia.org/wiki/Data_access_object

    https://www.tutorialspoint.com/design_pattern/data_access_object_pattern.htm

    .

    • Marked as answer by Mike_1369 Friday, May 3, 2019 2:25 PM
    Thursday, May 2, 2019 7:33 PM
  • Thank you for the help with the articles to read !!

    Thursday, May 2, 2019 8:44 PM
  • Hi

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 3, 2019 3:07 AM
    Moderator
  • Yes, the articles helped me, I didn't mark "solved" until I made sure the articles somehow made sense.

    Haha, they didn't to me. :(

    Friday, May 3, 2019 2:25 PM