[DataSet]간단한 DataAdapter와 DataSet 만들기

DataSet 값 불러오기

    string sql = "SELECT * FROM TEST";
    //아답터 생성
    this._DataAdapter = new System.Data.OleDb.OleDbDataAdapter();

//아답터 쿼리가져오기
    _DataAdapter.SelectCommand = new System.Data.OleDb.OleDbCommand(sql , this._Conn );

    //데이터셋 만들기
    this._DataSet = new DataSet();

    //아답터 쿼리담기
    this._DataAdapter.Fill(this._DataSet );

    //테이블 만들기
    this._DataTable = this._DataSet.Tables[0];
this._DataRowCollection = this._DataTable.Rows;

    //Get Data
    foreach (DataRow dr in this._DataRowCollection){     

        //필드안에 값 불러오기

         for(int i=0; i< _DataTable.Columns.Count; i++){          
            this.richTextBox1.Text += "\n"+dr[i];     
         }
    }

업데이트하기

    System.Data.DataRow row  = this._DataTable.NewRow();
    row["comm"] = "8";  
    _DataTable.Rows.Add(row);   

    string sqlstr ="insert into test(comm) values(?);"; 
    this._DataAdapter.InsertCommand = new System.Data.OleDb.OleDbCommand( sqlstr  , this._Conn ); 
    this._DataAdapter.InsertCommand.Parameters.Add("comm",System.Data.OleDb.OleDbType.VarChar ,50 , "comm" );
    this._DataAdapter.Update( this._DataSet );
    this.dataGrid1.DataSource =  this._DataSet.Tables[0];

  }

using System;
using System.Data;
using System.Data.SqlClient;
public class DataSetHandleTest{
public static void Main(){
    string conStr = "Server=localhost;user id=sa;password=;database=northwind";
    string query = "select  * from Address";
    Console.WriteLine("1. Connenction 생성과 Open");
    SqlConnection conn = new SqlConnection(conStr);
    conn.Open();
    Console.WriteLine("2. SqlDataAdapter 생성");
    SqlDataAdapter adapter = new SqlDataAdapter();
    Console.WriteLine("3. Adapter에 SelectCommand 할당");
    adapter.SelectCommand = new SqlCommand(query, conn);
    Console.WriteLine("4. DataSet 생성");
    DataSet ds = new DataSet();
    Console.WriteLine("5. Adapter를 통해서 DataSet 채우기");
    adapter.Fill(ds);  
    Console.WriteLine("6. Connection 닫기");
    conn.Close();
    Console.WriteLine("7. DataSet으로 작업하기");
    DataTable table= ds.Tables[0];
    DataRowCollection rows = table.Rows;
    foreach (DataRow dr in rows){
for (int i = 0; i<table.Columns.Count; i++)
        Console.Write("{0,15}",dr[i]);
      Console.WriteLine();
    }
  } //main
} //class

 

 

메소드 응용~

[C#]
public static SqlDataAdapter CreateCustomerAdapter(SqlConnection conn)
{
  SqlDataAdapter da = new SqlDataAdapter();
  SqlCommand cmd;
  // Create the SelectCommand.
  cmd = new SqlCommand("SELECT * FROM Customers " +
                       "WHERE Country = @Country AND City = @City", conn);
  cmd.Parameters.Add("@Country", SqlDbType.NVarChar, 15);
  cmd.Parameters.Add("@City", SqlDbType.NVarChar, 15);
  da.SelectCommand = cmd;
  // Create the InsertCommand.
  cmd = new SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " +
                       "VALUES (@CustomerID, @CompanyName)", conn);
  cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
  cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
  da.InsertCommand = cmd;
  return da;
}