How to execute a query and stored procedure in Kentico CMS?
Some of the cases we need some query and storedprocedure execution in our webparts for retirve some data for our requirement. For this cases we may use some functions present in the some dll which are already defined over there.
Add following namespaces:-
SettingsProvider namespace contains QueryDataParameters class which contain list of Query parameter.
SqlClient name space contains functions for the query executions.
Following method is used for the Execute both the Query and the StoredProcedure.
Returns result of the query.
<param name="queryText">Query or stored procedure to be run</param>
< param name="queryParams">Query parameters</param>
< param name="queryType">Indicates it query is a SQL query or stored procedure</param>
< param name="requiresTransaction">If true, the query should run within transaction</param>
SettingsProvider.QueryTypeEnum this enumeration class contains two variable
// SQL query.
SQLQuery = 0,
StoredProcedure = 1,
Execution of a select Query:-
string Query = "SELECT * FROM custom_KenticoDemo where KenticoDemoID ="+ 2;
We have an dll called DataEngine which is a inbuild kentico dll which conatins class called ConnectionHelper class which had some predefined function by using that we execute the query ;
Let Consider the Following Method:
DataSet ds = ConnectionHelper.ExecuteQuery(Query, parameters,QueryTypeEnum.SQLQuery, true);
Query :It specify the query that u want to execute.
QueryDataParameters parameters = new QueryDataParameters();
It is a object of QueryDataParameters class By using its object you can send the parameters required for Query like:insert and update Query;
QueryTypeEnum-It is a enumuration class contain two variables called SQLQuery , StoredProcedure
if its a query then pass QueryTypeEnum.SQLQuery and if it is a stored procedure then pass QueryTypeEnum.StoredProcedure
This method return the data in a DataSet.Retrive the data from dataset and use as per requirement.
Strored Procedure:Create a stored procedure for Delete a record from a table called TestTable in the Database under kentico DataBase StoredProcedure:
Delete the record with id 3;
specify that id through the QueryDataParameters by creating a new obj of QueryDataParameters as AddParameter
ConnectionHelper.ExecuteQuery(procname, AddParameter.AddQuery,TypeEnum.StoredProcedure, true);