c# - parameter - ¿Cómo traduzco una Lista<string> en un Parámetro Sql para una instrucción Sql In?

sqlparameter array in c#

Question

Esta pregunta ya tiene una respuesta aquí:

• Pass Array Parameter en SqlCommand 10 respuestas

Parece que estoy confundido sobre cómo realizar una instrucción In con un SqlParameter . Hasta ahora tengo el siguiente código:

cmd.CommandText = "Select dscr from system_settings where setting in @settings";
cmd.Connection = conn;
cmd.Parameters.Add(new SqlParameter("@settings", settingList));
reader = cmd.ExecuteReader();

settingsList es una List<string> . Cuando se llama a cmd.ExecuteReader() , obtengo una ArgumentException debido a que no puedo asignar una List<string> a "un tipo de proveedor conocido".

¿Cómo realizo (de manera segura) una consulta In con SqlCommand s?

Answer 1

Parece que intentas pasar un parámetro de valor múltiple, que la sintaxis SQL no va a hacer lo que esperas. Es posible que desee pasar un parámetro de valor de tabla.

Lea esto: http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-strings

específicamente: http://www.sommarskog.se/arrays-in-sql-2008.html#ListSqlDataRecord

private static void datatable_example() {
string [] custids = {"ALFKI", "BONAP", "CACTU", "FRANK"};
DataTable custid_list = new DataTable();
custid_list.Columns.Add("custid", typeof(String));
foreach (string custid in custids) {
DataRow dr = custid_list.NewRow();
dr["custid"] = custid;
custid_list.Rows.Add(dr);
}
using(SqlConnection cn = setup_connection()) {
using(SqlCommand cmd = cn.CreateCommand()) {
cmd.CommandText =
@"SELECT C.CustomerID, C.CompanyName
FROM Northwind.dbo.Customers C
WHERE C.CustomerID IN (SELECT id.custid FROM @custids id)";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@custids", SqlDbType.Structured);
cmd.Parameters["@custids"].Direction = ParameterDirection.Input;
cmd.Parameters["@custids"].TypeName = "custid_list_tbltype";
cmd.Parameters["@custids"].Value = custid_list;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
using (DataSet ds = new DataSet()) {
da.Fill(ds);
PrintDataSet(ds);
}
}
}
}

Answer 2

Podrías probar algo como esto:

string sql = "SELECT dscr FROM system_settings WHERE setting IN ({0})";
string[] paramArray = settingList.Select((x, i) => "@settings" + i).ToArray();
cmd.CommandText = string.Format(sql, string.Join(",", paramArray));
for (int i = 0; i < settingList.Count; ++i)
{
cmd.Parameters.Add(new SqlParameter("@settings" + i, settingList[i]));
}

Answer 3

Por lo general, paso la lista como una cadena separada por comas, y luego uso una función con valores de tabla para "dividir" la cadena en una tabla que luego puedo usar para unirme en otra consulta.

DECLARE @Settings TABLE (Sid INT)
INSERT INTO @Settings(Sid)
SELECT CAST(Items AS INT) FROM dbo.Split(@SettingsParameter, '','')

A menos que, por supuesto, esté utilizando SQL Server 2008, yo usaría los parámetros con valores de tabla.

Answer 4

Si está utilizando Sql Server 2008 o posterior, puede hacer uso de parámetros con valores de tabla, lo que le permite pasar una tabla de valores como parámetro. Desde .net, se define un tipo "estructurado" de SqlParameter y se establece el valor en algo que implementa IEnumerable.

Vea la referencia completa de MSDN con ejemplos aquí: http://msdn.microsoft.com/en-us/library/bb675163.aspx

Answer 5

Siempre uso mi propia función para crear parámetros como este:

public void SomeDataFunction() {
ArrayList params = GetParameters(someEntity);
CommandObject.Parameters.AddRange(parameters.ToArray());
}
public static ArrayList GetParameters(ISomeEntity entity) {
ArrayList result = new ArrayList {
OleDbUtility.NewDbParam("@Param1", OleDbType.Integer, , entity.Parameter1),
OleDbUtility.NewDbParam("@Param2", OleDbType.VarChar, 9, entity.Parameter2),
}
}
public static OleDbParameter NewDbParam(string parameterName, OleDbType dataType,
int size, object value) {
OleDbParameter result = new OleDbParameter(parameterName, dataType, size, string.Empty);
result.Value = value;
return result;
}

Answer 6

Usa XML, es bastante rápido para este escenario. Convierte tu lista en XML y simplemente pasa una cadena:

CREATE TABLE #myTempTable
( Letter VARCHAR(20) )
INSERT INTO #myTempTable (Letter) VALUES (''A''), (''B'')
Declare @xml XML = ''<a>A</a><a>B</a><a>C</a>''
Select * from #myTempTable
Where Letter in
(Select p.value(''.'', ''VARCHAR(40)'') AS [Letter] from @xml.nodes(''//a'') as t(p))
DROP TABLE #myTempTable