c# - C# 上 mysql 语法的输入参数

Question

我尝试输入值，但 @numVal 不起作用(以 arr[10] 开头的行)

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using MySql.Data.MySqlClient;

    namespace ConsoleApplication2
    {
        class Program
        {
            static void Main(string[] args)
            {


                MySqlConnection conDatabase = new MySqlConnection("Data Source=localhost;" +
                                           "Persist Security Info=yes;" +
                                           "UserId=tee; PWD=t5794849; database=ph3;");
                conDatabase.Open();
               // MySqlCommand cmdDatabase = new MySqlCommand("DROP TABLE IF EXISTS `q_mem_sur`; create table q_mem_sur as SELECT Count(*) As rowa,  member.Ssurname,  Sum(Case When ((member.status = '1')) Then 1 Else 0 End) As Status11 From member Group By member.Ssurname Order By rowa Desc;", conDatabase);


                Console.WriteLine("Enter username");
                string input = Console.ReadLine();

                try
                {
                  int  numVal = Convert.ToInt16(input);
                }
                catch (FormatException )
                {
                    Console.WriteLine("Input string is not a sequence of digits.");
                }
                catch (OverflowException )
                {
                    Console.WriteLine("The number cannot fit in an Int32.");
                }






                string[] arr = new string[11];
                arr[0] = "UPDATE `member` SET `amphurecode`= SUBSTRING(member.own,3,4)";
                arr[1] = "UPDATE `member` SET `provincecode`= SUBSTRING(member.own,3,2)";
                arr[2] = "DROP TABLE IF EXISTS `q_mem_tim`; create table q_mem_tim as SELECT member.idmember, member.own, member.provincecode, province.PROVINCE_NAME, member.amphurecode, amphur.AMPHUR_NAME, member.novote, member.Sname, member.Ssurname, member.Hno, member.Moo, member.Sex, member.tambol, member.dateofbirth, member.migratedate, Year( Current_Date( ) ) -  Year( member.dateofbirth ) AS y,  DATEDIFF('2011-08-01',(migratedate)) AS d FROM member LEFT JOIN amphur ON ( member.amphurecode  = amphur.AMPHUR_CODE ) LEFT JOIN province ON member.provincecode  = province.PROVINCE_CODE";
                arr[3] = "DROP TABLE IF EXISTS `q_mem_sur`; create table q_mem_sur as SELECT Count(*) As rowa,  member.Ssurname,  Sum(Case When ((member.status = '1')) Then 1 Else 0 End) As Status11 From member Group By member.Ssurname Order By rowa Desc";
                arr[4] = "DROP TABLE IF EXISTS `q_mem_hno` ; create table q_mem_hno as select member.Hno, member.Moo, member.tambol, COUNT( member.Hno ) AS cntHno, COUNT(DISTINCT member.Ssurname) as NoSur FROM member GROUP BY member.Hno, member.Moo, member.tambol ORDER BY cntHno DESC  ";
                arr[5] = "DROP TABLE IF EXISTS `q_pro_ori`; create table q_pro_ori as Select member.provincecode,   count(*) As cnt  From member Group By member.provincecode Order By cnt Desc ";
                arr[6] = "DROP TABLE IF EXISTS `q_am_ori`; create table q_am_ori as Select member.amphurecode,  member.provincecode,  count(*) As cnt  From member Group By member.amphurecode, member.provincecode Order By cnt Desc ";
                arr[7] = "DROP TABLE IF EXISTS `Sur_Hno_`; create table Sur_Hno_ as SELECT count( *  ) , Ssurname, q_mem_tim.Hno, q_mem_tim.Moo, q_mem_tim.tambol FROM q_mem_tim GROUP BY q_mem_tim.Ssurname, q_mem_tim.Hno, q_mem_tim.Moo, q_mem_tim.tambol ORDER BY count( * ) DESC";
                arr[8] = "DROP TABLE IF EXISTS `q_dup_own`; create table q_dup_own as SELECT Count(*) as n ,member.own FROM member GROUP BY member.own order by n DESC ";
            //  arr[9] = "drop table if exists q_mem_birth; create table q_mem_birth as SELECT member.idmember, member.own, member.provincecode, province.PROVINCE_NAME, member.amphurecode, amphur.AMPHUR_NAME, member.Sname, member.Ssurname, member.Hno, member.Moo, member.Sex, member.tambol, member.dateofbirth, member.migratedate, member.fathercode , member.mathercode,   Year( Current_Date( ) ) -  Year( member.dateofbirth ) AS y,  DATEDIFF('2011-08-01',(migratedate)) AS d ,member.inputstaf  FROM member LEFT JOIN amphur ON ( member.amphurecode  = amphur.AMPHUR_CODE ) LEFT JOIN province ON member.provincecode  = province.PROVINCE_CODE WHERE DAYOFYEAR(member.dateofbirth)-DAYOFYEAR(NOW())  < 30 and DAYOFYEAR(member.dateofbirth)-DAYOFYEAR(NOW())  > -1 order by DAYOFYEAR(member.dateofbirth)";
                arr[9] = "ALTER TABLE `q_mem_tim` ADD `agec` VARCHAR( 10 )  NULL ";
            //    arr[10] = "UPDATE q_mem_birth SET agec = CASE WHEN y < 10 THEN 'ด'  WHEN y > 10 and y < 20 and Sex='ญ' THEN  'วญ'  WHEN y > 10 and y < 20 and Sex='ช' THEN  'วช'   ELSE 'ผญ' END";
                arr[10] = "drop table if exists q_mem_birth; create table q_mem_birth as SELECT q_mem_tim.idmember,q_mem_tim.own,q_mem_tim.PROVINCE_NAME,q_mem_tim.AMPHUR_NAME,q_mem_tim.novote,q_mem_tim.Sname,q_mem_tim.Ssurname,q_mem_tim.Hno,q_mem_tim.Moo,q_mem_tim.Sex,q_mem_tim.tambol,q_mem_tim.dateofbirth,q_mem_tim.migratedate,q_mem_tim.y,q_mem_tim.d,q_mem_tim.agec FROM q_mem_tim where  q_mem_tim.dateofbirth is not null and q_mem_tim.dateofbirth != '00000000' and day(q_mem_tim.dateofbirth) != '00' and day(q_mem_tim.dateofbirth) > 0 and day(q_mem_tim.dateofbirth) < 11 and month(q_mem_tim.dateofbirth) != '00'   and month(q_mem_tim.dateofbirth) = @numVal order by tambol,Moo, month(dateofbirth),day(dateofbirth)  "; 


                foreach (string s in arr)
                {
                    Console.WriteLine(s);
                    MySqlCommand cmdDbase = new MySqlCommand((s), conDatabase);
                    cmdDbase.CommandTimeout = 500;
                    cmdDbase.ExecuteNonQuery();
                }
                conDatabase.Close();
            }

            }
        }

Answer 1

1)截断表；不要删除它们并重新创建它们，因为当您删除它们时，您必须重新创建索引并添加主键。

using(var cm = _dbConnection.CreateCommand())
{
    cm.CommandText = @"Truncate Table Table";
    cm.CommandType = CommandType.Text;
    cm.ExecuteNonQuery();
}

2)不要忘记@符号；它对任何 SQL 都有帮助:

    using(var cm = _dbConnection.CreateCommand())
    {
        cm.CommandText = @"Select * 
                           From table 
                           Where Id = @Id";
        cm.CommandType = CommandType.Text;
        cm.Parameter.AddWithValue("Id", id);
        cm.ExecuteNonQuery();
    }

我知道你没有这样做，并且你正在使用字符串数组(只需在 using 语句之外放置一个 foreach 循环，并将 cm.CommandText 值替换为字符串的值)，但通过示例应该会有所帮助你或者至少给你一些想法。