ASP.NET MVC5(C#)でSQL Serverにあるデータを取得する方法

---

---

---

このチャプターではASP.NET MVCでデータベースに接続してデータを取りに行ったり、取得したデータをHTML(CSS)とプログラムで連携してデザインして返すプログラムを作ります。

まずはソリューションエクスプローラーより「WebSite」の項目から右クリックして「追加」の「新しいフォルダー」を選択します。フォルダ名は任意ですが、ここでは「Modules」と名付けました。

今度はデータベースに接続するコードなど値が変わらない文字列を管理するクラスファイルを作成します。作成したModulesフォルダを右クリックして、「追加」の「新しい項目」を選択します。

画像のようにVisual C#をクリックして、「クラス」を選択します。ファイル名は任意ですが「mdlConst.cs」としました。

namespace mdlCONST
{
    public class CONSTOpn
    {
        /* DB定義変数 */

        public const string cstrDB_Sorce = "DESKTOP-5A5T3FK"; // お使いのコンピュータ名
        public const string cstrDB_Name = "websitedb";
        public const string cstrDB_User = "sa";
        public const string cstrDB_Pass = "password";
    }
}

変数名は分かりやすいようにし、これに合わせる必要はありません。接続するデータベースのコネクションデータをベタ書きします。 cstrDB_Sorceの変数にSQL Serverに接続する文字列を記述するためにSQL Server Management Studioを立ち上げて現環境を見てみましょう。

画像のように一番上にあるサーバを右クリックでプロパティを選択してください。

名前の右の項目がデータベースの接続に必要となるSorceになります。コピーできるのでドラッグしてコピーし、クラスファイルを書き変えます。注意する点が1つあります。階層表示のスラッシュを意味する「\」はプログラムだと2つ続けないとスラッシュとして認識されません。なので\マークを2つずつ「\\」にします(可読性を上げる他のやり方もありますがこれで対応します)

cstrDB_Nameにデータベースの名前を、cstrDB_Userに「sa」とパスワードは最初にデータベースを作った時に設定したパスワードをベタ書きします。

public const string cstrDB_Sorce = "DESKTOP-5A5T3FK\\SQLEXPRESS";
public const string cstrDB_Name = "websitedb";
public const string cstrDB_User = "sa";
public const string cstrDB_Pass = "password";

最終的にこんな感じになりました。本サイトでは解説しませんが、Webページのいいねやツイートなどで使う値が変わらない絶対的な変数はこのクラスに置いておいて参照します。重要なのは上記4行のコードだけなので、そこだけ記述願います。

データベースに接続する専用のクラスを作成しましょう。

Modulesフォルダに追加で新しい項目よりクラスを選択し、ここでは名前を「mdlDatabase.cs」としました。作成されたファイルの中身を全て削除し、下記コードをコピペします。※ここでは最低限のコードしか記述しておりません。本来なら例外処理にエラー内容をDBなりファイルなりに書き込むコードを記述するべきです。

using System;
using System.Data.SqlClient;
using mdlCONST;

namespace mdlDatabase
{
    public class DatabaseOpn
    {
        // SQL Server用コネクション
        public SqlConnection DB_Connection;

        // DB接続
        public bool DB_Connect()
        {
            DB_Connection = new SqlConnection();

            try
            {
                // 接続文字列を作成して接続を開始する
                DB_Connection.ConnectionString = "data source = " + CONSTOpn.cstrDB_Sorce + ";" +
                                          "initial catalog = " + CONSTOpn.cstrDB_Name + ";" +
                                          "user id         = " + CONSTOpn.cstrDB_User + ";" +
                                          "password        = " + CONSTOpn.cstrDB_Pass + ";" +
                                          "persist security info=True;" +
                                          "Pooling=True;" +
                                          "Min Pool Size=20;" +
                                          "Max Pool Size=200;" +
                                          "Connection Lifetime=2;";

                if (DB_Connection.State == System.Data.ConnectionState.Closed)
                {
                    DB_Connection.Open();
                }

                return true;
            }
            catch (Exception ex)
            {
                if (DB_Connection != null)
                {
                    DB_Connection.Close();
                }

                return false;
            }
        }

        // DB切断
        public bool DB_Close()
        {
            try
            {
                if (DB_Connection.State == System.Data.ConnectionState.Open)
                {
                    DB_Connection.Close();
                }

                DB_Connection.Dispose();
                DB_Connection = null;

                return true;

            }
            catch (Exception ex)
            {
                return false;
            }
        }

        // SQL実行
        public bool DB_SqlReader(string strSql, ref SqlDataReader sqlRdr)
        {
            SqlCommand sqlCmn = new SqlCommand();

            try
            {
                sqlCmn = new SqlCommand(strSql, DB_Connection);
                sqlCmn.Connection = DB_Connection;
                sqlRdr = sqlCmn.ExecuteReader();
                sqlCmn.Dispose();

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}

大丈夫！コードの簡単な解説をします。DB_Connect()はデータベースに接続するときに使う最初に呼び出すメソッドです。

DB_SqlReader()は渡されたSQL文を実行して、データベースからデータを取得した後に参照渡しでデータを返します。
ref SqlDataReader sqlRdとありますが、refが参照渡しです。

データの取得が終わったらデータベースの接続を終了しなければなりません。DB_Close()を呼び出してコネクションを切断します。DB_Connect()で接続をしたのにDB_Close()を忘れると次に接続するときにエラーが出てしまい、最悪システムが動かなくなってしまいます。

要約すると
DB接続開始→データ取得→DB切断
DB_Connect()→DB_SqlReader()→DB_Close()
の順でメソッドを呼び出してデータを取得します。

今度は実際にデータを取得するコードを書きましょう。まずはトップページで記事の見出し(ヘッドライン)など取得して生成するメソッドを作成します。Modulesフォルダに追加で新しい項目よりクラスを選択し、クラスファイルを作成します。ここでは名前を「mdlCommon.cs」としました。 下記のコードを用意しました。

using System;
using System.IO;
using System.Text;
using System.Data.SqlClient;

using mdlDatabase;

namespace mdlCommon
{
    public class CmnOpn
    {

        DatabaseOpn db = new DatabaseOpn();
        mdlCONST.CONSTOpn cnst = new mdlCONST.CONSTOpn();

        public string GET_headLine()
        {
            SqlDataReader sqlRdr = null;
            StringBuilder strSql = new StringBuilder();

            string strHeadLine = "";

            strSql.Append("SELECT b.big_urlroot, s.small_urlroot, k.kiji_id, k.title, k.description, k.ogimage, k.ins_date ");
            strSql.Append("FROM m_kiji k ");
            strSql.Append("INNER JOIN c_big_category b ");
            strSql.Append("ON k.big_category_id = b.big_category_id ");
            strSql.Append("INNER JOIN c_small_category s ");
            strSql.Append("ON k.big_category_id = s.big_category_id ");
            strSql.Append("AND k.small_category_id = s.small_category_id ");
            strSql.Append("ORDER BY k.ins_date DESC ");

            try
            {
                if (!db.DB_Connect())
                {
                    return "";
                }

                if (!db.DB_SqlReader(strSql.ToString(), ref sqlRdr))
                {
                    return "";
                }

                while (sqlRdr.Read())
                {
                    strHeadLine += "\n";
                    strHeadLine += " 
\n";
                    strHeadLine += "  
\n";
                    strHeadLine += "  
" + sqlRdr["title"].ToString() + "
\n";
                    strHeadLine += "  
" + sqlRdr["description"].ToString() + "
\n";
                    strHeadLine += "  
" + sqlRdr["ins_date"].ToString() + "
\n";
                    strHeadLine += " 
\n";
                    strHeadLine += "\n\n";
                }

                return strHeadLine;
            }
            catch (Exception ex)
            {
                return "";
            }
            finally
            {
                if (sqlRdr != null)
                {
                    if (!sqlRdr.IsClosed)
                    {
                        sqlRdr.Close();
                    }
                }
                db.DB_Close();
            }
        }
    }            
}

簡単に解説します。このメソッドは記事のヘッドラインを取得して返します。

取得したいデータをSQL文で指定します。

tryの中身を見てください。初めにデータベースに接続するためにDB_Connect()を使い次にSQL文をDB_SqlReader()に渡してデータを取得してもらいます。 while文でデータの数だけループし、データを最後まで取得するとwhile文を抜けます。

ここが非常に見づらくて申し訳ないのですが、sqlRdr["XXX"].ToString()のコードでXXXにあたる文字列がSQL文のSELECTで指定した列名になります。ぐるぐるループを回してデータベースのデータを取得しながら、HTMLのコード生成します。

HTMLタグでよく使われるダブルクォーテーションはそのままだと使えないのでエスケープシーケンスで「\"」と書かなければなりません。DB_Close()は最後にエラーだろうが必ず通るfinallyに記述してデータベースの切断をしています。

コントローラー側でこのメソッドを呼び出して使ってみましょう！
ソリューションエクスプローラーからTopControllerを開きます。
下記のようにコードを書きます。

using System.Web.Mvc;

using mdlCommon;

namespace WebSite.Controllers
{
    public class TopController : Controller
    {
        // GET: Top

        CmnOpn mdlCmn = new CmnOpn();

        public ActionResult Index()
        {
            ViewBag.HeadLine = mdlCmn.GET_headLine();

            return View();
        }
    }
}

このViewBagに格納されるデータがViewに渡されて、HTMLとして出力してくれます。

続いてViewの設定もします。

Views/Top/Index.cshtmlを開いて下記のようにコードを書きます。

@{
    Layout = "~/Views/Shared/Top.cshtml";
}

@section HeadLine{@Html.Raw(ViewBag.HeadLine)}

更にViews/Shared/Top.cshtmlを開いてmdlCommonで生成したHTMLを使うために@RenderSection("HeadLine")を所定の箇所に書きます。

Top.cshtml内にある@RenderBody()はもう削除して構いません。

< ! DOCTYPE html >
< html lang="ja" >
< head >
    < meta charset="UTF-8" >
    < meta name="viewport" content="width=device-width, initial-scale=1" >
    < title>IT技術部(素材屋)
    < link rel="stylesheet" type="text/css" href="/Content/css/style.css" >

< body >
    

        @RenderPage("_Title.cshtml")
        @RenderPage("_Topmenu.cshtml")

        


        

            

                @RenderPage("_Sidemenu.cshtml")
                

            

            

                @RenderSection("HeadLine")
            
    

    

    

        @RenderPage("_Copyright.cshtml")
    
< /body >
< /html >

これでデバッグをしてみると ヘッドライン(記事の見出し)がちゃんと表示されましたね！