ASP.NET/MVC 3
ASP.NET MVC 3 MSSQL 연결하기 + Select
littlemk
2018. 8. 23. 15:18
Visual studio 2010 express
SQL Server 2017
현재 DB내용 조회가능한 페이지 만들기
- USER_DAL.cs 코드 변경
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
//using MySql.Data.MySqlClient;
using System.Data.SqlClient;
using System.Data;
namespace Test0821.DAL
{
public class USER_DAL
{
public static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TEST_DBConnectionString"].ToString());
//public static con = ConfigurationManager.ConnectionStrings["TEST_DBConnectionString"].ConnectionString;
//context = new DataContext(connectionString);
//usersTable = context.GetTable<UserObj>();
/* insert */
public static void Insert_User(string id, string name, int age, string sex)
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = string.Format("Insert TB_USER values('{0}','{1}','{2}','{3}')", id, name, age, sex);
cmd.ExecuteNonQuery();
con.Close();
}
/* Select */
public static DataSet Select_User()
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "TB_USER");
con.Close();
return ds;
}
}
}
- Index.cshtml 코드 변경
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
<div>
안녕하세요! Index.cshtml 뷰 페이지입니다.
<br>
@ViewBag.Hello
<br>
@* ("보여질 이름", "실행될 액션")*@
@Html.ActionLink("모델테스트페이지", "NewForm0821");
@Html.ActionLink("조회하기", "NewForm_Search");
</div>
</body>
</html>
- Test0821Controller.cs 코드 변경
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Test0821.Models;
using Test0821.DAL;
using System.Data;
namespace Test0821.Controllers
{
public class Test0821Controller : Controller
{
//
// GET: /Home/
// public ActionResult Index()
//{
//return View();
//}
// 홈페이지상에서 보여질 문구
//public string Index()
//{
//return "안녕하세요~ HomeController 실행했습니다.";
//}
// View 추가하기
public ViewResult Index()
{
string str_Message = "동적 출력하는 부분입니다.";
ViewBag.Hello = str_Message;
return View();
}
[HttpGet]
public ViewResult NewForm0821()
{
return View();
}
[HttpPost]
public ViewResult NewForm0821(Test0821_Model dataModel)
{
USER_DAL.Insert_User(dataModel.ID, dataModel.NAME, dataModel.AGE, dataModel.SEX);
return View("dataViewPage", dataModel);
}
[HttpGet]
public ViewResult NewForm_Search()
{
DataSet ds = new DataSet();
ds = USER_DAL.Select_User();
return View(ds);
}
}
}
- 새로 생성한 ViewResult NewForm_Search의 View 파일 추가 및 코드 변경
@model System.Data.DataSet
@using System.Data
<!DOCTYPE html>
<html>
<head>
<title>NewForm2</title>
</head>
<body>
<table>
<tr>
<td>id</td>
<td>NAME</td>
<td>AGE</td>
<td>SEX</td>
</tr>
@foreach (DataRow row in Model.Tables["TB_USER"].Rows)
{
<tr>
<td>@(row["ID"])</td>
<td>@(row["NAME"])</td>
<td>@(row["AGE"])</td>
<td>@(row["SEX"])</td>
</tr>
}
</table>
</body>
</html>
- 결과 화면
- 현재 DB 화면
