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);
}