DB2 VS2005.Net插件进行XML数据验证(3)
插入和验证 XML 数据
至此，已经建立了 DB2 连接并注册了 XML 模式，现在可以编写 SQL INSERT 或 UPDATE 语句，以便将新的 XML 数据插入到包含 XML 列的表中，并在插入 XML 数据之前，让 DB2 验证 XML 数据。DB2 可以存储最大为 2GB 的格式良好的任何 XML 文档。清单 6 展示了将一行插入到 CARPOOL 表中的一种方法。在这个例子中，插入到 CARPOOLINFO 列的 XML 文档是从字符串读取的。
清单 6. 插入和更新 XML 数据的方法
// Update the database based on the user's action in the dataGrid.
// Performs INSERT, UPDATE and DELETE.
private void update()
{
mydataSourceDT = carpoolInfo.DataSource as DataTable;
dtChanges = mydataSourceDT.GetChanges();
if (dtChanges == null)
return;
// Need to generate insert/update/delete commands to //validate against
// carpoolinfo.xsd
DB2Command insert = new DB2Command
("INSERT INTO CARPOOL" +
"(FIRSTNAME,LASTNAME,TITLE,PHONE,CARPOOLINFO)" +
" VALUES(?,?,?,?," +
"XMLVALIDATE(xmlparse (DOCUMENT CAST" +
" ( ? as CLOB) PRESERVE WHITESPACE )" +
"ACCORDING TO XMLSCHEMA ID " + schema + ".CARPOOLINFO ))");
DB2Command update = new DB2Command
("UPDATE CARPOOL SET FIRSTNAME=?,LASTNAME=?,TITLE=?,PHONE=?," +
"CARPOOLINFO=XMLVALIDATE(xmlparse (DOCUMENT CAST " +
"( ? as CLOB) PRESERVE WHITESPACE ) " +
"ACCORDING TO XMLSCHEMA ID " + schema + ".CARPOOLINFO ) WHERE ID=?");
DB2Command delete = new DB2Command
("DELETE FROM CARPOOL WHERE ID=?");
//Add the Parameters and bind them to the DataTable's //corresponding columns.
DB2Parameter fn1 = new DB2Parameter("fn1", DB2Type.VarChar);
DB2Parameter fn2 = new DB2Parameter("fn2", DB2Type.VarChar);
fn1.SourceColumn = "FIRSTNAME";
fn2.SourceColumn = "FIRSTNAME";
insert.Parameters.Add(fn1);
update.Parameters.Add(fn2);
DB2Parameter ln1 = new DB2Parameter("ln1", DB2Type.VarChar);
DB2Parameter ln2 = new DB2Parameter("ln2", DB2Type.VarChar);
ln1.SourceColumn = "LASTNAME";
ln2.SourceColumn = "LASTNAME";
insert.Parameters.Add(ln1);
update.Parameters.Add(ln2);
DB2Parameter tl1 = new DB2Parameter("tl1", DB2Type.VarChar);
DB2Parameter tl2 = new DB2Parameter("tl2", DB2Type.VarChar);
tl1.SourceColumn = "TITLE";
tl2.SourceColumn = "TITLE";
insert.Parameters.Add(tl1);
update.Parameters.Add(tl2);
DB2Parameter ph1 = new DB2Parameter("ph1", DB2Type.VarChar);
DB2Parameter ph2 = new DB2Parameter("ph2", DB2Type.VarChar);
ph1.SourceColumn = "PHONE";
ph2.SourceColumn = "PHONE";
insert.Parameters.Add(ph1);
update.Parameters.Add(ph2);
DB2Parameter info1 = new DB2Parameter("info1", DB2Type.Clob);
DB2Parameter info2 = new DB2Parameter("info2", DB2Type.Clob);
info1.SourceColumn = "CARPOOLINFO";
info2.SourceColumn = "CARPOOLINFO";
insert.Parameters.Add(info1);
update.Parameters.Add(info2);
DB2Parameter i1 = new DB2Parameter("i1", DB2Type.Integer);
i1.SourceColumn = "ID";
update.Parameters.Add(i1);
DB2Parameter i2 = new DB2Parameter("i2", DB2Type.Integer);
i2.SourceColumn = "ID";
delete.Parameters.Add(i2);
da.InsertCommand = insert;
da.UpdateCommand = update;
da.DeleteCommand = delete;
// Perform the update.
da.Update(dtChanges);
mydataSourceDT.AcceptChanges();
// Refill the dataset, refresh the dataGridView.
ds.Clear();
da.Fill(ds, xsdname);
}
现在来看看这段代码。在建立数据库连接之后，该方法创建三个 DB2Command;一个用于插入，一个用于更新，还有一个用于删除。插入和更新命令包含 4 个用于常规列值的参数占位符，而第 5 个参数占位符则用于 XML 列，该方法还使用 DB2 XMLVALIDATE 函数，并将 Carpoolinfo XML 模式传递给它以便进行验证。