ado seems to be working

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace ADOnet
{
    class Program
{
        private static SqlConnection connection;

        static int check_tablename(string table) {
            DataTable dTable = connection.GetSchema("TABLES",
                           new string[] { null, null, table });

            if (dTable.Rows.Count > 0) {
                return 1;
            } else {
                Console.WriteLine("table does not exist");
                return -1;
            }

        }

        static int run(SqlCommand command) {
            try {
                command.ExecuteNonQuery();
                return 0;
            }
            catch (SqlException e) {
                Console.WriteLine(e.Message);
                return -1;
            }
        }

        static void exec(SqlCommand command) {
            try {

                    SqlDataReader reader = command.ExecuteReader();
                    do {
                        while (reader.Read()) {
                            Object[] values = new Object[reader.FieldCount];
                            int fieldCount = reader.GetValues(values);

                            for (int i = 0; i < fieldCount; i++) {
                                Console.Write("\t{0}", values[i]);
                            }
                            Console.WriteLine();

                        }
                    } while (reader.NextResult());

                    reader.Close();


            } catch (SqlException e) { Console.WriteLine(e.Message); }
            Console.WriteLine();
        }

        static void PrintTable(DataTable dt) {
            DataTableReader dtReader = dt.CreateDataReader();

            while (dtReader.Read()) {
                for (int i = 0; i < dtReader.FieldCount; i++) {
                    Console.Write("\t{0}", dtReader.GetValue(i).ToString());
                }
                Console.WriteLine();
            }
            dtReader.Close();
        }

        static void Main(string[] args) {
            string connectionString = ConfigurationManager.ConnectionStrings["AdoConnString"].ConnectionString;
            connection = new SqlConnection(connectionString);

            connection.Open();
            Console.WriteLine("Connection has been opened.");

            int i = 0;
            string com = "help";
            Console.WriteLine("Cвязный уровень");
            while (true) {

                if ('m' == com[0] || 'h' == com[0]) {
                    Console.Write("\tselect\n\t\ttables\n\t\t*\n\t\t\t<table_name>\n");
                    Console.Write("\tinsert\n\t\t<table_name>\n\t\t\t<value_string>\n");
                    Console.Write("\tdelete\n\t\t<table_name>\n\t\t\t<by-fields>\n");
                    Console.Write("\tupdate\n\t\t<table_name>\n\t\t\t<fields to modify>\n\t\t\t\t<by-fields>\n");
                    Console.WriteLine("\texplicit\n\t\t<explicit_query>\n");
                    Console.WriteLine("\texit = q");
                }

                string table, column, q;
                Console.Write(">");
                com = Console.ReadLine();

                if (com.Equals("")) com = "help";
//=========================================================
//------------------------SELECT---------------------------
//=========================================================
                if ('s' == com[0]) {

                    Console.Write("tables | * | cols>>");
                    string subcom = Console.ReadLine();

                    if ('t' == subcom[0]) {

                        q = "select * from INFORMATION_SCHEMA.TABLES";
                        SqlCommand sqlcom = new SqlCommand(q, connection);
                        exec(sqlcom);

                    }

                    if ('*' == subcom[0]) {

                        Console.Write(">>tablename:");
                        table = Console.ReadLine();
                        if (-1 == check_tablename(table))
                            continue;

                        q = string.Format("select * from {0}", table);
                        SqlCommand sqlcom = new SqlCommand(q, connection);

                        exec(sqlcom);

                    }

                    if (subcom.Equals("q"))
                        continue;
                }


//=========================================================
//------------------------INSERT---------------------------
//=========================================================
                if ('i' == com[0]) {

                    Console.Write("->tablename:");
                    table = Console.ReadLine();
                    if (-1 == check_tablename(table))
                        continue;

                    q = string.Format("select * from {0}", table);
                    SqlCommand command = new SqlCommand(q, connection);
                    SqlDataReader reader = command.ExecuteReader();

                    //get column names as result of simple query
                    if (reader.Read()) {

                        int fieldCount = reader.FieldCount;

                        //for query string
                        q = string.Format("insert into {0} (", table);
                        for (int j = 0; j < fieldCount; j++) {
                            q += reader.GetName(j) + ", ";
                        }
                        q = q.Remove(q.Length - 2);
                        q += ") values (";
                        for (int j = 0; j < fieldCount; j++) {
                            q += "@" + reader.GetName(j) + ", ";
                        }
                        q = q.Remove(q.Length - 2);
                        q += ")";
                        Console.WriteLine("Q:" + q);

                        SqlCommand cmd = new SqlCommand(q, connection);

                        //get parameters and add to command
                        for (int j = 0; j < fieldCount; j++) {
                            Console.Write("-->{0}[{1}]:", reader.GetName(j), reader.GetDataTypeName(j));
                            Object value = Console.ReadLine();

                            cmd.Parameters.AddWithValue("@" + reader.GetName(j), value);
                        }

                        reader.Close();

                        if (0 == run(cmd))
                            Console.WriteLine("entry has been inserted");

                    }
                }


//=========================================================
//------------------------DELETE---------------------------
//=========================================================
                if ('d' == com[0])
                {

                    Console.Write("->tablename:");
                    table = Console.ReadLine();
                    if (-1 == check_tablename(table))
                        continue;

                    q = string.Format("select * from {0}", table);
                    SqlCommand command = new SqlCommand(q, connection);
                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.Read()) {

                        Object[] del_values = new Object[reader.FieldCount];//array of values to delete
                        int[] field_indices = new int[reader.FieldCount];//array of their indices in table
                        int delete_col_num = 0, fieldCount = reader.FieldCount;

                        q = string.Format("delete {0} where ", table);
                        for (int j = 0; j < fieldCount; j++) {
                            Console.Write("-->del by {0}? Y/N:", reader.GetName(j));
                            string t_com = Console.ReadLine();

                            if ('n' == t_com[0] || 'N' == t_com[0]) {
                                continue;
                            } else {
                                Console.Write("-->{0}[{1}] value:", reader.GetName(j), reader.GetDataTypeName(j));
                                del_values[delete_col_num] = Console.ReadLine();
                                field_indices[delete_col_num] = j;
                                q += reader.GetName(j) + " = @" + reader.GetName(j) + " and ";
                                delete_col_num++;
                            }

                        }

                        if (0 == delete_col_num) {
                            Console.WriteLine("delete parameters are missing");
                            reader.Close();
                            continue;
                        }
                        q = q.Remove(q.Length - 5);
                        Console.WriteLine("Q:" + q);

                        SqlCommand cmd = new SqlCommand(q, connection);

                        for (int k = 0; k < delete_col_num; k++) {
                            cmd.Parameters.AddWithValue("@" + reader.GetName(field_indices[k]), del_values[k]);
                        }
                        reader.Close();

                        if (0 == run(cmd))
                            Console.WriteLine("entry(-ies) has been deleted");
                    }

                }


//=========================================================
//------------------------UPDATE---------------------------
//=========================================================
                if ('u' == com[0])
                {

                    Console.Write("->tablename:");
                    table = Console.ReadLine();
                    if (-1 == check_tablename(table))
                        continue;

                    q = string.Format("select * from {0}", table);
                    SqlCommand command = new SqlCommand(q, connection);
                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.Read()) {

                        Object[] old_values = new Object[reader.FieldCount];
                        int[] old_field_indices = new int[reader.FieldCount];//array of their indices in table
                        Object[] new_values = new Object[reader.FieldCount];
                        int[] new_field_indices = new int[reader.FieldCount];//array of their indices in table
                        int new_col_num = 0, old_col_num = 0, fieldCount = reader.FieldCount;

                        q = string.Format("update {0} set ", table);
                        for (int j = 0; j < fieldCount; j++) {
                            Console.Write("-->modify {0}? Y/N:", reader.GetName(j));
                            string t_com = Console.ReadLine();

                            if ('n' == t_com[0] || 'N' == t_com[0]){
                                continue;
                            } else {
                                Console.Write("-->{0}[{1}] value:", reader.GetName(j), reader.GetDataTypeName(j));
                                new_values[new_col_num] = Console.ReadLine();
                                new_field_indices[new_col_num] = j;
                                q += reader.GetName(j) + " = @new" + reader.GetName(j) + ", ";
                                new_col_num++;
                            }
                        }
                        q = q.Remove(q.Length - 2);

                        q += " where ";
                        for (int j = 0; j < fieldCount; j++) {
                            Console.Write("-->del by {0}? Y/N:", reader.GetName(j));
                            string t_com = Console.ReadLine();

                            if ('n' == t_com[0] || 'N' == t_com[0]) {
                                continue;
                            } else {
                                Console.Write("-->{0}[{1}] value:", reader.GetName(j), reader.GetDataTypeName(j));
                                old_values[old_col_num] = Console.ReadLine();
                                old_field_indices[old_col_num] = j;
                                q += reader.GetName(j) + " = @old" + reader.GetName(j) + " and ";
                                old_col_num++;
                            }
                        }
                        q = q.Remove(q.Length - 5);
                        Console.WriteLine("Q:" + q);

                        if (0 == new_col_num || 0 == old_col_num) {
                            Console.WriteLine("delete or update parameters are missing");
                            reader.Close();
                            continue;
                        }

                        SqlCommand cmd = new SqlCommand(q, connection);

                        for (int k = 0; k < new_col_num; k++) {
                            cmd.Parameters.AddWithValue("@new" + reader.GetName(new_field_indices[k]), new_values[k]);
                        }
                        for (int k = 0; k < old_col_num; k++) {
                            cmd.Parameters.AddWithValue("@old" + reader.GetName(old_field_indices[k]), old_values[k]);
                        }

                        reader.Close();

                        if (0 == run(cmd))
                            Console.WriteLine("entry(-ies) has been updated");
                    }

                }


//=========================================================
//------------------------EXPLICIT-------------------------
//=========================================================
                if ('e' == com[0]) {

                    Console.Write("->");
                    q = Console.ReadLine();

                    SqlCommand sqlcom = new SqlCommand(q, connection);
                    exec(sqlcom);
                }

                if (com.Equals("q")) break;

                i++;
            }






            //несвязный уровень


            Console.Write("Перейти на Несвязный уроевнь? Y/N:");
            com = Console.ReadLine();

            if ('n' == com[0] || 'N' == com[0]) {
                Console.WriteLine("Closing connection...");
                connection.Close();
                Console.WriteLine("App will shut down soon.");
                return;
            }

            DataSet cds = new DataSet("cds");

            DataColumn cid = new DataColumn("cid", typeof(int));
            cid.AllowDBNull = false;
            cid.Unique = true;
            cid.ReadOnly = true;

            DataColumn name = new DataColumn("name", typeof(string));
            name.AllowDBNull = false;
            name.MaxLength = 35;

            DataColumn balance = new DataColumn("balance", typeof(int));
            balance.AllowDBNull = false;
            balance.DefaultValue = 0;

            DataTable cdt = new DataTable("cardholders");

            //cdt.Columns.AddRange(new DataColumn[] {cid, name, balance });
            //cds.Tables.Add(cdt);



            SqlDataAdapter adapter;
            SqlCommandBuilder builder;
            adapter = new SqlDataAdapter("select * from test", connection);
            builder = new SqlCommandBuilder(adapter);
            adapter.Fill(cds, "test");

            DataTable dt = cds.Tables["test"];
            try
            {
                DataColumn[] primary_key = new DataColumn[1];
                primary_key[0] = cds.Tables["test"].Columns["id"];
                dt.PrimaryKey = primary_key;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }





            while (true) {

                Console.Write(">");
                String q, subcom;
                com = Console.ReadLine();

                if (com.Equals("")) continue;
//=========================================================
//------------------------SELECT---------------------------
//=========================================================
                if ('s' == com[0]) {
                    Console.WriteLine("select * from test");
                    PrintTable(cds.Tables["test"]);
                }

//=========================================================
//------------------------INSERT---------------------------
//=========================================================
                if ('i' == com[0]) {

                    DataRow row = null;

                    try {

                        Console.Write("->id:");
                        int tid = Convert.ToInt32(Console.ReadLine());
                        Console.Write("->name:");
                        string tname = Console.ReadLine();
                        Console.Write("->email:");
                        string tbal = Console.ReadLine();

                        row = dt.NewRow();
                        row[0] = tid;
                        row[1] = tname;
                        row[2] = tbal;

                    } catch (Exception e) {
                        Console.WriteLine(e.Message);
                        continue;
                    }

                    if (null != row) {

                        try
                        {
                            dt.Rows.Add(row);

                            adapter.Update(cds, dt.TableName);
                        }
                        catch (Exception e) { Console.Write(e.Message); continue; }
                        Console.WriteLine("row has been added");

                    }

                }


//=========================================================
//------------------------DELETE---------------------------
//=========================================================
                if ('d' == com[0]) {
                    Console.Write("->by PrimaryKey id:");
                    DataRow row = null;

                    try {

                        int tid = Convert.ToInt32(Console.ReadLine());
                        row = dt.Rows.Find(tid);

                    } catch (Exception e) {
                        Console.WriteLine(e.Message);
                        continue;
                    }

                    if (null != row) {
                        row.Delete();
                        dt.AcceptChanges();
                        Console.WriteLine("row(s) has been deleted");
                        adapter.Update(cds, dt.TableName);
                    }
                }


//=========================================================
//------------------------UPDATE---------------------------
//=========================================================
                if ('u' == com[0]) {

                    int tid = -1;
                    int indx;
                    DataRow row = null;
                    string newmail;

                    try {
                        Console.Write("->new email:");
                        newmail = Console.ReadLine();
                        Console.Write("->by id:");
                        tid = Convert.ToInt32(Console.ReadLine());

                    } catch (Exception e) {
                        Console.WriteLine(e.Message);
                        continue;
                    }

                    try {
                        row = dt.Rows.Find(tid);
                        indx = dt.Rows.IndexOf(row);
                    } catch (Exception e){
                        Console.WriteLine(e.Message);
                        continue;
                    }


                    dt.Rows[indx]["email"] = newmail;
                    adapter.Update(cds, dt.TableName);

                    /*
                    adapter.UpdateCommand = new SqlCommand("update cardholders set balance=@balance where cid=@cid", connection);

                    SqlParameter p1 = new SqlParameter("@balance", SqlDbType.Int);
                    p1.SourceColumn = "balance";
                    p1.SourceVersion = DataRowVersion.Current;
                    adapter.UpdateCommand.Parameters.Add(p1);

                    SqlParameter p2 = new SqlParameter("@cid", SqlDbType.Int);
                    p2.SourceColumn = "cid";
                    p2.SourceVersion = DataRowVersion.Original;
                    adapter.UpdateCommand.Parameters.Add(p2);

                    //row[2] = newbal;
                    adapter.Update(cds, "cardholders");
                    cds.AcceptChanges();*/
                    Console.WriteLine("row has been updated");
                    continue;
                }

                if (com.Equals("q")) break;
            }

            Console.WriteLine("Closing connection...");
            connection.Close();
            Console.WriteLine("App will shut down soon.");
        }
    }
}