Load() public method

public Load ( IDataReader reader, LoadOption loadOption ) : void
reader IDataReader
loadOption LoadOption
return void
Exemplo n.º 1
0
        protected DataSet.DSParameter baseUpdate(DataSet.DSParameter ds, string tableName)
        {
            if (_is_Single_Transaction)
            {
                try
                {
                    _base.BeginTransaction();
                    _base.SetConnection();
                    _db = _base.GetDatabase();
                    ds.Load(_base.Update(ds), LoadOption.OverwriteChanges, tableName);
                    _base.CommitTransaction();
                }
                catch (Exception exp)
                {
                    _base.RollBackTransaction();
                    throw exp;
                }
                finally
                {

                }
                return ds;
            }


            _base.SetConnection();
            _db = _base.GetDatabase();
            ds.Load(_base.Update(ds), LoadOption.OverwriteChanges, tableName);
            return ds;
        }
Exemplo n.º 2
0
        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( );
        }
Exemplo n.º 3
0
        /// <summary>
        /// Exports the Gallery Server Pro data in the current database to an XML-formatted string.
        /// </summary>
        /// <param name="exportMembershipData">if set to <c>true</c> export membership data.</param>
        /// <param name="exportGalleryData">if set to <c>true</c> export gallery data.</param>
        /// <returns>Returns an XML-formatted string containing the gallery data.</returns>
        internal static string ExportData(bool exportMembershipData, bool exportGalleryData)
        {
            using (DataSet ds = new DataSet("GalleryServerData"))
            {
                System.Reflection.Assembly asm = System.Reflection.Assembly.GetExecutingAssembly();
                using (System.IO.Stream stream = asm.GetManifestResourceStream("GalleryServerPro.Data.SqlCe.GalleryServerProSchema.xml"))
                {
                    ds.ReadXmlSchema(stream);
                }

                using (SqlCeConnection cn = Util.GetDbConnectionForGallery())
                {
                    using (SqlCeCommand cmd = cn.CreateCommand())
                    {
                        cn.Open();

                        if (exportMembershipData)
                        {
                            foreach (string tableName in _schemaMembershipTableNames)
                            {
                                cmd.CommandText = String.Concat(@"SELECT * FROM ", tableName, ";");
                                ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, tableName);
                            }
                        }

                        if (exportGalleryData)
                        {
                            foreach (string tableName in _galleryTableNames)
                            {
                                cmd.CommandText = String.Concat(@"SELECT * FROM ", tableName, ";");
                                ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, tableName);
                            }
                        }

                        // We always want to get the schema into the dataset, even when we're not getting the rest of the gallery data.
                        cmd.CommandText = @"SELECT SettingValue AS SchemaVersion FROM gs_AppSetting WHERE SettingName='DataSchemaVersion';";
                        ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, "gs_SchemaVersion");

                        using (System.IO.StringWriter sw = new System.IO.StringWriter(CultureInfo.InvariantCulture))
                        {
                            ds.WriteXml(sw, XmlWriteMode.WriteSchema);
                            //ds.WriteXmlSchema(@"D:\gsp_schema.xml"); // Use to create new schema file after a database change

                            return sw.ToString();
                        }
                    }
                }
            }
        }
Exemplo n.º 4
0
        private void InitializeGrid( )
        {
            CommonUtils.ConexionBD.AbrirConexion( );

            string sqlUsuarios = "SELECT UsuarioId, Nombre, Apellidos, Login, Password, Telefono, PrivilegiosXML " +
                "From UsuariosSistema ";

            try
            {
                SqlCommand cmdProduct = new SqlCommand( sqlUsuarios , CommonUtils.ConexionBD.Conexion );

                DataSet dataSet = new DataSet( );
                dataSet.Load( cmdProduct.ExecuteReader( ) , LoadOption.OverwriteChanges , "Usuarios" );

                gridControlUsuarios.DataSource = dataSet.Tables[ "Usuarios" ];
               
                MessageBarValue = dataSet.Tables[ "Usuarios" ].Rows.Count.ToString( ) + " Usuarios";
            }
            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( );
        }
