Example #1
0
        public int ExecuteUpdateStatements(IDbConnection con)
        {
            // Check for no updates
            if (regs.Count == 0)
            {
                return(0);
            }

            // Creating the command and the parameters
            IDictionary <string, object> parameters    = new Dictionary <string, object>(regs.Count * 2);
            IDictionary <object, int>    parametersIdx = new Dictionary <object, int>(regs.Count * 2);

            // The StringBuilder with all the UPDATE statements
            System.Text.StringBuilder sb = new System.Text.StringBuilder();

            using (IDbCommand com = con.CreateCommand())
            {
                int parameterIdx = 0;
                foreach (ObjectAndColumns reg in regs)
                {
                    SqlFragment frag = CreateUpdateStatement(reg);
                    sb.Append(frag.ToSqlString(ref parameterIdx, parameters, parametersIdx) + ";");
                }

                // Defines the command text, composed of all the updates
                com.CommandText = sb.ToString();

                foreach (var param in parameters)
                {
                    IDbDataParameter com_param = com.CreateParameter();
                    com_param.ParameterName = param.Key;
                    com_param.Value         = param.Value;
                    com.Parameters.Add(com_param);
                }

                Console.WriteLine("Executing the following updates:\n{0}", com.CommandText);

                return(com.ExecuteNonQuery());
            }
        }
Example #2
0
        public int ExecuteUpdateStatement(IDbConnection con)
        {
            SqlFragment query = new SqlFragment("UPDATE " + table + " SET ");

            // Update fields section
            for (int i = 0; i < chosenPropsOrFields.Count; i++)
            {
                var getter = chosenPropsOrFields.ElementAt(i);

                if (i == chosenPropsOrFields.Count - 1)
                {
                    query.AppendText("{0}=t.{0}", getter.Key);
                }
                else
                {
                    query.AppendText("{0}=t.{0},", getter.Key);
                }
            }

            // Values and ids section
            query.AppendText(" FROM (VALUES ");
            for (int r = 0; r < regs.Count; r++)
            {
                T reg = regs[r];

                query.AppendText("(");

                for (int i = 0; i < chosenPropsOrFields.Count; i++)
                {
                    var getter = chosenPropsOrFields.ElementAt(i);
                    query.AppendParameter(getter.Value(reg));

                    query.AppendText(",");
                }

                query.AppendParameter(idGetter(reg))
                .AppendText(")");

                if (r < regs.Count - 1)
                {
                    query.AppendText(",");
                }
            }
            query.AppendText(") AS t(");

            for (int i = 0; i < chosenPropsOrFields.Count; i++)
            {
                var getter = chosenPropsOrFields.ElementAt(i);
                query.AppendText(getter.Key + ",");
            }
            query.AppendText("id) WHERE " + idColumn + "=t.id");

            // Creating the command and the parameters
            IDictionary <string, object> parameters    = new Dictionary <string, object>(regs.Count * (chosenPropsOrFields.Count + 1));
            IDictionary <object, int>    parametersIdx = new Dictionary <object, int>(regs.Count * (chosenPropsOrFields.Count + 1));

            using (IDbCommand com = con.CreateCommand())
            {
                int initialParameterIdx = 0;
                com.CommandText = query.ToSqlString(ref initialParameterIdx, parameters, parametersIdx);

                foreach (var param in parameters)
                {
                    IDbDataParameter com_param = com.CreateParameter();
                    com_param.ParameterName = param.Key;
                    com_param.Value         = param.Value;
                    com.Parameters.Add(com_param);
                }

                return(com.ExecuteNonQuery());
            }
        }