Ado.net

asp.net
有兩種資料庫的
select command --> dataReader

dataadapter  -->ds

----------------------------------------------------------
DataSet  DataReader
Read/write access to data Read-only
Includes mutiple tables from differnt datavases  Based on one SQL statement fro one db
(because it is filled by adapter)

Slower Access 

Disconnected Connected
Bind to multiple controls  Bind to one control only
(把資料抓回來記憶體)

Supported by visual Studio .net tools  Manually coded

-------------------------------------------------------------------

Datagrid and a button

conn.Open(0;
sqlDataReader dr = da.SelectCommand.ExecuteReader();
DataGrid1.DataSource = dr;
DataGrid1.DataBind();
dr.Close();
conn.Close();

-----------------------------------------------------------------
Connecting to a DataBase

SQL Server Security

clinet (client) --> web server --> asp.net run -->mixed mode authentication(混合

Windows 認證
user 1 是個網域的帳號

because it is run  then we will check
The Authentification is doned by Windows but not by SQL

user1(ticket)票會

不是傳帳號密碼‧
--------------------------------------------------------------

mixed mode authenticaion用ㄧ個帳號

SQL server will used the user/password table to verify

兩部機器之間

VPN
IPSec 加密
--------------------------------------------------------------

設定SQL SERVER Enterprise Manager:

Security 可以看到所有的帳號

展開會看到安全性

登入

名稱                                 類型

類型是標準的就是混合式

--------------------------------------------------

\144.144.144.144    1433 port

理論上sql是可以跨網域的
只使用windows認證

Using SqlConnection
Dim strConn As String = "dat source=localhost; "

Provider = ??

Connection Timeout
Persist security info /保留固定

-----------------------------------------------------

name pipe

SQL網路公用程式
tcp/ip

用戶端網路公用程式

??? 活動專區 研討會
??? ADO .net 連線字串

Provider=SQLOLEDB

沒有provider, 不用透過第三者creative pure Provider.

-------------------------------------------------------------
ODBC 是掛上去的
driver 是個dll檔

------------------------------------------------------------
???
<debug>

SQL profiler

系統管理工具 /抓效能

-------------------------------------------------------------
效能會差到幾十倍，是架構或演算法

-------------------------------------------------------------

SqlDataAdapter
da.
如果

da.Fill(ds,"authors")
ds.Tables["0"].Rows.Count

--------------------------------------------------

dim dv as DataView
dv.RowFilter = "state='CA'";

DefaultView 會有sort filter

-----------------------------------------
要設datagrid:

dg.DataSource
dg.DataMember
Dg.DataBind();

--------------------------------------------

Connection will not open

***Connection string is invalid
***Server or database not found
***Login failed

Try catcht

DataAdapter cannot create a DataSet
***Invalid SQL comment

try
{

}
catch (System.Data.Sqlclient.SqlException
{
 switch(ex1.number)
 {
  case 17:
  156
  170

 }

}

catch (System.Exception)

---------------------------------------------------
表格關連

Multiple Tables
daCustomer
daOrders

da.fill

daCustomers = New SqlDataAdapter _
("select * from Customers"

---------------------------------------------------------

Dim parentCol as DataColumn = ds.Tables("Customers").columns

Dim chilcol As DataColumn = ds.Tables("Orders").Coumns("CustomersID")

dimr dr as new DataRelation ("name", prentcol, childCol)
ds.Relations.add(dr)

------------------------------------------------------------
寫程式
ds.Tables(index).Rows(index).GetChildRows("relation")
ds.Tables(index).Rows(index).GetParentRows("relation")

ds.Tables[index].Rows[index].GetChildRows("dr");

傳回來是DataRow[]的陣列

----------------------------------------------------

客戶     訂單明細      產品

1 對      多    多     對 1
Primary   Foriengn key

tableView = new Dataview(ds.Table["Customers"]);
currentRowView = tableView[dgcustomers.SelectIndex];
dgChild.DataSource

-----------------------------------------------------------
dg_SelectedIndexChanged(object sender, system.EventArgs e)
{

}

ds可以放多個 da進來

daOrders 設定delte 与insert

put two

dg_selectedIndexchanged

da.fill(ds);
daorders.Fill(ds);
DataRelation dr = new DataRelation("C2O", ds.Tables["Customers"].columns["CustomerId"], ds.Tables["Orders"].columns

["CustomerId"]);

ds.Realtions.Add(dr);
dgOrders.DataSource = ds.Tables["Customers"].Rows[dg.SelectedIndex].GetChildRows("C2O");
dsOrders.Merge(children);
dgOrders.DataSource=dsOrders.Table[0];
dgOrders.DataBind();

1.先把DataRelation mark 起來

2.再dataset1.xsd
給予 C20

3.從 customer把關聯拉到 orders去

XML Schema Definiation

.xsd .xml

G 代表 group  成績 有數學成績，國文成績
E 代表 element
A 代表 attribute
usertype
companytye

??? 分享  ADO.NET 与XML 整合應用

Customers 是個元素

<Age> </Age>

設定age 的範圍

DOM(Document Object Model): 處理樹狀

SOM(Schema Object Model) : 處理

--------------------------------------------------------------

DataReader
Forward-only, read-only
Fast access to data
Connected to a data source
Mange the connection yourself
Manage the data yourself, or bind it to a list-bound control
Uses fewer server resources

有點像以前的recordset

------------------------------------------------------------------

Sql connectoin
SqlCommand
conn.opn
SqlDataReader dr;
dr = cmdAuthros.ExecuteReader();

whle (dr.Read()
{

 lstBuiltNames.Items.Add(dr["au_lname"] + ", " + dr["au_fname"]);

}

dr.Close();
conn.Close();

轉型別

(int)dr[0]
dr.GetInt32(0);
dr.GetSql....(0);

只能讀不能改

DataRead一定要關
Conn才能關

Dataset才能關

-----------------------------------------------------------------------------

用號碼

dg.Datasource=dr();
dgAuthors.Databind();

-----------------------------------------------------------------------------
分頁
1. sql cursor
   fetch 第幾筆到第幾筆
 
2. 用select 語法 用序號加索引
 
select top 30 where ID Not In (select TOP 20 from tablename) from tablename

-----------------------------------------------------------------------------