本文整理汇总了C#中Npgsql.NpgsqlConnection.Query方法的典型用法代码示例。如果您正苦于以下问题：C# NpgsqlConnection.Query方法的具体用法？C# NpgsqlConnection.Query怎么用？C# NpgsqlConnection.Query使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类Npgsql.NpgsqlConnection的用法示例。
在下文中一共展示了NpgsqlConnection.Query方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: NpgsqlTableChanged
public void NpgsqlTableChanged(ListBox listBox, UiShow uiShow)
{
IEnumerable<object> enumerable;
using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
{
connection.Open();
string sql =
$"SELECT column_name, is_nullable, udt_name FROM INFORMATION_SCHEMA.COLUMNS\r\nWHERE table_catalog='{connection.Database}' AND table_schema='public' AND table_name='{listBox.SelectedItem}';";
enumerable = connection.Query(sql, null, null, true, null, null);
}
StringBuilder builder = new StringBuilder();
builder.AppendFormat("public class {0}{1}{{{1}", listBox.SelectedItem, Environment.NewLine);
foreach (dynamic obj2 in enumerable)
{
string columnName = (string)obj2.column_name;
bool isNullable = (bool)(obj2.is_nullable.ToUpper() == "YES");
string dataType = (string)obj2.udt_name;
builder.AppendLine(GenerateField(columnName, isNullable, dataType));
}
string @class = builder.Append("}").ToString();
uiShow.txtClass.Text = @class;
File.AppendAllText("models.cs", @class);
}示例2: List
public IEnumerable<Recipe> List()
{
using ( var conn = new NpgsqlConnection( ConnectionString ) ) {
conn.Open( );
return conn.Query<Recipe>("SELECT * FROM Recipes");
}
}示例3: RecreateSchemaAndInstallObjects
private static void RecreateSchemaAndInstallObjects()
{
using (var connection = new NpgsqlConnection(
ConnectionUtils.GetMasterConnectionString()))
{
bool databaseExists = connection.Query<bool?>(
@"select true :: boolean from pg_database where datname = @databaseName;",
new
{
databaseName = ConnectionUtils.GetDatabaseName()
}
).SingleOrDefault() ?? false;
if (!databaseExists)
{
connection.Execute([email protected]"CREATE DATABASE ""{ConnectionUtils.GetDatabaseName()}""");
}
}
using (var connection = new NpgsqlConnection(ConnectionUtils.GetConnectionString()))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
PostgreSqlObjectsInstaller.Install(connection);
PostgreSqlTestObjectsInitializer.CleanTables(connection);
}
}示例4: GetById
public Recipe GetById(Guid id)
{
using ( var conn = new NpgsqlConnection( ConnectionString ) ) {
conn.Open( );
return conn.Query<Recipe>("SELECT * FROM Recipes WHERE [email protected]", new { id }).SingleOrDefault();
}
}示例5: Run
private void Run()
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var orders = conn.Query<Order>("select * from Orders where IsMatchSearched <> true or IsMatchSearched is null").ToList();
Console.WriteLine("finding matches for " + orders.Count + " orders");
foreach(var order in orders)
{
var minAmount = 0.9 * order.Amount;
var maxAmount = 1.1 * order.Amount;
var minPrice = 0.9 * order.Price;
var maxPrice = 1.1 * order.Price;
var type = order.OrderType == "Purchase" ? "Sell" : "Purchase";
var matchedOrders = conn.Query<Order>("select * from Orders where IsMatchSearched = true and Price < @MaxPrice and Price > @MinPrice and Amount < @MaxAmount and Amount > @MinAmount and OrderType = @Type and CommodityType = @CommodityType and fkUserId <> @UserId",
new {
MinAmount = minAmount,
MaxAmount = maxAmount,
MinPrice = minPrice,
MaxPrice = maxPrice,
CommodityType = order.CommodityType,
Type = type,
UserId = order.fkUserId
}).ToList();
Console.WriteLine("order " + order.Id + " has "+matchedOrders.Count+" matches");
foreach(var matchedOrder in matchedOrders)
{
String message = "Kami menemukan order yang cocok. {0} {1} {2} sebesar {3} kg dengan harga Rp. {4}";
conn.Execute("insert into orders_matches(first_id, second_id) values (@FirstId, @SecondId)", new { FirstId = order.Id, SecondId = matchedOrder.Id });
conn.Execute("insert into orders_matches(first_id, second_id) values (@FirstId, @SecondId)", new { FirstId = matchedOrder.Id, SecondId = order.Id });
var verb = order.OrderType == "Purchase" ? "menjual" : "membeli";
var commodity = conn.Query<String>("select Name from CommodityType where Id = @Id", new { Id = order.CommodityType }).FirstOrDefault();
var user = conn.Query<User>("select Name, PhoneNumber from Users where Id = @Id", new { Id = matchedOrder.fkUserId }).FirstOrDefault();
message = String.Format(message, user.Name, verb, commodity, matchedOrder.Amount, matchedOrder.Price);
conn.Execute("insert into Outboxes(PhoneNumber, Message) values (@PhoneNumber, @Message)",
new
{
PhoneNumber = user.PhoneNumber,
Message = message
}
);
}
conn.Execute("update Orders set IsMatchSearched = true where Id = @Id", new { Id = order.Id });
}
};
}示例6: GetCustomers
private List<Customer> GetCustomers()
{
// Получаем данные из БД с помощью ORM Dapper
NpgsqlConnection cn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=Customers;User Id=postgres;Password=123;MaxPoolSize=100;");
cn.Open();
// Данные получаются левым соединением сразу из двух таблиц, используется маппинг сразу в два типа: Customer и BusinessType
var res = cn.Query<Customer, BusinessType, Customer>("select * from \"Customer\" as \"Customer\" left join \"BusinessType\" as \"BusinessType\" on \"Customer\".\"BusinessTypeBusinessTypeId\" = \"BusinessType\".\"BusinessTypeId\" order by \"Customer\".\"CustomerName\"", (customer, businessType)=> { customer.BusinessType = businessType; return customer; }, splitOn: "BusinessTypeId");
return (List<Customer>)res;
}示例7: GetAvailableTables
/// <summary>
/// Gets all available table names from the database.
/// </summary>
/// <returns>Enumerable containing all tables in the database in schema.table format.</returns>
public IEnumerable<string> GetAvailableTables()
{
if (!TestConnection())
throw new InvalidOperationException("Failed to connect to the database.");
using (var conn = new NpgsqlConnection(ConnectionString))
{
string query = "select table_schema || '.' || table_name from information_schema.tables where table_schema = 'public' order by table_schema, table_name";
conn.Open();
return conn.Query<string>(query);
}
}示例8: ReadPostgis
public static void ReadPostgis(string connectionString, string sql)
{
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
var res = conn.Query(sql);
foreach (var p in res)
{
// todo: create something here
}
}
}示例9: Main
static void Main(string[] args)
{
ClassType ct = new ClassType();
File.WriteAllText("classtype.json", JsonConvert.SerializeObject(ct.ClassOrders));
IEnumerable<IdenRec> idenRecs;
string connStr = @"PORT=5432;KRBSRVNAME=POSTGRES;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;HOST=127.0.0.1;USER ID=postgres;PASSWORD=123456;DATABASE=irisNewAttend;";
using (NpgsqlConnection conn = new NpgsqlConnection(connStr))
{
conn.Open();
idenRecs = conn.Query<IdenRec>(@"SELECT person_id AS PersonId, recog_time AS RecTime
FROM person_recog_base WHERE recog_time>[email protected] ORDER BY person_id, recog_time;", new { time = ct.Origin });
File.WriteAllText("idenRecs.json", JsonConvert.SerializeObject(idenRecs));
}
NormalAttend na = new NormalAttend();
List<AttendRec> ars = new List<AttendRec>();
foreach (var recogs in idenRecs.GroupBy(r => new { r.PersonId, }).Select(r => r))
{
var attends = na.AttendCalc(recogs.Select(r => r).ToArray(), ct);
if (attends.Length > 0)
{
ars.AddRange(attends);
}
}
File.WriteAllText("attendrecs.json", JsonConvert.SerializeObject(ars));
File.WriteAllText("idenRecsNew.json", JsonConvert.SerializeObject(idenRecs));
Console.WriteLine(DateTime.Now.TimeOfDay);
//不区分设备计算考勤 => 根据班次信息和识别时间推算设备类型
//考勤：1 实时服务， 2 归档服务， 3 数值计算
//查询数据库，获得指定日期的识别记录，原数据设置为已处理
// 班次信息
//对数据进行去重（5min内重复识别不算）
Console.ReadKey();
}示例10: CreateExpirationEntry
private static int CreateExpirationEntry(NpgsqlConnection connection, DateTime? expireAt)
{
string insertSqlNull = @"
insert into """ + GetSchemaName() + @""".""counter""(""key"", ""value"", ""expireat"")
values ('key', 1, null) returning ""id""";
string insertSqlValue = @"
insert into """ + GetSchemaName() + @""".""counter""(""key"", ""value"", ""expireat"")
values ('key', 1, now() at time zone 'utc' - interval '{0} seconds') returning ""id""";
string insertSql = expireAt == null
? insertSqlNull
: string.Format(insertSqlValue,
((long) (DateTime.UtcNow - expireAt.Value).TotalSeconds).ToString(CultureInfo.InvariantCulture));
var id = connection.Query(insertSql).Single();
var recordId = (int) id.id;
return recordId;
}示例11: IsEntryExpired
private static bool IsEntryExpired(NpgsqlConnection connection, int entryId)
{
var count = connection.Query<long>(
@"select count(*) from """ + GetSchemaName() + @""".""counter"" where ""id"" = @id", new {id = entryId}).Single();
return count == 0;
}示例12: GetAll
public static IEnumerable<mal_anime_prerequisite> GetAll(NpgsqlConnection conn, NpgsqlTransaction transaction)
{
string sql = "SELECT * FROM mal_anime_prerequisite";
return conn.Query<mal_anime_prerequisite>(sql, transaction: transaction);
}示例13: DashboardModule
public DashboardModule()
{
const string sunburstQuery = "select string_agg ( commoditytype.name || '-' || ordertype || ',' || amount , '\\n' ) from orders inner join commoditytype on orders.commoditytype = commoditytype.id";
Get["/dashboard"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<String>(sunburstQuery).ToList();
var data = datas[0];
return View["dashboard",data];
}
};
Get["/sunburst"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<String>(sunburstQuery).ToList();
var data = datas[0];
return View["sunburst", data];
};
};
Get["/verify"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<Registration>("select * from Registrations where IsVerified <> true or IsVerified is null").ToList();
return View["verifylist", datas];
};
};
Get["/verify/{id}"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var data = conn.Query<Registration>("select * from Registrations where (IsVerified <> true or IsVerified is null) and Id = @Id", new { Id = parameters.id }).FirstOrDefault();
return View["verify", data];
};
};
Post["/verify"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
dynamic form = Request.Form;
conn.Execute("insert into Users(Name, KtpNumber, PhoneNumber, Address, Longitude, Latitude) values (@Name, @KtpNumber, @PhoneNumber, @Address, @Longitude, @Latitude)",
new
{
Name = form.Name,
KtpNumber = form.KtpNumber,
PhoneNumber = form.PhoneNumber,
Address = form.Address,
Longitude = form.Longitude,
Latitude = form.Latitude,
}
);
conn.Execute("update Registrations set IsVerified = true where Id = @Id", new { Id = form.id });
return Response.AsRedirect("/verify");
};
};
Get["/orders/{start}"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var data = conn.Query<OrderUser>("select * from Orders_Users where Id > @Start", new { Start = parameters.start }).ToList();
return JsonConvert.SerializeObject(data);
};
};
Get["/sunburstdata"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<String>(sunburstQuery).ToList();
return datas[0];
};
};
}示例14: GetAll
public static IEnumerable<mal_user> GetAll(NpgsqlConnection conn, NpgsqlTransaction transaction)
{
string sql = @"
SELECT mal_user_id, mal_name, time_added
FROM mal_user
";
// This will buffer all rows in memory before returning
return conn.Query<mal_user>(sql, transaction: transaction);
}示例15: Count
public static long Count(NpgsqlConnection conn, NpgsqlTransaction transaction)
{
long count = conn.Query<long>("SELECT Count(*) FROM mal_user", transaction: transaction).First();
return count;
}