Passing C# Form text as parameter to SQL Server

Question

I have a C# form with a MaskedTextBox that I am needing to pass as a parameter to my SQL Server.

The form:

+-------------------------------------------------------+
|                     ___________                       |
|               Date:|__/__/_____|   <---MaskedTextBox  |
|                                                       |
|                     _________                         |
|                    |btnSubmit|     <---Button         |
|                                                       |
+-------------------------------------------------------+

When a date is entered in the MaskedTextBox, it looks for a file matching that date, reads from it, and then sends that data to my SQL Server. However, I'm needing to create a stored procedure to delete the data matching the day of the import- as to not have duplicated data.

The tricky part is that I have the method for sending the data to my SQL Server on another .cs file that is added to my project.

The method(Methods.cs):

public void SendTable(DataTable table, string StoredProcedure, string TableType)
{

    string conStr = "Data Source=" + ConfigurationManager.AppSettings["DataSource"] + "Initial Catalog=" + ConfigurationManager.AppSettings["InitialCatalog"] + "Integrated Security=True;";

    //Use Stored procedure to send data table
    using (SqlConnection connection = new SqlConnection(conStr))
    {
        SqlCommand cmd = new SqlCommand();

        cmd.Connection = connection;
        cmd.CommandText = StoredProcedure;

        connection.Open();

        cmd.CommandType = CommandType.StoredProcedure;

        var fm = new frmMain();  //Main form located in frmMain.cs
        string date = fm.uDate;  //setting string date to a public string, also on frmMain.cs

        SqlParameter dtparam = new SqlParameter("@date", table);
        dtparam.SqlDbType = SqlDbType.Structured;
        dtparam.TypeName = "dbo." + TableType;

        cmd.Parameters.Add(dtparam).Value = date;

        cmd.ExecuteNonQuery();  //ERROR HERE: Failed to convert parameter value from a String to a IEnumerable`1

    }
}

Here is uDate(frmMain.cs):

public string uDate
{
    get { return mtbDate.Text; }
}

In case it's needed, my stored procedure:

ALTER Procedure [dbo].[Submit]
--Add Parameter
@date AS nvarchar(max)
AS
Begin 
    Set NoCount ON

    --DELETE FROM _table

    Begin Tran T1;

    INSERT INTO [Archive]([Customer Name], IDNbr, City, Bal, ONbr, BalDate) SELECT CustName, IDNbr, City, Bal, ONbr, (@date) AS BalDate FROM myView;

    Commit Tran T1;

End

Thank you in advance for any help or advice! If anything is unclear, I will gladly attempt to clarify. I have looked into Passing parameter to SQL Server. I've attempted to correct my code where I thought mistakes were made, though I'm still erroring.

How can I pass the entered date on my frmMain to my SQL Server as a parameter @date ?

EDIT:

When btnSubmit is clicked:

private void btnSubmit_Click(object sender, EventArgs e)
{
    string date = uDate;
    string day = date.Substring(3, 2);
    string month = date.Substring(0, 2);
    string year = date.Substring(8);
    string conStr = "Data Source=" + ConfigurationManager.AppSettings["DataSource"] + "Initial Catalog=" + ConfigurationManager.AppSettings["InitialCatalog"] + "Integrated Security=True;";

    using (var conn = new SqlConnection(conStr))
    {
        conn.Open();

        SqlCommand cmd = new SqlCommand("Submit", conn);
        cmd.CommandType = CommandType.StoredProcedure;                
    }            

    string suffix = @"\myFile.txt";
    date = year + month + day;
    string prefix = @"\\myDirectory\";
    string fileName = prefix + date + suffix;

    ImportList(fileName);
}

ImportList() then collects the data, reads through the file, and then sends the data through another method located on Method.cs:

public void Send(string StoredProcedure, string TableType)
{
    DataTable table = new DataTable();
    DataRow Row;

    //Gets Column info from list and creates columns in table         
    foreach (var column in ExportList[0])
    {
        table.Columns.Add(column.Key, column.Value.GetType());
    }

    foreach(var item in ExportList)
    {
        Row = table.NewRow();

        foreach (var column in item)
        { 
            Row[column.Key] = column.Value;     
        }

        table.Rows.Add(Row);
    }

    //send data table to stored procedure
    SendTable(table, StoredProcedure, TableType);
}

Answer 1

I believe the problem is in your construction of the parameter.

SqlParameter dtparam = new SqlParameter("@date", table);

That constructor is taking the second parameter as the value. Here is a modified snippet of your code that should work better.

 ...
 SqlParameter dtparam = new SqlParameter("@date", date);
 dtparam.SqlDbType = SqlDbType.NVarChar;
 dtparam.TypeName = "dbo." + TableType;
 ...

Edit: Fikse is right you also need to not set your type to structured

Answer 2

You set the data type of @date to a structured type (think table) rather than a nvarchar. The provider is trying to enumerate through the structured parameter.

Change

dtparam.SqlDbType = SqlDbType.Structured;

To

dtparam.SqlDbType = SqlDbType.NVarChar;