ZDAL 6.1.0

SQL SELECT Query Functions

If the query is "Select * From Chapter where ChaptrerId = 5"

Use any one query of below to SELECT the data from Chapter table

Chapter objChapter = new Chapter() { ChapterId = 5 };
List<Chapter> lst = await ChapterBLL.SelectList(objChapter);

OR

List<Chapter> lst = await ChapterBLL.SelectList(new Chapter() { ChapterId = 5 });

Same you can do with AND Conditions. If the query is "Select * From Chapter where ChaptrerId = 5 AND ChapterNumber = 3"

Use below to SELECT the data from Chapter table

List<Chapter> lst = await ChapterBLL.SelectList(new Chapter() { ChapterId = 5, ChapterNumber = 3 });

SQL INSERT Query Functions

Use below query to INSERT the data to Chapter table

var objChapter = new Chapter()
{
	SubjectId = 1,
	ChapterName = “Set Theory”,
	ChapterNumber = 2,
};
await ChapterBLL.Insert(objChapter);
OR
int NewId = await ChapterBLL.InsertId(objChapter);

"NewId" Will have value of primary Key of new inserted Row.

SQL UPDATE Query Functions

Use below query to UPDATE the data in Chapter table

var objChapter = new Chapter()
{
	ChapterId = 2
	SubjectId = 1,
	ChapterName = “Set Theory”,
	ChapterNumber = 2,
};
int Count = await ChapterBLL.Update(objChapter);

"Count" Will Number of rows Updated by Query.

SQL DELETE Query Functions

Use below query to DELETE the data from Chapter table

int Count = await ChapterBLL.Delete(new Chapter() { ChapterId = 5 });

"Count" Will Number of rows Deleted by Query.

Note: We prefer to delete by primary key only.

Step to Make Custom SQL Query Functions

Step 1 - Add enum in BLL.eQuery File

public enum eQuery
{	
	.
	.
	.
	.
	.
	Get_All_Branch = 5,
}

Step 2 - Add Same Name Method in BLL.Queries Class

public static string Get_All_Branch()
{
	return @"
	SELECT 
		BranchId,
		BranchName,
		Language,
		eStatus,
		SerialNo,
		LastUpdateTime
	
	FROM Branch

	WHERE (@SchoolUniqueId	IS NULL OR SchoolUniqueId	= @SchoolUniqueId)
		AND	(@MasterSearch	    IS NULL OR BranchName       LIKE '%' + @MasterSearch +'%') 
		AND	(@eStatus			IS NULL OR eStatus			= @eStatus)
		AND	(@eStatusNot		IS NULL OR eStatus			<> @eStatusNot)
	
	ORDER BY eStatus, SerialNo";
}

Step 3 - Add Query Parameter if any in BOL.Query

public class Query : IModel
{
	.
	.
	.	
	.	
	.
	[ModelPropertyAttribute("MasterSearch", "select")]
	public string MasterSearch { get; set; }
}

Step 4 - To Call That Query

var lstData = await QueryBLL.ExeQuery<AllBranchDto>(new Query()
{
	SchoolUniqueId = jwtPayload.SchoolUniqueId,
	MasterSearch = request.MasterSearch,
	eStatus = (int)request.eStatus,
}, eQuery.Get_All_Branch);