时间戳

首页 > 解决方案 > C# ExecuteNonQuery

c# - C# ExecuteNonQuery

问题描述

我的查询将在 switch 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;

标签： c#sql-serverado.net

解决方案

由于我不知道参数的值来自哪里，我只是假设它们被传递到过程中。您需要检查数据库中参数的数据类型并相应地更改代码。将值转换为匹配类型。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
    }

推荐阅读