public bool IsTableExist(string tableName)
{
bool isTableExist = true;
//try
//{
if (tableName == null)
throw new ArgumentNullException("table=null");
this.Open();
// SELECT count(*) FROM sqlite_master WHERE type='table' AND name='test';
SQLiteCommand cmd = new SQLiteCommand("SELECT count(*) as c FROM sqlite_master WHERE type='table' AND name=@tableName ");
cmd.Connection = this.Connection;
cmd.Parameters.Add(new SQLiteParameter("tableName", tableName));
SQLiteDataReader reader = cmd.ExecuteReader();
reader.Read();
int c = reader.GetInt32(0);
reader.Close();
reader.Dispose();
cmd.Dispose();
//return false;
this.CloseQuietly();
return c == 1;
//string sql = "select count(*) FROM sqlite_master WHERE type='table' AND name=" + tableName;
//SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
//SQLiteDataReader reader = command.ExecuteReader();
//string qx_Str;
//if (reader.HasRows)
//{
// reader.Read();
// qx_Str = reader[0].ToString(); //出错位置
// if (qx_Str.Equals("0"))
// {
// isTableExist = false;
// }
// Console.WriteLine(qx_Str);
//}
//reader.Close();
//command.Dispose();
//}catch(Exception ex)
//{
// MessageBox.Show(ex.Message);
//}
return isTableExist;
}
private void ConnectDatabase()
{
if (this.connection == null)
{
connection = new SQLiteConnection(@"Data source=" + path + "\db\local.db;Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10;");
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
return;
}
connection = new SQLiteConnection(@"Data source=" + path + "\db\local.db;Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10;");
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
}
//在指定数据库中创建一个table
public void CreateTable(string sqlStatement)
{
ConnectDatabase();
//string sql = "create table local_batch_menu (node_text varchar(20),node_name varchar(50), sort int)";
string sql = sqlStatement;
SQLiteCommand command = new SQLiteCommand(sql,connection);
//if (this.Connection.State == ConnectionState.Closed)
//{
// this.Open();
//}
command.ExecuteNonQuery();
command.Dispose();
this.Connection.Close();
}
public DataSet ExecuteDataSet(string commandText)
{
// string sql = "select * from highscores order by score desc";
// SQLiteCommand command = new SQLiteCommand(sql, connection);
SQLiteCommand cmd = this.Connection.CreateCommand();
cmd.CommandText = commandText;
DataSet ds = new DataSet();
if (this.Connection.State == ConnectionState.Closed)
this.Open();
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd);
// SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
dataAdapter.Fill(ds);
dataAdapter.Dispose();
cmd.Dispose();
this.CloseQuietly();
return ds;
}
//插入一些数据
void fillTable()
{
string sql = "insert into highscores (name, score) values ('Me', 3000)";
SQLiteCommand command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('Myself', 6000)";
command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('And I', 9001)";
command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
}
//使用sql查询语句,并显示结果
void printHighscores()
{
string sql = "select * from highscores order by score desc";
SQLiteCommand command = new SQLiteCommand(sql, connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine("Name: " + reader["name"] + " Score: " + reader["score"]);
Console.ReadLine();
}