コード例 #1
0
        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) });
            }
        }
コード例 #2
0
 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;
         }
     }
 }
コード例 #3
0
 /// <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);
     }
 }
コード例 #4
0
        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");
            }
        }
コード例 #5
0
ファイル: SeedData.cs プロジェクト: sphiecoh/MusicStore
        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 });
                        });
                    }
                }
            }
        }
コード例 #6
0
            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);
            }
コード例 #7
0
        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);
            }
        }
コード例 #8
0
ファイル: UserDal.cs プロジェクト: tudou777/netcoreapi
        /// <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 });
            }
        }
コード例 #9
0
        /// <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 });
            }
        }
コード例 #10
0
ファイル: BaseDao.cs プロジェクト: Niles-team/graph-master
 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;
         }
     }
 }
コード例 #11
0
ファイル: TestSuites.cs プロジェクト: nathan-c/Dapper
        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);");
            }
        }
コード例 #12
0
        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();
        }
コード例 #13
0
        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();
        }
コード例 #14
0
        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);
        }
コード例 #15
0
ファイル: Program.cs プロジェクト: pilad0hwtts/PsqlDotnet
        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);
        }