Calling stored procedures with parameters in PetaPoco
20
I want to be able to call a stored proc with named parameters in PetaPoco.
In order to call a stored proc that does a search/fetch:
Can I do something like this:
return db.Fetch<Customer>("EXEC SP_FindCust",
new SqlParameter("@first_name", fName),
new SqlParameter("@last_name", lName),
new SqlParameter("@dob", dob));
Also, how can I call a stored proc that does an insert?
return db.Execute("EXEC InsertCust @CustID = 1, @CustName = AAA")
Thanks,
Nac
sql petapoco
edited Aug 5 '11 at 1:18
Chandu
64.9k15110117
asked Aug 5 '11 at 1:15
Tech XieTech Xie
58741023
add a comment |
20
I want to be able to call a stored proc with named parameters in PetaPoco.
In order to call a stored proc that does a search/fetch:
Can I do something like this:
return db.Fetch<Customer>("EXEC SP_FindCust",
new SqlParameter("@first_name", fName),
new SqlParameter("@last_name", lName),
new SqlParameter("@dob", dob));
Also, how can I call a stored proc that does an insert?
return db.Execute("EXEC InsertCust @CustID = 1, @CustName = AAA")
Thanks,
Nac
sql petapoco
edited Aug 5 '11 at 1:18
Chandu
64.9k15110117
asked Aug 5 '11 at 1:15
Tech XieTech Xie
58741023
2
I had to set EnableAutoSelect = false. otherwise petapoco kept trying to put a select clause in from of my EXEC
– Al W
Nov 6 '12 at 21:51
3
If you add a;before the EXEC PetaPoco won't add the SELECT:.Execute(";EXEC InsertCust @C
– Paddy
Jan 10 '13 at 15:05
Adding that semicolon is a little like doing your own SQL injection. I think thatdb.EnableAutoSelect = falseis the cleaner solution.
– asherber
May 19 '17 at 20:31
add a comment |
20
20
20
6
I want to be able to call a stored proc with named parameters in PetaPoco.
In order to call a stored proc that does a search/fetch:
Can I do something like this:
return db.Fetch<Customer>("EXEC SP_FindCust",
new SqlParameter("@first_name", fName),
new SqlParameter("@last_name", lName),
new SqlParameter("@dob", dob));
Also, how can I call a stored proc that does an insert?
return db.Execute("EXEC InsertCust @CustID = 1, @CustName = AAA")
Thanks,
Nac
sql petapoco
edited Aug 5 '11 at 1:18
Chandu
64.9k15110117
asked Aug 5 '11 at 1:15
Tech XieTech Xie
58741023
I want to be able to call a stored proc with named parameters in PetaPoco.
In order to call a stored proc that does a search/fetch:
Can I do something like this:
return db.Fetch<Customer>("EXEC SP_FindCust",
new SqlParameter("@first_name", fName),
new SqlParameter("@last_name", lName),
new SqlParameter("@dob", dob));
Also, how can I call a stored proc that does an insert?
return db.Execute("EXEC InsertCust @CustID = 1, @CustName = AAA")
Thanks,
Nac
sql petapoco
sql petapoco
edited Aug 5 '11 at 1:18
Chandu
64.9k15110117
asked Aug 5 '11 at 1:15
Tech XieTech Xie
58741023
edited Aug 5 '11 at 1:18
Chandu
64.9k15110117
asked Aug 5 '11 at 1:15
Tech XieTech Xie
58741023
edited Aug 5 '11 at 1:18
Chandu
64.9k15110117
edited Aug 5 '11 at 1:18
Chandu
64.9k15110117
edited Aug 5 '11 at 1:18
Chandu
64.9k15110117
64.9k15110117
asked Aug 5 '11 at 1:15
Tech XieTech Xie
58741023
asked Aug 5 '11 at 1:15
Tech XieTech Xie
58741023
asked Aug 5 '11 at 1:15
Tech XieTech Xie
58741023
58741023
2
I had to set EnableAutoSelect = false. otherwise petapoco kept trying to put a select clause in from of my EXEC
– Al W
Nov 6 '12 at 21:51
3
If you add a;before the EXEC PetaPoco won't add the SELECT:.Execute(";EXEC InsertCust @C
– Paddy
Jan 10 '13 at 15:05
Adding that semicolon is a little like doing your own SQL injection. I think thatdb.EnableAutoSelect = falseis the cleaner solution.
– asherber
May 19 '17 at 20:31
add a comment |
2
I had to set EnableAutoSelect = false. otherwise petapoco kept trying to put a select clause in from of my EXEC
– Al W
Nov 6 '12 at 21:51
3
If you add a;before the EXEC PetaPoco won't add the SELECT:.Execute(";EXEC InsertCust @C
– Paddy
Jan 10 '13 at 15:05
Adding that semicolon is a little like doing your own SQL injection. I think thatdb.EnableAutoSelect = falseis the cleaner solution.
– asherber
May 19 '17 at 20:31
2
2
I had to set EnableAutoSelect = false. otherwise petapoco kept trying to put a select clause in from of my EXEC
– Al W
Nov 6 '12 at 21:51
I had to set EnableAutoSelect = false. otherwise petapoco kept trying to put a select clause in from of my EXEC
– Al W
Nov 6 '12 at 21:51
3
3
If you add a
; before the EXEC PetaPoco won't add the SELECT: .Execute(";EXEC InsertCust @C– Paddy
Jan 10 '13 at 15:05
If you add a
; before the EXEC PetaPoco won't add the SELECT: .Execute(";EXEC InsertCust @C– Paddy
Jan 10 '13 at 15:05
Adding that semicolon is a little like doing your own SQL injection. I think that
db.EnableAutoSelect = false is the cleaner solution.– asherber
May 19 '17 at 20:31
Adding that semicolon is a little like doing your own SQL injection. I think that
db.EnableAutoSelect = false is the cleaner solution.– asherber
May 19 '17 at 20:31
add a comment |
2 Answers
2
active
oldest
votes
26
Update:
I tried the following for fetch and insert and it worked perfectly:
var s = PetaPoco.Sql.Builder.Append("EXEC SP_FindCust @@last_name = @0", lname);
s.Append(", @@first_name = @0", fName);
s.Append(", @@last_name = @0", lName);
s.Append(", @@dob = @0", dob);
return db.Query<Cust>(s);
This can be improved further to pass SQL parameters.
edited Mar 27 '18 at 15:37
Gaspa79
2,56622446
answered Aug 5 '11 at 17:54
Tech XieTech Xie
58741023
Hi there, that's great thanks, but do you know how I can return the identity from the insert? My stored proc is INSERT INTO t_Book VALUES(@BookName, @ParentBookId) SELECT @BookId = SCOPE_IDENTITY()
– Dr. ABT
Sep 6 '11 at 21:27
That's it: using double @ for the store procedure parameters and single @ for petapoco parameters does the trick. Thanks.
– JCallico
Nov 25 '11 at 15:17
add a comment |
0
As of v6.0.344-beta, PetaPoco now supports stored procedures without needing to use EXEC. See https://github.com/CollaboratingPlatypus/PetaPoco/wiki/Stored-procedures
answered Nov 24 '18 at 16:09
asherberasherber
1,440910
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f6950420%2fcalling-stored-procedures-with-parameters-in-petapoco%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
26
Update:
I tried the following for fetch and insert and it worked perfectly:
var s = PetaPoco.Sql.Builder.Append("EXEC SP_FindCust @@last_name = @0", lname);
s.Append(", @@first_name = @0", fName);
s.Append(", @@last_name = @0", lName);
s.Append(", @@dob = @0", dob);
return db.Query<Cust>(s);
This can be improved further to pass SQL parameters.
edited Mar 27 '18 at 15:37
Gaspa79
2,56622446
answered Aug 5 '11 at 17:54
Tech XieTech Xie
58741023
Hi there, that's great thanks, but do you know how I can return the identity from the insert? My stored proc is INSERT INTO t_Book VALUES(@BookName, @ParentBookId) SELECT @BookId = SCOPE_IDENTITY()
– Dr. ABT
Sep 6 '11 at 21:27
That's it: using double @ for the store procedure parameters and single @ for petapoco parameters does the trick. Thanks.
– JCallico
Nov 25 '11 at 15:17
add a comment |
26
Update:
I tried the following for fetch and insert and it worked perfectly:
var s = PetaPoco.Sql.Builder.Append("EXEC SP_FindCust @@last_name = @0", lname);
s.Append(", @@first_name = @0", fName);
s.Append(", @@last_name = @0", lName);
s.Append(", @@dob = @0", dob);
return db.Query<Cust>(s);
This can be improved further to pass SQL parameters.
edited Mar 27 '18 at 15:37
Gaspa79
2,56622446
answered Aug 5 '11 at 17:54
Tech XieTech Xie
58741023
Hi there, that's great thanks, but do you know how I can return the identity from the insert? My stored proc is INSERT INTO t_Book VALUES(@BookName, @ParentBookId) SELECT @BookId = SCOPE_IDENTITY()
– Dr. ABT
Sep 6 '11 at 21:27
That's it: using double @ for the store procedure parameters and single @ for petapoco parameters does the trick. Thanks.
– JCallico
Nov 25 '11 at 15:17
add a comment |
26
26
26
Update:
I tried the following for fetch and insert and it worked perfectly:
var s = PetaPoco.Sql.Builder.Append("EXEC SP_FindCust @@last_name = @0", lname);
s.Append(", @@first_name = @0", fName);
s.Append(", @@last_name = @0", lName);
s.Append(", @@dob = @0", dob);
return db.Query<Cust>(s);
This can be improved further to pass SQL parameters.
edited Mar 27 '18 at 15:37
Gaspa79
2,56622446
answered Aug 5 '11 at 17:54
Tech XieTech Xie
58741023
Update:
I tried the following for fetch and insert and it worked perfectly:
var s = PetaPoco.Sql.Builder.Append("EXEC SP_FindCust @@last_name = @0", lname);
s.Append(", @@first_name = @0", fName);
s.Append(", @@last_name = @0", lName);
s.Append(", @@dob = @0", dob);
return db.Query<Cust>(s);
This can be improved further to pass SQL parameters.
edited Mar 27 '18 at 15:37
Gaspa79
2,56622446
answered Aug 5 '11 at 17:54
Tech XieTech Xie
58741023
edited Mar 27 '18 at 15:37
Gaspa79
2,56622446
edited Mar 27 '18 at 15:37
Gaspa79
2,56622446
edited Mar 27 '18 at 15:37
Gaspa79
2,56622446
2,56622446
answered Aug 5 '11 at 17:54
Tech XieTech Xie
58741023
answered Aug 5 '11 at 17:54
Tech XieTech Xie
58741023
answered Aug 5 '11 at 17:54
Tech XieTech Xie
58741023
58741023
Hi there, that's great thanks, but do you know how I can return the identity from the insert? My stored proc is INSERT INTO t_Book VALUES(@BookName, @ParentBookId) SELECT @BookId = SCOPE_IDENTITY()
– Dr. ABT
Sep 6 '11 at 21:27
That's it: using double @ for the store procedure parameters and single @ for petapoco parameters does the trick. Thanks.
– JCallico
Nov 25 '11 at 15:17
add a comment |
Hi there, that's great thanks, but do you know how I can return the identity from the insert? My stored proc is INSERT INTO t_Book VALUES(@BookName, @ParentBookId) SELECT @BookId = SCOPE_IDENTITY()
– Dr. ABT
Sep 6 '11 at 21:27
That's it: using double @ for the store procedure parameters and single @ for petapoco parameters does the trick. Thanks.
– JCallico
Nov 25 '11 at 15:17
Hi there, that's great thanks, but do you know how I can return the identity from the insert? My stored proc is INSERT INTO t_Book VALUES(@BookName, @ParentBookId) SELECT @BookId = SCOPE_IDENTITY()
– Dr. ABT
Sep 6 '11 at 21:27
Hi there, that's great thanks, but do you know how I can return the identity from the insert? My stored proc is INSERT INTO t_Book VALUES(@BookName, @ParentBookId) SELECT @BookId = SCOPE_IDENTITY()
– Dr. ABT
Sep 6 '11 at 21:27
That's it: using double @ for the store procedure parameters and single @ for petapoco parameters does the trick. Thanks.
– JCallico
Nov 25 '11 at 15:17
That's it: using double @ for the store procedure parameters and single @ for petapoco parameters does the trick. Thanks.
– JCallico
Nov 25 '11 at 15:17
add a comment |
0
As of v6.0.344-beta, PetaPoco now supports stored procedures without needing to use EXEC. See https://github.com/CollaboratingPlatypus/PetaPoco/wiki/Stored-procedures
answered Nov 24 '18 at 16:09
asherberasherber
1,440910
add a comment |
0
As of v6.0.344-beta, PetaPoco now supports stored procedures without needing to use EXEC. See https://github.com/CollaboratingPlatypus/PetaPoco/wiki/Stored-procedures
answered Nov 24 '18 at 16:09
asherberasherber
1,440910
add a comment |
0
0
0
As of v6.0.344-beta, PetaPoco now supports stored procedures without needing to use EXEC. See https://github.com/CollaboratingPlatypus/PetaPoco/wiki/Stored-procedures
answered Nov 24 '18 at 16:09
asherberasherber
1,440910
As of v6.0.344-beta, PetaPoco now supports stored procedures without needing to use EXEC. See https://github.com/CollaboratingPlatypus/PetaPoco/wiki/Stored-procedures
answered Nov 24 '18 at 16:09
asherberasherber
1,440910
answered Nov 24 '18 at 16:09
asherberasherber
1,440910
answered Nov 24 '18 at 16:09
asherberasherber
1,440910
answered Nov 24 '18 at 16:09
asherberasherber
1,440910
1,440910
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f6950420%2fcalling-stored-procedures-with-parameters-in-petapoco%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
I had to set EnableAutoSelect = false. otherwise petapoco kept trying to put a select clause in from of my EXEC
– Al W
Nov 6 '12 at 21:51
3
If you add a
;before the EXEC PetaPoco won't add the SELECT:.Execute(";EXEC InsertCust @C– Paddy
Jan 10 '13 at 15:05
Adding that semicolon is a little like doing your own SQL injection. I think that
db.EnableAutoSelect = falseis the cleaner solution.– asherber
May 19 '17 at 20:31