学习笔记（如何在各类控件中输入/输出数据）
一、思维导图
二、知识点描述+示例（运行截图）
1.输出：声明并实例化SQL数据适配器，同时借助构造函数，将其SelectCommand属性设为先前创建的SQL命令，然后SQL数据适配器利用FILL方法读取数据，之后调用SQL命令的方法ExecuteReader来执行命令，并获取数据阅读器，最后在数据阅读器的索引器中指定列名，从而访问当前记录的指定列的值，并赋予相应控件，即输出数据成功。
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
SqlCommand sqlCommand = new SqlCommand();
SqlCommand sqlCommand2 = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand2.Connection = sqlConnection;
sqlCommand.CommandText = "SELECT * FROM tb_Province;";
sqlCommand2.CommandText = "SELECT * FROM tb_Drug WHERE [email protected];";
sqlCommand2.Parameters.AddWithValue("@DrugNo", this.txb_DrugNo.Text.Trim());
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlCommand;
DataTable provinceTable = new DataTable();
sqlConnection.Open();
sqlDataAdapter.Fill(provinceTable);
this.cmb_ProducingArea.DataSource = provinceTable;
this.cmb_ProducingArea.DisplayMember = "Name";
this.cmb_ProducingArea.ValueMember = "No";
SqlDataReader sqlDataReader = sqlCommand2.ExecuteReader();
if (sqlDataReader.Read())
{
this.txb_DrugNo.Text = sqlDataReader["DrugNo"].ToString();
this.txb_DrugName.Text = sqlDataReader["DrugName"].ToString();
this.rdb_ChineseMedicine.Checked = (bool)sqlDataReader["Type"];
this.rdb_WesternMedicine.Checked = !(bool)sqlDataReader["Type"];
this.txb_Bid.Text = sqlDataReader["Bid"].ToString();
this.txb_Price.Text = sqlDataReader["Price"].ToString();
this.dtp_DateOfManufacture.Value=(DateTime)sqlDataReader["DateOfManufacture"];
this.cmb_ProducingArea.SelectedValue = (int)sqlDataReader["ProducingAreaNo"];
this.txb_TermOfValidity.Text = sqlDataReader["TermOfValidity"].ToString();
}
sqlDataReader.Close(); 运行截图：
DateGridView：
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "SELECT * FROM tb_Custom;";
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlCommand;
DataTable customTable = new DataTable();
sqlConnection.Open();
sqlDataAdapter.Fill(customTable);
sqlConnection.Close();
this.dgv_Custom.DataSource = customTable; 运行截图：
2.输入
先指定SQL命令的命令文本，然后向SQL命令的参数集合添加参数的名称、值，最后调用SQL命令的方法ExecuteNonQuery来执行命令，向数据库写入数据，即输入成功。
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText =
"UPDATE tb_Drug"
+ " SET [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected]"
+ " WHERE [email protected];";
sqlCommand.Parameters.AddWithValue("@DrugName", this.txb_DrugName.Text.Trim());
sqlCommand.Parameters.AddWithValue("@Type", this.rdb_ChineseMedicine.Checked);
sqlCommand.Parameters.AddWithValue("@Bid", this.txb_Bid.Text.Trim());
sqlCommand.Parameters.AddWithValue("@Price", this.txb_Price.Text.Trim());
sqlCommand.Parameters.AddWithValue("@DateOfManufacture", this.dtp_DateOfManufacture.Value);
sqlCommand.Parameters.AddWithValue("@ProducingAreaNo", (int)this.cmb_ProducingArea.SelectedValue);
sqlCommand.Parameters.AddWithValue("@TermOfValidity", this.txb_TermOfValidity.Text.Trim());
sqlCommand.Parameters.AddWithValue("@DrugNo", this.txb_DrugNo.Text.Trim());
sqlConnection.Open();
int rowAffected = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
MessageBox.Show("更新" + rowAffected.ToString() + "行。"); 运行截图：
DateGridView:
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
SqlCommand insertCommand = new SqlCommand();
insertCommand.Connection = sqlConnection;
insertCommand.CommandText =
"INSERT tb_Custom"
+ "(CustomNo,CustomName,Property,Address1,Phone,linkman)"
+ " VALUES(@CustomNo,@CustomName,@Property,@Address1,@Phone,@linkman);";
insertCommand.Parameters.Add("@CustomNo", SqlDbType.Char, 5, "CustomNo");
insertCommand.Parameters.Add("@CustomName", SqlDbType.VarChar, 0, "CustomName");
insertCommand.Parameters.Add("@Property", SqlDbType.VarChar, 0, "Property");
insertCommand.Parameters.Add("@Address1", SqlDbType.VarChar, 0, "Address1");
insertCommand.Parameters.Add("@Phone", SqlDbType.VarChar, 0, "Phone");
insertCommand.Parameters.Add("@linkman", SqlDbType.VarChar, 0, "linkman");
SqlCommand updateCommand = new SqlCommand();
updateCommand.Connection = sqlConnection;
updateCommand.CommandText =
"UPDATE tb_Custom"
+ " SET [email protected],[email protected],[email protected],[email protected],[email protected],[email protected]"
+ " WHERE [email protected];";
updateCommand.Parameters.Add("@NewCustomNo", SqlDbType.Char, 5, "CustomNo");
updateCommand.Parameters.Add("@CustomName", SqlDbType.VarChar, 0, "CustomName");
updateCommand.Parameters.Add("@Property", SqlDbType.VarChar, 0, "Property");
updateCommand.Parameters.Add("@Address1", SqlDbType.VarChar, 0, "Address1");
updateCommand.Parameters.Add("@Phone", SqlDbType.VarChar, 0, "Phone");
updateCommand.Parameters.Add("@linkman", SqlDbType.VarChar, 0, "linkman");
updateCommand.Parameters.Add("@OldCustomNo", SqlDbType.Char, 10, "CustomNo");
updateCommand.Parameters["@OldCustomNo"].SourceVersion = DataRowVersion.Original;
SqlCommand deleteCommand = new SqlCommand();
deleteCommand.Connection = sqlConnection;
deleteCommand.CommandText =
"DELETE tb_Custom"
+ " WHERE [email protected];";
deleteCommand.Parameters.Add("@CustomNo", SqlDbType.Char, 5, "CustomNo");
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.InsertCommand = insertCommand;
sqlDataAdapter.UpdateCommand = updateCommand;
sqlDataAdapter.DeleteCommand = deleteCommand;
DataTable customTable = (DataTable)this.dgv_Custom.DataSource;
sqlConnection.Open();
int rowAffected = sqlDataAdapter.Update(customTable);
sqlConnection.Close();
MessageBox.Show("更新" + rowAffected.ToString() + "行。"); 运行截图：