Ejemplo n.º 1
0
        public Tuple <string, bool> RunNonQueSql(string sql, string back_name, bool del)
        {
            string            except     = "";
            var               nback_name = CopyBack("tmp", back_name);
            KingbaseESCommand cmd        = new KingbaseESCommand("SET search_path TO " + '"' + nback_name + '"' + ",public", conn);

            cmd.ExecuteNonQuery();
            cmd.CommandText = sql;
            bool flag = true;

            try {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) {
                flag   = false;
                except = e.Message;
            }
            var t = new Tuple <string, bool>(except, flag);

            if (del)
            {
                cmd.CommandText = $"DROP SCHEMA \"{nback_name}\" CASCADE";
                cmd.ExecuteNonQuery();
            }
            return(t);
        }
Ejemplo n.º 2
0
        public string ClearTable(string back_name, int table_num)
        {
            string               sql = "select table_name from information_schema.tables where table_schema='" + back_name + "'";
            KingbaseESCommand    cmd = new KingbaseESCommand(sql, conn);
            KingbaseESDataReader dr  = null;

            dr = cmd.ExecuteReader();
            var tables = new List <string>();

            while (dr.Read())
            {
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    tables.Add((string)dr.GetValue(i));
                }
            }
            dr.Close();
            back_name = '"' + back_name + '"';
            string table_name = $"{back_name}.{tables[table_num]}";

            sql = $"delete from {table_name}";
            string except = "";

            try{ cmd.ExecuteNonQuery(); }
            catch (System.Exception e) {
                except = e.Message;
            }
            return(except);
        }
Ejemplo n.º 3
0
        public string DeleteBack(string back_name)
        {
            KingbaseESCommand cmd = new KingbaseESCommand($"DROP SCHEMA \"{back_name}\" CASCADE", conn);

            try { cmd.ExecuteNonQuery(); }
            catch (Exception e) {
                return(e.Message);
            }
            return("");
        }
Ejemplo n.º 4
0
        public List <string> GetSchemaInfo(string back_name)
        {
            string               sql = "select table_name from information_schema.tables where table_schema='" + back_name + "'";
            KingbaseESCommand    cmd = new KingbaseESCommand(sql, conn);
            KingbaseESDataReader dr  = null;

            dr = cmd.ExecuteReader();
            var tables = new List <string>();

            while (dr.Read())
            {
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    tables.Add((string)dr.GetValue(i));
                }
            }
            dr.Close();
            return(tables);
        }
Ejemplo n.º 5
0
        public string CreateBackGround(background back)
        {
            var result = "";

            string[] sqls = back.sql.Split(';');
            try {
                KingbaseESCommand cmd = new KingbaseESCommand("CREATE SCHEMA " + '"' + back.name.ToUpper() + '"' + " " + sqls[0], conn);
                var res1 = cmd.ExecuteNonQuery();
                cmd.CommandText = "SET search_path TO " + '"' + back.name + '"' + ",public";
                cmd.ExecuteNonQuery();
                for (int i = 0; i < sqls.Count(); i++)
                {
                    cmd.CommandText = sqls[i];
                    cmd.ExecuteNonQuery();
                }
            }catch (Exception e) {
                result = e.Message;
            }
            return(result);
        }
Ejemplo n.º 6
0
        public DataSet[] ShowBackGround(string back_name)
        {
            KingbaseESCommand cmd = new KingbaseESCommand();

            cmd.Connection = conn;
            var tables = GetSchemaInfo(back_name);

            DataSet[] data = new DataSet[tables.Count()];
            for (int i = 0; i < tables.Count(); i++)
            {
                cmd.CommandText = "select * from " + '"' + back_name + '"' + "." + tables[i];
                KingbaseESDataAdapter da = new KingbaseESDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                ds.DataSetName = tables[i];
                data[i]        = ds;
                da.Dispose();
            }
            return(data);
        }
