The most efficient way to find a list of values ​​in a SQL Server table

Question

I am working on a Win-Form C # application that is linked to a SQL Server database. I have a situation where I have to parse data from an Excel worksheet and look for each value in all columns of the table. And at the end, display the rows of the table whose value matches.

Now what i do

• I have analyzed the whole Excel sheet in a DataTable
• I have a stored procedure on an SQL server that accepts string input and looks for it in all columns of the table and returns a row if it is matched.
• Now I pass each Datatable value (extracted from the excel sheet) to the stored procedure for the search.

Please advise me if this is effective, or give me a suggestion for its effective solution.

Answer 1

You can use the parameter Table Valued, and in SQL you can use cursoreither loopthe given table and look for each column in the SQL table. Here is a simple example.

Create a new database Tpye

CREATE TYPE [dbo].[SearchInDB] AS TABLE(
[Id] [int] NOT NULL
)

And in SP you pass this type from C # code. Your SP will get it like this

ALTER PROCEDURE [dbo].[YourSPNameHere]  
@DataToSearch dbo.SearchInDB ReadOnly  
AS  
BEGIN  
--Your SP logic here
END

And in your code you will create DataTableand populate a datatable with values ​​and pass them to SP like this

DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
//Add rows in datatable with values
DataRow dr = dt.NewRow();
dr["Id"] = 10;
dt.Rows.Add(dr);

//Now pass this table to SP as parameter
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@DataToSearch"; 
parameter.SqlDbType = System.Data.SqlDbType.Structured; 
parameter.Value = dt;

Note

I added only one column if you need to add another column. To get the values ​​from the parameter passed to SP, you have to use a loop or use the cursor. Here is a link for another example

Answer 2

SSIS- SQL Server, excel