C#を始めよう｜DataGridViewにデータベースのテーブルを表示

何かと便利なDataGridViewにSQL ServerとMySQLのテーブルを表示します。

参照設定

SQL ServerとMySQLのそれぞれの参照設定します。

SQL Server

using System.Data.SqlClient;

MySQL

using MySql.Data.MySqlClient;

SQL Server

private  void    SqlServerToDataGridView()
{
    //  SQL Server接続
    using (SqlConnection Connection = new SqlConnection())
    {
        try
        {
            string  TxConnection    =   "";
            TxConnection    +=  "Data Source = "        +   @"localhost\SQLEXPRESS" +   ";";
            TxConnection    +=  "Initial Catalog = "    +   "SSSPRODUCT"            +   ";";
            TxConnection    +=  "User ID = "            +   "sa"                    +   ";";
            TxConnection    +=  "Password = "           +   "xsysmngr"              +   ";";
            Connection.ConnectionString = TxConnection;
            Connection.Open();
        }
        catch(SqlException  e)
        {
            Console.WriteLine("SQL Server接続失敗");
            Console.WriteLine(e.Message);
            return;
        }
        using (SqlCommand Command = new SqlCommand())
        {
            try
            {
                string TxSQL = "";
                TxSQL += "SELECT";
                TxSQL += "        TBLTESTID,";
                TxSQL += "        TBLTESTNAMERUBY,";
                TxSQL += "        TBLTESTNAME";
                TxSQL += "    FROM";
                TxSQL += "       TBLTEST";
                Command.CommandText =   TxSQL;
                Command.Connection  =   Connection;
            }
            catch(SqlException  e)
            {
                Console.WriteLine("SQL Server SqlCommand Error");
                Console.WriteLine(e.Message);
                return;
            }
            using (SqlDataAdapter Adapter = new SqlDataAdapter(Command))
            {
                try
                {
                    DataSet DataSet = new DataSet();
                    Adapter.Fill(DataSet);
                    DgvSqlServer.DataSource = DataSet.Tables[0];
                }
                catch(SqlException  e)
                {
                    Console.WriteLine("SQL Server SqlDataAdapter Error");
                    Console.WriteLine(e.Message);
                    return;
                }
            }
        }
        //  SQL Server解放
        Connection.Close();
    }
}

MySQL

private void    MySqlToDataGridView()
{
//  MySQL 接続
using (MySqlConnection Connection = new MySqlConnection())
{
    try
    {
        string  TxConnection=   "";
        TxConnection    +=  "Server="   +   "localhost;";
        TxConnection    +=  "Database=" +   "sssproduct;";
        TxConnection    +=  "Uid="      +   "root;";
        TxConnection    +=  "Pwd="      +   ";";
        Connection.ConnectionString     =   TxConnection; 
        Connection.Open();
    }
    catch (MySqlException   e)
    {
        Console.WriteLine("MySQL接続失敗");
        Console.WriteLine(e.Message);
        return;
    }
    using (MySqlCommand Command =   new MySqlCommand())
    {
        try
        {
            string TxSQL = "";
            TxSQL += "SELECT ";
            TxSQL += "        TESTID,";
            TxSQL += "        TESTNAME,";
            TxSQL += "        TESTRUBY,";
            TxSQL += "        TESTGENDER,";
            TxSQL += "        TESTBLOOD,";
            TxSQL += "        TESTBIRTHDAY,";
            TxSQL += "        TESTTEL,";
            TxSQL += "        TESTMOBILETEL,";
            TxSQL += "        TESTEMAIL,";
            TxSQL += "        TESTPOSTNO,";
            TxSQL += "        TESTADDRESS,";
            TxSQL += "        TESTTIMESTAMP";
            TxSQL += "    FROM ";
            TxSQL += "        TEST;";
            Command.CommandText =   TxSQL;
            Command.Connection  =   Connection;
        }
        catch (MySqlException   e)
        {
            Console.WriteLine("MySQL MySqlCommand Error");
            Console.WriteLine(e.Message);
            return;
        }
        using ( MySqlDataAdapter Adapter = new MySqlDataAdapter(Command))
        {
            try
            {
                DataSet DataSet = new DataSet();
                Adapter.Fill(DataSet);
                DgvMySql.DataSource =   DataSet.Tables[0];
            }
            catch (MySqlException   e)
            {
                Console.WriteLine("MySQL MySqlDataAdapter Error");
                Console.WriteLine(e.Message);
                return;
            }
        }
    }
    //  MySQL解放
    Connection.Close();
}

まとめ

SQL ServerとMySQLもほとんど同じようなコードになります。

最初にデータベースに接続。
コマンドでコネクションとSQL文を設定して、アダプターでデータセットを生成。
データセットをDataGridViewのデータソースに設定するだけです。

何等かのデータベースからDataGridViewへの表示を知っていれば応用で色んなデータベースに対応できると思います。