static void Main(string[] args)
{
//添加处理对象
SqlMapper.AddTypeHandler(new MyTypeHandler <Data>());
SqlMapper.AddTypeHandler(new MyTypeHandler <List <Address> >());
var constring = "Server=127.0.0.1;Port=5432;UserId=postgres;Password=postgres2018;Database=TestDB";
using (var con = new Npgsql.NpgsqlConnection(constring))
{
//注意json字段参数后要跟 ::json
var results = con.Execute("insert into test(id,data,addresses) values(@ID,@Data::json,@Addresses::json)", new Test
{
ID = 11,
Data = new Data {
IDs = 2, Age = 33, Name = "张三", Sex = true
},
Addresses = new List <Address> {
new Address {
ID = 1, PostCode = "1234567", AddressString = "地址1"
},
new Address {
ID = 2, PostCode = "3456789", AddressString = "地址2"
}
}
});
var result = con.Query <Test>("select * from test");
}
}
private static void Join()
{
List <Customer> customers;
var map = new Dictionary <int, Customer>();
using (var connection = new Npgsql.NpgsqlConnection(Constants.PSQL_CONNECTION))
{
customers = connection.Query <Customer, Order, Customer>(
joinSql,
(c, o) =>
{
Customer entity = c;
if (map.ContainsKey(c.Id))
{
entity = map[c.Id];
}
else
{
map.Add(c.Id, entity);
entity.Orders = new List <Order>();
}
entity.Orders.Add(o);
return(entity);
}
, splitOn: "id"
).Distinct().ToList();
}
foreach (var item in map)
{
Customer c = item.Value;
System.Console.WriteLine(c);
System.Console.WriteLine($"amount of orders {c.Orders.Count}");
}
}
private static void ReadStrong()
{
using (var connection = new Npgsql.NpgsqlConnection(Constants.PSQL_CONNECTION))
{
var customers = connection.Query <Customer>(sql).ToList();
System.Console.WriteLine(string.Join(Environment.NewLine, customers));
}
}
private static void Read()
{
using (var connection = new Npgsql.NpgsqlConnection(Constants.PSQL_CONNECTION))
{
var customers = connection.Query(sql).FirstOrDefault();
System.Console.WriteLine(customers);
}
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="keyword"></param>
/// <returns></returns>
public List <UserModel> GetList(string keyword)
{
string sql = "select * from t_user where 1=1 " + GetCondition(keyword);
using (var conn = new Npgsql.NpgsqlConnection(ConnectionString))
{
return(conn.Query <UserModel>(sql, new { keyword = "%" + keyword + "%" }).AsList());
}
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="keyword"></param>
/// <param name="pageSize"></param>
/// <param name="pageNo"></param>
/// <returns></returns>
public List <UserModel> GetPager(string keyword, int pageSize, int pageNo)
{
string sql = DalHelper.GetPagerSql("t_user", "*", " user_name asc", GetCondition(keyword), pageSize, pageNo);
using (var conn = new Npgsql.NpgsqlConnection(ConnectionString))
{
return(conn.Query <UserModel>(sql, new { keyword = "%" + keyword + "%" }).AsList());
}
}
public static IEnumerable <stl_load_errors> Read(string connectionString, DateTime dtStart)
{
using (var conn = new Npgsql.NpgsqlConnection(connectionString))
{
conn.Open();
foreach (var i in conn.Query <stl_load_errors> ("select filename, colname, position, err_code, err_reason from stl_load_errors where starttime >= @dtStart order by starttime desc limit 1000;", new { dtStart = dtStart.AddMinutes(-2) }))
{
yield return(i);
}
}
}
public static IEnumerable <stv_load_state> Read(string connectionString)
{
using (var conn = new Npgsql.NpgsqlConnection(connectionString))
{
conn.Open();
foreach (var i in conn.Query <stv_load_state> ("select query, slice, recordtime, num_files, lines, pct_complete, num_files, num_files_complete, current_file from STV_LOAD_STATE;"))
{
yield return(i);
}
}
}
public static IEnumerable <string> ReadCommitedFiles(string connectionString, DateTime dtStart)
{
using (var conn = new Npgsql.NpgsqlConnection(connectionString))
{
conn.Open();
foreach (var i in conn.Query <string> ("select distinct filename from stl_load_commits where status = 1 and curtime >= @dtStart order by curtime desc limit 1000;", new { dtStart = dtStart.AddMinutes(-2) }))
{
yield return(i);
}
}
}
public void LoadPlayer(Guid playerId, PlayerType playerType, Action <PlayerStoreData> callback)
{
Task.Run(() =>
{
using (IDbConnection con = new Npgsql.NpgsqlConnection(connectionString))
{
var playerDatas = con.Query <PlayerStoreData>(
"SELECT * FROM users WHERE player_id=@playerId AND player_type=@playerType",
new { playerId, playerType = playerType.ToString() });
callback(playerDatas.Single());
}
});
}
protected IEnumerable <T> Query <T>(string sql, object parameters = null)
{
using (IDbConnection connection = new Npgsql.NpgsqlConnection(this.connectionString))
{
try
{
connection.Open();
return(connection.Query <T>(sql, parameters));
}
catch (Exception exc)
{
Console.WriteLine(exc.Message);
throw exc;
}
}
}
static void FIEnumberableTest2()
{
using (var con = new Npgsql.NpgsqlConnection(";"))
{
var list = con.Query <dynamic>("select * from where endtime>'2020-02-01' and endtime<'2020-02-10'");
var stream = new FileStream(Directory.GetCurrentDirectory() + "/data.txt", FileMode.OpenOrCreate, FileAccess.ReadWrite);
foreach (var item in list)
{
if (item != null && item.RecordId != null)
{
stream.Write(Encoding.UTF8.GetBytes(item.RecordId.ToString()));
}
}
stream.Close();
Console.WriteLine("完成了！");
}
}
public IReadOnlyCollection <XElement> GetAllElements()
{
var list = new List <XElement>();
var constring = "Server=192.168.252.41;Port=5432;UserId=postgres;Password=postgres2018;Database=TestDB;";
using (var con = new Npgsql.NpgsqlConnection(constring))
{
var sql = "select value from dataprotectiondb";
var values = con.Query <string>(sql);
foreach (var value in values)
{
list.Add(Newtonsoft.Json.JsonConvert.DeserializeObject <XElement>(value));
}
}
var coll = new ReadOnlyCollection <XElement>(list);
return(coll);
}
public string GenerySql(List <dynamic> dataList, string tableName)
{
var pgconnection = "pg连接字符串";
List <dynamic> fieldItmes = null;
using (var pgcon = new Npgsql.NpgsqlConnection(pgconnection))
{
var sql = @"SELECT a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull
FROM pg_class c,pg_attribute a,pg_type t
WHERE c.relname =@tablename and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid order by a.attname";
fieldItmes = pgcon.Query <dynamic>(sql: sql, param: new { tablename = tableName.ToLower() }).AsList();
}
var sqlBuilder = new StringBuilder(@"INSERT INTO public." + tableName + "(");
sqlBuilder.Append(string.Join(',', fieldItmes.Select(s => s.field)));
sqlBuilder.Append(") values");
foreach (IEnumerable <KeyValuePair <string, dynamic> > data in dataList)
{
var line = new StringBuilder("(");
foreach (var field in fieldItmes)
{
var fieldData = data.SingleOrDefault(s => s.Key.ToLower() == field.field);
if (fieldData.Key != null)
{
string fieldValue = Convert.ToString(fieldData.Value);
if (fieldValue == null)
{
fieldValue = "null,";
}
else
{
fieldValue = fieldValue.Replace("'", "''");
fieldValue = $"'{fieldValue}',";
}
line.Append(fieldValue);
}
}
sqlBuilder.Append(line.ToString().TrimEnd(',') + "),");
}
return(sqlBuilder.ToString().TrimEnd(','));
}
