C# : SQLite 사용 ExecuteNonQuery(), ExecuteReader()

1-1. SQLiteCommand, SQLiteDataReader를 이용해서 ExecuteReader() 실행

string szDBFileName = "데이터베이스파일이름.db";
string szExecutablePath = Path.GetDirectoryName(Application.ExecutablePath);

// 애플리케이션이 있는 위치 경로
string szDBFile = String.Format(@"{0}\{1}\{2}", szExecutablePath, "Database", szDBFileName);

// DB접속정보 
string constring = string.Format("Data Source={0};Version=3;", szDBFile);

SQLiteConnection dbConnection = new SQLiteConnection();
dbConnection.ConnectionString = constring;
dbConnection.Open();

string sqlCom = "SELECT * FROM 테이블명";
SQLiteCommand scdCommand = new SQLiteCommand(sqlCom, dbConnection);
SQLiteDataReader reader = scdCommand.ExecuteReader();

while (reader.Read())
{
    string Value1 = (string)reader["컬럼명1"];
    string Value2 = (string)reader["컬럼명2"];
}

reader.Close();
dbConnection.Close();

우리는 SQLiteDataReader를 통해 데이터를 하나 하나씩 가져올 수 있습니다. 만약 데이터를 한꺼번에 가져오고 싶다면, SQLiteDataReader말고 SQLiteDataAdapter를 이용하세요. SQLiteDataAdapter는 DataSet, DataTable 등으로 가져올 수 있습니다.

 

1-2. SQLiteCommand, SQLiteDataAdapter를 이용해서 ExecuteReader() 실행

string szDBFileName = "데이터베이스파일이름.db";
string szExecutablePath = Path.GetDirectoryName(Application.ExecutablePath);
// 애플리케이션이 있는 위치 경로
string szDBFile = String.Format(@"{0}\{1}\{2}", szExecutablePath, "Database", szDBFileName);
// DB접속정보 
string constring = string.Format("Data Source={0};Version=3;", szDBFile);

SQLiteConnection dbConnection = new SQLiteConnection();
dbConnection.ConnectionString = constring;
dbConnection.Open();

string sqlCom = "SELECT * FROM 테이블명";
SQLiteCommand scdCommand = new SQLiteCommand(sqlCom, dbConnection);
SQLiteDataAdapter sdAdapter = new SQLiteDataAdapter();
sdAdapter.SelectCommand = scdCommand;

DataTable dtOut = new DataTable();
sdAdapter.Fill(dtOut);

dbConnection.Close();

 

2. SQLiteCommand를 이용해서 ExcuteNonQuery() 실행

string szDBFileName = "데이터베이스파일이름.db";
string szExecutablePath = Path.GetDirectoryName(Application.ExecutablePath);

// 애플리케이션이 있는 위치 경로
string szDBFile = String.Format(@"{0}\{1}\{2}", szExecutablePath, "Database", szDBFileName);
// DB접속정보 
string constring = string.Format("Data Source={0};Version=3;", szDBFile);

SQLiteConnection dbConnection = new SQLiteConnection();
dbConnection.ConnectionString = constring;
dbConnection.Open();

string sqlCom = "DELETE FROM 테이블명";
SQLiteCommand scdCommand = new SQLiteCommand(sqlCom, dbConnection);
scdCommand.ExecuteNonQuery();

dbConnection.Close();