C# SQLiteDataAdapter.Fill方法代码示例

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            string dbConnectionString = @"Data Source=patient.db;Version=3;";
            SQLiteConnection sqlite_connection = new SQLiteConnection(dbConnectionString);

            try
            {
                sqlite_connection.Open();
                string query = "select * from patient_table";

                SQLiteCommand create_command = new SQLiteCommand(query, sqlite_connection);
                create_command.ExecuteNonQuery();
                //SQLiteDataReader dr = create_command.ExecuteReader();

                SQLiteDataAdapter dataAdapt = new SQLiteDataAdapter(query, sqlite_connection);
                //var ds = new DataSet();

                DataTable dt = new DataTable("patient_table");
                //dataAdapt.Fill(ds,"patient_table");
                dataAdapt.Fill(dt);
               // listView1.DataContext = ds.Tables["patient_table"].DefaultView;
                admin_monthly_accounting_datagrid.ItemsSource = dt.DefaultView ;
                dataAdapt.Update(dt);

                sqlite_connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

		static void Main(string[] args)
		{
			// Create sqlite connection
			SQLiteConnection connection = new SQLiteConnection(string.Format(@"Data Source={0}\SimpleDatabase.s3db", Environment.CurrentDirectory));

			// Open sqlite connection
			connection.Open();

			// Get all rows from example_table
			SQLiteDataAdapter db = new SQLiteDataAdapter("SELECT * FROM Names", connection);

			// Create a dataset
			DataSet ds = new DataSet();

			// Fill dataset
			db.Fill(ds);

			// Create a datatable
			DataTable dt = new DataTable("Names");
			dt = ds.Tables[0];

			// Close connection
			connection.Close();

			// Print table
			foreach (DataRow row in dt.Rows)
			{
				Console.WriteLine(string.Format("{0} {1}", row["Firstname"], row["Surname"]));
			}

			Console.ReadLine();
		}

        private void Consulta_Load(object sender, EventArgs e)
        {
            string appPath = Path.GetDirectoryName(Application.ExecutablePath);
            string connString = @"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;";

            DataSet DS = new DataSet();
            SQLiteConnection con = new SQLiteConnection(connString);
            con.Open();
            SQLiteDataAdapter DA = new SQLiteDataAdapter("select * from Expediente", con);
            DA.Fill(DS, "Expediente");
            dataGridView1.DataSource = DS.Tables["Expediente"];
            con.Close();

            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;

            int i = 0;
            foreach (DataGridViewColumn c in dataGridView1.Columns)
            {
                i += c.Width;

            }
            if ((i + dataGridView1.RowHeadersWidth + 2) > 616)
            {
                dataGridView1.Width = 616;
            }
            else
            {
                dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2;
            }
        }

 /// <summary>
 /// 执行数据库查询，返回DataSet对象
 /// </summary>
 /// <param name="connectionString">连接字符串</param>
 /// <param name="commandText">执行语句或存储过程名</param>
 /// <param name="commandType">执行类型</param>
 /// <returns>DataSet对象</returns>
 public DataSet ExecuteDataSet(string connectionString, string commandText, CommandType commandType)
 {
     if (connectionString == null || connectionString.Length == 0)
         throw new ArgumentNullException("connectionString");
     if (commandText == null || commandText.Length == 0)
         throw new ArgumentNullException("commandText");
     DataSet ds = new DataSet();
     SQLiteConnection con = new SQLiteConnection(connectionString);
     SQLiteCommand cmd = new SQLiteCommand();
     SQLiteTransaction trans = null;
     PrepareCommand(cmd, con, ref trans, false, commandType, commandText);
     try
     {
         SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
         sda.Fill(ds);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         if (con != null)
         {
             if (con.State == ConnectionState.Open)
             {
                 con.Close();
             }
         }
     }
     return ds;
 }

        public CookieCollection GetCookie()
        {
            string value = "";
            const string DOMAIN = ".nicovideo.jp";
            const string KEY = "user_session";
            const string TABLE = "moz_cookies";
            string QUERY = string.Format("SELECT * FROM {2}  WHERE host = \"{0}\" AND name = \"{1}\"", DOMAIN, KEY, TABLE);
            using (SQLiteConnection con = new SQLiteConnection("Data Source=" + filepath))
            using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(QUERY, con))
            {
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                try
                {
                    value = (string)ds.Tables[0].Rows[0][2];
                }
                catch (Exception e)
                {
                }
            }

            CookieCollection cookies = new CookieCollection();
            cookies.Add(new Cookie(KEY, value, "/", DOMAIN));

            return cookies;
        }

        public int CheckcustomerID(int customerid)
        {
            string sql = "select * from Listcustomer where [email protected] ";
            try
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@customerid", customerid);
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                if (dt.Rows.Count>0)
                    return 1;
                else
                    return 0;
            }
            catch (Exception ex)
            {
                return 0;
            }
            finally
            {
                conn.Close();
            }
            return 1;
        }

 /// <summary>
 /// Получить данные из таблицы
 /// </summary>
 /// <param name="databasename">Имя таблицы</param>
 /// <param name="where">Условия</param>
 /// <param name="etc">Остальные параметры: сортировка, группировка и т.д.</param>
 /// <returns>Таблица с данными</returns>
 public DataTable FetchAll(string databasename, string where, string etc)
 {
     DataTable dt = new DataTable();
     string sql = string.Format("SELECT * FROM {0} {1} {2}", databasename, where, etc);
     ConnectionState previousConnectionState = ConnectionState.Closed;
     using (SQLiteConnection connect = new SQLiteConnection(ConnectionString))
     {
         try
         {
             previousConnectionState = connect.State;
             if (connect.State == ConnectionState.Closed)
             {
                 connect.Open();
             }
             SQLiteCommand command = new SQLiteCommand(sql, connect);
             SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
             adapter.Fill(dt);
         }
         catch (Exception error)
         {
             System.Windows.Forms.MessageBox.Show(error.Message, "Ошибка при получении данных из базы", MessageBoxButtons.OK, MessageBoxIcon.Error);
             return null;
         }
         finally
         {
             if (previousConnectionState == ConnectionState.Closed)
             {
                 connect.Close();
             }
         }
     }
     return dt;
 }

 /// <summary>
 /// 对SQLite数据库执行Insert操作，并返回rowID。
 /// </summary>
 /// <param name="sql">要执行的Insert SQL语句</param>
 /// <param name="parameters">执行Insert语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
 /// <returns>RowID</returns>
 public static int ExcuteInsertReturnRowID(string sql, SQLiteParameter[] parameters = null)
 {
     int rowID = -1;
     int affectedRows;
     using (SQLiteConnection connection = new SQLiteConnection(connectionString))
     {
         connection.Open();
         using (DbTransaction transaction = connection.BeginTransaction())
         {
             using (SQLiteCommand command = new SQLiteCommand(connection))
             {
                 command.CommandText = sql;
                 if (parameters != null)
                 {
                     command.Parameters.AddRange(parameters);
                 }
                 affectedRows = command.ExecuteNonQuery();
             }
             transaction.Commit();
         }
         if (affectedRows == 0)
         {
             return rowID;
         }
         string getRowIDSql = "select last_insert_rowid()";
         using (SQLiteCommand getRowIDCmd = new SQLiteCommand(getRowIDSql, connection))
         {
             SQLiteDataAdapter adapter = new SQLiteDataAdapter(getRowIDCmd);
             DataTable data = new DataTable();
             adapter.Fill(data);
             rowID = Convert.ToInt32(data.Rows[0][0]);
         }
     }
     return rowID;
 }

        protected DataTable loadEntsDtBySql(string sql)
        {
            DataTable ret = new DataTable("mytable");

            try
            {
                open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.CommandText = sql;
                cmd.Connection = conn;

                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                da.Fill(ret);

                close();

                return ret;

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

		public static DataTable SelectQueryNew (string query, string tablename)
		{
			//http://lists.ximian.com/pipermail/mono-list/2005-June/027584.html
			checkOperatingSystem ();
			/*IDbConnection dbcon;
			dbcon = (IDbConnection) new SqliteConnection(CONstr);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            dbcmd.CommandText = query;
            IDataReader reader = dbcmd.ExecuteReader();
			DataTable dt = new DataTable();
			dt.Load(reader);
			dt.AcceptChanges();*/
			try
			{
				DataTable dt = new DataTable();
				using (SQLiteConnection CON = new SQLiteConnection(CONstr))
				{
					SQLiteCommand CMD = new SQLiteCommand(query, CON);
					DataSet dataset = new DataSet();
					SQLiteDataAdapter AD = new SQLiteDataAdapter(CMD);
					AD.Fill(dataset, tablename);
					dt = dataset.Tables[0];
				}
				return dt;

			}
			catch(Exception Ex)
			{
				return new DataTable();
			}
		}

        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public int ExecuteDataTable(StringBuilder sql, SQLiteParameter[] parameters, ref DataTable dt)
        {
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), connection))
                    {
                        if (parameters != null)
                        {
                            command.Parameters.AddRange(parameters);
                        }

                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                        adapter.Fill(dt);
                        return 0;
                    }
                }

            }
            catch (Exception ex)
            {
                return -1;
                throw new Exception(ex.Message);
            }
        }

        public static DataTable ExecuteReader(string sqlitefn, string command, List<SqliteParam> parameters, out string errorMessage)
        {
            DataTable dt = null;
            try
            {
                errorMessage = "";
                using (var conn = new SQLiteConnection(sqlitefn))
                {
                    conn.Open();
                    var commandSql = new SQLiteCommand(command, conn);
                    if (parameters != null && parameters.Count > 0)
                    {
                        foreach (SqliteParam param in parameters)
                        {
                            commandSql.Parameters.Add(param.Name, param.Type).Value = param.Value;
                        }

                    }
                    dt = new DataTable();
                    var sqlDa = new SQLiteDataAdapter(commandSql);
                    sqlDa.Fill(dt);
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                errorMessage = ex.ToString();
                dt = null;
            }
            return dt;
        }

        public static void Copy()
        {
            DataSet DS = new DataSet();
            string ConnectionString = "Data Source=" + CalibrationSettings.Default.ConnectionDir + "Users.db;Version=3;New=False;Compress=True;";
            string LoadCommand = "SELECT * FROM users";
            DS.Clear();
            SQLiteConnection Connection = new SQLiteConnection(ConnectionString);
            //SQLiteCommand Query = new SQLiteCommand(LoadCommand, Connection);
            SQLiteDataAdapter DataAdaptor = new SQLiteDataAdapter(LoadCommand, Connection);
            DataAdaptor.Fill(DS);

            foreach (DataRow dRow in DS.Tables[0].Rows)
            {
                DataClasses.UserProfile CopyUser = new UserProfile();
                CopyUser.Gender = dRow[0].ToString();
                CopyUser.Name = dRow[2].ToString();
                CopyUser.Surname = dRow[3].ToString();
                CopyUser.Weight = Convert.ToInt32(dRow[5]);
                CopyUser.Password = dRow[6].ToString() + "000";
                if (dRow[1].ToString() == "255")
                    CopyUser.Name = "Richard";
                CopyUser.Name = new String(CopyUser.Name.Where(x => x != ' ' && x != '\r' && x != '\n').ToArray());
                CopyUser.EmailAddress = CopyUser.Name + dRow[1].ToString() + "@grucox.com";
                AccessorUsers.RegisterNewUser(CopyUser);
            }
        }

        // datagridview de veriler görüntülenecek...
        public void btnverilerigetir_Click(object sender, EventArgs e)
        {
            dt = null;
            if (!string.IsNullOrEmpty(txttabloisim.Text.Trim()))
            {
                SQLiteDataAdapter da = new SQLiteDataAdapter();
                if (sayi == 1)
                {
                    da = db.verigetir(txttabloisim.Text);
                }

                else
                {
                    da = proje.verigetir(txttabloisim.Text);
                }

                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    dataGridView1.DataSource = dt;

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

            else
                MessageBox.Show("Tablo ismini giriniz.");
        }

        private void BajaUsuarios_Load(object sender, EventArgs e)
        {
            string appPath = Path.GetDirectoryName(Application.ExecutablePath);
            string connString = @"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;";

            DataSet DS = new DataSet();
            SQLiteConnection con = new SQLiteConnection(connString);
            con.Open();
            SQLiteDataAdapter DA = new SQLiteDataAdapter("select Nombre,Usuario,TipoDeUsuario from Usuarios", con);
            DA.Fill(DS, "Usuarios");
            dataGridView1.DataSource = DS.Tables["Usuarios"];
            con.Close();

            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;

            int i = 0;
            foreach (DataGridViewColumn c in dataGridView1.Columns)
            {
                i += c.Width;

            }
            if ((i + dataGridView1.RowHeadersWidth + 2) > 616)
            {
                dataGridView1.Width = 616;
                dataGridView1.Left = 79;
            }
            else
            {
                dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2;
                dataGridView1.Left = 211;
            }
        }