public static void Convert(string shapefile, string connectionString, string tableName, int srid = 0, string targetProjectionWKT = null) { GeometryTransform transform = GeometryTransform.GetTransform(shapefile, targetProjectionWKT); GeometryFactory factory = new GeometryFactory(new PrecisionModel(), srid); using (SqlConnection conn = new SqlConnection(connectionString)) using (SqlBulkCopy copy = new SqlBulkCopy(conn)) using (ShapefileDataReader reader = new ShapefileDataReader(shapefile, factory, transform)) { conn.Open(); string createTableSql = GenerateCreateTableQuery(reader, tableName); using (SqlCommand createTableComm = new SqlCommand(createTableSql, conn)) createTableComm.ExecuteNonQuery(); copy.SqlRowsCopied += (object sender, SqlRowsCopiedEventArgs e) => { System.Console.Clear(); System.Console.WriteLine("Copied " + e.RowsCopied); }; copy.NotifyAfter = 257; copy.DestinationTableName = tableName; copy.WriteToServer(new ShapefileBulkSqlReader(reader, srid)); } }
private static string GenerateCreateTableQuery(ShapefileDataReader reader, string tableName) { StringBuilder bldr = new StringBuilder(); bldr.Append("CREATE TABLE ["); bldr.Append(tableName); bldr.Append("] ([Id_"); bldr.Append(tableName); bldr.Append("] [int] IDENTITY(1,1) NOT NULL, [Geom] [geometry] NOT NULL, "); DbfHeader header = reader.DbfHeader; for (int i = 0; i < header.Count; i++) { DbfColumn col = header[i]; bldr.Append('['); bldr.Append(col.Name); bldr.Append("] "); switch (col.Type) { case DbfColumnType.Character: bldr.Append("[varchar]("); bldr.Append(col.Length); bldr.Append(")"); break; case DbfColumnType.Float: case DbfColumnType.Number: bldr.Append("[float]"); break; case DbfColumnType.Boolean: bldr.Append("[bit]"); break; case DbfColumnType.Date: bldr.Append("[datetime]"); break; default: throw new Exception(String.Format("Column type '{0}' is not supported.", (char)col.Type)); } bldr.Append(" NULL, "); } bldr.Append("CONSTRAINT [PK_"); bldr.Append(tableName); bldr.Append("] PRIMARY KEY CLUSTERED ([Id_"); bldr.Append(tableName); bldr.Append("] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON))"); return bldr.ToString(); }
public ShapefileBulkSqlReader(ShapefileDataReader reader, int srid) { _reader = reader; _srid = srid; }