Ejemplo n.º 7
0
        public Tuple <string, bool> RunSql(string sql, string back_name, bool del)
        {
            string            except     = "";
            var               nback_name = CopyBack("tmp", back_name);
            KingbaseESCommand cmd        = new KingbaseESCommand("SET search_path TO " + '"' + nback_name + '"' + ",public", conn);

            cmd.ExecuteNonQuery();
            cmd.CommandText = sql;
            DataSet res = new DataSet();
            KingbaseESDataAdapter resda;
            bool flag = true;

            try {
                resda = new KingbaseESDataAdapter(cmd);
                resda.Fill(res);
                var licount = res.Tables[0].Rows.Count;
                if (licount == 0)
                {
                    except = "null";
                }
                else
                {
                    foreach (var eli in res.Tables[0].Columns)
                    {
                        except += eli.ToString() + " ";
                    }
                }
            }
            catch (System.Exception e) {
                flag   = false;
                except = e.Message;
            }
            var t = new Tuple <string, bool>(except, flag);

            if (del)
            {
                cmd.CommandText = $"DROP SCHEMA \"{nback_name}\" CASCADE";
                cmd.ExecuteNonQuery();
            }
            return(t);
        }
Ejemplo n.º 8
0
        public string CopyBack(string username, string back_name)
        {
            string            res = $"{username}_{back_name}_{Thread.CurrentThread.ManagedThreadId}";
            KingbaseESCommand cmd = new KingbaseESCommand($"select table_name from information_schema.tables where table_schema='{res}'", conn);
            var reader            = cmd.ExecuteReader();

            if (reader.Read())
            {
                reader.Close();
                return(res);//待复制数据库已存在副本,使用副本
            }
            reader.Close();
            var tables = GetSchemaInfo(back_name);

            cmd = new KingbaseESCommand($"CREATE SCHEMA \"{res}\" ", conn);
            cmd.ExecuteNonQuery();
            for (int i = 0; i < tables.Count(); i++)
            {
                cmd.CommandText = $"select * into \"{res}\".{tables[i]} FROM  (SELECT * FROM \"{back_name}\".{tables[i]})";
                cmd.ExecuteNonQuery();
            }
            return(res);
        }
Ejemplo n.º 9
0
        //public void TestDQL(question ques, string ans);
        public Tuple <string, object> InsertData(string path, char deli, string back_name, int table_num, string filename)
        {
            string               sql = "select table_name from information_schema.tables where table_schema='" + back_name + "'";
            KingbaseESCommand    cmd = new KingbaseESCommand(sql, conn);
            KingbaseESDataReader dr  = null;

            dr = cmd.ExecuteReader();
            var tables = new List <string>();

            while (dr.Read())
            {
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    tables.Add((string)dr.GetValue(i));
                }
            }
            dr.Close();
            string table_name = $"{back_name}.{tables[table_num]}";

            path = $"'{path.Replace(@"\","/")}'";
            string[] lines     = { $"DELIMITER='{deli}'", "TYPE=csv", $"DATAFILE={path}", $"TABLE={table_name}", "ROLLBACK=True" };
            var      fname     = filename.Split('.')[0];
            var      ctrl_path = HttpContext.Current.Server.MapPath($"/KingbaseCtrl/{fname + ".ctrl"}");

            System.IO.File.WriteAllLines(ctrl_path, lines);
            sql             = $"select bulkload('{ctrl_path}')";
            cmd.CommandText = sql;
            string except = "";
            object result = new object();

            try { result = cmd.ExecuteScalar(); }
            catch (System.Exception e) {
                except = e.Message;
            }
            return(new Tuple <string, object>(except, result));
        }
