public DataSet GetUbicacion()
{
DataSet ds = new DataSet();
SqlDatabase SqlClient = new SqlDatabase(connectionString);
//Departamento
DbCommand SqlCommand1 = SqlClient.GetStoredProcCommand("ventas.Usp_GetDepartamento");
SqlCommand1.CommandTimeout = 200;
ds.Load(SqlClient.ExecuteReader(SqlCommand1), LoadOption.PreserveChanges, "Departamento");
//Distrito
DbCommand SqlCommand2 = SqlClient.GetStoredProcCommand("ventas.Usp_GetDistrito");
SqlCommand2.CommandTimeout = 200;
ds.Load(SqlClient.ExecuteReader(SqlCommand2), LoadOption.PreserveChanges, "Distrito");
//Provincia
DbCommand SqlCommand3 = SqlClient.GetStoredProcCommand("ventas.Usp_GetProvincia");
SqlCommand3.CommandTimeout = 200;
ds.Load(SqlClient.ExecuteReader(SqlCommand3), LoadOption.PreserveChanges, "Provincia");
//Via
DbCommand SqlCommand4 = SqlClient.GetStoredProcCommand("ventas.Usp_GetVia");
SqlCommand4.CommandTimeout = 200;
ds.Load(SqlClient.ExecuteReader(SqlCommand4), LoadOption.PreserveChanges, "Via");
return(ds);
}
private void InitializeMRUFields( )
{
string sqlQueryCliente = "SELECT Nombre from Cliente";
string sqlQueryNIT = "SELECT NIT from Cliente";
CommonUtils.ConexionBD.AbrirConexion( );
SqlCommand cmdCliente = new SqlCommand(sqlQueryCliente, CommonUtils.ConexionBD.Conexion);
SqlCommand cmdNIT = new SqlCommand(sqlQueryNIT, CommonUtils.ConexionBD.Conexion);
DataSet dataSet = new DataSet( );
dataSet.Load(cmdCliente.ExecuteReader( ), LoadOption.OverwriteChanges, "Clientes");
dataSet.Load(cmdNIT.ExecuteReader( ), LoadOption.OverwriteChanges, "NIT");
List <DataRow> listaClientes = dataSet.Tables["Clientes"].AsEnumerable( ).ToList( );
List <DataRow> listaNIT = dataSet.Tables["NIT"].AsEnumerable( ).ToList( );
// TODO: Esto se puede mejorar
foreach (DataRow item in listaClientes)
{
var items = item.ItemArray;
mruCliente.Properties.Items.AddRange(items);
break;
}
foreach (DataRow item in listaNIT)
{
var items = item.ItemArray;
mruNIT.Properties.Items.AddRange(items);
break;
}
CommonUtils.ConexionBD.CerrarConexion( );
}
private void updateClientesFromDB()
{
clientes = new Clientes();
#region autocomplete settings
txtNIT.AutoCompleteCustomSource.Clear();
txtCliente.AutoCompleteCustomSource.Clear();
#endregion
string sqlQueryCliente = "SELECT Nombre,NIT from Cliente";
string sqlQueryNIT = "SELECT NIT from Cliente";
CommonUtils.ConexionBD.AbrirConexion();
SqlCommand cmdCliente = new SqlCommand(sqlQueryCliente, CommonUtils.ConexionBD.Conexion);
SqlCommand cmdNIT = new SqlCommand(sqlQueryNIT, CommonUtils.ConexionBD.Conexion);
DataSet dataSet = new DataSet();
dataSet.Load(cmdCliente.ExecuteReader(), LoadOption.OverwriteChanges, "Clientes");
dataSet.Load(cmdNIT.ExecuteReader(), LoadOption.OverwriteChanges, "NIT");
List <DataRow> listaClientes = dataSet.Tables["Clientes"].AsEnumerable().ToList();
//List<DataRow> listaNIT = dataSet.Tables["NIT"].AsEnumerable().ToList();
foreach (DataRow clienteTemp in listaClientes)
{
Cliente newClient = new Cliente((String)clienteTemp[1], (String)clienteTemp[0]);
clientes.addCliente(newClient);
txtNIT.AutoCompleteCustomSource.Add(newClient.NIT);
txtCliente.AutoCompleteCustomSource.Add(newClient.Nombre);
}
CommonUtils.ConexionBD.CerrarConexion();
}
public DataSet ReadObjects(List <object> anchors)
{
Tracer.Enter("readobjects");
DataSet ds = new DataSet();
try
{
string singleanchor = Configuration.AnchorColumn;
string multivalueanchorref = Configuration.BackReferenceColumn;
DataTable single = new DataTable(Configuration.TableNameSingle);
DataTable multi = Configuration.HasMultivalueTable ? new DataTable(Configuration.TableNameMulti) : null;
ds.Tables.Add(single);
StringBuilder anchorList = new StringBuilder();
for (int i = 0; i < anchors.Count; i++)
{
if (i > 0)
{
anchorList.Append(",");
}
anchorList.AppendFormat("'{0}'", anchors[i]);
}
StringBuilder query = new StringBuilder();
query.AppendFormat("select * from {0} where [{1}] in ({2});", Configuration.TableNameSingle, singleanchor, anchorList);
if (Configuration.HasMultivalueTable)
{
query.AppendFormat("select * from {0} where [{1}] in ({2});", Configuration.TableNameMulti, multivalueanchorref, anchorList);
ds.Tables.Add(multi);
}
Tracer.TraceInformation("run-query '{0}'", query.ToString());
using (SqlCommand cmd = new SqlCommand(query.ToString(), con))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (Configuration.HasMultivalueTable)
{
ds.Load(dr, LoadOption.OverwriteChanges, single, multi);
DataRelation relCustOrder = new DataRelation("mv", single.Columns[singleanchor], multi.Columns[multivalueanchorref]);
ds.Relations.Add(relCustOrder);
}
else
{
ds.Load(dr, LoadOption.OverwriteChanges, single);
}
}
}
}
catch (Exception ex)
{
Tracer.TraceError("readobjects {0}", ex.Message);
throw;
}
finally
{
Tracer.Exit("readobjects");
}
return(ds);
}
private void InitializeGrid( )
{
CommonUtils.ConexionBD.AbrirConexion( );
string sqlProductData = "SELECT Producto.ProductoId, Producto.Imagen, Producto.Nombre, Producto.Descripcion, Producto.Tipo, " +
"Categoria.Tipo AS [Categoria], Producto.Tamano AS Tamaño, Producto.PrecioVenta AS [Precio de Venta], Producto.RecetaId, Producto.Mostrar AS [Visible] From Producto, Categoria " +
"WHERE Producto.CategoriaId=Categoria.CategoriaId";
string sqlProductCombo = "SELECT Producto.Nombre, Producto_Producto.ComboId FROM Producto INNER JOIN Producto_Producto ON Producto.ProductoId = Producto_Producto.ProductoId";
try
{
SqlCommand cmdProduct = new SqlCommand(sqlProductData, CommonUtils.ConexionBD.Conexion);
SqlCommand cmdCombo = new SqlCommand(sqlProductCombo, CommonUtils.ConexionBD.Conexion);
DataSet dataSet = new DataSet( );
dataSet.Load(cmdProduct.ExecuteReader( ), LoadOption.OverwriteChanges, "Producto");
dataSet.Load(cmdCombo.ExecuteReader( ), LoadOption.OverwriteChanges, "Combo");
dataSet.Relations.Add("Combo",
dataSet.Tables["Producto"].Columns["ProductoId"],
dataSet.Tables["Combo"].Columns["ComboId"], false);
gridProductos.DataSource = dataSet.Tables["Producto"];
ColumnView view = this.gridProductos.MainView as ColumnView;
view.Columns.Add( );
view.ActiveFilterEnabled = true;
DevExpress.XtraEditors.Repository.RepositoryItemPictureEdit rPic = new DevExpress.XtraEditors.Repository.RepositoryItemPictureEdit( );
rPic.NullText = "No Image";
rPic.SizeMode = DevExpress.XtraEditors.Controls.PictureSizeMode.Squeeze;
view.Columns[1].ColumnEdit = rPic;
view.Columns[1].GetBestWidth( );
view.Columns[8].Visible = false;
view.Columns[9].Visible = true;
this.gridProductos.RepositoryItems.Add(view.Columns[1].ColumnEdit);
MessageBarValue = dataSet.Tables["Producto"].Rows.Count.ToString( ) + " Items";
}
catch (Exception ex)
{
log.Error(ex.Message, ex);
MessageBarValue = "No se pudieron listar los productos. Hubo un error: " + ex.Message;
}
finally
{
barItem.Caption = MessageBarValue;
}
CommonUtils.ConexionBD.CerrarConexion( );
}
/// <summary>
/// Exports the Gallery Server data in the current database to an XML-formatted string.
/// </summary>
/// <returns>Returns an XML-formatted string containing the gallery data.</returns>
private static string Export(IBackupFile backupFile)
{
using (var ds = new DataSet("GalleryServerData"))
{
var asm = System.Reflection.Assembly.GetExecutingAssembly();
using (var stream = asm.GetManifestResourceStream("GalleryServer.Data.Schema.GalleryServerSchema.xml"))
{
ds.ReadXmlSchema(stream);
}
using (var cn = GetDbConnection(backupFile))
{
using (var cmd = cn.CreateCommand())
{
cn.Open();
if (backupFile.IncludeMembershipData)
{
foreach (var tableName in backupFile.MembershipTables)
{
cmd.CommandText = String.Concat(@"SELECT * FROM ", Utils.GetSqlName(tableName, backupFile.GalleryDataStore, "dbo"), ";");
ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, tableName);
}
}
if (backupFile.IncludeGalleryData)
{
foreach (var tableName in backupFile.GalleryTableNames)
{
cmd.CommandText = String.Concat(@"SELECT * FROM ", Utils.GetSqlName(tableName, backupFile.GalleryDataStore), ";");
ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, tableName);
}
}
else
{
// We always want to export the AppSettings table because that contains the schema version, which we need during a restore.
cmd.CommandText = String.Concat(@"SELECT * FROM ", Utils.GetSqlName("AppSetting", backupFile.GalleryDataStore), ";");
ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, "AppSetting");
}
using (var sw = new StringWriter(CultureInfo.InvariantCulture))
{
ds.WriteXml(sw, XmlWriteMode.WriteSchema);
//ds.WriteXmlSchema(@"E:\GalleryServerSchema.xml"); // Use to create new schema file after a database change
return(sw.ToString());
}
}
}
}
}
void LoadDataSet()
{
ObjectTracker objectTracker = TrackerManager.Instance.GetTracker <ObjectTracker> ();
DataSet dataSet = objectTracker.CreateDataSet();
if (dataSet.Load(MergeCube.MergeMultiTargetCore.DataSet, VuforiaUnity.StorageType.STORAGE_ABSOLUTE))
{
objectTracker.Stop();
if (!objectTracker.ActivateDataSet(dataSet))
{
Debug.LogError("Failed to Activate MergeCube DataSet");
}
if (!objectTracker.Start())
{
Debug.LogError("Tracker Failed to Start.");
}
int counter = 0;
IEnumerable <TrackableBehaviour> tbs = TrackerManager.Instance.GetStateManager().GetTrackableBehaviours();
foreach (TrackableBehaviour tb in tbs)
{
if (tb.name == "New Game Object")
{
tb.gameObject.name = ++counter + ":DynamicImageTarget-" + tb.TrackableName;
tb.gameObject.AddComponent <MergeTrackableEventHandler> ();
}
}
}
else
{
Debug.LogError("Failed to Load MergeCube Dataset");
}
}
public void SingleTable_Load_Succeeds()
{
Logger.Enable();
using (var connection = new AseConnection(ConnectionStrings.Pooled))
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = "select * from (select 'asdf' as stringCol, 1 as intCol union select 'derp' as stringCol, 2 as intCol) x";
command.CommandType = CommandType.Text;
var ds = new DataSet();
using (var reader = command.ExecuteReader())
{
var tbl = new DataTable("table_1");
ds.Tables.Add(tbl);
ds.Load(reader,
LoadOption.OverwriteChanges,
(sender, args) => Console.WriteLine($"FillErrorEventHandler - {args}"),
tbl);
}
var table1 = ds.Tables["table_1"];
var stringCol = table1.Columns["stringCol"];
var intCol = table1.Columns["intCol"];
var row = table1.Rows[0];
Assert.AreEqual("asdf", Convert.ToString(row[stringCol]));
Assert.AreEqual(1, Convert.ToInt32(row[intCol]));
}
}
public void LoadBill(Bill bill)
{
Bill = bill;
WorkTimeRanges = new DataSet <WorkTimeRange>(Bill.WorkTimeRangesFilePath);
WorkTimeRanges.Load();
}
/// <summary>
/// 리턴값이 리스트형태의 쿼리에 사용됩니다.
/// </summary>
/// <param name="entity"></param>
/// <param name="sql"></param>
/// <returns></returns>
public virtual DataTable ListAndCount(Query query, out int count, IEntity entity)
{
count = 0;
try
{
IDataParameter[] parms = this.GetParameters(query);
parms = this.ParamHelper.SetParameterValue(parms, entity);
IDataReader reader = this.IXDB.ExecuteReader(this.GetCommand(query, parms));
DataSet ds = new DataSet();
ds.Load(reader, LoadOption.OverwriteChanges, "LIST", "COUNT");
count = Convert.ToInt32(ds.Tables["COUNT"].Rows[0][0]);
reader.Close();
return(ds.Tables["LIST"]);
}
catch (System.Exception ex)
{
throw ex; // throw new Exception.DBException(ex.Message, ex);
}
finally
{
this.CloseConnection();
}
}
protected void Page_Load(object sender, EventArgs e)
{
string connStr = "Data Source=.\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;";
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand command = new SqlCommand("select * from customers", conn);
conn.Open();
DataSet ds = new DataSet();
ds.DataSetName = "Customers";
ds.Load(command.ExecuteReader(), LoadOption.OverwriteChanges, "Customer");
//Response.ContentType = "text/xml";
//ds.WriteXml(Response.OutputStream);
//Added in Listing 13-15
XmlDataDocument doc = new XmlDataDocument(ds);
doc.DataSet.EnforceConstraints = false;
XmlNode node = doc.SelectSingleNode(@"//Customer[CustomerID = 'ANATR']/ContactTitle");
node.InnerText = "Boss";
doc.DataSet.EnforceConstraints = true;
DataRow dr = doc.GetRowFromElement((XmlElement)node.ParentNode);
Response.Write(dr["ContactName"].ToString() + " is the ");
Response.Write(dr["ContactTitle"].ToString());
}
}
public All GetAll(IDbConnection connection)
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (var cmd = connection.CreateCommand()) {
var sql = File.ReadAllText(Constant.CurrentProviderQueryFile);
if (sql.IsNullOrWhiteSpace())
{
throw new Exception($"{Constant.CurrentProviderQueryFile}:查询语句为空!");
}
sql = sql.Replace("@DATABASE", connection.Database);
cmd.CommandText = sql;
var reader = cmd.ExecuteReader();
DataSet dataSet = new DataSet();
DataTable table1 = new DataTable();
DataTable table2 = new DataTable();
DataTable table3 = new DataTable();
dataSet.Tables.Add(table1);
dataSet.Tables.Add(table2);
dataSet.Tables.Add(table3);
dataSet.Load(reader, LoadOption.OverwriteChanges, table1, table2, table3);
return(new All {
Tables = GetTables(table1).ToList(),
Columns = GetColumns(table2).ToList(),
ForeignKeys = GetForeignKeys(table3).ToList()
});
}
}
protected DataSet ApplyFilterOnData()
{
if (null == this.RawData)
{
throw new InvalidOperationException("No RawData upon to apply filter");
}
if (!(this.RawData is IDataReader))
{
throw new InvalidOperationException("Raw data must be a Reader");
}
DataSet ds = new DataSet();
ds.Load((IDataReader)this.RawData, LoadOption.OverwriteChanges, "data");
if (ds.Tables.Count > 0)
{
DataTable table = ds.Tables[0];
if (table.Rows.Count > 0)
{
DataRow[] rows = new DataRow[table.Rows.Count];
table.Rows.CopyTo(rows, 0);
foreach (DataRow dr in rows)
{
if (!this.IsFilterMatch(dr))
{
table.Rows.Remove(dr);
}
}
}
}
return(ds);
}
public ImageCompress()
{
int[] layersizes = new int[3] {
8, 18, 8
};
ActivationFunction[] activFunctions = new ActivationFunction[3] {
ActivationFunction.None, ActivationFunction.Sigmoid, ActivationFunction.Linear
};
XmlDocument xdoc = new XmlDocument();
xdoc.Load(Server.MapPath("resources/ann.xml"));
ds = new DataSet();
ds.Load((XmlElement)xdoc.DocumentElement.ChildNodes[0]);
bpnetwork = new BackPropNetwork(layersizes, activFunctions);
nt = new NetworkTrainer(bpnetwork, ds);
nt.maxError = 0.00001;
nt.maxiterations = 10000;
nt.nudgewindow = 500;
nt.traininrate = 0.1;
nt.TrainDataset();
// save error
double[] err = nt.geteHistory();
string[] filedata = new string[err.Length];
for (int i = 0; i < err.Length; i++)
{
filedata[i] = i.ToString() + " " + err[i].ToString();
}
}
//public virtual T PopulateRecord(SqlDataReader reader)
//{
// return null;
//}
//protected IEnumerable<T> GetRecords(SqlCommand command)
//{
// var list = new List<T>();
// command.Connection = _connection;
// _connection.Open();
// try
// {
// var reader = command.ExecuteReader();
// try
// {
// while (reader.Read())
// list.Add(PopulateRecord(reader));
// }
// finally
// {
// // Always call Close when done reading.
// reader.Close();
// }
// }
// finally
// {
// _connection.Close();
// }
// return list;
//}
//protected T GetRecord(SqlCommand command)
//{
// T record = null;
// command.Connection = _connection;
// _connection.Open();
// try
// {
// var reader = command.ExecuteReader();
// try
// {
// while (reader.Read())
// {
// record = PopulateRecord(reader);
// break;
// }
// }
// finally
// {
// // Always call Close when done reading.
// reader.Close();
// }
// }
// finally
// {
// _connection.Close();
// }
// return record;
//}
//protected IEnumerable<T> ExecuteStoredProc(SqlCommand command)
//{
// var list = new List<T>();
// command.Connection = _connection;
// command.CommandType = CommandType.StoredProcedure;
// _connection.Open();
// try
// {
// var reader = command.ExecuteReader();
// try
// {
// while (reader.Read())
// {
// var record = PopulateRecord(reader);
// if (record != null) list.Add(record);
// }
// }
// finally
// {
// // Always call Close when done reading.
// reader.Close();
// }
// }
// finally
// {
// _connection.Close();
// }
// return list;
//}
/// <summary>
/// Executes the stored proc, on the supplied connection, and returns the IDataReader instance (use this in multi-threaded cases, managing the cycle of your own connection)
/// </summary>
/// <param name="procedureName"></param>
/// <param name="CommandTimeout"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private DataSet GetDataForSP(string procedureName, IDbConnection connection, int commandTimeout, string[] tableNames, Dictionary <string, object> parameters, ref Dictionary <string, object> outParameters)
{
IDbCommand command = GetCommandForSP(procedureName, connection, commandTimeout, parameters, outParameters);
//open the connection if necessary
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
//Load data
DataSet data = new DataSet();
using (IDataReader reader = command.ExecuteReader())
{
data.Load(reader, LoadOption.OverwriteChanges, tableNames);
}
//Load output params
Dictionary <string, object> outParamData = new Dictionary <string, object>();
if (outParameters != null && outParameters.Count > 0)
{
foreach (string key in outParameters.Keys)
{
outParamData.Add(key, (command.Parameters[key] as IDbDataParameter).Value.ToString());
}
//assign to out parameters
outParameters = outParamData;
}
return(data);
}
private void SetupTrackables()
{
// TODO: Avoid to load all trackables
ObjectTracker objectTracker = TrackerManager.Instance.GetTracker <ObjectTracker>();
DataSet dataSet = objectTracker.CreateDataSet();
if (dataSet.Load("SimpleARDefaultMarkers"))
{
objectTracker.Stop();
if (!objectTracker.ActivateDataSet(dataSet))
{
// Note: ImageTracker cannot have more than 1000 total targets activated
Debug.Log("<color=yellow>Failed to Activate DataSet: SimpleARDefaultMarkers</color>");
}
if (!objectTracker.Start())
{
Debug.Log("<color=yellow>Tracker Failed to Start.</color>");
}
IEnumerable <TrackableBehaviour> tbs = TrackerManager.Instance.GetStateManager().GetTrackableBehaviours();
foreach (TrackableBehaviour tb in tbs)
{
predefinedNaturalMarkers.Add(tb.TrackableName, tb);
}
}
}
/// <summary>
/// GetDataSet
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public async Task <DataSet> GetDataSet(string procedureName,
List <SqlParameter> parameters,
CommandType commandType = CommandType.StoredProcedure)
{
string[] s = new string[10];
DataSet ds = new DataSet();
try
{
using (SqlConnection connection = this.GetConnection())
{
using (DbCommand cmd = this.GetCommand(connection, procedureName, commandType))
{
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
ds.Load(await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection), LoadOption.OverwriteChanges, s);
}
}
}
catch (Exception ex)
{
//LogException("Failed to GetDataReader for " + procedureName, ex, parameters);
throw;
}
return(ds);
}
public DataTable ExecuteQuery(string query, string dataTableName, SqlParameter[] parameters)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = query;
command.CommandTimeout = commandExecutionTimeout;
command.CommandType = CommandType.Text;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
using (var reader = command.ExecuteReader())
{
var dataSet = new DataSet();
var table = new DataTable(dataTableName);
dataSet.Tables.Add(table);
dataSet.Load(reader, LoadOption.OverwriteChanges, table);
return(table);
}
}
}
}
public DataTable Query(String sql, params object[] args)
{
using (DbConnection conn = new SqlConnection(db.Database.Connection.ConnectionString))
{
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
if (null != args)
{
for (int i = 0; i < args.Length; i++)
{
Object v = args[i];
DbParameter param = cmd.CreateParameter();
param.ParameterName = (i + 1) + "";
param.Value = v ?? DBNull.Value;
param.DbType = v == null ? DbType.AnsiString : (DBTypesMapping.ContainsKey(v.GetType()) ? DBTypesMapping[v.GetType()] : DbType.AnsiString);
cmd.Parameters.Add(param);
}
}
DbDataReader ddr = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds.Tables.Add(dt);
ds.Load(ddr, LoadOption.OverwriteChanges, dt);
ds.EnforceConstraints = false;
return(dt);
}
}
public async Task <DataSet> GetAllAatfSentOnDataCsv(int complianceYear, string obligationType, Guid?authority, Guid?panArea)
{
var complianceYearParameter = new SqlParameter("@ComplianceYear", complianceYear);
var obligationTypeParameter = new SqlParameter("@ObligationType", (object)obligationType ?? DBNull.Value);
var authorityParameter = new SqlParameter("@CA", (object)authority ?? DBNull.Value);
var panAreaParameter = new SqlParameter("@PanArea", (object)panArea ?? DBNull.Value);
var dataSet = new DataSet();
var sentOnData = new DataTable();
var addressData = new DataTable();
dataSet.Tables.Add(sentOnData);
dataSet.Tables.Add(addressData);
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = "[AATF].[getAllAatfSentOnCsvData] @ComplianceYear, @ObligationType, @CA, @PanArea";
cmd.Parameters.Add(complianceYearParameter);
cmd.Parameters.Add(obligationTypeParameter);
cmd.Parameters.Add(authorityParameter);
cmd.Parameters.Add(panAreaParameter);
await cmd.Connection.OpenAsync();
dataSet.Load(await cmd.ExecuteReaderAsync(), LoadOption.OverwriteChanges, sentOnData, addressData);
return(dataSet);
}
internal void RunCommand(string commandText, DataSet dataSet)
{
using (SqlCeCommand cmd = new SqlCeCommand())
{
cmd.CommandText = commandText;
cmd.Connection = cn;
CommandExecute execute = FindExecuteType(commandText);
if (execute != CommandExecute.Undefined)
{
if (execute == CommandExecute.DataTable)
{
string[] tables = new string[1];
tables[0] = "table" + dataSet.Tables.Count.ToString();
SqlCeDataReader rdr = cmd.ExecuteReader();
dataSet.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, tables);
dataSet.Tables[dataSet.Tables.Count - 1].MinimumCapacity = 0;
dataSet.Tables[dataSet.Tables.Count - 1].Locale = CultureInfo.InvariantCulture;
}
if (execute == CommandExecute.NonQuery || execute == CommandExecute.NonQuerySchemaChanged)
{
if (execute == CommandExecute.NonQuerySchemaChanged)
{
schemaHasChanged = true;
}
int rows = cmd.ExecuteNonQuery();
DataTable table = new DataTable();
table.MinimumCapacity = Math.Max(0, rows);
dataSet.Tables.Add(table);
}
}
}
}
public void Process()
{
// read the iris data from the resources
Assembly assembly = Assembly.GetExecutingAssembly();
var res = assembly.GetManifestResourceStream("AIFH_Vol1.Resources.abalone.csv");
// did we fail to read the resouce
if (res == null)
{
Console.WriteLine("Can't read iris data from embedded resources.");
return;
}
// load the data
var istream = new StreamReader(res);
DataSet ds = DataSet.Load(istream);
istream.Close();
// The following ranges are setup for the Abalone data set. If you wish to normalize other files you will
// need to modify the below function calls other files.
ds.EncodeOneOfN(0, 0, 1);
istream.Close();
var trainingData = ds.ExtractSupervised(0, 10, 10, 1);
var reg = new MultipleLinearRegression(10);
var train = new TrainLeastSquares(reg, trainingData);
train.Iteration();
Query(reg, trainingData);
Console.WriteLine("Error: " + train.Error);
}
/// <summary>
/// Executes the sql and returns all result sets.
/// </summary>
public static async Task <DataSet> QueryAsync(this IDbConnection conn, string sql, CommandType commandType, CommandBehavior commandBehavior, TimeSpan?commandTimeout, IDbTransaction transaction, CancellationToken cancellationToken, params DbParameter[] parameters)
{
if (conn == null)
{
throw new ArgumentNullException(nameof(conn));
}
if (sql == null)
{
throw new ArgumentNullException(nameof(sql));
}
var dataset = new DataSet {
EnforceConstraints = false
};
using (var cmd = CreateCommand(conn, sql, commandType, commandTimeout, transaction, parameters))
using (await conn.ConnectAsync(cancellationToken))
using (var reader = await cmd.ExecuteReaderAsync(commandBehavior, cancellationToken))
{
dataset.Load(reader);
reader.Close();
}
return(dataset);
}
private void InitDataSource( )
{
CommonUtils.ConexionBD.AbrirConexion( );
ChartTitle chartTitle = new ChartTitle( );
chartTitle.Text = "Productos por tipo";
chartControl.Titles.Add(chartTitle);
//chartControl.SeriesTemplate.ValueDataMembersSerializable = "Tipo";
string sql = "SELECT Categoria.Tipo, COUNT(Categoria.Tipo) AS Amount FROM Categoria INNER JOIN Producto ON Categoria.CategoriaId = Producto.CategoriaId " +
"GROUP BY Categoria.Tipo HAVING (Categoria.Tipo = Categoria.Tipo)";
//DataTable table = CommonUtils.ConexionBD.EjecutarConsulta( sql );
SqlCommand cmdProduct = new SqlCommand(sql, CommonUtils.ConexionBD.Conexion);
DataSet dataSet = new DataSet( );
dataSet.Load(cmdProduct.ExecuteReader( ), LoadOption.OverwriteChanges, "Producto");
BindingSource bs = new BindingSource( );
bs.DataSource = dataSet.Tables[0];
chartControl.DataSource = bs;
chartControl.SeriesDataMember = "Tipo";
chartControl.SeriesTemplate.ArgumentDataMember = "Amount";
//chartControl.SeriesTemplate.ValueDataMembersSerializable = "Amount";
chartControl.SeriesTemplate.ValueDataMembers.AddRange(new string[] { "Amount" });
CommonUtils.ConexionBD.CerrarConexion( );
}
/// <summary>
/// Loads a DataSet with the results of the specified command.
/// </summary>
/// <param name="command">Database command.</param>
/// <param name="tables">DataTable names.</param>
/// <param name="parameters">Database command parameters.</param>
/// <returns>Result set.</returns>
public DataSet ExecuteDataSet(SqlCommand command, string[] tables, List <SqlParameter> parameters)
{
DataSet dataSet = new DataSet();
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
using (SqlConnection connection = GetConnection())
{
RetryPolicy retryPolicy = GetRetryPolicy();
command.Connection = connection;
using (SqlDataReader reader = command.ExecuteReaderWithRetry(retryPolicy, retryPolicy))
{
dataSet.Load(reader, LoadOption.OverwriteChanges, tables);
}
}
// Clear parameters to avoid problems on subsequent calls.
command.Parameters.Clear();
return(dataSet);
}
public static DataSet SQLGetData(string ConnectionString, string commandString)
{
DataSet DS = new DataSet();
DataTable DT = new DataTable("Table1");
DS.Tables.Add(DT);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
try
{
connection.Open();
SqlCommand command = new SqlCommand(commandString, connection);
//command.CommandTimeout = 3000;
SqlDataReader read = command.ExecuteReader();
DS.Load(read, LoadOption.PreserveChanges, DS.Tables[0]);
}
catch (SqlException e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
finally
{
connection.Close();
}
}
return(DS);
}
public DataTable GetsbyDT(string sSQL)
{
DataSet oDataSet = new DataSet();
try
{
SqlCommand cmd = new SqlCommand("SP_ProductConfigure_GetsbyDT", _conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@PCID", SqlDbType.VarChar)).Value = sSQL;
if (_conn.State == ConnectionState.Open)
{
}
else
{
cmd.Connection.Open();
}
IDataReader reader = cmd.ExecuteReader();
oDataSet.Load(reader, LoadOption.OverwriteChanges, new string[1]);
reader.Close();
cmd.Dispose();
cmd.Connection.Close();
}
catch (Exception e)
{
throw new ServiceException(e.Message, e);
}
return(oDataSet.Tables[0]);
}
private DataSet GetDataSet(IDbConnectionFactory connectionFactory)
{
var sql = GetSql();
var dataSet = new DataSet("Activity");
var orgUnit = dataSet.Tables.Add("OrgUnit");
var jobAd = dataSet.Tables.Add("JobAd");
using (var connection = connectionFactory.CreateConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
command.CommandTimeout = DbCommandTimeout;
DatabaseHelper.AddParameter(command, "@startTime", DbType.DateTime, _dateRange.Start.Value.Date);
DatabaseHelper.AddParameter(command, "@endTime", DbType.DateTime, _dateRange.End.Value.Date.AddDays(1));
DatabaseHelper.AddParameter(command, "@orgUnitId", DbType.Guid, _report.ClientId);
DatabaseHelper.InlineParameters(command);
using (var reader = command.ExecuteReader())
{
dataSet.Load(reader, LoadOption.OverwriteChanges, orgUnit, jobAd);
}
}
}
dataSet.Relations.Add(orgUnit.Columns["id"], jobAd.Columns["organisationId"]);
return(dataSet);
}
/// <summary>
/// Envoie une requête
/// </summary>
/// <param name="strRequete">Requête à exécuter</param>
/// <param name="columnsToRetrieve">Champs à récupérer</param>
/// <returns>DataSet contenant les champs</returns>
public static DataSet query(String strRequete, params String[] columnsToRetrieve)
{
String strConn = String.Format("server={0}; user id={1}; password={2}; database={3}",
server,
dbuser,
dbpass,
database);
DataSet ds = new DataSet();
try
{
using (MySqlConnection conn = new MySqlConnection(strConn))
{
conn.Open();
MySqlCommand requete = new MySqlCommand();
requete.Connection = conn;
requete.CommandText = strRequete;
MySqlDataReader dr = requete.ExecuteReader();
ds.Load(dr, LoadOption.OverwriteChanges, columnsToRetrieve);
}
}
catch (Exception sarlon)
{ ds = null; }
return(ds);
}
// <Snippet1>
static void Main()
{
DataSet dataSet = new DataSet();
DataTable customerTable = new DataTable();
DataTable productTable = new DataTable();
// This information is cosmetic, only.
customerTable.TableName = "Customers";
productTable.TableName = "Products";
// Add the tables to the DataSet:
dataSet.Tables.Add(customerTable);
dataSet.Tables.Add(productTable);
// Load the data into the existing DataSet.
DataTableReader reader = GetReader();
dataSet.Load(reader, LoadOption.OverwriteChanges,
customerTable, productTable);
// Print out the contents of each table:
foreach (DataTable table in dataSet.Tables)
{
PrintColumns(table);
}
Console.WriteLine("Press any key to continue.");
Console.ReadKey();
}
public static DataSet ReportAssaysResult()
{
DataSet ds = new DataSet("ReportResult");
using (new TransactionScope(TransactionScopeOption.Suppress))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
//////Get Curves
cmd.CommandText = "[X_SP_GetAssaysByWorkflow]";
//cmd.CommandText = "[dbo].[X_SP_GetAssays]";
cmd.CommandType = CommandType.StoredProcedure;
var parm1 = cmd.CreateParameter();
parm1.ParameterName = "@assay_group_projectid";
parm1.DbType = DbType.Guid;
parm1.Value = new Guid("31B4CCEC-A72C-4F30-A13A-32B48762FDD9");
//cmd.Parameters.Add(parm1);
try
{
//Let's actually run the queries
cmd.Connection.Open();
cmd.CommandTimeout = 600; //10 mins
var reader = cmd.ExecuteReader();
ds.Load(reader, LoadOption.OverwriteChanges, "t");
}
finally
{
cmd.Connection.Close();
}
return(ds);
}
}
protected void Page_Load(object sender, EventArgs e)
{
string connStr = "Data Source=.\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;";
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand command = new SqlCommand("select * from customers", conn);
conn.Open();
DataSet ds = new DataSet();
ds.DataSetName = "Customers";
ds.Load(command.ExecuteReader(), LoadOption.OverwriteChanges, "Customer");
Response.ContentType = "text/xml";
ds.WriteXml(Response.OutputStream);
}
}
public DataSet GetDonationAmount()
{
DataSet ds = new DataSet();
string sql = "Select DonationDate, '$' + Cast(Cast(DonationAmount as decimal(8,2)) as nvarchar) as DonationAmount from Donation where PersonKey=@personKey";
SqlCommand cmd = new SqlCommand(sql, connect);
cmd.Parameters.AddWithValue("@personKey", pKey);
SqlDataReader reader = null;
connect.Open();
reader = cmd.ExecuteReader();
ds.Load(reader, LoadOption.OverwriteChanges, "Donations");
reader.Close();
connect.Close();
return ds;
}
public DataSet GetName()
{
DataSet ds = new DataSet();
string sql = "Select LastName, FirstName From Person Where personkey = @PersonKey";
SqlCommand cmd = new SqlCommand(sql, connect);
cmd.Parameters.AddWithValue("@PersonKey", pKey);
SqlDataReader reader = null;
connect.Open();
reader = cmd.ExecuteReader();
ds.Load(reader, LoadOption.OverwriteChanges, "Customer");
reader.Close();
connect.Close();
return ds;
}
public void Load_TableConflictT()
{
_fillErrCounter = 0;
_fillErr[0].init("Table1", 1, true,
"Input string was not in a correct format.Couldn't store <mono 1> in name1 Column. Expected type is Double.");
_fillErr[1].init("Table1", 2, true,
"Input string was not in a correct format.Couldn't store <mono 2> in name1 Column. Expected type is Double.");
_fillErr[2].init("Table1", 3, true,
"Input string was not in a correct format.Couldn't store <mono 3> in name1 Column. Expected type is Double.");
localSetup();
DataSet dsLoad = new DataSet("LoadTableConflict");
DataTable table1 = new DataTable();
table1.Columns.Add("name1", typeof(double));
dsLoad.Tables.Add(table1);
DataTable table2 = new DataTable();
dsLoad.Tables.Add(table2);
DataTableReader dtr = _ds.CreateDataReader();
dsLoad.Load(dtr, LoadOption.PreserveChanges,
fillErrorHandler, table1, table2);
}
public void Load_TableConflictF()
{
Assert.Throws<ArgumentException>(() =>
{
_fillErrCounter = 0;
_fillErr[0].init("Table1", 1, false,
"Input string was not in a correct format.Couldn't store <mono 1> in name1 Column. Expected type is Double.");
localSetup();
DataSet dsLoad = new DataSet("LoadTableConflict");
DataTable table1 = new DataTable();
table1.Columns.Add("name1", typeof(double));
dsLoad.Tables.Add(table1);
DataTable table2 = new DataTable();
dsLoad.Tables.Add(table2);
DataTableReader dtr = _ds.CreateDataReader();
dsLoad.Load(dtr, LoadOption.Upsert,
fillErrorHandler, table1, table2);
});
}
public void Load_StringsAsc()
{
localSetup();
DataSet dsLoad = new DataSet("LoadStrings");
DataTable table1 = new DataTable("First");
dsLoad.Tables.Add(table1);
DataTable table2 = new DataTable("Second");
dsLoad.Tables.Add(table2);
DataTableReader dtr = _ds.CreateDataReader();
dsLoad.Load(dtr, LoadOption.OverwriteChanges, "First", "Second");
CompareTables(dsLoad);
}
public void Load_StringsDesc()
{
localSetup();
DataSet dsLoad = new DataSet("LoadStrings");
DataTable table1 = new DataTable("First");
dsLoad.Tables.Add(table1);
DataTable table2 = new DataTable("Second");
dsLoad.Tables.Add(table2);
DataTableReader dtr = _ds.CreateDataReader();
dsLoad.Load(dtr, LoadOption.PreserveChanges, "Second", "First");
Assert.Equal(2, dsLoad.Tables.Count);
Assert.Equal(3, dsLoad.Tables[0].Rows.Count);
Assert.Equal(3, dsLoad.Tables[0].Columns.Count);
Assert.Equal(3, dsLoad.Tables[1].Rows.Count);
Assert.Equal(2, dsLoad.Tables[1].Columns.Count);
}
public void Load_StringsNew()
{
localSetup();
DataSet dsLoad = new DataSet("LoadStrings");
DataTable table1 = new DataTable("First");
dsLoad.Tables.Add(table1);
DataTable table2 = new DataTable("Second");
dsLoad.Tables.Add(table2);
DataTableReader dtr = _ds.CreateDataReader();
dsLoad.Load(dtr, LoadOption.Upsert, "Third", "Fourth");
Assert.Equal(4, dsLoad.Tables.Count);
Assert.Equal("First", dsLoad.Tables[0].TableName);
Assert.Equal(0, dsLoad.Tables[0].Rows.Count);
Assert.Equal(0, dsLoad.Tables[0].Columns.Count);
Assert.Equal("Second", dsLoad.Tables[1].TableName);
Assert.Equal(0, dsLoad.Tables[1].Rows.Count);
Assert.Equal(0, dsLoad.Tables[1].Columns.Count);
Assert.Equal("Third", dsLoad.Tables[2].TableName);
Assert.Equal(3, dsLoad.Tables[2].Rows.Count);
Assert.Equal(2, dsLoad.Tables[2].Columns.Count);
Assert.Equal("Fourth", dsLoad.Tables[3].TableName);
Assert.Equal(3, dsLoad.Tables[3].Rows.Count);
Assert.Equal(3, dsLoad.Tables[3].Columns.Count);
}
public void Load_StringsNewMerge()
{
localSetup();
DataSet dsLoad = new DataSet("LoadStrings");
DataTable table1 = new DataTable("First");
table1.Columns.Add("col1", typeof(string));
table1.Rows.Add(new object[] { "T1Row1" });
dsLoad.Tables.Add(table1);
DataTable table2 = new DataTable("Second");
table2.Columns.Add("col2", typeof(string));
table2.Rows.Add(new object[] { "T2Row1" });
table2.Rows.Add(new object[] { "T2Row2" });
dsLoad.Tables.Add(table2);
DataTableReader dtr = _ds.CreateDataReader();
dsLoad.Load(dtr, LoadOption.OverwriteChanges, "Third", "First");
Assert.Equal(3, dsLoad.Tables.Count);
Assert.Equal("First", dsLoad.Tables[0].TableName);
Assert.Equal(4, dsLoad.Tables[0].Rows.Count);
Assert.Equal(4, dsLoad.Tables[0].Columns.Count);
Assert.Equal("Second", dsLoad.Tables[1].TableName);
Assert.Equal(2, dsLoad.Tables[1].Rows.Count);
Assert.Equal(1, dsLoad.Tables[1].Columns.Count);
Assert.Equal("Third", dsLoad.Tables[2].TableName);
Assert.Equal(3, dsLoad.Tables[2].Rows.Count);
Assert.Equal(2, dsLoad.Tables[2].Columns.Count);
}
public void Load_Basic()
{
localSetup();
DataSet dsLoad = new DataSet("LoadBasic");
DataTable table1 = new DataTable();
dsLoad.Tables.Add(table1);
DataTable table2 = new DataTable();
dsLoad.Tables.Add(table2);
DataTableReader dtr = _ds.CreateDataReader();
dsLoad.Load(dtr, LoadOption.OverwriteChanges, table1, table2);
CompareTables(dsLoad);
}
public void LoadTest2()
{
DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();
DataTable dt1 = new DataTable("T1");
DataTable dt2 = new DataTable("T2");
DataTable dt3 = new DataTable("T1");
DataTable dt4 = new DataTable("T2");
dt1.Columns.Add("ID", typeof(int));
dt1.Columns.Add("Name", typeof(string));
dt2.Columns.Add("EmpNO", typeof(int));
dt2.Columns.Add("EmpName", typeof(string));
dt1.Rows.Add(new object[] { 1, "Andrews" });
dt1.Rows.Add(new object[] { 2, "Mathew" });
dt1.Rows.Add(new object[] { 3, "Jaccob" });
dt2.Rows.Add(new object[] { 1, "Arul" });
dt2.Rows.Add(new object[] { 2, "Jothi" });
dt2.Rows.Add(new object[] { 3, "Murugan" });
ds2.Tables.Add(dt1);
ds2.Tables.Add(dt2);
ds1.Tables.Add(dt3);
ds1.Tables.Add(dt4);
DataTableReader reader = ds2.CreateDataReader();
//ds1.Load (reader, LoadOption.PreserveChanges, dt3, dt4);
ds1.Load(reader, LoadOption.OverwriteChanges, dt3, dt4);
Assert.Equal(ds2.Tables.Count, ds1.Tables.Count);
int i = 0;
foreach (DataTable dt in ds1.Tables)
{
DataTable dt5 = ds2.Tables[i];
Assert.Equal(dt5.Rows.Count, dt.Rows.Count);
int j = 0;
DataRow row1;
foreach (DataRow row in dt.Rows)
{
row1 = dt5.Rows[j];
for (int k = 0; k < dt.Columns.Count; k++)
{
Assert.Equal(row1[k], row[k]);
}
j++;
}
i++;
}
}
public void Load_TableUnknown()
{
Assert.Throws<ArgumentException>(() =>
{
localSetup();
DataSet dsLoad = new DataSet("LoadTableUnknown");
DataTable table1 = new DataTable();
dsLoad.Tables.Add(table1);
DataTable table2 = new DataTable();
// table2 is not added to dsLoad [dsLoad.Tables.Add (table2);]
DataTableReader dtr = _ds.CreateDataReader();
dsLoad.Load(dtr, LoadOption.OverwriteChanges, table1, table2);
});
}
public static DataSet getLocationGroupById(int groupId)
{
string sql = "select location_groupid,groupname from location_group where location_groupid=?groupid; " +
" select location_groupid,locationid,locationtype, (select concat(c.name,':',CAST(c.countryid as char),',','0,0,0') from countries c where c.countryid =g.locationid and g.locationtype=1 union " +
" select concat(concat(c.name,' > ',l.name),':',CAST(c.countryid as char),',',cast(l.locationid as char),',', '0,0') from countries c join locations l on l.countryid=c.countryid where l.locationid=g.locationid and g.locationtype=2 union " +
" select concat(concat(c.name,' > ',l.name,' > ',s.sublocation),':',CAST(c.countryid as char),',',cast(l.locationid as char),',', cast(s.sublocationid as char),',','0') from countries c join locations l on l.countryid=c.countryid join locationsub s on s.locationid=l.locationid " +
" where s.sublocationid=g.locationid and g.locationtype=3 union " +
" select concat(concat(c.name,' > ',l.name,' > ',s.sublocation,' > ',ss.name),':',CAST(c.countryid as char),',',cast(l.locationid as char),',', cast(s.sublocationid as char),',',cast(ss.subsublocationid as char) ) from countries c join locations l on l.countryid=c.countryid " +
" join locationsub s on s.locationid=l.locationid join locationsub_subs ss on ss.sublocationid=s.sublocationid where ss.subsublocationid=g.locationid and g.locationtype=4 ) as parentids " +
" from location_groupdetails g where location_groupid=?groupid order by parentids asc; ";
MySqlDataReader dr = DAO.ExecuteReader(sql, new MySqlParameter("groupid", groupId));
DataSet ds = new DataSet();
ds.EnforceConstraints = false;
ds.Load(dr, LoadOption.PreserveChanges, new string[2]);
dr.Close();
dr.Dispose();
return ds;
}
