样例中使用的xml文件:xmlsample.xml
<music>
<song>
<id>0</id>
<artist>The Chi-lites</artist>
<genre>Soul</genre>
<album>A lonely man</album>
<year>1972</year>
</song>
<song>
<id>1</id>
<artist>Babyface</artist>
<genre>R&B</genre>
<album>unknown</album>
<year></year>
</song>
<song>
<id>2</id>
<artist>Babyface</artist>
<genre>R&B</genre>
<album>The essential babyface</album>
<year>2001</year>
</song>
<song>
<id>3</id>
<artist>Babyface</artist>
<genre>R&B</genre>
<album>Grown and sexy</album>
<year>2005</year>
</song>
<song>
<id>4</id>
<artist>Maria Arredondo</artist>
<genre>Pop</genre>
<album>Not going under</album>
<year>2004</year>
</song>
<song>
<id>5</id>
<artist>Leona Lewis</artist>
<genre>Pop</genre>
<album>Unknown</album>
<year>2008</year>
</song>
<song>
<id>6</id>
<artist>Usher</artist>
<genre>R&B</genre>
<album>Usher</album>
<year>2008</year>
</song>
<song>
<id>7</id>
<artist>Christina Aguilera</artist>
<genre>Blues</genre>
<album>Back to basics</album>
<year>2004</year>
</song>
<song>
<id>8</id>
<artist>Sting</artist>
<genre>Pop</genre>
<album>Shape of my heart</album>
<year></year>
</song>
</music>
样例原码：
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
//参考：http://support.microsoft.com/kb/308507/zh-cn
class Program
{
static List<DataRowState> list = new List<DataRowState>();
static void Main(string[] args)
{
//按xmlsample.xml结构建表(ID定义为主键，否则不更新)，利用下面的方法把Xml数据入库
AdapterInsert(XmlToDataTableByFile());
SqlDataAdapterMain();
}
/// <summary>
/// 利用SqlDataAdapter Update方法，把指定的DataTable插入到表中。
/// </summary>
/// <param name="dt"></param>
static void AdapterInsert(DataTable dt)
{
using (SqlConnection conn = new SqlConnection("Server=localhost;DataBase=B2B;Uid=sa;Pwd=ll;"))
{
SqlCommand com = conn.CreateCommand();
com.CommandText = "Insert Into Song (id,artist,genre,album,year) Values (@id,@artist,@genre,@album,@year)";
//SqlDbType.VarChar的长度如果定义小了，将自动截掉，不象字段定义小了提示截掉错误。
com.Parameters.Add("@id", SqlDbType.VarChar, 20, "id");
com.Parameters.Add("@artist", SqlDbType.VarChar, 20, "artist");
com.Parameters.Add("@genre", SqlDbType.VarChar, 20, "genre");
com.Parameters.Add("@album", SqlDbType.VarChar, 20, "album");
com.Parameters.Add("@year", SqlDbType.VarChar, 20, "year");
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = com;
//adapter.Update没有事物机制，还需要定义事物。
//第一种方法
//需要引用System.Transactions
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
{
try
{
adapter.Update(dt);
scope.Complete();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
//第二种方法
//conn.Open();
//SqlTransaction tran = conn.BeginTransaction("Song");
//com.Transaction = tran;
//SqlDataAdapter adapter = new SqlDataAdapter();
//adapter.InsertCommand = com;
//try
//{
// adapter.Update(dt);
// tran.Commit();
//}
//catch (Exception e)
//{
// tran.Rollback("Song");
// Console.WriteLine(e.Message);
//}
}
}
/// <summary>
/// SqlDataAdapter入口
/// </summary>
static void SqlDataAdapterMain()
{
SqlConnection connection = new SqlConnection("Server=localhost;DataBase=B2B;Uid=sa;Pwd=ll;");
SqlDataAdapter adapter = CreateSqlDataAdapter(connection);
DataTable dt = DataAdapterFill(connection, adapter);
foreach (DataRow row in dt.Rows)
{
list.Add(row.RowState);
}
UpdateRows(connection, adapter, dt);
}
//自动生成命令
static SqlDataAdapter CreateSqlDataAdapter(SqlConnection connection)
{
SqlCommand com = connection.CreateCommand();
com.CommandText = "Select id,artist,genre,album,year From Song Where year >= @pyear";
//定义参数的值
com.Parameters.Add("@pyear",SqlDbType.VarChar).Value = "2004";
SqlDataAdapter adapter = new SqlDataAdapter(com);
//使用“CommandBuilder”对象自动生成“DataAdapter”对象的“DeleteCommand”、“InsertCommand”和“UpdateCommand”属性。
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
//生成的命令语句
//string text = builder.GetUpdateCommand().CommandText;
return adapter;
}
//自定义命令
static SqlDataAdapter CreateCustomerAdapter(SqlConnection connection)
{
SqlDataAdapter adapter = new SqlDataAdapter();
//选择命令
SqlCommand com = new SqlCommand("Select id,artist,genre,album,year From Song Where year >= @pyear",connection);
com.Parameters.Add("@pyear",SqlDbType.VarChar).Value = "2004";
adapter.SelectCommand = com;
//更新命令
com = new SqlCommand("Update Song Set artist = @partist Where id = @pid", connection);
//参数@partist的值为DataTable的artist列值
com.Parameters.Add("@partist", SqlDbType.VarChar, 20, "artist");
com.Parameters.Add("@pid", SqlDbType.VarChar, 20, "id");
adapter.UpdateCommand = com;
return adapter;
}
static DataTable DataAdapterFill(SqlConnection connection, SqlDataAdapter adapter)
{
DataTable dt = new DataTable();
try
{
adapter.Fill(dt);
}
catch (Exception e)
{
connection.Close();
connection.Dispose();
}
return dt;
}
//新增行，完成后才能关闭连接
static void AddRows(SqlConnection connection, SqlDataAdapter adapter, DataTable dataTable)
{
DataRow row = dataTable.NewRow();
row["artist"] = "artist";
row["genre"] = "genre";
row["album"] = "genre";
row["year"] = "2010";
dataTable.Rows.Add(row);
try
{
adapter.Update(dataTable);
}
catch (Exception e)
{
}
finally
{
//全程只能在这关闭连接，放回到连接池，其它时间连接不能关闭。
connection.Close();
connection.Dispose();
}
}
//更新行
static void UpdateRows(SqlConnection connection, SqlDataAdapter adapter, DataTable dataTable)
{
//如果这时在目标表中删除这一行，在执行Update时，将引发 DBConcurrencyException 异常
dataTable.Rows[0]["genre"] = "xxxxx";
try
{
adapter.Update(dataTable);
}
catch (Exception e)
{
}
finally
{
//全程只能在这关闭连接，放回到连接池。
connection.Close();
connection.Dispose();
}
}
// Xml结构的文件读到DataTable中
static DataTable XmlToDataTableByFile()
{
string fileName = "E:\\xmlsample.xml";
XmlDocument doc = new XmlDocument();
doc.Load(fileName);
DataTable dt = new DataTable("song");
//以第一个元素song的子元素建立表结构
XmlNode songNode = doc.SelectSingleNode("/music/song[1]");
string colName;
if (songNode != null)
{
for (int i = 0; i < songNode.ChildNodes.Count; i++)
{
colName = songNode.ChildNodes.Item(i).Name;
dt.Columns.Add(colName);
}
}
DataSet ds = new DataSet("music");
ds.Tables.Add(dt);
//Xml所有song元素的子元素读到表song中,当然用dt也可以读。
ds.ReadXml(fileName);
return dt;
}
}
