> For the complete documentation index, see [llms.txt](https://johch3n611u.gitbook.io/c50108/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://johch3n611u.gitbook.io/c50108/ju-li-cheng-bei/201902/2019-0227-jsmusicplay-and.md).

# 2019/0227/Javascript\_music\_play\_end\&ASP.net串聯SQL(datasource就是拿來串SQLServer的)

## JSmusicplay

自動撥放 => if ( 目前播放的時間=這首歌的長度 ){ 撥放下一首 };

### 第十次 單曲循環

```
<span id="loop">q</span>
<div id="info2"></div>
 
 <cript>
 var loop = document.getElementById("loop");
 var info2 = document.getElementById("info2");
 loop.addEventListener("click", loopSong);

//單曲循環
function loopSong() 
{
if (info2.innerText == "單曲循環")
{info2.innerText = "";}
else 
{info2.innerText = "單曲循環";}
}
```

判斷自身狀態 如果是開啟單曲循環中則取消 如果是取消中則開啟

### &#x20;第十一次 隨機撥放

```
<span id="random">s</span>
<div id="info2"></div>

<cript>
var random = document.getElementById("random");
var info2 = document.getElementById("info2");
random.addEventListener("click", randomSong);

//隨機播放
        function randomSong() {
            if (info2.innerText == "隨機播放") {
                info2.innerText = "";
            }
            else {
                info2.innerText = "隨機播放";
            }
        }

```

### 第十二次 全曲循環撥放

```
<span id="allloop">`</span>
<div id="info2"></div>

<cript>
var allloop = document.getElementById("allloop");
var info2 = document.getElementById("info2");
allloop.addEventListener("click", allloopSong);

//全曲循環
        function allloopSong() {
            if (info2.innerText == "全曲循環") {
                info2.innerText = "";
            }
            else {
                info2.innerText = "全曲循環";
            }
        }


```

### 第十三次 資訊看板顯示時間

```
<div id="duration">00:00 / 00:00</div>

function getDuration() {

Var durationTime = audio.duration 歌曲總長度
Var currebtTime = audio.currentTime目前播放的長度
取得時間值

duration.innerText = currentTime + " / " + durationTime;
顯示時間值

setTimeout(getDuration,1000);
每隔多久呼叫此功能顯示一次

}

function getTimeFormat(TimeSec) 
{
var min = Math.floor(TimeSec / 60);
var sec = Math.floor(TimeSec) - min * 60;
min = min < 10 ? "0" + min : min;
sec = sec < 10 ? "0" + sec : sec;
但預設取得的是秒數，所以要寫分秒計算

return min + ":" + sec; 重新呼叫的時候重新丟值
}


```

### 第十四次 歌曲循環功能(單曲/隨機/全曲)

目前播放時間 = 整首歌曲時間 => 歌曲播完了

單取循環時 一首歌播完時 讓歌曲回到原點播放

隨機播放 一首歌播完時 陣列裡面隨機選一首

循環播放 一首歌播完時 跳下一首 如果是最後一首就變第一首

三個功能互斥不能同時存在

上一首 下一首 變化 進度條 其實也能整合過來

```
if (durationTime == currentTime) 
{



if (info2.innerText == "單曲循環") 
{
audio.currentTime = 0;
audio.load();
audio.play();
}



else if (info2.innerText == "隨機播放") 
//如果else後面要加條件必須使用eles if
{
index = Math.floor(Math.random() * music.options.length);
//0~1的小數*乘上歌曲幾首並取整數 ( 只會介於0~6
//JS隨機值是給0~1的一個數 所以也須經過運算

song.src = music.options[index].value;
//運算完變化後傳值切換歌曲數值

music.options[index].selected = true;
//同時切換下拉式選單裡的歌曲曲目

audio.load();
audio.play();
//換掉曲目物件裡面的數值，並重新lord play一次





else if (music.selectedIndex == music.options.length - 1)
//目前選曲歌曲的位置 等於 陣列內歌曲的位置 = 最後一首
{
if (info2.innerText == "全曲循環") 
//當歌曲模式為全曲循環時
{
index = 0;
//回到第一首
song.src = music.options[index].value;
music.options[index].selected = true;
audio.load();
audio.play();
}
else {
StopMusic();
}
}


                
//所有模式都沒點時回到播放器最初始值
else
{
index = music.selectedIndex+1;
song.src = music.options[index].value;
music.options[index].selected = true;
audio.load();
}


}

```

### 第十五次 進度條顯示

除了隨著時間調整顯示樣式外，還可以操作(每首歌不同時間換算歌曲比例%)

雙div 一個最寬當底 一個當進度條 ，進度條隨著時間慢慢加長(改變css style)，

```
#progress 
{
background-color: #ff6a00;
height: 20px;
width:0;
}

<div id="settime">
<div id="progress"></div>
</div>






progress.style.width = 
Math.floor(audio.currentTime / audio.duration * 100) + "%";
//現在時間 除與 歌曲長度 * 100% 

console.log(progress.style.width); 
//f12可以看到console值做偵錯
```

### 第十六次 進度條操作

傳事件

當動作mouseclick時

抓 x & y 軸 標值

以進度條為範圍

歌的總時間 \*  進度條長度百分比( 滑鼠點取時的 x 值 / 600 )&#x20;

```
settime.addEventListener("click", function () { setTimeUseBar(event); });
//當settime這個物件被點擊時傳值到event這個變數

//event = evt
function setTimeUseBar(evt) 
{
//console.log(ControlPanel.style.width);
audio.currentTime = audio.duration * (evt.offsetX / 600);
console.log(audio.currentTime);
}
```

寬度 = 撥放器寬度 但不知道位啥抓不到 所以給 600

evt.offsetX 從 evt這個click後抓取的數值中叫出 offsetX的值

{% embed url="<https://blog.csdn.net/weinideai/article/details/3885444>" %}

## 第十七次 調整耦合/內聚

雖然 程式功能FUNCTION每個都獨立出來了，但有雖然不同名稱但內容重複的功能FUNCTION

或是 IF ELSE 改成 SWITCH CASS

ex.

上一首下一首&#x20;

case n 為上一首 做完 break

case p 為下一首 做完 break

單曲循環 case s

隨機撥放 case r

全曲循環 case a

呼叫與上面同一功能 但傳不同值(參數)

什麼都不做的 case c

switch 裡面再將可以動 "不是指定值給屬性的、等號左邊、設定用" 重複的變數縮減

重複寫的程式改為只寫一次

不能重複寫的寫出來

#### 但很容易出錯 變成不知道怎偵錯

### 原始

```
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title></title>
    <style>
        #ControlPanel{
            width:600px;
            margin:auto;
            border:5px solid
        }
        #FuncBtn > span {
            border: 2px solid;
            box-shadow: 3px 3px 3px black;
            cursor: pointer;
            font-family: Webdings;
            font-size: 24pt;
        }
        #volValue {
            width: 25px;
            background-color: black;
            color: yellow;
            text-align: center;
        }
        #settime{
            background-color:#808080;
            height:10px;
            cursor:pointer;
        }
        #progress {
            background-color: #ff6a00;
            height: 10px;
            width:0;
        }
    </style>
