/// <summary> /// 静态构造器 /// </summary> static PgSqlSeedProvider() { string connectionString = null; #region # 验证 if (!string.IsNullOrWhiteSpace(SerialNumberSection.Setting.ConnectionString.Name)) { connectionString = ConfigurationManager.ConnectionStrings[SerialNumberSection.Setting.ConnectionString.Name]?.ConnectionString; } if (string.IsNullOrWhiteSpace(connectionString)) { connectionString = SerialNumberSection.Setting.ConnectionString.Value; } if (string.IsNullOrWhiteSpace(connectionString)) { throw new NullReferenceException("序列号生成器连接字符串未配置!"); } #endregion //初始化SQL工具 _SqlHelper = new PgSqlHelper(connectionString); //初始化数据表 InitTable(); }
/// <summary> /// 静态构造器 /// </summary> static PgSqlLogger() { string connectionString = null; #region # 验证 if (!string.IsNullOrWhiteSpace(AopSection.Setting.ConnectionString.Name)) { connectionString = ConfigurationManager.ConnectionStrings[AopSection.Setting.ConnectionString.Name]?.ConnectionString; } if (string.IsNullOrWhiteSpace(connectionString)) { connectionString = AopSection.Setting.ConnectionString.Value; } if (string.IsNullOrWhiteSpace(connectionString)) { throw new NullReferenceException("连接字符串未配置!"); } #endregion //初始化SQL工具 _SqlHelper = new PgSqlHelper(connectionString); //初始化日志数据表 InitTable(); }
public List <SchemaViewModel> List() { string[] filters = new string[this.Filters.Count]; for (int i = 0; i < Filters.Count; i++) { filters[i] = $"'{Filters[i]}'"; } List <SchemaViewModel> schemas = new List <SchemaViewModel>(); string sql = $@"SELECT schema_name FROM information_schema.schemata WHERE SCHEMA_NAME NOT IN({string.Join(",", filters)}) ORDER BY SCHEMA_NAME; "; PgSqlHelper.ExecuteDataReader(dr => { var schema = new SchemaViewModel { Name = dr[0].ToString() }; schemas.Add(schema); }, CommandType.Text, sql); foreach (var item in schemas) { Console.WriteLine("正在生成模式:{0}", item.Name); item.Tables = GetTables(item.Name); } return(schemas); }
/// <summary> /// generate schema name list /// </summary> /// <returns></returns> public static List <string> Get_List() { List <string> schemaList = new List <string>(); string[] notin = { "'geometry_columns'", "'raster_columns'", "'spatial_ref_sys'", "'raster_overviews'", "'us_gaz'", "'topology'", "'zip_lookup_all'", "'pg_toast'", "'pg_temp_1'", "'pg_toast_temp_1'", "'pg_catalog'", "'information_schema'", "'tiger'", "'tiger_data'" }; string sql = $@"SELECT schema_name FROM information_schema.schemata WHERE SCHEMA_NAME NOT IN({string.Join(",", notin)}) ORDER BY SCHEMA_NAME; "; PgSqlHelper.ExecuteDataReader(dr => { schemaList.Add(dr[0].ToString()); }, CommandType.Text, sql); return(schemaList); }
protected void Get_Constraint() { string _sqltext = string.Format(@" SELECT(select attname from pg_attribute where attrelid = a.conrelid and attnum = any(a.conkey)) as conname ,b.relname,c.nspname,d.attname as ref_column,e.typname FROM pg_constraint a left JOIN pg_class b on b.oid= a.confrelid inner join pg_namespace c on b.relnamespace = c.oid INNER JOIN pg_attribute d on d.attrelid =a.confrelid and d.attnum=any(a.confkey) inner join pg_type e on e.oid = d.atttypid WHERE conrelid in ( SELECT a.oid FROM pg_class a inner join pg_namespace b on a.relnamespace=b.oid WHERE b.nspname='{0}' and a.relname='{1}');" , this.schemaName, this.table.name); PgSqlHelper.ExecuteDataReader(dr => { string conname = dr["conname"].ToString(); string contype = dr["typname"].ToString(); string ref_column = dr["ref_column"].ToString(); string relname = dr["relname"].ToString(); string nspname = dr["nspname"].ToString(); consList.Add(new ConstraintInfo() { conname = conname, contype = contype, ref_column = ref_column, table_name = relname, nspname = nspname }); }, CommandType.Text, _sqltext); }
static void Main(string[] args) { if (args == null || args.Length == 0) { Console.WriteLine("try --help"); return; } if (args[0] == "--help") { Console.WriteLine("Use the following parameters to create a project using Mystaging.App,The parameter name ignore case"); Console.WriteLine("-h [host/ip] required"); Console.WriteLine("-p [port] required"); Console.WriteLine("-u [postgresql database access username] required"); Console.WriteLine("-a [postgresql database auth password] required"); Console.WriteLine("-d [postgresql database] required"); Console.WriteLine("-pool [maxinum pool size numbric,default 32] optional"); Console.WriteLine("-proj [the project build name] required"); Console.WriteLine("-0 [the project output path] required"); return; } Console.OutputEncoding = System.Text.Encoding.UTF8; string projName = string.Empty, outPutPath = string.Empty; StringBuilder connection = new StringBuilder(); for (int i = 0; i < args.Length; i++) { var item = args[i].ToLower(); switch (item) { case "-h": connection.Append($"host={args[i + 1]};"); break; case "-p": connection.Append($"port={args[i + 1]};"); break; case "-u": connection.Append($"username={args[i + 1]};"); break; case "-a": connection.Append($"password={args[i + 1]};"); break; case "-d": connection.Append($"database={args[i + 1]};"); break; case "-pool": connection.Append($"maximum pool size={args[i + 1]};"); break; case "-proj": projName = args[i + 1]; break; case "-o": outPutPath = args[i + 1]; break; } i++; } //outPutPath = @"E:\my\"; //projName = "Crmmt"; //PgSqlHelper.InitConnection(null, "Host=127.0.0.1;Port=5432;Username=postgres;Password=postgres;Database=crmmt;Pooling=true;Maximum Pool Size=100"); PgSqlHelper.InitConnection(null, connection.ToString()); GeneralFactory.Build(outPutPath, projName); Console.WriteLine("success....."); }
public void ToPipe() { var user = User.Context.OrderBy(f => f.Createtime).Where(f => f.Age == 18).ToPipe(); var user2 = User.Context.OrderBy(f => f.Createtime).Where(f => f.Age == 38).ToPipe(); var articles = Article.Context.ToPipe(); var result = PgSqlHelper.ExecutePipeLine(true, user, user2, articles); Assert.Equal("5d6e36bc953f702910000007", result[2][0].Userid); }
public static void Init(StagingOptions options) { PgSqlHelper.InitConnection(options); Type[] jsonTypes = { typeof(JToken), typeof(JObject), typeof(JArray) }; NpgsqlNameTranslator translator = new NpgsqlNameTranslator(); NpgsqlConnection.GlobalTypeMapper.UseJsonNet(jsonTypes); NpgsqlConnection.GlobalTypeMapper.MapEnum <Et_data_state>("public.et_data_state", translator); NpgsqlConnection.GlobalTypeMapper.MapEnum <Et_role>("public.et_role", translator); }
public static void Generate(string rootpath, string modelpath, string projName) { rootPath = rootpath; modelPath = modelpath; projectName = projName; string _sqltext = @" select a.oid,a.typname,b.nspname from pg_type a INNER JOIN pg_namespace b on a.typnamespace = b.oid where a.typtype = 'e' order by oid asc"; List <EnumTypeInfo> list = new List <EnumTypeInfo>(); PgSqlHelper.ExecuteDataReader(dr => { list.Add(new EnumTypeInfo() { Oid = Convert.ToInt32(dr["oid"]), TypeName = dr["typname"].ToString(), NspName = dr["nspname"].ToString() }); }, System.Data.CommandType.Text, _sqltext); string _fileName = Path.Combine(modelpath, "_Enums.cs"); using (StreamWriter writer = new StreamWriter(File.Create(_fileName), System.Text.Encoding.UTF8)) { writer.WriteLine("using System;"); writer.WriteLine(); writer.WriteLine($"namespace {projectName}.Model"); writer.WriteLine("{"); for (int i = 0; i < list.Count; i++) { var item = list[i]; writer.WriteLine($"\tpublic enum {item.TypeName.ToUpperPascal()}"); writer.WriteLine("\t{"); string sql = $"select oid,enumlabel from pg_enum WHERE enumtypid = {item.Oid} ORDER BY oid asc"; PgSqlHelper.ExecuteDataReader(dr => { string c = i < list.Count ? "," : ""; writer.WriteLine($"\t\t{dr["enumlabel"]}{c}"); }, System.Data.CommandType.Text, sql); writer.WriteLine("\t}"); } writer.WriteLine("}"); } GenerateMapping(list); }
protected void Get_Primarykey(int oid) { string _sqltext = $@"SELECT b.attname, format_type(b.atttypid, b.atttypmod) AS data_type FROM pg_index a INNER JOIN pg_attribute b ON b.attrelid = a.indrelid AND b.attnum = ANY(a.indkey) WHERE a.indrelid = '{schemaName}.{table.name}'::regclass AND a.indisprimary; "; PgSqlHelper.ExecuteDataReader(dr => { PrimarykeyInfo pk = new PrimarykeyInfo(); pk.Field = dr["attname"].ToString(); pk.Typname = dr["data_type"].ToString(); pkList.Add(pk); }, CommandType.Text, _sqltext); }
private static List <TableViewModel> GetTables(string schema) { string _sqltext = $@"SELECT table_name,'table' as type FROM INFORMATION_SCHEMA.tables WHERE table_schema='{schema}' AND table_type='BASE TABLE' UNION ALL SELECT table_name,'view' as type FROM INFORMATION_SCHEMA.views WHERE table_schema = '{schema}'"; List <TableViewModel> tableList = new List <TableViewModel>(); PgSqlHelper.ExecuteDataReader(dr => { TableViewModel model = new TableViewModel() { name = dr["table_name"].ToString(), type = dr["type"].ToString() }; tableList.Add(model); }, CommandType.Text, _sqltext); return(tableList); }
static void Main(string[] args) { if (args == null || args.Length == 0) { Console.WriteLine("try --help"); return; } if (args[0] == "--help") { Console.WriteLine("Use the following parameters to create a project using Mystaging.App,The parameter name ignore case"); Console.WriteLine("-h [host/ip] required"); Console.WriteLine("-p [port] required"); Console.WriteLine("-u [postgresql database access username] required"); Console.WriteLine("-a [postgresql database auth password] required"); Console.WriteLine("-d [postgresql database] required"); Console.WriteLine("-pool [maxinum pool size numbric,default 32] optional"); Console.WriteLine("-proj [the project build name] required"); Console.WriteLine("-0 [the project output path] required"); return; } string projName = string.Empty, outPutPath = string.Empty; StringBuilder connection = new StringBuilder(); for (int i = 0; i < args.Length; i++) { var item = args[i].ToLower(); switch (item) { case "-h": connection.Append($"host={args[i + 1]};"); break; case "-p": connection.Append($"port={args[i + 1]};"); break; case "-u": connection.Append($"username={args[i + 1]};"); break; case "-a": connection.Append($"password={args[i + 1]};"); break; case "-d": connection.Append($"database={args[i + 1]};"); break; case "-pool": connection.Append($"maximum pool size={args[i + 1]};"); break; case "-proj": projName = args[i + 1]; break; case "-o": outPutPath = args[i + 1]; break; } i++; } //var outPutPath = @"D:\MyGitHub\mystaging"; //var projName = "MyStaging.SuperApp"; //MyStaging.Common.StagingOptions options = new Common.StagingOptions() //{ // ConnectionMaster = "Host=127.0.0.1;Port=5432;Username=postgres;Password=123456;Database=mystaging;Pooling=true;Maximum Pool Size=10;" //}; //PgSqlHelper.InitConnection(options); var options = new Common.StagingOptions() { ConnectionMaster = connection.ToString() }; PgSqlHelper.InitConnection(options); GeneralFactory factory = new GeneralFactory(outPutPath, projName); factory.Build(); Console.WriteLine("success....."); }
public static void Init(ILogger logger, string connectionMaster, string[] connectionSlaves = null, int slavesMaxPool = -1) { PgSqlHelper.InitConnection(logger, connectionMaster, connectionSlaves, slavesMaxPool); }
private void Get_Fields() { string _sqltext = @"SELECT a.oid ,c.attnum as num ,c.attname as field , (case when f.character_maximum_length is null then c.attlen else f.character_maximum_length end) as length ,c.attnotnull as notnull ,d.description as comment ,(case when e.typcategory ='G' then e.typname when e.typelem = 0 then e.typname else e2.typname end) as type ,(case when e.typelem = 0 then e.typtype else e2.typtype end) as data_type ,e.typcategory ,f.is_identity from pg_class a inner join pg_namespace b on a.relnamespace=b.oid inner join pg_attribute c on attrelid = a.oid LEFT OUTER JOIN pg_description d ON c.attrelid = d.objoid AND c.attnum = d.objsubid and c.attnum > 0 inner join pg_type e on e.oid=c.atttypid left join pg_type e2 on e2.oid=e.typelem inner join information_schema.columns f on f.table_schema = b.nspname and f.table_name=a.relname and column_name = c.attname WHERE b.nspname='{0}' and a.relname='{1}';"; _sqltext = string.Format(_sqltext, this.schemaName, this.table.name); PgSqlHelper.ExecuteDataReader(dr => { FieldInfo fi = new FieldInfo(); fi.Oid = Convert.ToInt32(dr["oid"]); fi.Field = dr["field"].ToString(); fi.Length = Convert.ToInt32(dr["length"].ToString()); fi.Is_not_null = Convert.ToBoolean(dr["notnull"]); fi.Comment = dr["comment"].ToString(); fi.Data_Type = dr["data_type"].ToString(); fi.Db_type = dr["type"].ToString(); fi.Db_type = fi.Db_type.StartsWith("_") ? fi.Db_type.Remove(0, 1) : fi.Db_type; fi.PgDbType = PgsqlType.SwitchToSql(fi.Data_Type, fi.Db_type); fi.Is_identity = dr["is_identity"].ToString() == "YES"; fi.Is_array = dr["typcategory"].ToString() == "A"; fi.Is_enum = fi.Data_Type == "e"; fi.CsType = PgsqlType.SwitchToCSharp(fi.Db_type); if (fi.Is_enum) { fi.CsType = fi.CsType.ToUpperPascal(); } string _notnull = ""; if ( fi.CsType != "string" && fi.CsType != "byte[]" && fi.CsType != "JToken" && !fi.Is_array && fi.CsType != "System.Net.IPAddress" && fi.CsType != "System.Net.NetworkInformation.PhysicalAddress" && fi.CsType != "System.Xml.Linq.XDocument" && fi.CsType != "System.Collections.BitArray" && fi.CsType != "object" ) { _notnull = fi.Is_not_null ? "" : "?"; } string _array = fi.Is_array ? "[]" : ""; fi.RelType = $"{fi.CsType}{_notnull}{_array}"; // dal this.fieldList.Add(fi); }, CommandType.Text, _sqltext); }
/// <summary> /// 静态构造器 /// </summary> static ExceptionLogRepository() { //初始化SQL工具 _SqlHelper = new PgSqlHelper(GlobalSetting.ReadConnectionString); }
public static void Init(StagingOptions options) { PgSqlHelper.InitConnection(options); }
public static void Init(ILogger logger, string connectionString) { PgSqlHelper.InitConnection(logger, connectionString); }