Создание веб-приложения, сочетающего в себе проектирование и создание баз данных, работу со сторонними форматами данных, программирование в NET и веб-разработку, страница 86

                    sql = "select h.N from (select " + table_calendar + ".id as N, " + table_calendar + ".round as R, teams.name as Home from " + table_calendar + " inner join teams on teams.id = " + table_calendar + ".home) as h inner join(select " + table_calendar + ".id as N, " + table_calendar + ".round as R, teams.name as Away, " + table_calendar + ".date_tab as DATE from " + table_calendar + " inner join teams on teams.id = " + table_calendar + ".away) as a on h.N = a.N WHERE (Home='" + team + "' or Away='" + team + "') and (DATE=(SELECT MAX(date_tab) FROM " + table_calendar + "))";

                }

                else

                {

                    conn.Close();

                    return;

                }

                comm = new MySqlCommand(sql, conn);

                reader = comm.ExecuteReader();

                while (reader.Read())

                {

                    id_game = Convert.ToInt32(reader[0].ToString());

                }

                reader.Close();

                if (id_game != 0)

                {

                    sql = "select * from " + table_team_stats + " inner join teams on teams.id = " + table_team_stats + ".team and teams.id = "+id_team+" WHERE " + table_team_stats + ".game = "+id_game+";";

                }

                else

                {

                    conn.Close();

                    return;

                }

                dt.Clear();

                da = new MySqlDataAdapter(sql, conn);

                da.Fill(dt);

                report.DataSource = dt;

                report.DataBind();

                conn.Close();

            }

            catch (MySqlException exp)

            {

                conn.Close();

            }

        }

        public static void getNextGamesForTeam(GridView report, string team, string season)

        {

            string database = "powerplay";

            string password_db = "admin";

            string user_db = "root";

            string server_db = "localhost";

            connectionString = ("server=" + server_db + "; user id=" + user_db + "; password=" + password_db + "; database=" + database + "");

            MySqlConnection conn = null;

            try

            {

                //ОБЪЯВЛЕНИЕ

                string table_calendar = "calendar_khl20122013_1";

                conn = new MySqlConnection(connectionString);

                conn.Open();

                string sql;

                MySqlDataAdapter da = null;

                DataTable dt = new DataTable();

                MySqlCommand comm = null;

                MySqlDataReader reader = null;

                int id_team = 0;

                //ВЫБИРАЕМ СТРОКУ ИЗ ТАБЛИЦЫ

                sql = "SELECT id FROM teams WHERE name = '" + team + "' ";

                comm = new MySqlCommand(sql, conn);

                reader = comm.ExecuteReader();

                while (reader.Read())

                {

                    id_team = Convert.ToInt32(reader[0].ToString());

                }

                reader.Close();

                if (id_team != 0)

                {

                    sql = "select h.N,h.R,h.Home,a.Away from (select " + table_calendar + ".id as N, " + table_calendar + ".round as R, teams.name as Home from " + table_calendar + " inner join teams on teams.id = " + table_calendar + ".home) as h inner join(select " + table_calendar + ".id as N, " + table_calendar + ".round as R, teams.name as Away, " + table_calendar + ".date_tab as DATE from " + table_calendar + " inner join teams on teams.id = " + table_calendar + ".away) as a on h.N = a.N WHERE (Home='" + team + "' or Away='" + team + "') and (h.R=(SELECT MIN(round) FROM " + table_calendar + " where (" + table_calendar + ".date_tab is null or " + table_calendar + ".date_tab= '')));";