</head>
<body>

    <audio id="audio">
        <source id="song" src="music/nothing.mp3" type="audio/mpeg" />

    </audio>

    <div id="ControlPanel">
        <select id="music">
            <option value="music/nothing.mp3">Nothing Gonna Change My Love For You</option>
            <option value="music/nomouth.mp3">真的不想嘴</option>
            <option value="music/travel.mp3">帶你去旅行</option>
            <option value="music/tearshining.mp3">夏川里美-淚光閃閃</option>
            <option value="music/airball.mp3">周杰倫-告白汽球</option>
            <option value="music/sweety.mp3">汪蘇瀧&By2-有點甜</option>
            <option value="music/careyou.mp3">鄧麗君-我只在乎你</option>
        </select>

        <div id="FuncBtn">
            <span id="play">4</span>
            <!--<span id="pause">;</span>-->
            <span id="stop"><</span>
            <span id="prevsong">9</span>
            <span id="prevtime">7</span>
            <span id="nexttime">8</span>
            <span id="nextsong">:</span>
            <span id="muted">V</span>
            <span id="loop">q</span>
            <span id="random">s</span>
            <span id="allloop">`</span>

        </div>
        <div id="volume">
            <input id="vol" type="range" min="0" max="100" value="10" />
            <input id="volM" type="button" value="-" />
            <input id="volP" type="button" value="+" />
            <input id="volValue" type="text"  readonly="readonly" />
        </div>
        <div id="settime">
            <div id="progress"></div>
        </div>

        <div id="InfoPanel">
            <div id="duration">00:00 / 00:00</div>
            <marquee id="info">請按播放鈕~~~~~</marquee>
            <div id="info2"></div>
        </div>
    </div>

    <script>
        var audio = document.getElementById("audio");
        var play = document.getElementById("play");
        //var pause = document.getElementById("pause");
        var stop = document.getElementById("stop");
        var info = document.getElementById("info");
        var music = document.getElementById("music");
        var song = document.getElementById("song");
        var prevtime = document.getElementById("prevtime");
        var nexttime = document.getElementById("nexttime");
        var prevsong = document.getElementById("prevsong");
        var nextsong = document.getElementById("nextsong");

        var vol = document.getElementById("vol");
        var volM = document.getElementById("volM");
        var volP = document.getElementById("volP");
        var volValue = document.getElementById("volValue");
        var muted = document.getElementById("muted");
        var loop = document.getElementById("loop");
        var random = document.getElementById("random");
        var allloop = document.getElementById("allloop");

        var info2 = document.getElementById("info2");
        var duration = document.getElementById("duration");
        var settime = document.getElementById("settime");


        play.addEventListener("click", PlayMusic);
        //pause.addEventListener("click", PauseMusic);
        stop.addEventListener("click", StopMusic);
        music.addEventListener("change", ChangeMusic);
        prevtime.addEventListener("click", function () { TimeChange(false); });
        nexttime.addEventListener("click", function () { TimeChange(true); });
        prevsong.addEventListener("click", function () { SongChange(false); });
        nextsong.addEventListener("click", function () { SongChange(true); });
        vol.addEventListener("input", function () { VolumeChange("c"); });
        volM.addEventListener("click", function () { VolumeChange("m"); });
        volP.addEventListener("click", function () { VolumeChange("p"); });
        muted.addEventListener("click", SetMuted);
        loop.addEventListener("click", loopSong);
        random.addEventListener("click", randomSong);
        allloop.addEventListener("click", allloopSong);
        settime.addEventListener("click", function () { setTimeUseBar(event); });

        VolumeChange("c");

        function setTimeUseBar(evt) {
            //console.log(ControlPanel.style.width);
            audio.currentTime = audio.duration * (evt.offsetX / 600);
            //console.log(ControlPanel.style.width);
        }


        function getDuration() {
            var durationTime = getTimeFormat(audio.duration);
            var currentTime = getTimeFormat(audio.currentTime);
            duration.innerText = currentTime + " / " + durationTime;

            progress.style.width = Math.floor(audio.currentTime/audio.duration  * 100) + "%";
            console.log(progress.style.width);
            setTimeout(getDuration, 1000);

            if (durationTime == currentTime) {
                if (info2.innerText == "單曲循環") {
                    audio.currentTime = 0;
                    audio.load();
                    audio.play();
                }
                else if (info2.innerText == "隨機播放") {
                    index = Math.floor(Math.random() * music.options.length);
                    song.src = music.options[index].value;
                    music.options[index].selected = true;
                    audio.load();
                    audio.play();

                }
                else if (music.selectedIndex == music.options.length - 1) {
                    if (info2.innerText == "全曲循環") {
                        index = 0;
                        song.src = music.options[index].value;
                        music.options[index].selected = true;
                        audio.load();
                        audio.play();
                    }
                    else {
                        StopMusic();
                    }
                }
                else {
                    index = music.selectedIndex+1;
                    song.src = music.options[index].value;
                    music.options[index].selected = true;
                    audio.load();
                    audio.play();
                }

            }

        }


        function getTimeFormat(TimeSec) {
            var min = Math.floor(TimeSec / 60);
            var sec = Math.floor(TimeSec) - min * 60;
            min = min < 10 ? "0" + min : min;
            sec = sec < 10 ? "0" + sec : sec;
            return min + ":" + sec;
        }

        //全曲循環
        function allloopSong() {
            if (info2.innerText == "全曲循環") {
                info2.innerText = "";
            }
            else {
                info2.innerText = "全曲循環";
            }
        }

        //隨機播放
        function randomSong() {
            if (info2.innerText == "隨機播放") {
                info2.innerText = "";
            }
            else {
                info2.innerText = "隨機播放";
            }
        }

        //單曲循環
        function loopSong() {
            if (info2.innerText == "單曲循環") {
                info2.innerText = "";
            }
            else {
                info2.innerText = "單曲循環";
            }
        }



        function SetMuted() {
            if (muted.innerText == "V") {
                audio.muted = true;
                muted.innerText = "U";
            }
            else {
                audio.muted = false;
                muted.innerText = "V";
            }
        }

        function VolumeChange(sound) {
            switch (sound) {
                case "m":
                    vol.value--;
                    break;
                case "p":
                    vol.value++;
                    break;

            }
            volValue.value = vol.value;

            audio.volume = volValue.value/100;
        }

        function SongChange(boolFlag) {
            music.options.length;
            if (boolFlag) {
                if (music.selectedIndex < music.options.length - 1)
                    music.selectedIndex++;
                else
                    music.selectedIndex = 0;
            }
            else {
                if (music.selectedIndex == 0)
                    music.selectedIndex = music.options.length - 1;
                else
                    music.selectedIndex--;
            }
            console.log(music.selectedIndex);
            ChangeMusic();
        }

        function TimeChange(boolFlag) {
            if (boolFlag)
                audio.currentTime += 10;
            else
                audio.currentTime -= 10;
        }


        function PlayMusic() {
            audio.play();
            getDuration();
            ShowStatus(1);
            if (play.innerText == "4")
                play.innerText = ";";
            else {
                play.innerText = "4";
                PauseMusic();
            }
        }
        function PauseMusic() {
            audio.pause();
            ShowStatus(2);
        }
      

        function StopMusic() {
            audio.pause();
            audio.load();
            play.innerText = "4";
            ShowStatus(3);
           
        }
       

        function ChangeMusic() {
            song.src = music.options[music.selectedIndex].value;
            audio.load();
            if (info.innerText == "音樂播放中")
                audio.play();
                //PlayMusic();
        }

        function ShowStatus(status) {
            var strStatus = "";

            switch (status) {
                case 1:
                    strStatus = "音樂播放中";
                    break;
                case 2:
                    strStatus = "音樂暫停";
                    break;
                case 3:
                    strStatus = "音樂停止";
                    break;
            }

            info.innerText = strStatus;
        }
    </script>
</body>
</html>
```

