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