Load() public method

public Load ( IDataReader reader ) : void
reader IDataReader
return void
Exemplo n.º 1
1
        private void lc(object sender, MouseEventArgs e)
        {
            c.Open();
            DataSet ds = new DataSet();
            string query = "select ID,pname,bill,pbill from pdetails where date=@bc ";
            OleDbCommand cmd = new OleDbCommand(query, c);
            cmd.Parameters.Add("@bc", OleDbType.Date).Value = dateTimePicker1.Value.Date;
            OleDbDataReader dr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(dr);
            /*ds.Tables.Add(dt);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.Fill(dt);*/
            dataGridView1.DataSource = dt.DefaultView;
            c.Close();
            try
            {
                c.Open();
                String str = @"SELECT SUM(pbill) FROM pdetails WHERE date=@bb;";

                OleDbCommand comm2 = new OleDbCommand(str, c);
                comm2.Parameters.Add("@bb", OleDbType.Date).Value = dateTimePicker1.Value.Date;
                bill = Convert.ToDouble(comm2.ExecuteScalar());

                label3.Text = bill.ToString() + "/-";
            }
            catch(Exception ex)
            {
                MessageBox.Show("selected date miss match");
                c.Close();
            }
                c.Close();
        }
        public String GetLastIDInserted(String pStrDBTable)
        {
            String mStrSQL = "SELECT IDENT_CURRENT('" + pStrDBTable + "')";
            String mStrLastIDInserted = "";

            try
            {
                oCommand = new SqlCommand(mStrSQL,oConnection);
                oReader = oCommand.ExecuteReader();
                oTable = new DataTable();
                oTable.Load(oReader);

                foreach (DataRow oDataRow in oTable.Rows)
                {
                    foreach (DataColumn oDataColumn in oTable.Columns)
                    {
                        mStrLastIDInserted = oDataRow[oDataColumn.ColumnName].ToString();
                    }
                }
            }
            catch (DataException Ex)
            {
                mStrLastIDInserted = "";
            }
            oCommand = null;
            oReader = null;
            oTable = null;
            return mStrLastIDInserted;
        }
Exemplo n.º 3
1
 public HashSum(SqlDataReader reader, String secret = "PZS")
 {
     DataTable dt = new DataTable();
     dt.Load(reader);
     dt.TableName = secret;
     this.dt = dt;
 }
Exemplo n.º 4
0
    protected void btnAdminCalEdit_Click(object sender, EventArgs e)
    {
        lblAdminCalBlockDate.Visible = true;
        txtAdminCalBlockDate.Visible = true;
        grdviewAdminCalendar.Visible = true;

        conn = new SqlConnection(connstr);
        cmd = new SqlCommand("select * from AdminCalendar", conn);

        System.Data.DataTable dt = new System.Data.DataTable();

        conn.Open();
        rdr = cmd.ExecuteReader();

        if (rdr.HasRows)
        {
            dt.Load(rdr);
            grdviewAdminCalendar.DataSource = dt;
            grdviewAdminCalendar.DataBind();
            clear();
        }
        else
        {
            dt.Load(rdr);
            grdviewAdminCalendar.DataSource = dt;
            grdviewAdminCalendar.EmptyDataText = "No Record Found";
            grdviewAdminCalendar.DataBind();
        }
        FillHolidayDataset();
        conn.Close();
        clear();
    }
Exemplo n.º 5
0
        /// <summary>
        /// Unose se postojeće primke (bez njihovih stavki) u datagrid
        /// </summary>
        private void UnesiPodatkeUDatagrid()
        {
            string upit = "SELECT \"Primke\".\"idPrimke\", \"Primke\".datum::varchar(10) AS \"Datum dospijeća\", \"Primke\".dobavljac, \"Dobavljaci\".\"nazivDobavljaca\" AS \"Naziv dobavljača\", \"Primke\".placeno AS \"Plaćeno\" FROM \"Primke\" JOIN \"Dobavljaci\" ON \"Primke\".dobavljac = \"Dobavljaci\".\"idDobavljaca\" ORDER BY 2";

            using (NpgsqlDataReader dr = DB.Instance.dohvati_podatke(upit))
            {
                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Load(dr);
                while (dr.Read())
                {
                    dt.Load(dr);
                }
                Primke_datagrid.DataSource = dt;

                //skrivamo stupce u kojima se nalaze ID primke i ID dobavljaca
                Primke_datagrid.Columns[0].Visible = false;
                Primke_datagrid.Columns[2].Visible = false;
                //zaključavamo datagrid kako bi spriječili direktne promjene u njemu (za to imamo obrazac)
                Primke_datagrid.ReadOnly = true;
                //sprječavamo da korisnik selektira više redaka
                Primke_datagrid.MultiSelect = false;
                //omogućavamo full row select umjesto single column select
                Primke_datagrid.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                //sprječavamo korisnika da dodaje nove zapise u datagrid
                Primke_datagrid.AllowUserToAddRows = false;
            }
        }
        public static DataTable CallQueryProcedure(string ProcudereName)
        {
            DataTable rows = new DataTable();

            try
            {

                string ConnectionString = connection();
                SqlConnection Conn = new SqlConnection(ConnectionString);

                SqlCommand cmd = new SqlCommand(ProcudereName, Conn);

                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                Conn.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                rows.Load(rdr);

                //cmd.CommandText =ProcudereName;
                return rows;
            }
            catch (Exception ex)
            {
                return rows;
            }
        }
Exemplo n.º 7
0
        /// <summary>
        /// 排序
        /// </summary>
        /// <param name="list"></param>
        /// <param name="orderByTypes"></param>
        /// <param name="orderByUnqueField"></param>
        /// <returns></returns>
        public static List <DataRow> OrderByDataRow(this IEnumerable <DataRow> list, List <OrderByDictionary> orderByTypes, OrderByDictionary orderByUnqueField)
        {
            orderByTypes.Add(orderByUnqueField);
            var dt          = list.AsEnumerable().CopyToDataTable();
            var guidType    = typeof(Guid);
            var sqlGuidType = typeof(SqlGuid);

            System.Data.DataTable dtByConvertGuidToSqlGuid = new System.Data.DataTable();
            foreach (DataColumn it in dt.Columns)
            {
                var isGuid = it.DataType == guidType;
                if (isGuid)
                {
                    dtByConvertGuidToSqlGuid.Columns.Add(it.ColumnName, sqlGuidType);
                }
                else
                {
                    dtByConvertGuidToSqlGuid.Columns.Add(it.ColumnName, it.DataType);
                }
            }
            //将dataTable中guid换转成sqlguid,这样排序才会和SQL一致
            dtByConvertGuidToSqlGuid.Load(dt.CreateDataReader(), System.Data.LoadOption.OverwriteChanges);
            var view = dtByConvertGuidToSqlGuid.AsDataView();

            view.Sort = string.Join(",", orderByTypes.Select(it => string.Format(" {0} {1} ", it.OrderByField, it.IsAsc ? "ASC" : "DESC")));
            var reval = view.ToTable().AsEnumerable().ToList();

            orderByTypes.Remove(orderByUnqueField);
            return(reval);
        }
Exemplo n.º 8
0
        /// <summary>
        /// Read data from database.
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        protected DataTable ReadData(string query)
        {
            if (this._Connection.State != System.Data.ConnectionState.Open)
            {
                throw new Exception("Database not connected.");
            }

            try
            {
                using (SQLiteCommand command = new SQLiteCommand(query, this._Connection))
                {
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        using (System.Data.DataTable table = new System.Data.DataTable())
                        {
                            table.Load(reader);
                            return(table);
                        }
                    }
                }
            }
            catch (SQLiteException e)
            {
                MessageBox.Show(e.Message);
            }
            return(null);
        }
Exemplo n.º 9
0
 public DataTable getOrderByID(String pStrID)
 {
     DataTable dt = new DataTable();
     SqlConnection conn = Program.CreateConnection();
     SqlCommand cmd = new SqlCommand();
     try
     {
         using (conn)
         {
             conn.Open();
             cmd.Connection = conn;
             cmd.CommandText = "SELECT OrderID, UserName, ProdName, PaymentTypeName, ShipperName, CustEmail, DeliveryNotes, OrderAmount FROM Orders WHERE OrderID='" + pStrID + "' " +
                               "UNION " +
                               "SELECT OrderID, UserName, ProdName, PaymentTypeName, ShipperName, CustEmail, DeliveryNotes, OrderAmount FROM RejectedOrders WHERE OrderID='" + pStrID + "' " +
                               "UNION " +
                               "SELECT OrderID, UserName, ProdName, PaymentTypeName, ShipperName, CustEmail, DeliveryNotes, OrderAmount FROM ApprovedOrders WHERE OrderID='" + pStrID + "'";
             dt.Load(cmd.ExecuteReader());
         }
     }
     catch (SqlException)
     {
         conn.Close();
     }
     return dt;
 }
Exemplo n.º 10
0
    // executes a command and returns the results as a DataTable object
    public static DataTable ExecuteSelectCommand(DbCommand command)
    {
        // The DataTable to be returned
        DataTable table;
        // Execute the command making sure the connection gets closed in the end
        try
        {
            // Open the data connection
            command.Connection.Open();
            // Execute the command and save the results in a DataTable
            DbDataReader reader = command.ExecuteReader();
            table = new DataTable();
            table.Load(reader);

            // Close the reader
            reader.Close();
        }
        catch (Exception ex)
        {
            Utilities.LogError(ex);
            throw;
        }
        finally
        {
            // Close the connection
            command.Connection.Close();
        }
        return table;
    }
Exemplo n.º 11
0
        //Export
        private DataTable GetSimpleQueryResult(string Query)
        {
            //실패시 반환용
            DataTable dataTable = new DataTable();

            try
            {
                using (OracleCommand cmd = new OracleCommand(Query, conn))
                {
                    conn.Open();
                    using (OracleDataReader reader = cmd.ExecuteReader())
                    {
                        dataTable.Load(reader);
                        conn.Close();
                        return(dataTable);
                    }
                }
            }
            catch (Exception ex)
            {
                //TODO: 외부로 직접적인 에러 메세지 노출되지 않도록 하기
                //MessageBox.Show(ex.Message);
                conn.Close();
                return(dataTable);
            }
        }
        private void dataGridViewRequirementList_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex != dataGridViewRequirementList.RowCount - 1)
            {
                SqlDataReader dr = dbConnection.query("select productdetails.productID from productdetails where itemname='" + dataGridViewRequirementList.Rows[e.RowIndex].Cells[0].Value.ToString() + "'");
                dr.Read();
                string productID = dr[0].ToString();
                string sqlqry = "select productionorder.orderID,customer.customername,productionorder.quantity,count(product.productID) from product,productionorder,customer where product.productID=productionorder.productID and customer.customer_ID=productionorder.customerID and productionorder.orderstatus=0 group by productionorder.orderstatus,productionorder.orderID,product.productID,productionorder.customerID,product.salestatus,productionorder.productID,productionorder.quantity,customer.customername having product.salestatus='INSTOCK' and product.productID='" + productID + "'";

                dr = dbConnection.query(sqlqry);
                DataTable dt = new DataTable();
                dt.Load(dr);
            dataGridViewRequirementByCust.DataSource = dt;
                if (gridviewbuttonstat == false)
                {
                    DataGridViewButtonColumn dgbut = new DataGridViewButtonColumn();
                    dgbut.HeaderText = "sell product";
                    dgbut.Text = "Sell";
                    dgbut.UseColumnTextForButtonValue = true;
                    dataGridViewRequirementByCust.Columns.Add(dgbut);
                    dataGridViewRequirementByCust.Columns[2].HeaderText = "Quantity Required";
                    dataGridViewRequirementByCust.Columns[3].HeaderText = "Stock Available";
                    dataGridViewRequirementByCust.Update();
                    gridviewbuttonstat = true;
                    this.Width = this.Width + 410;
                    dataGridViewRequirementByCust.Visible = true;
                    label3.Visible = true;
                }
            }
        }
Exemplo n.º 13
0
 private void InfoUser()
 {
     using (SqlConnection conn = new SqlConnection(@"Data Source=AYE\SQLEXPRESS;Initial Catalog=Bank;Integrated Security=True"))
     {
         try
         {
             conn.Open();
             SqlCommand cmd = conn.CreateCommand();
             cmd.CommandText = "SELECT [Name], [Surname],[Patronymic],[Phone],[Adress],[E-Mail],[DateOfBirth],[PlaceOfBirth] FROM [User] WHERE [IDUser] = @IdUser ";
             cmd.Parameters.AddWithValue(@"IdUser", UserId);
             SqlDataReader reader = cmd.ExecuteReader();
             table = new System.Data.DataTable();
             table.Load(reader);
             reader.Close();
             Head.Text            = table.Rows[0][1].ToString() + " " + table.Rows[0][0].ToString() + " " + table.Rows[0][2].ToString();
             LabAdress.Text       = table.Rows[0][4].ToString();
             LabPhone.Text        = table.Rows[0][3].ToString();
             LabEmail.Text        = table.Rows[0][5].ToString();
             LabDataOfBirth.Text  = (table.Rows[0][6].ToString());
             LabDataOfBirth.Text  = LabDataOfBirth.Text.Remove(10);
             LabPlaceOfBirth.Text = table.Rows[0][7].ToString();
         }
         catch (Exception ex)
         {
             MessageBox.Show(ex.ToString());
         }
         finally
         {
             conn.Close();
         }
     }
 }
Exemplo n.º 14
0
        public bool ExecuteProc(String strProcName, ref System.Data.DataTable table, params SqlParameter[] aryProcParams)
        {
            if (m_dbConnect.State == System.Data.ConnectionState.Closed)
            {
                m_dbConnect.Open();
            }

            SqlCommand dbCmd = m_dbConnect.CreateCommand();

            dbCmd.CommandText = strProcName;
            dbCmd.CommandType = CommandType.StoredProcedure;

            if (aryProcParams != null)
            {
                dbCmd.Parameters.AddRange(aryProcParams); // Past the params
            }

            SqlDataReader dbReader = dbCmd.ExecuteReader();

            if (dbReader == null)
            {
                return(false);
            }

            table.Load(dbReader);

            dbReader.Close();

            return(true);
        }
        public static System.Data.DataTable GetTableFromServerArgs(string procedureName, params string [] ArgsValues)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand    cmd           = new SqlCommand();
            SqlDataReader reader;

            System.Data.DataTable table = new System.Data.DataTable();
            cmd.CommandText    = procedureName;
            cmd.CommandType    = CommandType.StoredProcedure;
            cmd.CommandTimeout = 1000;
            cmd.Connection     = sqlConnection;
            sqlConnection.Open();

            foreach (string param in ArgsValues)
            {
                string paramName  = param.Substring(0, param.IndexOf("-"));
                string paramValue = null;
                if (param.IndexOf("-") != param.Length - 1)
                {
                    paramValue = param.Substring(param.IndexOf("-") + 1, param.Length - param.IndexOf("-") - 1);
                }
                cmd.Parameters.AddWithValue(paramName, paramValue);
            }



            reader = cmd.ExecuteReader();

            table.Load(reader);

            sqlConnection.Close();
            return(table);
        }
Exemplo n.º 16
0
 /// <summary>
 /// 执行查询返回DataTable
 /// </summary>
 /// <param name="sql">Sql语句</param>
 /// <returns>成功返回DataTable,失败则返回 null</returns>
 static public System.Data.DataTable ExecuteQueryToDataTable(string sql, System.Data.Common.DbConnection Conn)
 {
     if (Conn is System.Data.SQLite.SQLiteConnection)
     {
         var cmd = Conn.CreateCommand() as System.Data.SQLite.SQLiteCommand;
         cmd.CommandText    = sql;
         cmd.CommandTimeout = 180;
         var       da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
         DataTable dt = new DataTable();
         da.Fill(dt);
         return(dt);
     }
     if (Conn is OracleConnection)
     {
         var cmd = Conn.CreateCommand() as OracleCommand;
         cmd.CommandText    = sql;
         cmd.CommandTimeout = 180;
         var       da = new OracleDataAdapter(cmd);
         DataTable dt = new DataTable();
         da.Fill(dt);
         return(dt);
     }
     else
     {
         System.Data.DataTable   dt     = new System.Data.DataTable();
         System.Data.IDataReader reader = ExecuteQuery(sql, Conn);
         dt.Load(reader);
         return(dt);
     }
 }//ExecuteQueryToDataTable(string sql)
Exemplo n.º 17
0
        private void ShowAppointmentData()
        {
            conn = new SqlConnection(connstr);
            cmd  = new SqlCommand("SELECT * from Appointment WHERE Patient_Name LIKE @Name AND Dentist=@Dentist", conn);

            cmd.Parameters.AddWithValue("@Name", ViewAppointmentTextBox.Text + "%");
            cmd.Parameters.AddWithValue("@Dentist", Globals.CurrentUser);
            System.Data.DataTable dt = new System.Data.DataTable();

            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();

                if (rdr.HasRows)
                {
                    dt.Load(rdr);
                    AppointmentDataGridView.DataSource = dt;
                }
                else
                {
                }
            }
            catch (Exception)
            {
                MessageBox.Show("No data in table");
            }
        }
Exemplo n.º 18
0
        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            Console.WriteLine("Worker Complete");

            if (errorMsg == null)
            {
                //   dgv.Columns.Clear();
                //   dgv.Rows.Clear();

                try
                {
                    System.Data.DataTable dataTable = new System.Data.DataTable();
                    dataTable.Load(reader);
                    dgv.DataSource        = dataTable;
                    lblBottomMessage.Text = "Results: " + dataTable.Rows.Count;
                    MessageBox.Show("Search Complete", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception ee)
                {
                    lblBottomMessage.Text = "Failed to display results";
                    MessageBox.Show("Failed to display results\n" + ee.Message, "Oops", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            else
            {
                lblBottomMessage.Text = errorMsg;
            }
        }
 public OperationResult <List <DataRow> > SubmitQuery(string sql, CommandType commandType, IEnumerable <KeyValuePair <string, object> > parameters) =>
 PerformDataBaseCall(sql, commandType, parameters, command => {
     System.Data.DataTable result = new System.Data.DataTable();
     using (var reader = command.ExecuteReader())
         result.Load(reader);
     return(result.AsEnumerable().ToList());
 });
Exemplo n.º 20
0
        public static string GetName(int id)
        {
            string _out;

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["PrimaryDatabase"].ConnectionString);
            con.Open();

            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT name FROM States WHERE id =" + Convert.ToString(id);

                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    if (sdr != null && sdr.HasRows)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(sdr);
                        _out = dt.Rows[0]["name"].ToString();

                        return _out;
                    }
                }
            }

            return null;
        }
Exemplo n.º 21
0
Arquivo: DBS.cs Projeto: chaoskie/LP
            /// <summary>
            /// Selects and retrieves values from the database 
            /// </summary>
            /// <param name="query">The selection statement</param>
            /// <returns>A DataTable with the retrieved values></returns>
            public static DataTable RetrieveQuery(string query)
            {
                if (Regex.IsMatch(query, @"-{2,}"))
                {
                    throw new SQLInjectionException();
                }

                using (OracleConnection c = new OracleConnection(@connectionstring))
                {
                    try
                    {
                        c.Open();
                        OracleCommand cmd = new OracleCommand(@query);
                        cmd.Connection = c;
                        try
                        {
                            OracleDataReader r = cmd.ExecuteReader();
                            DataTable result = new DataTable();
                            result.Load(r);
                            c.Close();
                            return result;
                        }
                        catch (OracleException e)
                        {
                            Console.Write(e.Message);
                            throw;
                        }
                    }
                    catch (OracleException e)
                    {
                        Console.Write(e.Message);
                        return new DataTable();
                    }
                }
            }
Exemplo n.º 22
0
        private void LoadOperators()
        {
            var dt = new System.Data.DataTable();

            if (dataGridView1.DataSource != null)
            {
                dataGridView1.DataSource = null;
            }

            var q = "select * from operators";

            using (var con = new SqlConnection(Config.ConString))
            {
                var cmd = new SqlCommand(q, con);
                con.Open();
                var dr = cmd.ExecuteReader();
                dt.Load(dr);
                con.Close();
                dr.Close();
            }

            dataGridView1.DataSource = dt;

            dataGridView1.Columns[0].Visible = false;
            dataGridView1.Columns[1].Width   = 60;
            dataGridView1.Columns[2].Width   = 150;
            dataGridView1.Columns[3].Width   = 150;
        }
Exemplo n.º 23
0
        public static DataTable datatablemonths()
        {
            SqlConnection con = new SqlConnection(cs);
                DataTable table = new DataTable();

                try
                {
                    SqlCommand cmd = new SqlCommand("getmonths", con);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Connection.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.HasRows)
                    {
                        table.Load(dr);
                        dr.Close();
                    }
                }
                catch (Exception)
                {

                }

                finally
                {
                    con.Close();
                }
                return table;
        }
Exemplo n.º 24
0
        //search
        private void searchProjectTxtBox_TextChanged(object sender, EventArgs e)
        {
            string qry            = "";
            string projectNameTxt = searchProjectTxtBox.Text;


            qry = "Select p.type, p.date, p.Category,p.amount, s.first_name from office_expenses p inner join staff s on s.staff_id=p.staff_id where s.first_name like '%" + projectNameTxt + "%' or p.Category like '%" + projectNameTxt + "%'";

            try
            {
                MySqlDataReader reader = DBConnection.getData(qry);

                if (reader.HasRows)
                {
                    System.Data.DataTable table1 = new System.Data.DataTable();

                    table1.Load(reader);

                    metroGrid1.DataSource = table1;
                }
                else
                {
                    reader.Close();
                }
            }
            catch (Exception exc)
            {
                MessageBox.Show("Invalid data!\n" + exc.StackTrace, "Project finder", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Exemplo n.º 25
0
        public static DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();

            try

            {

                SQLiteConnection cnn = new SQLiteConnection(@"Data Source=C:\Projects\showdownsharp\db\showdown.db");

                cnn.Open();

                SQLiteCommand mycommand = new SQLiteCommand(cnn);

                mycommand.CommandText = sql;

                SQLiteDataReader reader = mycommand.ExecuteReader();

                dt.Load(reader);

                reader.Close();

                cnn.Close();

            } catch {

            // Catching exceptions is for communists

            }

            return dt;
        }
Exemplo n.º 26
0
        public System.Data.DataTable SelectUsersInfo(int ID)
        {
            string query = "select * from fullusersinfo as a where a.ID <>" + ID;

            System.Data.DataTable tempUserList = new System.Data.DataTable();


            if (this.openConnection() == true)
            {
                MySqlCommand    cmd        = new MySqlCommand(query, dbConnect);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list

                tempUserList.Load(dataReader);
                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();


                //return list to be displayed
                return(tempUserList);
            }
            else
            {
                return(tempUserList);
            }
        }
Exemplo n.º 27
0
        public static Boolean UploadClients()
        {
            var db = new BHelpContext();

            var filePath = @"c:\TEMP\BH Food Client List-Table 1.csv";

            DataTable csvtable = new DataTable();

            using (CsvReader csvReader =
                       new CsvReader(new StreamReader(filePath), true))
            {
                csvtable.Load(csvReader);
            }

            foreach (DataRow row in csvtable.Rows)
            {
                Client client = new Client()
                {
                    Active       = true,
                    LastName     = row[0].ToString(),
                    FirstName    = row[1].ToString(),
                    StreetNumber = row[2].ToString(),
                    StreetName   = row[3].ToString(),
                    City         = row[4].ToString(),
                    Zip          = row[5].ToString(),
                    Phone        = row[6].ToString(),
                    Notes        = row[13].ToString()
                };
                db.Clients.Add(client);
                db.SaveChanges();
                //System.Diagnostics.Debug.WriteLine(client.FirstName, client.LastName);
            }

            return(true);
        }
Exemplo n.º 28
0
        public System.Data.DataTable SelectGroupsInfo()
        {
            string query = "select * from groupinfoview";

            System.Data.DataTable tempList = new System.Data.DataTable();


            if (this.openConnection() == true)
            {
                MySqlCommand    cmd        = new MySqlCommand(query, dbConnect);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list

                tempList.Load(dataReader);
                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();


                //return list to be displayed
                return(tempList);
            }
            else
            {
                return(tempList);
            }
        }
Exemplo n.º 29
0
        private void PopulateLocation()
        {
            DataTable dt = new DataTable();

            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString))
            {
                string query = "Select * from Locations";
                SqlCommand cmd = new SqlCommand(query, con);
                
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                dt.Load(cmd.ExecuteReader());
            }

            direcFrom.DataSource = dt;
            direcFrom.DataTextField = "LocName";
            direcFrom.DataValueField = "LocID";
            direcFrom.DataBind();

            direcTo.DataSource = dt;
            direcTo.DataTextField = "LocName";
            direcTo.DataValueField = "LocID";
            direcTo.DataBind();
        }
Exemplo n.º 30
0
        public System.Data.DataTable SelectRolesInfo()
        {
            string query = "select a.id, a.name as `Название`, IF((`a`.`allowRead` = 1),'Да','Нет') AS `Чтение`, IF((`a`.`allowWrite` = 1), 'Да', 'Нет') AS `Запись`,IF((`a`.`Admin` = 1), 'Да', 'Нет') AS `Полный доступ` from roles as a";

            System.Data.DataTable tempList = new System.Data.DataTable();


            if (this.openConnection() == true)
            {
                MySqlCommand    cmd        = new MySqlCommand(query, dbConnect);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list

                tempList.Load(dataReader);
                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();


                //return list to be displayed
                return(tempList);
            }
            else
            {
                return(tempList);
            }
        }
