C# NpgsqlConnection.CreateCommand方法代码示例

        public void Bug1011241_DiscardAll()
        {
            var connection = new NpgsqlConnection(ConnectionString + ";SearchPath=public");
            connection.Open();

            if (connection.PostgreSqlVersion < new Version(8, 3, 0)
                || new NpgsqlConnectionStringBuilder(ConnectionString).Protocol == ProtocolVersion.Version2)
            {
                connection.Close();
                return;
            }

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SHOW SEARCH_PATH";
                Assert.AreEqual("public", command.ExecuteScalar());

                command.CommandText = "SET SEARCH_PATH = \"$user\"";
                command.ExecuteNonQuery();
                command.CommandText = "SHOW SEARCH_PATH";
                Assert.AreEqual("\"$user\"", command.ExecuteScalar());
            }
            connection.Close();

            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SHOW SEARCH_PATH";
                Assert.AreEqual("public", command.ExecuteScalar());
            }
            connection.Close();
        }

        public static User CreateUser(User user)
        {
            NpgsqlConnection conn = new NpgsqlConnection (conn_string);
            NpgsqlCommand comm = conn.CreateCommand ();

            // Add data to the User table
            string sql = "INSERT INTO users (name, email) VALUES (@name, @email)";
            comm.CommandText = sql;

            comm.Parameters.Add ("@name", user.Name);
            comm.Parameters.Add ("@email", user.Email);

            conn.Open ();
            comm.ExecuteNonQuery ();
            comm.Dispose ();

            User new_user = GetUser (user.Name);

            // Add data to the OpenID table
            comm = conn.CreateCommand ();

            sql = "INSERT INTO openids (id, userid) VALUES (@id, @userid)";
            comm.CommandText = sql;

            comm.Parameters.Add ("@id", user.OpenID);
            comm.Parameters.Add ("@userid", new_user.ID);

            comm.ExecuteNonQuery ();

            comm.Dispose ();
            conn.Close ();

            return new_user;
        }

        public IDocumentStore Initialize(Action<MartenRegistry> register = null)
        {
            var builder = new NpgsqlConnectionStringBuilder(_targetConnectionString);
            var targetDatabaseName = builder.Database;

            using (var connection = new NpgsqlConnection(_masterConnectionString))
            {
                connection.Open();
                var existsCommand = connection.CreateCommand();
                existsCommand.CommandText = "select (count(*) > 0)::boolean as exists from pg_database where datname=:0";
                existsCommand.Parameters.Add(new NpgsqlParameter("0", targetDatabaseName));
                var exists = (bool)existsCommand.ExecuteScalar();
                if (!exists)
                {
                    var createCommand = connection.CreateCommand();
                    createCommand.CommandText = string.Format("CREATE DATABASE \"{0}\"", targetDatabaseName);
                    createCommand.ExecuteNonQuery();
                }
            }
            var store = DocumentStore.For(cfg =>
            {
                cfg.Connection(_targetConnectionString);
                cfg.AutoCreateSchemaObjects = true;
                cfg.Schema.For<Commit>()
                    .Searchable(x => x.StreamId)
                    .Searchable(x => x.StreamVersion);
                if (register != null)
                {
                    register(cfg.Schema);
                }
            });
            return store;
        }

        protected override void CreateTestStore()
        {
            using (var conn = new NpgsqlConnection(this.ConnectionString))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    var version = PostGisVersion();
                    if (version.StartsWith("1."))
                    {
                        cmd.CommandText = "DELETE FROM \"geometry_columns\" WHERE \"f_table_name\" = 'nts_io_postgis_2d'; ";
                        cmd.ExecuteNonQuery();
                    }

                    cmd.CommandText = "DROP TABLE IF EXISTS \"nts_io_postgis_2d\";";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = 
                        "CREATE TABLE \"nts_io_postgis_2d\" (id int primary key, wkt text);" 
                      + "SELECT AddGeometryColumn('nts_io_postgis_2d', 'the_geom', " + 4326 + ",'GEOMETRY', 2);"                        
                        ;
                    cmd.ExecuteNonQuery();
                }
            }
            RandomGeometryHelper.Ordinates = Ordinates.XY;
        }

        public void Test()
        {

            using (NpgsqlConnection cn = new NpgsqlConnection(ConnectionString))
            {
                cn.Open();
                NpgsqlCommand cmd = cn.CreateCommand();
                cmd.CommandText = "SELECT \"srid\", \"srtext\" FROM \"public\".\"spatial_ref_sys\" ORDER BY \"srid\";";

                int counted = 0;
                int failed = 0;
                int tested = 0;
                using (NpgsqlDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (r != null)
                    {
                        while (r.Read())
                        {
                            counted++;
                            string srtext = r.GetString(1);
                            if (!string.IsNullOrEmpty(srtext))
                            {
                                tested++;
                                if (!TestParse(r.GetInt32(0), srtext)) failed++;
                            }
                        }
                    }
                }

                Console.WriteLine("\n\nTotal number of Tests {0}, failed {1}", tested, failed);
                Assert.IsTrue(failed == 0);
            }

        }

        public static void Main(string[] args)
        {
            NpgsqlConnection connection = new NpgsqlConnection
                ("Server= localhost; Database= postgres; User Id=dbprueba; Password = 12345");
            connection.Open();

            //Comando para insertar datos en las columnas
            //IDbCommand command2 = connection.CreateCommand();
            //command2.CommandText ="insert into prueba (id, nombre) values (2, 'otro')";
            //command2.ExecuteNonQuery();
            // fin comando

            //comando para mostrar datos de tabla "prueba"
            IDbCommand command = connection.CreateCommand();
            command.CommandText ="select * from prueba";
            IDataReader datareader = command.ExecuteReader();

            Console.WriteLine(datareader.GetName(0));
            Console.WriteLine(datareader.GetName(1));

            while(datareader.Read() ){
                Console.WriteLine(datareader["id"]);
                Console.WriteLine(datareader["nombre"]);
            }
            //fin comando

            datareader.Close();
            connection.Close();
        }

        public static List<Role> GetRolesForUser(int userId)
        {
            NpgsqlConnection conn = new NpgsqlConnection (conn_string);
            NpgsqlCommand comm = conn.CreateCommand ();

            string sql = "SELECT * FROM roles, userroles WHERE userroles.roleid = roles.id and userroles.userid = @userid";
            comm.CommandText = sql;

            comm.Parameters.Add ("@userid", userId);

            conn.Open ();

            NpgsqlDataReader reader = comm.ExecuteReader ();

            List<Role> roles = new List<Role> ();

            while (reader.Read ())
                roles.Add (new Role (reader));

            reader.Close ();
            comm.Dispose ();
            conn.Close ();

            return roles;
        }

    private void fillComboBox()
    {
        CellRenderer cellRenderer = new CellRendererText();
        comboBox.PackStart(cellRenderer, false); //expand=false
        comboBox.AddAttribute (cellRenderer, "text", 1);

        ListStore listStore = new ListStore(typeof(string), typeof(string));

        comboBox.Model = listStore;

        string connectionString = "Server=localhost;Database=PruebaBD;User Id=ximo;Password=admin";
        IDbConnection dbConnection = new NpgsqlConnection(connectionString);
        dbConnection.Open ();

        IDbCommand dbCommand = dbConnection.CreateCommand();
        dbCommand.CommandText = "select id, nombre from categoria";

        IDataReader dataReader = dbCommand.ExecuteReader();

        while (dataReader.Read ())
            listStore.AppendValues (dataReader["id"].ToString (), dataReader["nombre"].ToString () );

        dataReader.Close ();

        dbConnection.Close ();
    }

    //Modelo de funcionamiento
    //1º Referencias (System data y Npgsql)
    //2º Diseñador
    //3º picar codigo
    private void fillComboBox()
    {
        CellRendererText cellRenderer = new CellRendererText();
        comboBox.PackStart(cellRenderer,false); //expand = false
        comboBox.AddAttribute(cellRenderer,"text", 1);

        ListStore liststore = new ListStore(typeof(string), typeof(string));

        comboBox.Model=liststore;

        //Atacamos a la base de datos para conectarnos
        string connectionString="Server=localhost; Database=dbprueba; User Id=dbprueba; password=1234";
        IDbConnection dbConnection = new NpgsqlConnection(connectionString);
        dbConnection.Open();

        IDbCommand dbCommand = dbConnection.CreateCommand();
        dbCommand.CommandText="select id, nombre from categoria";

        IDataReader dataReader = dbCommand.ExecuteReader();

        while(dataReader.Read()){

            liststore.AppendValues(dataReader("id").ToString(), dataReader("nombre").ToString());
        }

        dataReader.Close ();
        dbConnection.Close();
    }

    protected void OnExecuteActionActivated(object sender, System.EventArgs e)
    {
        string connectionString = "Server=localhost;Database=aula;User Id=aula;Password=clase" ;

        IDbConnection dbConnection = new NpgsqlConnection(connectionString);
        IDbCommand selectCommand = dbConnection.CreateCommand();
        selectCommand.CommandText = "select * from articulo where id=4";
        IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
        dbDataAdapter.SelectCommand = selectCommand;

        DataSet dataSet = new DataSet();

        dbDataAdapter.Fill (dataSet);

        Console.WriteLine("Tables.Count={0}", dataSet.Tables.Count);
        foreach (DataTable dataTable in dataSet.Tables)
            show (dataTable);

        DataRow dataRow = dataSet.Tables[0].Rows[0];
        dataRow["nombre"] = DateTime.Now.ToString ();
        Console.WriteLine("Tabla con los cambios");
        show (dataSet.Tables[0]);

        dbDataAdapter.Update (dataSet);
    }

    private void fillComboBox()
    {
        CellRenderer cellRenderer = new CellRendererText();
        comboBox.PackStart(cellRenderer, false); //expand:false
        comboBox.AddAttribute(cellRenderer, "text", 0);

        CellRenderer cellRenderer2 = new CellRendererText();
        comboBox.PackStart(cellRenderer2, false); //expand:false
        comboBox.AddAttribute(cellRenderer2, "text", 1);

        ListStore listStore = new ListStore(typeof(string),typeof(string));

        string connectionString = "Server=localhost;Database=dbprueba2;User Id=dbprueba; Password=sistemas;";
        IDbConnection dbConnection = new NpgsqlConnection(connectionString);
        dbConnection.Open();

        IDbCommand dbCommand = dbConnection.CreateCommand();
        dbCommand.CommandText = "SELECT id, nombre FROM categoria";

        IDataReader dataReader = dbCommand.ExecuteReader();

        while(dataReader.Read()) {
            listStore.AppendValues(dataReader["id"].ToString(),dataReader["nombre"].ToString());
        }

        comboBox.Model = listStore;

        dataReader.Close();
        dbConnection.Close();
    }

        internal static bool ExecuteNonQuery(string CommandName, CommandType cmdType, NpgsqlParameter[] pars)
        {
            int result = 0;

            using (NpgsqlConnection con = new NpgsqlConnection(CONNECTION_STRING))
            {
                using (NpgsqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = CommandName;
                    cmd.Parameters.AddRange(pars);

                    try
                    {
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }

                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        //Log.Error(e);
                        throw;
                    }
                }
            }

            return (result > 0);
        }

        public static int AddTodo(string todo)
        {
            //string sql = "insert into todos(task) values (:todo)";

            //using (var conn = new NpgsqlConnection(connStr))
            //{
            //    NpgsqlCommand cmd = conn.CreateCommand();
            //    cmd.CommandText = sql;
            //    cmd.Parameters.AddWithValue("todo", todo);
            //    conn.Open();
            //    cmd.ExecuteNonQuery();
            //    conn.Close();
                
            //}

            string sql = "insert into todos(task) values (:todo) RETURNING id;";
            int a = 0;
            using (var conn = new NpgsqlConnection(connStr))
            {
                NpgsqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("todo", todo);

                conn.Open();
                a = (int)cmd.ExecuteScalar();
                conn.Close();

            }
            return a;

        }

        private async Task<string> Create()
        {
            string sql;
            var assembly = typeof(TestDatabase).GetTypeInfo().Assembly;
            using (var reader = new StreamReader(assembly.GetManifestResourceStream(_sqlFile)))
            {
                sql = await reader.ReadToEndAsync();
            }

            var dbName = await CreateDatabase();
            var connectionString = $"Server=localhost;Port=5432;Database={_databaseName};User Id=postgres;Password=s1mpl3;";

            using (var connection = new NpgsqlConnection(connectionString))
            {
                await connection.OpenAsync();

                using (var command = connection.CreateCommand())
                {
                    foreach (
                        var statement in
                        Regex.Split(sql, @"^\s*GO\s*$", RegexOptions.Multiline)
                            .Where(s => !string.IsNullOrWhiteSpace(s)))
                    {
                        //_logger.LogDebug(sql);
                        command.CommandText = statement;
                        await command.ExecuteNonQueryAsync();
                    }
                }
            }
            return _connectionString = connectionString;
        }

    public void TestReturn()
    {
      var parameters = GetParameters("public", "test_return");

      using (var conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
      {
        conn.Open();
        using (var cmd = conn.CreateCommand())
        {
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = "public.test_return";


          cmd.Parameters.Add(new NpgsqlParameter
                             {
                               Direction = ParameterDirection.Input,
                               Value = 2
                             });
          cmd.Parameters.Add(new NpgsqlParameter
                             {
                               ParameterName = "__return",
                               Direction = ParameterDirection.ReturnValue
                             });


          using (var rdr = cmd.ExecuteReader())
          {
            rdr.Read();
          }
        }
      }
    }