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