Jak korzystać z DbContext.Database.SqlQuery <TElement> (sql, params) z procedurą przechowywaną? Pierwszy kod EF CTP5


250

Mam procedurę składowaną, która ma trzy parametry i próbuję użyć następujących do zwrócenia wyników:

context.Database.SqlQuery<myEntityType>("mySpName", param1, param2, param3);

Na początku próbowałem użyć SqlParameterobiektów jako parametrów, ale to nie zadziałało i rzuciłem SqlExceptionnastępujący komunikat:

Procedura lub funkcja „mySpName” oczekuje parametru „@ param1”, który nie został podany.

Więc moje pytanie brzmi, jak można użyć tej metody z procedurą składowaną, która oczekuje parametrów?

Dzięki.


Jakiej wersji programu SQL Server używasz? Mam problem z kodem, który działa w 2008 r. W trybie zgodnym (90), ale kiedy uruchomię go w stosunku do 2005 r., Kończy się to błędem składni.
Gats

4
@Gats - Miałem ten sam problem z SQL 2005. Dodaj „EXEC” przed nazwą procedury składowanej. Opublikowałem te informacje tutaj w celu późniejszego wykorzystania: stackoverflow.com/questions/6403930/...
Dan Mork

Odpowiedzi:


389

Należy podać instancje SqlParameter w następujący sposób:

context.Database.SqlQuery<myEntityType>(
    "mySpName @param1, @param2, @param3",
    new SqlParameter("param1", param1),
    new SqlParameter("param2", param2),
    new SqlParameter("param3", param3)
);

3
Jak sprawiłbyś, by ta metoda działała z typami zerowalnymi? Próbowałem tego z zerowymi wartościami dziesiętnymi, ale gdy wartości dziesiętne są zerowe, pojawiają się błędy informujące o braku parametru. Jednak metoda opisana poniżej przez @DanMork działa znaleźć.
Paul Johnson

2
Czy podanie DbNull.Valuezamiast wartości zerowej rozwiązuje problem?
Alireza,

29
Możesz także użyć składni \ @ p #, aby uniknąć używania SqlParameter jak w kontekście.Database.SqlQuery <myEntityType („mojaSpName \ @ p0, \ @ p1, \ @ p2”, param1, param2, param3). Źródło: msdn.microsoft.com/en-US/data/jj592907 . (Uwaga: musiał użyć \ @, aby uniknąć powiadomień użytkownika, należy czytać bez ukośnika).
Marco

3
Jeśli używasz parametrów DateTime, musisz również określić typ parametru, a nie tylko nazwę i wartość. Na przykład: dbContext.Database.SqlQuery <Faktura> („spGetInvoices @dateFrom, @dateTo”, nowy SqlParameter {ParameterName = „dateFrom”, SqlDbType = SqlDbType.DateTime, wartość = startDate}, nowy parametr SqlParametr = parametr SqlDbType = SqlDbType.DateTime, Value = endDate}); Kolejną ważną rzeczą jest przestrzeganie kolejności parametrów.
Francisco Goldenstein,

czy możesz uprzejmie sprawdzić, co robię źle? Podążam za tobą, ale nie ma żadnego wpływu na stackoverflow.com/questions/27926598/…
Toxic

129

Możesz także użyć parametru „sql” jako specyfikatora formatu:

context.Database.SqlQuery<MyEntityType>("mySpName @param1 = {0}", param1)

Musiałem głosować za tym. Chociaż nie został zaakceptowany jako odpowiedź, jest o wiele łatwiejszy do napisania niż rozwiązanie wybrane jako odpowiedź.
Nikkoli,

10
Ta składnia trochę mnie niepokoi. Czy byłoby podatne na iniekcję SQL? Zakładam, że EF działa „EXEC mySpName @ Param1 =” i byłoby możliwe wysłanie „x 'GO [złośliwego skryptu]” i spowodowanie pewnych problemów?
Tom Halladay,

10
@TomHalladay nie ma ryzyka wstrzyknięcia SQL - metoda nadal będzie cytować i zmieniać parametry na podstawie ich typu, tak samo jak parametry @. Zatem dla parametru ciągu użyłbyś „WYBIERZ * OD UŻYTKOWNIKÓW GDZIE email = {0}” bez cudzysłowów w wyciągu.
Ross McNab

w moim przypadku mamy wiele opcjonalnych parametrów dla SP i nie działały wywołania z SqlParameters, ale ten format załatwi sprawę, wystarczyło dodać „EXEC” na początku. Dzięki.
Onur Topal

