Click here to Skip to main content
15,890,282 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
How is it possible to pass an array of strings to an SQL Server stored procedure, and return another array of strings from the stored procedure back into c# code?

My problem is :- In my project, I am using Microsoft Interop Word to edit ms word files. Using this Interop, I parsed all the word in the MS word document into a string array. Now, I have to pass this string into a stored procedure. Once this is reached at the stored procedure, I have to compare these strings with a table in the database, and if there is a match found, I have to pass back the "found" word to the c# code.

I tried the foreach statement to pass the array of strings to the stored procedure, but I don't know why I am not getting anything in return.
Posted
Comments
Mahesh Bailwal 24-Aug-13 1:45am    
Please share your stored procedure and C# code calling it.
Gee Varghese 24-Aug-13 2:35am    
My stored procedure:-

USE [hrms]
GO
/****** Object: StoredProcedure [dbo].[sp_load_resumes] Script Date: 8/23/2013 10:59:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author: Roshan George V
-- =============================================


ALTER procedure [dbo].[sp_load_resumes]
@flag int=0,
@skill_name varchar(max)=null
as
if(@flag=0)
begin
select skill_name from tbl_skills where skill_name IN (' + @skill_name + ')
end
go


My c# code:-

string Doc_Content = doc.Content.Text;
string str = Doc_Content;
var words = str.Split(new char[] { ' ', ':', '\r', '\t' });
//var words = str.Split(new char[] { ':', '\r', '\t' });

SqlCommand cmd3 = new SqlCommand("sp_load_resumes", obj.con);
cmd3.CommandType = CommandType.StoredProcedure;
cmd3.Parameters.Add("@flag", 2);

//Loop with the foreach keyword.
/*foreach (var value in words)
{
cmd3.Parameters.Add("@skill_name", value);
}*/
for (int i = 0; i < words.Length; i++)
{
skill_words = string.Join(",", words[i].ToString());

}
//skill_words=string.Join(",",
cmd3.Parameters.Add("@skill_name", skill_words);
cmd3.ExecuteNonQuery();

//string t_return = cmd3.Parameters["@temp_skill_name"].Value.ToString();




DataTable dt = new DataTable();
SqlDataAdapter adt = new SqlDataAdapter(cmd3);
adt.Fill(dt);

ArrayList arr = new ArrayList();
foreach (DataRow row in dt.Rows)
{
arr.Add(row.ToString());
}
Label9.Text = arr[1].ToString();
Label8.Text = arr[2].ToString();
Label10.Text = arr[3].ToString();
Label11.Text = arr[4].ToString();
Mahesh Bailwal 24-Aug-13 8:18am    
Looking at your code I think you need to debug your code in two ways.
First create hard coded skill string instead of making it dynamically and pass it to stored procedure and check whether SP is working as expected.

Once you have checked that your SP is working fine then create skills string dynamically and check whether your code is making correct skill string or not.
Gee Varghese 24-Aug-13 9:24am    
Can you provide a sample?
Mahesh Bailwal 24-Aug-13 10:28am    
Sorry, I don't have any sample which will fit in your need.

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900