Using data commands to execute Transact-SQL statements in Visual C # 2005

To use a data command to transfer Transact-SQL statements to SQL Server, assign the Transact-SQL statement to the CommandText property of the SqlCommand object. and set the CommandType property of the SqlCommand object to CommandType.Text (this is the default).

To remind you that when you use a data command with the CommandType property of text, be careful to check the information sent by the client before you can pass that information to your database. A malicious user attempts to send (insert) modified or additional SQL statements to gain unauthorized access or even destroy the database. Before transferring user input to a database, be sure to verify that the information is valid. The best practice is to use parametric queries or stored procedures whenever possible.

program Example 1

Figure 6-4 shows the execution screen of the Ch6_demoform003.cs. It demonstrates how to use the SqlCommand object to transfer Transact-SQL statements to and from the server, thereby obtaining the number of employees in the flight Fox Studio datasheet.

Figure 6-4

The related program code is written in the Load event handler for the form, as shown in the following columns:

private void CH6_DemoForm003_Load(object sender, EventArgs e)
{
...
try
{
// 建立连接。
using (SqlConnection con =
new SqlConnection(connectStringBuilder.ConnectionString))
{
// 建立数据命令对象(亦即 SqlCommand 对象)。
SqlCommand foxCMD = new SqlCommand();
// 设置 SqlCommand 对象所要使用的连接。
foxCMD.Connection = con;
// 设置赋给 SqlCommand 对象的是 TransactSQL 语句。
foxCMD.CommandType = CommandType.Text;
// 指派所要执行的 TransactSQL 语句。
foxCMD.CommandText = "SELECT COUNT(*) FROM dbo.飞狐工作室";
// 打开连接。
con.Open();
// 执行数据命令并将所返回的单一值赋给变量 total。
Int32 total = (int)(foxCMD.ExecuteScalar());
lblInfo.Text = "飞狐工作室的员工人数是:" +
total.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "请注意",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}