postgresql数据库连接，增删改查数据

postgresql数据库连接

//地址连接字符串格式如下
//DATABASE=test;HOST=111.12.13.200;USER ID=postgres;PASSWORD=1234;PORT=5432;Pooling = false;encoding=UNICODE
 NpgsqlConnection conn = null; 
 string connectionString = string.Format("DATABASE={0};HOST={1};USER ID={2};PASSWORD={3};PORT={4};Pooling = false;encoding=UNICODE", database, hostname, username, password, port);
 try
  {
      conn = new NpgsqlConnection(connectionString);
      conn.Open();
  }
  catch (Exception ex)
  {
  //连接失败
  }
  finally
  {
      if (conn != null) conn.Close();
  }

postgesql增删改查数据

NpgsqlCommand command3 = null;
int querycount = 0;
//查询
string querySql = "select count(*) from tableName";
command3 = new NpgsqlCommand(querySql, txConn);
var reader = command3.ExecuteReader();
while (reader.Read())
{
    string count = reader.GetValue(0).ToString();
    querycount = Convert.ToInt32(count);
}
if (querycount > 0)
{
	//删除
    string delSql = string.Format("delete from tableName");
    command3 = new NpgsqlCommand(delSql, txConn);
    int del = command3.ExecuteNonQuery();
}
else
{
	//插入数据:以下列出插入几何图形坐标点、和json格式字符的语句
	string tableName ="testTable";
	double x=0.0011;//X坐标点
	double y=2.111;//y坐标点
	string pJson="{***}";
	string insertsql= string.Format("insert into {0} (name,geom,json) values ({1},('SRID=4490;POINT({3} {4})'),('{5}'))", tableName ,x, y, pJson);
	 
	pgsqlCommand command2 = new NpgsqlCommand(insertsql, txConn);
	int sw = Convert.ToInt32(command2.ExecuteScalar());
}