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(',')); }