c# - 如何重构插入存储过程

Question

只需要帮助获取这一大块并使其尽可能高效，我大约 2 天前才开始学习 C#，所以对它还是很陌生。但是我正在编写使用我的 SQL 数据库中的插入存储过程的代码。

还请解释为什么您的更改也是有效的。我刚开始学习 C#，所以我还没有编写任何东西来连接任何东西的前端，我只是认为能够将我的输入写入控制台应用程序并且仍然能够使用存储过程会很酷。我面临的另一个问题是，在我输入技能之前一切正常，它会完全跳过那部分。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;


namespace TestConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection PubsConn = new SqlConnection("Server=.\\SQLEXPRESS;Database=PeopleDatabase;Trusted_Connection=True;"))
            {


                SqlCommand testCMD = new SqlCommand("People_Insert", PubsConn);

                testCMD.CommandType = CommandType.StoredProcedure;

                SqlParameter Id = testCMD.Parameters.Add("@Id", SqlDbType.Int, 5);
                Id.Direction = ParameterDirection.Output;
                SqlParameter Title = testCMD.Parameters.Add("@Title", SqlDbType.NVarChar, 100);
                Title.Direction = ParameterDirection.Input;
                SqlParameter Bio = testCMD.Parameters.Add("@Bio", SqlDbType.NVarChar, 100);
                Bio.Direction = ParameterDirection.Input;
                SqlParameter Summary = testCMD.Parameters.Add("@Summary", SqlDbType.NVarChar, 100);
                Summary.Direction = ParameterDirection.Input;
                SqlParameter Headline = testCMD.Parameters.Add("@Headline", SqlDbType.NVarChar, 100);
                Headline.Direction = ParameterDirection.Input;
                SqlParameter Slug = testCMD.Parameters.Add("@Slug", SqlDbType.NVarChar, 100);
                Slug.Direction = ParameterDirection.Input;
                SqlParameter StatusId = testCMD.Parameters.Add("@StatusId", SqlDbType.Int, 3);
                StatusId.Direction = ParameterDirection.Input;
                SqlParameter Skills = testCMD.Parameters.Add("@Skills", SqlDbType.NVarChar, 100);
                Skills.Direction = ParameterDirection.Input;
                SqlParameter PrimaryImage = testCMD.Parameters.Add("@PrimaryImage", SqlDbType.NVarChar, 100);
                PrimaryImage.Direction = ParameterDirection.Input;

                string titleInput;
                string bioInput;
                string summaryInput;
                string headlineInput;
                string slugInput;
                int statusId;
                string skillsInput;
                string imageInput;
                string strRowAffect;

                Console.WriteLine("Please Enter a Title");
                titleInput = Console.ReadLine();
                Title.Value = titleInput;

                Console.WriteLine("Please Enter a Bio");
                bioInput = Console.ReadLine();
                Bio.Value = bioInput;

                Console.WriteLine("Please Enter a Summary");
                summaryInput = Console.ReadLine();
                Summary.Value = summaryInput;

                Console.WriteLine("Please Enter a Headline");
                headlineInput = Console.ReadLine();
                Headline.Value = headlineInput;

                Console.WriteLine("Please Enter a Unique Slug");
                slugInput = Console.ReadLine();
                Slug.Value = slugInput;

                Console.WriteLine("Please Enter a 1 digit Status Id");
                statusId = Console.Read();
                StatusId.Value = statusId;

                Console.WriteLine("Please Enter Skills");
                skillsInput = Console.ReadLine();
                Skills.Value = skillsInput;

                Console.WriteLine("Enter an Image Url");
                imageInput = Console.ReadLine();
                PrimaryImage.Value = imageInput;

                PubsConn.Open();

                strRowAffect = testCMD.ExecuteNonQuery().ToString();

                Console.WriteLine("Number of Rows: " + strRowAffect);
                Console.WriteLine("Return Value: " + Id.Value);

            }

            Console.ReadLine();
        }
    }
}

标签： c#sql-serverstored-proceduresrefactoring

Answer 1

你的代码看起来不错。在我的示例中没有效率提升，它只是视觉重构。参数默认具有输入方向，因此您无需显式设置它们。

static void Main(string[] args)
{
    var command = new SqlCommand("People_Insert");
    command.CommandType = CommandType.StoredProcedure;

    var idParam = command.Parameters.Add("@Id", SqlDbType.Int, 5);
    idParam.Direction = ParameterDirection.Output;

    var titleParam = command.Parameters.Add("@Title", SqlDbType.NVarChar, 100);
    var bioParam = command.Parameters.Add("@Bio", SqlDbType.NVarChar, 100);
    var summaryParam = command.Parameters.Add("@Summary", SqlDbType.NVarChar, 100);
    var headlineParam = command.Parameters.Add("@Headline", SqlDbType.NVarChar, 100);
    var slugParam = command.Parameters.Add("@Slug", SqlDbType.NVarChar, 100);
    var statusIdParam = command.Parameters.Add("@StatusId", SqlDbType.Int, 3);
    var skillsParam = command.Parameters.Add("@Skills", SqlDbType.NVarChar, 100);
    var primaryImageParam = command.Parameters.Add("@PrimaryImage", SqlDbType.NVarChar, 100);

    Console.WriteLine("Please Enter a Title");
    titleParam.Value = Console.ReadLine();

    Console.WriteLine("Please Enter a Bio");
    bioParam.Value = Console.ReadLine();

    Console.WriteLine("Please Enter a Summary");
    summaryParam.Value = Console.ReadLine();

    Console.WriteLine("Please Enter a Headline");
    headlineParam.Value = Console.ReadLine();

    Console.WriteLine("Please Enter a Unique Slug");
    slugParam.Value = Console.ReadLine();

    Console.WriteLine("Please Enter a 1 digit Status Id");
    statusIdParam.Value = Console.Read();

    Console.WriteLine("Please Enter Skills");
    skillsParam.Value = Console.ReadLine();

    Console.WriteLine("Enter an Image Url");
    primaryImageParam.Value = Console.ReadLine();

    using (var connection = new SqlConnection("Server=.\\SQLEXPRESS;Database=PeopleDatabase;Trusted_Connection=True;"))
    {
        connection.Open();
        command.Connection = connection;
        var affectedRowsCount = command.ExecuteNonQuery();

        Console.WriteLine("Number of Rows: " + affectedRowsCount);
        Console.WriteLine("Return Value: " + idParam.Value);
    }

    Console.ReadLine();
}