C# DbDataAdapter.Fill方法代码示例

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                string[] df = comboBox1.Text.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                string fid = df[df.Count() - 1].Trim();
                df = comboBox2.Text.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
                string sid = df[df.Count() - 1].Trim();
                Adapter = DbProvider.GetDataAdapter("SELECT subject1, subject2, subject3, subject4, subject5 FROM Subjects WHERE (Faculty_ID='" + fid + "'AND Spec_ID='" + sid + "'AND SesionNo='" + int.Parse(textBox1.Text).ToString() + "')", ORM.DB_University.connection);
                Table = new DataTable();
                Adapter.Fill(Table);
                if (Table.Rows.Count == 0)
                {
                    for (int i = 0; i < 5; i++)
                        dataGridView1[i, 0].Value = "";
                    MessageBox.Show("0 rows");
                    return;
                }
                for (int i = 0; i < 5; i++)
                    dataGridView1[i, 0].Value = Table.Rows[0][i].ToString().Trim();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {

                Adapter = DbProvider.GetDataAdapter("SELECT subject1, subject2, subject3, subject4, subject5 FROM Subjects WHERE (Faculty_ID='" + Faculty_ID.ToString()+ "'AND Spec_ID='" + Spec_ID.ToString() + "'AND SesionNo='" + int.Parse(textBox1.Text).ToString() + "')", ORM.DB_University.connection);
                Table = new DataTable();
                Adapter.Fill(Table);
                if (Table.Rows.Count == 0)
                {
                    throw new Exception("Invalid SesionNo");
                }

                for (int i = 0; i < 5; i++)
                    dataGridView1.Columns[i].HeaderText = Table.Rows[0][i].ToString().Trim();

                Adapter = DbProvider.GetDataAdapter("SELECT Mark1, Mark2, Mark3, Mark4, Mark5 FROM Marks WHERE (Student_ID='" + Stud_ID.ToString() + "' AND SesionNo='" + int.Parse(textBox1.Text).ToString() + "')", ORM.DB_University.connection);
                Table = new DataTable();
                Adapter.Fill(Table);
                if (Table.Rows.Count == 0)
                {
                    for (int i = 0; i < 5; i++)
                        dataGridView1[i, 0].Value = "";
                    MessageBox.Show("0 rows");
                    return;
                }
                for (int i = 0; i < 5; i++)
                    dataGridView1[i, 0].Value = Table.Rows[0][i].ToString().Trim();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public TableInform( string tableName )
        {
            try
            {
                dataAdapter = TableInform.dbProviderFactory.CreateDataAdapter();

                command = TableInform.dbProviderFactory.CreateCommand();
                command.Connection = TableInform.Connection;

                DbCommandBuilder commandBuilder = TableInform.dbProviderFactory.CreateCommandBuilder();
                commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                commandBuilder.DataAdapter = dataAdapter;

                dataTable = new DataTable();
                dataTable.TableName = tableName;

                command.CommandText = "Select * from " + Table.TableName;

                dataAdapter.SelectCommand = command;
                dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();

                dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
                dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();

                dataAdapter.Fill( dataTable );
            }
            catch ( Exception ex )
            { }
        }

        protected void CreateFile(string database, string tableName, IDbCommand cmd, DbDataAdapter da)
        {
            cmd.CommandText = string.Format(TableInfoSQL, database.ToLower(), tableName.ToLower());
            DataTable dt = new DataTable();
            da.Fill(dt);

            cmd.CommandText = string.Format(TableCommentSQL, database.ToLower(), tableName.ToLower());
            string tableComments = (cmd.ExecuteScalar() ?? string.Empty).ToString();

            CreateFile(tableName.ToUpper(), tableComments, dt);
        }

 private void frmanimals_Load(object sender, EventArgs e)
 {
     DbCommand idbCommand = Connection.provideConnection().CreateCommand();
     idbCommand.CommandText = "select * from `animals`";
     ad = returnAdapter();
     DbCommandBuilder builder = returnBuilder();
     builder.DataAdapter = ad;
     ad.SelectCommand = idbCommand;
     ad.Fill(this.newDataSet.animals);
     ad.DeleteCommand = builder.GetDeleteCommand();
     ad.UpdateCommand = builder.GetUpdateCommand();
     ad.InsertCommand = builder.GetInsertCommand();
 }

 public DataSet GetData(DbDataAdapter adapter, string CommandText, CommandType cmdType, DbParameter[] parameters = null)
 {
     DataSet ds = new DataSet();
     _cmd.CommandText = CommandText;
     _cmd.CommandType = cmdType;
     if (parameters != null && parameters.Count() > 0)
     {
         _cmd.Parameters.AddRange(parameters);
     }
     adapter.SelectCommand = _cmd;
     adapter.Fill(ds);
     return ds;
 }

        public SubjectsForm()
        {
            InitializeComponent();
            dataGridView1.Rows.Add(1);

            Adapter = DbProvider.GetDataAdapter("SELECT Name, Object_ID FROM UObjects WHERE Class_ID='20'", ORM.DB_University.connection);
            Table = new DataTable();
            Adapter.Fill(Table);
            Fac = (from DataRow c in Table.AsEnumerable()
                   select (c[0].ToString().Trim() + " " + c[1].ToString().Trim())).ToList();
            comboBox1.DataSource = Fac;

            Adapter = DbProvider.GetDataAdapter("SELECT Spec, Spec_ID FROM Spec", ORM.DB_University.connection);
            Table = new DataTable();
            Adapter.Fill(Table);
            Spc = (from DataRow c in Table.AsEnumerable()
                   select (c[0].ToString().Trim() + " " + c[1].ToString().Trim())).ToList();
            comboBox2.DataSource = Spc;
        }

        public DataSet GetDataSet(string sql)
        {
            // Description: fill DataSet via OleDbDataAdapter, connect Firebird, Interbase
            using (var ds = new DataSet())
            {
                try
                {
                    OpenFbData();

                    //Using not work with transaction
                    Com = GetCommandDb(sql);

                    Adapter = GetAdapterDb(Com);

                    Adapter.Fill(ds);
                }
                finally
                {
                    ReleaseResource();
                }

                return ds;
            }
        }

        public virtual DataTable GetTableFromDb(string tablename, bool with_primary_key, bool with_max_string_length, bool with_default_values, bool CaseSensivity)
        {
            _dt = new DataTable(tablename);
            string sql = "";

            if (!CaseSensivity)
            {
                sql = "select * from {0}";
                if (!string.IsNullOrEmpty(_owner))
                {
                    sql = "select * from {0}.{1}";
                    sql = string.Format(sql, _owner, tablename);
                }
                else
                    sql = string.Format(sql, tablename);
            }
            else
            {
                sql = "select * from {0}{1}{2}";
                if (!string.IsNullOrEmpty(_owner))
                {
                    sql = "select * from {0}{1}{2}.{0}{3}{2}";
                    sql = string.Format(sql, _open_bracket, _owner, _close_bracket, tablename);
                }
                else
                    sql = string.Format(sql, _open_bracket, tablename, _close_bracket);
            }

            _DA = CreateDataAdapter(sql);
            _DA.FillLoadOption = LoadOption.Upsert;//--Важднейшая опция, после которой можно вставить эту таблицу в другую базу
            _DA.FillSchema(_dt, SchemaType.Source);
            PrepareTableSchemeBeforeFill(_dt);
            _DA.Fill(_dt);

            #warning перенести код ниже в PrepareTableSchemeBeforeFill
            //--описание колонок
            foreach (DataColumn col in _dt.Columns)
            {
                var col_info = GetDbColumnInformation(tablename, col.ColumnName, CaseSensivity);
                if (col_info.AdditionalInfo!=null && col_info.AdditionalInfo.ToString() != "default")
                    col.AllowDBNull = col_info.IsNullable;
                if (with_max_string_length)
                {
                    if (col.DataType == typeof(string))
                        if (col_info.MaxLength > 0 && col_info.MaxLength > col.MaxLength)
                            col.MaxLength = col_info.MaxLength;
                }

                if (with_default_values)
                {
                    //--надо будет придумать какой-то флаг, чтобы сообщалось что не все дефолтные значения распарсились
                    if (col_info.DefaultValue != null && col_info.DefaultValue != DBNull.Value)
                    {
                        try
                        {
                            col.DefaultValue = col_info.DefaultValue;
                        }
                        catch
                        {
                            col.DefaultValue = DBNull.Value;
                        }
                    }
                }
            }

            if (with_primary_key)
                _dt = GetTablePrimaryKey(_dt, CaseSensivity);

            return _dt;
        }

        virtual public DataSet queryDatabase(string qstring)
        {
            try
            {
                int rows;
                DataSet ds = new DataSet();

                // Create the DbCommand.

                DbCommand SelectTableCommand = factory.CreateCommand();
                SelectTableCommand.CommandText = qstring;
                SelectTableCommand.Connection = connection;

                adapter = factory.CreateDataAdapter();
                adapter.SelectCommand = SelectTableCommand;

                rows = adapter.Fill(ds);

                return ds;
            }
            catch (Exception exp)
            {
                throw;
            }
        }

		/** 
		 * Creates a DataSet object for the specified adapter.
		 * 
		 *  @param  adapter     System.Data.DBDataAdapter object.
		 *  @param  dset        System.Data.DataSet object to fill.
		 *  @return A reference to the System.Data.DataSet object.
		 * 
		 *  @author     Martin Turon
		 *  @version    2004/4/13    mturon      Initial version
		 */
		public DataSet FillDataSet(DbDataAdapter dataAdapter, DataSet ds) 
		{
			if (null == ds) 
			{
				ds = new DataSet();
			}
			
			try 
			{
				dataAdapter.Fill(ds, m_Table);
			} 
			catch (Exception ex)
			{
				ErrorLog("\nError: DataSet fill\n"	+ ex.Message);
				ds = null;
			}

			return ds;
		}

        // �������ʱ��������ݼ�
        private void SearchFriendForm_Load(object sender, EventArgs e)
        {
            // ʵ�������ݼ������������������
            string sql = "SELECT Id, NickName, Age, Sex FROM Users";
            dataAdapter = DBHelper.GetDataAdapter(sql, DBHelper.GetConnection());
            dataSet = new DataSet("MyQQ");
            dataAdapter.Fill(dataSet, "Users");

            // ָ��DataGridView������Դ
            dgvBasicResult.DataSource = dataSet.Tables[0];
            dgvAdvancedResult.DataSource = dataSet.Tables[0];
        }

        /// <summary>
        /// 返回DataSet--重载方法三
        /// </summary>
        /// <param name="sql">SQL语句或者存储过程</param>
        /// <param name="startindex">开始行</param>
        /// <param name="num">总行数</param>
        /// <param name="dataname">虚拟表名</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string sql, int startindex, int num, string dataname)
        {
            DataSet ds = new DataSet();
            IsStoredProcedure(sql);
            dbDataAdapter = dbProviderFactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = dbCommand;

            dbDataAdapter.Fill(ds, startindex, num, dataname);
            dbDataAdapter.Dispose();
            m_querycount++;
            return ds;
        }

 /// <summary>
 /// 执行查询 返回DataTable
 /// </summary>
 /// <param name="strCmdText"></param>
 /// <param name="arg0"></param>
 /// <param name="commandType"></param>
 /// <returns></returns>
 public DataTable ExecuteQuery(string strCmdText, DbParameter arg0, CommandType commandType)
 {
     DataTable dt = new DataTable();
     try
     {
         Database db = GetDataBase();
         _cmd = db.DbProviderFactory.CreateCommand();
         _cmd.Connection = db.CreateConnection();
         _cmd.CommandText = strCmdText;
         _cmd.CommandTimeout = TimeOut;
         _cmd.CommandType = commandType;
         _cmd.Parameters.Add(arg0);
         _da = db.DbProviderFactory.CreateDataAdapter();
         _da.SelectCommand = _cmd;
         _da.Fill(dt);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         Dispose();
     }
     return dt;
 }

        /// <summary>
        /// Ejecuta un procedimiento almacenado de consulta
        /// </summary>
        /// <param name="nombrePA">Nombre del procedimiento</param>
        /// <returns>Conjunto de datos devuelto por el procedimiento</returns>
        public DataSet ejecutarProcedimiento(string nombrePA)
        {
            m_comando.CommandText = nombrePA;
            m_comando.CommandType = CommandType.StoredProcedure;
            m_dataset = new DataSet();
            m_adaptador = ObtenerFabrica().CreateDataAdapter();
            if (m_adaptador != null) m_adaptador.SelectCommand = m_comando;
            Abrir();
            try
            {
                if (m_adaptador != null) m_adaptador.Fill(m_dataset);
            }
            catch (Exception ex)
            {
                throw new Exception("Error al ejecutar el Procedimiento Almacenado. " + ex.Message);
            }
            finally
            {
                Cerrar();
            }

            return m_dataset;
        }