[ASP .NET] 2. ASP .NET Framework MVC, MSSQL 연결

 

[개발/C#] - [C#] 1. ASP .NET MVC 프로젝트 생성에 이어

.NET Framework 4.7.2 MVC 패턴을 사용한 ASP WebApplication에서 MSSQL을 연결 해보겠습니다.

 

Controller -> Service -> Dao -> DBHelper 순으로 호출이 이어집니다.

역순으로 만들어보겠습니다.

DB 연결하기

1. Common, Dao, Service 폴더 추가

Model -> 우클릭 -> 추가 -> 새 폴더(D) -> Common, Dao, Service 폴더 추가

 

2. DBHelper.cs 클래스 추가/편집 

Common -> 우클릭 -> 추가 -> 새 항목(W)... -> DBHelper.cs 클래스 추가/편집

using System.Data.SqlClient; //추가

namespace WebApplication1.Models.Common
{
    public class DBHelper
    {
        private static string SERVER = "<000.000.000.000>";
        private static string UID = "<로그인>";
        private static string PWD = "<암호>";
        private static string DATABASE = "<데이터베이스 명>";

        private string CONNECTIONSTRING = $"server={SERVER};uid={UID};pwd={PWD};database={DATABASE};";

        private SqlConnection connection { get; set; }

        public SqlConnection GetConnection()
        {
            if (connection != null)
            {
                return connection;
            }
            else
            {
                return new SqlConnection(CONNECTIONSTRING);
            }
        }
    }
}

 

3. SampleDao.cs 클래스 추가/편집

Dao -> SampleDao.cs 클래스 추가/편집

using Dapper;
using System.Data;
using System.Linq;
using WebApplication1.Models.Common;

namespace WebApplication1.Models.Dao
{
    public class SampleDao: DBHelper
    {
        public string GetDbVersion()
        {
            string rtnVal = null;

            using(var conn = base.GetConnection())
            {
                string sql = "SELECT @@VERSION";
                var p = new DynamicParameters(); // 'Dapper' 패키지 설치
                
                conn.Open();

                rtnVal = conn.Query<string>(sql, p, commandType: CommandType.Text).FirstOrDefault();
            }

            return rtnVal; 
        }
    }
}

 

4. SampleService.cs 클래스 추가/편집

Service -> SampleService.cs 클래스 추가/편집

using WebApplication1.Models.Dao;

namespace WebApplication1.Models.Service
{
    public class SampleService
    {
        private SampleDao sampleDao = new SampleDao();
        public string GetDbVersion()
        {
            return sampleDao.GetDbVersion();
        }
    }
}

 

5. HomeController.cs 편집

Controller -> HomeController.cs 편집

using System.Web.Mvc;
using WebApplication1.Models.Service;

namespace WebApplication1.Controllers
{
    public class HomeController : Controller
    {
        SampleService _sampleService = new SampleService();
        public ActionResult Index()
        {
            object rtnVal = _sampleService.GetDbVersion();
            return View(rtnVal);
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

 

6. Index.cshtml 편집

Views>Home -> Index.cshtml 편집 // @Model 추가

@{
    ViewBag.Title = "Home Page";
}

<div class="jumbotron">
    <h1>ASP.NET</h1>
    <p class="lead">ASP.NET is a free web framework for building great Web sites and Web applications using HTML, CSS and JavaScript.</p>
    <p><a href="https://asp.net" class="btn btn-primary btn-lg">Learn more &raquo;</a></p>
    <p>@Model</p>
</div>

<div class="row">
    <div class="col-md-4">
        <h2>Getting started</h2>
        <p>
            ASP.NET MVC gives you a powerful, patterns-based way to build dynamic websites that
            enables a clean separation of concerns and gives you full control over markup
            for enjoyable, agile development.
        </p>
        <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301865">Learn more &raquo;</a></p>
    </div>
    <div class="col-md-4">
        <h2>Get more libraries</h2>
        <p>NuGet is a free Visual Studio extension that makes it easy to add, remove, and update libraries and tools in Visual Studio projects.</p>
        <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301866">Learn more &raquo;</a></p>
    </div>
    <div class="col-md-4">
        <h2>Web Hosting</h2>
        <p>You can easily find a web hosting company that offers the right mix of features and price for your applications.</p>
        <p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301867">Learn more &raquo;</a></p>
    </div>
</div>

 

결과

DB 버전 정보 출력