ArcGIS Engine - 操作PostgreSQL数据库

1.安装NpgSQL

使用C#访问本地数据库，特别是PostSQL的话，就用NpgSQL就Ok了，直接用NuGet为你的解决方案添加即可。右击选择“管理NuGet程序包”安装Npgsql。

2.连接数据库

		//配置数据库连接返回数据库连接 
        public static NpgSQLConnection Connection()
        {
            //配置数据库连接，Port安装默认端口号为5432
            string hostname = "127.0.0.1";
            string database = "postgres";
            string username = "postgres";
            string password = "*****";
            
            string connectionString= string.Format("PORT=5432;DATABASE={0};HOST={1};PASSWORD={2};USER ID={3};Pooling = false", database, hostname, password, username);  
            
            //配置数据库连接字符串
            try
            {
                NpgsqlConnection connection = new NpgsqlConnection(connectionString);
                connection.Open();
				return connection;
            }
            catch (Exception e)
            {
                return MessageBox.show(e.Message);
            } 
            
        }

3.操作数据库

		private static DataToDataBase()
		{
 			//连接到数据库
            NpgsqlConnection connection = Connection();

            //数据库操作
            try
            {
                string SQL = "";
                string TableName = "DataBase";
                string colunm = "";

                //新建表格
                SQL = "select * from pg_class where relname = " + "'" + TableName + "'";
                using (NpgsqlCommand cmd = new NpgsqlCommand(SQL, connection))
                {
                    object obj = cmd.ExecuteScalar();

                    //如果存在删除表格
                    if (obj != null)
                    {
                        SQL = "drop table " + TableName;
                        response = DatabaseHelper.ExecuteNonQuery(SQL, connection);
                        if (response.Code != Constant.Success) return response;
                    }

                    //创建表格
                    for (int i = 0; i < Exportable_Fields_List.Count; i++)
                    {
                        colunm += '"' + Exportable_Fields_List[i].Name  + '"' + " ";
                        colunm += DataType(Exportable_Fields_List[i].DataType) + ",";

                    }
                    SQL = "create table " + TableName + "(" + colunm.Substring(0, colunm.Length - 1) + ")";
                    response = DatabaseHelper.ExecuteNonQuery(SQL, connection);
                    if (response.Code != Constant.Success) return response;

                }

                //获取工程工作空间
                IWorkspace workspace = null;
                string projectPath = ProjectHelper.GetProjectPath();
                response = MapHelper.GetShapefileWorkspace(projectPath, out workspace);
                if (response.Code == Constant.Failure) return response;

                //检查并获取要素图层
                ILayer pLayer = null;
                response = CheckNTSLFeatureLayer(feature, workspace, out pLayer);
                if (response.Code == Constant.Failure) return response;

                //遍历所有要素
                IFeatureLayer pFeatureLayer = pLayer as IFeatureLayer;
                IFeatureClass pFeatureClass = pFeatureLayer.FeatureClass;
                IFeatureCursor pFeatureCursor = pFeatureClass.Search(null, true);
                IFeature pFeature = pFeatureCursor.NextFeature();
                while (pFeature != null)
                {
                    string FilesName = "";
                    string Values = "";

                    //获取数据
                    for (int i = 0; i < Exportable_Fields_List.Count; i++)
                    {
                        int l = pFeature.Fields.FindField(Exportable_Fields_List[i].Name);
                        if (l != -1)
                        {
                            //获取对应字段的值
                            object Value = pFeature.get_Value(l);
                            
                            if (Value != null)
                            {

                                FilesName += '"' + Exportable_Fields_List[i].Name + '"' + ",";

                                Values += "'" + Value.ToString() + "'" + ",";
                                
                            }
                           
                        }

                    }

                    //数据入库
                    if (FilesName != "" && Values != "")
                    {
                        SQL = "insert into " + TableName + " (" + FilesName.Substring(0, FilesName.Length - 1) + ")" + " values " + "(" + Values.Substring(0, Values.Length - 1) + ")";
                        response = DatabaseHelper.ExecuteNonQuery(SQL, connection);
                        if (response.Code != Constant.Success) return response;
                    }
                    
                    pFeature = pFeatureCursor.NextFeature();
                }

                //断开数据库连接
                connection.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
	}