C# NpgsqlCommand.AddParameter方法代码示例

        public void add_second_parameter()
        {
            var command = new NpgsqlCommand();

            command.AddParameter("a");
            var param = command.AddParameter("b");

            param.ParameterName.ShouldBe("arg1");
        }

        public string ToSql(NpgsqlCommand command)
        {
            var json = _serializer.ToCleanJson(_dictionary);
            var param = command.AddParameter(json);
            param.NpgsqlDbType = NpgsqlDbType.Jsonb;

            return $"d.data @> :{param.ParameterName}";
        }

        public string ToSql(NpgsqlCommand command)
        {
            var sql = _sql;
            _parameters.Each(x =>
            {
                var param = command.AddParameter(x);
                sql = sql.ReplaceFirst("?", ":" + param.ParameterName);
            });

            return sql;
        }

        public void add_first_parameter()
        {
            var command = new NpgsqlCommand();

            var param = command.AddParameter("a");

            param.Value.ShouldBe("a");
            param.ParameterName.ShouldBe("arg0");

            param.NpgsqlDbType.ShouldBe(NpgsqlDbType.Text);

            command.Parameters.ShouldContain(param);
        }

        public string ToSql(NpgsqlCommand command)
        {
            var param = command.AddParameter(_values, _dbType);

            return $"{_locator} = ANY(:{param.ParameterName})";
        }

        /// <summary>
        /// Transfere dados de um arquivo Excel para o banco de dados atual.
        /// Conexão com o banco atual precisa estar aberta.
        /// Não pára a execução se der um problema num comando de inserção específico.
        /// </summary>
        /// <returns>Número de linhas transferidas.</returns>
        /// <param name="p_filename">Nome do arquivo de origem.</param>
        /// <param name="p_separator">Separador de campos do arquivo CSV.</param>
        /// <param name="p_delimitator">Delimitador de campos do arquivo CSV.</param>
        /// <param name="p_header">Se deve considerar a primeira linha como cabeçalho ou não.</param>
        /// <param name="p_encoding">Codificação para leitura do arquivo CSV.</param>
        /// <param name="p_newtable">Nome da nova tabela a ser criada no banco de dados.</param>
        /// <param name="p_progress">Evento de progresso.</param>
        /// <param name="p_error">Evento de erro.</param>
        public override uint TransferFromFile(string p_filename, char p_separator, char p_delimitator, bool p_header, System.Text.Encoding p_encoding, string p_newtable, Spartacus.Utils.ProgressEventClass p_progress, Spartacus.Utils.ErrorEventClass p_error)
        {
            Spartacus.Database.Command v_cmd;
            Spartacus.Utils.Excel v_excel = null;
            uint v_transfered = 0;
            string v_createtable;
            string v_insert;

            try
            {
                v_excel = new Spartacus.Utils.Excel();
                v_excel.Import(p_filename, p_separator, p_delimitator, p_header, p_encoding);

                v_createtable = "create table " + p_newtable + " (";
                for (int k = 0; k < v_excel.v_set.Tables[0].Columns.Count; k++)
                {
                    if (k < v_excel.v_set.Tables[0].Columns.Count-1)
                        v_createtable += v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + " varchar,";
                    else
                        v_createtable += v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + " varchar)";
                }
                try
                {
                    this.Execute(v_createtable);
                }
                catch (Spartacus.Database.Exception e)
                {
                    p_error.FireEvent(v_createtable + "\n" + e.v_message);
                }

                v_cmd = new Spartacus.Database.Command();
                v_cmd.v_text = "insert into " + p_newtable + " values (";
                for (int k = 0; k < v_excel.v_set.Tables[0].Columns.Count; k++)
                {
                    if (k < v_excel.v_set.Tables[0].Columns.Count-1)
                        v_cmd.v_text += "#" + v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + "#,";
                    else
                        v_cmd.v_text += "#" + v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + "#)";
                    v_cmd.AddParameter(v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower(), Spartacus.Database.Type.QUOTEDSTRING);
                }

                foreach (System.Data.DataRow r in v_excel.v_set.Tables[0].Rows)
                {
                    foreach (System.Data.DataColumn c in v_excel.v_set.Tables[0].Columns)
                        v_cmd.SetValue(c.ColumnName, r[c].ToString());

                    v_insert = v_cmd.GetUpdatedText();
                    try
                    {
                        this.Execute(v_insert);
                        v_transfered++;
                        p_progress.FireEvent(v_transfered);
                    }
                    catch (Spartacus.Database.Exception e)
                    {
                        p_error.FireEvent(v_insert + "\n" + e.v_message);
                    }
                }

                return v_transfered;
            }
            catch (Spartacus.Utils.Exception e)
            {
                throw new Spartacus.Database.Exception(e);
            }
            catch (Spartacus.Database.Exception e)
            {
                throw e;
            }
            finally
            {
                if (v_excel != null)
                {
                    v_excel.Clear();
                    v_excel = null;
                }
            }
        }

        private IEnumerable<string> buildBinary(BinaryExpression[] binaryExpressions, NpgsqlCommand command)
        {
            if (!binaryExpressions.Any())
            {
                yield break;
            }

            var dictionary = new Dictionary<string, object>();

            // Are we querying directly againt the elements as you would for primitive types?
            if (binaryExpressions.All(x => x.Left is QuerySourceReferenceExpression && x.Right is ConstantExpression))
            {
                if (binaryExpressions.Any(x => x.NodeType != ExpressionType.Equal))
                {
                    throw new NotSupportedException("Only the equality operator is supported on Collection.Any(x => x) searches directly against the element");
                }

                var values = binaryExpressions.Select(x => x.Right.Value()).ToArray();
                if (_members.Length == 1)
                {
                    dictionary.Add(_members.Single().Name, values);
                }
                else
                {
                    throw new NotSupportedException();
                }
            }
            else
            {
                var search = new Dictionary<string, object>();
                binaryExpressions.Each(x => gatherSearch(x, search));


                if (_members.Length == 1)
                {
                    dictionary.Add(_members.Single().Name, new[] { search });
                }
                else
                {
                    throw new NotImplementedException();
                }
            }




            var json = _serializer.ToCleanJson(dictionary);
            var param = command.AddParameter(json);
            param.NpgsqlDbType = NpgsqlDbType.Jsonb;


            yield return $"d.data @> :{param.ParameterName}";
        }

        private string buildSubQuery(SubQueryExpression subQuery, NpgsqlCommand command)
        {
            var contains = subQuery.QueryModel.ResultOperators.OfType<ContainsResultOperator>().FirstOrDefault();
            if (contains == null)
            {
                throw new NotSupportedException("Only the Contains() operator is supported on subqueries within Collection.Any() searches");
            }

            // build rhs of ?|
            var from = subQuery.QueryModel.MainFromClause.FromExpression as ConstantExpression;
            if (from == null || !supportedTypes.Any(supp => isListOrArrayOf(from.Type, supp)))
            {
                throwNotSupportedContains();
            }
            var fromParam = command.AddParameter(from.Value);
            fromParam.NpgsqlDbType = NpgsqlDbType.Array | NpgsqlDbType.Text;

            // check/build lhs of ?|
            var item = contains.Item as QuerySourceReferenceExpression;
            if (item == null)
            {
                throwNotSupportedContains();
            }
            if (!supportedTypes.Any(supp => supp == item.ReferencedQuerySource.ItemType))
            {
                throwNotSupportedContains();
            }
            var itemSource = item.ReferencedQuerySource as MainFromClause;
            if (itemSource == null)
            {
                throwNotSupportedContains();
            }
            var member = itemSource.FromExpression as MemberExpression;
            if (member == null)
            {
                throwNotSupportedContains();
            }
            var visitor = new FindMembers();
            visitor.Visit(member);
            var members = visitor.Members;
            if (!members.Any())
                throwNotSupportedContains();
            var path = members.Select(m => m.Name).Join("'->'");
            return $"data->'{path}' ?| :{fromParam.ParameterName}";
        }

        public string ToSql(NpgsqlCommand command)
        {
            var wheres = _expression
                .QueryModel
                .BodyClauses
                .OfType<WhereClause>()
                .Select(x => x.Predicate)
                .ToArray();

            if (!wheres.All(x => x is BinaryExpression))
            {
                throw new NotImplementedException();
            }


            var visitor = new FindMembers();
            visitor.Visit(_expression.QueryModel.MainFromClause.FromExpression);

            var members = visitor.Members;
            var binaryExpressions = wheres.OfType<BinaryExpression>().ToArray();
            var dictionary = new Dictionary<string, object>();

            // Are we querying directly againt the elements as you would for primitive types?
            if (binaryExpressions.All(x => x.Left is QuerySourceReferenceExpression && x.Right is ConstantExpression))
            {
                if (binaryExpressions.Any(x => x.NodeType != ExpressionType.Equal))
                {
                    throw new NotSupportedException("Only the equality operator is supported on Collection.Any(x => x) searches directly against the element");
                }

                var values = binaryExpressions.Select(x => x.Right.Value()).ToArray();
                if (members.Count == 1)
                {
                    dictionary.Add(members.Single().Name, values);
                }
                else
                {
                    throw new NotSupportedException();
                }
            }
            else
            {
                var search = new Dictionary<string, object>();
                binaryExpressions.Each(x => gatherSearch(x, search));


                if (members.Count == 1)
                {
                    dictionary.Add(members.Single().Name, new[] { search });
                }
                else
                {
                    throw new NotImplementedException();
                }
            }




            var json = _serializer.ToCleanJson(dictionary);
            var param = command.AddParameter(json);
            param.NpgsqlDbType = NpgsqlDbType.Jsonb;


            return $"d.data @> :{param.ParameterName}";
        }

        public NpgsqlParameter AddParameter(object query, NpgsqlCommand command)
        {
            var param = command.AddParameter(_value);

            return param;
        }