Does SQL Server Compact Edition or sqlmobile support stored procedure?
No, SQL Server CE doesn't suport stored procedures.
Sqlce is a local database with a single file. It's an in-process client database. It's server less. It's code free - no stored procedures (sprocs). There is no need for sprocs since the database is just a local data store. The business logic is in the application. Therefore you will have to use inline sql command in your application. Use SqlCeCommand.CommandText Property to specify sql command text.
There was a
great debate on the stored procedures in sqlce. Microsoft doesn't intend to support stored procedures in sqlce as in its big brother SQL Server.
However, you can use the following strategies to achieve similar thing to a stored procedures in sqlce:
Place all T-SQL statements in one place Resource Designer or Settings Designer in Visual Studio.
Like procs which are stored in a single location on the server, we can store all T-SQL statements in a common location in either Resource Designer or Settings Designer in Visual Studio. This will eliminate the need to search through all the code to find queries that must be modified. Please note that the Settings Designer is not available in device projects.
For example, add a new resource named as SqlQueries.resx to project myProj, then add a query string myQuery:
public SqlCeResultSet GetAllCustomers(ResultSetOptions resultSetOptions)
Code:{
SqlCeCommand cmd = new SqlCeCommand(
myProj.SqlQueries.myQuery,
GetConnection());
cmd.Connection.Open();
return cmd.ExecuteResultSet(resultSetOptions);
}
With parameters
Code:SELECT SchoolID, School Name
FROM Schools
WHERE City = @City
Code:public SqlCeResultSet GetSchoolsByName(string SchoolName, ResultSetOptions resultSetOptions)
{
SqlCeCommand cmd = new SqlCeCommand(
myProj.SqlQueries.myQuery,
GetConnection());
// Pass the SchoolName parameter
cmd.Parameters.Add("@City", City);
cmd.Connection.Open();
return cmd.ExecuteResultSet(resultSetOptions);
}
or
Code:public int SchoolInsert(string SchoolID, string city)
{
DataValidationErrorCollection dataValidationErrors = new DataValidationErrorCollection();
if (city.Trim().Length == 0)
dataValidationErrors.Add(new DataValidationErrorItem("City",
City, "A value for City must be provided"));
using (SqlCeCommand cmd = new SqlCeCommand(myProj.SqlQueries.myQuery,
GetConnection()))
{
cmd.Parameters.Add("@City", City);
cmd.Connection.Open();
return cmd.ExecuteNonQuery();
}
}
Now we can see another advantage of the centralisation of the sql statements. You don't need to place an sql statement in quote in the central location as we do for an inline sql statement. Using the resource designer or the Settings designer, we can simply copy/paste the query directly into SQL Server Management Studio to test the query.
Related:
SqlCeResultSet is an updateable, scrollable, and bindable cursor.
Quote:A cursor comprises a control structure for the successive traversal (and potential processing) of records in a result set. One can think of a database cursor as an iterator over the collection of rows in the result set.
Scrollable cursors
Programmers may declare cursors as scrollable or not scrollable. The scrollability indicates the direction in which a cursor can move.
With a non-scrollable cursor, also known as forward-only, one can FETCH each row at most once, and the cursor automatically moves to the immediately following row. A fetch operation after the last row has been retrieved positions the cursor after the last row and returns SQLSTATE 02000 (SQLCODE +100).
A program may position a scrollable cursor anywhere in the result set using the FETCH SQL statement. The keyword SCROLL must be specified when declaring the cursor. The default is NO SCROLL.
Source Wikipedia
