C#语言在winform下链接postgresql数据库

【实例截图】

【核心代码】

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Npgsql;
using Mono;
using ESRI.ArcGIS.Controls;
using ESRI.ArcGIS.Geometry;
using ESRI.ArcGIS.Carto;
using ESRI.ArcGIS.Display;
using ESRI.ArcGIS.Geodatabase;
using ESRI.ArcGIS.DataSourcesFile;
using ESRI.ArcGIS.esriSystem;
using ESRI.ArcGIS.DataSourcesGDB;
using ESRI.ArcGIS.ArcMapUI;
using OSGeo.OGR;

namespace ConectPGsql
{
    public partial class Form1 : Form
    {
        //postgis数据库连接参数
        string STR = "Server=localhost;Port=5432;UserId=postgres;Password=123456;Database=omtgis";

        public Form1()
        {
            InitializeComponent();

            GetData();//得到postgis里的shp数据并生成图层
        
            Initlayertree();//图层控制树

        
        }

        private void GetData()
        {
          NpgsqlConnection conn = new NpgsqlConnection(STR);
          conn.Open();

          //得到基础设施类型
          DataSet ds0 = new DataSet();
          NpgsqlDataAdapter objAdapter0 = new NpgsqlDataAdapter("SELECT * FROM slx", conn);
          objAdapter0.Fill(ds0, "ds0");
          DataTable DT0 = ds0.Tables[0];

        //得到shp图层的全部属性数据
          //for (int p = 0; p < DT0.Rows.Count; p  )
          //{
              DataSet ds = new DataSet();
              string sql1 = "SELECT * FROM xzs";
              //string sql1 = "SELECT * FROM "   DT0.Rows[p]["名称"].ToString();
              
              NpgsqlDataAdapter objAdapter = new NpgsqlDataAdapter(sql1, conn);
              objAdapter.Fill(ds, "a");
              DataTable DT = ds.Tables[0];

              //得到shp图层的字段信息
              IFieldsEdit curFileds = new FieldsClass();
              IFieldEdit curField = new FieldClass();
              for (int w = 0; w < DT.Columns.Count; w  )
              {
                  curField = new FieldClass();
                  curField.Name_2 = DT.Columns[w].ColumnName;
                  curField.Type_2 = esriFieldType.esriFieldTypeString;
                  curFileds.AddField(curField);
              }

              //得到shp图层的要素并图层化
              string sql2 = "SELECT  st_asbinary(geom) FROM xzs";
              //string sql2 = "SELECT st_asbinary(geom) FROM "   DT0.Rows[p]["名称"].ToString();
              NpgsqlCommand objCommand = new NpgsqlCommand(sql2, conn);
              NpgsqlDataReader dr = objCommand.ExecuteReader();
              int m = 0;
              while (dr.Read())
              {
                  
                  IGeometry geom = null;
                  int countin;
                
                  object byteArrayObject = dr.GetValue(0);
                
                  if (byteArrayObject== null)
                  { return; }


                  IGeometryFactory2 factory = new GeometryEnvironment() as IGeometryFactory2;
                  factory.CreateGeometryFromWkbVariant(byteArrayObject, out geom, out countin);
                 
         
                  if (geom != null)
                  {
                      //生成图层
                      IFeatureLayer pFeatureLayer = CreateFeatureLayerInmemeory(DT.Rows[m]["名称"].ToString(), DT.Rows[m]["名称"].ToString(), null, geom.GeometryType, curFileds as IFields);
                      
                      IFeatureClass pFeatureClass = pFeatureLayer.FeatureClass;
                      IFeature pFeature = pFeatureClass.CreateFeature();
                      pFeature.Shape = geom;
                    
                      pFeature.Store();
                  
                      axMapControl1.AddLayer(pFeatureLayer);
                  }
                      m  ;
                  
              }
          //}
        }

