C# ExecuteNonQuery

Question

我的查询将在switch case循环中第一次执行，但在第二个case循环中什么也没有发生。

所有内容都写在for循环中，它会设法将第一个查询正确地添加到数据库中，但之后就不会了

string sQuery = string.Format("'{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}','{9}',{10}", sName, sMiddleName, sSurname, sBirthdate, sSex, sNationality, sDateOfArrival, sCardID, sUsername, sPassword, sPhoneNumber);
SqlConnection cnn;

cnn = new SqlConnection(Globals.sqlConnect);
cnn.Open();
SqlCommand command;
SqlDataAdapter adapter = new SqlDataAdapter();
String sql = "";
for (int i = 0; i < 2; i++)
{
    switch (i)
    {
        case 0:
            sql = "INSERT INTO dbo.Refugee ([Name],[Middlename],[Surname],[Birthdate],[Sex],[Nationality],[Date_of_arrival],[ID_Card_Number],[Username],[Password],[Phone_Number]) VALUES(" + sQuery + ")";

            command = new SqlCommand(sql, cnn);
            adapter.InsertCommand = command;
            adapter.InsertCommand.ExecuteNonQuery();

            command.Dispose();
            break;
        case 1:
            if (Properties.Settings.Default.HoF == true)
            {
            sQuery = string.Format("'{0}' ,{1}",Properties.Settings.Default.Familyname,tb_cardID);
            sql = "INSERT INTO dbo.Family ([Family_name],[Head_Of_Family_ID_Card_Number]) VALUES ("+ sQuery +")";

                command = new SqlCommand(sql, cnn);

                adapter.InsertCommand = command;
                adapter.InsertCommand.ExecuteNonQuery();

                command.Dispose();
            }

            break;

Answer 1

因为我不知道参数的值从哪里来，所以我只是假设它们被传递到过程中。您需要检查数据库中参数的数据类型，并相应地更改代码。将值转换为匹配的类型。using块可确保即使出现错误，也会关闭并释放数据库对象。使用参数可以防止Sql注入。

循环、开关和数据适配器是不必要的。

    private void OPCode(string sName,string sMiddleName,string sSurname,DateTime sBirthdate,string sSex,string sNationality,DateTime sDateOfArrival,int sCardID,string sUsername,string sPassword,string sPhoneNumber, int tb_cardID)
    {
        using (SqlConnection cnn = new SqlConnection(Globals.sqlConnect))
        {
            using (SqlCommand command = new SqlCommand("INSERT INTO dbo.Refugee ([Name],[Middlename],[Surname],[Birthdate],[Sex],[Nationality],[Date_of_arrival],[ID_Card_Number],[Username],[Password],[Phone_Number]) VALUES (@sName, @sMiddleName, @sSurname, @sBirthdate, @sSex, @sNationality, @sDateOfArrival, @sCardID, @sUsername, @sPassword, @sPhoneNumber);", cnn))
            {
                command.Parameters.Add("@sName", SqlDbType.VarChar).Value = sName;
                command.Parameters.Add("@sMiddleName", SqlDbType.VarChar).Value = sMiddleName;
                command.Parameters.Add("@sSurname", SqlDbType.VarChar).Value = sSurname;
                command.Parameters.Add("@sBirthdate", SqlDbType.DateTime).Value = sBirthdate;
                command.Parameters.Add("@sSex", SqlDbType.VarChar).Value = sSex;
                command.Parameters.Add("@sNationality", SqlDbType.VarChar).Value = sNationality;
                command.Parameters.Add("@sDateOfArrival", SqlDbType.DateTime).Value = sDateOfArrival;
                command.Parameters.Add("@sCardID", SqlDbType.Int).Value = sCardID;
                command.Parameters.Add("@sUsername", SqlDbType.VarChar).Value = sUsername;
                command.Parameters.Add("@sPassword", SqlDbType.VarChar).Value = sPassword;
                command.Parameters.Add("@sPhoneNumber", SqlDbType.VarChar).Value = sPhoneNumber;
                cnn.Open();
                command.ExecuteNonQuery();
            } //disposes command
            if (Properties.Settings.Default.HoF == true)
            {
                using(SqlCommand command = new SqlCommand("INSERT INTO dbo.Family ([Family_name],[Head_Of_Family_ID_Card_Number]) VALUES (@FamilyName, @tb_carID;", cnn))
                { 
                    command.Parameters.Add("@Familyname", SqlDbType.VarChar).Value = Properties.Settings.Default.Familyname;
                    command.Parameters.Add("@tb_cardID", SqlDbType.Int).Value = tb_cardID; 
                    command.ExecuteNonQuery();
                }//disposes second command
            }
        }//closes and disposes connection
    }