public SqlQuery ToQuery(string tableAlias)
{
string tableName = this.MetaData.TableName;
SqlQuery query = new SqlQuery();
StringBuilder text = query.Text;
text.Append("SELECT ");
string tableNameOp = tableAlias + ".";
foreach (PropertyMetaData property in this.MetaData.Properties)
{
string columnName = property.Schema.ColumnName;
text.Append(tableNameOp);
text.Append(columnName);
text.Append(" AS ");
text.Append(columnName);
text.Append(", ");
}
text.Remove(text.Length - 2, 1);
text.Append("FROM ");
text.Append(tableName);
text.Append(' ');
text.Append(tableAlias);
return query;
}
internal ExecuteSqlCompleteEventArgs(DbAccess dataAccess, SqlQuery query, DateTime executionStart, DateTime executionFinish, Exception executionException)
: base(dataAccess, query)
{
this.ExecutionStart = executionStart;
this.ExecutionFinish = executionFinish;
this.ExecutionException = executionException;
}
protected virtual IDataReader CreateDataReader()
{
SqlQuery query = new SqlQuery();
StringBuilder text = query.Text;
text.Append("SELECT ");
text.Append(this.DataValueField);
text.Append(',');
text.Append(this.DataTextField);
text.Append(" FROM ");
text.Append(this.DataTableField);
if (!String.IsNullOrEmpty(this.FilterExpression))
{
text.AppendLine();
text.Append("WHERE ");
text.Append(this.FilterExpression);
}
if (!String.IsNullOrEmpty(this.SortExpression))
{
text.AppendLine();
text.Append("ORDER BY ");
text.Append(this.SortExpression);
}
return this.Factory.DataAccess.CreateDataReader(query, CommandBehavior.Default);
}
public static IEnumerable Select(this ICommandAdapter adapter, Type entityType, SqlQuery extendedQuery)
{
if (null != adapter && null != entityType)//extendedQuery null olabilir.
{
return SelectMethod(entityType).Invoke(adapter, new object[] { extendedQuery }) as IEnumerable;
}
return null;
}
public static object SelectSingle(this ICommandAdapter adapter, Type entityType, SqlQuery extendedQuery)//extendedQuery null olabilir
{
if (null != adapter && null != entityType)
{
return SelectSingleMethod(entityType).Invoke(adapter, new object[] { extendedQuery });
}
return null;
}
protected virtual void LoadItemsInternal()
{
SqlQuery query = new SqlQuery();
StringBuilder text = query.Text;
text.Append("SELECT ");
text.Append(this.ParentValueField);
text.Append(',');
string[] arr = this.ParentTextField.Split(',');
foreach(string s in arr)
{
text.Append(s);
text.Append(',');
}
text.Remove(text.Length - 1, 1);
text.Append(" FROM ");
text.Append(this.ParentTable);
if (!String.IsNullOrEmpty(this.FilterExpression))
{
text.AppendLine();
text.Append("WHERE ");
text.Append(this.FilterExpression);
}
if (!String.IsNullOrEmpty(this.SortExpression))
{
text.AppendLine();
text.Append("ORDER BY ");
text.Append(this.SortExpression);
}
base.Items.Clear();
using (IDataReader dr = this.Factory.DataAccess.CreateDataReader(query, CommandBehavior.Default))
{
StringBuilder sb = new StringBuilder();
while (dr.Read())
{
int j = 1;
for (; j < dr.FieldCount - 1; ++j)
{
if (dr[j].GetType() != CachedTypes.DBNull)
{
sb.Append(dr[j]);
sb.Append(" - ");
}
}
sb.Append(dr[j]);
ListItem item = new ListItem(sb.ToString(), dr[0].ToString());
base.Items.Add(item);
sb.Length = 0;
}
}
}
protected virtual void OnPreExecuteSql(SqlQuery query)
{
PreExecuteSqlEventHandler fPtr = (PreExecuteSqlEventHandler)this.events[DbAccess.PreExecuteSqlEvent];
if (fPtr != null)
{
PreExecuteSqlEventArgs e = new PreExecuteSqlEventArgs(this, query);
fPtr(e);
}
}
protected virtual void OnExecuteSqlComplete(SqlQuery query, DateTime executionStart, Exception executingException)
{
ExecuteSqlCompleteEventHandler fPtr = (ExecuteSqlCompleteEventHandler)this.events[DbAccess.ExecuteSqlCompleteEvent];
if (fPtr != null)
{
ExecuteSqlCompleteEventArgs e = new ExecuteSqlCompleteEventArgs(this, query, executionStart, DateTime.Now, executingException);
fPtr(e);
}
}
//Builder
public SqlQuery Combine(SqlQuery queryInfo)
{
if (null != queryInfo)
{
this.text.Append(queryInfo.text);
this.parameters.AddRange(queryInfo.parameters);
}
return this;
}
private DbCommand CreateCommand(SqlQuery query, out OutParameters outParameters)
{
if (null == query)
throw new ArgumentNullException(nameof(query));
string commandText = query.Text.ToString();
if (string.IsNullOrEmpty(commandText))
throw new ArgumentException("query, SqlQuery.Text is null");
outParameters = null;
this.OnPreExecuteSql(query);
DbCommand cmd = null;
try
{
cmd = this.connection.CreateCommand();
foreach (SqlQueryParameter parameter in query.Parameters)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = parameter.ParameterName;
dbParameter.IsNullable = parameter.IsNullable;
if (parameter.dbType.HasValue)
dbParameter.DbType = parameter.dbType.Value;
object parameterValue = parameter.Value;
dbParameter.Value = (null == parameterValue) ? DBNull.Value : parameterValue;
ParameterDirection direction = parameter.Direction;
dbParameter.Direction = direction;
cmd.Parameters.Add(dbParameter);
if (direction == ParameterDirection.InputOutput || direction == ParameterDirection.Output)
{
if (null == outParameters)
outParameters = new OutParameters();
outParameters.Add(parameter, dbParameter);
}
}
cmd.CommandText = commandText;
cmd.CommandType = query.CmdType;
}
catch (Exception)
{
if (null != cmd) cmd.Dispose();
throw;
}
this.OnCommandCreated(cmd);
return cmd;
}
public virtual void SetColumnValue(IEntityMetaData metaData, int index, SqlQuery query, PropertyMetaData pm, object entity)//Parametewnin eklenip eklenmeyeceği bilinmdeğinden prefix ve entity verilmek zorunda.
{
SchemaInfo schema = pm.Schema;
PropertyInfo pi = pm.Property;
object parValue = pm.Property.GetValue(entity, null);
StringBuilder text = query.Text;
if (schema.DefaultValue.Length != 0)
{
object defaultValue = ReflectionExtensions.GetDefault(pi.PropertyType);
if (Object.Equals(defaultValue, parValue))//Eğer Property Değeri Default Haldeyse yazdır Bunu
{
text.Append(schema.DefaultValue);
return;
}
}
switch (schema.SqlValueType)
{
case SqlValueType.Parameterized:
text.Append(this.Prefix);
string parameterName = metaData.GetParameterName(pm, index);
text.Append(parameterName);
SqlQueryParameter par = SqlQueryParameter.Create(parameterName, pm, parValue);
query.Parameters.Add(par);
break;
case SqlValueType.Text:
string textValue = null;
if (null != parValue)
{
Type dataType = pm.Schema.DataType;//Neden Schema.DataType çünkü pi.PropertyType nullable olabalir.
if (WithQuotes.Contains(dataType))
{
textValue = "'" + parValue + "'";
}
else
{
IFormattable f = parValue as IFormattable;
textValue = null != f ? f.ToString(null, NumericCulture) : parValue.ToString();
}
}
text.Append(textValue);
break;
default:
throw new NotSupportedException(schema.SqlValueType.ToString());
}
}
//Arama Yapısından Sık Kullanılan İç Sorgular İçin Eklendi
public static SqlQuery ToInnerQuery(this SqlQuery query, string alias)
{
if (null != query)
{
if (String.IsNullOrEmpty(alias))
alias = "T";
SqlQuery inner = new SqlQuery();
inner.Text.Append("SELECT * FROM (");
inner.Combine(query);
inner.Text.Append(") ");
inner.Text.Append(alias);
return inner;
}
return null;
}
public int ExecuteNonQuery(SqlQuery query)
{
DbCommand cmd = null;
DbTransaction tran = null;
OutParameters outParameters;
int ret = 0;
DateTime executionStart = DateTime.Now;
try
{
cmd = this.CreateCommand(query, out outParameters);
if (this.enableTransaction)
{
tran = this.connection.BeginTransaction();
cmd.Transaction = tran;
}
ret = cmd.ExecuteNonQuery();
if (this.enableTransaction)
tran.Commit();
}
catch (Exception ex)
{
if (this.enableTransaction && tran != null)
tran.Rollback();
this.OnExecuteSqlComplete(query, executionStart, ex);
throw;
}
finally
{
if (this.enableTransaction) tran.Dispose();
if (null != cmd) cmd.Dispose();
}
if (null != outParameters)
{
outParameters.SetOutParametersValues();
}
this.OnExecuteSqlComplete(query, executionStart, null);
return ret;
}
private bool LoadDataSource()
{
if (this.IsAutoDataBindEnabled)
{
if (this.Key.Length == 0)
throw new InvalidOperationException("AutoDataBind is enabled but Key not be given");
if (null == this.ItemsSource)
{
SqlQuery query = new SqlQuery();
if (this.CustomSql.Length == 0)
{
StringBuilder text = query.Text;
text.Append("SELECT ");
foreach (GridColumn clmn in base.Columns)
{
GridBoundColumn boundColumn = clmn as GridBoundColumn;
if (null != boundColumn)
{
text.Append(boundColumn.DataField);
text.Append(" AS ");
text.Append(boundColumn.UniqueName);
text.Append(", ");
}
}
text.Remove(text.Length - 2, 1);
text.Append("FROM ");
text.Append(this.TableName);
if (this.ExtendedSql.Length != 0)
{
text.Append(' ');
text.Append(this.ExtendedSql);
}
}
else
{
query.Text.Append(this.CustomSql);
}
this.ItemsSource = this.dataAccess.QueryDataTable(query).DefaultView;
}
return true;
}
return false;
}
private void SetItemSource()
{
if (this.IsAutoDataBindEnabled)
{
SqlQuery query = new SqlQuery();
StringBuilder text = query.Text;
text.Append("SELECT ");
text.Append(this.ListValueField);
text.Append(',');
text.Append(this.ListTextField);
text.Append(" FROM ");
text.Append(this.TableName);
if (this.ExtendedSql.Length != 0)
{
text.Append(' ');
text.Append(this.ExtendedSql);
}
this.ClearDataSource();
if (this.AllowNull)
{
this._itemSource.Add(ComboItem.Empty);
}
using (IDataReader dr = this.DataAccess.CreateDataReader(query, CommandBehavior.Default))
{
while (dr.Read())
{
this._itemSource.Add(new ComboItem(dr[1].ToString(), dr[0].ToString()));
}
}
}
else
this.ClearDataSource();
using (MemoryStream ms = new MemoryStream())
{
BinaryFormatter ser = new BinaryFormatter();
ser.Serialize(ms, this._itemSource);
ms.Position = 0;
base.ViewState["ItemSource"] = Convert.ToBase64String(ms.ToArray());
}
}
public static SqlQuery ToQuery2(this string sql, dynamic parameters)
{
SqlQuery q = new SqlQuery();
if (null != parameters)
{
foreach (PropertyInfo pi in parameters.GetType().GetProperties())
{
bool flag = false;
object value = pi.GetValue(parameters);
if (null != value)
{
Type valueType = value.GetType();
if (!collTtpes.Contains(valueType))
{
IEnumerable list = value as IEnumerable;
if (null != list)
{
char prefix = sql.Contains("@") ? '@' : ':';
StringBuilder sb = new StringBuilder("(");
int index = 0;
foreach (var item in list)
{
sb.Append(prefix)
.Append(pi.Name + index)
.Append(',');
q.Parameters.Add(pi.Name + index, item);
++index;
}
sb.Remove(sb.Length - 1, 1);
sb.Append(')');
sql = sql.Replace(prefix + pi.Name, sb.ToString());
flag = true;
}
}
}
if (!flag)
q.Parameter(pi.Name, value);
}
}
q.Sql(sql);
return q;
}
public SelectSql Where(SqlQuery query)
{
if (null != query)
{
WhereItem item = new WhereItem(query);
this.wheres.Add(item);
}
return this;
}
internal PreExecuteSqlEventArgs(DbAccess dataAccess, SqlQuery query)
{
this.DataAccess = dataAccess;
this.Query = query;
}
public SqlQuery ToQuery()
{
SqlQuery query = new SqlQuery();
StringBuilder text = query.Text;
bool justWhereSql = this.columns.Count == 0 || this.tables.Count == 0;
if (!justWhereSql)
{
text.Append("SELECT ");
foreach (SelectSql.ColumnItem clmn in this.columns)
{
text.Append(clmn); text.Append(", ");
}
foreach (string sqlColumn in this.customColumnSql)
{
text.Append(sqlColumn); text.Append(", ");
}
text.Remove(text.Length - 2, 2);
text.Append(" FROM ");
text.Append(this.GetLeftTable());
text.AppendLine();
foreach (SelectSql.JoinItem item in this.joins)
{
text.Append(item);
text.AppendLine();
}
}
if (this.custom.Text.Length != 0)
{
query.Combine(this.custom);
}
if (this.wheres.Count > 0)
{
if (justWhereSql)
text.AppendLine();
text.Append("WHERE ");
foreach (SelectSql.WhereItem item in this.wheres)
{
query.Combine(item.ToQuery());
query.Text.Append(" AND ");
}
query.Text.Remove(query.Text.Length - 5, 5);
}
return query;
}
public DbDataReader CreateDataReader(SqlQuery query, CommandBehavior behavior)
{
DbCommand cmd = null;
DbDataReader dr = null;
OutParameters outParameters;
DateTime executionStart = DateTime.Now;
try
{
cmd = this.CreateCommand(query, out outParameters);
dr = cmd.ExecuteReader(behavior);
}
catch (Exception ex)
{
if (dr != null) dr.Dispose();
this.OnExecuteSqlComplete(query, executionStart, ex);
throw;
}
finally
{
if (cmd != null) cmd.Dispose();
}
if (null != outParameters)
{
outParameters.SetOutParametersValues();
}
this.OnExecuteSqlComplete(query, executionStart, null);
return dr;
}
IDataReader IDbAccess.CreateDataReader(SqlQuery query, CommandBehavior behavior)
{
return this.CreateDataReader(query, behavior);
}
public static object QuerySingle(this ICommandAdapter adapter, Type entityType, SqlQuery query)
{
if ((null != adapter && null != entityType) && null != query)
{
return QuerySingleMethod(entityType).Invoke(adapter, new object[] { query });
}
return null;
}
//i.e. "update Perseon set Name=@0, No=@1 where Id=@2".ToQuery("Memo", 12, 1);
public static SqlQuery ToQuery(this string sql, params object[] parameters)
{
SqlQuery query = new SqlQuery();
query.Sql(sql, parameters);
return query;
}
public SqlQuery ToQuery()
{
if (null != this.query)
return this.query;
SqlQuery query = new SqlQuery();
if (null != this.Table)
{
query.Text.Append(this.Table.Alias);
query.Text.Append('.');
}
query.Combine(this.Criteria.ToQuery());
return query;
}
internal WhereItem(SqlQuery query)
{
if (null == query)
throw new ArgumentNullException("query");
this.query = query;
}
public static IEnumerable Query(this ICommandAdapter adapter, Type entityType, SqlQuery query)
{
if ((null != adapter && null != entityType) && null != query)
{
return QueryMethod(entityType).Invoke(adapter, new object[] { query }) as IEnumerable;
}
return null;
}
public SelectSql Custom(SqlQuery query)
{
if (null != query)
this.custom.Combine(query);
return this;
}