Exemplo n.º 31
0
        public static string GetDesc()
        {
            string _out = string.Empty;

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = Connections.GetConnection;
                con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT DESCRIPTION FROM Table1 WHERE Id=1", con))
                {
                    cmd.CommandType = System.Data.CommandType.Text;

                    try
                    {
                        using (SqlDataReader sdr = cmd.ExecuteReader())
                        {
                            if (sdr != null && sdr.HasRows)
                            {
                                DataTable dt = new DataTable();
                                dt.Load(sdr);
                                _out = dt.Rows[0][0].ToString();
                                return _out;
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        return e.Message.ToString();
                    }
                }
            }

            return _out;
        }
Exemplo n.º 32
0
        private System.Data.DataTable getLabelDataFromView()
        {
            var strSQL = "Select * from LABEL where [KEY] = @sqlPara order by _NullFlags";

            System.Data.DataTable result = new System.Data.DataTable();
            var paraKey = new SqlParameter("@sqlPara", SqlDbType.Char, 14);

            paraKey.Value = packingKey;

            var sqlConn = new SqlConnection(dbConnStr);

            try
            {
                sqlConn.Open();
                SqlCommand sCmd = new SqlCommand(strSQL, sqlConn);
                sCmd.Parameters.Add(paraKey);
                var value = sCmd.ExecuteReader();
                result.Load(value);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "執行 execQuery() 出現問題");
            }
            finally
            {
                sqlConn.Close();
            }
            return(result);;
        }
Exemplo n.º 33
0
 public void BindPrimaryGrid()
 {
     try
     {
         int clientId = Convert.ToInt32(Session["ClId"]);
         DateTime from = Convert.ToDateTime(txtFromDate.Text);
         DateTime to = Convert.ToDateTime(txtToDate.Text);
         BLSelectProject blselPr = new BLSelectProject();
         SqlDataReader dr = blselPr.GetProjectByAddInv1(clientId, from, to);
         DataTable dt = new DataTable();
         if (dr.HasRows)
         {
             dt.Load(dr);
             GdVwSelPrjct.DataSource = dt.DefaultView;
             GdVwSelPrjct.DataBind();
             dr.Close();
         }
         else
         {
             GdVwSelPrjct.DataSource = null;
             GdVwSelPrjct.DataBind();
             lblErrorMsg.Text = "Records not Available";
         }
     }
     catch (Exception ex)
     {
         lblCatchError.Text = ex.Message.ToString();
     }
 }