Ejemplo n.º 10
0
        public Tuple <string, bool> JudgeSql(string username, question ques, string ans, string back_name)
        {
            ans = ans.Trim();
            ans = ans.TrimEnd(';');
            string            except     = null;
            var               nback_name = CopyBack(username, back_name);
            bool              iscorrect  = false;
            KingbaseESCommand cmd        = new KingbaseESCommand("SET search_path TO " + '"' + nback_name + '"' + ",public", conn);

            cmd.ExecuteNonQuery();
            cmd.CommandText = ques.right_answer;
            DataSet right_ds = new DataSet();

            if (ques.q_type == 1)
            {
                KingbaseESDataAdapter da = new KingbaseESDataAdapter(cmd);
                da.Fill(right_ds);
                da.Dispose();
            }
            else if (ques.q_type == 2)
            {
                cmd.ExecuteNonQuery();
                cmd.CommandText = ques.check_sentence;
                KingbaseESDataAdapter da = new KingbaseESDataAdapter(cmd);
                da.Fill(right_ds);
                da.Dispose();
                DeleteBack(nback_name);
                nback_name = CopyBack(username, back_name);
            }
            cmd.CommandText    = ans;
            cmd.CommandTimeout = 5;
            DataTable ans_dt = new DataTable();

            if (ques.q_type == 2)
            {
                try { cmd.ExecuteNonQuery(); }
                catch (System.Exception e) {
                    except          = e.Message;
                    iscorrect       = false;
                    cmd.CommandText = $"DROP SCHEMA \"{nback_name}\" CASCADE";
                    cmd.ExecuteNonQuery();
                    return(new Tuple <string, bool>(except, iscorrect));
                }
                cmd.CommandText = ques.check_sentence;
            }
            else
            {
                cmd.CommandText = ans + " limit 100";
            }
            //KingbaseESDataReader dr = null;
            int count = 0;

            try {
                /*
                 * dr = cmd.ExecuteReader();
                 * for (var i = 0; i < dr.FieldCount; i++) {
                 *  DataColumn column = new DataColumn();
                 *  column.DataType = dr.GetFieldType(i);
                 *  column.ColumnName = dr.GetName(i);
                 *  ans_dt.Columns.Add(column);
                 * }
                 * while (dr.Read() && count<=60) {
                 *  DataRow row = ans_dt.NewRow();
                 *  for(var i = 0; i < dr.FieldCount; i++) {
                 *      row[i] = dr[i].ToString();
                 *  }
                 *  ans_dt.Rows.Add(row);
                 *  row = null;
                 *  count++;
                 * }
                 * dr.Close();
                 */
                KingbaseESDataAdapter da = new KingbaseESDataAdapter(cmd);
                da.Fill(0, 100, ans_dt);
                da.Dispose();
                var res = DataTableCompare(ans_dt, right_ds.Tables[0]);
                if (res.Item1.Length > 0 || res.Item2.Length > 0)
                {
                    iscorrect = false;
                    if (ques.q_type == 2)
                    {
                        except += $"验证语句{ques.check_sentence}下\n";
                    }
                    if (res.Item1.Length > 0)
                    {
                        var str = "";
                        foreach (var item in res.Item1[0].ItemArray)
                        {
                            str += item.ToString().TrimEnd(' ') + ' ';
                        }
                        except += $"比正确结果少{res.Item1.Length}个,如:{str}\n";
                    }
                    if (res.Item2.Length > 0)
                    {
                        var str = "";
                        foreach (var item in res.Item2[0].ItemArray)
                        {
                            str += item.ToString().TrimEnd(' ') + ' ';
                        }
                        except += $"比正确结果多{res.Item2.Length}个,如:{str}\n";
                    }
                }
                else
                {
                    iscorrect = true;
                    except    = "congratulations!!";
                }
            }
            catch (System.Exception e) {
                except    = e.Message;
                iscorrect = false;
            }
            cmd.CommandText = $"DROP SCHEMA \"{nback_name}\" CASCADE";
            try { cmd.ExecuteNonQuery(); }
            catch (System.Exception e) {
                except    = "执行超时";
                iscorrect = false;
            }
            var re = new Tuple <string, bool>(except, iscorrect);

            return(re);
        }