I have inserted xml into SQL Server 2005 through rich text field successfully, now what I want to do is retrieve the xml from the DB but values separately and schema seperate... how can i do that in my existing code??
public void setData()
{
dc.ID = textBox1.Text;
dc.Name = richTextBox1.Text;
}
private void button1_Click(object sender, EventArgs e)
{
setData();
int flag = db.InsertData("insert into xmlTB values('" + dc.ID + "','" + dc.Name + "')");
if (flag > 0)
MessageBox.Show("Record Added");
else
MessageBox.Show("Not Added");
try
{
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
where the remain code of insertion is in a separate class:
public SqlConnection conn = new SqlConnection("Data Source=SERVER1\\SQLEXPRESS;Initial Catalog=xml;Integrated Security=True;Pooling=False");
public int flag = 0;
public SqlDataReader sdr = null;
public DBConnection() { } // constructor
public int InsertData(string qry)
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(qry, conn);
flag = cmd.ExecuteNonQuery();开发者_如何学运维
conn.Close();
return flag;
}
catch (Exception)
{
return flag;
}
}
thanks a lot
Several things you should definitely start using:
- use parametrized queries for inserting values into your tables
- use a specific list of columns in your INSERT statement - otherwise, next time that table changes, your INSERT will fail
The way you do it today is both fragile / brittle and will break when your table changes, plus the concatenating together of your SQL command is a great opportunity for SQL injection attacks. Just don't do it that way!
So your first method should look something like this:
private void button1_Click(object sender, EventArgs e)
{
setData();
string query = "INSERT INTO dbo.xmlTB(ID, Name) VALUES(@ID, @Name)";
int flag = db.InsertData(query, ...(somehow pass in the parameters!.....);
......
}
Secondly, your second method should
- use the
using(....) { ... }
constructs to protect and dispose yourSqlConnection
andSqlCommand
object instances - do retrieve the XML from the database, use a simple SELECT query and call either
ExecuteReader
orExecuteScalar
on yourSqlCommand
object.
Something like this:
public string ReadXmlData(int ID)
{
string query = "SELECT XmlContent FROM dbo.xmlTB WHERE ID = @ID";
string connectionString = "Data Source=SERVER1\\SQLEXPRESS;Initial Catalog=xml;Integrated Security=True;Pooling=False";
using(SqlConnection conn = new SqlConnection(connectionString))
using(SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add("@ID", SqlDbType.Int);
cmd.Parameters["@ID"].Value = ID;
conn.Open();
string xmlContents = cmd.ExecuteScalar().ToString();
conn.Close();
return xmlContents;
}
catch (Exception)
{
return flag;
}
}
The question appears vague but: After record added, call another method called say "GetData" (you'll need to write this). This method might use cmd.ExecuteReader() to call the db. Ensure that your select statement in your query has "FOR XML" at the end of the table name.
精彩评论