(二)新建表、新增数据、SQL表转DataTable

1.新建表结构

注释：conn指的是MySqlConnection 我之前已经设置和连接好了

        /// <summary>
        /// 创建数据库中的表
        /// </summary>
        /// <param name="tableName">输入表名</param>
        public void AlterTableExample(string tableName)
        {
            string createStatement = "CREATE TABLE " + tableName + "(Id int,Latitude double,Longtitude double,Height double,KiloPos double,Tag text,PointType text)";
            //括号内为所建表中添加的字段
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            // 建表                      
            using (MySqlCommand cmd = new MySqlCommand(createStatement, conn))
            {
                int tmp = cmd.ExecuteNonQuery();
            }
        }

升级版：可以添加中文字符（通过设置字符集为utf8），并且直接判断表是否存在
//如果表设置字符集为utf8则无需去掉中文字符
//Regex reg = new Regex(@”[\u4e00-\u9fa5]”);//去掉中文字符
//p.Tag = reg.Replace(s[4],””).Trim();

/// <summary>
        /// 判断数据库表是否存在,如果不存在创建数据库中的表，存在返回true
        /// </summary>
        /// <param name="tableName">输入表名</param>
        public bool AlterTableExample(string tableName)
        {
            bool flag = false;
            string createStatement = "CREATE TABLE " + tableName + "(Id int auto_increment primary key,Longtitude double,Latitude double,Height double,KiloPos double,Trackid int,Tag text,PointType text)";
            string charset = "engine=myisam default charset=utf8 auto_increment=1";
            createStatement += charset;
            //括号内为所建表中添加的字段
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            // 建表                      
            using (MySqlCommand cmd = new MySqlCommand(createStatement, conn))
            {
                try
                {
                    int tmp = cmd.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    flag = true;
                }
            }
            return flag;
        }

2.插入数据

/// <summary>
        /// 向数据库中插入数据
        /// </summary>
        /// <param name="tableName">输入表名</param>
        public void getInsert(MySqlCommand mySqlCommand)
        {
            try
            {
                mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                String message = ex.Message;
                Console.WriteLine("插入数据失败了！" + message);
            }
        }

3.执行命令语句

/// <summary>
        /// 建立执行命令语句对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="mysql"></param>
        /// <returns></returns>
        public MySqlCommand getSqlCommand(String sql, MySqlConnection mysql)
        {
            MySqlCommand mySqlCommand = new MySqlCommand(sql, mysql);
            return mySqlCommand;
        }

4.使用步骤

//创建表格
AlterTableExample(tableName);
//插入数据的指令
string sqlInsert = "insert into " + TableName + " (Id,Latitude,Longtitude,Height,KiloPos,Tag,PointType) values('" + p.ID + "','" + p.Lat + "','" + p.Lon + "','"+p.Hgt+ "','"+p.KiloPos+ "','"+p.Tag+ "','" +p.pointType.ToString() + "')";
//执行插入的指令
MySqlCommand cmd = ms.getSqlCommand(sqlInsert, mysql);
cmd.ExecuteNonQuery();

5.使用数据库处理海量数据的步骤

https://www.cnblogs.com/520playboy/p/6275233.html

6.删除表

/// <summary>
        /// 删除表
        /// </summary>
        /// <param name="tableName"></param>
        private void DeleteTable(string tableName)
        {
            string deleteCmd = string.Format("drop table {0}", tableName);
            using (MySqlCommand cmd = new MySqlCommand(deleteCmd, conn))
            {
                try
                {
                    int tmp = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }

7.数据库表转DataTable

//MySql的表转DataTable
        public DataTable PullDataTableFromSQL(string tableName)
        {
            if(conn==null||conn.State==ConnectionState.Closed)
            {
                SetConnStr();
                SetSqlConnection(connStr);
            }
            // This will hold the records. 
            DataTable inv = new DataTable();
            // Prep command object.
            string sql = "Select * From "+tableName;
            using (MySqlCommand cmd = new MySqlCommand(sql,conn))
            {
                MySqlDataReader dr = cmd.ExecuteReader();
                // Fill the DataTable with data from the reader and clean up.
                inv.Load(dr);
                dr.Close();
            }
            return inv;
        }