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