Asp.Net 雖然有很多工具可以做到零程式碼 ,就能完整的撈出資料 , 但是有些功能還是手工 "卡實在"
第一件事是要引用 web.config 裡的連接語法 , 如果還沒有做的 , 請手工KEY進去 , 或建一個 SqlDataSoure ,VS 會幫你建好
|
1 |
<span style="color: #606060" id="lnum1"> 1:</span> <span style="color: #0000ff"><</span><span style="color: #800000">connectionStrings</span><span style="color: #0000ff">></span> |
|
1 |
<span style="color: #606060" id="lnum2"> 2:</span> <span style="color: #0000ff"><</span><span style="color: #800000">add</span> <span style="color: #ff0000">name</span><span style="color: #0000ff">="Conn"</span> <span style="color: #ff0000">connectionString</span><span style="color: #0000ff">="Data Source=sql_server;Initial Catalog=EIP;Persist Security Info=True;User ID=myid;Password=xxxxxx"</span> <span style="color: #ff0000">providerName</span><span style="color: #0000ff">="System.Data.SqlClient"</span><span style="color: #0000ff">/></span> |
|
1 |
<span style="color: #606060" id="lnum3"> 3:</span> <span style="color: #0000ff"></</span><span style="color: #800000">connectionStrings</span><span style="color: #0000ff">></span> |
name="Conn" =>連線名稱
Data Source=sql_server =>SQL伺服器
User ID=myid =>帳號
Password=xxxxxx =>密碼
再來介紹最常用的查詢(select)
C#:
|
1 |
<span style="color: #606060" id="lnum1"> 1:</span> SqlConnection conn = <span style="color: #0000ff">new</span> SqlConnection(ConfigurationManager.ConnectionStrings[<span style="color: #006080">"Conn"</span>].ToString()); |
|
1 |
<span style="color: #606060" id="lnum2"> 2:</span> <span style="color: #008000">//引用web.config 裡連線語法</span> |
|
1 |
<span style="color: #606060" id="lnum3"> 3:</span> <span style="color: #0000ff">string</span> cmdst = <span style="color: #006080">"select sys_dep_id from sys_dep where sys_dep_name ='ABC'"</span>; <span style="color: #008000">//sql語法</span> |
|
1 |
<span style="color: #606060" id="lnum4"> 4:</span> SqlCommand scmd = <span style="color: #0000ff">new</span> SqlCommand(cmdst, conn); <span style="color: #008000">//建立命令</span> |
|
1 |
<span style="color: #606060" id="lnum5"> 5:</span> conn.Open(); <span style="color: #008000">//開啟conn連線</span> |
|
1 |
<span style="color: #606060" id="lnum6"> 6:</span> SqlDataReader sdr = scmd.ExecuteReader(); <span style="color: #008000">//進行讀取資料</span> |
|
1 |
<span style="color: #606060" id="lnum7"> 7:</span> <span style="color: #0000ff">while</span> (sdr.Read()) <span style="color: #008000">//利用 while 讀出檔案 , Read() 每次讀一行 , 最後 fales 跳出</span> |
|
1 |
<span style="color: #606060" id="lnum8"> 8:</span> { |
|
1 |
<span style="color: #606060" id="lnum9"> 9:</span> Response.Write(ser[0].ToString()); |
|
1 |
<span style="color: #606060" id="lnum10"> 10:</span> } |
|
1 |
<span style="color: #606060" id="lnum11"> 11:</span> sdr.Close(); |
|
1 |
<span style="color: #606060" id="lnum12"> 12:</span> conn.Close(); <font color="#008000">//切記 , 有開啟, 就有關閉 , 不然連線會卡進(個人有慘痛經驗)</font> |
如果只是要確認有無資料可在 ExecuteReaDer 後 使用 if(sdr.HasRow) 來判斷 ,,
新增 (Insert)
C#
|
1 |
<span style="color: #606060" id="lnum1"> 1:</span> SqlConnection conn = <span style="color: #0000ff">new</span> SqlConnection(ConfigurationManager.ConnectionStrings[<span style="color: #006080">"Conn"</span>].ToString()); |
|
1 |
<span style="color: #606060" id="lnum2"> 2:</span> <span style="color: #0000ff">string</span> cmdst = <span style="color: #006080">"INSERT INTO [EIP].[dbo].[sys_docs_modify] ([橺位1]"</span> + |
|
1 |
<span style="color: #606060" id="lnum3"> 3:</span> <span style="color: #006080">",[欄位2]) VALUES ('插入資料1','插入資料2')"</span>; <span style="color: #008000">//T_SQL 插入語法</span> |
|
1 |
<span style="color: #606060" id="lnum4"> 4:</span> SqlCommand scmd = <span style="color: #0000ff">new</span> SqlCommand(cmdst, conn); |
|
1 |
<span style="color: #606060" id="lnum5"> 5:</span> conn.Open(); |
|
1 |
<span style="color: #606060" id="lnum6"> 6:</span> scmd.ExecuteNonQuery(); <span style="color: #008000">//重點在這行!! 針對連接執行 Transact-SQL 陳述式</span> |
|
1 |
<span style="color: #606060" id="lnum7"> 7:</span> conn.Close(); <font color="#008000">//同上 , 有開就有關 !!</font> |
修改 , 刪除 請待續..... !!!
(Visited 33 times, 1 visits today)