public void StoreElement(XElement element, string friendlyName) { var constring = "Server=192.168.252.41;Port=5432;UserId=postgres;Password=postgres2018;Database=TestDB;"; using (var con = new Npgsql.NpgsqlConnection(constring)) { var deleteSql = "delete from dataprotectiondb where name=@name"; con.Execute(deleteSql, new { name = friendlyName }); var sql = "insert into dataprotectiondb(name,value) values(@name,@value)"; con.Execute(sql, new { name = friendlyName, value = Newtonsoft.Json.JsonConvert.SerializeObject(element) }); } }
public static void RunLoad(string connectionString, string script) { try { // Make a connection using the Npgsql provider. using (var conn = new Npgsql.NpgsqlConnection(connectionString)) { conn.Open(); conn.Execute(script); } } catch (Exception ex) { if (ex.Message.IndexOf("timeout", StringComparison.OrdinalIgnoreCase) > 0) { // do nothing, if the loaded files are large this operation // may take a while to complete... } else { if (ex.Message.IndexOf("stl_load_errors", StringComparison.OrdinalIgnoreCase) > 0) { throw ex; } throw ex; } } }
/// <summary> /// 执行pg sql /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnCreateTable_Click(object sender, EventArgs e) { try { if (MessageBox.Show("执行此语句会把原表删除并重新创建,你确定吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { using (var con = new Npgsql.NpgsqlConnection(_pgconnectionstring)) { if (string.IsNullOrEmpty(_pgsql)) { MessageBox.Show("请先选择表生成PostgreSql语句后再执行!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { con.Execute(_pgsql); } } } MessageBox.Show("执行成功!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Information); btnMigration.Enabled = true; } catch (Exception exc) { MessageBox.Show(exc.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
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"); } }
public static void Populate(string connectionString) { GenFu.Configure <Genre>().Fill(x => x.Name).AsMusicGenreName(); GenFu.Configure <Artist>().Fill(x => x.Name).AsMusicArtistName(); GenFu.Configure <Album>().Fill(x => x.AlbumArtUrl, "/Content/Images/placeholder.gif").Fill(x => x.Price).WithinRange(100, 500); var rand = new System.Random(100); using (var conn = new Npgsql.NpgsqlConnection(connectionString)) { if (conn.ExecuteScalar <int>("select count(1) from albums ") == 0) { for (int i = 0; i < 10; i++) { var genre = A.New <Genre>(); var artist = A.New <Artist>(); var genreid = conn.ExecuteScalar <int>("insert into genres (name,description) values (@name,@description) returning genreid;", new{ genre.Name, genre.Description }); var artistid = conn.ExecuteScalar <int>("insert into artists (name) values (@name) returning artistid;", new{ artist.Name }); var albums = A.ListOf <Album>(10); albums.ForEach(a => { conn.Execute("insert into albums(genreid,artistid,title,price,albumarturl) values(@genreid,@artistid,@title,@price,@albumarturl) returning albumid;", new { genreid, artistid, a.Title, a.Price, a.AlbumArtUrl }); }); } } } }
public bool MigrationData(string tablename, string deleteSql, string selectSql, DateTime date) { var par = File.ReadAllText(@"C:\MyFile\abc\par.txt"); var pgconnection = File.ReadAllText(@"C:\MyFile\abc\pgconnection.txt"); var pagesize = 1000; var pageindex = 1; using (var pgcon = new Npgsql.NpgsqlConnection(pgconnection)) { pgcon.Execute(deleteSql, new { orgshortname = par, date }); } while (true) { var list = GetList(tablename, selectSql, date, pagesize, pageindex); if (list.Count == 0) { break; } var insertSql = new GenerPgSql().GenerySql(pgconnection, list, tablename); using (var pgcon = new Npgsql.NpgsqlConnection(pgconnection)) { pgcon.Execute(insertSql); } pageindex++; } return(true); }
static void Main(string[] args) { var tablename = ""; var sqlconnection = "连接字符串"; using (var sqlcon = new SqlConnection(sqlconnection)) { var watch = new Stopwatch(); watch.Start(); var pagesize = 1000; var pageindex = 1; while (true) { var s = "select ROW_NUMBER() OVER (ORDER BY 主键) as rowno,* from " + tablename; var sql = $@"SELECT TOP {pagesize} * FROM ({s})querytable WHERE rowno > {(pageindex - 1) * pagesize} "; var list = sqlcon.Query <dynamic>(sql, new { orgshortname = "nss" }).ToList(); if (list.Count == 0) { break; } var insertSql = new GenerPgSql().GenerySql(list, tablename); var pgconnection = "pg连接字符串"; using (var pgcon = new Npgsql.NpgsqlConnection(pgconnection)) { pgcon.Execute(insertSql, list); } Console.WriteLine(pageindex); pageindex++; } Console.WriteLine("制造数据总共花费{0}ms.", watch.Elapsed.TotalMilliseconds); } }
/// <summary> /// 删除 /// </summary> /// <param name="user_id"></param> /// <returns></returns> public void Delete(string user_id) { string sql = "delete from t_user where user_id=@user_id"; using (var conn = new Npgsql.NpgsqlConnection(ConnectionString)) { conn.Execute(sql, new { user_id = user_id }); } }
/// <summary> /// 删除 /// </summary> /// <param name="system_id"></param> /// <returns></returns> public void Delete(string system_id) { string sql = "delete from t_system where system_id=@system_id"; using (var conn = new Npgsql.NpgsqlConnection(ConnectionString)) { conn.Execute(sql, new { system_id = system_id }); } }
protected void Execute(string sql, object parameters = null) { using (IDbConnection connection = new Npgsql.NpgsqlConnection(this.connectionString)) { try { connection.Open(); connection.Execute(sql, parameters); } catch (Exception exc) { Console.WriteLine(exc.Message); throw exc; } } }
static PostgresqlTestSuite() { using (var connection = new Npgsql.NpgsqlConnection(ConnectionString)) { // ReSharper disable once AccessToDisposedClosure void dropTable(string name) => connection.Execute($"DROP TABLE IF EXISTS {name}; "); connection.Open(); dropTable("Stuff"); connection.Execute("CREATE TABLE Stuff (TheId serial not null, \"Name\" varchar(100) not null, \"Created\" timestamp null);"); dropTable("People"); connection.Execute("CREATE TABLE People (Id serial not null, \"Name\" varchar(100) not null);"); dropTable("\"users\""); connection.Execute("CREATE TABLE \"users\" (Id serial not null, \"Name\" varchar(100) not null, \"Age\" int not null);"); dropTable("Automobiles"); connection.Execute("CREATE TABLE Automobiles (Id serial not null, \"Name\" varchar(100) not null);"); dropTable("Results"); connection.Execute("CREATE TABLE Results (Id serial not null, \"Name\" varchar(100) not null, \"Order\" int not null);"); dropTable("ObjectX"); connection.Execute("CREATE TABLE ObjectX (ObjectXId varchar(100) not null, \"Name\" varchar(100) not null);"); dropTable("ObjectY"); connection.Execute("CREATE TABLE ObjectY (ObjectYId int not null, \"Name\" varchar(100) not null);"); dropTable("ObjectZ"); connection.Execute("CREATE TABLE ObjectZ (Id int not null, \"Name\" varchar(100) not null);"); dropTable("GenericType"); connection.Execute("CREATE TABLE GenericType (Id varchar(100) not null, \"Name\" varchar(100) not null);"); dropTable("NullableDates"); connection.Execute("CREATE TABLE NullableDates (Id serial not null, \"DateValue\" timestamp null);"); } }
private void btnMigration_Click1(object sender, EventArgs e) { var tablenames = new List <string>(); foreach (ListViewItem item in lvTable.CheckedItems) { tablenames.Add(item.Text); } var frmMessage = new frmMessage(); var thread = new Thread(delegate() { foreach (var item in tablenames) { this.BeginInvoke(new ThreadStart(delegate() { frmMessage.labMessage.Text = $"正在查询{item}表……"; })); List <dynamic> list = null; string pgsql = null; using (var con = new SqlConnection(_sqlconnectionstring)) { list = con.Query <dynamic>($@"select * from {item}").ToList(); this.BeginInvoke(new ThreadStart(delegate() { frmMessage.labMessage.Text = $"正在搬运{item}表,总共{list.Count()}条记录……"; })); var fields = con.Query <string>($@"select sc.name from syscolumns sc,systypes st where sc.xtype=st.xtype and st.status=0 and sc.id in(select id from sysobjects where xtype='U' and name='{item}')"); var pgsqlField = new StringBuilder(); var pgsqlPar = new StringBuilder(); foreach (var field in fields) { pgsqlField.Append($"{field},"); pgsqlPar.Append($"@{field},"); } pgsql = $"insert into {item}({pgsqlField.ToString().TrimEnd(',')}) values({pgsqlPar.ToString().TrimEnd(',')})"; } var index = 0; var count = 1000; using (var pgcon = new Npgsql.NpgsqlConnection(_pgconnectionstring)) { while (index * count < list.Count) { this.BeginInvoke(new ThreadStart(delegate() { frmMessage.labMessage.Text = $"正在搬运{item}表,{index * count}/{list.Count()}……"; })); var newlist = new List <dynamic>(); newlist.AddRange(list.Skip(index * count).Take(count)); pgcon.Execute(pgsql, newlist); index++; } } } this.BeginInvoke(new ThreadStart(delegate() { frmMessage.picLoad.Image = Resources.migration1; frmMessage.labMessage.Text = $"全部搬运完成!"; frmMessage.tmrCount.Stop(); })); }); frmMessage.MigrationThread = thread; frmMessage.tmrCount.Start(); thread.Start(); frmMessage.ShowDialog(); }
private void btnMigration_Click(object sender, EventArgs e) { var tablenames = new List <string>(); foreach (ListViewItem item in lvTable.CheckedItems) { tablenames.Add(item.Text); } var frmMessage = new frmMessage(); var thread = new Thread(delegate() { foreach (var item in tablenames) { this.BeginInvoke(new ThreadStart(delegate() { frmMessage.labMessage.Text = $"正在查询{item}表……"; })); var pagesize = 5000; var pageindex = 1; while (true) { List <dynamic> list = null; string pgsql = null; List <string> keyfields = null; int recordCount = 0; using (var con = new SqlConnection(_sqlconnectionstring)) { keyfields = con.Query <string>($@"SELECT c.name Cname FROM sys.objects T INNER JOIN sys.objects P ON t.object_id=p.parent_object_id AND t.type='U' AND p.type='PK' INNER JOIN sys.SysColumns C ON c.id=t.object_id INNER JOIN sysindexes i ON i.name=p.name INNER JOIN sysindexkeys k ON k.id=c.id AND k.colid=c.colid AND k.indid=i.indid where t.name='{item}'").ToList(); if (keyfields.Count() > 0) { list = con.Query <dynamic>($@"SELECT TOP {pagesize} * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {keyfields[0]}) AS RowNo,* FROM {item})querytable WHERE RowNo > {(pageindex - 1) * pagesize} ").ToList(); if (list.Count == 0) { break; } } else { list = con.Query <dynamic>($@"SELECT * from {item}").ToList(); } recordCount = con.ExecuteScalar <int>($@"SELECT count(1) as sl from {item}"); //生成pg的insert into 语句 var fields = con.Query <string>($@"select sc.name from syscolumns sc,systypes st where sc.xtype=st.xtype and st.status=0 and sc.id in(select id from sysobjects where xtype='U' and name='{item}')"); var pgsqlField = new StringBuilder(); var pgsqlPar = new StringBuilder(); foreach (var field in fields) { pgsqlField.Append($"{field},"); pgsqlPar.Append($"@{field},"); } pgsql = $"insert into {item}({pgsqlField.ToString().TrimEnd(',')}) values({pgsqlPar.ToString().TrimEnd(',')})"; } using (var pgcon = new Npgsql.NpgsqlConnection(_pgconnectionstring)) { this.BeginInvoke(new ThreadStart(delegate() { frmMessage.labMessage.Text = $"正在搬运{item}表,{pageindex * pagesize}/{recordCount}条记录……"; })); pgcon.Execute(pgsql, list); } //没有关键了,就退出 if (keyfields.Count() == 0) { break; } pageindex++; } } this.BeginInvoke(new ThreadStart(delegate() { frmMessage.picLoad.Image = Resources.migration1; frmMessage.labMessage.Text = $"全部搬运完成!"; frmMessage.tmrCount.Stop(); })); }); frmMessage.MigrationThread = thread; frmMessage.tmrCount.Start(); thread.Start(); frmMessage.ShowDialog(); }
public bool Execute(ISessionContext context) { var logger = context.GetLogger(); var options = context.Options; AWSS3Helper s3 = null; try { var loadScript = options.Get("sqlScriptPath", ""); var customCSharpScriptPath = options.Get("customCSharpScriptPath", ""); if ((!String.IsNullOrWhiteSpace(loadScript)) && (!String.IsNullOrWhiteSpace(customCSharpScriptPath))) { throw new Exception("No action configured"); } // prepare paths var parsedLoadScript = FileTransferDetails.ParseSearchPath(loadScript); var parsedCustomCSharpScriptPath = FileTransferDetails.ParseSearchPath(customCSharpScriptPath); // open s3 connection s3 = new AWSS3Helper(options.Get("awsAccessKey", ""), options.Get("awsSecretAccessKey", ""), parsedLoadScript.BucketName, Amazon.RegionEndpoint.USEast1, true); // load sql script string sqlScript = null; if (!String.IsNullOrWhiteSpace(loadScript)) { sqlScript = s3.ReadFileAsText(parsedLoadScript.FilePath, true); } // generate code IAWSRedshiftPluginDynamicScript customCode = null; if (!String.IsNullOrWhiteSpace(customCSharpScriptPath)) { // load custom code var csharpScript = s3.ReadFileAsText(parsedCustomCSharpScriptPath.FilePath, true); var evaluator = ScriptEvaluator.CompileAndCreateModel(csharpScript); if (evaluator.HasError || evaluator.Model == null) { throw new Exception("Script compilation error. " + (evaluator.Message ?? "<empty>")); } customCode = evaluator.Model; } // execute commands using (var conn = new Npgsql.NpgsqlConnection(RedshiftHelper.GetConnectionString(context))) { conn.Open(); if (customCode != null) { logger.Debug("Custom csharp code Initialize"); customCode.Initialize(conn, s3, context); logger.Debug("Custom csharp code BeforeExecution"); customCode.BeforeExecution(); logger.Debug("Custom csharp code PrepareSqlCOPYCommand"); if (!String.IsNullOrEmpty(sqlScript)) { sqlScript = customCode.PrepareSqlCOPYCommand(sqlScript); } } if (!String.IsNullOrEmpty(sqlScript)) { logger.Debug("SQL command start"); try { conn.Execute(sqlScript); } catch (Exception ex) { // do nothing in case of timeout... some operations may take a while to complete... if (ex.Message.IndexOf("timeout", StringComparison.OrdinalIgnoreCase) < 0) { throw ex; } logger.Info("SQL command executed, but is still running (connection timeout)..."); } logger.Debug("SQL command end"); } if (customCode != null) { logger.Debug("Custom csharp code AfterExecution"); customCode.AfterExecution(); } } logger.Success("Done"); } catch (Exception ex) { context.Error = ex.Message; logger.Error(ex); return(false); } return(true); }
static void Main(string[] args) { Log.Logger = new LoggerConfiguration() .WriteTo.Console() .CreateLogger(); Log.Information("Start testing PostgreConfigurer"); var workDirectory = Path.Combine(Directory.GetParent(Directory.GetCurrentDirectory()).FullName, "postgres-oracle"); string username; SecureString password; Console.WriteLine("Username: "******"Enter password: "******"force"); if (isForceInstall || conf.IsInstalled) { if (conf.IsRunning) { conf.StopPostgres(); } conf.InstallPostgreSql(); ps.InstallPostgis(); } var sudoUser = new Npgsql.NpgsqlConnectionStringBuilder { Host = "127.0.0.1", Database = "postgres", Username = "******", Password = "******" }; if (!conf.IsRunning) { conf.RunPostgres(); } using (var contex = new Contex()) { bool isDbReady = !args.Any(x => x == "full_recreate"); //FIXME: Не работает /*try { * isDbReady &= contex.Database.CanConnect (); * } catch { * isDbReady = false; * }*/ if (!isDbReady) { Log.Information("Need recreation of database"); using (var connection = new Npgsql.NpgsqlConnection(sudoUser.ToString())) { connection.Open(); connection .DropDatabase("TestDatabase", true) .CreateUser(new NpgsqlUtils.CreateUserOptions { Username = "******", Password = "******" }) .CreateDatabase(new NpgsqlUtils.CreateDatabaseOptions { Name = "TestDatabase", Owner = "pilad", }); contex.Database.EnsureCreated(); connection.ChangeDatabase("testdatabase"); connection.Execute(ps.ActivatePostgisSql()); //TODO: Is nesessary? connection.Close(); } Log.Information("Recreating full scheme contex..."); } contex.Works.Add(new Work { first = "123", second = "dsa" }); contex.SaveChanges(); foreach (var work in contex.Works) { Log.Information("{first} {second}", work.first, work.second); } } //conf.StopPostgres(); System.Threading.Thread.Sleep(1000); }