.NET SQLServer2005 XML字段

//这是添加?

private void button1_Click(object sender,EventArgs e)
??????? {
??????????? string s = "Persist Security Info=False;Integrated Security=SSPI;database=IIntegration;server=(local)";
??????????? using (SqlConnection conn = new SqlConnection(s))
??????????? {
??????????????? using (SqlCommand cmd1 = new SqlCommand("INSERT testxml VALUES(@ID,@XmlData)",conn))
??????????????? {
??????????????????? conn.Open();

??????????????????? cmd1.Parameters.Add("@ID",SqlDbType.Int,4);
??????????????????? cmd1.Parameters[0].Value = 2;

??????????????????? cmd1.Parameters.Add("@XmlData",SqlDbType.Xml);

??????????????????? cmd1.Parameters[1].Value = GetXmlData();

??????????????????? cmd1.ExecuteNonQuery();

?????????????????
??????????????? }
??????????? }
??????? }

??????? private SqlXml GetXmlData()
??????? {
??????????? DataTable dt = new DataTable("B_Quality");

??????????? dt.Columns.Add("Q_Name",typeof(string));

??????????? dt.Columns.Add("Q_Symbol",typeof(string));

??????????? dt.Columns.Add("Q_Value",typeof(string));

??????????? DataRow dr = dt.NewRow();

??????????? dr["Q_Name"] = "水分";
??????????? dr["Q_Symbol"] = ">";
??????????? dr["Q_Value"] = "15";

??????????? dt.Rows.Add(dr);

??????????? MemoryStream ms = new MemoryStream();

??????????? dt.WriteXml(ms); //,XmlWriteMode.WriteSchema
??????????
??????????? return new SqlXml(ms);

?

//
??????? }

?

//查看

?string s = "Persist Security Info=False;Integrated Security=SSPI;database=IIntegration;server=(local)";
??????????? using (SqlConnection conn = new SqlConnection(s))
??????????? {
??????????????? using (SqlCommand cmd1 = new SqlCommand("SELECT * FROM testxml",conn))
??????????????? {
??????????????????? conn.Open();

??????????????????? SqlDataReader sdr = cmd1.ExecuteReader();

??????????????????? //DataTable dt = sdr.GetSchemaTable();

??????????????????? DataTable dt = new DataTable();

??????????????????? if (sdr.Read())
??????????????????? {
??????????????????????? SqlXml sql = sdr.GetSqlXml(1);

??????????????????????? XmlReader xr = sql.CreateReader();

??????????????????????? dt.ReadXml(xr);
???????????????????????
??????????????????? }

??????????????????? this.dataGridView1.AutoGenerateColumns = true;

??????????????????? this.bindingSource1.DataSource = dt;

???????????????????
??????????????? }
??????????? }

?

很简单，参照下面的微软代码

?using (SqlConnection conn = new SqlConnection(s))

??????????? {

??????????????? using (SqlCommand cmd1 = new SqlCommand("INSERT xmltab(xmlcol) VALUES(@x)",conn))

??????????????? using (SqlCommand cmd2 = new SqlCommand(" insert_xml",conn))

??????????????? {

??????????????????? string str = "<somedoc/>";

?

??????????????????? conn.Open();

?

??????????????????? // server-side conversion

??????????????????? cmd1.Parameters.Add("@x",SqlDbType.NVarChar);

??????????????????? cmd1.Parameters[0].Value = str;

??????????????????? cmd1.ExecuteNonQuery();

?

??????????????????? // client-side conversion works too

??????????????????? cmd2.CommandType = CommandType.StoredProcedure;

??????????????????? cmd2.Parameters.Add("@x",SqlDbType.Xml);

??????????????? ????cmd2.Parameters[0].Value = s;

??????????????????? cmd2.ExecuteNonQuery();

??????????????? }

}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!