SQL Server 2012 Insert Stored Procedure for N parameters

debugcn Published at Dev

Muhammad Zeeshan

I have a table with 70 columns in SQL Server 2012. I am using 3-Tier Architecture in ASP.NET. How can I store the values in that table with minimal code.

Following are the properties which are to be stored in table.

 public int DeptID { get; set; }
    public int ProgID { get; set; }

    public string Session { get; set; }
    public string Name { get; set; }
    public string FName { get; set; }
    public string FOccupation { get; set; }
    public string FIncome { get; set; }
    public string FNIC { get; set; }
    public string ANIC { get; set; }
    public string DBirth { get; set; }
    public string Gender { get; set; }
    public string Religion { get; set; }
    public string Martial_Status { get; set;}
    public string Nationality { get; set; }
    public string Domicile { get; set; }
    public string Telephone { get; set; }
    public string Mobile { get; set; }
    public string PAddress { get; set; }
    public string MAddress { get; set; }
    public string Service { get; set; }
    public string Designation { get; set; }
    public string Organization { get; set; }

    public int SSCYear { get; set; }
    public string SSCEBody { get; set; }
    public int SSCRNo { get; set; }
    public int SSCTMarks { get; set; }
    public  int SSCOMarks { get; set; }
    public string SSCDivision { get; set; }
    public string SSCMSubjects { get; set; }

    public int IntermidiateYear { get; set; }
    public string IntermidiateEBody { get; set; }
    public int IntermidiateRNo { get; set; }
    public int IntermidiateTMarks { get; set; }
    public int IntermidiateOMarks { get; set; }
    public string IntermidiateDivision { get; set; }
    public string IntermidiateMSubjects { get; set; }

    public int UGraduate1Year { get; set; }
    public string UGraduate1EBody { get; set; }
    public int UGraduate1RNo { get; set; }
    public int UGraduate1TMarks { get; set; }
    public int UGraduate1OMarks { get; set; }
    public string UGraduate1Division { get; set; }
    public string UGraduate1MSubjects { get; set; }

    public int UGraduate2Year { get; set; }
    public string UGraduate2EBody { get; set; }
    public int UGraduate2RNo { get; set; }
    public int UGraduate2TMarks { get; set; }
    public int UGraduate2OMarks { get; set; }
    public string UGraduate2Division { get; set; }
    public string UGraduate2MSubjects { get; set; }

    public int GraduateYear { get; set; }
    public string GraduateEBody { get; set; }
    public int GraduateRNo { get; set; }
    public int GraduateTMarks { get; set; }
    public int GraduateOMarks { get; set; }
    public string GraduateDivision { get; set; }
    public string GraduateMSubjects { get; set; }

    public int GatGeneralRNo { get; set; }
    public int GatGeneralScore { get; set; }
    public string GatGeneralFStudy { get; set; }
    public string GatGeneralTestDate { get; set; }
    public string GatGeneralValid { get; set; }

    public int GatSubjectRNo { get; set; }
    public int GatSubjectScore { get; set; }
    public string GatSubjectFStudy { get; set; }
    public string GatSubjectTestDate { get; set; }
    public string GatSubjectValid { get; set; }

Here is the fuction for calling the procedure

 public DataTable GraduateProgInsert()
    {

        SqlParameter[] prm = new SqlParameter[72];

        //how to add these values in the SqlParameter

    }

Ruben Steins

You could use reflection to go over all the properties and store them in the array:

foreach(var property in myClass.GetType().GetProperties())
{
    SqlParameter newParam = new SqlParameter();
    newParam.Name = property.Name;
    ...
    // set other fields of the new parameter here and add it to the array 
    // the logic to determine the exact type of param can get hairy
 }

Using an ORM is another alternative I would certainly consider to avoid having to write this kind of code yourself.

Collected from the Internet

Please contact [email protected] to delete if infringement.