Developer forum

Forum » Development » RE: Database bound parameters

RE: Database bound parameters

E-mail notifications
Jonas Mersholm
Reply
Posted on 06/02/2014 11:57:29

Hi DW.

Do you have any database class available which offers named parameters or prepared statements? If not, how would you suggest that i should escape my queries. 

 

Best regards

Jonas


Replies

 
Nicolai Høeg Pedersen
Posted on 06/02/2014 12:47:00
Reply

Like this?

http://developer.dynamicweb-cms.com/api8/#Dynamicweb~Dynamicweb.Database_members.html

 

using (IDbConnection connection = Database.CreateConnection("Ecom.mdb")) {
	IDbCommand command = connection.CreateCommand();
	command.CommandText = "SELECT ValidationID FROM EcomValidations WHERE ValidationID = @ValidationID";
	Database.AddStringParam(command, "@ValidationID", ID);
	Database.AddStringParam(command, "@ValidationGroupID", GroupID);
	string pureFieldName = FieldName;
	pureFieldName = pureFieldName.Remove(0, FieldType.ToString().Length + 1);
	Database.AddStringParam(command, "@ValidationFieldName", pureFieldName);
	Database.AddBooleanParam(command, "@ValidationUseAndOperator", UseAndOperator);
	Database.AddStringParam(command, "@ValidationFieldType", FieldType.ToString());

	//Execute command
	command.ExecuteNonQuery();
}

 

BR Nicolai

 
Jonas Mersholm
Posted on 06/02/2014 13:55:04
Reply

Great! Saved my day once again Nikolai. Thanks :)

 
Jonas Mersholm
Posted on 06/02/2014 14:17:44
Reply

Using this:

 

           

        private User UpdateAccessField(User User, string Field, string Value, bool prefix = false)

        {

            if (prefix == true) { Field = "AccessUser" + Field; }

            using ( IDbConnection connection = Database.CreateConnection() )

            {

                IDbCommand command = connection.CreateCommand();

                command.CommandText = "UPDATE AccessUser SET " + Field + " = @ParamValue WHERE AccessUserID = @UserId";

                Database.AddStringParam(command, "@ParamValue", Value);

                Database.AddInt32Param(command, "@UserId", User.ID);

 

                //Execute command

                command.ExecuteNonQuery();

            }

            return User;

        }

Calling it like this

//UserId contains INT 2175 which is my user ID.
User DW = Dynamicweb.Modules.UserManagement.User.GetUserByID(UserId); 
DW = UpdateAccessField(DW, "Email", "jom@email.dk", true);

Nothing happens. Not even an error shows up.  Ive tried the query in the firehose, without any problems.  Did i do anything wrong here?

 

best regards.

Jonas

 
Jonas Mersholm
Posted on 06/02/2014 15:45:04
Reply

This really makes no sense.

If i try to read the data for the same userid, right after doing the ExecuteNonQuery, i see the changes to the email field.

If i then go into FireHose in the DynamicWeb Management panel, and enters "SELECT * FROM AccessUser WHERE AccessUserID = 2175" - The email is still the wrong one from before the update. Whoa. Odd.

 
Morten Snedker
Posted on 06/02/2014 23:56:32
Reply

Hi Jonas,

 

Could you try and assign a value (int) to the ExecuteNonQuery(). And output that value somewhere? Just to see if any records are actually affected.

 

Regards /Snedker

 
Jonas Mersholm
Posted on 07/02/2014 08:02:43
Reply

Hi Morten, thank you for the reply.

My ExecuteNonQuery() returns a single affected row (As expected when selecting on the ID). This is the confusing part. After the insert, and verification that the record is affectd, i do a select query, on the exact same id, where i see the email as changed. But refreshing the page or checking firehose gives me the old result. This is a very weird behaivour.

 

You must be logged in to post in the forum