###

### 改過

```
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title></title>
    <style>
        #ControlPanel{
            width:600px;
            margin:auto;
            border:5px solid
        }
        #FuncBtn > span {
            border: 2px solid;
            box-shadow: 3px 3px 3px black;
            cursor: pointer;
            font-family: Webdings;
            font-size: 24pt;
        }
        #volValue {
            width: 25px;
            background-color: black;
            color: yellow;
            text-align: center;
        }
        #settime{
            background-color:#808080;
            height:10px;
            cursor:pointer;
        }
        #progress {
            background-color: #ff6a00;
            height: 10px;
            width:0;
        }
    </style>
</head>
<body>

    <audio id="audio">
        <source id="song" src="music/nothing.mp3" type="audio/mpeg" />

    </audio>

    <div id="ControlPanel">
        <select id="music">
            <option value="music/nothing.mp3">Nothing Gonna Change My Love For You</option>
            <option value="music/nomouth.mp3">真的不想嘴</option>
            <option value="music/travel.mp3">帶你去旅行</option>
            <option value="music/tearshining.mp3">夏川里美-淚光閃閃</option>
            <option value="music/airball.mp3">周杰倫-告白汽球</option>
            <option value="music/sweety.mp3">汪蘇瀧&By2-有點甜</option>
            <option value="music/careyou.mp3">鄧麗君-我只在乎你</option>
        </select>

        <div id="FuncBtn">
            <span id="play">4</span>
            <!--<span id="pause">;</span>-->
            <span id="stop"><</span>
            <span id="prevsong">9</span>
            <span id="prevtime">7</span>
            <span id="nexttime">8</span>
            <span id="nextsong">:</span>
            <span id="muted">V</span>
            <span id="loop">q</span>
            <span id="random">s</span>
            <span id="allloop">`</span>

        </div>
        <div id="volume">
            <input id="vol" type="range" min="0" max="100" value="10" />
            <input id="volM" type="button" value="-" />
            <input id="volP" type="button" value="+" />
            <input id="volValue" type="text"  readonly="readonly" />
        </div>
        <div id="settime">
            <div id="progress"></div>
        </div>

        <div id="InfoPanel">
            <div id="duration">00:00 / 00:00</div>
            <marquee id="info">請按播放鈕~~~~~</marquee>
            <div id="info2"></div>
        </div>
    </div>

    <script>
        var audio = document.getElementById("audio");
        var play = document.getElementById("play");
        //var pause = document.getElementById("pause");
        var stop = document.getElementById("stop");
        var info = document.getElementById("info");
        var music = document.getElementById("music");
        var song = document.getElementById("song");
        var prevtime = document.getElementById("prevtime");
        var nexttime = document.getElementById("nexttime");
        var prevsong = document.getElementById("prevsong");
        var nextsong = document.getElementById("nextsong");

        var vol = document.getElementById("vol");
        var volM = document.getElementById("volM");
        var volP = document.getElementById("volP");
        var volValue = document.getElementById("volValue");
        var muted = document.getElementById("muted");
        var loop = document.getElementById("loop");
        var random = document.getElementById("random");
        var allloop = document.getElementById("allloop");

        var info2 = document.getElementById("info2");
        var duration = document.getElementById("duration");
        var settime = document.getElementById("settime");


        play.addEventListener("click", PlayMusic);
        //pause.addEventListener("click", PauseMusic);
        stop.addEventListener("click", StopMusic);
        music.addEventListener("change", function () { ChangeMusic(music.selectedIndex); });
        prevtime.addEventListener("click", function () { TimeChange(false); });
        nexttime.addEventListener("click", function () { TimeChange(true); });
        prevsong.addEventListener("click", function () { SongChange("p"); });
        nextsong.addEventListener("click", function () { SongChange("n"); });
        vol.addEventListener("input", function () { VolumeChange("c"); });
        volM.addEventListener("click", function () { VolumeChange("m"); });
        volP.addEventListener("click", function () { VolumeChange("p"); });
        muted.addEventListener("click", SetMuted);
        loop.addEventListener("click", loopSong);
        random.addEventListener("click", randomSong);
        allloop.addEventListener("click", allloopSong);
        settime.addEventListener("click", function () { setTimeUseBar(event); });

        VolumeChange("c");

        function setTimeUseBar(evt) {
            //console.log(ControlPanel.style.width);
            audio.currentTime = audio.duration * (evt.offsetX / 600);
            //console.log(ControlPanel.style.width);
        }


        function getDuration() {
            var durationTime = getTimeFormat(audio.duration);
            var currentTime = getTimeFormat(audio.currentTime);
            duration.innerText = currentTime + " / " + durationTime;

            progress.style.width = Math.floor(audio.currentTime/audio.duration  * 100) + "%";
            console.log(progress.style.width);
            setTimeout(getDuration, 1000);

            if (durationTime == currentTime) {
                if (info2.innerText == "單曲循環") {
                    SongChange("s");
                }
                else if (info2.innerText == "隨機播放") {
                    SongChange("r");
                }
                else if (music.selectedIndex == music.options.length - 1) {
                    if (info2.innerText == "全曲循環") {
                        SongChange("a");
                        
                    }
                    else {
                        StopMusic();
                    }
                }
                else {
                    SongChange("c");
                
                }

            }

        }


        function getTimeFormat(TimeSec) {
            var min = Math.floor(TimeSec / 60);
            var sec = Math.floor(TimeSec) - min * 60;
            min = min < 10 ? "0" + min : min;
            sec = sec < 10 ? "0" + sec : sec;
            return min + ":" + sec;
        }

        //全曲循環
        function allloopSong() {
            if (info2.innerText == "全曲循環") {
                info2.innerText = "";
            }
            else {
                info2.innerText = "全曲循環";
            }
        }

        //隨機播放
        function randomSong() {
            if (info2.innerText == "隨機播放") {
                info2.innerText = "";
            }
            else {
                info2.innerText = "隨機播放";
            }
        }

        //單曲循環
        function loopSong() {
            if (info2.innerText == "單曲循環") {
                info2.innerText = "";
            }
            else {
                info2.innerText = "單曲循環";
            }
        }



        function SetMuted() {
            if (muted.innerText == "V") {
                audio.muted = true;
                muted.innerText = "U";
            }
            else {
                audio.muted = false;
                muted.innerText = "V";
            }
        }

        function VolumeChange(sound) {
            switch (sound) {
                case "m":
                    vol.value--;
                    break;
                case "p":
                    vol.value++;
                    break;

            }
            volValue.value = vol.value;

            audio.volume = volValue.value/100;
        }

        function SongChange(strFlag) {
            var index = music.selectedIndex;
            var length = music.options.length;

            switch (strFlag) {
                case "n":
                    if (index < music.options.length - 1)
                        index++;
                    else
                        index = 0;
                    break;
                case "p":
                    if (index == 0)
                        index = length- 1;
                    else
                        index--;
                    break;
                case "s":
                    audio.currentTime = 0;
                    break;
                case "r":
                    index = Math.floor(Math.random() * length);
                    break;
                case "a":
                    index = 0;
                    break;
                default:
                    index++;
                    break;

            }

            //song.src = music.options[index].value;
            //music.options[index].selected = true;

            ChangeMusic(index);
        }

        function TimeChange(boolFlag) {
            if (boolFlag)
                audio.currentTime += 10;
            else
                audio.currentTime -= 10;
        }


        function PlayMusic() {
            audio.play();
            getDuration();
            ShowStatus(1);
            if (play.innerText == "4")
                play.innerText = ";";
            else {
                play.innerText = "4";
                PauseMusic();
            }
        }
        function PauseMusic() {
            audio.pause();
            ShowStatus(2);
        }
      

        function StopMusic() {
            audio.pause();
            audio.load();
            play.innerText = "4";
            ShowStatus(3);
           
        }
       

        function ChangeMusic(index) {
            song.src = music.options[index].value;
            music.options[index].selected = true;
            audio.load();
            if (info.innerText == "音樂播放中")
                audio.play();
                //PlayMusic();
        }

        function ShowStatus(status) {
            var strStatus = "";

            switch (status) {
                case 1:
                    strStatus = "音樂播放中";
                    break;
                case 2:
                    strStatus = "音樂暫停";
                    break;
                case 3:
                    strStatus = "音樂停止";
                    break;
            }

            info.innerText = strStatus;
        }
    </script>
