How to select specific rows of a SQL table without looping in .net
Suppose I have a table called "Na开发者_如何学Cmes"
ID | Name
---+--------------
1 | Bob
2 | Billy
3 | James
4 | John
5 | Tom
and on and on with thousands of names...
Suppose that I have written the following .net code to retrieve the names:
public DataSet selectFromNames()
{
const string SQL_STATEMENT =
@"SELECT Id, Name From Names;";
Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("MyConnection");
DbCommand command = db.GetSqlStringCommand(SQL_STATEMENT);
command.CommandType = CommandType.Text;
command.CommandText = SQL_STATEMENT;
DataSet ds = db.ExecuteDataSet(command);
return ds;
}
That works fine. What if I want a function that has an integer array parameter specifying the rows of the names I want to retrieve? How would I write that query?
I'm familiar with passing parameters as @parameter
, but I have no idea how to pass in an array with using the string builder to write something like where ID in (1,2,3)
which is ugly and error-prone.
Suggest using a SQL Server UDF to help you. Here's an article explaining how to implement this.
Basically you'd create a UDF to split a varchar()
into a table variable. Let's call it dbo.MySplit()
.
Then you could:
string sql = "SELECT * FROM Table WHERE ID IN (SELECT Item FROM dbo.MySplitUDF(@csv));";
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@csv", string.Join(",",myIntegerArray);
how about this (using LINQtoSQL) ?
//...
int[] idList = new int[] { 1, 2, 3, 4 };
var myNames = from n in db.Names
where idList.Contains(n.ID)
select n;
(lifted from Creating IN Queries With Linq To SQL)
精彩评论