ラベル データベース の投稿を表示しています。 すべての投稿を表示
ラベル データベース の投稿を表示しています。 すべての投稿を表示

2017年9月14日木曜日

MySQL JOIN コマンド

incident テーブル
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | NO   | PRI | NULL    |       |
| title | varchar(1000) | YES  |     | NULL    |       |
| owner | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

owner テーブル
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | NO   | PRI | NULL    |       |
| name  | varchar(1000) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

incident テーブルと owner テーブルを結合する。
SELECT i.id, i.title, o.name
FROM `incident` AS i
RIGHT JOIN (SELECT id, name FROM `owner`) AS o
ON i.owner = o.id

2017年9月11日月曜日

ASP.NET MVC, ASP.NET Web API でのデータベース接続


ASP.NET MVC、ASP.NET Web API 両方で同じ設定が可能です。

Web.config
App_Data フォルダに DB1.mdb がある状態で、下記のように connectionString を設定します。
<connectionStrings>
  <!--a database file(mdf, ldf) will be created in App_Data folder of the project and attach to SQL Server-->
  <add name="DataContext" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DB1.mdf;Integrated Security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
</connectionStrings>

C# コードでは、Web.config で設定した接続文字列を使ってデータベースへ接続します。
var str = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;

using (SqlConnection connection = new SqlConnection(str))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand("Select * from dbo.incident;", connection))
    {
        SqlDataReader dr = command.ExecuteReader();
        while (dr.Read())
        {
            Debug.WriteLine(dr.GetValue(1).ToString());
        }
        dr.Close();
    }
}


2017年3月26日日曜日

ASP.NET MVC でデータベースにテーブルを追加する方法

まずはモデルを用意します。

public class MyModel
{
    public int Id { get; set; }
    public string Description { get; set; }
}

続いて、Application_Start() 内で、Database.SetInitializer メソッドにデータイニシャライザーを生成して渡します。

public class MvcApplication : System.Web.HttpApplication
{
    protected void Application_Start()
    {
        ...
        Database.SetInitializer<DataContext>(new DataInitializer());
        ...
    }
}

データベースとモデルの紐づけは DbContext 継承クラス内で行います。

public partial class DataContext : DbContext
{
    public DataContext()
        : base("name=DataContext")
    {
    }

    //モデル
    public virtual DbSet<MyModel> MyModels { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        ...
        modelBuilder.Entity<MyModel>();
    }
}

2017年3月16日木曜日

MySQL JOIN による3つのテーブルを結合

つなぎ合わせるテーブル3つは下記のとおりです。

受注一覧テーブル
SELECT * FROM order_master;
+----+------------+-----------+
| id | product_id | vendor_id |
+----+------------+-----------+
|  1 |          1 |         2 |
|  2 |          2 |         1 |
|  3 |          4 |         3 |
|  4 |          3 |         2 |
+----+------------+-----------+

商品一覧テーブル
SELECT * FROM product_master;
+----+----------------+
| id | product_name   |
+----+----------------+
|  1 | 調味料A        |
|  2 | 調味料B        |
|  3 | 佃煮           |
|  4 | コーンフレーク |
+----+----------------+

配送業者テーブル
SELECT * FROM vendor_master;
+----+-------------+---------------------+
| id | vendor_name | contract_date       |
+----+-------------+---------------------+
|  1 | 東京配送    | 2017-03-03 00:00:00 |
|  2 | なにわ屋    | 2017-05-03 00:00:00 |
|  3 | 桃山Express | 2018-03-03 00:00:00 |
+----+-------------+---------------------+


① 配送業者テーブル(vendor_master)から、有効期限内である業者からの受注情報を取得します。有効期限を判定するには、配送業者テーブル(vendor_master)の contract_date が今日よりも大きい日付であることを調べます。

② ①の結果取得される配送業者テーブル(vendor_master)を、RIGHT OUTER JOIN で受注一覧テーブル(order_master)と結合します。

③ ②の結果と商品一覧テーブル(product_master)を結合します。

SQL 文はこちらです。

SELECT
 o.id '注文 ID'
 ,o.product_id '商品 ID'
 ,p.product_name '商品名'
 ,v.vendor_name '配送業者'
FROM
 order_master o
 RIGHT OUTER JOIN /*②の処理*/
 (SELECT id, vendor_name FROM vendor_master WHERE contract_date > CURDATE()) v /*①の処理*/
 ON
 o.vendor_id = v.id
 JOIN /*③の処理*/
 product_master p
 ON
 o.product_id = p.id
;

2017年3月13日月曜日

Visual Studio から MySQL に接続してデータを DataGridView に表示する

今回は xampp の MySQL データベースのデータテーブルを取得して Windows Forms の DataGridView に表示する例を紹介します。

NuGet を利用して MySQL のドライバー(MySql.Data)をダウンロード、参照します。
次のスクリーンショットは、MySql.Data をインストールし、プロジェクトでの参照設定がなされた後の状態です。


続いて、フォーム上にボタンを配置してクリックイベントのハンドラーを用意します。イベントハンドラー内に、下記のように実装します。
接続文字列は、MySqlConnectionStringBuilder を利用していますが、代わりに string に直接接続文字列を記述することも可能です。

private void button1_Click(object sender, EventArgs e)
{
    //方法1:MySqlConnectionStringBuilder を利用して接続情報を作成
    MySqlConnectionStringBuilder conBuilder = new MySqlConnectionStringBuilder
    {
        UserID = "root",
        Database = "test",
        Server = "127.0.0.1"
    };

    //方法2:もしくはこのように string で接続文字列を作成
    //string conString = "host=127.0.0.1; user=root; database=test";

    //方法1、方法2で、using を使わずにコネクションオブジェクトを生成する場合、下記2行が利用可能
    //try 内で connection.Open() メソッドでデータベースと接続できるかを確認する
    //MySqlConnection connection = new MySqlConnection(conBuilder.ConnectionString);
    //MySqlConnection connection = new MySqlConnection(conString);

    //using 内でコネクションオブジェクトを生成(利用後に確実に破棄されるようにする)
    using (var connection = new MySqlConnection(conBuilder.ConnectionString))
    //using (var connection = new MySqlConnection(conString))
    {
        try
        {
            // データベースとの接続をオープン
            connection.Open();
            // データを保存するテーブルを作成
            DataTable dt = new DataTable();
            // SQL 文と接続情報を引数に、データアダプターを作成
            MySqlDataAdapter da = new MySqlDataAdapter("select * from books", connection);
            // SQL 文で指定したデータを DataTable に格納
            da.Fill(dt);
            // DataTable を DataGridView に表示
            dataGridView1.DataSource = dt;
            // データベースとの接続をクローズ
            connection.Close();

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

参考情報:

Connect Visual Studio 2015 (VB.net) with XAMPP Tutorial (EASY WAY)
https://www.youtube.com/watch?v=UQ_C8c6rev8