DB Class for CRUD question

[Mike_1369]

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

Continue reading...