Exemplo n.º 5
0
            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;
                }
            }
        public static DataSet ExecuteDatasetTEST(string strConnection, string commandText)
        {
            if (strConnection == null) throw new ArgumentNullException("connection");

            // Create a command and prepare it for execution
            DbConnection conn = new TAdoDbxInterBaseConnection();
            conn.ConnectionString = strConnection;

            DataSet dsReturnin = new DataSet();
            DataTable dt = new DataTable();
            string sql = commandText;
          

                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                conn.Open();

                DbDataReader myreader = cmd.ExecuteReader();
                dsReturnin.Tables.Add(dt);
                dsReturnin.Load(myreader, LoadOption.PreserveChanges, dsReturnin.Tables[0]);
                myreader.Close();
                conn.Close();
                conn.Dispose();
         

            return dsReturnin;
        }
Exemplo n.º 7
0
        protected void UploadAs_Click(object sender, EventArgs e)
        {
            if (DataSetName.Text.Trim().Length != 0 && DataSetUpload.HasFile)
            {
                //move most to uploader
                //handle spaces in file name
                String uploadPath = System.IO.Path.Combine(System.IO.Path.GetTempPath().ToString(), DataSetUpload.FileName);
                DataSetUpload.SaveAs(uploadPath);
                String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.GetTempPath().ToString() + ";Extended Properties='text;HDR=Yes;FMT=Delimited'";
                connection = new OleDbConnection(connectionString);
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM " + DataSetUpload.FileName, connection);
                da = new OleDbDataAdapter(cmd);
                connection.Open();
                dt = new System.Data.DataTable();
                da.Fill(dt);
                System.Data.DataSet ds         = new System.Data.DataSet(DataSetName.Text.Trim());
                String[]            parameters = { "main" };
                ds.Load(dt.CreateDataReader(), System.Data.LoadOption.OverwriteChanges, parameters);

                Session.Add("table", dt);
                Session.Add("connection", connection);
                Session.Add("adapter", da);

                Registry.Registry registry = Registry.Registry.getRegistry(Session);
                registry.registerDataset(ds);
                DatasetList.Items.Add(ds.DataSetName);
            }
        }
Exemplo n.º 8
0
        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( );
        }
Exemplo n.º 9
0
        /// <summary>Executes a SQL Stored Procedure with 1 parameter, and returns a DataSet.</summary>
        /// <param name="StoredProcName">The actual name of the Stored Procedure on the SQL Server</param>
        /// <param name="ParamValue">The Valure being passed to the Stored Procedure.</param>
        /// <param name="ParamName">The actual name of the Paramter in the Stored Procedure.</param>
        /// <returns>DataSet</returns>
        public DataSet SelectQuery(string StoredProcName, object ParamValue, string ParamName)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            DataSet ds1 = new DataSet();

            // create and open a connection object
            //conn = new SqlConnection(@"Data Source=NEWDUCK\SQLEXPRESSR2;Initial Catalog=ARC_ORG;Integrated Security=True");             
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Home_ARC_ORGConnectionString"].ToString());
            conn.Open();

            // 1.  create a command object identifying
            //     the stored procedure
            SqlCommand cmd = new SqlCommand(StoredProcName, conn);

            // 2. set the command object so it knows
            //    to execute a stored procedure
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter(ParamName, ParamValue));
            // execute the command            
            //SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            //adapter.Fill(ds1);

            // iterate through results, printing each to console
            rdr = cmd.ExecuteReader();
            ds1.Load(rdr, LoadOption.OverwriteChanges, "eNewLetter");
            //while (rdr.Read())
            //{

            //}  
            conn.Close();
            return ds1;
        }
Exemplo n.º 10
0
        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;
                 }
              }
           }
        }
