public static SqlObjQueryParameters WithParser(SqlProc proc, Action <SqlTableMapper, DbDataReader> propertiesParser = null) { return(new SqlObjQueryParameters() { proc = proc, mapper = new SqlObjectReader(propertiesParser) }); }
public static DataTable Execute(this Context context, SqlProc procedure, bool parseError) { if (parseError) { try { context.LastError = null; if (procedure.Connection == null) { procedure.Connection = context.SqlConnection; if (procedure.Connection == null) { return(null); } } Trace.WriteLine("SqlProc: " + procedure.CmdText); return(procedure.Result()); } catch (Exception ex) { Trace.Write("Error in " + procedure.CmdText + " : " + ex.Message); context.LastError = ex; } } return(null); }
public static IEnumerable <T> MultiExec <T>(this SqlProc proc, Action <SqlDataReader, SqlField[]> onReadFields = null, Action <Exception> onError = null) where T : class { var cmd = proc.CreateCommand(); var mapper = new DbObject(); SqlDataReader reader = SqlMultiDyn.ExecMultiReader(proc, onError: onError); if (reader == null) { yield break; } IEnumerable <object[]> worker = ExecMulti(proc, reader, mapper, onReadFields, progress: null); var numerator = worker.GetEnumerator(); Guard.Check(numerator.Current == null); while (numerator.MoveNext()) { object[] record = numerator.Current; T obj = (T)Activator.CreateInstance <T>(); yield return(obj); } }
private bool ExecProc(SqlProc proc, int iFrom = 0, int iTake = 0) { var fld = this.Fields; // && Rows.Count == 0 Ai.Assert.IsTrue(Rows != null, "SqlTable rows error"); Action <SqlTableMapper, DbDataReader> parser = (map, dbReader) => { int len = dbReader.FieldCount; Array.Resize <SqlField>(ref fld, len); for (int i = 0; i < len; i++) { fld[i] = new SqlField() { Ordinal = i, Name = dbReader.GetName(i), Type = dbReader.GetFieldType(i), Caption = dbReader.GetName(i), Hide = false }; } this.Fields = fld; }; bool init = this.queryParam.Prepare(proc, parser); Ai.Assert.IsTrue(fld.Length > 0); return(init); // return enumerable.Any<object[]>(); }
public static Task <IList <T> > ExecAsync <T>(this Context context, SqlProc procedure, IList <T> list, TaskScheduler scheduler, Action <double> progress) where T : class { Func <IList <T> > func = () => { var conn = new SqlConnection(); conn.ConnectionString = context.ConnectionString(); if (context.OnBeforeOpen != null) { context.OnBeforeOpen(context, new SqlConnEventArgs(conn)); } conn.Open(); var procClone = new SqlProc() { CmdText = procedure.CmdText, Connection = conn, Param = procedure.Param }; Trace.WriteLine("SqlProc: " + procClone.CmdText); var res = procClone.Exec <T>(list, progress); // .Result; return(res); }; var task = Task.Factory.StartNew <IList <T> >(func, CancellationToken.None, TaskCreationOptions.LongRunning, scheduler); return(task); }
public static MultiResult <T> MultiExec <T>(this SqlProc proc) where T : class { MultiResult <T> result = new MultiResult <T>(); result.Prepare(proc); return(result); }
public static SqlProc ProcNamed(object namedParam, Context db = null) { var properties = new NameProperties(namedParam); if (properties.List.Count == 0) { return(null); } string cmdText = properties.GetValue(namedParam, properties.FirstName()) as string; Ai.Guard.Check(cmdText.Length > 4); var proc = SqlProcExt.CmdText(cmdText); var listParam = new List <SqlParameter>(); foreach (string itemName in properties.Names(1)) { var val = Ai.Reflection.Utils.GetPropertyValue(namedParam, itemName); if (val != null) { listParam.Add(SqlProc.AddWithValue("@" + itemName, val)); } } proc.Param = listParam; proc.Context = db; return(proc); }
public XDocument ExecXml(SqlProc procedure) { if (procedure.Connection == null) { procedure.Connection = SqlConnection; } AssureOpen(); var cmd = procedure.CreateCommand(); Trace.WriteLine("ExecXml " + cmd.CommandText); XDocument doc = null; using (var reader = cmd.ExecuteXmlReader()) { XDocument docNext = null; // list nodes : reader.NameTable; while (reader.Read()) { if (doc == null) { doc = XDocument.Load(reader.ReadSubtree()); } else { docNext = XDocument.Load(reader.ReadSubtree()); doc.Document.Root.Add(docNext.Root); docNext = null; } } } return(doc); }
public bool Prepare(SqlProc proc, Action <SqlTableMapper, DbDataReader> parser = null) { if (state != StateExec.Init) { this.Dispose(); } Context db = proc.Context; conn = new SqlConnection(db.ConnectionString()); conn.Open(); if (conn.State != ConnectionState.Open) { return(false); } conn.ChangeDatabase(db.DbName); cmd = proc.CreateCommand(); cmd.Connection = conn; dataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo); if (parser != null) { base.propertiesParser = parser; } (this as IDataMapHelper <object[]>).GetProperties(dataReader); // CommandBehavior. // SingleResult = 1, The query returns a single result set. // SchemaOnly = 2, // The query returns column information only. When using System.Data.CommandBehavior.SchemaOnly, // the .NET Framework Data Provider for SQL Server precedes the statement being // executed with SET FMTONLY ON. // KeyInfo = 4, // The query returns column and primary key information. // SingleRow = 8, // The query is expected to return a single row of the first result set. Execution // of the query may affect the database state. Some .NET Framework data providers // may, but are not required to, use this information to optimize the performance // of the command. When you specify System.Data.CommandBehavior.SingleRow with // the System.Data.OleDb.OleDbCommand.ExecuteReader() method of the System.Data.OleDb.OleDbCommand // object, the .NET Framework Data Provider for OLE DB performs binding using // the OLE DB IRow interface if it is available. Otherwise, it uses the IRowset // interface. If your SQL statement is expected to return only a single row, // specifying System.Data.CommandBehavior.SingleRow can also improve application // performance. It is possible to specify SingleRow when executing queries that // are expected to return multiple result sets. In that case, where both a multi-result // set SQL query and single row are specified, the result returned will contain // only the first row of the first result set. The other result sets of the // query will not be returned. dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult); state = StateExec.Prepared; return(true); }
public static IEnumerable <object[]> ResultObj(this SqlProc proc , Action <SqlField[]> onReadFields = null) { var cmd = proc.CreateCommand(); var helper = new DbDataMapHelper <object[]>(); return(DbGetHelper.ExecEnumerable(proc, helper, onReadFields: onReadFields)); }
public object[] FirstRecord(SqlProc proc, Action <SqlTableMapper, DbDataReader> parser = null, int?commandTimeout = null) { if (parser != null) { base.propertiesParser = parser; } LastRow = null; var conn = OpenConnection(proc.Context); if (conn == null) { return(LastRow); } using (var cmd = proc.CreateCommand()) { cmd.Connection = conn; if (commandTimeout.HasValue) { cmd.CommandTimeout = commandTimeout.Value; } cmd.Prepare(); var dataReader = cmd.ExecuteReader(CommandBehavior.SingleRow); // CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo); (this as IDataMapHelper <object[]>).GetProperties(dataReader); // CommandBehavior. // SingleResult = 1, The query returns a single result set. // SchemaOnly = 2, // The query returns column information only. When using System.Data.CommandBehavior.SchemaOnly, // the .NET Framework Data Provider for SQL Server precedes the statement being // executed with SET FMTONLY ON. // KeyInfo = 4, // The query returns column and primary key information. // SingleRow = 8, // The query is expected to return a single row of the first result set. Execution // may, but are not required to, use this information to optimize the performance // of the command. When you specify System.Data.CommandBehavior.SingleRow with // the System.Data.OleDb.OleDbCommand.ExecuteReader() method of the System.Data.OleDb.OleDbCommand // object, the .NET Framework Data Provider for OLE DB performs binding using // the OLE DB IRow interface if it is available. Otherwise, it uses the IRowset // interface. If your SQL statement is expected to return only a single row, also improve performance. var helper = this; object[] objVal = helper.DbRecordArray(); dataReader.GetValues(objVal); LastRow = helper.SetValues(objVal); cmd.Cancel(); // The name/value pair "Asynchronous Processing=true" was not included within dataReader.Dispose(); } return(LastRow); }
public static MultiResult <object[]> MultiObj(this Context db, string proc, Action <SqlCommand> setup = null) { var procMap = new SqlProc { Context = db, Connection = db.SqlConnection, CmdText = proc, Param = null }; var result = new MultiResult <object[]>(); return(result.Prepare(procMap, setup)); }
public static SqlProc WithParam(this SqlProc proc, object namedParam) { if (namedParam != null) { proc.Param = NameProperties.Parse(namedParam); } return(proc); }
public static DbObject Exec(SqlProc proc, Action <double> progress = null) { var mapper = new DbObject(); mapper.Worker = DbGetHelper.ExecEnumerable(proc, mapper, progress).GetEnumerator(); if (mapper.Worker == null) { return(null); } return(mapper); }
public static MultiResult <T> MultiExec <T>(this Context db, SqlCommand cmd) where T : class { var proc = new SqlProc { Connection = new SqlConnection(db.ConnectionString()), CmdText = cmd.CommandText, Param = SqlProc.CloneParam(cmd.Parameters) }; return(MultiExec <T>(proc)); }
// in T public static bool ExecFill <T>(this SqlProc proc, IList <T> list, IDataMapHelper <T> mapper, Action <double> progress = null) where T : class // , TMap : IDataMapHelper { Ai.Assert.IsTrue(proc.Connection != null, "proc.Connection null error in ExecFill"); Ai.Assert.IsTrue(list != null, "list null error in ExecFill"); if (proc.Connection.State != ConnectionState.Open) { proc.Connection.Open(); } using (var command = proc.CreateCommand()) { if (progress != null) { progress(0.0); } using (SqlDataReader dataReader = command.ExecuteReader()) { var results = list as IList <T>; if (!dataReader.Read()) { if (progress != null) { progress(1.0); } return(false); } // Task<T> GetFieldValueAsync<T>(int i, CancellationToken cancellationToken); var helper = mapper; // new DbDataMapHelper<T>(); helper.GetProperties(dataReader); do { object[] objVal = helper.DbRecordArray(); // int ret = dataReader.GetValues(objVal); T val = helper.SetValues(objVal); results.Add(val); }while (dataReader.Read()); if (progress != null) { progress(1.0); } return(true); } } }
public static async Task <DataTable> ExecuteAsync(this Context context , SqlProc procedure, bool parseError = true) { if (procedure.Connection == null) { procedure.Connection = context.SqlConnection; if (procedure.Connection == null) { return(null); } } Trace.WriteLine("SqlProc: " + procedure.CmdText); if (procedure.Connection.State != ConnectionState.Open) { Task <object> task = OpenAsync(context, procedure.Connection); // , CancellationToken.None); await task; if (task.Exception != null) { var tcs = new TaskCompletionSource <DataTable>(); tcs.SetException(task.Exception); context.LastError = task.Exception; if (!parseError) { throw task.Exception; } return(await tcs.Task); } } if (!parseError) { return(await Task.Factory.StartNew(() => { return procedure.Result(); })); } try { return(await Task.Factory.StartNew(() => { return procedure.Result(); })); } catch (Exception ex) { context.LastError = ex; } return(null); }
public static DbObject Exec(SqlProc proc, Action <double> progress = null, bool withFirst = true) { var mapper = new DbObject(); mapper.Worker = DbGetHelper.ExecEnumerable(proc, mapper, progress).GetEnumerator(); if (mapper.Worker == null) { return(null); } if (withFirst && mapper.Worker.Current == null) { mapper.Worker.MoveNext(); } return(mapper); }
public static DataTable Execute(this Context context, SqlProc procedure) { if (procedure.Connection == null) { procedure.Connection = context.SqlConnection; if (procedure.Connection == null || string.IsNullOrEmpty(procedure.Connection.ConnectionString)) { return(null); } } Trace.WriteLine("SqlProc: " + procedure.CmdText); return(procedure.Result()); }
public static SqlProc CmdText(string cmdText, Context context = null, Action <SqlProc> setup = null) { var proc = new SqlProc() { CmdText = cmdText, Context = context, Connection = context == null ? null : context.SqlConnection, Param = null }; if (setup != null) { setup(proc); } return(proc); }
public static SqlProc WithParam(this SqlProc proc, string name, object value) { if (proc.Param == null) { proc.Param = new List <SqlParameter>() { SqlProc.AddWithValue(name, value) } } ; else { proc.Param.Add(SqlProc.AddWithValue(name, value)); } return(proc); }
public IList <T> Exec <T>(SqlProc procedure) where T : class { if (procedure.Connection == null) { procedure.Connection = SqlConnection; if (procedure.Connection == null) { throw new ArgumentNullException("procedure.Connection"); } } Trace.WriteLine("SqlProc: " + procedure.CmdText); var list = new Collection <T>(); return(procedure.Exec <T>(list, null)); // .Result; }
public bool ExecNamed(DbContext context, object paramNamed, int iFrom = 0, int iTake = 0) { var prop = new NameProperties(paramNamed); string execName = prop.GetValue(paramNamed, prop.FirstName()) as string; var proc = SqlProcExt.CmdText(execName, context); var paramList = new List <SqlParameter>(); foreach (string item in prop.Names(1)) { paramList.Add(SqlProc.AddWithValue("@" + item, prop.GetValue(paramNamed, item))); } if (paramList.Count > 0) { proc.Param = paramList; } return(ExecProc(proc, iFrom, iTake)); }
public MultiResult <T> Prepare(SqlProc proc , Action <SqlCommand> setup = null , Action <SqlField[]> onReadFields = null , bool noMoveFirst = false ) { this.proc = proc; var mapper = new DbObject(); numerator = DbEnumeratorData.GetEnumerator(() => { this.reader = SqlMultiDyn.ExecMultiReader(proc, setup, progress: null); return(reader); }); if (numerator == null) { return(null); } if (reader.IsClosed || reader.Depth != 0) { return(null); } if (noMoveFirst) { return(this); } numerator.MoveNext(); var rec = numerator.Current as object[]; // DbDataRecord; mapHelper = mapper.GetProperties(reader); Fields = reader.GetFields(); // SqlFieldArray FirstRecord = rec; // mapHelper.SetValues(mapHelper.DbRecordArray()); return(this); }
public static List <SqlParameter> Parse(object namedParam, int skipFrom = 0) { var properties = new NameProperties(namedParam); if (properties.List.Count == 0) { return(null); } var listParam = new List <SqlParameter>(); foreach (string itemName in properties.Names(skipFrom)) { var val = Utils.GetPropertyValue(namedParam, itemName); if (val != null) { listParam.Add(SqlProc.AddWithValue("@" + itemName, val)); } } return(listParam); }
public static DataTable Result(this SqlProc proc) { var cmd = proc.CreateCommand(); var table = new DataTable(); if (cmd.Connection.State != ConnectionState.Open) { Assert.IsTrue(cmd.Connection.ConnectionString.Length > 0); cmd.Connection.Open(); if (cmd.Connection.State != ConnectionState.Open) { return(null); } } var reader = cmd.ExecuteReader(); // Load(IDataReader reader, LoadOption loadOption); table.Load(reader, LoadOption.OverwriteChanges); return(table); }
// Sql data set to multiple XML public static MultiResult <XElement> MultiXElem(this SqlProc proc) { var result = new MultiResult <XElement>(); return(result.Prepare(proc)); }
public static MultiResult <object[]> MultiObj(this SqlProc proc) { var result = new MultiResult <object[]>(); return(result.Prepare(proc)); }
public static IEnumerable <object[]> ExecEnumerable(this SqlProc proc, IDataMapHelper <object[]> mapper, Action <double> progress = null) { Ai.Assert.IsTrue(proc.Connection != null, "proc.Connection null error in ExecFill"); // Ai.Assert.IsTrue(list != null, "list null error in ExecFill"); using (SqlConnection connection = new SqlConnection(proc.Context.ConnectionString())) { proc.Connection = connection; if (proc.Connection.State != ConnectionState.Open) { proc.Connection.Open(); if (proc.Connection.State != ConnectionState.Open) { yield break; } } if (proc.Connection.Database != proc.Context.DbName) { proc.Connection.ChangeDatabase(proc.Context.DbName); } using (var command = proc.CreateCommand()) { if (progress != null) { progress(0.0); } using (SqlDataReader dataReader = command.ExecuteReader()) { if (!dataReader.Read()) { if (progress != null) { progress(1.0); } yield break; } // Task<T> GetFieldValueAsync<T>(int i, CancellationToken cancellationToken); var helper = mapper; // new DbDataMapHelper<T>(); helper.GetProperties(dataReader); // int records = dataReader.RecordsAffected; do { object[] objVal = helper.DbRecordArray(); dataReader.GetValues(objVal); object[] val = helper.SetValues(objVal); yield return(val); }while (dataReader.Read()); if (progress != null) { progress(1.0); } } } } }
public bool Prepare(SqlProc proc, Action <SqlTableMapper, DbDataReader> parser = null) { this.proc = proc; return(IsEmpty() || mapper.Prepare(proc, parser)); }