IDataStoreBase.SetSqlSelect(string statement, bool isValidation = true) Method

.NET Standard 2.x

Specifies the SQL SELECT statement for the DataStore.

Namespace: DWNet.Data

Assembly: DWNet.Data.dll

Syntax

   public bool SetSqlSelect(string statement, bool isValidation = true);

Parameters

statement System.String

A string whose value is the SELECT statement for the DataWindow object. The statement must structurally match the current SELECT statement (that is, it must return the same number of columns, the columns must be the same datatype, and the columns must be in the same order).

isValidation System.Boolean

Whether to validate the statement immediately.

If true, it validates the statement immediately.

If false, it does not validate the statement.

Default is true.

Returns

System.Boolean

Returns whether the statement is valid. If isValidation is false, it always returns false.

Remarks

Use SetSQLSelect to dynamically change the SQL SELECT statement for a DataWindow object in a script.

If the DataWindow object is updatable, it validates the SELECT statement against the database and DataWindow object column specifications when you call the SetSQLSelect method. Each column in the SQL SELECT statement must match the column type in the DataWindow object.

You must use the constructor of DataStore or the SetDataContext method to set the DataContext object before the SetSQLSelect method is executed.

If the new SELECT statement has a different table name in the FROM clause and the DataWindow object is updatable, then it must change the update information for the DataWindow object. It assumes the key columns are in the same positions as in the original definition. The following conditions would make the DataWindow object not updatable:

• There is more than one table in the FROM clause
• A DataWindow object update column is a computed column in the SELECT statement

If changing the SELECT statement makes the DataStore object not updatable, the DataStore cannot execute an Update method call for the DataWindow object in the future.

Examples

The following code example calls the SetSqlSelect method to modify the SQL Select statement by adding a criteria (budget>=200000) to it.

using DWNet.Data;
using System;

namespace Appeon.ApiDoc.IDataStoreBaseExamples
{
    public class SetSqlSelectExample
    {
        private readonly SchoolContext _context;

        public SetSqlSelectExample(SchoolContext dataContext)
        {
            // Sets the data context
            _context = dataContext;
        }

        public void Example()
        {
            // Instantiates a DataStore object with datawindow: d_department.
            var datastore = new DataStore("d_department", _context);

            datastore.Retrieve();

            Console.WriteLine("Before Update SQLSelect, Rowcount: {0}",
                datastore.RowCount);

            // Gets the SQL SELECT statement.
            string sqlSelect = datastore.GetSqlSelect();

            // Calls SetSqlSelect to add a criterion (budget>=200000) to the 
            // SQL SELECT statement.
            datastore.SetSqlSelect(sqlSelect + " where budget >= 200000");

            // Retrieves data again
            datastore.Retrieve();

            Console.WriteLine("After Update SQLSelect, Rowcount: {0}",
                datastore.RowCount);

            /*This code produces the following output:
            
            Before Update SQLSelect, Rowcount: 4
            After Update SQLSelect, Rowcount: 3
            */
        }
    }
}

Example Refer To

Model Class: D_Department
DataWindow File: d_department

Applies to

.NET Standard

2.x