Adding Applicant Info in SQL using Class object

Question

I would like to ask i have a class of Applicants

public class Applicant_info
{
    public long Id { get; set; }

    public DateTime ApplicantDateofRegistration { get; set; }

    public long ApplicantID { get; set; }

    public string ApplicantName { get; set; }

    public string ApplicantFatherName { get; set; }
}

my objective is to Insert List of Applicants object in to SQL Database using Stored Procedure , How can I achieve my objective?

protected void btnAddNewApplicant_Click(object sender, EventArgs e)
    {
        apprepo = new Repositories.ApplicantRepository();
        List<Applicant_info> appinfo = new List<Applicant_info>();
        appinfo[0].ApplicantDateofRegistration = Convert.ToDateTime(dateofreg.Text);
        appinfo[0].ApplicantID = Convert.toInt64(txtID.text);
        appinfo[0].ApplicantName = txtapplicantName.Text;
        appinfo[0].ApplicantFatherName = txtsonof.Text;
    }

Do I need to make a custome type in SQL? Thanks in Advance :)

Answer 1

There are a couple of options available, one option is to make use of a stored procedure with a table valued parameter. It will require that you create a custom table value type in the database. It will also require that you copy your list into a .net Datatable. This is a good option if you are certain that your table and class structures will never change, otherwise it can get a bit painful to maintain.

I prefer simply serializing the list as an xml and then pass the xml as a parameter to your stored procedure. It is a little bit less sensitive to minor changes in your class structure (such as adding new field members). The following example shows what the implementation in C# looks like:

    private void SqlExportApplicantInfo(List<Applicant_info> applicants)
    {
        const string storedProcedureName = @"UploadApplicants";  // Your sql stored procedure name here
        SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder();

        connectionBuilder.ApplicationName = "AppName";      // Set your application name here
        connectionBuilder.ConnectTimeout = 30;
        connectionBuilder.DataSource = "(local)";           // Set your server name here
        connectionBuilder.InitialCatalog = "SimplyData";    // Set your database here
        connectionBuilder.IntegratedSecurity = true;

        string connectionString = connectionBuilder.ConnectionString;

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            try
            {
                using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    MemoryStream memoryStream = new MemoryStream();
                    XmlSerializer serializer = new XmlSerializer(typeof(List<Applicant_info>));
                    XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.UTF8);
                    serializer.Serialize(xmlTextWriter, applicants);
                    memoryStream = (MemoryStream)xmlTextWriter.BaseStream;
                    memoryStream.Position = 0;

                    SqlXml applicantsXml = new SqlXml(memoryStream);
                    SqlParameter parameter = command.Parameters.AddWithValue("@applicantsXml", applicantsXml);
                    parameter.SqlDbType = SqlDbType.Xml;

                    command.ExecuteNonQuery();
                }
            }
            catch (Exception exception)
            {
                throw new ApplicationException("Failed to upload applicant list to sql.", exception);
            }
        }

    }

The following stored procedure example shows how you can evaluate the generated xml in SQL Server:

CREATE PROCEDURE UploadApplicants
(
    @applicantsXml XML
) 
AS
BEGIN

    SET NOCOUNT ON;

    INSERT INTO [dbo].[Applicants]
    (
        Id
        ,ApplicantDateofRegistration
        ,ApplicantID
        ,ApplicantName
        ,ApplicantFatherName
    )
    SELECT 
         Applicants.Info.value('Id[1]', 'INT') AS Id
        ,Applicants.Info.value('ApplicantDateofRegistration[1]', 'DATETIME') AS ApplicantDateofRegistration
        ,Applicants.Info.value('ApplicantID[1]', 'INT') AS ApplicantID
        ,Applicants.Info.value('ApplicantName[1]', 'VARCHAR(100)') AS ApplicantName
        ,Applicants.Info.value('ApplicantFatherName[1]', 'VARCHAR(100)') AS ApplicantFatherName
    FROM    
        @applicantsXml.nodes('ArrayOfApplicant_info/Applicant_info') Applicants(Info);

END