        private static IFeatureLayer CreateFeatureLayerInmemeory(string DataSetName, string AliaseName, ISpatialReference SpatialRef, esriGeometryType GeometryType, IFields PropertyFields)
        {
            IWorkspaceFactory workspaceFactory = new InMemoryWorkspaceFactoryClass();
            ESRI.ArcGIS.Geodatabase.IWorkspaceName workspaceName = workspaceFactory.Create("", "MyWorkspace", null, 0);
            ESRI.ArcGIS.esriSystem.IName name = (IName)workspaceName;
            ESRI.ArcGIS.Geodatabase.IWorkspace inmemWor = (IWorkspace)name.Open();

            IField oField = new FieldClass();
            IFields oFields = new FieldsClass();
            IFieldsEdit oFieldsEdit = null;
            IFieldEdit oFieldEdit = null;
            IFeatureClass oFeatureClass = null;
            IFeatureLayer oFeatureLayer = null;

            try
            {
                oFieldsEdit = oFields as IFieldsEdit;
                oFieldEdit = oField as IFieldEdit;

                for (int i = 0; i < PropertyFields.FieldCount; i  )
                {
                    oFieldsEdit.AddField(PropertyFields.get_Field(i));
                }

                IGeometryDef geometryDef = new GeometryDefClass();
                IGeometryDefEdit geometryDefEdit = (IGeometryDefEdit)geometryDef;
                geometryDefEdit.AvgNumPoints_2 = 1;
                geometryDefEdit.GeometryType_2 = GeometryType;
                geometryDefEdit.GridCount_2 = 1;
                geometryDefEdit.HasM_2 = false;
                geometryDefEdit.HasZ_2 = false;
                geometryDefEdit.SpatialReference_2 = SpatialRef;
                geometryDefEdit.SpatialReference_2 = new UnknownCoordinateSystemClass();//没有这一句就报错，说尝试读取或写入受保护的内存。
                geometryDefEdit.SpatialReference.SetDomain(-200, 200, -200, 200);//没有这句就抛异常来自HRESULT：0x8004120E。


                oFieldEdit.Name_2 = "SHAPE";
                oFieldEdit.Type_2 = esriFieldType.esriFieldTypeGeometry;
                oFieldEdit.GeometryDef_2 = geometryDef;
                oFieldEdit.IsNullable_2 = true;
                oFieldEdit.Required_2 = true;
                oFieldsEdit.AddField(oField);

                oFeatureClass = (inmemWor as IFeatureWorkspace).CreateFeatureClass(DataSetName, oFields, null, null, esriFeatureType.esriFTSimple, "SHAPE", "");
                (oFeatureClass as IDataset).BrowseName = DataSetName;

                oFeatureLayer = new FeatureLayerClass();
                oFeatureLayer.Name = AliaseName;
                oFeatureLayer.FeatureClass = oFeatureClass;

            }
            catch
            {
            }
            return oFeatureLayer;
        }


        private void Initlayertree()
        {
            TreeNode node1 = new TreeNode("图层");

            for (int k = 0; k < axMapControl1.LayerCount; k  )
            {
                TreeNode node2 = new TreeNode(axMapControl1.get_Layer(k).Name);
                node1.Nodes.Add(node2);
            }
                this.treeView1.Nodes.Add(node1);
        }

        private void GetD()
        {
            IGeometry geom = null;
            int countin;

        NpgsqlConnection dbcon = new NpgsqlConnection(STR);
        dbcon.Open();
        DataSet ds = new DataSet();
        NpgsqlDataAdapter objAdapter = new NpgsqlDataAdapter("SELECT  st_asbinary(geom) FROM sfz", dbcon);
        objAdapter.Fill(ds, "a");//a这个表是自定义的

    

        IGeometryFactory3 factory = new GeometryEnvironment() as IGeometryFactory3;
        factory.CreateGeometryFromWkbVariant(ds.Tables[0].Rows[0], out geom, out countin);

            ILayer fl = geom as ILayer;
            axMapControl1.AddLayer(fl);
        //dataGridView1.DataSource = ds.Tables[0];
        
      //  AsBinary();
        }


        /// <summary>
        /// Load a PostGis layer in the test MapControl
        /// </summary>
        private void LoadPostGisLayer()
        {
            // Open workspace and feature class.
           // IWorkspaceFactory wksf = new PostGisWorkspaceFactory();

            //Open from zig file
           // IFeatureWorkspace fwks = (IFeatureWorkspace)wksf.OpenFromFile(@"C:\ziggis\ZigGis\example2.zig", 0);

          
            //Open from PropertySet
            //IPropertySet ps = new PropertySetClass();
            //ps.SetProperty("server", "localhost");
            //ps.SetProperty("database", "TUTORIAL");
            //ps.SetProperty("user", "psqluser");
            //ps.SetProperty("password", "psqluser");
            //ps.SetProperty("port", "5432");
            //ps.SetProperty("configfile", @"C:\ziggis\ZigGis\logging.config");
            //IFeatureWorkspace fwks = (IFeatureWorkspace)wksf.Open(ps, 0);
          

            //IFeatureClass fc = fwks.OpenFeatureClass("paolo.canada");
            //// Create the new layer (default renderer is ISimpleRenderer)
            //IFeatureLayer layer = new PostGisFeatureLayer();
            //layer.FeatureClass = fc;
            //layer.Name = fc.AliasName;
            //ILayer ly = layer as ILayer;
            //IGeoFeatureLayer gfl = layer as IGeoFeatureLayer;
            ////doSimpleRenderer(gfl);
            //doUniqueValueRenderer(gfl);
            ////IFeatureRenderer fr = new VerySimpleCustomRenderer();
            ////gfl.Renderer = fr;
            //axMapControl1.AddLayer(gfl as ILayer, 0);
            ////SelectFeaturesFromFeatureClass(fc, fwks as IWorkspace);
            ////SelectFeaturesFromFeatureLayer(layer);
        }