Exemplo n.º 11
0
		/// <summary>
		/// Exports the data from the SQL Server database to <paramref name="filePath"/>.
		/// </summary>
		/// <param name="filePath">The file name (including the path) to which to write.</param>
		/// <param name="exportMembershipData">if set to <c>true</c> export membership data.</param>
		/// <param name="exportGalleryData">if set to <c>true</c> export gallery data.</param>
		internal static void ExportData(string filePath, bool exportMembershipData, bool exportGalleryData)
		{
			DataSet ds = new DataSet("GalleryServerData");

			System.Reflection.Assembly asm = System.Reflection.Assembly.GetExecutingAssembly();
			using (System.IO.Stream stream = asm.GetManifestResourceStream("GalleryServerPro.Data.SqlServer.GalleryServerProSchema.xml"))
			{
				ds.ReadXmlSchema(stream);
			}

			using (SqlConnection cn = SqlDataProvider.GetDbConnection())
			{
				if (cn.State == ConnectionState.Closed)
					cn.Open();

				if (exportMembershipData)
				{
					string[] aspnet_TableNames = new string[] { "aspnet_Applications", "aspnet_Membership", "aspnet_Profile", "aspnet_Roles", "aspnet_Users", "aspnet_UsersInRoles" };
					using (SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_ExportMembership"), cn))
					{
						cmd.CommandType = CommandType.StoredProcedure;

						using (IDataReader dr = cmd.ExecuteReader())
						{
							ds.Load(dr, LoadOption.OverwriteChanges, aspnet_TableNames);
						}
					}
				}

				if (exportGalleryData)
				{
					string[] gs_TableNames = new string[] { "gs_Album", "gs_Gallery", "gs_MediaObject", "gs_MediaObjectMetadata", "gs_Role", "gs_Role_Album", "gs_AppError", "gs_SchemaVersion" };
					using (SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_ExportGalleryData"), cn))
					{
						cmd.CommandType = CommandType.StoredProcedure;

						using (IDataReader dr = cmd.ExecuteReader())
						{
							ds.Load(dr, LoadOption.OverwriteChanges, gs_TableNames);
						}
					}

					ds.WriteXml(filePath, XmlWriteMode.WriteSchema);
					//ds.WriteXmlSchema(filePath);
				}
			}
		}
Exemplo n.º 12
0
        protected DataSet.DS baseUpdate(DataSet.DS ds, string tableName)
        {
            _base.SetConnection();
            _db = _base.GetDatabase();
            ds.Load(_base.Update(ds), LoadOption.OverwriteChanges, tableName);
            return ds;


        }
Exemplo n.º 13
0
        /// <summary>
        /// Excel导入Access
        /// </summary>
        public void Excel2Access(string[] filenames)
        {
            try
            {
                for (int j = 0; j < filenames.Length; j++)
                {
                    OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
                    connectStringBuilder.DataSource = filenames[j];
                    connectStringBuilder.Provider = "Microsoft.Jet.Oledb.4.0";
                    connectStringBuilder.Add("Extended Properties", "Excel 8.0");
                    using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
                    {
                        DataSet ds = new DataSet();
                        string sql = string.Format("select * from [{0}$]", sheetName);
                        OleDbCommand cmdLiming = new OleDbCommand(sql, cn);
                        cn.Open();
                        using (OleDbDataReader dr = cmdLiming.ExecuteReader())
                        {
                            ds.Load(dr, LoadOption.OverwriteChanges, new string[] { sheetName });
                            DataTable dt = ds.Tables[sheetName];
                            if (dt.Rows.Count > 0)
                            {
                                for (int i = 4; i < dt.Rows.Count; i++)
                                {

                                    //写入数据库数据
                                    if (!(dt.Rows[i][0].ToString() == "" || string.IsNullOrEmpty(dt.Rows[i][0].ToString())))
                                    {
                                        string MySql = "insert into TB_Car (PlateNumber,Color,Brand,UseYear,MotorcyleType) values('" + dt.Rows[i][1].ToString() + "','" +
                                            dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" +
                                            dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "')";
                                        AccessHelper.SQLExecute(MySql);
                                    }

                                }
                                MessageBox.Show("数据导入成功!");

                                CarBLL carBLL = new CarBLL();
                                DataTable carList = carBLL.FindAll_infos(getblacklistsql);

                                dgvCarList.DataSource = carList;

                            }
                            else
                            {
                                MessageBox.Show("请检查你的Excel中是否存在数据");
                            }
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
Exemplo n.º 14
0
        public static DataSet DataSetFromSql(string sql, params string[] tableNames)
        {
            var dataSet = new DataSet();

            using (var reader = DataReaderFromSql(sql))
                dataSet.Load(reader, LoadOption.OverwriteChanges, tableNames);

            return dataSet;
        }
Exemplo n.º 15
0
        /// <summary>
        /// Excel导入Access
        /// </summary>
        public void Excel2Access(string[] filenames)
        {
            try
            {
                for (int j = 0; j < filenames.Length; j++)
                {
                    OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
                    connectStringBuilder.DataSource = filenames[j];
                    connectStringBuilder.Provider = "Microsoft.Jet.Oledb.4.0";
                    connectStringBuilder.Add("Extended Properties", "Excel 8.0");
                    using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
                    {
                        DataSet ds = new DataSet();
                        string sql = string.Format("select * from [{0}$]", sheetName);
                        OleDbCommand cmdLiming = new OleDbCommand(sql, cn);
                        cn.Open();
                        using (OleDbDataReader dr = cmdLiming.ExecuteReader())
                        {
                            ds.Load(dr, LoadOption.OverwriteChanges, new string[] { sheetName });
                            DataTable dt = ds.Tables[sheetName];
                            if (dt.Rows.Count > 0)
                            {
                                for (int i = 4; i < dt.Rows.Count; i++)
                                {

                                    //写入数据库数据
                                    if (!(dt.Rows[i][0].ToString() == "" || string.IsNullOrEmpty(dt.Rows[i][0].ToString())))
                                    {
                                        string MySql = "insert into TB_BlacakList (User_ChineseName,User_EnglishName,Department_ChineseName,Department_EnglishName,Job,Sex,Birthdy,Identification_Type,Identification_Number,Employer,TELEPHONE) values('" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')";
                                        AccessHelper.SQLExecute(MySql);
                                    }

                                }
                                MessageBox.Show("数据导入成功!");
                                string getblacklistsql = "select ID ,User_ChineseName as 中文,User_EnglishName as 英文,Department_ChineseName as 单位名称中文,Department_EnglishName as 单位名称英文,Job as 职务,Sex as 性别,Birthdy as 出生日期,Identification_Type as 身份证件类型,Identification_Number as 身份证件号码,Employer as 工作单位,TELEPHONE as 联系方式 from TB_BlacakList ";
                                DriverListBLL driverListBll = new DriverListBLL();
                                DataTable dtDriverList = driverListBll.FindAll_infos(getblacklistsql);

                                dgvBlackList.DataSource = dtDriverList;
                                dgvBlackList.Rows[0].Selected = false;

                            }
                            else
                            {
                                MessageBox.Show("请检查你的Excel中是否存在数据");
                            }
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
Exemplo n.º 16
0
        protected System.Data.DataSet baseGetList()
        {
            System.Data.DataSet ds = new System.Data.DataSet();
            _base.SetConnection();
            //_db = _base.GetDatabase();
            ds.Tables.Add(_listTable);
            ds.Load(_base.GetList(), LoadOption.OverwriteChanges, _listTable);
            return ds;

        }
Exemplo n.º 17
0
        ///<summary>
        /// Deserialize a protocol-buffer binary stream as a <see cref="System.Data.DataSet"/>.
        ///</summary>
        ///<param name="stream">The <see cref="System.IO.Stream"/> to read from.</param>
        ///<param name="tables">An array of strings, from which the <see cref="System.Data.DataSet"/> Load method retrieves table name information.</param>
        public DataSet DeserializeDataSet(Stream stream, params string[] tables)
        {
            if (stream == null) throw new ArgumentNullException("stream");
            if (tables == null) throw new ArgumentNullException("tables");

            var dataSet = new DataSet();
            using (var reader = Deserialize(stream))
                dataSet.Load(reader, LoadOption.OverwriteChanges, tables);

            return dataSet;
        }
        /// <summary>
        /// Used to get the user
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public static DataSet GetUser(string query)
        {
            DataSet dataset = new DataSet();
            SqlConnection con = connection();
            SqlCommand cmd = new SqlCommand(query, con);

            cmd.CommandType = CommandType.Text;
            var dr = cmd.ExecuteReader();
            dataset.Load(dr, LoadOption.OverwriteChanges, "User");
            return dataset;
        }
Exemplo n.º 19
0
        public System.Data.DataSet GetEventList(string zipcode, int? distance,
        ref decimal startLatitude, ref decimal startLongitude, ref bool is_Valid_Postal_Code, string start_Date, string end_Date)
        {



            DataAccessLayer.Parameter.ZipCodes obj2 = new DataAccessLayer.Parameter.ZipCodes();
            obj2.SetConnection();
            _db = obj2.GetDatabase();
            obj2._zipcodes_ID = zipcode;

            DataSet.DSParameter ds = new DataSet.DSParameter();
            ds.Load(obj2.GetItem(), LoadOption.OverwriteChanges, ds.ZipCodes.TableName);

            System.Data.DataSet __ds = new System.Data.DataSet();

            if (ds.ZipCodes.Count == 0)
            {
                __ds.Tables.Add("List");
                is_Valid_Postal_Code = false;
                return __ds;
            }


            startLatitude = ds.ZipCodes[0].Latitude;
            startLongitude = ds.ZipCodes[0].Longitude;






            //_is_Single_Transaction = false;
            //bool is_Valid = false;
            try
            {
                DataAccessLayer.Search.Event obj = new DataAccessLayer.Search.Event();
                obj.SetConnection();
                _db = obj.GetDatabase();
                IDataReader dr = obj.GetEventList(startLatitude, startLongitude, distance, start_Date, end_Date);
                __ds.Load(dr, LoadOption.OverwriteChanges, "List");

            }
            catch
            {
                //_base.RollBackTransaction();
                //throw;
            }

            is_Valid_Postal_Code = true;
            return __ds;
        }
        public override void Run(object context)
        {
            DbController dbc = new DbController(Connection);
            DataSet ds = new DataSet();

            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("SELECT * FROM {0} ", TableName.PRODUCT.ToString());
            sql.AppendFormat("SELECT * FROM {0} ", TableName.CATEGORY.ToString());
            sql.AppendFormat("SELECT * FROM {0} ", TableName.SUPPLIER.ToString());

            DataTable[] tables = new DataTable[]{ 
                new DataTable(TableName.PRODUCT.ToString()),
                new DataTable(TableName.CATEGORY.ToString()),
                new DataTable(TableName.SUPPLIER.ToString())};
            
            try
            {
                ds.Tables.Add(tables[0]);
                ds.Tables.Add(tables[1]);
                ds.Tables.Add(tables[2]);

                Command.CommandText = sql.ToString();
                DataReader = Command.ExecuteReader();
                
                ds.Load(DataReader, LoadOption.OverwriteChanges, tables);

                if (ds.Tables[TableName.PRODUCT.ToString()].Rows.Count != dbc.GetProductCount())
                    Fail("DataSet failed to load all rows in Product table");
                if (ds.Tables[TableName.CATEGORY.ToString()].Rows.Count != dbc.GetCategoryCount())
                    Fail("DataSet failed to load all rows in Category table");
                if (ds.Tables[TableName.SUPPLIER.ToString()].Rows.Count != dbc.GetSupplierCount())
                    Fail("DataSet failed to load all rows in Supplier table");                
            }
            catch (Exception e)
            {
                Fail(e);
            }
            finally
            {
                try
                {                    
                    Command.Close();
                }
                catch (Exception e)
                {
                    Fail(e);
                }

                base.Run(context);
            }
        }        
Exemplo n.º 21
0
        public override DataTable GetInstance()
        {
            var connection = new SqlConnection(ConnectionString);
            var command = new SqlCommand(string.Format("SELECT * FROM {0}", TableName), connection);
            connection.Open();

            var dataSet = new DataSet();
            var table = dataSet.Tables.Add(TableName);
            dataSet.Load(command.ExecuteReader(), LoadOption.OverwriteChanges, new[] { table });

            connection.Close();

            table.PrimaryKey = table.Columns.Cast<DataColumn>().Where(currColumn => IdentityColumns.Contains(currColumn.ColumnName)).ToArray();
            return table;
        }
Exemplo n.º 22
0
        public static bool DatabaseIsValid(string connectionString, Verbosity verbosity, bool executeObjects)
        {
            bool rc = true;
            int objectCount = 0;
            int invalidObjectCount = 0;

            using (SqlConnection db = new SqlConnection(connectionString))
            using (SqlCommand cmd = new SqlCommand(GetProcedureListSql(db, verbosity), db))
            {
                if (db.State != ConnectionState.Open) db.Open();
                using (DataSet procReader = new DataSet())
                {
                    procReader.Locale = CultureInfo.CurrentCulture;
                    procReader.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, "main");

                    bool succeeded = false;
                    //for each object get the command text and execute the command
                    foreach (DataRow dr in procReader.Tables[0].Rows)
                    {
                        string name = (string)dr["Name"];
                        string quotedIdent = (int)dr["quoted_ident_on"] == 1 ? "ON" : "OFF";
                        string ansiNulls = (int)dr["ansi_nulls_on"] == 1 ? "ON" : "OFF";
                        string schema = (string)dr["schema"];

                        if (verbosity == Verbosity.Verbose) Console.WriteLine("Processing {0}.{1}", schema, name);

                        string text = GetProcedureText(db, schema, name);
                        if (!string.IsNullOrEmpty(text))
                        {
                            succeeded = TryCompile(db, schema, name, quotedIdent, ansiNulls, text);
                            if (succeeded && executeObjects && dr["type"] as string != "TR")
                                succeeded = ExecuteIfSafe(db, schema, name, dr["type"] as string, text);
                        }

                        if (!succeeded && !string.IsNullOrEmpty(text)) invalidObjectCount++;
                        objectCount++;
                    }

                    if (verbosity != Verbosity.Quiet && verbosity != Verbosity.None)
                        Console.WriteLine("\nProcessing complete.  Objects processed: {0}\tInvalid objects found: {1}", objectCount, invalidObjectCount);
                }
                db.Close();
            }
            if (invalidObjectCount > 0) rc = false;
            return rc;
        }
Exemplo n.º 23
0
        public static DataSet ExecuteQuery(string cmd, string connectstring, params SqlParameter[] parameters)
        {
            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(connectstring))
            {

                SqlCommand command = new SqlCommand(cmd, connection);
                command.Parameters.AddRange(parameters);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                ds.Load(reader, LoadOption.OverwriteChanges, new string[] { "" });
                reader.Close();
            }

            return ds;
        }
Exemplo n.º 24
0
        public GridProductos( string sql )
        {
            InitializeComponent( );
            InitConexionDB( );

            // TODO: Complete member initialization
            this.sqlQuery = sql;

            CommonUtils.ConexionBD.AbrirConexion( );
           
            gridControl.MainView = cardView;
            SqlCommand cmdProduct = new SqlCommand( this.sqlQuery , CommonUtils.ConexionBD.Conexion );
            DataSet dataSet = new DataSet( );
            dataSet.Load( cmdProduct.ExecuteReader( ) , LoadOption.OverwriteChanges , "Producto" );
           
            gridControl.DataSource = dataSet.Tables[ "Producto" ];

            ColumnView view = this.gridControl.MainView as ColumnView;
            view.Columns.Add( );
            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( );

            /*
             * Make just the Default columns visible. Imagen and Precio de Venta.
             */
            view.Columns[0].Visible = false;
            view.Columns[2].Visible = false;
            view.Columns[3].Visible = false;
            view.Columns[4].Visible = false;
            view.Columns[5].Visible = false;
            view.Columns[6].Visible = false;
            view.Columns[8].Visible = false;
            /*
             * The user can make them visible if needed.
             */
            this.gridControl.RepositoryItems.Add( view.Columns[ 1 ].ColumnEdit );

            CommonUtils.ConexionBD.CerrarConexion( );
        }
Exemplo n.º 25
0
        /// <summary> Executes a SQL Stored Procedure with no parameters, and returns a DataSet.</summary>
        /// <param name="StoredProcName">The actual name of the Stored Prcedure.</param>
        /// <returns>DataSet</returns>
        public DataSet SelectQuery(string StoredProcName)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            DataSet ds1 = new DataSet();

            // create and open a connection object             
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Home_ARC_ORGConnectionString"].ToString());
            conn.Open();

            // 1.  create a command object identifying
            //     the stored procedure
            SqlCommand cmd = new SqlCommand(StoredProcName, conn);

            // 2. set the command object so it knows
            //    to execute a stored procedure
            cmd.CommandType = CommandType.StoredProcedure;
            // execute the command            
            rdr = cmd.ExecuteReader();
            ds1.Load(rdr, LoadOption.OverwriteChanges, "eNewLetter");
            conn.Close();
            return ds1;
        }
Exemplo n.º 26
0
 public DataSet GetUserList(int pageIndex, int pageSize, string orderBy, Dictionary<string, object> parameters, out int total)
 {
     if(string.IsNullOrEmpty(orderBy))
     {
         orderBy="guid";
     }
     using (ISession session = sessionFactory.OpenSession())
     {
         IDbCommand cmd = session.Connection.CreateCommand();
         string table = "select * from [user]";
         string where = setWhereParameters(cmd, parameters);
         string sql = SQLHelper.CreatPageSelectSQL(pageIndex, pageSize, table, where, orderBy);
         cmd.CommandText = sql;
         cmd.CommandType = CommandType.Text;
         cmd.Connection = session.Connection;
         DataSet dataset = new DataSet();
         dataset.Load(cmd.ExecuteReader(), LoadOption.Upsert, new string[] { "User" });
         string getCountSQL = SQLHelper.GetTotalCountSQL(table, where);
         cmd.CommandText = getCountSQL;
         total = (int)cmd.ExecuteScalar();
         return dataset;
     }
 }
Exemplo n.º 27
0
        private void InitializeGrid( )
        {
            CommonUtils.ConexionBD.AbrirConexion( );

            string sqlDosificacion = "SELECT dosificacion_llave AS [Llave], dosificacion_nro_autorizacion as [Nro de Autorizacion], dosificacion_ultimo_valor AS [Nro de Factura], " +
                " dosificacion_fechalimite AS [Fecha Limite de Emision], dosificacion_activa AS [Activa], dosificacion_id " +
                "From dosificacion ";

            try
            {
                SqlCommand cmdDosificacion = new SqlCommand( sqlDosificacion , CommonUtils.ConexionBD.Conexion );

                DataSet dataSet = new DataSet( );
                dataSet.Load( cmdDosificacion.ExecuteReader( ) , LoadOption.OverwriteChanges , "Dosificacion" );


                gridDosificacion.DataSource = dataSet.Tables[ "Dosificacion" ];

                ColumnView view = this.gridDosificacion.MainView as ColumnView;
                view.Columns.Add( );
                view.Columns[ 5 ].Visible = false;
                this.gridDosificacion.RepositoryItems.Add( view.Columns[ 1 ].ColumnEdit );

                MessageBarValue = dataSet.Tables[ "Dosificacion" ].Rows.Count.ToString( ) + " Dosificacion";
            }
            catch ( Exception ex )
            {
                log.Error( ex.Message , ex );
                MessageBarValue = "No se pudieron listar las dosificaciones. Hubo un error: " + ex.Message;
            }
            finally
            {
                barItem.Text = MessageBarValue;
            }

            CommonUtils.ConexionBD.CerrarConexion( );
        }
Exemplo n.º 28
0
 /// <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;
 }
        /// <summary>
        /// This method is used to retrieve the current data stored for a Entity in the database. This is used by the runtime to 
        /// detect the current values for a row that is being skipped due to data errors.
        /// </summary>
        /// <param name="entities">Entities whose current server version is required</param>
        /// <param name="connection">SqlConnection to use for reading from the database</param>
        /// <param name="transaction">SqlTransaction to use when reading from the database</param>
        /// <returns>Server version or null if entity doesnt exist in database.</returns>
        internal List<IOfflineEntity> GetCurrentServerVersionForEntities(IEnumerable<IOfflineEntity> entities, SqlConnection connection, SqlTransaction transaction)
        {
            var serverVersions = new List<IOfflineEntity>();

            foreach (IOfflineEntity entity in entities)
            {
                // Craft the command for retrieving the row.
                SqlCommand command = new SqlCommand(_converter.GetSelectScriptForType(entity.GetType()), connection, transaction);

                int index = 1;
                // Set parameters
                foreach (PropertyInfo pinfo in ReflectionUtility.GetPrimaryKeysPropertyInfoMapping(entity.GetType())
                    )
                {
                    SqlParameter p = new SqlParameter("@p" + index, pinfo.GetValue(entity, null));
                    index++;
                    command.Parameters.Add(p);
                }

                DataSet ds = new DataSet();
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))
                {
                    if (reader.HasRows)
                    {
                        ds.Load(reader, LoadOption.OverwriteChanges, new string[] {_converter.GetTableNameForType(entity.GetType())});

                        serverVersions.Add(_converter.ConvertDataSetToEntities(ds).First());
                    }
                    else
                    {
                        serverVersions.Add(null);
                    }
                }
            }

            return serverVersions;
        }
Exemplo n.º 30
0
        /// <summary>
        /// Executes the query.
        /// </summary>
        /// <param name="dataSet">The data set to return containing the data.</param>
        /// <param name="tables">The datatable schema to add.</param>
        /// <param name="queryText">The query text to execute.</param>
        /// <param name="commandType">The command type.</param>
        /// <param name="connectionString">The connection string to use.</param>
        /// <param name="values">The collection of sql parameters to include.</param>
        /// <returns>The sql command containing any return values.</returns>
        public DbCommand ExecuteQuery(ref System.Data.DataSet dataSet, DataTable[] tables, string queryText,
                                      CommandType commandType, string connectionString, params DbParameter[] values)
        {
            // Initial connection objects.
            DbCommand     dbCommand     = null;
            SqlConnection sqlConnection = null;
            IDataReader   dataReader    = null;

            try
            {
                // Create a new connection.
                using (sqlConnection = new SqlConnection(connectionString))
                {
                    // Open the connection.
                    sqlConnection.Open();

                    // Create the command and assign any parameters.
                    dbCommand = new SqlCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
                                                   ConnectionContext.ConnectionDataType.SqlDataType, queryText), sqlConnection);
                    dbCommand.CommandType = commandType;

                    if (values != null)
                    {
                        foreach (SqlParameter sqlParameter in values)
                        {
                            dbCommand.Parameters.Add(sqlParameter);
                        }
                    }

                    // Load the data into the table.
                    using (dataReader = dbCommand.ExecuteReader())
                    {
                        dataSet = new System.Data.DataSet();
                        dataSet.Tables.AddRange(tables);
                        dataSet.EnforceConstraints = false;
                        dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
                        dataReader.Close();
                    }

                    // Close the database connection.
                    sqlConnection.Close();
                }

                // Return the sql command, including
                // any parameters that have been
                // marked as output direction.
                return(dbCommand);
            }
            catch (Exception ex)
            {
                // Throw a general exception.
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (dataReader != null)
                {
                    dataReader.Close();
                }

                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
            }
        }
Exemplo n.º 31
0
        public void ReaderExecuted(DbCommand command,
                                   DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            _stopwatch.Stop();

            DataTable dataTable = null;

            if (interceptionContext.OriginalException == null)
            {
                var dataSet = new DataSet { EnforceConstraints = false };
                dataTable = new DataTable(tableName: Guid.NewGuid().ToString());
                dataSet.Tables.Add(dataTable);
                dataSet.Load(interceptionContext.OriginalResult, LoadOption.OverwriteChanges, dataTable);

                interceptionContext.Result = dataTable.CreateDataReader();
            }

            var context = EFProfilerContextProvider.GetLoggedResult(command, interceptionContext, _stopwatch.ElapsedMilliseconds, dataTable);
            _profiler.ReaderExecuted(command, context);
        }
        public static DataTable getMailTemplateByParentId(int parentId)
        {
            EmailTemplateInfo Info = new EmailTemplateInfo();
            string sql = "Select email_templateid as id,subject,body,status,header,footer,version,date_format(createddate,'%d-%b-%Y-%T') as createddate,username from email_template e inner join users u on e.userid=u.userid where parentid = ?parentid";
            MySqlDataReader dr = DAO.ExecuteReader(sql, new MySqlParameter("parentid", parentId));
            DataSet ds = new DataSet();
            ds.EnforceConstraints = false;
            DataTable dt = new DataTable();

            ds.Load(dr, LoadOption.PreserveChanges, new string[1]);
            dt = ds.Tables[0];
            dr.Close();
            dr.Dispose();
            return dt;
        }
Exemplo n.º 33
0
        /// <summary>
        /// Executes the query.
        /// </summary>
        /// <param name="dataSet">The data set to return containing the data.</param>
        /// <param name="tables">The data tables names to return.</param>
        /// <param name="queryText">The query text to execute.</param>
        /// <param name="commandType">The command type.</param>
        /// <param name="connectionString">The connection string to use.</param>
        /// <param name="values">The collection of sql parameters to include.</param>
        /// <returns>The sql command containing any return values.</returns>
        public DbCommand ExecuteClientQuery(ref System.Data.DataSet dataSet, string[] tables, string queryText,
                                            CommandType commandType, string connectionString, params DbParameter[] values)
        {
            // Initial connection objects.
            OracleClient.OracleCommand    sqlCommand = null;
            OracleClient.OracleConnection connection = null;
            IDataReader dataReader = null;

            try
            {
                // Create a new connection.
                using (connection = new OracleClient.OracleConnection(connectionString))
                {
                    // Open the connection.
                    connection.Open();

                    // Create the command and assign any parameters.
                    sqlCommand             = new OracleClient.OracleCommand(queryText, connection);
                    sqlCommand.CommandType = commandType;

                    if (values != null)
                    {
                        foreach (OracleClient.OracleParameter sqlParameter in values)
                        {
                            sqlCommand.Parameters.Add(sqlParameter);
                        }
                    }

                    // Load the data into the table.
                    using (dataReader = sqlCommand.ExecuteReader())
                    {
                        dataSet = new System.Data.DataSet();
                        dataSet.EnforceConstraints = false;
                        dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
                        dataReader.Close();
                    }

                    // Close the database connection.
                    connection.Close();
                }

                // Return the sql command, including
                // any parameters that have been
                // marked as output direction.
                return(sqlCommand);
            }
            catch (Exception ex)
            {
                // Throw a general exception.
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (dataReader != null)
                {
                    dataReader.Close();
                }

                if (connection != null)
                {
                    connection.Close();
                }
            }
        }