Stored Procedures in SQL CE version
Is it possible to create a Stored Procedure in SQL CE database?
In other words if i have a batch of SQL Statements that need to be executed in a sequence, instead of executing them individually is there any other procedure to execute them with one Connection from my application designed using .NET CF?
Regards,
Chakravarthy (DSKCHECK)
PS: Reply me atskcheck@gmail.com" href="mailto_3Cimg src="/library/msdn/emoticons/emotion-2.gif" alt="Big Smile" />skcheck@gmail.com">DSKCHECK @ GMAIL.COM too
Hi,
I would like to execute a batch of SQL Statements in my Pocket PC application. Currently we are executing them individually, for every SQL Connection we are opening new SQLCeCommand and using the .ExecuteNonQuery Method as mentioned below
cmdDDL = new SqlCeCommand(ddlCmdStr, conn); // These two are parameters
cmdDDL.CommandType = CommandType.Text;
cmdDDL.ExecuteNonQuery();
Is there any other method for execting the Batch of SQL Commends... i know that i can put all the commands in a Stored Procedure and execute the Procedure once... so that you can eliminate the connections for every SQL Statment.
So Can some one suggest me as how can i create a Stored Procedure in SQL CE database...
Thanks in advance,
Regards
Chakravarthy
Hi, I just spoke to the Data PM. Here is his reply from him as he was having some connectivity issues.
"Stored procedures aren't supported using SqlMobile, sorry. The approach you're using is the correct one for running several queries in a row against the local database."
In future you could send SQL CE / Whidbey related questions to the Smart Devices Forum.
http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=34
Thanks
Amit Chopra
Hi Chakravarthy,you can always put all your procedures in one procedure and call it only once.
ie:
CREATE PROCEDURE SampleMultiProc_SP
@p1 int = 0,
@p2 int = 0
AS
UPDATE tbl1 SET fld1 = @p2
UPDATE tbl2 SET fld1 = @p1 WHERE fld2 = @p2
GOand that procedure will do both updates...
According to a number of people on the web, SPs aren't supported in ce, but they are incorrect -
stored procedures can be used in sql server ce. As Jim Wilson explains in this blog
http://blog.jwhedgehog.com/200402archive001.asp, the CommandType of StoredProcedure isn't supported in RDA so this won't work (hence why most think it doesn't work at all):
SqlCommand cmd = new SqlCommand();
cmd.CommandText = “MyStoredProc '02/23/2004' ”;
cmd.CommandType = CommandType.StoredProcedure;
However, the solution is to use the exec command like this: SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(); rda.Pull("authors", "exec MyStoredProc '02/23/2004' ", ...);
- Art Gaisin
agaisi1@yahoo.com