</body>
</html>
```

## ASP串聯SQL

處理資料控制器

看到的明碼因該是雜湊演算

雜湊不可逆 跟加密SSL不一樣

WAN/CRY 128 BIT 加密

A 雜湊完一定 = A1 但不可逆

所以可以雜湊跟雜湊比對 知道本來的內容

![Hashing](/files/-LZhUNLKAVGpeLyN-AM6)

![](/files/-LZhUUOgU3P2nxNopXYt)

樂透彩=課窮人的稅 ， 如果有賺郭台銘早就包牌了

12支排  猜中其中1支 賠十倍 你就算全包 也要12000 他頂多種1支 陪10000 老闆一定賺2000

趨近於零 不等於零 就是有機率重 趨近於一百 不等於一百 就是有機率沒重

### 第一步  鏈結資料庫

利用程式碼自動部屬資料庫(如同上面兩張圖) or 自己設計&#x20;

```
USE master 
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name='MySystem')
DROP DATABASE MySystem
GO

Create database [MySystem]
go

use [MySystem]
go

Create table Members(
	Account varchar(10) primary key,
	Pswd varchar(4000) not null,
	[Name] nvarchar(20) not null,
	Birthday datetime not null,
	Email varchar(30) not null,
	Gender bit not null,
	EduLevel char(1) not null,
	Notes nvarchar(max)
)

