如何为存储过程提供参数

Friends i am making a windows form application in which i have created a stored procedure in sql-server-2008 , then created a external class in windows form like this

朋友我正在制作一个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; }
        }

    }

}

where i have created get set function for all the paramerets In another external class i define all the stored procedure like this

我为所有参数创建了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
    }
}

After to insert it into the database i again created a seprate class and pass the parameters like this

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

  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;
           }}

then on submit button i have done the coding for submit action ....

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

  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 ");
                }
}}

On running the program it is showing the error that

在运行程序时，它显示错误

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

过程或函数'insertpersonal'期望参数'@educationlvl'未提供

But i have supplied it like all other parameter then whats the problem occurring ..... Please solve this.Please Please Please

但我提供它像所有其他参数然后发生的问题.....请解决这个问题。请请

2 个解决方案

#1

Looks like a naming problem. Try changing:

看起来像命名问题。尝试改变：

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

至

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

Edit, for Marrital issue:

编辑，对于Marrital问题：

Try changing:

尝试改变：

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

至

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

The equivalent of C# null for a database is DBNull.Value. You can't pass in a C# null to a database, it doesn't like it. So, what you do is check if the value that you're wanting to send in is null. If it is, send in DBNull.Value instead. That's what per.Marrital ?? DBNull.Value does. It's the same as doing this, but in a much shorter syntax:

数据库的C＃null等价物是DBNull.Value。你不能将C＃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

#2

The parameter has to be with the same name @educationlvl is not the same as @EducationLevel. Hope this helps.

该参数必须与@ educationlvl具有相同名称，与@EducationLevel不同。希望这可以帮助。

#1