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; }