1、ADO.NET相關物件一句話介紹
1)DataAdapter:
DataAdapter實際是一個SQL語句集合,因為對Database的操作最終需要歸結到SQL語句。
2)Dataset:
DataSet可以理解成若干DataTable的集合,DataSet在記憶體裡面維護一個表集合包括表間關係。對於.NET Framework 2.0之前的版本,DataSet在ADO.NET中擁有至關重要的作用,但在其後的版本中,由於DataTable類的完備(例如與XML相關的幾個方法以及Merge方法),其作用稍有削弱,甚至於有些情況下你去初始化一個DataSet物件本身就是多餘的。
3)DataView:
與資料庫中的檢視在概念上是類似的。DataView本身並不真正包含資料行,而只是包含指向源DataTable中資料行的引用,這一點你可以通過object.ReferenceEquals()方法來驗證。
4)DataTable:
ADO.NET的核心物件。它是位於記憶體中的一張表,是你執行SQL查詢之後的結果集,可以形象地把它理解為一張包含若干行若干列的表格。
2、如何更新資料到Database
從本質上來說,你對Database操作總是歸結到SQL語句,但是從表面上我們可以作一點區分,
1)直接使用SQL命令
在.NET中,最常見的是拼接SQL字串,使用Command物件來執行此命令以達到操作Database的目的,例如,
string sql = "update table1 set fvalue=" + this.textBox1.Text + " where fname='x'";
SqlCommand cmd = new SqlCommand(sql,conn);
cmd.ExecuteNonQuery();
這是一種最直接淺顯的方式,因為SQL語句就在你眼前,反過來說,這需要你對SQL命令有一定的瞭解。
2)使用DataAdapter.Update()
另外一種方式,是使用DataAdapter.Update()方法,這並不是說我們不需要SQL語句了,只是SQL語句拼接的工作已經交給了DataAdapter(實際上是交給了CommandBuilder)來完成(以引數的形式),例如,
string c = "select fname,fvalue from table1";
SqlCommand cmd = new SqlCommand(c,conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlCommandBuilder scb = new SqlCommandBuilder(da); //(1)
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0].Delete();//(2)
da.Update(dt);
在這裡,你看不到SQL語句,因為在你初始化SqlCommandBuilder的過程中,將自動根據表結構(基於你的Select語句)構造insert,update,delete語句。對於上面的程式碼,你可以獲得SQL語句內容,
DELETE FROM [table1] WHERE (([fname] = @p1) AND ((@p2 = 1 AND [fvalue] IS NULL) OR ([fvalue] = @p3)))
而執行時候,會傳入相應的引數值,
exec sp_executesql N'DELETE FROM [table1] WHERE (([fname] = @p1) AND ((@p2 = 1 AND [fvalue] IS NULL) OR ([fvalue] = @p3)))',N'@p1 varchar(1),@p2 int,@p3 int',@p1='a',@p2=0,@p3=100
或
xec sp_executesql N'DELETE FROM [table1] WHERE (([fname] = @p1) AND ((@p2 = 1 AND [fvalue] IS NULL) OR ([fvalue] = @p3)))',N'@p1 varchar(1),@p2 int,@p3 int',@p1='b',@p2=1,@p3=NULL
由於表中只有兩個列,列fname為主鍵列,fvalue列可空,至於為什麼會出現三個引數,看看上面的SQL你就會明白了。
以下則分別是update語句、insert語句,
UPDATE [table1] SET [fname] = @p1, [fvalue] = @p2 WHERE (([fname] = @p3) AND ((@p4 = 1 AND [fvalue] IS NULL) OR ([fvalue] = @p5)))
INSERT INTO [table1] ([fname], [fvalue]) VALUES (@p1, @p2)
另外,上述C#程式碼中的dt.Rows[0].Delete()行寫在這裡只是示例作用,實際的系統中,你可能會有一個叫“Delete”的按鈕,這樣你可以在按鈕的事件中執行Delete()操作,然後叫某個叫“Save”的按鈕裡寫上Update(),這很常見,不多說了。
再另外,由於這些語句的構造過程中依賴於你的Select語句,所以你的Select語句中必須包含主鍵列,否則無法正常生成其它SQL命令。
以下我們的討論,將主要針對第二種方式,即使用Update()進行資料更新過程中涉及的各種問題。
3、行狀態
為了後續的資料操作,DataTable中引入了一個“行狀態”的概念(事實上該屬性屬於DataRow類)。每一個DataRow都有一個狀態標誌,你可以通過DataTable.Rows[i].RowState檢視,對DataRow的不同操作將導致該行處於不同的狀態,同時,不同的狀態又導致儲存資料時的不同行為。參見下圖,
1)初始狀態差異
從資料庫中查詢並通過DataAdapter.Fill()方法填充的DataTable,其所有行的狀態初始都為Unchanged(我們可以認為在Fill()方法的內部呼叫了AcceptChanges()方法),然而對於在程式中手工構造並新增的資料行,在未接受AcceptChanges()方法前,都為Added(行狀態的不同在DataTable中是一個比較隱蔽的但又需要十分關注的問題,後續會有相應的說明),參見以下程式碼。
private void button1_Click(object sender, EventArgs e)
{
try
{
dataAdapter1.Fill(dt);
DataRowState s = dt.Rows[0].RowState;//unchanged
}
catch
{
}
}
private void button2_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("fname");
dt.Columns.Add("fvalue");
dt.Rows.Add("zhang", 100);
DataRowState s = dt.Rows[0].RowState;//added
}
2)理解Delete()
此方法並未真正移除DataRow(除非此行原狀態為Added),而只是將RowState狀態變成了Deleted(當然這會導致你無法使用正常的索引方式訪問此行的資料)。對於Added狀態的行執行Delete()操作,將導致DataTable行數減少,這點需要注意,因為它可能導致你在使用for迴圈遍歷時出現索引越界異常。
private void button7_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("fname");
dt.Columns.Add("fvalue");
dt.Rows.Add("zhang", 100);
//
dt.Rows[0].Delete();
int c = dt.Rows.Count;//0
}
private void button8_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("fname");
dt.Columns.Add("fvalue");
dt.Rows.Add("zhang", 100);
//
dt.AcceptChanges();
dt.Rows[0].Delete();
int c = dt.Rows.Count;//1
}
3)Exception:Deleted row information cannot be accessed through the row.
private void button8_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("fname");
dt.Columns.Add("fvalue");
dt.Rows.Add("zhang", 100);
//
dt.AcceptChanges();
dt.Rows[0].Delete();
DataRow dr = dt.Rows[0]; //No error
object o = dt.Rows[0]["fvalue"];//Exception,row can be accessed,but row data cannot
}
4)理解AcceptChanges()
此方法容易給人誤解,以為在呼叫它之後對DataTable所做的所有更改將會被提交到Database。事實上,此方法跟Database沒有直接的關係(注意),它只直接影響各DataRow的RowState(具體地說來是將所有狀態為Deleted的行真正移除,所有狀態為Added或Modified的行都變成Unchanged)。與Database有直接相關的是DataAdapter.Update()方法,它是真正負責執行相關SQL命令的地方。
但是,從另一方面來說,沒有直接的影響,言外之意就是有間接的影響,由於它影響了所有DataRow的RowState,而DataAdapter.Update()方法在執行SQL命令時必須依據RowState以確定使用insert、update、或delete 命令。舉個例子,如果你在DataAdapter.Update()呼叫之前執行AcceptChanges()方法,這將阻止所有對Database的更改,因此對這兩個方法呼叫的順序應有充分的考慮。
另外,DataSet、DataTable、DataRow都有AcceptChanges()方法,這些方法除了影響的範圍大小不同之外,沒有本質的區別。
5)DataRowState與Update()
不同的資料行狀態,將導致最終DataAdapter.Update()出現不同的行為,例如對於Added狀態的行,將導致insert操作、Modified狀態將導致update操作、Deleted狀態將導致delete操作。
6)使用DataRowState
除了Update()方法內部使用DataRowState外,在我們自己寫的程式碼中,也可以將它與GetChanges()方法配合使用,以獲取DataTable的當前變化,參見以下程式碼,在你獲得所有發生更新的行後,實際上你可以自己構造Update SQL命令,而不使用CommandBuilder,當然這需要用到稍後會提到的DataRowVersion。
private void button4_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("fname");
dt.Columns.Add("fvalue");
dt.Rows.Add("zhang", 100);
dt.AcceptChanges();
dt.Rows[0]["fvalue"] = 101
//get all Modified rows,then you can use UPDATE SQL to save data.
DataTable dt1 = dt.GetChanges(DataRowState.Modified);
}
7)狀態Detached
除了上圖中給出的幾種行狀態外,還有一種特殊的狀態Detached,這種狀態表示已初始化但未新增到DataTable中的資料行,此狀態我們不必太關心。參見,
private void button3_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("fname");
dt.Columns.Add("fvalue");
DataRow dr = dt.NewRow();
DataRowState s = dr.RowState;//detached
}
4、行狀態、行版本、行資料版本
行版本(DataRowVersion)描述資料行的版本;
行資料版本(DataViewRowState)描述資料行中資料的版本。
這兩個概念令人困惑,我認為可以僅僅從用法上對它們進行了解,畢竟我們使用它們的機會並非很大。
1)使用DataRowVersion
關於DataRowVersion,以狀態為Modified的行為例,它包含兩個DataRowVersion(即儲存了兩行資料):Current,Original,分別儲存該行修改後與修改前的資料,也就是說,行版本實際可以幫助RejectChanges()等方法實現一個類似於“回滾”的功能。
private void button4_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("fname");
dt.Columns.Add("fvalue");
dt.Rows.Add("zhang", 100);
dt.AcceptChanges();
dt.Rows[0]["fvalue"] = 101;
int i = Convert.ToInt32(dt.Rows[0]["fvalue", DataRowVersion.Original]);//100
int i2 = Convert.ToInt32(dt.Rows[0]["fvalue", DataRowVersion.Current]);//101
}
同理你可以藉助DataRowVersion來訪問Deleted的資料,前面我們提到了對於Deleted的資料,使用dt.Rows[0]["fvalue"]訪問將引發異常,可以使用
dt.Rows[0]["fvalue", DataRowVersion.Original]。
2) DataRowVersion與Update()
現在我們回想一下,當我們使用CommandBuilder構造完Update,Insert,Delete命令之後,那些SQL命令中的引數怎麼辦?我們知道在SQL命令執行之前,我們必須為所有輸入引數指定引數值,那麼Update()方法內部是如何工作的?這就有賴於DataRowVersion了。
我們可以簡單看一下Update()方法使用過程中涉及的相關.NET原始碼,
System.Data.Common.DbDataAdapter
protected virtual int Update(DataRow[] dataRows, DataTableMapping tableMapping);
在Update()方法中,呼叫了ParameterInput(),下面是該方法的摘要,
System.Data.Common.DbDataAdapter
private void ParameterInput(IDataParameterCollection parameters, StatementType typeIndex, DataRow row, DataTableMapping mappings)
{
foreach (IDataParameter parameter in parameters)
{
if (column != null)
{
DataRowVersion parameterSourceVersion = GetParameterSourceVersion(typeIndex, parameter);
parameter.Value = row[column, parameterSourceVersion];
}
}
}
在ParameterInput()方法中,呼叫了GetParameterSourceVersion()方法,
System.Data.Common.DbDataAdapter
private static DataRowVersion GetParameterSourceVersion(StatementType statementType, IDataParameter parameter)
{
switch (statementType)
{
case StatementType.Select:
case StatementType.Batch:
throw ADP.UnwantedStatementType(statementType);
case StatementType.Insert:
return DataRowVersion.Current;
case StatementType.Update:
return parameter.SourceVersion;
case StatementType.Delete:
return DataRowVersion.Original;
}
throw ADP.InvalidStatementType(statementType);
}
以行被更新的情況為例,在為引數的賦值的過程中,系統會將相應要更新的DataRow一併傳入,同時對於Update語句,
UPDATE [table1] SET [fname] = @p1, [fvalue] = @p2 WHERE (([fname] = @p3) AND ((@p4 = 1 AND [fvalue] IS NULL) OR ([fvalue] = @p5)))
我們要了解的一點是,5個引數中@p1,@p2是一類,@p3, @p5是一類,它們的區別在於,前一類的SourceVersion是Current,而後一類的SourceVersion是Original,這在上述的GetParameterSourceVersion()方法中被用到,所以!!,針對傳入的需要更新的DataRow,Update()方法內部將使用當前值(即修改後的值)填充@p1,@p2,而使用原始值(即修改前的值)填充@p3, @p5。Insert,delete同理。
3)理解DataRowVersion.Default
對於Added、Modified、Deleted狀態的行,其Default版本實際是Current版本,對於Unchanged則無甚區別。
4)使用DataViewRowState
(1)配合DataTable.Select()
private void button9_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("fname");
dt.Columns.Add("fvalue");
dt.Rows.Add("zhao", 100);
dt.Rows.Add("qian", 100);
dt.Rows.Add("sun", 100);
dt.AcceptChanges();
//
dt.Rows[1]["fvalue"] = 101;
dt.Rows[2].Delete();
dt.Rows.Add("li", 100);
//object o = dt.Rows[2]["fvalue", DataRowVersion.Original];
//
StringBuilder sb = new StringBuilder();
DataRow[] drs = dt.Select(null, null, DataViewRowState.Added);
sb.AppendLine("-----------------------------------------------");
sb.AppendLine("Added:");
for (int i = 0; i < drs.Length; i++)
{
sb.AppendLine(drs[i]["fname"].ToString() +": "+ drs[i]["fvalue"].ToString());
}
drs = dt.Select(null, null, DataViewRowState.CurrentRows);
sb.AppendLine("-----------------------------------------------");
sb.AppendLine("CurrentRows:");
for (int i = 0; i < drs.Length; i++)
{
sb.AppendLine(drs[i]["fname"].ToString() + ": " + drs[i]["fvalue"].ToString());
}
drs = dt.Select(null, null, DataViewRowState.Deleted);
sb.AppendLine("-----------------------------------------------");
sb.AppendLine("Deleted:");
for (int i = 0; i < drs.Length; i++)
{
sb.AppendLine(drs[i]["fname", DataRowVersion.Original].ToString() + ": " + drs[i]["fvalue", DataRowVersion.Original].ToString());
}
drs = dt.Select(null, null, DataViewRowState.ModifiedCurrent);
sb.AppendLine("-----------------------------------------------");
sb.AppendLine("ModifiedCurrent:");
for (int i = 0; i < drs.Length; i++)
{
sb.AppendLine(drs[i]["fname"].ToString() + ": " + drs[i]["fvalue"].ToString());
}
drs = dt.Select(null, null, DataViewRowState.ModifiedOriginal);
sb.AppendLine("-----------------------------------------------");
sb.AppendLine("ModifiedOriginal:");
for (int i = 0; i < drs.Length; i++)
{
sb.AppendLine(drs[i]["fname"].ToString() + ": " + drs[i]["fvalue"].ToString());
}
drs = dt.Select(null, null, DataViewRowState.OriginalRows);
sb.AppendLine("-----------------------------------------------");
sb.AppendLine("OriginalRows:");
for (int i = 0; i < drs.Length; i++)
{
if (drs[i].RowState == DataRowState.Deleted)
{
sb.AppendLine(drs[i]["fname", DataRowVersion.Original].ToString() + ": " + drs[i]["fvalue", DataRowVersion.Original].ToString());
}
else
{
sb.AppendLine(drs[i]["fname"].ToString() + ": " + drs[i]["fvalue"].ToString());
}
}
drs = dt.Select(null, null, DataViewRowState.Unchanged);
sb.AppendLine("-----------------------------------------------");
sb.AppendLine("Unchanged:");
for (int i = 0; i < drs.Length; i++)
{
sb.AppendLine(drs[i]["fname"].ToString() + ": " + drs[i]["fvalue"].ToString());
}
MessageBox.Show(sb.ToString());
}
結果輸出:
-----------------------------------------------
Added:
li: 100
-----------------------------------------------
CurrentRows:
zhao: 100
qian: 101
li: 100
-----------------------------------------------
Deleted:
sun: 100
-----------------------------------------------
ModifiedCurrent:
qian: 101
-----------------------------------------------
ModifiedOriginal:
qian: 101
-----------------------------------------------
OriginalRows:
zhao: 100
qian: 101
sun: 100
-----------------------------------------------
Unchanged:
zhao: 100
(2)配合DataView.RowFilter
private void button10_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("fname");
dt.Columns.Add("fvalue");
dt.Rows.Add("zhao", 100);
dt.Rows.Add("qian", 100);
dt.Rows.Add("sun", 100);
dt.AcceptChanges();
//
dt.Rows[1]["fvalue"] = 101;
dt.Rows[2].Delete();
dt.Rows.Add("li", 100);
//
DataView dv = new DataView(dt);
dv.RowStateFilter = DataViewRowState.Added | DataViewRowState.ModifiedCurrent;
StringBuilder sb = new StringBuilder();
sb.AppendLine("-----------------------------------------------");
sb.AppendLine("Added & ModifiedCurrent:");
for (int i = 0; i < dv.Count; i++)
{
sb.AppendLine(dv[i]["fname"].ToString() + ": " + dv[i]["fvalue"].ToString());
}
sb.AppendLine("-----------------------------------------------");
MessageBox.Show(sb.ToString());
}
//-----------------------------------------------
Added & ModifiedCurrent:
qian: 101
li: 100
-----------------------------------------------
5)DataViewRowState中的“複合版本”
DataViewRowState包含多個列舉成員,我可以給出每個列舉成員對應的int值,
Added 4
CurrentRow 22
Deleted 8
ModifiedCurrent 16
ModifiedOriginal 32
None 0
OriginalRow 42
Unchanged 2
你可以發現,其中的兩個狀態CurrentRow、OriginalRow實際是經由其它幾種狀態二進位制或運算的結果,
CurrentRow=Added|ModifiedCurrent|Unchanged,
OriginalRow=Deleted|ModifiedOriginal|Unchanged。
5、瞭解其它幾個方法
1)Delete()、Remove()、Clear()
DataRow.Delete();
DataRowCollection.Remove();
DataTable.Clear()、DataSet.Clear()
正如前面所述,對於DataRow的Delete()方法,其內部的處理並未真正刪除此行,而只是將行標識為Deleted,並“移除”了它的Current版本。這樣,當使用DataAdapter的Update()進行更新時,其內部機制可以根據仍然存在的Original版本資料,為DeleteCommand填充引數,完成更新資料庫的操作。
而Clear()方法則完全刪除了堆上的資料行物件,並且將對資料引用置空(這點可以參見Clear()方法的反編譯程式碼),這種情況下無法生成可執行的DeleteCommand,這就是說,當你用Clear()方法“清空”DataTable後,使用Update()方法並不能像你預想的一樣將對應的資料庫表資料刪除。
另外,需要注意一點是Delete()並不導致資料行減少(除非原行是Added狀態),當然,如果是對Added狀態的行執行Delete(),則導致行數減少。當你使用for迴圈時,這可能會造成問題。
另外,我們還有一個方法:DataRowCollection.Remove(),其作用類似於Clear(),是徹底地移除行,假設你是使用DataAdapter.Update()方法更新Database,那麼你將沒有機會將你的刪除操作同步到Database中。
2)Copy()、Clone()
.NET中有兩類拷貝,淺拷貝(Shadow copy)、深拷貝(Deep copy),對於大多數我們所見的類(比如常見的集合類等等),沒有深拷貝方法,大多數會有一個淺拷貝方法Clone()。我唯一所見的一個深拷貝方法是DataTable.Copy(),同時,DataTable.Clone()方法也比較特殊,因為它並非是淺拷貝方法,而是拷貝DataTable的結構(不包含資料)。
順便提一下深、淺拷貝的區別,淺拷貝建立原物件型別的一個新例項,複製原物件的所有值型別成員,對於引用型別成員,則只複製該值的指標。深拷貝則複製原物件的所有成員,對於引用型別成員,亦複製其所指的堆上的物件。
static void Main(string[] args)
{
DataTable dt = new DataTable();
dt.Columns.Add("fname", typeof(System.String));
dt.Columns.Add("fvalue", typeof(System.Int32));
for (int i = 1; i <= 10; i++)
{
dt.Rows.Add("p" + i, i);
}
dt.AcceptChanges();
//
DataTable dtc = dt.Copy();
//
bool b = object.ReferenceEquals(dt.Rows[0], dtc.Rows[0]);//false
DataRowState s = dtc.Rows[0].RowState;//Unchanged
//Clone() and ImportRow()
DataTable dtc2 = dt.Clone();
for (int i = 0; i < 5; i++)
{
dtc2.ImportRow(dt.Rows[i]);
}
bool b2 = object.ReferenceEquals(dt.Rows[0], dtc2.Rows[0]);//false
DataRowState s2 = dtc2.Rows[0].RowState;//Unchanged
//ItemArray
DataTable dtc3 = dt.Clone();
for (int i = 0; i < 5; i++)
{
dtc3.Rows.Add(dt.Rows[i].ItemArray);
//dtc3.Rows.Add(dt.Rows[i]);//run time exception
}
bool b5 = object.ReferenceEquals(dt.Rows[0], dtc3.Rows[0]);//false
DataRowState s5 = dtc3.Rows[0].RowState;//Added
//
ArrayList al = new ArrayList();
al.Add("xy");
ArrayList alc = al.Clone() as ArrayList;
if (alc != null)
{
bool b3 = object.ReferenceEquals(al, alc);//false
bool b4 = object.ReferenceEquals(al[0], alc[0]);//true
}
}
3)Select()、Compute()
這兩個方法在很多情況下都有助於你簡化程式碼,避免每一次使用迴圈遍歷DataTable,參見以下,
對於這兩個方法中可用的表示式,參見,
http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx
class Program
{
static void Main(string[] args)
{
DataTable dt = new DataTable();
dt.Columns.Add("fname",typeof(System.String));
dt.Columns.Add("fvalue",typeof(System.Int32));
for (int i = 1; i <= 10; i++)
{
dt.Rows.Add("p" + i, i);
}
dt.AcceptChanges();
//
DataRow[] drs = dt.Select("fvalue>6");
PrindRows(drs);
//
drs = dt.Select("fvalue>6 and fvalue<9");//AND OR NOT
PrindRows(drs);
//
drs = dt.Select("fname like 'p1%'");
PrindRows(drs);
//
drs = dt.Select("fname in ('p1','p3')");//< > <= >= <> = IN LIKE
PrindRows(drs);
//
drs = dt.Select("fvalue=max(fvalue)");//SUM AVG MIN MAX COUNT STDEV VAR
PrindRows(drs);
//
drs = dt.Select("fvalue%2=0");//+ - * / %
PrindRows(drs);
//
drs = dt.Select("len(fname)=3");//LEN(expression) ISNULL(expression, replacementvalue) IIF(expr, truepart, falsepart) TRIM(expression) SUBSTRING(expression, start, length)
PrindRows(drs);
object o = dt.Compute("count(fname)", "fvalue>6");//4
Console.WriteLine(o.ToString());
Console.Read();
}
static void PrindRows(DataRow[] pDrs)
{
Console.WriteLine("----------------------------------");
foreach (DataRow dr in pDrs)
{
Console.WriteLine(dr["fname"].ToString().PadRight(8, ' ') + dr["fvalue"].ToString());
}
Console.WriteLine("----------------------------------");
}
}
----------------------------------
p7 7
p8 8
p9 9
p10 10
----------------------------------
----------------------------------
p7 7
p8 8
----------------------------------
----------------------------------
p1 1
p10 10
----------------------------------
----------------------------------
p1 1
p3 3
----------------------------------
----------------------------------
p10 10
----------------------------------
----------------------------------
p2 2
p4 4
p6 6
p8 8
p10 10
----------------------------------
----------------------------------
p10 10
----------------------------------