1
Ta odpowiedź jest przydatna, jeśli musisz określić parametry proc z parametrami opcjonalnymi. Przykład, który nie działa: ProcName @optionalParam1 = @opVal1, @optionalParam2 = @opVal2 Przykład, który działa:ProcName @optionalParam1 = {0}, @optionalParam2 = {1}
Garrison Neely

72

To rozwiązanie jest (tylko) dla SQL Server 2005

Jesteście ratownikami, ale jak powiedział @Dan Mork, musicie dodać EXEC do miksu. To, co mnie potknęło, to:

  • „EXEC” przed nazwą procesu
  • Przecinki między Params
  • Odcinanie „@” w definicjach Param (nie jestem pewien, czy ten bit jest wymagany).

:

context.Database.SqlQuery<EntityType>(
    "EXEC ProcName @param1, @param2", 
    new SqlParameter("param1", param1), 
    new SqlParameter("param2", param2)
);

21
+1. Żadna z wyżej głosowanych odpowiedzi nie obejmuje exec, ale mogę potwierdzić, że dostaję wyjątek, jeśli go pominę.
Jordan Gray,

Dziękuję, otrzymałem błąd, dodałem EXEC i błąd zniknął. Dziwne było to, że zrobiłem context.Database.SqlQuery <EntityType> („ProcName” + param1 + „”, „” + param2 + ””); działało, ale jeśli dodałem parametry, nie działało, dopóki nie dodałem słowa kluczowego EXEC.
Solmead,

2
FYI: Nie potrzebuję execsłowa kluczowego. +1 za usunięcie @ z parametrów, które zawsze mnie psują.
Nathan Koop

+1, brakowało mi EXEC i ciągle otrzymywałem wyjątki Sql z komunikatem: Niepoprawna składnia w pobliżu 'procName'.
A. Murray,

1
@Ziggler masz rok 2005 lub nowszy? Słowo kluczowe EXEC było głównie problemem dla tych z nas, którzy wystąpili przeciwko 2005 r.
Tom Halladay

15
return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
new object[] { param1, param2, param3 });

//Lub

using(var context = new MyDataContext())
{
return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
new object[] { param1, param2, param3 }).ToList();
}

//Lub

using(var context = new MyDataContext())
{
object[] parameters =  { param1, param2, param3 };

return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
parameters).ToList();
}

//Lub

using(var context = new MyDataContext())
{  
return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
param1, param2, param3).ToList();
}

to działa dla mnie dla Assembly EntityFramework.dll, v4.4.0.0
Thulasiram

2
jeśli używasz za pomocą (var context = new MyDataContext ()), to .ToList () jest obowiązkowe.
Thulasiram

Spędziłem sporo czasu, aby odkryć, że .ToList () jest obowiązkowy, aby uzyskać poprawny zestaw wyników.
Halim

8

Większość odpowiedzi jest krucha, ponieważ opierają się na kolejności parametrów SP. Lepiej nazwać parametry Stored Proc i nadać im sparametryzowane wartości.

Aby korzystać z Nazwanych parametrów podczas dzwonienia do SP, nie martwiąc się o kolejność parametrów

Używanie nazwanych parametrów SQL Server z ExecuteStoreQuery i ExecuteStoreCommand

Opisuje najlepsze podejście. Lepsze niż odpowiedź Dana Morka tutaj.

  • Nie polega na łączeniu łańcuchów i nie opiera się na kolejności parametrów zdefiniowanych w SP.

Na przykład:

var cmdText = "[DoStuff] @Name = @name_param, @Age = @age_param";
var sqlParams = new[]{
   new SqlParameter("name_param", "Josh"),
   new SqlParameter("age_param", 45)
};

context.Database.SqlQuery<myEntityType>(cmdText, sqlParams)

Wydaje się, że „params” jest zastrzeżonym słowem kluczowym, więc nie sądzę, że można go tak używać. W przeciwnym razie była to dla mnie pomocna odpowiedź. Dzięki!
ooXei1sh

@ ooXei1sh - naprawiono, używając sqlParamszmiennej
Don Cheadle

możesz użyć przedrostka @, aby użyć słowa zastrzeżonego, ale tak naprawdę nie powinieneś
StingyJack

