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 = "單曲循環";}
}

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

第十一次 隨機撥放

<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 ) 

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的值

第十七次 調整耦合/內聚

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

或是 IF ELSE 改成 SWITCH CASS

ex.

上一首下一首

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 但不可逆

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

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

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

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

第一步 鏈結資料庫

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

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

ODBC ?

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

第三步 不需要寫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可以讓整個專案都能使用，但寫在單一頁則只有那頁能使用

第四步 GUI輔助工具箱物件

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

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

​SqlDataSource

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

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

columns 列

bind 繫結 = 被動與態 bound

boundfield = 只顯示欄位

datafield = 要顯示的欄位名稱

headertext = 資料表名稱

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

dateformatstring = 資料格式化字串

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

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

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

細節還是要自己調整

checkboxfield

Previous2019/0226/伺服器ntfs安全性權限/共用權限Next201903

Last updated