Will: 利用DotSpatial匯入ShapeFile至MS-SQL

在先前文章有介紹

利用Shape2Sql匯入shpFile至MS-SQL

但會遇到中文字會亂碼的問題

因此利用 DotSpatial 這個open source元件

自己寫了一個function來將ShapeFile匯入MS-SQL

主程式如下：

//匯入ShapeFile到MS-SQL
public static string ImportShapefile(string ShapeFileName, int EpsgCode, string SQLConnection, string TableName)
{
try
{
Shapefile sf = Shapefile.OpenFile(ShapeFileName);
sf.Projection = ProjectionInfo.FromEpsgCode(EpsgCode); //設定來源shapeFile的坐標格式

FeatureSet fs = sf;
for (int i = 0; i < fs.Features.Count; i++)
{
IFeature feature = fs.GetFeature(i); //取得圖層

if (i == 0)
{
//建立Table Schema
string strCreateTable = "SET ANSI_NULLS ON ";
strCreateTable = strCreateTable + "SET QUOTED_IDENTIFIER ON ";
strCreateTable = strCreateTable + "BEGIN ";
strCreateTable = strCreateTable + "CREATE TABLE [dbo].[" + TableName + "]( ";
strCreateTable = strCreateTable + " [PK_ID] [int] IDENTITY(1,1) NOT NULL, ";
for (int j = 0; j < feature.DataRow.Table.Columns.Count; j++)
{
strCreateTable = strCreateTable + " [" + feature.DataRow.Table.Columns[j].ColumnName + "] ";
string strType = feature.DataRow.Table.Columns[j].DataType.Name; //欄位型態
if (strType == "Int16")
strCreateTable = strCreateTable + "[int] ";
else if (strType == "Int32")
strCreateTable = strCreateTable + "[bigint] ";
else if (strType == "Int64")
strCreateTable = strCreateTable + "[bigint] ";
else if (strType == "DateTime")
strCreateTable = strCreateTable + "[datetime] ";
else if (strType == "Double")
strCreateTable = strCreateTable + "[float] ";
else if (strType == "Single")
strCreateTable = strCreateTable + "[real] ";
else if (strType == "String")
strCreateTable = strCreateTable + "[nvarchar] (" + feature.DataRow.Table.Columns[j].MaxLength + ") ";

if (String.IsNullOrEmpty(feature.DataRow.Table.Columns[j].DefaultValue.ToString()) == false)
strCreateTable = strCreateTable + "default " + feature.DataRow.Table.Columns[j].DefaultValue.ToString(); //設定預設值

if (feature.DataRow.Table.Columns[j].AllowDBNull) //設定是否允許為NULL
strCreateTable = strCreateTable + "NULL, ";
else
strCreateTable = strCreateTable + "NOT NULL, ";
}
strCreateTable = strCreateTable + " [geom] [geometry] NULL, "; //設定Geometry
strCreateTable = strCreateTable + "PRIMARY KEY CLUSTERED ";
strCreateTable = strCreateTable + " ( ";
strCreateTable = strCreateTable + " [PK_ID] ASC ";
strCreateTable = strCreateTable + " )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ";
strCreateTable = strCreateTable + ") ON [PRIMARY] ";
strCreateTable = strCreateTable + "END ";

SqlCommand scCreate = new SqlCommand();
scCreate.CommandText = strCreateTable;
string strCreate = Cls_Datasource.ExecuteSQL(SQLConnection, scCreate);
scCreate.Dispose();
}

string strInsertData = "";
//將每個圖層的Metadata寫入資料庫
for (int k = 0; k < feature.DataRow.Table.Rows.Count; k++)
{
strInsertData = "insert into [" + TableName + "] ";
strInsertData = strInsertData + " values ( ";
for (int j = 0; j < feature.DataRow.Table.Columns.Count; j++)
{
if (String.IsNullOrEmpty(feature.DataRow.Table.Rows[k].ItemArray[j].ToString())) //檢查資料是否為NULL
{
strInsertData = strInsertData + "null";
}
else
{
string strType = feature.DataRow.Table.Columns[j].DataType.Name;
if (strType == "DateTime")
{
strInsertData = strInsertData + "'" + Convert.ToDateTime(feature.DataRow.Table.Rows[k].ItemArray[j].ToString()).ToString("yyyy/MM/dd hh:mm:ss") + "' ";
}
else if (strType == "String")
{
strInsertData = strInsertData + "'" + feature.DataRow.Table.Rows[k].ItemArray[j].ToString() + "' ";
}
else
{
strInsertData = strInsertData + feature.DataRow.Table.Rows[k].ItemArray[j].ToString();
}
}
strInsertData = strInsertData + ",";
}
strInsertData = strInsertData + "'" + feature.BasicGeometry.ToString() + "') ";

SqlCommand scInsert = new SqlCommand();
scInsert.CommandText = strInsertData;
string strInsert = Cls_Datasource.ExecuteSQL(SQLConnection, scInsert);
scInsert.Dispose();
}
}

return "Success";
}
catch (Exception er)
{
return er.Message.ToString();
}
}

參數意義分別為：

ShapeFileName：要匯入的ShapeFile檔案名稱含路徑

EpsgCode：要匯入的ShapeFile的坐標格式

SQLConnection：MS-SQL的連線字串

TableName：匯入SQL的Table名稱

呼叫方式為：

string strResult = cls_Geometry.ImportShapefile(@"D:\大里區太平區.shp", 4326, "Data Source=localhost;Initial Catalog=Test;Persist Security Info=True;User ID=abc;Password=123", "大里區太平區");

實際執行畫面

原始shapeFile圖層