Create table Edu(
	EduLevel_Code char(1) primary key,
	EduLevel nvarchar(5) not null
)
go

ALTER TABLE Members
  ADD FOREIGN KEY([EduLevel]) REFERENCES [Edu] ([EduLevel_Code])
GO

Insert into Edu values('1','國小'),('2','國中'),('3','高中'),('4','大學'),('5','研究所以上')
go

Insert into Members values
('wuchi',HASHBYTES('SHA2_256','abcd1234'),'張無忌','1999-12-3','wuchi@wda.gov.tw',1,'4',null ),
('huchun',HASHBYTES('SHA2_256','1234abcd'),'令狐沖','1984-11-6','huchun@wda.gov.tw',1,'5',null ),
('fone',HASHBYTES('SHA2_256','abcdaaaa'),'蕭峰','1993-2-23','fone@wda.gov.tw',1,'3',null ),
('bubai',HASHBYTES('SHA2_256','1234aaaa'),'東方不敗','1988-7-25','bubai@wda.gov.tw',0,'3',null ),
('hufay',HASHBYTES('SHA2_256','fhff4aaaa'),'胡斐','1970-6-22','hufay@wda.gov.tw',1,'4',null ),
('guochin',HASHBYTES('SHA2_256','ddfh6789'),'郭靖','1971-4-7','guochin@wda.gov.tw',1,'4',null ),
('bow',HASHBYTES('SHA2_256','dsfa53df'),'韋小寶','1976-8-31','bow@wda.gov.tw',1,'4',null ),
('yang',HASHBYTES('SHA2_256','sfjaaao9t'),'楊過','1985-6-11','yang@wda.gov.tw',1,'5',null ),
('dragan',HASHBYTES('SHA2_256','df$678hff'),'小龍女','1986-10-10','dragan@wda.gov.tw',0,'2',null ),
('HuangRong',HASHBYTES('SHA2_256','4rtkdasdf'),'黃蓉','1998-4-8','HuangRong@wda.gov.tw',0,'3',null ),
('WangYuYen',HASHBYTES('SHA2_256','fgfbhn6asf'),'王語焉','1963-6-17','WangYuYen@wda.gov.tw',0,'4',null ),
('bochun',HASHBYTES('SHA2_256','dsf56tyhdd'),'岳不群','1989-5-21','bochun@wda.gov.tw',0,'4',null ),
('botong',HASHBYTES('SHA2_256','abcd233455'),'周伯通','1990-9-15','botong@wda.gov.tw',1,'5',null )
go
```

1.設定資料庫伺服器位置在哪(IP位置or電腦名稱)

websevrer datebase

![](/files/-LZhWpIov6KLQhyTWPHZ)

![](/files/-LZhWvfNuWnQQL0EBKWI)

ODBC ?

{% embed url="<https://www.google.com/search?q=ODBC&rlz=1C1GCEU_zh-TWTW835TW836&oq=ODBC&aqs=chrome..69i57j0l5.514j0j7&sourceid=chrome&ie=UTF-8>" %}

![](/files/-LZhY5Ji5g9K602I0yDy)

![](/files/-LZhYVr9l7zb4cuVupoi)

### 第二步 GUI 控制項 設計 分頁 排序修改  刪除

![](/files/-LZhZai1Y3anfEr5BchC)

![](/files/-LZh_Uv3GZhWjN9wrjHe)

![](/files/-LZh_cJY6RjV-4_mSdJt)

![](/files/-LZh_mpaL3iiPUxz4zTI)

####

####

####

####

####

####

####

####

####

####

### 第三步 不需要寫C#可以修改刪除分頁? 因為有處理資料控制項裡面的物件與屬性

```
 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="Account" DataSourceID="SqlDataSource1" EmptyDataText="沒有資料錄可顯示。" Height="399px" Width="1501px">
                <Columns>
                    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                    <asp:BoundField DataField="Account" HeaderText="Account" ReadOnly="True" SortExpression="Account" />
                    <asp:BoundField DataField="Pswd" HeaderText="Pswd" SortExpression="Pswd" />
                    <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                    <asp:BoundField DataField="Birthday" HeaderText="Birthday" SortExpression="Birthday" />
                    <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
                    <asp:CheckBoxField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
                    <asp:BoundField DataField="EduLevel" HeaderText="EduLevel" SortExpression="EduLevel" />
                    <asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MySystemConnectionString1 %>" DeleteCommand="DELETE FROM [Members] WHERE [Account] = @Account" InsertCommand="INSERT INTO [Members] ([Account], [Pswd], [Name], [Birthday], [Email], [Gender], [EduLevel], [Notes]) VALUES (@Account, @Pswd, @Name, @Birthday, @Email, @Gender, @EduLevel, @Notes)" ProviderName="<%$ ConnectionStrings:MySystemConnectionString1.ProviderName %>" SelectCommand="SELECT [Account], [Pswd], [Name], [Birthday], [Email], [Gender], [EduLevel], [Notes] FROM [Members]" UpdateCommand="UPDATE [Members] SET [Pswd] = @Pswd, [Name] = @Name, [Birthday] = @Birthday, [Email] = @Email, [Gender] = @Gender, [EduLevel] = @EduLevel, [Notes] = @Notes WHERE [Account] = @Account">
                <DeleteParameters>
                    <asp:Parameter Name="Account" Type="String" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="Account" Type="String" />
                    <asp:Parameter Name="Pswd" Type="String" />
                    <asp:Parameter Name="Name" Type="String" />
                    <asp:Parameter Name="Birthday" Type="DateTime" />
                    <asp:Parameter Name="Email" Type="String" />
                    <asp:Parameter Name="Gender" Type="Boolean" />
                    <asp:Parameter Name="EduLevel" Type="String" />
                    <asp:Parameter Name="Notes" Type="String" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="Pswd" Type="String" />
                    <asp:Parameter Name="Name" Type="String" />
                    <asp:Parameter Name="Birthday" Type="DateTime" />
                    <asp:Parameter Name="Email" Type="String" />
                    <asp:Parameter Name="Gender" Type="Boolean" />
                    <asp:Parameter Name="EduLevel" Type="String" />
                    <asp:Parameter Name="Notes" Type="String" />
                    <asp:Parameter Name="Account" Type="String" />
                </UpdateParameters>
            </asp:SqlDataSource>
