CommandText的研究

SqlConnection myConnection = new SqlConnection(); myConnection.ConnectionString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=abc"; string qury = "insert into df(Id,Pass) values(@Id,@Pass)"; SqlCommand myCommand = new SqlCommand(qury, myConnection); myConnection.Open(); DataSet ds = new DataSet(); myCommand.Parameters.Add(new SqlParameter("@Id", SqlDbType.NVarChar, 10)); myCommand.Parameters["@Id"].Value = textBox1.Text; myCommand.Parameters.Add(new SqlParameter("@Pass", SqlDbType.NVarChar, 10)); myCommand.Parameters["@Pass"].Value = textBox2.Text; try { myCommand.ExecuteNonQuery(); } catch(SqlException ex) { MessageBox.Show("插入错误！"+ex.Message.ToString()); } myCommand.Clone(); myConnection.Close();

此处用了CommandText的最简单的形式string qury = "insert into df(Id,Pass) values(@Id,@Pass)";

若有多个SQL语句，也可以用这样的形式string qury="Sql1"+"Sql2";当需要插入两条记录时，就可以用两个insert  into语句

例如：

 

SqlConnection myConnection = new SqlConnection(); myConnection.ConnectionString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=media2"; string qury = "declare @firm_name varchar(50)"+ "select @firm_name = playListName from playMediaList where playListId = 'P'" +" INSERT INTO TABLE2 values(@firm_name,'xyt125')"; SqlCommand myCommand = new SqlCommand(qury, myConnection); myConnection.Open(); try { myCommand.ExecuteNonQuery(); } catch (SqlException ex) { MessageBox.Show("插入错误！" + ex.Message.ToString()); } myCommand.Clone(); myConnection.Close(); 

 

此处需注意 qury中多个string可以和在一起，但彼此之间只能是空格，不能用逗号隔开；

string qury = "declare @firm_name varchar(50) select @firm_name = playListName from playMediaList where playListId = 'P' INSERT INTO TABLE2 values(@firm_name,'xyt125')";

这个Sql语句组，用到了申明变量@firm_name 来存储查询的结果，后用该变量去填充table2；

这样就通过sql语句实现了一条记录的值部分来自另一个表