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; }