Esempio n. 1
0
        public static void createTable(string dataSource, string tableName)
        {
            using (SQLiteConnection conn = new SQLiteConnection(dataSource))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = conn;
                    conn.Open();
                    SQLiteHelper sh = new SQLiteHelper(cmd);
                    sh.DropTable(tableName);

                    SQLiteTable tb = new SQLiteTable(tableName);
                    tb.Columns.Add(new SQLiteColumn("id", true)); // auto increment
                    tb.Columns.Add(new SQLiteColumn("count"));
                    tb.Columns.Add(new SQLiteColumn("responsetime", ColType.Decimal));
                    sh.CreateTable(tb);
                    conn.Close();
                }
            }
        }
Esempio n. 2
0
 private void createTable3()
 {
     using (var conn = new SQLiteConnection(logDataSource))
     {
         using (var cmd = new SQLiteCommand())
         {
             cmd.Connection = conn;
             conn.Open();
             var sh = new SQLiteHelper(cmd);
             if (!sh.ExistsTable("SfxFiddlerProductLog"))
             {
                 var tb = new SQLiteTable("SfxFiddlerProductLog");
                 tb.Columns.Add(new SQLiteColumn("Id", true));
                 tb.Columns.Add(new SQLiteColumn("EndKeyword", ColType.Text));
                 tb.Columns.Add(new SQLiteColumn("duration", ColType.Integer));
                 tb.Columns.Add(new SQLiteColumn("InsertTime", ColType.DateTime));
                 sh.CreateTable(tb);
                 conn.Close();
             }
         }
     }
 }
Esempio n. 3
0
    /// <summary>
    /// Gets the local item.
    /// </summary>
    /// <returns><c>true</c>, if local item was gotten, <c>false</c> otherwise.</returns>
    /// <param name="sql">Variable sql.</param>
    /// <param name="varOutItems">Variable out items.</param>
    public SQLiteTable GetTable(string sql)
    {
        SQLiteTable table = null;

        if (string.IsNullOrEmpty(sql) || IsOpen() == false)
        {
            return(table);
        }

        SQLiteQuery query = new SQLiteQuery(mDbConnect, sql);

        if (query != null)
        {
            table = new SQLiteTable(query);
            return(table);
        }
        else
        {
            query.Release();
            return(table);
        }
    }
Esempio n. 4
0
        private void button1_Click(object sender, EventArgs e)
        {
            using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = conn;
                    conn.Open();

                    SQLiteHelper sh = new SQLiteHelper(cmd);

                    SQLiteTable tb = new SQLiteTable("AAAA");
                    tb.Columns.Add(new SQLiteColumn("id", true));
                    tb.Columns.Add(new SQLiteColumn("name"));
                    sh.CreateTable(tb);

                    GetTableStatus(sh);

                    conn.Close();
                }
            }
        }
Esempio n. 5
0
        private void CreateTable(string filename)// read file create database table
        {
            using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;

                    SQLiteHelper sh = new SQLiteHelper(cmd);

                    DataTable dt = sh.GetTableList();
                    dt.ToString();
                    SQLiteTable tb = new SQLiteTable(filename);
                    tb.Columns.Add(new SQLiteColumn("CmdID", ColType.Integer, false, true, true, "0"));
                    tb.Columns.Add(new SQLiteColumn("CmdType", ColType.Text, false, false, true, ""));
                    tb.Columns.Add(new SQLiteColumn("CmdValue", ColType.Text, false, false, true, ""));

                    sh.CreateTable(tb);
                    conn.Close();
                }
            }
        }
Esempio n. 6
0
        private void Create_Empty_DataBase_old()
        {
            //SQLiteHelper sqlite = new SQLiteHelper(new SQLiteCommand(config.DataSource));

            //using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
            //{
            //    conn.Open();
            //    SQLiteTable table = new SQLiteTable(config.TableName_Users);
            //    sqlite.CreateTable(table);
            //    conn.Close();
            //}


            using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;

                    SQLiteHelper sqlite           = new SQLiteHelper(cmd);
                    SQLiteTable  table            = new SQLiteTable(config.TableName_Users);
                    SQLiteColumn column_ID        = new SQLiteColumn("ID", ColType.Integer, true, true, true, "");
                    SQLiteColumn column_FirstName = new SQLiteColumn("FirstName", ColType.Text);
                    SQLiteColumn column_LastName  = new SQLiteColumn("LastName", ColType.Text);
                    SQLiteColumn column_Age       = new SQLiteColumn("Age", ColType.Integer);
                    table.Columns.Add(column_ID);
                    table.Columns.Add(column_FirstName);
                    table.Columns.Add(column_LastName);
                    table.Columns.Add(column_Age);

                    sqlite.CreateTable(table);

                    conn.Close();
                }
            }
        }
