static void Main1(string[] args)
{
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
Console.WriteLine(connstr.ToString());
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES('dotnetthink','mypassword')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n").Append("password:").Append(reader.GetString(1));
}
Console.WriteLine(sb.ToString());
}
public string ToConnectionString()
{
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = @DataFile;
connstr.Password = Psw;
return(connstr.ToString());
}
static void ExecuteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "sample.sqlite");
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder()
{
DataSource = path
};
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Memberではなく、Members)
var sql = "CREATE TABLE Members (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Address TEXT, TelNo TEXT); ";
sql += "CREATE TABLE Items (Id INTEGER PRIMARY KEY AUTOINCREMENT, Price INTEGER, MemberId INTEGER, Name TEXT, SoldAt datetime, FOREIGN KEY(MemberId) REFERENCES Members(Id))";
var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
private void buttonImportData_Click(object sender, EventArgs e)
{
if (!datasource.Equals(""))
{
//建立SQLite连接
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "******";
conn.ConnectionString = connstr.ToString();
conn.Open();
//执行查询语句
string sql = "select id,puid from camdevices";
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataAdapter dataAdapter = new System.Data.SQLite.SQLiteDataAdapter(sql, conn);
//填充架构及数据
dataAdapter.FillSchema(dataSet1, SchemaType.Source, "camdevices");
dataAdapter.Fill(dataSet1, "camdevices");
//清空dataGridView数据源
if (dataGridViewDb.DataSource != null && dataGridViewDb.DataSource != "")
{
dataGridViewDb.DataSource = "";
dataGridViewDb.Refresh();
}
//填充dataGridView
dataGridViewDb.DataSource = dataSet1.Tables[0];
//刷新映射表
if (puidmap.ToString() != null && puidmap.ToString() != "")
{
puidmap.Clear();
}
for (int i = 0; i < dataSet1.Tables[0].Rows.Count; i++)
{
puidmap.Add(dataSet1.Tables[0].Rows[i][0], dataSet1.Tables[0].Rows[i][1]);
}
conn.Close();
}
else
{
MessageBox.Show("请选择数据库文件");
return;
}
if (!excelsource.Equals(""))
{
if (dataGridViewExcel.DataSource != null && dataGridViewExcel.DataSource != "")
{
dataGridViewExcel.DataSource = "";
dataGridViewExcel.Refresh();
}
DataTable dt = ExcelInput(excelsource);
dataGridViewExcel.DataSource = dt;
}
else
{
MessageBox.Show("请选择模板文件");
return;
}
}
internal static System.Data.IDbConnection SQLiteConn()
{
string strConn = System.Windows.Forms.Application.StartupPath + "//Database.db";
System.Data.SQLite.SQLiteConnectionStringBuilder strBuild = new System.Data.SQLite.SQLiteConnectionStringBuilder();
strBuild.DataSource = strConn;
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(strBuild.ToString());
return conn;
}
private void buttonSelectDbFolder_Click(object sender, EventArgs e)
{
string folder = "";
FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog();
if (folderBrowserDialog.ShowDialog() == DialogResult.OK)
{
folder = folderBrowserDialog.SelectedPath;
}
if (Directory.Exists(folder))
{
//文件夹及子文件夹下的所有文件的全路径
string[] files = Directory.GetFiles(folder, "*.db", SearchOption.AllDirectories);
for (int i = 0; i < files.Length; i++)
{
files[i] = Path.GetFileNameWithoutExtension(files[i]);//只取后缀
}
for (int i = 0; i < files.Count(); i++)
{
//建立SQLite连接
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = files[i];
//connstr.Password = "******";
conn.ConnectionString = connstr.ToString();
conn.Open();
//执行查询语句
string sql = "select id,puid from camdevices";
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataAdapter dataAdapter = new System.Data.SQLite.SQLiteDataAdapter(sql, conn);
//填充架构及数据
dataAdapter.FillSchema(dataSet1, SchemaType.Source, "camdevices");
dataAdapter.Fill(dataSet1, "camdevices");
//清空dataGridView数据源
if (dataGridViewDb.DataSource != null && dataGridViewDb.DataSource != "")
{
dataGridViewDb.DataSource = "";
dataGridViewDb.Refresh();
}
//填充dataGridView
dataGridViewDb.DataSource = dataSet1.Tables[0];
//刷新映射表
if (puidmap.ToString() != null && puidmap.ToString() != "")
{
puidmap.Clear();
}
for (int j = 0; j < dataSet1.Tables[0].Rows.Count; j++)
{
puidmap.Add(dataSet1.Tables[0].Rows[i][0], dataSet1.Tables[0].Rows[i][1]);
}
conn.Close();
}
}
}
public void OpenFile(String fileName)
{
try
{
System.Data.SQLite.SQLiteConnectionStringBuilder dbBuild = new System.Data.SQLite.SQLiteConnectionStringBuilder();
dbBuild.DataSource = fileName;
string connString = "Provider=System.Data.SQLite;" + dbBuild.ToString();
var conn = new System.Data.SQLite.SQLiteConnection(dbBuild.ToString());
//Test Connection (kinda crap way but hey)
using (var context = new Model.XMusCringleContext(conn, true))
{
var c = context.People.ToList();
}
main.dbConnString = connString;
}
catch (Exception e)
{
main.dbConnString = null;
}
}
/// <summary>
/// 得到Entity的连接字符串
/// </summary>
/// <param name="edmxFullName">Edmx的包括命名空间的全名称</param>
/// <param name="server">服务器地址或名称</param>
/// <param name="dadaBase">数据库</param>
/// <param name="usr">用户</param>
/// <param name="pswd">密码</param>
/// <param name="appName">程序名称</param>
/// <returns>Entity连接字符串</returns>
private static string GetEntityConnectionString(string edmxFullName, string path)
{
System.Data.Entity.Core.EntityClient.EntityConnectionStringBuilder entityConnectionStringBuilder =
new System.Data.Entity.Core.EntityClient.EntityConnectionStringBuilder();
entityConnectionStringBuilder.Metadata = "res://*/" + edmxFullName + ".csdl|res://*/" + edmxFullName + ".ssdl|res://*/" + edmxFullName + ".msl";
entityConnectionStringBuilder.Provider = "System.Data.SQLite.EF6";
System.Data.SQLite.SQLiteConnectionStringBuilder sqlConnectionStringBuilder = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sqlConnectionStringBuilder.DataSource = path;
entityConnectionStringBuilder.ProviderConnectionString = sqlConnectionStringBuilder.ToString();
return(entityConnectionStringBuilder.ConnectionString);
}
public static string CreateConnectionStr(string dbPath, string password)
{
if (!File.Exists(dbPath))
{
throw new FileNotFoundException("文件" + dbPath + "不存在!");
}
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = dbPath;
if (!string.IsNullOrEmpty(password))
{
connstr.Password = password;
}
return(connstr.ToString());
}
static void ExecuteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "sample.sqlite");
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Memberではなく、Members)
var sql = "CREATE TABLE Members (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Address TEXT, TelNo TEXT); ";
sql += "CREATE TABLE Items (Id INTEGER PRIMARY KEY AUTOINCREMENT, Price INTEGER, MemberId INTEGER, Name TEXT, SoldAt datetime, FOREIGN KEY(MemberId) REFERENCES Members(Id))";
var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
static void Main(string[] args)
{
// get remote records
//GetUnclaimedBugs();
// compare datatime with DB
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connStr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connStr.DataSource = datasource;
conn.ConnectionString = connStr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "Create table test(username varchar(20), password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sql = "Insert into test values('dontnetthiink', 'passwd')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "select * from test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while(reader.Read())
{
sb.Append("username: "******"\n")
.Append("passwd:").Append(reader.GetString(1));
}
Console.WriteLine(sb.ToString());
// insert latest records
}
internal static System.Data.IDbConnection SQLiteConn()
{
string strConn = System.Windows.Forms.Application.StartupPath + "//Database.db";
System.Data.SQLite.SQLiteConnectionStringBuilder strBuild = new System.Data.SQLite.SQLiteConnectionStringBuilder();
strBuild.DataSource = strConn;
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(strBuild.ToString());
return(conn);
}
static void ExecuteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter.db");
if (File.Exists(path))
{
return;
}
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
CommonFunction common = new CommonFunction();
common.setDbPath(path);
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Wordではなく、Words)
var sql = "CREATE TABLE WORD_DIC( ";
sql += " WORD_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , RONRI_NAME1 TEXT";
sql += " , RONRI_NAME2 TEXT";
sql += " , BUTSURI_NAME TEXT";
sql += " , USER_ID INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += " , FOREIGN KEY (USER_ID) REFERENCES USER_MST(USER_ID)";
sql += "); ";
sql += "CREATE TABLE WORD_SHINSEI( ";
sql += " SHINSEI_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , RONRI_NAME1 TEXT";
sql += " , RONRI_NAME2 TEXT";
sql += " , BUTSURI_NAME TEXT";
sql += " , WORD_ID INTEGER";
sql += " , STATUS INTEGER";
sql += " , USER_ID INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += " , FOREIGN KEY (USER_ID) REFERENCES USER_MST(USER_ID)";
sql += "); ";
sql += "CREATE TABLE USER_MST( ";
sql += " USER_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , EMP_ID INTEGER UNIQUE ";
sql += " , USER_NAME TEXT";
sql += " , KENGEN INTEGER";
sql += " , MAIL_ID TEXT";
sql += " , PASSWORD TEXT";
sql += " , MAIL_ADDRESS TEXT";
sql += " , SANKA_KAHI INTEGER";
sql += " , DELETE_FLG INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += "); ";
sql += "insert into USER_MST(USER_ID,EMP_ID,USER_NAME,KENGEN,MAIL_ID,PASSWORD,MAIL_ADDRESS,SANKA_KAHI,DELETE_FLG,VERSION) values (1,999, 'Admin',0,'999','*****@*****.**','*****@*****.**',0,0,0);";
var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
static void ExecuteSqliteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter_v2.db");
if (File.Exists(path))
{
return;
}
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Wordではなく、Words)
var sql = "CREATE TABLE WORD_DIC( ";
sql += " word_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE WORD_SHINSEI( ";
sql += " shinsei_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , word_id INTEGER";
sql += " , status INTEGER";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE USER_MST( ";
sql += " user_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , emp_id INTEGER UNIQUE ";
sql += " , user_name TEXT";
sql += " , kengen INTEGER";
sql += " , mail_id TEXT";
sql += " , password TEXT";
sql += " , mail_address TEXT";
sql += " , sanka_kahi INTEGER";
sql += " , delete_flg INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += "); ";
sql += "insert into USER_MST(user_id,emp_id,user_name,kengen,mail_id,password,mail_address,sanka_kahi,delete_flg,version) values (1,999, 'Admin',0,'999','*****@*****.**','*****@*****.**',0,0,0);";
string sqliteDdlText = sql;
var cmd = new System.Data.SQLite.SQLiteCommand(sqliteDdlText, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
//创建一个数据库文件
//’dotnetthink','mypassword'
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES('dotnetthink', 'mypassword')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
static void ExecuteSqliteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter_v2.db");
if (File.Exists(path))
{
return;
}
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder()
{
DataSource = path
};
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Wordではなく、Words)
var sql = "CREATE TABLE WORD_DIC( ";
sql += " word_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE WORD_SHINSEI( ";
sql += " shinsei_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , word_id INTEGER";
sql += " , status INTEGER";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE USER_MST( ";
sql += " user_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , emp_id INTEGER UNIQUE ";
sql += " , user_name TEXT";
sql += " , kengen INTEGER";
sql += " , mail_id TEXT";
sql += " , password TEXT";
sql += " , mail_address TEXT";
sql += " , sanka_kahi INTEGER";
sql += " , delete_flg INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += "); ";
sql += "insert into USER_MST(user_id,emp_id,user_name,kengen,mail_id,password,mail_address,sanka_kahi,delete_flg,version) values (1,999, 'Admin',0,'999','*****@*****.**','*****@*****.**',0,0,0);";
string sqliteDdlText = sql;
var cmd = new System.Data.SQLite.SQLiteCommand(sqliteDdlText, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
//创建一个数据库文件
//’dotnetthink','mypassword'
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES('dotnetthink', 'mypassword')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
