CODEWITHSUNDEEP
c#sql-serverstored-procedures
John Pietrar
John Pietrar

Reputation: 543

Give input parameter of stored procedure from file

I have a stored procedure with 3 parameters,and I think I am not doing right the first parameter of my stored procedure.

string path = @"D:\test2";
var ngd = Directory.EnumerateFiles(path, "*.txt").Select(file => File.ReadAllLines(file)).FirstOrDefault();
using (SqlConnection connection = new SqlConnection("Data Source=;Initial Catalog=;User ID=;Password="))
{
    using (SqlCommand cmd = new SqlCommand("usp_SaveData", connection))
    {
        try
        {
            await Task.Delay(1500);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@request_xml", SqlDbType.NVarChar, 999999).Value = ngd;
            cmd.Parameters.Add("@ST", SqlDbType.NVarChar, 50).Value = MachineName;
            cmd.Parameters.Add("@SN", SqlDbType.NVarChar, 50).Value = listBoxItem;
            cmd.ExecuteNonQuery();
            MessageBox.Show("Good");
            connection.Open();
        }
        catch
        {
            MessageBox.Show("Verification failed!");
        }
    }
}

I ran my stored procedure in SQL and everything worked fine,I ran a different procedure that needs only the @ST,@SN and it worked but now in this procedure where I give the request_xml as

var ngd = Directory.EnumerateFiles(path, "*.txt").Select(file => File.ReadAllLines(file)).FirstOrDefault();

it's not working...

My question is,should I store the data from the file differently in a variable?Is there a "correct way" to store the file in a variable that can be used by the input parameter ?

Upvotes: 3

Views: 1265

Answers (1)

Ted
Ted

Reputation: 4067

The problem is that you are using ReadAllLines which returns an array of string with each element being a line from the file.

so in the line below you are actually passing a string[] rather than a string

cmd.Parameters.Add("@request_xml", SqlDbType.NVarChar, 999999).Value = ngd;

You should use ReadAllText instead, which returns a single string

var ngd = Directory.GetFiles(path, "*.txt").Select(file => File.ReadAllText(file)).FirstOrDefault();

Upvotes: 3

Related Questions