| public ExecuteReader ( ) : System.Data.Common.DbDataReader | ||
| return | System.Data.Common.DbDataReader | |
// executes a command and returns the results as a DataTable object
public static DataTable ExecuteSelectCommand(DbCommand command)
{
// The DataTable to be returned
DataTable table;
// Execute the command making sure the connection gets closed in the end
try
{
// Open the data connection
command.Connection.Open();
// Execute the command and save the results in a DataTable
DbDataReader reader = command.ExecuteReader();
table = new DataTable();
table.Load(reader);
// Close the reader
reader.Close();
}
catch (Exception ex)
{
Utilities.LogError(ex);
throw;
}
finally
{
// Close the connection
command.Connection.Close();
}
return table;
}
public static DataTable ExecuteSelectCommand(DbCommand command)
{
DataTable table;
try
{
command.Connection.Open();
DbDataReader reader = command.ExecuteReader();
table = new DataTable();
table.Load(reader);
reader.Close();
}
catch (Exception ex)
{
Utilities.LogError(ex);
throw;
}
finally
{
command.Connection.Close();
}
return table;
}
// executes a command and returns the results as a DataTable object
public static DataTable ExecuteCommand(DbCommand command)
{
DataTable table;
try
{
command.Connection.Open();
DbDataReader reader = command.ExecuteReader();
table = new DataTable();
table.Load(reader);
reader.Close();
}
catch (Exception exp)
{
//TODO: LogError
throw exp;
}
finally
{
command.Connection.Close();
}
return table;
}
internal DisposingDbDataReader(DbCommand command)
{
if (command == null)
throw new ArgumentNullException("command");
this.command = command;
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
public static IDataReader ExecuteReder(DbCommand command)
{
// The DataTable to be returned
IDataReader rdr = null;
// Execute the command making sure the connection gets closed in the end
try
{
// Open the data connection
command.Connection.Open();
// Execute the command and save the results in a DataTable
rdr = command.ExecuteReader();
// Close the reader
rdr.Close();
}
catch (Exception ex)
{
Utilities.LogError(ex);
throw ex;
}
finally
{
// Close the connection
command.Connection.Close();
}
return rdr;
}
protected override DataTable RunSql(System.Data.Common.DbCommand command, object arg)
{
command.CommandText = String.Format(@"
SELECT *
FROM [{0}$]
WHERE (LEN([{1}]) > 0) AND (([{2}] > 0) OR ([{3}] > 0) OR ([{4}] > 0))",
SheetName,
Entity.Literal.SupplierCodeField,
Entity.Literal.FeeField,
Entity.Literal.CobaltCenterFeeAmountField,
Entity.Literal.EdiField);
try
{
DataTable table = new DataTable();
using (DbDataReader reader = command.ExecuteReader())
{
table.Load(reader);
}
DestroyIfSupplierCodeFieldIsNotNumeric(table);
StampPaymentDateField(table);
return(table);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
throw new Exception();
}
public static DataTable ReadTable(DbCommand cmd)
{
DataTable dt = new DataTable();
DbDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
int fieldc = reader.FieldCount;
for (int i = 0; i < fieldc; i++)
{
DataColumn dc = new DataColumn(reader.GetName(i), reader.GetFieldType(i));
dt.Columns.Add(dc);
}
while (reader.Read())
{
DataRow dr = dt.NewRow();
for (int i = 0; i < fieldc; i++)
{
dr[i] = reader[i];
}
dt.Rows.Add(dr);
}
return dt;
}
finally
{
if (reader != null) reader.Close();
}
}
public IDataReader ExecuteReader(DbCommand command)
{
// check method parameters
if (command == null)
{
throw new ArgumentNullException(nameof(command));
}
DbConnection connection = null;
try
{
connection = GetOpenConnection();
command.Connection = connection;
var behavior = Transaction.Current == null ? CommandBehavior.CloseConnection : CommandBehavior.Default;
return command.ExecuteReader(behavior);
}
catch (Exception e)
{
Debug.WriteLine(e);
ReleaseConnection(connection);
return null;
}
}
public IDataReader ExecuteReader(DbCommand command)
{
var connection = new SQLiteConnection(_connectionString);
command.Connection = connection;
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
protected override object ExecuteResultInternal(IDbExecuteContext ctx, DbCommand command, Type resultType, object resultInstance)
{
using (var reader = command.ExecuteReader(CommandBehavior.SingleRow)) {
if(reader.Read()) {
resultInstance = this.ObjectMapper.Mapping(ctx.Statement, reader.ToDictionary(), resultType, resultInstance);
}
return resultInstance;
}
}
protected override object ExecuteResultInternal(IDbExecuteContext ctx, DbCommand command, Type resultType, object resultInstance)
{
var defaultType = typeof(IDataReader);
var type = this.GetResultType(ctx, resultType, resultInstance, defaultType);
if (!defaultType.IsAssignableFrom(type)) {
throw new DataException("要求返回的类型不正确");
}
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
public bool Fun_RsSQL(string sSQL, ref System.Data.Common.DbDataReader retRS, bool bDBClose = false)
{
try
{
sSQL = sSQL.Replace("''", "' '");
if (this.DBConn == null)
{
if (this.Fun_OpenDataBase() == false)
{
return(false);
}
}
else
{
if (this.DBConn.State != System.Data.ConnectionState.Open)
{
if (this.Fun_OpenDataBase() == false)
{
return(false);
}
}
}
if (DBConn.State != System.Data.ConnectionState.Open)
{
return(false);
}
DBCommand.Connection = DBConn;
DBCommand.CommandText = sSQL;
retRS = DBCommand.ExecuteReader();
if (retRS.HasRows == false)
{
retRS.Close();
if (bDBClose == true)
{
Fun_CloseDB();
}
return(false);
}
else
{
if (bDBClose == true)
{
Fun_CloseDB();
}
return(true);
}
}
catch (Exception)
{
return(false);
}
}
public DbDataReader ExecReader(DbCommand cm)
{
//OnExecuting(ExecTypes.Reader, cm);
DbDataReader r = cm.ExecuteReader();
//OnExecuted(ExecTypes.Reader, cm, null);
return r;
}
public virtual void AsyncExecuteReader(DbCommand cmd, Action<DbDataReader> readerHandler)
{
Require.IsNotNull("cmd", cmd);
Require.IsNotNull("resultHandler", readerHandler);
ThreadPool.QueueUserWorkItem(new WaitCallback((unused) =>
{
readerHandler(cmd.ExecuteReader());
}));
}
public static DbDataReader GetDataReader(DbCommand comando)
{
try
{
return comando.ExecuteReader();
}catch(Exception e){
MessageBox.Show("Erro ao retornar os valores " + e.Message + " " + e.Source, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
/// <summary>
/// Executes a command and returns the selected data
/// </summary>
/// <returns>The data selected by the command</returns>
public sp.DataTable ExecuteReader()
{
using (db.DbCommand cmd = ToDbCommand())
{
using (db.DbDataReader dr = cmd.ExecuteReader())
{
return(sp.DataTable.FromDataReader(dr));
}
}
}
public virtual System.Data.IDataReader ExecuteReader(System.Data.Common.DbCommand command, System.Data.Common.DbTransaction dbTransaction)
{
if (command == null)
{
return(DataReaderWrapper.Empty);
}
PrepareCommand(command, dbTransaction);
return(command.ExecuteReader());
}
public T[] List <T>(string Sql, System.Data.Common.DbTransaction transaction = null)
{
DbBase.LastSQL = Sql;
System.Data.Common.DbCommand cmd = null;
System.Data.Common.DbDataReader dataReader = null;
T[] result = null;
try
{
if (transaction == null && DbConnection.State != ConnectionState.Open)
{
DbConnection.Open();
}
cmd = DbBase.DbConnection.CreateCommand();
cmd.CommandText = Sql;
if (transaction != null)
{
cmd.Transaction = transaction;
}
dataReader = cmd.ExecuteReader();
List <string> columns = GetDataReaderColumns(dataReader);
result = List <T>(dataReader, columns);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
dataReader = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (transaction == null)
{
DbConnection.Close();
}
}
if (result == null)
{
return(new T[] { });
}
return(result);
}
public IEnumerable <System.Data.Common.DbDataReader> GetReader(System.Data.Common.DbCommand cmd)
{
LogCommand(cmd);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
yield return(reader);
}
}
public static DataTable ExecuteSelectCommand(DbCommand command)
{
DataTable table;
command.Connection.Open();
DbDataReader reader = command.ExecuteReader();
table = new DataTable();
table.Load(reader);
reader.Close();
command.Connection.Close();
return table;
}
public virtual System.Data.IDataReader ExecuteReader(System.Data.Common.DbCommand command)
{
if (command == null)
{
return(DataReaderWrapper.Empty);
}
using (var conn = GetWrapperedConnection())
{
PrepareCommand(command, conn);
return(new DataReaderWrapper(conn, command.ExecuteReader()));
}
}
public static DbDataReader ExecuteReader(DbCommand cmd)
{
DbConnection connection = GetConnection();
try
{
cmd.Connection = connection;
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
connection.Close();
throw;
}
}
public IEnumerable<ResultSet> ExecuteReader(DbCommand command)
{
command.Connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.FieldCount > 0)
{
return reader.ToMultipleDictionaries();
}
// Don't call ExecuteReader for this function again.
_executeImpl = ExecuteNonQuery;
return Enumerable.Empty<ResultSet>();
}
}
public IEnumerable<DbDataReader> Select(DbCommand cmd)
{
DbConnection conn = this.GetConnection();
cmd.Connection = conn;
try
{
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
yield return reader;
}
finally
{
cmd.Connection = null;
}
}
public static SDC.DbDataReader ReadData(string sproc, List <ParamStruct> paramList)
{
SDC.DbProviderFactory factory = SDC.DbProviderFactories.GetFactory(Properties.Settings.Default.provider);
SDC.DbCommand comm = factory.CreateCommand();
comm = BuildCommand(sproc, paramList);
SDC.DbConnection conn = Connection(factory);
comm.Connection = conn;
conn.Open();
SDC.DbDataReader dr = comm.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return(dr);
}
protected override object ExecuteResultInternal(IDbExecuteContext ctx, DbCommand command, Type resultType, object resultInstance)
{
var type = this.GetResultType(ctx, resultType, resultInstance, null);
if(type != null && !typeof(Array).IsAssignableFrom(type)) {
throw new DataException("要求返回的类型不正确");
}
var elementType = type != null ? type.GetElementType() : null;
var list = new ArrayList();
using (var reader = command.ExecuteReader(CommandBehavior.SingleResult)) {
while(reader.Read()) {
var result = this.ObjectMapper.Mapping(ctx.Statement, reader.ToDictionary(), elementType, null);
list.Add(result);
}
}
return elementType == null ? list.ToArray() : list.ToArray(elementType);
}
public IDataReader ExecuteReader(DbCommand cmd)
{
DbConnection connection = null;
try
{
connection = GetOpenConnection();
cmd.Connection = connection;
var behaviour = IsSharedConnection() ? CommandBehavior.Default : CommandBehavior.CloseConnection;
return cmd.ExecuteReader(behaviour);
}
catch (Exception)
{
Releaseconnection(connection);
throw;
}
}
/// <summary>
/// <see cref="System.Data.Common.DbCommand.Connection"/>に対して<see cref="System.Data.Common.DbCommand.CommandText"/>を実行し、<br />
/// <see cref="System.Data.CommandBehavior"/>値の<c>1</c>つを使用して<see cref="System.Data.Common.DbDataReader"/>を返します。
/// </summary>
/// <param name="command"><see cref="System.Data.Common.DbCommand.Connection"/></param>
/// <param name="behavior"><see cref="System.Data.CommandBehavior"/>値の<c>1</c>つ</param>
/// <returns><see cref="System.Data.Common.DbDataReader"/>オブジェクト。</returns>
public DbDataReader ExecuteReader(DbCommand command, CommandBehavior behavior)
{
var key = ExecuteReaderUtility.CreateKey(command);
var value = this.Get(key);
if (value == null)
{
using (var reader = command.ExecuteReader(behavior))
{
value = new DataTable();
value.Load(reader);
value = this.AddOrGetExisting(key, value);
}
}
return new DataTableReader(value);
}
public static IEnumerable <object[]> ExecuteQuery(
this ExtendDataContext ctx, string query)
{
using (System.Data.Common.DbCommand cmd = ctx.Connection.CreateCommand())
{
cmd.CommandText = query;
ctx.Connection.Open();
using (System.Data.Common.DbDataReader rdr =
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
object[] res = new object[rdr.FieldCount];
rdr.GetValues(res);
yield return(res);
}
}
}
}
private List <Dictionary <string, object> > ExecutaQuery(System.Data.Common.DbCommand cmd)
{
List <Dictionary <string, object> > Linhas = new List <Dictionary <string, object> >();
try
{
if (cmd.CommandText.Contains("CALL "))
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
cmd.CommandTimeout = 1800;
using (System.Data.IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Dictionary <string, object> linha = new Dictionary <string, object>();
// Colunas
for (int i = 0; i < reader.FieldCount; i++)
{
linha.Add(reader.GetName(i), reader.GetValue(i) == DBNull.Value ? null : reader.GetValue(i));
}
// Adiciona Dictionary da Linha
Linhas.Add(linha);
}
}
}
catch (Exception ex)
{
string txtComando = Util.DevolveStringCommand(cmd);
throw new Exception(string.Format("Erro ao executar comando no banco de dados.\r\nTexto: {0}\r\nDetalhes: {1}", txtComando, ex.Message), ex);
}
return(Linhas);
}
public DataTable ExecuteSelectCommand(DbCommand command)
{
DataTable table;
try
{
OpenConnection();
DbDataReader reader = command.ExecuteReader();
table = new DataTable();
table.Load(reader);
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
}
return table;
}
public static DataTable ExecuteSelectCommand(DbCommand comm)
{
DataTable dt = null;
try
{
comm.Connection.Open();
DbDataReader reader = comm.ExecuteReader();
dt = new DataTable();
dt.Load(reader);
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
comm.Connection.Close();
}
return dt;
}
public static DataTable ExecuteSelectCommand(DbCommand comm)
{
DataTable table;
table = new DataTable();
try
{
comm.Connection.Open();
DbDataReader r = comm.ExecuteReader();
table.Load(r);
r.Close();
}
catch (Exception ex)
{
throw ex;
}
return table;
}
public static DataTable ExecuteCommand(DbCommand command)
{
DataTable table;
try
{
command.Connection.Open();
DbDataReader reader = command.ExecuteReader();
table = new DataTable();
table.Load(reader);
reader.Close();
}
catch
{
throw;
}
finally
{
command.Connection.Close();
}
return table;
}
/// <summary>
/// Kiem tra xem so dien thoai khach hang da co tren he thong chua
/// </summary>
/// Tra ve: True - Da ton tại; False - Chua ton tai
public static bool m2018_CheckClient(string CodCardClient)
{
using (System.Data.Common.DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_2018_CheckClient;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters;
System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PCodCardClient;
DbParameter.Value = CodCardClient;
DbParameters.Add(DbParameter);
bool HasRows = false;
try
{
using (DbDataReader DbDataReader = DbCommand.ExecuteReader())
{ HasRows = DbDataReader.HasRows; }
}
catch { }
return(HasRows);
}
}
protected virtual IPaginateResult ExecuteResultInternal(IDbExecuteContext ctx, DbCommand command, Type elementType)
{
PaginateResult result;
if(elementType == null) {
result = new PaginateResult();
} else {
var type = typeof(PaginateResult<>);
var instanceType = type.MakeGenericType(elementType);
result = (PaginateResult)TypeHelper.CreateObject(instanceType, null, true);
}
using (var reader = command.ExecuteReader(CommandBehavior.Default)) {
if (reader.Read()) {
result.Count = Convert.ToInt32(reader[0]);
}
var list = new ArrayList();
if(reader.NextResult()) {
while (reader.Read()) {
list.Add(this.ObjectMapper.Mapping(ctx.Statement, reader.ToDictionary(), elementType, null));
}
}
result.Items = elementType != null ? (object[])list.ToArray(elementType) : list.ToArray();
}
return result;
}
/// <summary>
/// 执行查询，返回datatable
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public static DataTable ExecuteSelectCommand(DbCommand command)
{
DataTable table;
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
DbDataReader reader = command.ExecuteReader();
table = new DataTable();
table.Load(reader);
reader.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
command.Connection.Close();
}
return table;
}
/// <summary>
/// Executes a command object and returns a reader
/// </summary>
/// <param name="comm">The command object to return</param>
/// <returns>DbDataReader with the results</returns>
/// <history>
/// [coullm] 22/07/09 Added functionality to throw a data operation exception
/// </history>
public virtual DbDataReader ExecuteReader(DbCommand comm)
{
DbDataReader reader = null;
CommandBehavior behavior = CommandBehavior.Default;
if (_multipleConnections)
behavior = CommandBehavior.CloseConnection;
OpenConnection(comm.Connection);
reader = comm.ExecuteReader(behavior);
return reader;
}
public override DbDataReader GetResultSet(DbCommand statement)
{
return statement.ExecuteReader();
}
public static DbDataReader ExecuteReader(DbCommand dbCommand, CommandBehavior commandBehavior)
{
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
return dbCommand.ExecuteReader(commandBehavior);
}
/// <summary>
/// Execute a command and return the results as a DataTable object
/// </summary>
/// <param name="command">database command</param>
public static DataTable ExecuteSelectCommand(DbCommand command)
{
DataTable table = null;
using (command)
{
try
{
command.Connection.Open();
table = new DataTable();
using (var reader = command.ExecuteReader())
{
table.Load(reader);
reader.Close();
}
}
catch (Exception ex)
{
Utilities.LogError(ex);
}
finally
{
if (command != null)
{
command.Connection.Close();
command.Parameters.Clear();
}
}
return table;
}
}
private void button2_Click(object sender, EventArgs e)
{
try {
//////Microsoft.Office.Tools.Excel.Workbook theWorkbook = Microsoft.Office.Tools.Excel.ex .ExcelObj.Workbooks.Open(
////// this.txtXmlPath.Text, 0, true, 5,
////// "", "", true, Microsoft.Office.Tools.Excel.xl.xlWindows, "\t", false, false,
////// 0, true);
//////Excel.Sheets sheets = theWorkbook.Worksheets;
//////Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
//////int i=1;
//////string[] strArray;
//////System.Array myvalues;
//////do {
////// Excel.Range range = worksheet.get_Range("A" + i.ToString(), "O" + i.ToString());
////// myvalues = (System.Array)range.Cells.Value;
////// strArray = ConvertToStringArray(myvalues);
//////} while (strArray[0].Length > 0);
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.txtXlsPath.Text + @";Extended Properties=""Excel 8.0;HDR=YES;""";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
////using (DbCommand command = connection.CreateCommand())
////{
//// command.CommandText = "INSERT INTO [Cities$]
//// (ID, City, State) VALUES(4,\"Tampa\",\"Florida\")";
connection.Open();
//// command.ExecuteNonQuery();
////}
System.Data.Common.DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM [volumes$]";
System.Data.Common.DbDataReader drdr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(drdr);
drdr.Dispose();
cmd.Dispose();
XmlDocument docXml = new XmlDocument();
XmlNode nodeRoot = docXml.AppendChild(docXml.CreateNode(XmlNodeType.Element, "root", docXml.NamespaceURI));
XmlNode nodeVolumes = nodeRoot.AppendChild(docXml.CreateNode(XmlNodeType.Element, "volumes", docXml.NamespaceURI));
XmlNode nodeVolume;
XmlNode nodeColumns = nodeRoot.AppendChild(docXml.CreateNode(XmlNodeType.Element, "columns", docXml.NamespaceURI));
foreach (DataColumn dc in dt.Columns)
{
XmlNode nodeColumn = nodeColumns.AppendChild(docXml.CreateNode(XmlNodeType.Element, "column", docXml.NamespaceURI));
XmlAttribute attFldName = nodeColumn.Attributes.Append((XmlAttribute)docXml.CreateNode(XmlNodeType.Attribute, "FldName", docXml.NamespaceURI));
attFldName.InnerText = dc.ColumnName;
}
foreach (DataRow dr in dt.Rows)
{
nodeVolume = nodeVolumes.SelectSingleNode("./volume[@volume_code = '" + dr["Volume"].ToString() + "']");
if (nodeVolume == null)
{
nodeVolume = nodeVolumes.AppendChild(docXml.CreateNode(XmlNodeType.Element, "volume", docXml.NamespaceURI));
XmlAttribute attVolumeCode = nodeVolume.Attributes.Append((XmlAttribute)docXml.CreateNode(XmlNodeType.Attribute, "volume_code", docXml.NamespaceURI));
attVolumeCode.InnerText = dr["volume"].ToString();
}
XmlNode nodeIssue = nodeVolume.AppendChild(docXml.CreateNode(XmlNodeType.Element, "issue", docXml.NamespaceURI));
foreach (DataColumn dc in dt.Columns)
{
if (string.Compare(dc.ColumnName, "volume", true) != 0)
{
XmlNode nodeFld;
nodeFld = nodeIssue.AppendChild(docXml.CreateNode(XmlNodeType.Element, "Fld", docXml.NamespaceURI));
XmlAttribute attFldName = nodeFld.Attributes.Append((XmlAttribute)(docXml.CreateNode(XmlNodeType.Attribute, "Name", docXml.NamespaceURI)));
attFldName.InnerText = dc.ColumnName;//.Replace(" ", "_");
nodeFld.InnerText = dr[dc].ToString();
}
}
}
string strXmlFilePath = this.txtXlsPath.Text.Substring(0, this.txtXlsPath.Text.LastIndexOf(".xls")) + ".xml";
docXml.Save(strXmlFilePath);
this.txtXmlPath.Text = strXmlFilePath;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static DbDataReader Reader(this System.Data.Common.DbCommand cmd, string command)
{
cmd.CommandText = command;
return(cmd.ExecuteReader());
}