6
db.Database.SqlQuery<myEntityType>("exec GetNewSeqOfFoodServing @p0,@p1,@p2 ", foods_WEIGHT.NDB_No, HLP.CuntryID, HLP.ClientID).Single()

lub

db.Database.SqlQuery<myEntityType>(
    "exec GetNewSeqOfFoodServing @param1, @param2", 
    new SqlParameter("param1", param1), 
    new SqlParameter("param2", param2)
);

lub

var cmdText = "exec [DoStuff] @Name = @name_param, @Age = @age_param";
var @params = new[]{
   new SqlParameter("name_param", "Josh"),
   new SqlParameter("age_param", 45)
};

db.Database.SqlQuery<myEntityType>(cmdText, @params)

lub

db.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
new object[] { param1, param2, param3 }).ToList();

3

Używam tej metody:

var results = this.Database.SqlQuery<yourEntity>("EXEC [ent].[GetNextExportJob] {0}", ProcessorID);

Podoba mi się, ponieważ po prostu wprowadzam Guids i Datetimes, a SqlQuery wykonuje dla mnie całe formatowanie.


1

Odpowiedź @Toma Halladaya jest poprawna, z wzmianką o tym, że kupujesz również sprawdzanie wartości zerowych i wysyłasz DbNullable, jeśli parametry są zerowe, ponieważ otrzymujesz wyjątek, taki jak

Sparametryzowane zapytanie „...” oczekuje parametru „@parameterName”, który nie został podany.

Pomogło mi coś takiego

public static object GetDBNullOrValue<T>(this T val)
{
    bool isDbNull = true;
    Type t = typeof(T);

    if (Nullable.GetUnderlyingType(t) != null)
        isDbNull = EqualityComparer<T>.Default.Equals(default(T), val);
    else if (t.IsValueType)
        isDbNull = false;
    else
        isDbNull = val == null;

    return isDbNull ? DBNull.Value : (object) val;
}

(uznanie za metodę trafia na https://stackoverflow.com/users/284240/tim-schmelter )

Następnie użyj go w następujący sposób:

new SqlParameter("@parameterName", parameter.GetValueOrDbNull())

lub innym rozwiązaniem, prostszym, ale nie ogólnym byłoby:

new SqlParameter("@parameterName", parameter??(object)DBNull.Value)

0

Miałem ten sam komunikat o błędzie, gdy pracowałem z wywołaniem procedury składowanej, która pobiera dwa parametry wejściowe i zwraca 3 wartości za pomocą instrukcji SELECT, i rozwiązałem problem jak poniżej w Pierwszym podejściu do kodu EF

 SqlParameter @TableName = new SqlParameter()
        {
            ParameterName = "@TableName",
            DbType = DbType.String,
            Value = "Trans"
        };

SqlParameter @FieldName = new SqlParameter()
        {
            ParameterName = "@FieldName",
            DbType = DbType.String,
            Value = "HLTransNbr"
        };


object[] parameters = new object[] { @TableName, @FieldName };

List<Sample> x = this.Database.SqlQuery<Sample>("EXEC usp_NextNumberBOGetMulti @TableName, @FieldName", parameters).ToList();


public class Sample
{
    public string TableName { get; set; }
    public string FieldName { get; set; }
    public int NextNum { get; set; }
}

AKTUALIZACJA : Wygląda na to, że brakujące słowo kluczowe EXEC w programie SQL SERVER 2005 stwarza problem. Aby umożliwić działanie ze wszystkimi wersjami SQL SERVER, zaktualizowałem swoją odpowiedź i dodałem EXEC w poniższym wierszu

 List<Sample> x = this.Database.SqlQuery<Sample>(" EXEC usp_NextNumberBOGetMulti @TableName, @FieldName", param).ToList();

Zobacz poniższy link. Nie ma potrzeby używania exec msdn.microsoft.com/en-us/data/jj592907.aspx
Ziggler

0

Zrobiłem mój z EF 6.x w ten sposób:

using(var db = new ProFormDbContext())
            {
                var Action = 1; 
                var xNTID = "A239333";

                var userPlan = db.Database.SqlQuery<UserPlan>(
                "AD.usp_UserPlanInfo @Action, @NTID", //, @HPID",
                new SqlParameter("Action", Action),
                new SqlParameter("NTID", xNTID)).ToList();


            }

Nie podwajaj parametru sql, niektórzy ludzie palą się, robiąc to ze swoją zmienną

var Action = new SqlParameter("@Action", 1);  // Don't do this, as it is set below already.
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.