DotSpatial_建立Spatialite空間資料庫

//坐标系                        EPSG碼
        //xian 1980 3 Degree GK CM 123  2386
        //xian 1980 3 Degree GK CM 126  2387
        //xian 1980 3 Degree GK CM 129  2388
        //xian 1980 3 Degree GK CM 132    2389
        //xian 1980 3 Degree GK CM 135    2390
        //xian 1980 3 Degree GK Zone 41    2365
        //xian 1980 3 Degree GK Zone 42    2366
        //xian 1980 3 Degree GK Zone 43    2367
        //xian 1980 3 Degree GK Zone 44    2368
        //xian 1980 3 Degree GK Zone 45    2369
        //xian 1980 GK CM 123            2346
        //xian 1980 GK CM 129            2347
        //xian 1980 GK CM 135            2348
        //xian 1980 GK Zone 21            2335
        //xian 1980 GK Zone 22            2336
        //xian 1980 GK Zone 23            2337
        //CGCS2000 3 Degree GK CM 123    4550
        //CGCS2000 3 Degree GK CM 126    4551
        //CGCS2000 3 Degree GK CM 129    4552
        //CGCS2000 3 Degree GK CM 132    4553
        //CGCS2000 3 Degree GK CM 135    4554
        //CGCS2000 3 Degree GK Zone 41    4529
        //CGCS2000 3 Degree GK Zone 42    4530
        //CGCS2000 3 Degree GK Zone 43    4531
        //CGCS2000 3 Degree GK Zone 44    4532
        //CGCS2000 3 Degree GK Zone 45    4533
        //CGCS2000 GK CM 123            4510
        //CGCS2000 GK CM 126            4511
        //CGCS2000 GK CM 129            4512
        //CGCS2000 GK Zone 21            4499
        //CGCS2000 GK Zone 22            4500
        //CGCS2000 GK Zone 23            4501

        //建立Spatialite資料庫庫
        private void toolStripButtonCreateSpatialiteDB_Click(object sender, EventArgs e)
        {
            string dbFile = Application.StartupPath + "\\Data\\Test.sqlite";
            string dbConnStr = "Data Source=" + dbFile;
            //建立庫
            //SQLiteConnection.CreateFile(dbFile);


            //增加圖層
            string msg = "";

            //建立表

            using (SQLiteConnection conn = new SQLiteConnection(dbConnStr))
            {
                conn.Open();
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    string sql = "";


                    conn.LoadExtension("mod_spatialite.dll");
                    //sql = "SELECT load_extension('mod_spatialite.dll');";


                    sql = "select InitSpatialMetaData()";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    #region 建立點層

                    sql = "CREATE TABLE    Dian (PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,meas_value DOUBLE NOT NULL)";
                    cmd.CommandText = sql;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch(Exception exc)
                    {                        
                    }

                  //bRet = ExecuteSql(sql, conn);
                    /*  添加圖形列 */
                    sql = "SELECT AddGeometryColumn('Dian', 'GEOMETRY',2346, 'POINT', 'XY')"; //2346 為EPSG碼
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    /* 添加空間索引 */
                    sql = "SELECT CreateSpatialIndex('Dian', 'GEOMETRY')";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    /*  通過OGC标準的WTK 檔案描述格式插入一個點記錄*/
                    sql = "INSERT INTO Dian(PK_UID, name, meas_value, GEOMETRY) VALUES (1, 'first point', 1.23456,GeomFromText('POINT(636000 5209340)',2346))";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    /*  通過OGC标準的WTK 檔案描述格式插入一個點記錄*/

                    sql = "INSERT INTO Dian(GEOMETRY, meas_value, name, PK_UID) VALUES (GeomFromText('POINT(636100 5209340)',2346),11.123456789, 'eleventh point', 2)";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    #endregion


                    #region 建立線層

                    sql = "CREATE TABLE    Xian (PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,meas_value DOUBLE NOT NULL)";
                    cmd.CommandText = sql;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception exc)
                    {
                    }

                    //bRet = ExecuteSql(sql, conn);
                    /*  添加圖形列 */
                    sql = "SELECT AddGeometryColumn('Xian', 'GEOMETRY',2346, 'LINESTRING', 2)"; //2346 為EPSG碼
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    /* 添加空間索引 */
                    sql = "SELECT CreateSpatialIndex('Xian', 'GEOMETRY')";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    /*  通過OGC标準的WTK 檔案描述格式插入一個點記錄*/
                    sql = "INSERT INTO Xian(PK_UID, name, meas_value, GEOMETRY) VALUES (1, 'line1', 1.23456,GeomFromText('LINESTRING(636000 5209340,633950 5212200,634400 5207800)',2346))";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    /*  通過OGC标準的WTK 檔案描述格式插入一個點記錄*/

                    //sql = "INSERT INTO Xian(GEOMETRY, meas_value, name, PK_UID) VALUES (GeomFromText('POINT(636100 5209340)',2346),11.123456789, 'eleventh point', 2)";
                    //cmd.CommandText = sql;
                    //cmd.ExecuteNonQuery();

                    #endregion

                    #region 建立面層

                    sql = "CREATE TABLE    Mian (PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,meas_value DOUBLE NOT NULL)";
                    cmd.CommandText = sql;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception exc)
                    {
                    }

                    //bRet = ExecuteSql(sql, conn);
                    /*  添加圖形列 */
                    sql = "SELECT AddGeometryColumn('Mian', 'GEOMETRY',2346, 'POLYGON', 2)"; //2346 為EPSG碼
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    /* 添加空間索引 */
                    sql = "SELECT CreateSpatialIndex('Mian', 'GEOMETRY')";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    /*  通過OGC标準的WTK 檔案描述格式插入一個點記錄*/
                    sql = "INSERT INTO Mian(PK_UID, name, meas_value, GEOMETRY) VALUES (1, 'Poly1', 1.23456,GeomFromText('POLYGON((636000 5209340,633950 5212200,634400 5207800,632409 5209760,636000 5209340))',2346))";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    /*  通過OGC标準的WTK 檔案描述格式插入一個點記錄*/

                    //sql = "INSERT INTO Xian(GEOMETRY, meas_value, name, PK_UID) VALUES (GeomFromText('POINT(636100 5209340)',2346),11.123456789, 'eleventh point', 2)";
                    //cmd.CommandText = sql;
                    //cmd.ExecuteNonQuery();

                    #endregion

                }
            }

            //打開或建立Spatialite資料庫
            //DotSpatial.Plugins.SpatiaLite.SpatiaLiteHelper spatialiteHelper = DotSpatial.Plugins.SpatiaLite.SpatiaLiteHelper.Open(dbConnStr, out msg);
            
            //SpatiaLiteHelper.SetEnvironmentVars();
            //List<string> listTableNames = spatialiteHelper.GetTableNames();
            //foreach (string tableName in listTableNames)
            //{
            //    MessageBox.Show(tableName);
            //}

            //IFeatureSet fs =  spatialiteHelper.ReadFeatureSet("t");
            //mapMain.Layers.Add(fs);



            //sql = "insert into TableName values(2, 0, MakePoint(-111.879206, 226.255935, 4326))";


        }