c# - 使用 For 循环在单个查询中插入多个文本框和标签值

Question

for (int i = 1; i <= daysCount; i++)
{
    conn.Open();
    string sQuery = "INSERT INTO TripSheet VALUES('" + lblDate1.Text + "','" + txtFuel1.Text+ "','" + txtRate1.Text + "')";
    SqlCommand cmd = new SqlCommand(sQuery, conn);
    cmd.ExecuteNonQuery();
    conn.Close();
}

在这里我需要一个月count，我必须使用循环插入数据的月份。

lblDate1.Text, txtFuel1.Text & txtRate.Text

这个值我想通过 Like

lblDate[i].Text, txtFuel[i].Text & txtRate[i].Text    (Is it possible?)

标签： c#asp.netloops

Answer 1

通常，我希望这些值位于某种对象集合中，这些对象已从用户界面中清除。例如作为给定类型的列表或数组。我通常还会看到按该数据的类型插入 SQL 数据，例如datetime日期、VARCHAR(30)名称等。现在鉴于我对此一无所知，我可能AddWithValue在这里使用不太需要的东西。我输入的速度非常快，所以可能是错误的。把它放在一组适当的类和文件中。

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Data.SqlClient;

namespace Rates
{
    public class FuelRateThing
    {
        public DateTime RateDate
        {
            get;
            set;
        }

        public decimal FuelRate
        {
            get;
            set;
        }

        public string FuelName
        {
            get;
            set;
        }

        public FuelRateThing()
        {
        }

        public FuelRateThing(DateTime date, decimal rate, string name)
        {
            RateDate = date;
            FuelRate = rate;
            FuelName = name;
        }
    }

    public class Ratethings
    {
        public void dostuff()
        {
            CultureInfo provider = CultureInfo.InvariantCulture;
            //Create a class to manage these objects perhaps, here is a simple list example.
            // populate your list - normally parse and validation of UI values:
            List<FuelRateThing> monthRatesList = new List<FuelRateThing>()
            {new FuelRateThing{RateDate = DateTime.ParseExact("20180101", "yyyyMMdd", provider), FuelRate = 3.45m, FuelName = "kerosene"}, new FuelRateThing{RateDate = DateTime.ParseExact("20180102", "yyyyMMdd", provider), FuelRate = 3.49m, FuelName = "kerosene"}, new FuelRateThing{RateDate = DateTime.ParseExact("20180103", "yyyyMMdd", provider), FuelRate = 3.48m, FuelName = "kerosene"}, new FuelRateThing{RateDate = DateTime.ParseExact("20180104", "yyyyMMdd", provider), FuelRate = 3.65m, FuelName = "kerosene"}, new FuelRateThing{RateDate = DateTime.ParseExact("20180105", "yyyyMMdd", provider), FuelRate = 3.60m, FuelName = "kerosene"}, };
            // add a couple more rows
            monthRatesList.Add(new FuelRateThing(DateTime.ParseExact("20180106", "yyyyMMdd", provider), 3.64m, "kerosene"));
            var newrate = new FuelRateThing{RateDate = DateTime.ParseExact("20180107", "yyyyMMdd", provider), FuelRate = 3.47m, FuelName = "kerosene"};
            monthRatesList.Add(newrate);
        }

        //Now use the data access code to insert those: (probably in a method that accepts a list of `FuelRateThings`:
        public class DataStore
        {
            public void SaveRateThings(List<FuelRateThing> rateList)
            {
                var _connectionSB = new SqlConnectionStringBuilder();
                // do connections string stuff here
                var _connectionString = _connectionSB.ToString();
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    String query = @"
           INSERT INTO TripSheet (DateColumnName,FuelColumnName,RateColumnName)
           VALUES (@DateColumnName,@FuelColumnName,@RateColumnName)";
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        connection.Open();
                        foreach (FuelRateThing rate in rateList)
                        {
                            command.Parameters.AddWithValue("@DateColumnName", rate.RateDate);
                            command.Parameters.AddWithValue("@FuelColumnName", rate.FuelName);
                            command.Parameters.AddWithValue("@RateColumnName", rate.FuelRate);
                            int result = command.ExecuteNonQuery();
                        }
                    }
                }
            }
        }
    }
}