postgre数据库与.net

来源：互联网发布：列宁格勒号驱逐舰数据编辑：程序博客网时间：2024/05/17 16:43

用BeginTransaction后，就好像在系统上开辟一块内存，将执行的SQL语句后的结果，暂时保存到此内存中，

直到commit或者rollback后，这块内存的数据才会被提交到数据库中去。如果执行一条插入、删除或者修改语句后，

但是未执行commit，数据会被保存到此内存中，此时、执行查询语句的话，会将内存中的上一次执行的插入、删除或

者修改语句的结果给查询出来，而不是把数据库中的数据查询出来。

可以理解为，执行程序连接到数据库之后，会自动的开辟一块内存空间，之后每次执行（第一次除外）的SQL语句都会在此空间

上保存痕迹，所以要注意在每次执行SQL语句后是否已把数据提交到数据库中，否则有可能会对下次操作产生影响。

        private NpgsqlConnection conn;
        private void TestConnection()
        {
            string path = "Server=localhost;Port=5432;User Id=postgres;Password=123456;Database=xyz;";
            try
            {
                conn = new NpgsqlConnection(path);
                conn.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void TestDisconnect()
        {
            try
            {
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void TestSelect()
        {
            try
            {
                NpgsqlCommand command = new NpgsqlCommand("select * from zht3", conn);

                NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter();
                DataTable dt = new DataTable();
                dataAdapter.SelectCommand = command;
                int count = dataAdapter.Fill(dt);

                foreach (DataRow dr in dt.Rows)
                {
                    MessageBox.Show(dr[0].ToString() + dr[1].ToString() + dr[2].ToString());
                }

//NpgsqlDataReader dr = command.ExecuteReader();
//object dr = command.();

                //while (dr.Read())
                //{
                //    MessageBox.Show(dr[0].ToString() + dr[1].ToString() + dr[2].ToString());
                //    //for (int i = 0; i < dr.FieldCount; i++)
                //    //{
                //    //    MessageBox.Show(dr[i].ToString() + dr[i].ToString() + dr[i].ToString());
                //    //}
                //}
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void TestInsert()
        {
            NpgsqlTransaction tr;

            tr = (NpgsqlTransaction)conn.BeginTransaction();
            try
            {
                NpgsqlCommand comm = new NpgsqlCommand();

comm.Connection = conn;

string cmdText = "insert into zht3 (a, b, c) values ('4aaaaa', '4bbbbb', '4cccc');";

comm.CommandText = cmdText;

comm.ExecuteNonQuery();

cmdText = "insert into zht3 (a, b, c) values ('5aaaaa', '5bbbbb', '5scccc');";

comm.CommandText = cmdText;

comm.ExecuteNonQuery();

                tr.Commit();
            }
            catch (Exception ex)
            {
                tr.Rollback();
                MessageBox.Show(ex.ToString());
            }
        }

        private void TestDelete()
        {
            try
            {
                NpgsqlTransaction tr;

NpgsqlCommand comm = new NpgsqlCommand();

comm.Connection = conn;
tr = (NpgsqlTransaction)conn.BeginTransaction();

string cmdText = "delete from zht3 where a = '4aaaaa';";

comm.CommandText = cmdText;

comm.ExecuteNonQuery();

tr.Commit();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void TestUpdate()
        {
            try
            {
                NpgsqlTransaction tr;

NpgsqlCommand comm = new NpgsqlCommand();

comm.Connection = conn;
tr = (NpgsqlTransaction)conn.BeginTransaction();

string cmdText = "update zht3 set a='6aaaaa', b='6bbbbb', c='6cccc' where a='5aaaaa';";

comm.CommandText = cmdText;

comm.ExecuteNonQuery();

                tr.Commit();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }