C# SqlCeDataAdapter类代码示例

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

 private void refresh()
 {
     odData = odBox.Text;
     doData = doBox.Text;
     try
     {
         conn = new SqlCeConnection(connectionString);
         SqlCeDataAdapter dataadapter = new SqlCeDataAdapter("Select * from Seriale WHERE Data_Premiery BETWEEN '" + odData + "' AND '"
             + doData + "'", conn);
         DataSet ds = new DataSet();
         conn.Open();
         dataadapter.Fill(ds, "Seriale");
         conn.Close();
         tabela.DataSource = ds;
         tabela.DataMember = "Seriale";
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
     finally
     {
         if (conn != null) conn.Close();
     }
 }

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

		private static void Clean(string tableName)
		{
			if (!Program.Settings.General.UseCachedResults)
				return;

			string getMaxIDSql = string.Format("select max(ID) from {0};", tableName);
			long? maxID = null;

			using (SqlCeCommand cmd = new SqlCeCommand(getMaxIDSql, Program.GetOpenCacheConnection()))
			using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
			{
				DataSet ds = new DataSet();
				da.Fill(ds);

				if (ds.Tables[0].Rows.Count == 1)
				{
					if (!DBNull.Value.Equals(ds.Tables[0].Rows[0][0]))
						maxID = Convert.ToInt64(ds.Tables[0].Rows[0][0]);
				}
			}

			if (maxID != null)
			{
				maxID -= Program.Settings.General.CacheSize;
				if (maxID > 0)
				{
					string deleteSql = string.Format("delete from {0} where ID <= {1};", tableName, maxID);
					using (SqlCeCommand cmd = new SqlCeCommand(deleteSql, Program.GetOpenCacheConnection()))
					{
						cmd.ExecuteNonQuery();
					}
				}
			}
		}

        public Appointment getAppointment()
        {
            Appointment model = new 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 where Appointment.AppointmentID = @AppointmentID", conn);
            cmd.Parameters.AddWithValue("@AppointmentID", this._appointment.AppointmentID);
            SqlCeDataAdapter adapter = new SqlCeDataAdapter();
            adapter.SelectCommand = cmd;

            DataSet setdata = new DataSet();
            adapter.Fill(setdata, "Appointment");
            model.AppointmentID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[0].ToString());
            model.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString());
            model.UserID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[2].ToString());
            model.AppointmentDate = DateTime.Parse(setdata.Tables[0].Rows[0].ItemArray[3].ToString());
            model.Status = setdata.Tables[0].Rows[0].ItemArray[4].ToString();
            model.Respond = setdata.Tables[0].Rows[0].ItemArray[5].ToString();
            model.RoomAcc.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString());
            model.RoomAcc.ApartmentName = setdata.Tables[0].Rows[0].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.RoomAcc.RoomPrice = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[12].ToString());
            model.RoomAcc.Username = setdata.Tables[0].Rows[0].ItemArray [49].ToString ();
            Account AccountModel = new Account();
            AccountModel.ID = model.UserID;
            AccountRepository _accountRepository = new AccountRepository(AccountModel);
            model.Appointee = _accountRepository.getDataByID().username;
            return model;
        }

		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 GetData(string selectCommand)
        {
            try
            {
                // Specify a connection string. Replace the given value with a
                // valid connection string for a Northwind SQL Server sample
                // database accessible to your system.
                String connectionString = "Data Source=|DataDirectory|\\MyDatabase4.sdf";

                // Create a new data adapter based on the specified query.
                dataAdapter = new SqlCeDataAdapter(selectCommand, connectionString);

                // Create a command builder to generate SQL update, insert, and
                // delete commands based on selectCommand. These are used to
                // update the database.
                SqlCeCommandBuilder commandBuilder = new SqlCeCommandBuilder(dataAdapter);

                // Populate a new data table and bind it to the BindingSource.
                DataTable table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                dataAdapter.Fill(table);
                bindingSource1.DataSource = table;

                // Resize the DataGridView columns to fit the newly loaded content.
                dataGridView1.AutoResizeColumns(
                    DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
            }
            catch (SqlCeException)
            {
                MessageBox.Show("To run this example, replace the value of the " +
                    "connectionString variable with a connection string that is " +
                    "valid for your system.");
            }
        }

        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 modifica_copii_Load(object sender, EventArgs e)
        {
            var connString = (@"Data Source=" + System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)) + @"\Grupe.sdf");
            using (var conn = new SqlCeConnection(connString))
            {
                try
                {
                    conn.Open();
                    var query = "SELECT * FROM copii WHERE Nume='" + nume2 + "'";
                    MessageBox.Show(query);
                    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);

                    label5.Text = dataTable.Rows[0][1].ToString();
                    textBox2.Text = dataTable.Rows[0][2].ToString();
                    textBox3.Text = dataTable.Rows[0][3].ToString();
                    textBox4.Text = dataTable.Rows[0][4].ToString();
                    textBox5.Text = dataTable.Rows[0][5].ToString();

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

            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string cs = GetConnectionString();

            //need to upgrade to sql engine v4.0?
            if (!IsV40Installed())
            {
                SqlCeEngine engine = new SqlCeEngine(cs);
                engine.Upgrade();
            }

            //open connection
            SqlCeConnection sc = new SqlCeConnection(cs);

            //query customers
            string sql = "SELECT * FROM Customers";
            SqlCeCommand cmd = new SqlCeCommand(sql, sc);

            //create grid
            SqlCeDataAdapter sda = new SqlCeDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            dataGridView1.DataSource = dt;
            dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);

            //close connection
            sc.Close();
        }

        /// <summary>
        /// tipoTabela 1 = Endereço
        /// tipoTabela 2 = Locador
        /// tipoTabela 3 = Locatário
        /// tipoTabela 4 = Recibos Principais
        /// tipoTabela 5 = Recibos Locadores
        /// </summary>
        /// <param name="dsDados"></param>
        /// <param name="sdaDados"></param>
        /// <param name="tipoTabela"></param>
        private static void PreencheDataset(out DataSet dsDados, out SqlCeDataAdapter sdaDados, int tipoTabela)
        {
            sdaDados = new SqlCeDataAdapter();

            SqlCeCommand cmd = retornaConexao().CreateCommand();

            if (tipoTabela == 1)
            {
                cmd.CommandText = "select * from Imoveis where Ativo = 1";
            }
            else if (tipoTabela == 2)
            {
                cmd.CommandText = "select * from Locadores where Ativo = 1";
            }
            else if (tipoTabela == 3)
            {
                cmd.CommandText = "select * from Locatarios where Ativo = 1";
            }
            else if (tipoTabela == 4)
            {
                cmd.CommandText = "select * from RecibosPrincipais";
            }
            else if (tipoTabela == 5)
            {
                cmd.CommandText = "select * from RecibosLocadores";
            }

            sdaDados.SelectCommand = cmd;

            dsDados = new DataSet();
            sdaDados.Fill(dsDados);
        }

 public ListaFactura(DataTable dt,SqlCeDataAdapter da, int procedimiento)
 {
     InitializeComponent();
     this.dt = dt;
     this.da = da;
     proc = procedimiento;
 }

        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 void SelectUser(int x)
        {
            try
            {
                byte count = 0;
                SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Users", cKoneksi.Con);
                SqlCeDataReader dr;
                if (cKoneksi.Con.State == ConnectionState.Closed) { cKoneksi.Con.Open(); }
                dr = cmd.ExecuteReader();
                if (dr.Read()) { count = 1; } else { count = 0; }
                dr.Close(); cmd.Dispose(); if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
                if (count != 0)
                {
                    DataSet ds = new DataSet();
                    SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM Users", cKoneksi.Con);
                    da.Fill(ds, "Users");
                    textBoxUser.Text = ds.Tables["Users"].Rows[x][0].ToString();
                    textBoxPass.Text = ds.Tables["Users"].Rows[x][1].ToString();
                    checkBoxTP.Checked = Convert.ToBoolean(ds.Tables["Users"].Rows[x][2]);
                    checkBoxTPK.Checked = Convert.ToBoolean(ds.Tables["Users"].Rows[x][3]);

                    ds.Dispose();
                    da.Dispose();
                }
                else
                {
                    MessageBox.Show("Data User Kosong", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
                    buttonClose.Focus();
                }
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show(cError.ComposeSqlErrorMessage(ex), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
            }
        }