iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 2
0
自我挑戰組

What a good thing we lose? What a bad thing we knew?系列 第 2

【Day 2】 在Visual Studio 2017 建立 localDB資料庫 (2/2)

大家好,今天的教學是如何使用讀取內建的localDB中的資料。

Step 1. 首先先點擊資料庫,接著找到連線字串,並且複製
https://ithelp.ithome.com.tw/upload/images/20181016/20112000cl3YkXzUSF.png

Step 2.打開Web.config檔案,輸入底下的程式碼,檔案讀取連接字串

<connectionStrings>
    <add name="connect" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\black\source\repos\ithome\ithome\App_Data\Database1.mdf;Integrated Security=True" />
</connectionStrings>     
  

https://ithelp.ithome.com.tw/upload/images/20181016/20112000zDZb5qpxXM.png

Step 3. 在HomeController ,輸入以下的程式碼

    public ActionResult Demo()
    {

        return View();
    }

https://ithelp.ithome.com.tw/upload/images/20181016/20112000Jnce43d7Ur.png

Step 4-1. 建立View,對著Controller按下右鍵
https://ithelp.ithome.com.tw/upload/images/20181016/201120009LvwILesi5.png
Step 4-2. 按下加入
https://ithelp.ithome.com.tw/upload/images/20181016/201120000MCk0AY8IO.png
Step 4-3. 成功建立View的畫面
https://ithelp.ithome.com.tw/upload/images/20181016/20112000x70qSmDFJz.png

Step 5-1. using 要使用的類別

using System.Configuration;
using System.Data.SqlClient;

Step 5-2. 在Controller加入

string strConnString = ConfigurationManager.ConnectionStrings["connect"].ConnectionString;

https://ithelp.ithome.com.tw/upload/images/20181016/20112000oEioX6NZZf.png

Step 6. 建立資料

https://ithelp.ithome.com.tw/upload/images/20181016/20112000DPEiM7piTB.png

Step 7. 寫Function 撈資料

 public string[] GetData()
    {
        string[] arr = { "", "", ""};
        using (SqlConnection conn = new SqlConnection(strConnString))
        {
            conn.Open();
            SqlCommand scom = new SqlCommand("", conn);
            scom.CommandText = @"   
                                   select 
                                          id,
                                          name,
                                          age
                                   from [dbo].[people]                                      

                                ";            

            SqlDataReader sread = scom.ExecuteReader();
            if (sread.Read())
            {
                arr[0] = sread["id"].ToString();
                arr[1] = sread["name"].ToString();
                arr[2] = sread["age"].ToString();

            }
        }
        return arr;
    }

Step 8. 撈出資料後,傳到View

  public ActionResult Demo()
    {
        string[] array = GetData();
        ViewBag.id = array[0];
        ViewBag.name = array[1];
        ViewBag.age = array[2];


        return View();
    }

Step 9. View

@{
ViewBag.Title = "Demo";
}

<h2>Demo</h2>

@ViewBag.id 
@ViewBag.name 
@ViewBag.age 

https://ithelp.ithome.com.tw/upload/images/20181016/20112000RLT269thLz.png
Step 10. 成功畫面
https://ithelp.ithome.com.tw/upload/images/20181016/20112000E5HdwOepfQ.png


上一篇
【Day 1】 在Visual Studio 2017 建立 localDB資料庫 (1/2)
下一篇
【Day 3】 在Visual Studio 2017 建立身分證產生器
系列文
What a good thing we lose? What a bad thing we knew?30

1 則留言

0
Joe
iT邦新手 4 級 ‧ 2018-12-13 13:45:40

稍微改了一下:

public List<string> GetData(string strConnString, string CommandText)
        {
            List<string> rowList = new List<string>();

            using (SqlConnection conn = new SqlConnection(strConnString))
            {
                conn.Open();
                SqlCommand scom = new SqlCommand("", conn);
                scom.CommandText = CommandText;

                SqlDataReader sread = scom.ExecuteReader();
                if (sread.Read())
                {
                    for (int i = 0; i < sread.FieldCount; i++)
                    {
                        rowList.Add(sread[i].ToString());
                    }
                }
            }
            return rowList;
        }
public ActionResult Demo()
        {
            string strConnString = ConfigurationManager.ConnectionStrings["connect"].ConnectionString;
            string queryString = "Select ID,NAME,AGE FROM[DBO].[PEOPLE]";

            List<string> resList = GetData(strConnString, queryString);

            ViewBag.ID = resList[0];
            ViewBag.NAME = resList[1];
            ViewBag.AGE = resList[2];

            return View();
        }

/images/emoticon/emoticon34.gif

感謝分享

我要留言

立即登入留言