```

```
    public partial class _16DB_Connection : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
    }
```

#### 鏈結到資料庫的原始碼 寫在web.config可以讓整個專案都能使用，但寫在單一頁則只有那頁能使用

![](/files/-LZheDdTlWthIR0Jo0f5)

![](/files/-LZhonw-1ZQFQwfFBeSg)

![](/files/-LZhosAnwVRblTdlnaVm)

### 第四步 GUI輔助工具箱物件

將工具箱內物件拖移至CODING頁面在切換到設計頁面使用GUI自動部屬屬性

![](/files/-LZhphdS-bdv_8J9xPk5)

撈回來的資料會放在伺服器的記憶體中

#### ​SqlDataSource

```
 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:MySystemConnectionString1 %>" 
                SelectCommand="SELECT * FROM [Members]"></asp:SqlDataSource>
```

![](/files/-LZhs6YRcGMlFKjrXoH5)

#### GridView

選擇前

```
<asp:GridView ID="GridView" runat="server"></asp:GridView>
```

GUI選擇後

```
 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Account" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="Account" HeaderText="Account" ReadOnly="True" SortExpression="Account" />
                    <asp:BoundField DataField="Pswd" HeaderText="Pswd" SortExpression="Pswd" />
                    <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                    <asp:BoundField DataField="Birthday" HeaderText="Birthday" SortExpression="Birthday" />
                    <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
                    <asp:CheckBoxField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
                    <asp:BoundField DataField="EduLevel" HeaderText="EduLevel" SortExpression="EduLevel" />
                    <asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />
                </Columns>
            </asp:GridView>
```

DataSourceID=資料來源ID

其實給這個屬性就能顯示資料庫了

DataKeyNames=索引主key (可能由兩個以上欄位組成用,隔開)

AutoGenerateColumns ="False" 是否自動產生欄位

如不不要則需要寫 Columns&#x20;

columns 列

bind 繫結 = 被動與態 bound

boundfield = 只顯示欄位

datafield = 要顯示的欄位名稱

headertext = 資料表名稱

visible = 是否顯示(用於計算)

dateformatstring = 資料格式化字串

dateformatstring ="<{0}>"  自式化字元 原封不動顯示

dateformatstring ="<{0:D}>"  自式化字元 每種格式具有代碼取決於資料型態

{% embed url="<https://docs.microsoft.com/zh-tw/dotnet/api/system.web.ui.webcontrols.boundfield.dataformatstring?view=netframework-4.7.2>" %}

{% embed url="<https://blog.xuite.net/tolarku/blog/43506010-%5BASP.NET%5D+Gridview+%E6%AC%84%E4%BD%8D%E8%BC%B8%E5%87%BA%E6%A0%BC%E5%BC%8F+DataFormatString>" %}

自動部屬通常具有預設屬性

細節還是要自己調整

checkboxfield