Esempio n. 7
0
        private static void CreateTable(string tableName)
        {
            try
            {
                // Creating table....
                SQLiteTable tb = new SQLiteTable(tableName);
                tb.Columns.Add(new SQLiteColumn("ID", ColType.Integer, true, true, true, ""));
                tb.Columns.Add(new SQLiteColumn("qh", ColType.Text));
                tb.Columns.Add(new SQLiteColumn("jh", ColType.Text));

                //tb.Columns.Add(new SQLiteColumn("TotalCost", ColType.Integer, false, false, true, "0"));

                //tb.Columns.Add(new SQLiteColumn("LastModiTime", ColType.Text));
                //tb.Columns.Add(new SQLiteColumn("CreateTime", ColType.Text));

                // Execute Table Creation
                using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        conn.Open();
                        cmd.Connection = conn;

                        SQLiteHelper sh = new SQLiteHelper(cmd);

                        sh.DropTable(tableName);
                        sh.CreateTable(tb);

                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
Esempio n. 8
0
        public static void CreateTable()
        {
            var table = new SQLiteTable("DBCONF");
            var cols  = new SQLiteColumnList();

            cols.Add(new SQLiteColumn("id", ColType.Text, true, false, true, ""));
            cols.Add(new SQLiteColumn("dbType"));
            cols.Add(new SQLiteColumn("dbHost"));
            cols.Add(new SQLiteColumn("dbPort"));
            cols.Add(new SQLiteColumn("dbName"));
            cols.Add(new SQLiteColumn("dbUserName"));
            cols.Add(new SQLiteColumn("dbPassword"));
            cols.Add(new SQLiteColumn("updateDate", ColType.DateTime));
            table.SetCols(cols);
            db.CreateTable(table);

            table = new SQLiteTable("PATHCONF");
            cols  = new SQLiteColumnList();
            cols.Add(new SQLiteColumn("code"));
            cols.Add(new SQLiteColumn("name"));
            cols.Add(new SQLiteColumn("value"));
            table.SetCols(cols);
            db.CreateTable(table);
        }
Esempio n. 9
0
    private void Register(IDataTable data)
    {
        if (data == null)
        {
            return;
        }

        if (mDataTables.ContainsKey(data.name) == false)
        {
            string sql = string.Format("select * from {0}", data.name.ToString());

            SQLiteTable table = SQLite.Instance.GetTable(sql);
            if (table != null)
            {
                mDataTables.Add(data.name, data);
                data.Read(table);
                table.Close();
            }
            else
            {
                Debug.LogError("Can't find table:" + data.name);
            }
        }
    }
Esempio n. 10
0
        private void CreateNewDataBase()
        {
            if (File.Exists(DatabaseFile))
            {
                File.Delete(DatabaseFile);
            }
            SQLiteConnection.CreateFile(DatabaseFile);
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + DatabaseFile))
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    command.Connection = connection;
                    connection.Open();
                    SQLiteHelper helper = new SQLiteHelper(command);

                    SQLiteTable tb = new SQLiteTable("RssList");
                    tb.Columns.Add(new SQLiteColumn("Name", ColType.Text));
                    tb.Columns.Add(new SQLiteColumn("Url", ColType.Text));
                    tb.Columns.Add(new SQLiteColumn("UpdateTime", ColType.DateTime));
                    tb.Columns.Add(new SQLiteColumn("Md5", ColType.Text, true, false, true, null));
                    tb.Columns.Add(new SQLiteColumn("Selected", ColType.Text));
                    helper.CreateTable(tb);

                    SQLiteTable tb2 = new SQLiteTable("DownloadedList");
                    tb2.Columns.Add(new SQLiteColumn("RssUrl", ColType.Text));
                    tb2.Columns.Add(new SQLiteColumn("Title", ColType.Text));
                    tb2.Columns.Add(new SQLiteColumn("UpdateTime", ColType.DateTime));
                    tb2.Columns.Add(new SQLiteColumn("MagnetLink", ColType.Text));
                    tb2.Columns.Add(new SQLiteColumn("Guid"));
                    tb2.Columns.Add(new SQLiteColumn("Md5", ColType.Text, true, false, true, null));
                    helper.CreateTable(tb2);

                    connection.Close();
                }
            }
        }
