c# - 如何向存储过程提供参数

Question

friend 们，我正在制作一个 Windows 窗体应用程序，其中我在 sql-server-2008 中创建了一个存储过程，然后像这样在 Windows 窗体中创建了一个外部类

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

namespace Jain_milan.Common
{
   public class Personal
    {
        string name, fathername, mothername, familyhead, dateofbirth,educationlvl, education, blood, gotra, panth, marrital;

        public string Educationlvl
        {
           get { return educationlvl; }
           set { educationlvl = value; }
        }

        public string Panth
        {
            get { return panth; }
            set { panth = value; }
        }

        public string Gotra
        {
            get { return gotra; }
            set { gotra = value; }
        }

        public string Blood
        {
            get { return blood; }
            set { blood = value; }
        }

        public string Education
        {
            get { return education; }
            set { education = value; }
        }

        public string DateOfBirth
        {
            get { return dateofbirth; }
            set { dateofbirth = value; }
        }

        public string FamilyHead
        {
            get { return familyhead; }
            set { familyhead = value; }
        }

        public string MotherName
        {
            get { return mothername; }
            set { mothername = value; }
        }

        public string FatherName
        {
            get { return fathername; }
            set { fathername = value; }
        }

        public string Name
        {
            get { return name; }
            set { name = value; }
        }

        public string Marrital
        {
            get { return marrital; }
            set { marrital = value; }
        }

    }

}

我在其中为所有参数创建了 get set 函数在另一个外部类中，我像这样定义所有存储过程

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

namespace Jain_milan.DataAction
{
    public enum InsertStoredProcedure
    {
        insertpersonal,
        insertressicontact,
        insertoccupcontact,
        insertspouse,
        insert1children,
        insert2children,
        insert3children,
        insert4children,
        insert5children
    }
}

将其插入数据库后，我再次创建了一个单独的类并像这样传递参数

  public class InsertAction
    {
       public bool Insertpersonal(Personal per)
       {
               SqlCommand cmd = Database.GetConnection().CreateCommand();
               cmd.CommandText=InsertStoredProcedure.insertpersonal.ToString();
               cmd.CommandType= CommandType.StoredProcedure;
               cmd.Parameters.Add(new SqlParameter("@Name",per.Name));
               cmd.Parameters.Add(new SqlParameter("@FatherName",per.FatherName));
               cmd.Parameters.Add(new SqlParameter("@MotherName",per.MotherName));
               cmd.Parameters.Add(new SqlParameter("@FamilyHead",per.FamilyHead));
               cmd.Parameters.Add(new SqlParameter("@DateOfBirth",per.DateOfBirth));
               cmd.Parameters.Add(new SqlParameter("@EducationLevel", per.Educationlvl));
               cmd.Parameters.Add(new SqlParameter("@Education",per.Education));
               cmd.Parameters.Add(new SqlParameter("@Blood",per.Blood));
               cmd.Parameters.Add(new SqlParameter("@Gotra",per.Gotra));
               cmd.Parameters.Add(new SqlParameter("@Panth",per.Panth));
               cmd.Parameters.Add(new SqlParameter("@Marrital",per.Marrital));
               bool ans = cmd.ExecuteNonQuery() > 0;
               cmd.Dispose();
               Database.CloseConnection();
               return ans;
           }}

然后在提交按钮上我完成了提交操作的编码......

  private void submit_addbtn_Click(object sender, EventArgs e)
        {
            try
            {
                //personal data insert
                Personal per = new Personal();
                per.Name = nametxt.Text;
                per.FatherName = f_nametxt.Text;
                per.MotherName = m_nametxt.Text;
                per.Gotra = gotra_txt.Text;
                per.Panth = panthcb.Text;
                per.FamilyHead = fhntext.Text;
                per.Educationlvl = edulvlcb.Text;
                per.Education = educb.Text;
                per.Blood = bloodcb.Text;
                per.Marrital = MarritalStatus;
                per.DateOfBirth = (day + '/' + month + '/' + year).ToString();
                if (new InsertAction().Insertpersonal(per))
                {
                    MessageBox.Show("Personal Insertion Happen ");
                }
                else
                {
                    MessageBox.Show(" Personal Insertion does not Happen ");
                }
}}

运行程序时显示错误

Procedure or function 'insertpersonal' expects parameter '@educationlvl' which was not supplied

但是我已经像所有其他参数一样提供了它，那么发生了什么问题......请解决这个问题。拜托拜托

Answer 1

看起来像是命名问题。尝试更改:

cmd.Parameters.Add(new SqlParameter("@EducationLevel", per.Educationlvl));

到

cmd.Parameters.Add(new SqlParameter("@EducationLvl", per.Educationlvl));

编辑，针对婚姻问题:

尝试改变:

cmd.Parameters.Add(new SqlParameter("@Marrital",per.Marrital));

到

cmd.Parameters.Add(new SqlParameter("@Marrital", per.Marrital ?? DBNull.Value));

数据库的 C# null 等效于 DBNull.Value。您不能将 C# null 传递给数据库，它不喜欢这样。因此，您要做的是检查您要发送的值是否为 null。如果是，请改为发送 DBNull.Value。这就是 per.Marrital ?? DBNull.Value 可以。它与这样做相同，但语法更短:

object marritalValue = per.Marrital;
if(marritalValue == null)
    marritalValue = DBNull.Value
cmd.Parameters.Add(new SqlParameter("@Marrital", marritalValue ));

http://msdn.microsoft.com/en-us/library/ms173224.aspx