C#SQL选择，然后插入 [英] C# SQL Select and then Insert

问题描述

谁能告诉我为什么这行不通.我想做的是从数据库中读取数据，然后将其插入到另一个表和另一个列中.

Hi can anyone please tell me why this does not work. What im trying to do, is to Read data from a DB then I want to insert that into a different table and and another colum.

public void InsertUniqueGymMemberTrainingProgram(string gmemberID, int daynum)
      {

          using (SqlConnection conn = new SqlConnection(connectionString))
          {

              SqlCommand cmd = new SqlCommand("procGetProg1Day",conn);
              cmd.CommandType = CommandType.StoredProcedure;
              //cmd.CommandText = "procGetProg1Day";
              //cmd.Connection = conn;

              cmd.Parameters.Add(new SqlParameter("@dayNum", SqlDbType.SmallInt));
              cmd.Parameters["@dayNum"].Value = daynum;

              conn.Open();
              SqlDataReader reader = cmd.ExecuteReader();
              while(reader.Read())
              {
                  SqlCommand insCmd = new SqlCommand("procInsertUniqueGymMemberTrainingProgram", conn);
                  insCmd.CommandType = CommandType.StoredProcedure;
                  // insCmd.CommandText = "procInsertGymMemberTrainingProgram";

                  insCmd.Parameters.Add(new SqlParameter("@GymMemberID", SqlDbType.NVarChar,20));
                  insCmd.Parameters["@GymMemberID"].Value = gmemberID;

                  insCmd.Parameters.Add(new SqlParameter("@ExerciseID", SqlDbType.SmallInt));
                  insCmd.Parameters["@ExerciseID"].Value = Convert.ToInt32(reader[2]);

                  insCmd.Parameters.Add(new SqlParameter("@TrainingProgramID", SqlDbType.SmallInt));
                  insCmd.Parameters["@TrainingProgramID"].Value = Convert.ToInt32(reader[1]);

                  insCmd.Parameters.Add(new SqlParameter("@DayNumber", SqlDbType.SmallInt));
                  insCmd.Parameters["@DayNumber"].Value = Convert.ToInt32(reader[0]);

                  conn.Open();
                  insCmd.ExecuteNonQuery();
              }
              reader.Close();
          }
      }

在此先感谢:)

Thanks in advance:)

ALTER PROCEDURE [dbo].[procGetProg1Day]
@dayNum smallint
AS
SELECT DISTINCT TrainingProgramExercise.DayNumber, TrainingProgramExercise.TrainingProgramID,TrainingProgramExercise.ExerciseID
FROM Exercise
INNER JOIN TrainingProgramExercise
ON Exercise.ExerciseID = TrainingProgramExercise.ExerciseID
WHERE TrainingProgramExercise.DayNumber = @dayNum
AND TrainingProgramExercise.TrainingProgramID = 1

AND第二

AND 2nd

ALTER PROCEDURE [dbo].[procInsertUniqueGymMemberTrainingProgram]
@GymMemberID nvarchar(20),
@ExerciseID smallint,
@TrainingProgramID smallint,
@DayNumber smallint
AS
INSERT INTO GymMemberTrainingProgram(GymMemberID, ExerciseID,TrainingProgramID,DayNumber)
VALUES(@GymMemberID,@ExerciseID,@TrainingProgramID,@DayNumber)

推荐答案

您遇到什么问题?你有例外吗?如果您通过代码进行调试，则是从第一个过程中获取行，等等.

您可以执行此操作的另一种方法是使用以下语句在单个语句中执行整个操作:

What problem are you encountering? Are you getting an exception? If you debug through the code are you getting rows from the first procedure etc...

Another way you could do this is to execute the whole thing in a single statement using a statement like:

INSERT INTO GymMemberTrainingProgram(
   GymMemberID, 
   ExerciseID,
   TrainingProgramID,
   DayNumber)
SELECT DISTINCT
       @GymMemberID, 
       TrainingProgramExercise.ExerciseID,
       TrainingProgramExercise.TrainingProgramID,
       TrainingProgramExercise.DayNumber
FROM Exercise
INNER JOIN TrainingProgramExercise
ON Exercise.ExerciseID = TrainingProgramExercise.ExerciseID
WHERE TrainingProgramExercise.DayNumber = @dayNum
AND TrainingProgramExercise.TrainingProgramID = 1

如果正确设置参数并执行上述语句，则无需遍历客户端的所有记录，这样会更加高效.

If you set up the parameters correctly and execute the above statement you shouldn''t need to loop through all the records at client side, which would be much more efficient.

不要"多次打开SQL连接.我可以看到您已经在while循环中以及在输入连接之前打开了连接.

Don''t open the SQL connection multiple times. I can see that you have open the connection within the while loop as well as before enter to it.

这篇关于C#SQL选择，然后插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！