样例中使用的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; } }