10/29 데이터베이스 + C#

C#언어로 데이터베이스 프로그램 만들기

ADO.NET

구성 요소

• 크게 Provieder와 DataSet으로 구성되어 있다.

① Provider

• Conection - 데이터베이스에 연결한다.
• Command - INSERT, UPDATE, DELETE 처리한다.
• DataReader - SELECT에서 반환되는 값을 저장한다.
• DataAdapter - DataSet의 여러 테이블과 '관계'를 동기화 시키는 역할을 한다. SelectCommand, InsertCommand, UpdateCommand, DeleteCommand 쿼리문을 미리 등록하여 사용한다.

② DataSet

• TableCollection - DataTable(로우데이터, 칼럼, 제약조건)
• DataRelation - 관계성

연결 방식

• ① 연결 지향 :SQL 문장 기반 DB를 사용한다.
• ② 비연결 지향 : 프로시저 기반의 데이터베이스를 사용한다. -> DBMS에 미리 DB를 등록하여 사용한다. (함수 호출 방식과 유사)

---

1. SqlConnection

• SQL 데이터 소스와 연결을 제공하는 클래스
• SQL 데이터 소스와의 작업을 수행하기 위해 필요한 연결을 여는 작업을 수행한다.

• 데이터 소스와 계정 정보 등을 지정해야 한다.
• 네임 스페이스 : using MySql.Data; ([참조]에서 설치)

생성자

• MySqlConnection();

MySqlConnection _connect = new MySqlConnection();

private const string server_name = "127.0.0.1"; //IP가 들어갈 수 있어용 ("localhost")
private const string db_name     = "tableDB";   //연결하려는 데이터베이스
private const string user_id     = "root";      //cmd에서 권한설정 필요
private const string user_pw     = "1234";

• 데이터베이스와의 연결을 위해 IP와 연결하려는 데이터베이스 등이 필요하다.
• user_id와 user_pw를 사용하기 위해선 CMD 창에서 권한 설정을 해야 한다.

> mysql -u root -p
> GRANT ALL PRIVILEGES ON tableDB.* TO JSH@'%';

데이터베이스 연결 및 종료

• 메서드
  Open() : 데이터베이스에 연결한다.
  Close() : 데이터베이스의 연결을 종료한다.

public bool Open()
{
	try
	{
		string constr = string.Format(@"Data Source={0};Initial Catalog={1};User ID={2};Password={3}", server_name, db_name, user_id, user_pw);
		_connect = new MySqlConnection(constr); //데이터베이스 설정
		_connect.Open(); //지정된 데이터베이스에 연결 
		return true;
	}

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

• 위에서 정의한 데이터베이스와 계정 정보를 문자열 변수에 저장하여 생성자를 통해 데이터베이스와 연결한다.

데이터베이스 종료

public bool Close()
{
    _connect.Close();
    return false;
}

DB 연결 상태정보 출력

public string ConnectionInfo()
{
	//DB연결 상태정보 출력
	string temp = string.Empty;
	
	if (_connect.State == System.Data.ConnectionState.Open)
    	temp += "[ServerVersion] " + _connect.ServerVersion + "\r\n";
	else
    	temp += "[ServerVersion] " + "-" + "\r\n";
            
	temp += "[ConnectionString] " + _connect.ConnectionString + "\r\n";
	temp += "[ConnectionTimeout] " + _connect.ConnectionTimeout + "\r\n";
	temp += "[Database] " + _connect.Database + "\r\n";
	temp += "[DataSource] " + _connect.DataSource + "\r\n";
	temp += "[State] " + _connect.State;

	return temp;
}

• temp 임시 변수를 하나 생성하여 데이터베이스의 연결 상태 정보를 저장한다.
  ConnectionString : 연결 문자열
  ConnectionTime : 연결 대기 시간
  Database : 데이터베이스 이름
  DataSource : SQL 서버 인스턴스 명
  State : 데이터베이스와 연결 여부

---

2. SqlCommand

• SqlConnection으로 데이터 소스에 연결한 후 구체적인 작업을 할 땐 SqlCommand를 사용한다.

• ① 쿼리문 방식
  : SqlCommand 개체 생성 후 작업 내용을 쿼리문(INSERT, UPDATE, DELETE)을 문자열에 저장하여 사용한다.

• ② 저장 프로시저 방식
  : 따로 함수를 만들어 CommandText 속성에서 쿼리문을 설정하고 입력 인자로 전달한다.
  (함수의 호출 방식과 유사하다.)

2-1 쿼리문 방식

public bool Insert(string name, int price)
{
	string query = string.Format("INSERT INTO productTable (productName, productPrice) VALUES ('{0}', {1});", name, price);

	return ExecuteNonQuery(query);
}

public bool Update(string name, int price)
{
	string query = string.Format("UPDATE productTable SET productPrice = {0} WHERE productName = '{1}'", price, name);
            
	return ExecuteNonQuery(query);
}

public bool Delete(string name)
{
	string query = string.Format("DELETE FROM productTable WHERE productName = '{0}';", name);

	return ExecuteNonQuery(query);
}

public bool ExecuteNonQuery(string sql)
{
	bool b = false;
	using (MySqlCommand _command = new MySqlCommand(sql, _connect))
	{
		if (_command.ExecuteNonQuery() >= 1) //영향 받은 행의 수
		b = true;
		return b;
	}
}

• 쿼리문을 query 변수에 저장하여 ExecuteNonQuery 메서드에 전달한 후, 참 거짓을 반환 받는다.
• ExecuteNonQuery는 쿼리문을 수행하고 영향 받은 행의 수를 반환하는 메서드로, 위 코드에서는 행이 영향을 받았을 때 참, 아닐었을 때 거짓을 반환하는 역할로 쓰인다.

2-2 Execute 메서드

• ① ExecuteNonQuery()
  : 명령을 수행하고 영향을 받은 행의 수를 반환한다.
  INSERT, UPDATE, DELETE의 명령어를 수행한다.

• ② ExecuteScalar()
  : 1행 1열만 반환한다. 단 하나의 값만 리턴한다. 저장된 데이터의 수를 가져올 때 사용한다.

• ③ ExecuteReader()
  : 일반적인 SELECT 문에서 사용한다. 여러개의 칼럼과 로우데이터를 반환한다.

ExecuteScalar()

public long SelectProCount()
{
	string query = string.Format("SELECT COUNT(*) FROM productTable;");

	long count = 0; //저장된 제품 갯수
	using (MySqlCommand _command = new MySqlCommand(query, _connect))
    	count = (long)_command.ExecuteScalar(); //단 하나만 리턴한다.
	return count;
}

• ExecuteScalar 메서드를 통해 저장된 데이터의 수를 반환한다.

ExecuteReader()

public List<string> SelectProName(string name)
{
	//쿼리문 포맷
	string query = string.Format($"SELECT * FROM productTable WHERE productName = '{name}';");

	List<string> pList = new List<string>();

	using (MySqlCommand cmd = new MySqlCommand(query, _connect))
	{
		MySqlDataReader r = cmd.ExecuteReader(); //여러개의 데이터를 가져옴
		while (r.Read())
		{
			string productName = r["productName"].ToString();  //2) 칼럼명으로 받기 ["userTable"]  
			int productPrice = int.Parse(r["productPrice"].ToString());

			string temp = productName + "#" + productPrice;
			pList.Add(temp); //제품명#가격
		}
		r.Close();
    }
    return pList;
}

• SELECT 문을 query 문자열에 저장한 후, MySqlCommand 개체를 통해 쿼리문을 수행한다.
• ExecuteReader 메서드를 사용하여 저장된 칼럼과 로우 데이터의 정보들을 가져온다.

2-2 저장 프로시저 방식

• 쿼리문을 미리 등록한 후 호출하여 사용하는 방식
• 명령을 수행할 때 매개 변수를 설정하여 작업을 수행하고자 할 때 SqlParameter 개체를 이용한다.

public bool UserInsert_Proc(string id, string name, string phone, DateTime dt)
{
	//1) 프로시저 명
	string query = "UserInsert";

	//2) 명령 객체
	MySqlCommand _command = new MySqlCommand(query, myConn);

	//3) 타입 정의
	_command.CommandType = System.Data.CommandType.StoredProcedure; //저장 프로시저 호출임을 명시한다.

	//4) 파라미터 설정
	//userID
	MySqlParameter userID_param = new MySqlParameter("_userID", id); //프로시저 생성
	userID_param.MySqlDbType = MySqlDbType.String; //타입 지정
	_command.Parameters.Add(userID_param); //파라미터 추가

	int ret = _command.ExecuteNonQuery(); //리턴값을 반환
	if (ret >= 0)
		return true;
	else
		return false;
}

• 프로시저 생성하기
  ① 프로시저 명을 저장한다.
  ② 명령 객체를 생성한다. MySqlCommand
  ③ 프로시저의 타입을 정의한다. StoredProcedure : 저장 프로시저임을 명시

• 파라미터 설정하기
  ① MySqlParameter 클래스를 통해 프로시저를 생성하고
  ② MySqlDBType 속성으로 타입을 지정한다.
  ③ Parameters.Add()로 생성한 프로시저를 파라미터로 추가한다.