Esempio n. 11
0
        private void button1_Click(object sender, EventArgs e)
        {
            #region 判断IP的合法性
            foreach (Control c in this.Controls)
            {
                if (c is TextBox)
                {
                    if (string.IsNullOrEmpty((c as TextBox).Text))
                    {
                        MessageBox.Show("有设置选项未填写。", "错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                }
            }
            foreach (Control c in this.panel1.Controls)
            {
                if (c is TextBox)
                {
                    if (string.IsNullOrEmpty((c as TextBox).Text))
                    {
                        MessageBox.Show("COS服务器IP未填写。", "错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    else if (Convert.ToInt32(c.Text) >= 256)
                    {
                        MessageBox.Show("请输入正确的IP地址。", "错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                }
            }


            #endregion
            #region  务程序数据库操作
            string ip       = textBox1.Text + "." + textBox2.Text + "." + textBox3.Text + "." + textBox4.Text;
            int    interval = Convert.ToInt32(textBox5.Text);
            int    timeout  = Convert.ToInt32(textBox6.Text);
            using (SQLiteConnection conn = new SQLiteConnection(@"data source=.\CloudAgent.db"))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = conn;
                    conn.Open();
                    SQLiteHelper sh = new SQLiteHelper(cmd);
                    SQLiteTable  tb = new SQLiteTable("server");
                    tb.Columns.Add(new SQLiteColumn("id", true));
                    tb.Columns.Add(new SQLiteColumn("ip", ColType.Text));
                    sh.CreateTable(tb);
                    SQLiteTable tb2 = new SQLiteTable("setting");
                    tb2.Columns.Add(new SQLiteColumn("id", true));
                    tb2.Columns.Add(new SQLiteColumn("key", ColType.Text));
                    tb2.Columns.Add(new SQLiteColumn("value", ColType.Integer));

                    sh.CreateTable(tb2);
                    sh.BeginTransaction();
                    try
                    {
                        var dic = new Dictionary <string, object>();
                        dic["ip"] = ip;
                        sh.Insert("server", dic);
                        var dicData  = new Dictionary <string, object>();
                        var dicData2 = new Dictionary <string, object>();
                        var dicData3 = new Dictionary <string, object>();
                        dicData["key"]    = "interval";
                        dicData["value"]  = interval;
                        dicData2["key"]   = "timeout";
                        dicData2["value"] = timeout;
                        dicData3["key"]   = "hostset";
                        dicData3["value"] = "";
                        DataTable dt = sh.Select("select * from setting where key='interval';");
                        if (dt.Rows.Count > 0)
                        {
                            sh.Update("setting", dicData, "key", "interval");
                        }
                        else
                        {
                            sh.Insert("setting", dicData);
                        }
                        DataTable dt2 = sh.Select("select * from setting where key='timeout';");
                        if (dt2.Rows.Count > 0)
                        {
                            sh.Update("setting", dicData2, "key", "timeout");
                        }
                        else
                        {
                            sh.Insert("setting", dicData2);
                        }
                        DataTable dt3 = sh.Select("select * from setting where key='hostset';");
                        if (dt3.Rows.Count == 0)
                        {
                            sh.Insert("setting", dicData3);
                        }
                        sh.Commit();
                    }
                    catch
                    {
                        sh.Rollback();
                    }


                    conn.Close();
                }
            }

            #endregion
            #region 是否打开服务

            if (radioButton1.Checked)
            {
                Thread ce = new Thread(delegate()
                {
                    Process p   = new Process();
                    string path = System.Environment.CurrentDirectory;
                    p.StartInfo.UseShellExecute = false;
                    p.StartInfo.FileName        = path + @"\CloudAgent.exe";
                    //MessageBox.Show(p.StartInfo.FileName);
                    p.StartInfo.CreateNoWindow         = true;
                    p.StartInfo.RedirectStandardOutput = true;
                    p.EnableRaisingEvents = true;
                    p.StartInfo.Arguments = "-install";
                    try
                    {
                        // MessageBox.Show(radioButton1.Checked.ToString());
                        p.Start();

                        p.WaitForExit();
                    }
                    catch (System.ComponentModel.Win32Exception err)
                    {
                        MessageBox.Show("系统找不到指定的程序文件。\r{2}");
                        p.Close();
                        return;
                    }

                    p.Close();
                });
                ce.IsBackground = false;
                ce.Start();
                Thread ce2 = new Thread(delegate()
                {
                    Process p   = new Process();
                    string path = System.Environment.CurrentDirectory;
                    p.StartInfo.UseShellExecute = false;
                    p.StartInfo.FileName        = path + @"\AutoOnOffLine.exe";
                    //MessageBox.Show(p.StartInfo.FileName);
                    p.StartInfo.CreateNoWindow         = true;
                    p.StartInfo.RedirectStandardOutput = true;
                    p.EnableRaisingEvents = true;
                    p.StartInfo.Arguments = "-install";
                    try
                    {
                        // MessageBox.Show(radioButton1.Checked.ToString());
                        p.Start();

                        p.WaitForExit();
                    }
                    catch (System.ComponentModel.Win32Exception err)
                    {
                        MessageBox.Show("系统找不到指定的程序文件。\r{2}");
                        p.Close();
                        return;
                    }

                    p.Close();
                });
                ce2.IsBackground = false;
                ce2.Start();
            }
            if (radioButton2.Checked)
            {
                Thread ce = new Thread(delegate()
                {
                    Process p   = new Process();
                    string path = System.Environment.CurrentDirectory;
                    p.StartInfo.UseShellExecute        = false;
                    p.StartInfo.FileName               = path + @"\CloudAgent.exe";
                    p.StartInfo.CreateNoWindow         = true;
                    p.StartInfo.RedirectStandardOutput = true;
                    p.EnableRaisingEvents              = true;
                    p.StartInfo.Arguments              = "-remove";
                    try
                    {
                        p.Start();
                        p.WaitForExit();
                    }
                    catch (System.ComponentModel.Win32Exception err)
                    {
                        MessageBox.Show("系统找不到指定的程序文件。\r{2}");
                        p.Close();
                        return;
                    }

                    p.Close();
                });
                ce.IsBackground = false;
                ce.Start();
                Thread ce2 = new Thread(delegate()
                {
                    Process p   = new Process();
                    string path = System.Environment.CurrentDirectory;
                    p.StartInfo.UseShellExecute        = false;
                    p.StartInfo.FileName               = path + @"\AutoOnOffLine.exe";
                    p.StartInfo.CreateNoWindow         = true;
                    p.StartInfo.RedirectStandardOutput = true;
                    p.EnableRaisingEvents              = true;
                    p.StartInfo.Arguments              = "-remove";
                    try
                    {
                        p.Start();
                        p.WaitForExit();
                    }
                    catch (System.ComponentModel.Win32Exception err)
                    {
                        MessageBox.Show("系统找不到指定的程序文件。\r{2}");
                        p.Close();
                        return;
                    }

                    p.Close();
                });
                ce2.IsBackground = false;
                ce2.Start();
            }
            #endregion
            /* Environment.Exit(1);*/
            Application.Exit();
        }
Esempio n. 12
0
        private void btDropCreate_Click(object sender, EventArgs e)
        {
            try
            {
                // Check if the table name is blank
                if (textBox1.Text.Trim().Length == 0)
                {
                    MessageBox.Show("Table Name cannot be blank.");
                    return;
                }

                // Check if all the columns' name is filled
                foreach (Control c in flowLayoutPanel1.Controls)
                {
                    if (c is Column)
                    {
                        Column col = (Column)c;

                        if (col.ColumnName.Trim().Length == 0)
                        {
                            MessageBox.Show("Some column's name is blank. Cannot create table.");
                            return;
                        }
                    }
                }

                // Creating table....
                SQLiteTable tb = new SQLiteTable(textBox1.Text);

                foreach (Control c in flowLayoutPanel1.Controls)
                {
                    if (c is Column)
                    {
                        Column col = (Column)c;

                        tb.Columns.Add(new SQLiteColumn(col.ColumnName, col.ColumnType, col.PrimaryKey, col.AutoIncrement, col.NotNUll, col.DefaultValue));
                    }
                }

                // Execute Table Creation
                using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        conn.Open();
                        cmd.Connection = conn;

                        SQLiteHelper sh = new SQLiteHelper(cmd);

                        sh.DropTable(textBox1.Text);
                        sh.CreateTable(tb);

                        conn.Close();
                    }
                }

                MessageBox.Show("Table created.");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
Esempio n. 13
0
    static void ExportDataTable(string tableName, string fileName, string className)
    {
        if (string.IsNullOrEmpty(tableName))
        {
            return;
        }

        if (SQLite.Instance.IsOpen() == false)
        {
            if (SQLite.Instance.Open(database) == false)
            {
                Debug.LogError("无法打开数据库:" + database);
                return;
            }
        }
        SQLiteTable info = SQLite.Instance.GetTableInfo(tableName);

        if (info == null)
        {
            Debug.LogError("无法读取表" + tableName + "信息");
            return;
        }

        string path       = string.Format("{0}/Scripts/Data/Tables/{1}.cs", Application.dataPath, fileName);
        string definition = "";
        string read       = string.Format("\t{0} o = new {1}();\n", className, className);
        string keyName    = null;
        string keyType    = null;

        while (info.Read())
        {
            string columnName = info.GetByColumnName("name", "");
            string columnType = GetType(info.GetByColumnName("type", ""));

            if (string.IsNullOrEmpty(keyName))
            {
                keyName = columnName;
            }
            if (string.IsNullOrEmpty(keyType))
            {
                keyType = columnType;
            }

            definition += string.Format("\tpublic {0} {1};\n", columnType, columnName);
            read       += string.Format("\t\t\to.{0} = table.GetByColumnName(\"{1}\", {2});\n", columnName, columnName, GetDefaultValue(columnType));
        }

        read += string.Format("\t\t\tdic.Add(o.{0},o);\n\t\t\tlist.Add(o);", keyName);

        info.Close();


        if (File.Exists(path) == false)
        {
            string code = template.Replace("{filename}", fileName)
                          .Replace("{definition}", definition)
                          .Replace("{classname}", className)
                          .Replace("{tablename}", tableName)
                          .Replace("{read}", read)
                          .Replace("{key}", keyType);

            Debug.Log(code);

            FileEx.SaveFile(path, code);
        }
        else
        {
            string datatable = File.ReadAllText(path);
            datatable = datatable.ReplaceEx("//TABLE_DEFINITION_BEGIN", "//TABLE_DEFINITION_END", definition);
            datatable = datatable.ReplaceEx("//TABLE_READ_BEGIN", "//TABLE_READ_END", "\t\t" + read + "\n");

            FileEx.SaveFile(path, datatable);
        }

        Debug.Log("成功导出数据表:" + tableName);
    }
Esempio n. 14
0
        private static void CreateSpatialTable()
        {
            try
            {
                #region 创建点层
                SQLiteTable table = new SQLiteTable();
                table.Name = "PointT";
                SQLiteColumn col = new SQLiteColumn();
                col.AutoIncrement = true;
                col.DataType      = FieldType.Integer;
                col.Name          = "PK_UID";
                col.NotNull       = true;
                table.Columns.Add(col);

                col          = new SQLiteColumn();
                col.DataType = FieldType.Text;
                col.Name     = "name";
                table.Columns.Add(col);
                sh.CreateTable(table);
                string sql = "CREATE TABLE PointT (PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT)";
                //sh.Execute(sql);

                /*  添加图形列 AddGeometryColumn('你的表名', '你的几何列名',4326, 'POINT','XY')  XY为二维数据*/
                sql = "SELECT AddGeometryColumn('PointT', 'GEOMETRY',2346, 'POINT','XY')"; //2346 为EPSG码
                sh.Execute(sql);

                /* 添加空间索引 */
                sql = "SELECT CreateSpatialIndex('PointT', 'GEOMETRY')";
                sh.Execute(sql);

                /*  通过OGC标准的WTK 文件描述格式插入一个点记录*/
                sql = "INSERT INTO PointT(PK_UID, name, GEOMETRY) "
                      + "VALUES (1, 'first point',GeomFromText('POINT(636000 5209340)',2346))";
                sh.Execute(sql);
                /*  通过OGC标准的WTK 文件描述格式插入一个点记录*/
                sql = "INSERT INTO PointT(GEOMETRY, name, PK_UID) "
                      + "VALUES (GeomFromText('POINT(636100 5209340)',2346),'eleventh point', 2)";
                sh.Execute(sql);

                #endregion


                #region 创建线层

                sql = "CREATE TABLE PolylineT(PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL)";
                sh.Execute(sql);

                //bRet = ExecuteSql(sql, conn);
                /*  添加图形列 */
                sql = "SELECT AddGeometryColumn('PolylineT', 'GEOMETRY',2346, 'LINESTRING', 2)"; //2346 为EPSG码
                sh.Execute(sql);

                /* 添加空间索引 */
                sql = "SELECT CreateSpatialIndex('PolylineT', 'GEOMETRY')";
                sh.Execute(sql);

                /*  通过OGC标准的WTK 文件描述格式插入一个点记录*/
                sql = "INSERT INTO PolylineT(PK_UID, name, GEOMETRY) "
                      + "VALUES (1, 'line1', GeomFromText('LINESTRING(636000 5209340,633950 5212200,634400 5207800)',2346))";
                sh.Execute(sql);

                /*  通过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 PolygonT (PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL)";
                sh.Execute(sql);

                //bRet = ExecuteSql(sql, conn);
                /*  添加图形列 */
                sql = "SELECT AddGeometryColumn('PolygonT', 'GEOMETRY',2346, 'POLYGON', 2)"; //2346 为EPSG码
                sh.Execute(sql);

                /* 添加空间索引 */
                sql = "SELECT CreateSpatialIndex('PolygonT', 'GEOMETRY')";
                sh.Execute(sql);

                /*  通过OGC标准的WTK 文件描述格式插入一个点记录*/
                sql = "INSERT INTO PolygonT(PK_UID, name, GEOMETRY) "
                      + "VALUES (1, 'Poly1',GeomFromText('POLYGON((636000 5209340,633950 5212200,634400 5207800,632409 5209760,636000 5209340))',2346))";
                sh.Execute(sql);

                /*  通过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();

                Console.WriteLine("按任意键返回.");
                Console.ReadKey();
                DisplayMenu();

                #endregion
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("#错误:" + ex.Message);
                Console.ResetColor();

                Console.WriteLine("按任意键返回.");
                Console.ReadKey();
                DisplayMenu();
            }
        }
Esempio n. 15
0
 /// <summary>
 /// DisCover all the DataBase Columns and put them in Table object, return <see cref="IEnumerable{T}"/> contain all Columns
 /// </summary>
 /// <param name="table">the Table object</param>
 public static IEnumerable <SQLiteTableColumn> TableColumns(SQLiteTable table) =>
 table.GetObjectArray2D(table.ExecuteReaderQuery(table.Query.GetTableColumns))
 .Select(column => new SQLiteTableColumn(column[1].ToString(), column[2].ToString()));
Esempio n. 16
0
        public void CreateTable(SQLiteTable table)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("create table if not exists `");
            sb.Append(table.TableName);
            sb.AppendLine("`(");

            bool firstRecord = true;

            foreach (SQLiteColumn col in table.Columns)
            {
                if (col.ColumnName.Trim().Length == 0)
                {
                    throw new Exception("Column name cannot be blank.");
                }

                if (firstRecord)
                {
                    firstRecord = false;
                }
                else
                {
                    sb.AppendLine(",");
                }

                sb.Append(col.ColumnName);
                sb.Append(" ");

                if (col.AutoIncrement)
                {
                    sb.Append("integer primary key autoincrement");
                    continue;
                }

                switch (col.ColDataType)
                {
                case ColType.Text:
                    sb.Append("text"); break;

                case ColType.Integer:
                    sb.Append("integer"); break;

                case ColType.Decimal:
                    sb.Append("decimal"); break;

                case ColType.DateTime:
                    sb.Append("datetime"); break;

                case ColType.BLOB:
                    sb.Append("blob"); break;
                }

                if (col.PrimaryKey)
                {
                    sb.Append(" primary key");
                }
                else if (col.NotNull)
                {
                    sb.Append(" not null");
                }
                else if (col.DefaultValue.Length > 0)
                {
                    sb.Append(" default ");

                    if (col.DefaultValue.Contains(" ") || col.ColDataType == ColType.Text || col.ColDataType == ColType.DateTime)
                    {
                        sb.Append("'");
                        sb.Append(col.DefaultValue);
                        sb.Append("'");
                    }
                    else
                    {
                        sb.Append(col.DefaultValue);
                    }
                }
            }

            sb.AppendLine(");");

            cmd.CommandText = sb.ToString();
            cmd.ExecuteNonQuery();
        }
Esempio n. 17
0
 /// <summary>
 /// initialize new instance of <see cref="ObjectReader"/> withe Table
 /// </summary>
 /// <param name="table">the Table That Contain the Columns that comes with the object</param>
 public ObjectReader(SQLiteTable table) => Table = table;
Esempio n. 18
0
        public static void init()
        {
            using (SQLiteConnection conn = new SQLiteConnection(config.DB_FILE))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = conn;
                    conn.Open();
                    SQLiteHelper sh = new SQLiteHelper(cmd);

                    //系统信息表
                    SQLiteTable SystemConfig = new SQLiteTable("SystemConfig");
                    SystemConfig.Columns.Add(new SQLiteColumn("configid", ColType.Text));
                    SystemConfig.Columns.Add(new SQLiteColumn("value", ColType.Text));
                    SystemConfig.Columns.Add(new SQLiteColumn("remark", ColType.Text));

                    //收银员表
                    SQLiteTable cashier = new SQLiteTable("Cashier");
                    cashier.Columns.Add(new SQLiteColumn("id", ColType.Text));
                    cashier.Columns.Add(new SQLiteColumn("name", ColType.Text));
                    cashier.Columns.Add(new SQLiteColumn("code", ColType.Text));
                    cashier.Columns.Add(new SQLiteColumn("pwd", ColType.Text));

                    //键盘配置表
                    SQLiteTable keyboard = new SQLiteTable("KeyBoard");
                    keyboard.Columns.Add(new SQLiteColumn("commandkey", ColType.Text));
                    keyboard.Columns.Add(new SQLiteColumn("commandname", ColType.Text));
                    keyboard.Columns.Add(new SQLiteColumn("keycode", ColType.Text));

                    //支付方式表
                    SQLiteTable paytype = new SQLiteTable("PayType");
                    paytype.Columns.Add(new SQLiteColumn("code", ColType.Text));
                    paytype.Columns.Add(new SQLiteColumn("name", ColType.Text));
                    paytype.Columns.Add(new SQLiteColumn("isenable", ColType.Text));
                    paytype.Columns.Add(new SQLiteColumn("isearn", ColType.Text));

                    //售卖单据流水号
                    SQLiteTable sequence = new SQLiteTable("OrderSequence");
                    sequence.Columns.Add(new SQLiteColumn("date", ColType.DateTime));
                    sequence.Columns.Add(new SQLiteColumn("seq", ColType.Integer));

                    SQLiteTable pos = new SQLiteTable("PosConfig");
                    pos.Columns.Add(new SQLiteColumn("id", ColType.Text));
                    pos.Columns.Add(new SQLiteColumn("posname", ColType.Text));
                    pos.Columns.Add(new SQLiteColumn("poscode", ColType.Text));
                    pos.Columns.Add(new SQLiteColumn("shopcode", ColType.Text));
                    pos.Columns.Add(new SQLiteColumn("posid", ColType.Text));
                    pos.Columns.Add(new SQLiteColumn("isenable", ColType.Text));
                    pos.Columns.Add(new SQLiteColumn("shopname", ColType.Text));
                    pos.Columns.Add(new SQLiteColumn("initcode", ColType.Text));
                    pos.Columns.Add(new SQLiteColumn("createdate", ColType.Text));
                    pos.Columns.Add(new SQLiteColumn("lastping", ColType.Text));
                    pos.Columns.Add(new SQLiteColumn("lastsync", ColType.Text));
                    pos.Columns.Add(new SQLiteColumn("ipaddr", ColType.Text));


                    //商品档案表
                    SQLiteTable product = new SQLiteTable("Product");
                    product.Columns.Add(new SQLiteColumn("id", ColType.Integer, true, false, true, ""));
                    product.Columns.Add(new SQLiteColumn("barcode", ColType.Text));
                    product.Columns.Add(new SQLiteColumn("name", ColType.Text));
                    product.Columns.Add(new SQLiteColumn("spec", ColType.Text));
                    product.Columns.Add(new SQLiteColumn("unit", ColType.Text));
                    product.Columns.Add(new SQLiteColumn("tintype", ColType.Text));
                    product.Columns.Add(new SQLiteColumn("midtype", ColType.Text));
                    product.Columns.Add(new SQLiteColumn("bigtype", ColType.Text));
                    product.Columns.Add(new SQLiteColumn("classtype", ColType.Text));
                    product.Columns.Add(new SQLiteColumn("depttype", ColType.Text));
                    product.Columns.Add(new SQLiteColumn("price", ColType.Decimal));

                    //售卖单据
                    SQLiteTable saleorder = new SQLiteTable("SaleOrder");
                    saleorder.Columns.Add(new SQLiteColumn("id", ColType.Text, true, false, true, null));
                    saleorder.Columns.Add(new SQLiteColumn("ordercode"));
                    saleorder.Columns.Add(new SQLiteColumn("shopcode"));
                    saleorder.Columns.Add(new SQLiteColumn("poscode"));
                    saleorder.Columns.Add(new SQLiteColumn("cashier"));
                    saleorder.Columns.Add(new SQLiteColumn("amount"));
                    saleorder.Columns.Add(new SQLiteColumn("count"));
                    saleorder.Columns.Add(new SQLiteColumn("disamount"));
                    saleorder.Columns.Add(new SQLiteColumn("createdate"));
                    saleorder.Columns.Add(new SQLiteColumn("updatedate"));
                    saleorder.Columns.Add(new SQLiteColumn("state"));

                    //售卖明细
                    SQLiteTable saleorderList = new SQLiteTable("SaleOrderList");
                    saleorderList.Columns.Add(new SQLiteColumn("id", ColType.Text, true, false, true, null));
                    saleorderList.Columns.Add(new SQLiteColumn("ordercode"));
                    saleorderList.Columns.Add(new SQLiteColumn("orderid"));
                    saleorderList.Columns.Add(new SQLiteColumn("productid"));
                    saleorderList.Columns.Add(new SQLiteColumn("barcode"));
                    saleorderList.Columns.Add(new SQLiteColumn("name"));
                    saleorderList.Columns.Add(new SQLiteColumn("spec"));
                    saleorderList.Columns.Add(new SQLiteColumn("unit"));
                    saleorderList.Columns.Add(new SQLiteColumn("tintype"));
                    saleorderList.Columns.Add(new SQLiteColumn("midtype"));
                    saleorderList.Columns.Add(new SQLiteColumn("bigtype"));
                    saleorderList.Columns.Add(new SQLiteColumn("classtype"));
                    saleorderList.Columns.Add(new SQLiteColumn("depttype"));
                    saleorderList.Columns.Add(new SQLiteColumn("price"));
                    saleorderList.Columns.Add(new SQLiteColumn("count"));
                    saleorderList.Columns.Add(new SQLiteColumn("discount"));
                    saleorderList.Columns.Add(new SQLiteColumn("disprice"));
                    saleorderList.Columns.Add(new SQLiteColumn("amount"));
                    saleorderList.Columns.Add(new SQLiteColumn("disamount"));

                    //收银流水表
                    SQLiteTable account = new SQLiteTable("Account");
                    account.Columns.Add(new SQLiteColumn("id", ColType.Text, true, false, true, null));
                    account.Columns.Add(new SQLiteColumn("ordercode", ColType.Text));
                    account.Columns.Add(new SQLiteColumn("orderid", ColType.Text));
                    account.Columns.Add(new SQLiteColumn("sum", ColType.Text));
                    account.Columns.Add(new SQLiteColumn("type", ColType.Text));
                    account.Columns.Add(new SQLiteColumn("date", ColType.DateTime));



                    sh.BeginTransaction();
                    try
                    {
                        sh.CreateTable(SystemConfig);
                        sh.CreateTable(cashier);
                        sh.CreateTable(keyboard);
                        sh.CreateTable(paytype);
                        sh.CreateTable(sequence);
                        sh.CreateTable(pos);
                        sh.CreateTable(product);
                        sh.CreateTable(saleorder);
                        sh.CreateTable(saleorderList);
                        sh.CreateTable(account);
                        sh.Commit();
                    }
                    catch (Exception e)
                    {
                        System.Console.WriteLine(e);
                        sh.Rollback();
                    }
                    finally
                    {
                        conn.Close();
                    }
                    initData();
                }
            }
        }
Esempio n. 19
0
 /// <summary>
 /// create new instance of <see cref="SQLiteDataBase" /> initialized to Work with specific Table
 /// </summary>
 /// <param name="table">the Table Name that will Make Queries for it</param>
 public SQLiteQuery(SQLiteTable table)
 {
     Table        = table ?? throw new ArgumentException("message", nameof(table));
     ObjectReader = new ObjectReader(Table);
 }