Exemplo n.º 34
0
        //Поиск абонента по ОКПО
        protected void lbFindOKPO_Click(object sender, EventArgs e)
        {
            /*
             * Поиск по базе водосбыта
             */
            if (tbCorporateOKPO.Text.Length >= 4)
            {
                try
                {
                    FbConnection  fbCn  = new FbConnection("Database=water_wdk;Server=db3;User=viewer_;Password=VIEWER_;Role=viewer;");
                    FbDataAdapter fbDa  = new FbDataAdapter();
                    FbCommand     fbCmd = new FbCommand();
                    //DataTable dt = new DataTable();

                    fbCn.Open();
                    fbCmd = new FbCommand(string.Format(@"select delzero(d.id_dog)||' / '||r.name_ray||' район' dogovor,d.id_doc, d.okpo,d.bank,d.r_s,d.mfo,
                    h.full_name,h.ur_address,
                    case k.gr when 3 then 1 when 4 then 1 when 5 then 1 when 6 then 1 else 0 end budzhet,h.inn, h.id_svid,
                    d.info_1 kontact,
                    d.v_litse,d.na_osnovanii,d.otv_litso,d.contact_phone,d.ot_imeni,h.sys_no,s.name_no
                    from dogovor d
                    
                    left join rayon r on r.id_ray=d.id_ray
                    left join history h on h.id_doc=d.id_doc
                    left join history n on n.id_doc=h.id_doc
                    and (n.datebeg>h.datebeg or n.datebeg=h.datebeg and n.kod>h.kod)
                    left join kods k on k.podgr=h.podgr
left join spr_sys_no s on s.id_no=h.sys_no
                    where d.okpo like '%'||{0}||'%'
                    and d.date_close_d is null
                    and n.kod is null", tbCorporateOKPO.Text), fbCn);

                    FbDataReader          dr = fbCmd.ExecuteReader();
                    System.Data.DataTable dt = new System.Data.DataTable();
                    dt.Load(dr);

                    if (dt.Rows.Count > 0)
                    {
                        repCorporate.DataSource = dt;
                        repCorporate.DataBind();
                        litSearch.Text = "";
                    }
                    else
                    {
                        repCorporate.DataBind();
                        litSearch.Text = "Нет данных";
                    }
                    dr.Close();
                    fbCn.Close();
                }
                catch (Exception ex)
                {
                    SetMessege("Ошибка", "Сбой подключения к базе данных   <br>" + ex.Message);
                }
            }
            else
            {
                SetMessege("Предупреждение", "Для поиска необходимо ввести минимум 4 символа.");
            }
        }
Exemplo n.º 35
0
        private void ourMediaForm_Load(object sender, EventArgs e)
        {
            string qString = @"SELECT * FROM Genre;";
            string cString = @"Server=tcp:xj78qcdgml.database.windows.net,1433;Database=OurMedia;User ID=john@xj78qcdgml;Password=25b7Xl21211;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;";
            DataTable dTable = new DataTable();

            //make sure only one set of data is populated
            if(genreCombobox.Items.Count > 0)
                genreCombobox.Items.Clear();

            SqlConnection sConnection = new SqlConnection(cString);
            sConnection.Open();

            using(SqlCommand sCommand = new SqlCommand(qString, sConnection))
            {
                SqlDataReader sReader = sCommand.ExecuteReader();
                dTable.Load(sReader);
            }

            genreCombobox.DataSource = dTable;
            genreCombobox.ValueMember = dTable.Columns[0].ToString();
            genreCombobox.DisplayMember = dTable.Columns[1].ToString();

            sConnection.Close();
        }
Exemplo n.º 36
0
        public void LoadDataReader(IDataReader pDataReader)
        {
            DataTable DT = new System.Data.DataTable();

            DT.Load(pDataReader);
            dataSet.Tables.Add(DT);
        }
Exemplo n.º 37
0
        private void ShowAllBookBank()
        {
            string sqlUnit;
            sqlUnit = "SELECT * FROM BANKDOC ORDER BY BankDocId";

            SqlDataReader dr;  // สร้าง data reader ชื่อ dr
            com = new SqlCommand();
            com.CommandType = CommandType.Text;
            com.CommandText = sqlUnit;   // ดึง sqlunit
            com.Connection = Conn;  // เชื่อมกะ db
            dr = com.ExecuteReader(); // รันชุดคำสั่ง

            if (dr.HasRows)
            {
                DataTable dt = new DataTable();  // สร้าง object  datatable มาชื่อ dt
                dt.Load(dr);  //  ให้ dt โหลดข้อมูลออกมา
                //CheckResult = dt.Rows.Count;
                dgvSelectBookBank.DataSource = dt;  // แสดงรายชื่อ  ลงไปยัง dgv unit ( Datagrid view)
                FormatdgvHead();// FormatHeadDgv
            }
            else
            {
                //CheckResult = 0;
                dgvSelectBookBank.DataSource = null; // ล้างข้อมูลออก
            }

            dr.Close(); // ปิด object dr
        }
        private void dateTimeTo_ValueChanged_1(object sender, EventArgs e)
        {
            DateTime dateTime = dateTimeTo.Value;


            if (dateTime < dateTimefrom.Value)
            {
                MessageBox.Show("Invalid 'from' date");
            }
            else
            {
                try
                {
                    MySqlDataReader reader = DBConnection.getData("SELECT u.log_id as LogID, s.u_name as Username, u.logged_in_dateTime as LoginTime, u.logged_out_datetime as LogoutTime,"
                                                                  + " u.detail as Detail, u.ip as IP FROM user_log u, user s where s.user_id = u.user_id and date(u.logged_in_dateTime) >= '" + dateTimefrom.Value.ToString("yyyy/MM/dd") + "' and  date(u.logged_out_dateTime) <= '" + dateTimeTo.Value.ToString("yyyy/MM/dd") + "';");

                    if (reader.HasRows)
                    {
                        System.Data.DataTable table = new System.Data.DataTable();

                        table.Load(reader);
                        LogGrid.DataSource = table;
                    }
                    else
                    {
                        reader.Close();
                    }
                }
                catch (Exception exc)
                {
                    MessageBox.Show("Invalid data!\n" + exc.StackTrace, "User Log finder", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }
Exemplo n.º 39
0
        /// <summary>
        /// Get all designation information
        /// </summary>
        /// <returns></returns>
        public DataTable GetDesignationAll()
        {
            DataTable tblEmpInfo = new DataTable();
            SqlDataReader rdr = null;

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT [Id],[DesignationName] FROM [dbo].[Designation]";

            try
            {
                if (sqlConn.State == ConnectionState.Closed)
                    sqlConn.Open();

                rdr = cmd.ExecuteReader();
                tblEmpInfo.Load(rdr);
            }
            catch (Exception exp)
            {
                throw (exp);
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
            return tblEmpInfo;
        }
        private void metroTextBox1_TextChanged(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();

            try
            {
                MySqlDataReader reader = DBConnection.getData("SELECT u.log_id as LogID, s.u_name as Username, u.logged_in_dateTime as LoginTime, u.logged_out_datetime as LogoutTime, " +
                                                              " u.detail as Detail, u.ip as IP FROM user_log u, user s where s.user_id = u.user_id and s.u_name LIKE'%" + metroTextBox1.Text.ToString() + "%'");

                if (reader.HasRows)
                {
                    System.Data.DataTable table = new System.Data.DataTable();

                    table.Load(reader);

                    LogGrid.DataSource = table;
                }
                else
                {
                    reader.Close();
                }
            }
            catch (Exception exc)
            {
                MessageBox.Show("Invalid data!\n" + exc.StackTrace, "User Log finder", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        private void fillComboSemana()
        {
            // Step 1 
            MySqlConnection MyConexion = new MySqlConnection(Conexion.ConectionString);

            // Step 2
            MyConexion.Open();

            // Step 3
            MySqlCommand MyCommand = new MySqlCommand("SELECT idsemana, semana FROM semanas ORDER BY idsemana ASC", MyConexion);

            // Step 4
            MySqlDataReader MyReader;
            MyReader = MyCommand.ExecuteReader();

            // Step 5
            DataTable MyDataTable = new DataTable();
            MyDataTable.Columns.Add("idsemana", typeof(int));
            MyDataTable.Columns.Add("semana", typeof(string));
            MyDataTable.Load(MyReader);

            // Step 6             
            cmbSemana.ValueMember = "idsemana";
            cmbSemana.DisplayMember = "semana";
            cmbSemana.DataSource = MyDataTable;

            // Step 7
            MyConexion.Close();
        }
Exemplo n.º 42
0
        public d.DataTable GetAllProducts()
        {
            var dt = new d.DataTable();

            using (var conn = new ss.SqlConnection(this.ConnString))
            {
                using (var cmd = new ss.SqlCommand("select * from Products", conn))
                {
                    try
                    {
                        conn.Open();
                        dt.Load(cmd.ExecuteReader());
                    }
                    catch (Exception ex)
                    {
                        string exceptionMessage      = ex.Message;
                        string innerExceptionMessage = (ex.InnerException is Exception) ? ex.InnerException.Message : "";
                        dt.Rows.Add(dt.NewRow());
                        dt.Columns.Add(new d.DataColumn("ErrorMessage"));
                        dt.Rows[0][0] = exceptionMessage + " " + innerExceptionMessage;
                    }
                }
            }
            return(dt);
        }
Exemplo n.º 43
0
 /************************************************************************
 *  Function:  根据sql语句获取DataTable
 *  In:   string sql, DbConnection
 *  Out:  DataTable
 *  Author: HYF
 *  Date: 2017-04-28
 ************************************************************************/
 public System.Data.DataTable GetDataTable(string sql)
 {
     if (this.DBConn == null)
     {
         //Global.LogServer.Add(new LogInfo("Error", "DBUnit:GetDataTable 数据库连接对象为空", (int)EnumLogLevel.ERROR));
         return(null);
     }
     try
     {
         if (this.DBConn.State == System.Data.ConnectionState.Closed)
         {
             this.DBConn.Open();
         }
         if (this.DBConn.State == ConnectionState.Open)
         {
             System.Data.DataTable   dt     = new System.Data.DataTable();
             System.Data.IDataReader reader = GetDataReader(sql);
             dt.Load(reader);
             return(dt);
         }
     }
     catch (Exception ex)
     {
         //Global.LogServer.Add(new LogInfo("Error", "DBUnit:GetDataTable 错误信息:" + ex.Message, (int)EnumLogLevel.ERROR));
         return(null);
     }
     return(null);
 }
Exemplo n.º 44
0
 public DataTable ExecuteReader(string query)
 {
     try
     {
         MySqlCommand    myCommand = new MySqlCommand(query, myConnection);
         MySqlDataReader reader    = myCommand.ExecuteReader();
         if (reader.HasRows)
         {
             System.Data.DataTable dt = new System.Data.DataTable();
             dt.Load(reader);
             reader.Close();
             return(dt);
         }
         else
         {
             reader.Close();
             return(null);
         }
     }
     catch (MySqlException ex)
     {
         MessageBox.Show(ex.Message);
         return(null);
     }
 }
Exemplo n.º 45
0
        //---------------------------------------------------
        //Obtener PerInterface "Get_PerInterface"
        //--------------------------------------------------
        public DataTable Get_PerInterface()
        {
            DataTable dt = new DataTable();
            try
            {
                clsConection Obj = new clsConection();
                string Cadena = Obj.GetConexionString("Naylamp");

                using (SqlConnection cn = new SqlConnection(Cadena))
                {
                    cn.Open();
                    using (SqlCommand cm = new SqlCommand())
                    {
                        cm.CommandText = "[usp_Get_PerInterface]";
                        cm.CommandType = CommandType.StoredProcedure;
                        cm.Connection = cn;

                        using (SqlDataReader dr = cm.ExecuteReader())
                            dt.Load(dr);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }
        //Get_CtaCteNumeracion
        public DataTable Get_CtaCteNumeracion(BE_ReqCtaCteNumeracion Request)
        {
            DataTable dt = new DataTable();
            try
            {
                clsConection Obj = new clsConection();
                string Cadena = Obj.GetConexionString("Naylamp");

                using (SqlConnection cn = new SqlConnection(Cadena))
                {
                    cn.Open();

                    using (SqlCommand cm = new SqlCommand())
                    {
                        cm.CommandText = "usp_Get_CtaCteNumeracion";
                        cm.CommandType = CommandType.StoredProcedure;
                        cm.Parameters.AddWithValue("cPerJurCodigo", Request.cPerJurCodigo);
                        cm.Connection = cn;

                        using (SqlDataReader dr = cm.ExecuteReader())
                            dt.Load(dr);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }
Exemplo n.º 47
0
        /// <summary>
        /// load all account
        /// </summary>
        /// <param name="table"></param>
        public void LoadAccount(ref DataGridView table)
        {
            string query = "SELECT ID, TITLE, CREATED, MODIFIED FROM ACCOUNT";

            if (this._Connection.State != System.Data.ConnectionState.Open)
            {
                throw new Exception("Database not connected.");
            }

            try
            {
                using (SQLiteCommand command = new SQLiteCommand(query, this._Connection))
                {
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        using (System.Data.DataTable dt = new System.Data.DataTable())
                        {
                            dt.Load(reader);
                            table.DataSource = dt;
                        }
                    }
                }
            }
            catch (SQLiteException e)
            {
                MessageBox.Show(e.Message);
            }
        }
Exemplo n.º 48
0
 /// <summary>Carrega um <see cref="DataTable" /> a partir de um <see cref="SqlCommand" />.</summary>
 /// <param name="comando">Objeto a partir do qual o <see cref="DataTable" /> é carregado.</param>
 /// <returns>Retorna o novo <see cref="DataTable" />.</returns>
 public static DataTable GeraDataTable(SqlCommand comando)
 {
     var dataTable = new DataTable();
     SqlDataReader reader = comando.ExecuteReader();
     dataTable.Load(reader);
     return dataTable;
 }
        public void BindData()
        {
            try
            {
                DataTable dtData = new System.Data.DataTable();

                using (SqlConnection dbCon = new SqlConnection(@"Data Source=DESKTOP-S9RU5TT\USMAN;Initial Catalog=Lab11;Integrated Security=True"))

                {
                    using (SqlCommand cmdGetData = new SqlCommand("SELECT TOP 4 * FROM Book", dbCon))
                    {
                        if (dbCon.State == ConnectionState.Closed)
                        {
                            dbCon.Open();
                        }
                        using (SqlDataReader drGetData = cmdGetData.ExecuteReader())
                        {
                            dtData.Load(drGetData);
                            for (int iCount = 0; iCount < dtData.Rows.Count; iCount++)
                            {
                                byte[]       storedImage = (byte[])dtData.Rows[iCount]["Image"];
                                Image        newImage;
                                MemoryStream stream = new MemoryStream(storedImage);

                                newImage = Image.FromStream(stream);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            { }
        }
Exemplo n.º 50
0
        private void ExportAllRecord(ref System.Data.DataTable dt)
        {
            if (m_RecordModule.DatabaseConnection.State == System.Data.ConnectionState.Closed)
            {
                m_RecordModule.DatabaseConnection.Open();
            }

            try
            {
                SqlCommand cmd = new SqlCommand("Proc_Record_ExportRecord", m_RecordModule.DatabaseConnection);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter cmdParameter1 = new SqlParameter(
                    "@LanguageCode", SqlDbType.Char, 3,
                    ParameterDirection.Input, true, 0, 0, "@LanguageCode",
                    DataRowVersion.Current, m_strActiveLanguage);
                cmd.Parameters.Add(cmdParameter1);

                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
                dr.Close();
            }
            catch (System.Exception e)
            {
                DevComponents.DotNetBar.MessageBoxEx.Show(e.Message);
            }
        }
 public static List<NetworkProcessor> CreateNetProcs()
 {
     AppLog.WriteLine(5, "STATUS", "Entered IRCStatistician.Program.CreateNetProcs().");
     List<NetworkProcessor> returnList = new List<NetworkProcessor>();
     // Get the tbn_networks table
     MySqlCommand NetworkCmd = new MySqlCommand("SELECT * FROM " + Config.SQLTablePrefix + "networks", MyDBConn.Connection);
     MySqlDataReader NetworkDataReader = NetworkCmd.ExecuteReader();
     DataTable NetworkTable = new DataTable();
     NetworkTable.Load(NetworkDataReader);
     NetworkDataReader.Close();
     // Get the tbn_channels table
     MySqlCommand ChannelCmd = new MySqlCommand("SELECT * FROM " + Config.SQLTablePrefix + "channels", MyDBConn.Connection);
     MySqlDataReader ChannelDataReader = ChannelCmd.ExecuteReader();
     DataTable ChannelTable = new DataTable();
     ChannelTable.Load(ChannelDataReader);
     ChannelDataReader.Close();
     // Organize them together.
     foreach (DataRow CurNetwork in NetworkTable.Rows) {
         NetworkProcessor tempLW = new NetworkProcessor(CurNetwork);
         foreach (DataRow CurChannel in ChannelTable.Rows) {
             if (Convert.ToInt32(CurChannel["networkid"]) == tempLW.Network.Id) {
                 tempLW.Network.Channels.Add(CurChannel["name"].ToString(), new Channel(CurChannel["name"].ToString()));
             }
         }
         returnList.Add(tempLW);
     }
     return returnList;
 }
Exemplo n.º 52
0
        protected override CommandResult <DataTable> OnExecute(object commandParameter)
        {
            var result = new CommandResult <DataTable>();
            var param  = commandParameter as LoadPinOrderParameter;

            using (CoreContext context = new CoreContext())
            {
                var acc  = param.MemberAccount;
                var conn = context.Database.GetDbConnection();
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = $@"select p.Recid, p.MIN_COUNT,p.CONFIG,p.MAX_DATE,p.`STATUS`,o.CREATE_DATE,o.ORDER_NO from pin_order o left join pin_info p on o.MAIN_ID=p.RECID 
                              where o.`STATUS`=1 and p.`STATUS`!=0 and o.MEMBER_ACCOUNT=@p0 ORDER BY p.recid desc limit {param.PageSize * (param.PageIndex - 1)},{param.PageSize}";
                cmd.Parameters.Add(new MySqlParameter("@p0", acc));
                var reader = cmd.ExecuteReader();
                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Load(reader);
                reader.Close();
                conn.Close();
                result.Data = dt;
                //result.Data = context.PinOrder.FromSql(@"select  p.END_DATE,p.MIN_COUNT,p.CONFIG,p.MAX_DATE,p.`STATUS`,o.CREATE_DATE from pin_order o left join pin_info p on o.MAIN_ID=p.RECID
                //              where o.`STATUS`=1 and p.`STATUS`!=0 and o.MEMBER_ACCOUNT=@p0", param.MemberAccount).ToList();
            }
            return(result);
        }
Exemplo n.º 53
0
        private DataTable getCategories(out DataTable dtCategories)
        {
            dtCategories = new DataTable();
            try
            {
                using (SqlConnection nwndConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString))
                {
                    SqlCommand cmdCategories = new SqlCommand();
                    cmdCategories.Connection = nwndConnection;
                    cmdCategories.CommandText = "SELECT * FROM CATEGORIES";
                    cmdCategories.CommandType = System.Data.CommandType.Text;

                    nwndConnection.Open();
                    dtCategories.Load(cmdCategories.ExecuteReader());
                    nwndConnection.Close();

                    dtCategories.Columns["CategoryID"].ReadOnly = true;
                }
            }
            catch (Exception ex)
            {
                throw;
            }

            return dtCategories;
        }
Exemplo n.º 54
0
 private void montaGrid()
 {
     reader     = comandoSql.ExecuteReader();
     _dataTable = new System.Data.DataTable();
     _dataTable.Load(reader);
     gridProducao.DataSource = _dataTable;
 }
Exemplo n.º 55
0
        /**
         * @author Felipe de Oliveira
         * 
         * Retorna um <code>DataTable</code> apartir de uma string que indica a tabela.
         * São necessários o Nome da tabela.
         * */
        public DataTable RetornarTabela(string tabela)
        {
            //Abrindo conexão com o banco de dados
            DbConnection conexao = GetConnection();
            DbCommand comando = GetComando(conexao);

            //Informando parametros a serem passados
            comando.CommandText = CommandType.Text.ToString();
            comando.CommandText = "SELECT * FROM " + tabela + "";

            //Criando instancia de um dataTable que armazenara toda a tabela
            DataTable dataTable = new DataTable();
            try
            {
                DbDataReader reader = GetDataReader(comando);
                dataTable.Load(reader);

                //Fechando o DbDataReader
                reader.Close();
                reader.Dispose();
                comando.Dispose();
            }
            catch(Exception e)
            {
                MessageBox.Show("Erro ao tentar mostrar tabela. Mais informações:\n" + e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            //Fechando a conexão com o banco de dados
            conexao.Close();
            conexao.Dispose();
            return dataTable;
        }
Exemplo n.º 56
0
 public DataTable query(String sql)
 {
     lock (syncQuery)
     {
         SqlConnection sqlConn = getConnection();
         if (sqlConn.State != ConnectionState.Open)
         {
             sqlConn.Open();
         }
         DataTable dt = new DataTable();
         SqlCommand sqlcmd = new SqlCommand(sql, sqlConn);
         try
         {
             SqlDataReader dr = sqlcmd.ExecuteReader();
             dt.Load(dr);
         }
         catch (SqlException ae)
         {
             log.Info(ae.Message.ToString());
             log.Info(sql);
         }
         finally
         {
             sqlcmd.Dispose();
             sqlConn.Close();
         }
         return dt;
     }
 }
Exemplo n.º 57
0
        protected void Load_Grid()
        {
            string connectionString;
            connectionString = ConfigurationManager.ConnectionStrings["conStr"].ToString();

            SqlConnection con = new SqlConnection(connectionString);

            try
            {
                con.Open();
            }
            catch (Exception)
            {
                con.Close();
                return;
                throw;
            }

            SqlCommand cmd = new SqlCommand("SELECT C.*, A.agent_name FROM Customers AS C INNER JOIN Users AS U ON C.user_id = U.user_id INNER JOIN Employees AS E ON E.user_id = U.user_id INNER JOIN Agents AS A ON A.agent_id = E.agent_id", con);
            SqlDataReader dr = cmd.ExecuteReader();
            DataTable tablo = new DataTable();
            tablo.Load(dr);

            CustomerGrid.DataSource = tablo.DefaultView;
            CustomerGrid.DataBind();

            dr.Close();
            con.Close();
        }
Exemplo n.º 58
0
        public static DataTable ExecuteSqlQuery(string query, params SqlCeParameter[] sqlParams)
        {
            var dt = new DataTable();

            using (var conn = new SqlCeConnection(connStr))
            using (var cmd = new SqlCeCommand(query, conn))
            {
                try
                {
                    SqlCeEngine engine = new SqlCeEngine(conn.ConnectionString);
                    engine.Upgrade(conn.ConnectionString);
                }
                catch
                {
                }

                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddRange(sqlParams);

                conn.Open();
                dt.Load(cmd.ExecuteReader());
            }

            return dt;
        }
Exemplo n.º 59
0
        /// <summary>
        /// Reads the selected file and places the contents into a DataTable.
        /// </summary>
        /// <returns>Datatable of students in file</returns>
        public DataTable ReadFile()
        {
            DataTable data = new DataTable();

            try
            {
                using (cmd = con.CreateCommand())
                {
                    cmd.CommandText = string.Format("SELECT * from [{0}]", file.Name);
                    con.Open();

                    using (dr = cmd.ExecuteReader())
                    {
                        data.Load(dr);
                    }
                }
            }
            catch (OleDbException oExe)
            {
                Debug.WriteLine(oExe.Message);
            }
            finally
            {
                con.Close();
            }

            return data;
        }
Exemplo n.º 60
0
        public static DataTable GetUser(string username, int userID)
        {
            DataTable user = new DataTable();
            using (SqlConnection objConn = new SqlConnection(WebConfigurationManager.ConnectionStrings["eshopConnectionString"].ConnectionString))
            {
                using (SqlCommand objComm = new SqlCommand("SELECT userID, firstName, lastName, address, city, phone, email, username FROM [user]", objConn))
                {
                    if (username != string.Empty)
                    {
                        objComm.CommandText += " WHERE username=@username";
                        objComm.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = username;
                    }
                    else if (userID > 0)
                    {
                        objComm.CommandText += " WHERE userID=@userID";
                        objComm.Parameters.Add("@userID", SqlDbType.Int).Value = userID;
                    }

                    objConn.Open();

                    using (SqlDataReader reader = objComm.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            user = new DataTable();
                            user.Load(reader);
                        }
                    }
                }
            }
            return user;
        }