/// <summary> /// Skip - Take your list in database /// </summary> public static COASqlSelectData <T> Skip <T>(this COASqlSelectData <T> data, int skip, int take) { string SelectedColumns = ""; if (data.SelectedColumns.Count == 0 || data.SelectedColumns == null) { var properties = Types.GenerateListOfProperties(typeof(T).GetProperties()); properties.ForEach(props => { SelectedColumns += props + ","; data.SelectedColumns.Add(props); }); } else { data.SelectedColumns.ForEach(colums => { SelectedColumns += colums + ","; }); } SelectedColumns = SelectedColumns.Remove(SelectedColumns.Length - 1, 1); var Ordrquery = ""; if (string.IsNullOrEmpty(data.OrderQuery)) { Ordrquery = $"order by {data.PrimaryKeyName} asc"; } else { Ordrquery = data.OrderQuery; } var str = ""; if (data.DataBaseType == COADataBaseTypes.Sql) { str = $"with dummyTable as (select ROW_NUMBER() over({Ordrquery}) as RowNumber,* from {data.TableName}{data.WhereQuery}) select top({take}) {SelectedColumns} from dummyTable"; if (take > 0) { str += $" WHERE RowNumber > ({skip})"; } } else { str = $"select {SelectedColumns} from(select a.*, rownum rnum from(select * from {data.TableName}{data.WhereQuery} {Ordrquery}) a where rownum <= {take + skip})where rnum > {skip}"; } data.SqlQuery = str; data.Lenght = data.SqlQuery.Length; data.OrderQuery = Ordrquery; return(data); }
public COASqlData <T> GenerateInsertQuery() { var insertQuery = new StringBuilder($"INSERT INTO {TableName} "); insertQuery.Append("("); var properties = Types.GenerateListOfProperties(GetProperties); properties.ForEach(prop => { if (prop != PrimaryKeyName) { insertQuery.Append($"[{prop}],"); } }); insertQuery .Remove(insertQuery.Length - 1, 1) .Append(") VALUES ("); properties.ForEach(prop => { if (prop != PrimaryKeyName) { if (!Oracle) { insertQuery.Append($"@{prop},"); } else { insertQuery.Append($":{prop},"); } } }); insertQuery .Remove(insertQuery.Length - 1, 1) .Append(")"); return(new COASqlData <T>() { SqlQuery = insertQuery.ToString(), Lenght = insertQuery.ToString().Length, TableName = TableName, Oracle = Oracle, PrimaryKeyName = PrimaryKeyName }); }
public COASqlSelectData <T> GenerateSelectQuery() { return(selectQuery("*", Types.GenerateListOfProperties(GetProperties))); }