Skip to content
Navigation Menu
{{ message }}
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathQueryGenerator.cs
More file actions
238 lines (203 loc) · 9.37 KB
/
Copy pathQueryGenerator.cs
File metadata and controls
238 lines (203 loc) · 9.37 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
using DbSyncKit.DB.Helper;
using DbSyncKit.Templates.Interface;
using DbSyncKit.Templates.SQLite;
using Fluid;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
namespace DbSyncKit.SQLite
{
/// <summary>
/// Helps generate SQL queries for SQLite database operations.
/// </summary>
public class QueryGenerator : QueryHelper, IQueryGenerator
{
#region Properties
private QueryTemplates _template;
#region Default Properties
private readonly string DEFAULT_SCHEMA_NAME = "main";
#endregion
#endregion
#region Constructor
/// <summary>
/// Initializes a new instance of the <see cref="QueryGenerator"/> class.
/// </summary>
public QueryGenerator()
{
_template = new QueryTemplates();
}
#endregion
#region Public Methods
/// <summary>
/// Generates a SELECT query based on the provided table name, list of columns, and schema name.
/// </summary>
/// <typeparam name="T">Type of the entity.</typeparam>
/// <param name="tableName">Name of the table.</param>
/// <param name="listOfColumns">List of columns.</param>
/// <param name="schemaName">Optional schema name, default is 'main'.</param>
/// <returns>Select Query in string.</returns>
/// <exception cref="ArgumentException">Thrown when table name or columns are null or empty.</exception>
public string GenerateSelectQuery<T>(string tableName, List<string> listOfColumns, string schemaName)
{
if (string.IsNullOrEmpty(tableName) || listOfColumns == null || listOfColumns.Count == 0)
{
throw new ArgumentException("Table name and columns cannot be null or empty.");
}
if (string.IsNullOrEmpty(tableName))
{
tableName = GetTableName<T>();
}
if (string.IsNullOrEmpty(schemaName))
{
schemaName = GetTableSchema<T>() ?? DEFAULT_SCHEMA_NAME;
}
return _template.SelectTemplate.Render(new TemplateContext(new
{
TableName = tableName,
Schema = schemaName,
Columns = listOfColumns
}));
}
/// <summary>
/// Generates an UPDATE query based on the provided entity, key columns, excluded columns, and edited properties.
/// </summary>
/// <typeparam name="T">Type of the entity.</typeparam>
/// <param name="DataContract">The entity data contract.</param>
/// <param name="keyColumns">List of key columns.</param>
/// <param name="excludedColumns">List of excluded columns.</param>
/// <param name="editedProperties">Dictionary of edited properties.</param>
/// <returns>Update Query in string.</returns>
public string GenerateUpdateQuery<T>(T DataContract, List<string> keyColumns, List<string> excludedColumns, (string propName, object propValue)[] editedProperties)
{
string tableName = GetTableName<T>();
string schemaName = GetTableSchema<T>() ?? DEFAULT_SCHEMA_NAME;
List<string> setClause = editedProperties.Select(kv => $"{EscapeColumn(kv.propName)} = '{EscapeValue(kv.propValue)}'").ToList();
List<string> condition = GetCondition(DataContract, keyColumns);
return _template.UpdateTemplate.Render(new TemplateContext(new
{
TableName = tableName,
Schema = schemaName,
Set = setClause,
Where = condition
}));
}
/// <summary>
/// Generates a DELETE query based on the provided entity and key columns.
/// </summary>
/// <typeparam name="T">Type of the entity.</typeparam>
/// <param name="entity">The entity to be deleted.</param>
/// <param name="keyColumns">List of key columns.</param>
/// <returns>Delete Query in string.</returns>
public string GenerateDeleteQuery<T>(T entity, List<string> keyColumns)
{
string tableName = GetTableName<T>();
string schemaName = GetTableSchema<T>() ?? DEFAULT_SCHEMA_NAME;
List<string> condition = GetCondition(entity, keyColumns);
return _template.DeleteTemplate.Render(new TemplateContext(new
{
TableName = tableName,
Schema = schemaName,
Where = condition
}));
}
/// <summary>
/// Generates an INSERT query based on the provided entity, key columns, and excluded columns.
/// </summary>
/// <typeparam name="T">Type of the entity.</typeparam>
/// <param name="entity">The entity to be inserted.</param>
/// <param name="keyColumns">List of key columns.</param>
/// <param name="excludedColumns">List of excluded columns.</param>
/// <returns>Insert Query in string.</returns>
public string GenerateInsertQuery<T>(T entity, List<string> keyColumns, List<string> excludedColumns)
{
string tableName = GetTableName<T>();
string schemaName = GetTableSchema<T>() ?? DEFAULT_SCHEMA_NAME;
bool insertWithID = GetInsertWithID<T>();
bool identityInsert = GetIncludeIdentityInsert<T>();
List<string> condition = GetCondition(entity, keyColumns);
List<string> identityColumns = GetIdentityColumns<T>();
Type entityType = typeof(T);
PropertyInfo[] properties = entityType.GetProperties().Where(prop => !excludedColumns.Contains(prop.Name) || (insertWithID && identityColumns.Contains(prop.Name))).ToArray();
List<string> columns = properties.Select(p => EscapeColumn(p.Name)).ToList();
List<string> values = properties.Select(p => $"'{EscapeValue(p.GetValue(entity))}'").ToList();
return _template.InsertTemplate.Render(new TemplateContext(new
{
TableName = tableName,
Schema = schemaName,
IsIdentityInsert = identityInsert,
Columns = columns,
Values = values,
Where = condition
}));
}
/// <summary>
/// Generates a comment string in either single-line or multi-line format.
/// </summary>
/// <param name="comment">The content of the comment. If the comment contains line breaks, it will be treated as a multi-line comment; otherwise, it will be treated as a single-line comment.</param>
/// <returns>The generated comment string.</returns>
public string GenerateComment(string comment)
{
if (string.IsNullOrWhiteSpace(comment))
return string.Empty;
bool isMultiLine = comment.Contains(Environment.NewLine);
return _template.CommentTemplate.Render(new TemplateContext(new
{
isMultiLine = isMultiLine,
Comment = comment
}));
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting resources.
/// </summary>
public void Dispose()
{
_template.Dispose();
}
#endregion
#region Helper Methods
/// <summary>
/// Generates a SQL WHERE clause based on the specified entity and key columns.
/// </summary>
/// <param name="entity">The entity for which the condition is generated.</param>
/// <param name="keyColumns">The list of key columns used to create the condition.</param>
/// <returns>A string representing the SQL WHERE clause based on the key columns of the entity.</returns>
public List<string> GetCondition<T>(T entity, List<string> keyColumns)
{
Type entityType = typeof(T);
PropertyInfo[] keyProperties = GetKeyProperties<T>();
return keyProperties.Select(p => $"{EscapeColumn(p.Name)} = '{EscapeValue(p.GetValue(entity))}'").ToList();
}
/// <summary>
/// Escapes special characters in the input string to make it SQL-safe.
/// </summary>
/// <param name="input">The input object or string to be escaped.</param>
/// <returns>The escaped object or string.</returns>
public object? EscapeValue(object? input)
{
if (input != null && input is string && (input as string)!.Contains("'"))
return (input as string)!.Replace("'", "''");
return input;
}
/// <summary>
/// Escapes the input column name to be used safely in SQL queries.
/// </summary>
/// <param name="input">The input column name to be escaped.</param>
/// <returns>The escaped column name enclosed in double quotes.</returns>
public string EscapeColumn(string? input)
{
if (input is string && input.Contains(" "))
return $"\"{input}\"";
return input?.ToString() ?? string.Empty;
}
/// <summary>
/// Generates a batch separator for SQL queries (not used in this implementation).
/// </summary>
/// <returns>An empty string.</returns>
public string GenerateBatchSeparator()
{
return string.Empty;
}
#endregion
}
}
You can’t perform that action at this time.