public bool ImportTable(string tableName, string filePath, List <string> listAtrr) { if (File.Exists(filePath)) { ShpReader shpReader = new ShpReader(filePath); // 检查矢量文件的有效性 if (!shpReader.IsValidDataSource()) { return(false); } string wkt = shpReader.GetSridWkt(); int srid = shpReader.GetSrid(); try { using (NpgsqlConnection sqlConn = new NpgsqlConnection(_connStr)) { sqlConn.Open(); //建表 Dictionary <string, string> attr = shpReader.GetAttributeType(); List <string> shpAttr = new List <string>(); Hashtable hashTable = new Hashtable(); foreach (KeyValuePair <string, string> item in attr) { if (listAtrr.Exists(t => t.ToUpper() == item.Key.ToUpper())) { shpAttr.Add(item.Key); hashTable.Add(item.Key, item.Value); } } if (shpAttr.Count <= 0) { return(false); } //向表中添加数据 int pFeatureCount = shpReader.GetFeatureCount(); string sqlStr = String.Format("select Max(SID) from {0}", tableName); NpgsqlCommand sqlC = new NpgsqlCommand(sqlStr, sqlConn); object obj = sqlC.ExecuteScalar(); NpgsqlCommand sqlComm = new NpgsqlCommand(); int maxValue = (obj is System.DBNull) ? 0 : Convert.ToInt32(obj); int maxNum = maxValue + 1; string sqlCStr = string.Empty; for (int i = 0; i < pFeatureCount; i++) { string colStr = string.Empty; try { string valueStr = string.Empty; Dictionary <string, string> attr1 = shpReader.GetOneFeatureAttribute(i, shpAttr); foreach (KeyValuePair <string, string> item in attr1) { colStr += "\"" + item.Key + "\"" + ","; if (hashTable[item.Key].ToString() == "OFTString") { if (item.Value.Contains("'")) { string newValue = item.Value.Replace("'", "''"); valueStr += String.Format("'{0}',", newValue); } else { valueStr += String.Format("'{0}',", item.Value); } } else { if (!string.IsNullOrEmpty(item.Value)) { valueStr += String.Format("{0},", item.Value); } else { valueStr += String.Format("'{0}',", item.Value); } } } colStr = colStr.TrimEnd(','); valueStr = valueStr.TrimEnd(','); string sid = string.Format("{0}", maxNum + i); string geomStr = String.Format("'{0}'", shpReader.GetOneFeatureGeomWkt(i)); //string sqlInsert = String.Format("insert into {0}(sid,geom,{1}) values({2},{3},{4})", tableName, colStr, sid, geomStr, valueStr); //sqlComm.Connection = sqlConn; //sqlComm.CommandText = sqlInsert; //sqlComm.ExecuteNonQuery(); sqlCStr += string.Format("({0},{1},{2}),", sid, geomStr, valueStr); } catch (Exception ex) { Abp.Logging.LogHelper.LogException(ex); //throw ex; } if ((i % _count == 0) || (i == pFeatureCount - 1)) { sqlCStr = sqlCStr.TrimEnd(','); string sqlInsert = String.Format("insert into {0}(sid,geom,{1}) values{2}", tableName, colStr, sqlCStr); //sqlComm.Connection = sqlConn; //sqlComm.CommandText = sqlInsert; //sqlComm.ExecuteNonQuery(); ExceuteSQL(sqlInsert, string.Empty); sqlCStr = string.Empty; } } return(true); } } catch (Exception ex) { Abp.Logging.LogHelper.LogException(ex); //throw ex; } } return(false); }
public bool WriteData2Sql(VectorDataType data, out string dataGuid, out string tablename) { if (!File.Exists(data.Filename)) { dataGuid = null; tablename = null; return(false); } string tableName = Path.GetFileNameWithoutExtension(data.Filename).ToUpper(); string filename = Path.GetFileName(data.Filename); ShpReader shpReader = new ShpReader(data.Filename); // 检查矢量文件的有效性 if (!shpReader.IsValidDataSource()) { dataGuid = null; tablename = null; return(false); } // 创建索引表 //if (!IsTableNameExist(_connStr, _geometryColumnsTableName)) //{ // CreateGeometryColumnsTable(_connStr); //} int srid = shpReader.GetSrid(); try { using (NpgsqlConnection sqlConn = new NpgsqlConnection(_connStr)) { sqlConn.Open(); //建表 Dictionary <string, string> attr = shpReader.GetAttributeType(); List <string> shpAttr = new List <string>(); Hashtable hashTable = new Hashtable(); foreach (KeyValuePair <string, string> item in attr) { shpAttr.Add(item.Key); hashTable.Add(item.Key, item.Value); } if (IsTableNameExist(_connStr, tableName)) { NpgsqlCommand NpgsqlCommand = new NpgsqlCommand(string.Format("drop table {0}", tableName.ToUpper()), sqlConn); NpgsqlCommand.ExecuteNonQuery(); } string sqlCommStr = String.Format("CREATE TABLE {0}(SID SERIAL primary key,", tableName); for (int i = 0; i < shpAttr.Count; i++) { string attrName = shpAttr[i]; sqlCommStr += String.Format("{0} {1},", attrName, Utility.GdalTypeToNpSqlType(hashTable[attrName].ToString())); } sqlCommStr += String.Format("geom geometry)"); NpgsqlCommand sqlComm = new NpgsqlCommand(sqlCommStr, sqlConn); sqlComm.ExecuteNonQuery(); //向表中添加数据 int pFeatureCount = shpReader.GetFeatureCount(); string sqlStr = String.Format("select Max(SID) from {0}", tableName); NpgsqlCommand sqlC = new NpgsqlCommand(sqlStr, sqlConn); object obj = sqlC.ExecuteScalar(); int maxValue = (obj is System.DBNull) ? 0 : Convert.ToInt32(obj); int maxNum = maxValue + 1; for (int i = 0; i < pFeatureCount; i++) { try { string sqlInsert = String.Format("insert into {0} values({1},", tableName, maxNum + i); Dictionary <string, string> attr1 = shpReader.GetOneFeatureAttribute(i, shpAttr); foreach (KeyValuePair <string, string> item in attr1) { if (hashTable[item.Key].ToString() == "OFTString") { if (item.Value.Contains("'")) { string newValue = item.Value.Replace("'", "''"); sqlInsert += String.Format("'{0}',", newValue); } else { sqlInsert += String.Format("'{0}',", item.Value); } } else { if (!string.IsNullOrEmpty(item.Value)) { sqlInsert += String.Format("{0},", item.Value); } else { sqlInsert += String.Format("'{0}',", item.Value); } } } sqlInsert += String.Format("'{0}')", shpReader.GetOneFeatureGeomWkt(i)); sqlComm.CommandText = sqlInsert; sqlComm.ExecuteNonQuery(); } catch (Exception ex) { //NpgsqlEventLog. } } } } catch (Exception ex) { using (NpgsqlConnection sqlConn = new NpgsqlConnection(_connStr)) { sqlConn.Open(); string sqlCommStr = string.Format("drop table {0}", tableName.ToUpper()); NpgsqlCommand sqlComm = new NpgsqlCommand(sqlCommStr, sqlConn); sqlComm.ExecuteNonQuery(); sqlConn.Close(); } throw new Exception("NpgsqlVectorDataService:WriteVector出错,错误原因:" + ex.Message); } //zhangheng 2014-06-24 //如果索引表中已有记录,先删除. //DelIndexData(filename); //InsertIntoGeomColmn(_connStr, data.DataGuid, filename, tableName, shpReader.GetDeminsion(), shpReader.GetGeomType(), shpReader.GetSridWkt()); dataGuid = data.DataGuid; tablename = tableName; return(true); }