C# SqlCeDataAdapter.Fill方法代码示例

        private void Modify_RENT_Load(object sender, EventArgs e)
        {
            //get every car's name, type and number. This datas will appear as combobox items
            DataSet carRes = new DataSet();
            SqlCeDataAdapter adapter = new SqlCeDataAdapter();
            SqlCeCommand cmd = Form1.con.CreateCommand();
            //fill Dataset with datas
            cmd.CommandText = "SELECT ID, Marka, Tipus, Rendszam FROM Autok";
            adapter.SelectCommand = cmd;
            adapter.Fill(carRes, "Autok");
            //fill "cars" Dictionary with cars datas from from Dataset
            //Dictionary data will be displayed in the combobox
            int row = carRes.Tables["Autok"].Rows.Count - 1;
            Dictionary<int, String> cars = new Dictionary<int, String>();

            for (int r = 0; r <= row; r++)
            {
                String display_info = String.Concat(carRes.Tables["Autok"].Rows[r].ItemArray[1].ToString(), " ",
                    carRes.Tables["Autok"].Rows[r].ItemArray[2].ToString(), " ", carRes.Tables["Autok"].Rows[r].ItemArray[3].ToString());
                cars.Add((int)carRes.Tables["Autok"].Rows[r].ItemArray[0], display_info);
            }

            comboBox1.DataSource = new BindingSource(cars,null);
            comboBox1.DisplayMember = "Value";
            comboBox1.ValueMember = "Key";
            comboBox1.SelectedValue = carID;

            //get Clients data
            Dictionary<String, String> clients = new Dictionary<String, String>();
            DataSet clientRes = new DataSet();
            cmd.CommandText = "SELECT ID, Kereszt_nev, Vezetek_nev FROM Ugyfelek";
            adapter.SelectCommand = cmd;
            adapter.Fill(clientRes, "Ugyfelek");

            row = clientRes.Tables["Ugyfelek"].Rows.Count - 1;
            //fill Clients dictionary with information about clients
            for (int r = 0; r <= row; r++)
            {
                String display_info = String.Concat(clientRes.Tables["Ugyfelek"].Rows[r].ItemArray[1].ToString(), " ",
                    clientRes.Tables["Ugyfelek"].Rows[r].ItemArray[2].ToString());
                clients.Add(clientRes.Tables["Ugyfelek"].Rows[r].ItemArray[0].ToString(), display_info);
            }

            comboBox2.DataSource = new BindingSource(clients, null);
            comboBox2.DisplayMember = "Value";
            comboBox2.ValueMember = "Key";
            comboBox2.SelectedValue = clientID;

            //get information about selected sale and set controls values
            DataSet current_rent = new DataSet();
            cmd.CommandText = "SELECT Kezdeti_ido, Veg_ido FROM Berles WHERE Auto_ID = @carid AND Ugyfel_ID = @clientid";
            cmd.Parameters.AddWithValue("@carid", carID);
            cmd.Parameters.AddWithValue("@clientid", clientID);
            adapter.SelectCommand = cmd;
            adapter.Fill(current_rent, "Berles");
            DateTime current_date = Convert.ToDateTime(current_rent.Tables["Berles"].Rows[0].ItemArray[0].ToString());
            dateTimePicker1.Value = current_date;
            current_date = Convert.ToDateTime(current_rent.Tables["Berles"].Rows[0].ItemArray[1].ToString());
            dateTimePicker2.Value = current_date;
        }

        private void Modify_SELL_Load(object sender, EventArgs e)
        {
            SqlCeCommand cmd = Form1.con.CreateCommand();
            cmd.CommandText = "SELECT ID, Marka, Tipus, Rendszam FROM Autok";
            SqlCeDataAdapter adapter = new SqlCeDataAdapter();
            //fill Dataset with datas
            DataSet carRes = new DataSet();
            adapter.SelectCommand = cmd;
            adapter.Fill(carRes, "Autok");
            int row = carRes.Tables["Autok"].Rows.Count - 1;
            Dictionary<int, String> cars = new Dictionary<int, String>();
            //fill "cars" Dictionary with cars datas from from Dataset
            //Dictionary data will be displayed in the combobox
            for (int r = 0; r <= row; r++)
            {
                String display_info = String.Concat(carRes.Tables["Autok"].Rows[r].ItemArray[1].ToString(), " ",
                    carRes.Tables["Autok"].Rows[r].ItemArray[2].ToString(), " ", carRes.Tables["Autok"].Rows[r].ItemArray[3].ToString());
                cars.Add((int)carRes.Tables["Autok"].Rows[r].ItemArray[0], display_info);
            }

            //set combobox properties
            comboBox1.DataSource = new BindingSource(cars, null);
            comboBox1.DisplayMember = "Value";
            comboBox1.ValueMember = "Key";
            comboBox1.SelectedValue = carID;

            //get Clients data
            Dictionary<String, String> clients = new Dictionary<String, String>();
            DataSet clientRes = new DataSet();
            cmd.CommandText = "SELECT ID, Kereszt_nev, Vezetek_nev FROM Ugyfelek";
            adapter.SelectCommand = cmd;
            adapter.Fill(clientRes, "Ugyfelek");

            row = clientRes.Tables["Ugyfelek"].Rows.Count - 1;
            //fill Clients dictionary with information about clients
            for (int r = 0; r <= row; r++)
            {
                String display_info = String.Concat(clientRes.Tables["Ugyfelek"].Rows[r].ItemArray[1].ToString(), " ",
                    clientRes.Tables["Ugyfelek"].Rows[r].ItemArray[2].ToString());
                clients.Add(clientRes.Tables["Ugyfelek"].Rows[r].ItemArray[0].ToString(), display_info);
            }

            //fill second combobox with datas from Dictionary, set combobox properties
            comboBox2.DataSource = new BindingSource(clients, null);
            comboBox2.DisplayMember = "Value";
            comboBox2.ValueMember = "Key";
            comboBox2.SelectedValue = clientID;

            //get information about selected sale and set controls values
            DataSet current_sale = new DataSet();
            cmd.CommandText = "SELECT Eladasi_datum, Fizetett FROM Eladas WHERE Auto_ID = @carid and Ugyfel_ID = @clientid";
            cmd.Parameters.AddWithValue("@carid", carID);
            cmd.Parameters.AddWithValue("@clientid", clientID);
            adapter.SelectCommand = cmd;
            adapter.Fill(current_sale, "Eladas");
            textBox1.Text = current_sale.Tables["Eladas"].Rows[0].ItemArray[1].ToString();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'angajatiDataSet1.info' table. You can move, or remove it, as needed.
            this.infoTableAdapter.Fill(this.angajatiDataSet1.info);

            var connString = (@"Data Source=" + System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)) + @"\Angajati.sdf");
            using (var conn = new SqlCeConnection(connString))
            {
                try
                {
                    conn.Open();
                    var query = "SELECT * FROM info WHERE Prenume='" + prenume + "' AND Nume='" + nume + "'";
                    var command = new SqlCeCommand(query, conn);
                    SqlCeDataAdapter da = new SqlCeDataAdapter(command);
                    SqlCeCommandBuilder cbd = new SqlCeCommandBuilder(da);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    var dataAdapter = new SqlCeDataAdapter(command);
                    var dataTable = new DataTable();
                    dataAdapter.Fill(dataTable);
                    dataAdapter.Fill(dataTable);
                    byte[] ap = (byte[])(ds.Tables[0].Rows[0]["Poza"]);
                    MemoryStream ms = new MemoryStream(ap);
                    pictureBox1.Image = Image.FromStream(ms);
                    pictureBox1.SizeMode = PictureBoxSizeMode.CenterImage;

                    label1.Text = dataTable.Rows[0][1].ToString();
                    label2.Text = dataTable.Rows[0][2].ToString();
                    label3.Text = dataTable.Rows[0][3].ToString();
                    label4.Text = dataTable.Rows[0][4].ToString();
                    label5.Text = dataTable.Rows[0][5].ToString();
                    label14.Text = (dataTable.Rows[0][7].ToString());
                    label21.Text = (dataTable.Rows[0][8].ToString());
                    textBox1.Text = (dataTable.Rows[0][9].ToString());
                    textBox2.Text = (dataTable.Rows[0][10].ToString());
                    textBox4.Text = (dataTable.Rows[0][11].ToString());
                    textBox5.Text = (dataTable.Rows[0][12].ToString());
                    textBox6.Text = (dataTable.Rows[0][13].ToString());
                    textBox7.Text = (dataTable.Rows[0][14].ToString());
                    textBox8.Text = (dataTable.Rows[0][15].ToString());

                    

                    double baza = label21.Text == "" ? 0 : double.Parse(label21.Text);
                    textBox10.Text = ((baza *75)/1000).ToString();
                    textBox11.Text = ((baza * 100) / 1000).ToString();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }

            }
        }

        public static DataTable GetContentsOf(string tableName)
        {
            using (var dbContext = new ProductsDbContext())
            using (var connection = new SqlCeConnection(dbContext.Database.Connection.ConnectionString))
            using (var command = connection.CreateCommand())
            {

                var sql = "SELECT* FROM " + tableName;
                command.CommandText = sql;

                var adapter = new SqlCeDataAdapter(command);
                var dataSet = new DataSet();
                adapter.Fill(dataSet);

                return dataSet.Tables[0];
            }

            //var connection = new SqlConnection(ConnectionString);

            //using (connection)
            //{
            //    connection.Open();
            //    var command = new SqlCommand("SELECT * FROM " + tableName, connection);

            //    var adapter = new SqlDataAdapter(command);
            //    var dataSet = new DataSet();
            //    adapter.Fill(dataSet);

            //    return dataSet.Tables[0];
            //}
        }

        /*  private void GoFullscreen(bool fullscreen)
          {
              if (fullscreen)
              {
                  this.WindowState = FormWindowState.Normal;
                  this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None;
                  this.Bounds = Screen.PrimaryScreen.Bounds;
              }
              else
              {
                  this.WindowState = FormWindowState.Maximized;
                  this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Sizable;
              }
          }*/

        private void Grupa_Load(object sender, EventArgs e)
        {
            string startPath = Application.StartupPath;
            var filepath = startPath + "\\" + "Grupe.sdf";
            var connString = (@"Data Source=" + filepath + "");
            using (var conn = new SqlCeConnection(connString))
            {
                try
                {
                    conn.Open();
                    var query = "SELECT * FROM grupe WHERE Nume='" + nume + "'";
                    var command = new SqlCeCommand(query, conn);
                    var dataAdapter = new SqlCeDataAdapter(command);
                    var dataTable = new DataTable();
                    dataAdapter.Fill(dataTable);

                    label1.Text = dataTable.Rows[0][0].ToString();
                    label2.Text = dataTable.Rows[0][1].ToString();
                    label3.Text = dataTable.Rows[0][2].ToString();
                    label4.Text = dataTable.Rows[0][3].ToString();
                    //label21.Text = dataTable.Rows[0][0].ToString();
                    textBox7.Text = nume;

                    refresh();
                    sume();
                    this.dataGridView1.Sort(this.dataGridView1.Columns["Nume"], ListSortDirection.Ascending);
                    colorRows();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

        public Boolean checkStoredata()
        {
            bm = this.txtBom.Text.ToUpper();
            Boolean isCheck = false;
            myConnection = default(SqlCeConnection);
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            Adapter = default(SqlCeDataAdapter);
            myConnection = new SqlCeConnection(storagePath.getDatabasePath());
            myConnection.Open();
            myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "SELECT [ID]  FROM [" + storagePath.getBomTable() + "] WHERE ID ='"
                + bm + "' ";

            myCommand.CommandType = CommandType.Text;

            Adapter = new SqlCeDataAdapter(myCommand);
            Adapter.Fill(dt);
            Adapter.Dispose();
            if (dt.Rows.Count > 0)
            {
                isCheck = true;

            }
            else
            {
                isCheck = false;
            }
            myCommand.Dispose();
            dt = null;
            myConnection.Close();
            return isCheck;
        }

        public void conectaBD()
        {
            SqlCeConnection PathBD = new SqlCeConnection("Data Source=C:\\Facturacion\\Facturacion\\BaseDeDatos.sdf;Persist Security Info=False;");
            //abre la conexion
            try
            {
                da = new SqlCeDataAdapter("SELECT * FROM USUARIOS ORDER BY ID_USUARIO", PathBD);
                // Crear los comandos de insertar, actualizar y eliminar
                SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
                // Asignar los comandos al DataAdapter
                // (se supone que lo hace automáticamente, pero...)
                da.UpdateCommand = cb.GetUpdateCommand();
                da.InsertCommand = cb.GetInsertCommand();
                da.DeleteCommand = cb.GetDeleteCommand();
                dt = new DataTable();
                // Llenar la tabla con los datos indicados
                da.Fill(dt);

                PathBD.Open();
            }
            catch (Exception w)
            {
                MessageBox.Show(w.ToString());
                return;
            }
        }

 public List<Appointment> getAppointmentList()
 {
     List<Appointment> AppointmentList = new List<Appointment>();
     SqlCeCommand cmd = new SqlCeCommand("select * from Appointment " +
                                         "inner join RoomInformation on Appointment.RoomID = RoomInformation.RoomID " +
                                         "inner join UserInformation on Appointment.UserID = UserInformation.User_ID " +
                                         "inner join UserInformation as RoomAccount on RoomInformation.UserID = RoomAccount.User_ID " +
                                         "where Appointment.RoomID = @RoomID and Appointment.UserID = @UserID", conn);
     cmd.Parameters.AddWithValue("@UserID", this._appointment.UserID);
     cmd.Parameters.AddWithValue("@RoomID", this._appointment.RoomID);
     SqlCeDataAdapter adapter = new SqlCeDataAdapter();
     adapter.SelectCommand = cmd;
     DataSet setdata = new DataSet();
     adapter.Fill(setdata, "Appointment");
     for (int i = 0; i < setdata.Tables[0].Rows.Count; i++)
     {
         Appointment model = new Appointment();
         model.AppointmentID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[0].ToString());
         model.RoomID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[1].ToString());
         model.UserID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[2].ToString());
         model.AppointmentDate = DateTime.Parse(setdata.Tables[0].Rows[i].ItemArray[3].ToString());
         model.Status = setdata.Tables[0].Rows[i].ItemArray[4].ToString();
         model.Respond = setdata.Tables[0].Rows[i].ItemArray[5].ToString();
         model.RoomAcc.RoomID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[1].ToString());
         model.RoomAcc.ApartmentName = setdata.Tables[0].Rows[i].ItemArray[7].ToString();
         model.RoomAcc.RoomForSale = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[8].ToString());
         model.RoomAcc.RoomForRent = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[9].ToString());
         model.Appointee = setdata.Tables[0].Rows[i].ItemArray[49].ToString();
         model.RoomAcc.Username = setdata.Tables[0].Rows[i].ItemArray[62].ToString();
         AppointmentList.Add(model);
     }
     return AppointmentList;
 }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //Prikaži u GV-u životinje
            string connectionString = WebConfigurationManager.ConnectionStrings["ZivotinjeCS"].ConnectionString;
            //objekt za povezivanje
            SqlCeConnection connection = new SqlCeConnection(connectionString);
            //Command za dohvat podataka
            SqlCeCommand command = new SqlCeCommand("SELECT * FROM zivotinje",connection);
            // Adapter koji će dohvatiti podatke
            SqlCeDataAdapter adapter = new SqlCeDataAdapter();
            adapter.SelectCommand = command;
            //Novi data set koji će imati kopiju baze
            DataSet dataSet = new DataSet();
            //napuni dataset
            adapter.Fill(dataSet);

            //Poveži podatke
            gv_zivotinje.DataSource = dataSet;
            //Sadaaa
            gv_zivotinje.DataBind();

        }
    }

		public IEnumerable<string> GetRecentFiles()
		{
            if (!General.IsRecentFilesSaved)
            {
                _recentFiles = null;
                return new List<string>();
            }

			if (_recentFiles == null)
			{
				_recentFiles = new List<string>();
				using (SqlCeCommand cmd = new SqlCeCommand("select Path from RecentFile order by ID desc", Program.GetOpenSettingsConnection()))
				using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
				{
					DataSet ds = new DataSet();
					da.Fill(ds);

					foreach (DataRow dr in ds.Tables[0].Rows)
					{
						_recentFiles.Add(Convert.ToString(dr[0]));
						if (_recentFiles.Count >= 4)
							break;
					}
				}
			}

			return _recentFiles;
		}

        private void button2_Click(object sender, EventArgs e)
        {
            try{
            dataGridView1.DataSource = null;
            dataGridView1.Rows.Clear();
            dataGridView1.Refresh();

            String searchText = textBox1.Text;
            String query = "SELECT * FROM owner_master WHERE OwnerName LIKE '%" + searchText + "%'";

            SqlCeDataAdapter adapter = new SqlCeDataAdapter(query, conn);
            SqlCeCommandBuilder commnder = new SqlCeCommandBuilder(adapter);
            DataTable dt = new DataTable();

            adapter.Fill(dt);
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                    dataGridView1.Rows.Add(dt.Rows[i][0], dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3]);

            }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

 public static void DeleteFolderFromDB(string folderPath, string dbFilePath)
 {
     using (SqlCeConnection con = CreateConnection(dbFilePath))
     {
         con.Open();
         SqlCeDataAdapter da = new SqlCeDataAdapter("Select * FROM Folders", con);
         da.DeleteCommand = new SqlCeCommand(
             "DELETE FROM Folders WHERE id = @original_id " +
             "and name = @original_name");
         da.DeleteCommand.Parameters.Add("@original_id", SqlDbType.Int, 0, "id");
         da.DeleteCommand.Parameters.Add("@original_name", SqlDbType.NVarChar, 255, "name");
         da.DeleteCommand.Connection = con;
         DataSet ds = new DataSet("Folder");
         DataTable dt = new DataTable("Folders");
         dt.Columns.Add(new DataColumn("id", typeof(int)));
         dt.Columns.Add(new DataColumn("name", typeof(string)));
         ds.Tables.Add(dt);
         da.Fill(ds, "Folders");
         int ind = -1;
         for (int i = 0; i < folderList.Count; i++)
         {
             if (folderList[i] == folderPath.Replace("'", "`"))
             {
                 ind = i;
                 break;
             }
         }
         string folderid = ds.Tables["Folders"].Rows[ind]["id"].ToString();
         dt.Rows[ind].Delete();
         da.Update(ds, "Folders");
         string sql = "DELETE FROM Songs WHERE folder_id = " + folderid;
         SqlCeCommand com = new SqlCeCommand(sql, con);
         com.ExecuteNonQuery();
     }
 }

        public Order GetEverything(int id)
        {
            // Old-fashioned (but trusty and simple) ADO.NET
            var connectionString = GetConnectionString();
            var connection = new SqlCeConnection(connectionString);

            // setup dataset
            var ds = new DataSet();
            ds.Tables.Add("Orders");  // matches our model or could use dbtable attribute to specify
            ds.Tables.Add("OrderLineItems");  // matches a collection property on our model or could use dbtable attribute to specify

            // because sql compact does not support multi-select queries in a single call we need to do them one at a time
            var sql = "SELECT * FROM Orders WHERE OrderId = @id;";  // this is inline sql, but could also be stored procedure or dynamic
            var cmd = new SqlCeCommand(sql, connection);
            cmd.Parameters.AddWithValue("@id", id);

            var da = new SqlCeDataAdapter(cmd);
            da.Fill(ds.Tables["Orders"]);

            // make second sql call for child line items
            sql = "SELECT * FROM OrderLineItems WHERE OrderId = @id";  // additional query for child details (line items)
            cmd = new SqlCeCommand(sql, connection);
            cmd.Parameters.AddWithValue("@id", id);

            da = new SqlCeDataAdapter(cmd);
            da.Fill(ds.Tables["OrderLineItems"]);

            // Map to object - this is the only pertinent part of the example
            // **************************************************************
            var order = Map<Order>.MapSingle(ds);
            // **************************************************************

            return order;
        }

        public List<ClientInfoModel> GetClientInfoModels()
        {
            try
            {
                List<ClientInfoModel> oResult = new List<ClientInfoModel>();
                DataTable oData = new DataTable();

                SqlCeDataAdapter oDataAdapter = new SqlCeDataAdapter("SELECT * FROM Client", Connection);
                oDataAdapter.Fill(oData);

                if (oData.Rows.Count > 0)
                {
                    foreach (DataRow oRow in oData.Rows)
                    {
                        ClientInfoModel oClient = new ClientInfoModel();

                        oClient.macAddress = oRow["Client_MacAdresse"].ToString();
                        oClient.admin = Convert.ToBoolean(oRow["Client_Administrator"]);
                        oClient.group = Convert.ToInt32(oRow["Client_Gruppe"]);
                        oClient.ID = Convert.ToInt32(oRow["Client_ID"]);
                        oClient.arc = Convert.ToString(oRow["Client_Arc"]);
                        oClient.pcName = oRow["Client_PCName"].ToString();

                        oResult.Add(oClient);
                    }
                }
                return oResult;
            }
            catch (Exception ex)
            {
                Diagnostics.WriteToEventLog(ex.Message, System.Diagnostics.EventLogEntryType.Error, 3011);
                return null;
            }
        }

        private void frmEditReceipt_Load(object sender, EventArgs e)
        {
            SqlCeConnection myConnection = default(SqlCeConnection);
            myConnection = new SqlCeConnection("Data source="
                + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
                + "\\GodDB.sdf;"));
            myConnection.Open();
            SqlCeCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "Select [id],[itemid],[batch],[qty],[zone],[channel],[receiptdate],[receiptby] from [receipt] " +
                " where id = '" + strID + "'";
            myCommand.CommandType = CommandType.Text;

            DataTable dt = new DataTable();
            SqlCeDataAdapter Adapter = default(SqlCeDataAdapter);
            Adapter = new SqlCeDataAdapter(myCommand);
            Adapter.Fill(dt);

            myConnection.Close();
            if (dt.Rows.Count > 0)
            {
                this.txtItemId.Text = dt.Rows[0]["itemid"].ToString();
                this.txtBatch.Text = dt.Rows[0]["batch"].ToString();
                this.txtQty.Text = dt.Rows[0]["qty"].ToString();
                this.txtZone.Text = dt.Rows[0]["zone"].ToString();
                this.txtChannel.Text = dt.Rows[0]["channel"].ToString();
                this.txtReceiptDate.Text = dt.Rows[0]["receiptdate"].ToString();
                this.txtReceiptBy.Text = dt.Rows[0]["receiptby"].ToString();
            }
            dt = null;
        }