        /// <summary>
        /// Load a Shapefile layer in the test MapControl
        /// </summary>
        private void LoadShapefile()
        {
            IWorkspaceFactory wf = new ShapefileWorkspaceFactoryClass();
            IFeatureWorkspace fw = (IFeatureWorkspace)wf.OpenFromFile(@"C:\data", 0);
            IFeatureClass fc = fw.OpenFeatureClass("canada");
            IFeatureLayer layer = new FeatureLayerClass();
            layer.FeatureClass = fc;
            layer.Name = fc.AliasName;
            //SelectFeaturesFromFeatureLayer(layer);
            //SelectFeaturesFromFeatureClass(fc, fw as IWorkspace);
            axMapControl1.AddLayer(layer as ILayer, 0);
            IGeoFeatureLayer gfl = layer as IGeoFeatureLayer;
            doUniqueValueRenderer(gfl);
        }

        private void SelectFeaturesFromFeatureLayer(IFeatureLayer fl)
        {
            IFeatureSelection fs = fl as IFeatureSelection;
            IQueryFilter qf = new QueryFilterClass();
            qf.WhereClause = "";
            axMapControl1.ActiveView.PartialRefresh(esriViewDrawPhase.esriViewGeoSelection, null, null);
            fs.SelectFeatures(qf, esriSelectionResultEnum.esriSelectionResultNew, false);
            PrintSelectionSet(fs.SelectionSet);
            axMapControl1.ActiveView.PartialRefresh(esriViewDrawPhase.esriViewGeoSelection, null, null);
        }

        private void SelectFeaturesFromFeatureClass(IFeatureClass fc, IWorkspace ws)
        {
            IQueryFilter qf = new QueryFilterClass();
            qf.WhereClause = "";
            axMapControl1.ActiveView.PartialRefresh(esriViewDrawPhase.esriViewGeoSelection, null, null);
            ISelectionSet ss = fc.Select(qf, esriSelectionType.esriSelectionTypeIDSet, esriSelectionOption.esriSelectionOptionNormal, ws);
            PrintSelectionSet(ss);
            axMapControl1.ActiveView.PartialRefresh(esriViewDrawPhase.esriViewGeoSelection, null, null);
        }

        private void PrintSelectionSet(ISelectionSet ss)
        {
            IEnumIDs enumIDs = ss.IDs as IEnumIDs;
            try
            {
                int oid = enumIDs.Next();
                while (oid != -1)
                {
                    System.Diagnostics.Debug.WriteLine("OID:"   oid);
                    oid = enumIDs.Next();
                }
            }
            catch
            {
                System.Diagnostics.Debug.WriteLine("End of enum!");
            }
        }

        private void doUniqueValueRenderer(IGeoFeatureLayer gfl)
        {
            //TesterUtilities.ApplyUniqueValueRenderer(gfl);
        }

        private void doSimpleRenderer(IGeoFeatureLayer gfl)
        {
            //TesterUtilities.ApplySimpleRenderer(gfl);
        }

        private void ShowTableWindow(ILayer layer)
        {
            ITableWindow2 tw = new TableWindowClass();
            tw.FindViaLayer(layer);
            tw.Layer = layer;
        }

        private void treeView1_AfterCheck(object sender, TreeViewEventArgs e)
        {
            if (e.Action != TreeViewAction.Unknown)
            {
                SetNodeChecked(e.Node, e.Node.Checked);

                //刷新图层
                axMapControl1.ActiveView.Refresh();
                axMapControl1.ActiveView.ScreenDisplay.UpdateWindow();
            }
        }
        private void SetNodeChecked(TreeNode tn, bool Checked)
        {
            if (tn == null) return;

            // 设置子节点状态
            foreach (TreeNode tnChild in tn.Nodes)
            {
                tnChild.Checked = Checked;

                //递归设置字节点状态与父节点状态一致
                SetNodeChecked(tnChild, Checked);
            }

            // 设置父节点状态
            TreeNode tnParent = tn;
            int nNodeCount = 0;

            //循环设置父节点状态
            while (tnParent.Parent != null)
            {
                tnParent = (TreeNode)(tnParent.Parent);
                nNodeCount = 0;
                bool isChecked = false;//记录父节点中是否有选中的子节点
                foreach (TreeNode tnTemp in tnParent.Nodes)
                {
                    for (int k = 0; k < axMapControl1.LayerCount; k  )
                    {
                        if (axMapControl1.get_Layer(k).Name == tnTemp.Text)
                        {
                            axMapControl1.get_Layer(k).Visible = tnTemp.Checked;
                        }
                    }

                    if (tnTemp.Checked)
                    {
                        nNodeCount  ;
                        isChecked = true;
                    }
                }
                if (nNodeCount > 0 && isChecked)
                {
                    tnParent.Checked = true;//若有则选中父节点
                }
                else
                {
                    tnParent.Checked = Checked;
                }
            }

        }
    }
}