C#NpSql操作类(Insert\Update\Delete\Select)

public class PostgresqlHelper{Npgsql.NpgsqlConnection conn;public PostgresqlHelper(string connstr) {conn = new Npgsql.NpgsqlConnection(connstr);}/// <summary>/// 执行SQL/// </summary>/// <param name="sql"></param>/// <returns></returns>public bool ExcutrSql(string sql){try{if (conn.State != ConnectionState.Open){conn.Open();}Npgsql.NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);int suc= cmd.ExecuteNonQuery();if (suc > 0){return true;}else{return false;}}catch (Exception){throw;}}/// <summary>/// Insert插入返回id/// </summary>/// <param name="sql"></param>/// <returns></returns>public int ExcutrInsertSql(string sql){try{if (conn.State != ConnectionState.Open){conn.Open();}Npgsql.NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);int id =Convert.ToInt32(cmd.ExecuteScalar());return id;}catch (Exception){throw;}}/// <summary>/// 获取表格/// </summary>/// <param name="sql"></param>/// <returns></returns>public DataTable getTable(string sql) {try{if (conn.State != ConnectionState.Open){conn.Open();}Npgsql.NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);Npgsql.NpgsqlDataAdapter adp = new NpgsqlDataAdapter(cmd);DataTable dataTable = new DataTable();adp.Fill(dataTable);conn.Close();return dataTable;}catch (Exception ex){throw;}}/// <summary>/// 去重/// </summary>/// <param name="columnName">列名</param>/// <param name="tableName">表名</param>/// <returns></returns>public DataTable distinct(string columnName,string tableName) {try{string sql = string.Format("select distinct {0} from {1}", columnName, tableName);return getTable(sql);}catch (Exception ex){throw;}}/// <summary>/// 获取表格的所有列名/// </summary>/// <param name="tableName"></param>/// <returns></returns>public DataTable TableColumns(string tableName) {try{string sql=string.Format("select column_name from information_schema.columns where table_schema = 'public' and table_name = '{0}' ",tableName);return getTable(sql);}catch (Exception ex){throw;}}/// <summary>/// 空间查询（包含），最后一列为wkt/// </summary>/// <param name="wkt"></param>/// <param name="tableName"></param>/// <returns></returns>public DataTable geomContains(string wkt,string tableName) {try{DataTable columnsDt = TableColumns(tableName);string columns = "";foreach (DataRow item in columnsDt.Rows){if (item[0].ToString().Equals("geom")){continue;}columns = columns + item[0].ToString() + ",";}string sql = "";DataTable dt= getTable(sql);dt.TableName = tableName;return dt;}catch (Exception ex){throw;}}/// <summary>/// 空间查询（包含）:分为点（最后2列为Xy）,线（最后四列为xyxy）/// </summary>/// <param name="wkt"></param>/// <param name="tableName"></param>/// <returns></returns>public DataTable geomContains(string wkt, string tableName,string type){try{DataTable dt = null;DataTable columnsDt = TableColumns(tableName);string columns = "";foreach (DataRow item in columnsDt.Rows){if (item[0].ToString().Equals("geom")){continue;}columns = columns + item[0].ToString() + ",";}if (type == "point"){string sql = "";dt = getTable(sql);dt.TableName = tableName;}else if (type == "line"){string sql = "";dt = getTable(sql);dt.TableName = tableName;}return dt;}catch (Exception ex){throw;}}/// <summary>/// 空间查询（包含）(获取geom和指定列名)/// </summary>/// <param name="wkt"></param>/// <param name="tableName"></param>/// <returns></returns>public DataTable geomContains(string wkt, string tableName,List<string> otherName,string type){try{string columns = "";DataTable dt = null;if (otherName != null){foreach (string item in otherName){if (item.Equals("geom")){continue;}columns = columns + item + ",";}}if (type == "point"){string sql = "";dt = getTable(sql);dt.TableName = tableName;}else if(type == "line"){string sql = "";dt = getTable(sql);dt.TableName = tableName;}return dt;}catch (Exception ex){throw;}}/// <summary>/// 空间查询（单个图层），最后一列为wkt/// </summary>/// <param name="wkt"></param>/// <param name="tableName"></param>/// <returns></returns>public DataTable geomTable(string tableName,bool hasGeom){try{DataTable columnsDt = TableColumns(tableName);string columns = "";foreach (DataRow item in columnsDt.Rows){if (item[0].ToString().Equals("geom")){continue;}columns = columns + item[0].ToString() + ",";}string sql = "";DataTable dt = getTable(sql);if (hasGeom is false){dt.Columns.Remove("wkt");}dt.TableName = tableName;return dt;}catch (Exception ex){throw;}}/// <summary>/// 空间查询（单个图层）:分为点（最后2列为Xy）,线（最后四列为xyxy）/// </summary>/// <param name="wkt"></param>/// <param name="tableName"></param>/// <returns></returns>public DataTable geomTable(string tableName, string type){try{DataTable dt = null;DataTable columnsDt = TableColumns(tableName);string columns = "";foreach (DataRow item in columnsDt.Rows){if (item[0].ToString().Equals("geom")){continue;}columns = columns + item[0].ToString() + ",";}if (type == "point"){string sql = "";dt = getTable(sql);dt.TableName = tableName;}else if (type == "line"){string sql = "";dt = getTable(sql);dt.TableName = tableName;}return dt;}catch (Exception){throw;}}/// <summary>/// 获取Geom和其它指定列:分为点（最后2列为Xy）,线（最后四列为xyxy）/// </summary>/// <param name="tableName"></param>/// <param name="otherColumnName"></param>/// <returns></returns>public DataTable GeomTable(string tableName,List<string> otherColumnName,string type){try{string columns = "";DataTable dt = null;if (otherColumnName != null){foreach (string item in otherColumnName){if (item.Equals("geom")){continue;}columns = columns + item + ",";}}if (type == "point"){string sql = "";dt = getTable(sql);dt.TableName = tableName;}else if (type == "line"){string sql = "";dt = getTable(sql);dt.TableName = tableName;}return dt;}catch (Exception ex){throw;}}/// <summary>/// 获取指定字段的DataTable/// </summary>/// <param name="tableName"></param>/// <param name="id"></param>/// <returns></returns>public DataTable getShapesAtt(string tableName,string id){try{DataTable columnsDt = TableColumns(tableName);string columns = "";for (int i = 0; i < columnsDt.Rows.Count; i++){DataRow item = columnsDt.Rows[i];//移除ID和geom字段if (item[0].ToString().Equals("geom") || item[0].ToString().Equals(MapWinGis.MyConfigPri.postTableID)){continue;}columns = columns + item[0].ToString();if (i < columnsDt.Rows.Count - 1){columns = columns + ",";}}string sql = "";DataTable tempDt = getTable(sql);DataTable dt = new DataTable();dt.Columns.Add("字段", typeof(string));dt.Columns.Add("值", typeof(string));for (int i = 0; i < tempDt.Columns.Count; i++){string fieldName = tempDt.Columns[i].ColumnName;string value = Convert.ToString(tempDt.Rows[0][i]);DataRow dr = dt.NewRow();dr[0] = fieldName;dr[1] = value;dt.Rows.Add(dr);}return dt;}catch (Exception){throw;}}/// <summary>/// 更新shape属性/// </summary>/// <param name="tableName"></param>/// <param name="shapeIndex"></param>/// <param name="dt"></param>/// <returns></returns>public bool UpdateShapeAtt(string tableName, string id,DataTable dt){try{string va = "";//数字字段格式List<string> num_att = tableName.ToLower().Contains("point") ? MyConfigPri.point_num_att : MyConfigPri.line_num_att;//set后面的赋值字符串for (int i = 0; i < dt.Rows.Count; i++){string fieldName = dt.Rows[i][0].ToString();string value = dt.Rows[i][1].ToString();//数字格式的字段更新不能为空if (value.Trim() == "" && num_att.Contains(fieldName)){continue;}va += String.Format("{0}='{1}'",fieldName,value);va += ",";}if (va == ""){return false;}va = va.Substring(0,va.Length-1);string sql = "";return ExcutrSql(sql);}catch (Exception){throw;}}/// <summary>/// Geom更新/// </summary>/// <param name="tableName"></param>/// <param name="shapeIndex"></param>/// <param name="wkt"></param>/// <returns></returns>public bool UpdateShapeGeom(string tableName,string id,string wkt){try{string sql = "";return ExcutrSql(sql);}catch (Exception){throw;}}/// <summary>/// 数据库插入新增一条记录(增加一个shape geom)/// </summary>/// <param name="tableName"></param>/// <param name="insertID"></param>/// <param name="wkt"></param>/// <returns></returns>public int InsertShapeGeom(string tableName, string wkt){try{//Inert字符串string sql = "";return ExcutrInsertSql(sql);}catch (Exception){throw;}}/// <summary>/// 数据库插入新增一条记录(地形图)/// </summary>/// <param name="tableName"></param>/// <param name="insertID"></param>/// <param name="wkt"></param>/// <returns></returns>public int InsertShapeGeom(string tableName, string wkt,string value){try{//Inert字符串string sql = "";return ExcutrInsertSql(sql);}catch (Exception){throw;}}/// <summary>/// 数据库插入新增一条记录(地形图)/// </summary>/// <param name="tableName"></param>/// <param name="insertID"></param>/// <param name="wkt"></param>/// <returns></returns>public int InsertShapeGeom(string tableName, string wkt,string labelText, string value){try{//Inert字符串string sql = "";return ExcutrInsertSql(sql);}catch (Exception){throw;}}/// <summary>/// 删除一条记录(通过id)/// </summary>/// <returns></returns>public bool DeleteRecord(string tableName,string id){try{string sql = "";return ExcutrSql(sql);}catch (Exception){throw;}}}