//根据schema和指定投影参考在数据库中创建一个表，创建失败返回false
public bool CreateTable(Schema schema, int srid)
{
string connStr = "Server=" + server + ";Port=" + port + ";UserId=" + user + ";Password="******";Database=" + database + ";";
try
{
string sql = "CREATE TABLE " + schema.name + "(gid SERIAL PRIMARY KEY,";
foreach (string field in schema.fields.Keys)
{
if (field.Equals("gid") || field.Equals("geom") || field.Equals("geom1"))
{
continue;
}
sql += field + " " + Type2String.OGRFiledType2String(schema.fields[field].GetFieldType()) + ",";
}
sql += "geom geometry(" + Type2String.wkbGeometryType2String(schema.geometryType) + "," + srid + "));";
NpgsqlConnection conn = new NpgsqlConnection(connStr);
conn.Open();
IDbCommand dbcmd = conn.CreateCommand();
dbcmd.CommandText = sql;
dbcmd.ExecuteNonQuery();
conn.Close();
return(true);
}
catch
{
return(false);
}
}
//把FeatureSource保存成一个数据库表，如果已经存在则删除原表，重新创建一个表，需要指定投影参考对应的整数
public bool Save2Table(FeatureSource fs, int srid)
{
FeatureSource fc = fs;
string connStr = "Server=" + server + ";Port=" + port + ";UserId=" + user + ";Password="******";Database=" + database + ";";
try
{
if (HasTable(fc.schema.name))
{
DropTable(fc.schema.name);
}
CreateTable(fc.schema, srid);
NpgsqlConnection conn = new NpgsqlConnection(connStr);
conn.Open();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand dbcmd = conn.CreateCommand();
List <Feature> flst = fc.features.featureList;
for (int i = 0; i < flst.Count; i++)
{
string sql = "INSERT INTO " + fc.schema.name + "(gid,";
string temp = "";
int index = 0;
dbcmd.Parameters.Clear();
foreach (string field in flst[i].schema.fields.Keys)
{
if (field.Equals("gid") || field.Equals("geom") || field.Equals("geom1"))
{
continue;
}
sql += "\"" + field + "\",";
temp += ":p" + index + ",";
dbcmd.Parameters.AddWithValue("p" + index, flst[i].attributes[field]);
index++;
}
if (Type2String.wkbGeometryType2String(fs.schema.geometryType) == "MULTIPOLYGON")
{
sql += "geom) VALUES('" + flst[i].featureID + "'," + temp + "st_multi(st_geomfromText('" + Feature2WKT.Geometry2WKT(flst[i].geometry) + "'," + srid + ")))";
}
else
{
sql += "geom) VALUES('" + flst[i].featureID + "'," + temp + "st_geomfromText('" + Feature2WKT.Geometry2WKT(flst[i].geometry) + "'," + srid + "))";
}
dbcmd.CommandText = sql;
dbcmd.ExecuteNonQuery();
}
tran.Commit();
conn.Close();
return(true);
}
catch
{
return(false);
}
}
