public void TestPage()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
db.Open();
//第一种方式
var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList();
var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList();
//第二种方式
var id = 24;
var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany();
var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany();
//分组分页
var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
}
}
public void TestSingle()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
db.Open();
var entity1 = db.From <Student>().Single();
var entity2 = db.From <Student>().OrderByDescending(a => a.Id).Single();
var entity3 = db.From <Student>().OrderBy("create_time").Single();
var balance = db.From <Student>().Where(a => a.Id == 24).Single(s => s.Balance);
var entity4 = db.From <Student>().Where(a => a.Id == 24).Single(s => new
{
s.Id,
s.Balance,
s.BirthDay,
s.IsDelete
});
var entity5 = db.From <Student>().Where(a => a.Id == 24).Single(s => new Student
{
Id = s.Id,
Balance = s.Balance,
BirthDay = s.BirthDay,
IsDelete = s.IsDelete
});
}
}
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();
}
}
public string ReadDataRoles()
{
string data = "";
try
{
DataTable dtObj = new DataTable();
var conStingBuilder = new System.Data.SQLite.SQLiteConnectionStringBuilder();
conStingBuilder.DataSource = "C:/Users/INDIAN/Documents/DB/HighSchool";
var conObj = new System.Data.SQLite.SQLiteConnection(conStingBuilder.ConnectionString);
//System.Data.SQLite.SQLiteConnection conObj = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteCommand cmdObj = new System.Data.SQLite.SQLiteCommand("select * from roles;", conObj);
//SqliteDataAdapter daObj;
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(cmdObj);
dAdapter.Fill(dtObj);
foreach (DataRow i in dtObj.Rows)
{
data = data + i["RoleId"] + " " + i["RoleName"] + "\n";
}
}
catch (Exception ex)
{
data = "";
}
finally
{
//conTest.Close();
}
return(data);
}
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());
}
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;
}
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();
}
}
}
private void InitializeDatabase()
{
System.Data.SQLite.SQLiteConnectionStringBuilder connectionStringBuilder = new System.Data.SQLite.SQLiteConnectionStringBuilder(this.Database.Connection.ConnectionString);
string dataSourcePath = this.AppDataStorage.ResolveLocalDataSource(connectionStringBuilder.DataSource);
if (!System.IO.File.Exists(dataSourcePath))
{
this.Database.Initialize(true);
}
}
private static string ExtractFilePathFromConnectionString(string connectionString)
{
var conn = new System.Data.SQLite.SQLiteConnectionStringBuilder(connectionString);
var dataSource = conn.DataSource;
if (!dataSource.Contains(@"\"))
{
dataSource = $@"{AppDomain.CurrentDomain.BaseDirectory}\{dataSource}";
}
return(dataSource);
}
/// <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 void TestDelete()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
db.Open();
var row1 = db.From <Student>().Where(a => a.Id == 23).Delete();
var row2 = db.From <Student>().Where(a => Operator.In(a.Id, new int[] { 1, 2, 3 })).Delete();
}
}
public void TestJoin()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
db.Open();
var list0 = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.OrderBy((a, b) => a.Id)
.OrderByDescending((a, b) => b.Id)
.Select((a, b) => new
{
a.Id,
a.Name,
Class = b.Name
}).ToList();
var(list1, total1) = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.Page(1, 2)
.SelectMany((a, b) => new
{
a.Id,
a.Name,
Class = b.Name
});
var(list2, total2) = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.GroupBy((a, b) => b.Name)
.Page(1, 2)
.SelectMany((a, b) => new
{
Class = b.Name,
StuNames = SqlFun.GROUP_CONCAT(a.Name)
});
var list3 = db.From <Student, Stuclass, Stuid>()
.Join((Student a, Stuclass b) => a.Id == b.Sid)
.Join((Student a, Stuid b) => a.Id == b.Sid)
.Select((a, b, c) => new
{
a.Id,
b.Name,
c.IdNum
}).ToList();
}
}
public string ReadDataPerson()
{
var connectionStringBuilder = new SqliteConnectionStringBuilder();
connectionStringBuilder.DataSource = "C:/Users/INDIAN/Documents/DB/HighSchool";
var conTest = new SqliteConnection(connectionStringBuilder.ConnectionString);
string data = "";
try
{
DataTable dtObj = new DataTable();
var conStingBuilder = new System.Data.SQLite.SQLiteConnectionStringBuilder();
conStingBuilder.DataSource = "C:/Users/INDIAN/Documents/DB/HighSchool";
var conObj = new System.Data.SQLite.SQLiteConnection(conStingBuilder.ConnectionString);
//System.Data.SQLite.SQLiteConnection conObj = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteCommand cmdObj = new System.Data.SQLite.SQLiteCommand("select * from person;", conObj);
//SqliteDataAdapter daObj;
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(cmdObj);
dAdapter.Fill(dtObj);
foreach (DataRow i in dtObj.Rows)
{
data = data + i["PersonID"] + " " + i["FirstName"] + " " + i["LastName"] + " " + i["UserPassword"] + " " + i["RoleId"] + " " + i["Gender"] + " " + i["DateOfBirth"] + " " + i["Address"] + "\n";
}
//conTest.Open();
//data = "";
//var selectCmd = conTest.CreateCommand();
//selectCmd.CommandText = "select * from person;";
//SqliteDataReader rObj = null;
//rObj = selectCmd.ExecuteReader(CommandBehavior.CloseConnection);
//while (rObj.Read())
//{
// data = "\n" + data + rObj["PersonID"] + " " + rObj["FirstName"] + " " + rObj["LastName"] + " " + rObj["UserPassword"] + " " + rObj["RoleId"] + " " + rObj["Gender"] + " " + rObj["DateOfBirth"] + " " + rObj["Address"];
//}
}
catch (Exception ex)
{
data = "";
}
finally
{
//conTest.Close();
}
return(data);
}
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();
}
}
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;
}
}
public override DbConnection GetConnection()
{
// Look up configuration
var cs = ConfigurationManager.ConnectionStrings[FileOrName];
if (cs != null)
{
return(new ProviderConnectionSetting(cs.ProviderName, cs.ConnectionString).GetConnection());
}
// Access file?
if (FileOrName.EndsWith(".accdb") || FileOrName.EndsWith(".mdb"))
{
var builder = new System.Data.Odbc.OdbcConnectionStringBuilder();
builder.Add("Driver", "{Microsoft Access Driver (*.mdb, *.accdb)}");
builder.Add("Dbq", FileOrName);
var conn = new System.Data.Odbc.OdbcConnection();
conn.ConnectionString = builder.ConnectionString;
conn.Open();
return(conn);
}
// Assume a SQLite file
// Restricts to an exsiting file to prevent mistyping from creating a new database
if (File.Exists(FileOrName))
{
var builder = new System.Data.SQLite.SQLiteConnectionStringBuilder();
builder.Add("Data Source", FileOrName);
var conn = new System.Data.SQLite.SQLiteConnection();
conn.ConnectionString = builder.ConnectionString;
conn.Open();
return(conn);
}
throw new RuntimeException(String.Format("'{0}' is not a database file name nor connection string name", FileOrName));
}
public void TestGroup()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
db.Open();
var list = db.From <Student>()
.GroupBy(a => a.Name)
.Having(a => SqlFun.COUNT(1L) > 1)
.OrderByDescending(a => SqlFun.COUNT(1))
.Select(s => new
{
s.Name,
Names = SqlFun.GROUP_CONCAT(s.Name),
Count = SqlFun.COUNT(1L),
Balance = SqlFun.SUM(s.Balance)
}).ToList();
}
}
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
}
/// <summary>
/// Gets the full path and file name of a DB. In memory databases will instead set the <seealso cref="ConnectionStringInfo.IsInMemory"/> flag to true.
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
public static ConnectionStringInfo GetFileName(string connectionString)
{
System.Data.SQLite.SQLiteConnectionStringBuilder builder;
try
{
builder = new System.Data.SQLite.SQLiteConnectionStringBuilder(connectionString);
}
// ReSharper disable once UncatchableException
catch (System.ArgumentException) //bad format - return a connectionstring info that isn't valid
{
return new ConnectionStringInfo(false, string.Empty);
}
var dataSource = builder.DataSource.ToLowerInvariant();
var inMemory = dataSource.Contains(":memory:") || dataSource.Contains("mode=memory");
if (inMemory || string.IsNullOrWhiteSpace(builder.FullUri))
return new ConnectionStringInfo(inMemory, builder.DataSource);
var uri = builder.FullUri.ToLowerInvariant();
inMemory = uri.Contains(":memory:") || uri.Contains("mode=memory");
return new ConnectionStringInfo(inMemory, builder.DataSource);
}
public void TestSelect()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
db.Open();
var fun = new SqliteConcat <Student>();
fun.Concat(a => new { a.Name, a.Id });
var list0 = db.From <Student>().Select(s => (string)fun).ToList();
var list1 = db.From <Student>().Select().ToList();
var list2 = db.From <Student>().Where(a => a.IsDelete == false).Select().ToList();
var list3 = db.From <Student>().OrderBy(a => a.Id).OrderByDescending(a => a.Balance).Select().ToList();
var list4 = db.From <Student>().Take(4).Select().ToList();
var list5 = db.From <Student>().Take(4).Skip(2, 2).Select().ToList();
var list6 = db.From <Student>().Select(s => new { s.IsDelete, s.Id, s.Name }).ToList();
var list7 = db.From <Student>().Select(s => new Student {
IsDelete = s.IsDelete, Id = s.Id, Name = s.Name
}).ToList();
}
}
public void TestInsert()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
var row = 0;
db.Open();//自动提交
//1.更新所有字段（除自增列）
row = db.From <Student>().Insert(new Student()
{
BirthDay = new DateTime(1996, 10, 20),
CreateTime = DateTime.Now,
IsDelete = false,
Name = "sqlcommon",
Score = Grade.C,
Version = Guid.NewGuid().ToString("N")
});
//2.更新所有字段,返回id（除自增列）
var id = db.From <Student>().InsertReturnId(new Student()
{
BirthDay = new DateTime(1996, 10, 20),
CreateTime = DateTime.Now,
IsDelete = false,
Score = Grade.A,
Name = "sqlcommon",
Version = Guid.NewGuid().ToString("N")
});
//3.新增指定字段
row = db.From <Student>().Insert(s => new Student
{
Score = Grade.A,
Name = "sqlcommon",
});
//4.排除字段
row = db.From <Student>()
.Filter(s => new { s.BirthDay, s.Score })
.Insert(new Student()
{
BirthDay = new DateTime(1996, 10, 20),
CreateTime = DateTime.Now,
IsDelete = false,
Name = "sqlcommon",
Score = Grade.C,
Version = Guid.NewGuid().ToString("N")
});
//5.批量新增
var students = new List <Student>();
students.Add(new Student()
{
Name = "tom", Balance = 50
});
students.Add(new Student()
{
Name = "bob", Balance = 100
});
row = db.From <Student>().Insert(students);
}
}
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();
}
}
public void TestUpdate()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
var row = 0;
db.Open();//自动提交
var entity1 = new Student()
{
Id = 22,
Balance = 50,
BirthDay = DateTime.Now,
Score = Grade.E,
IsDelete = true,
Name = "faker",
Version = "test",
CreateTime = DateTime.Now,
};
var entity2 = new Student()
{
Id = 24,
Balance = 50,
BirthDay = DateTime.Now,
Score = Grade.E,
IsDelete = true,
Name = "faker",
Version = "test",
CreateTime = DateTime.Now,
};
//1.根据主键字段更新所有列
row = db.From <Student>().Update(entity1);
//2.忽略指定列
row = db.From <Student>().Filter(s => s.Version).Update(entity2);
//3.根据指定条件更新所有列
var oldVersion = "110";
row = db.From <Student>()
.Where(a => a.Id == 25 && a.Version == oldVersion)
.Update(entity1);
//4.批量修改,不推荐使用，
var students = new List <Student>();
students.Add(entity1);
students.Add(entity2);
row = db.From <Student>().Filter(a => a.CreateTime).Update(students);
//5.更新指定字段
db.From <Student>()
.Update(s => new Student()
{
Id = 26, //默认通过id更新，可以用where来重置默认
Score = Grade.F,
Name = "hihi"
});
//6.动态更新字段
var charat = "f";
db.From <Student>()
.Set(a => a.Balance, 100, 1 > 2) //false
.Set(a => a.IsDelete, true)
.Set(a => a.Balance, a => a.Balance + 200, 1 < 2) //true
.Set(a => a.Name, a => SqlFun.Replace(a.Name, "b", charat)) //true
.Where(a => a.Id == 27)
.Update();
}
}
public static void ProcessSniff(string filename, string todir)
{
var clientbuild = 0u;
var accountname = string.Empty;
var logstarted = DateTime.Now;
bool found = false;
bool empty = false;
using (var findcon = new System.Data.SQLite.SQLiteConnection())
{
findcon.ConnectionString = "Data Source=" + filename;
findcon.Open();
using (var tSQLiteCommand = findcon.CreateCommand())
{
var CMSG_AUTH_SESSION = 0x1ED;
tSQLiteCommand.CommandText = string.Format("select id, timestamp, direction, opcode, data from packets where opcode = {0} limit 1", CMSG_AUTH_SESSION);
using (var tempreader = tSQLiteCommand.ExecuteReader())
{
while (tempreader.Read())
{
var id = tempreader.GetInt32(0);
logstarted = tempreader.GetDateTime(1);
var direction = tempreader.GetInt32(2);
var opcode = tempreader.GetInt32(3);
var blob = (byte[])tempreader.GetValue(4);
using (var qs = new Reading.ReadingBase(blob))
{
found = true;
clientbuild = qs.ReadUInt32();
qs.ReadUInt32();
accountname = qs.ReadCString();
if (accountname.IsEmpty())
{
Console.WriteLine("Error");
}
break;
}
}
tempreader.Close();
empty = (clientbuild == 0);
}
}
if (!empty)
{
if (!found) throw new Exception("Invalid file");
string newdir = string.Format("{0}{1}\\", todir, clientbuild);
string newfile = string.Format(@"{0}{1}_{2}_{3}.sqlite", newdir, logstarted.ToString("yyyy-MM-dd-HH-mm"), clientbuild, accountname);
if (System.IO.File.Exists(newfile)) System.IO.File.Delete(newfile);// throw new Exception("File exists");
if (!System.IO.Directory.Exists(newdir)) System.IO.Directory.CreateDirectory(newdir);
System.Data.SQLite.SQLiteConnection.CreateFile(newfile);
var builder = new System.Data.SQLite.SQLiteConnectionStringBuilder();
builder.DataSource = newfile;
builder.CacheSize = builder.CacheSize * 100;
builder.PageSize = builder.PageSize * 100;
builder.JournalMode = System.Data.SQLite.SQLiteJournalModeEnum.Off;
builder.Pooling = false;
DateTime tstart = DateTime.Now;
using (var con = new System.Data.SQLite.SQLiteConnection(builder.ConnectionString))
{
con.Open();
//create tables
var sb = new StringBuilder();
sb.AppendLine("create table packets (id integer primary key autoincrement, timestamp datetime, direction integer, opcode integer, data blob);");
sb.AppendLine("create table header (key string primary key, value string);");
sb.AppendLine(string.Format("insert into header(key, value) values ('clientBuild', '{0}');", clientbuild));
sb.AppendLine("insert into header(key, value) values ('clientLang', 'enUS');");
sb.AppendLine(string.Format("insert into header(key, value) values ('accountName', '{0}');", accountname));
using (System.Data.SQLite.SQLiteCommand command = con.CreateCommand())
{
command.CommandText = sb.ToString();
command.ExecuteNonQuery();
}
Console.WriteLine("start processing newfile: {0} filename: {1}", tstart, newfile);
try
{
using (var dbTrans = con.BeginTransaction())
{
using (var command = con.CreateCommand())
{
command.CommandText = "insert into packets (timestamp, direction, opcode, data) VALUES (?,?,?,?)";
var timestamp = command.CreateParameter();
timestamp.DbType = System.Data.DbType.DateTime;
command.Parameters.Add(timestamp);
var direction = command.CreateParameter();
direction.DbType = System.Data.DbType.Int32;
command.Parameters.Add(direction);
var opcode = command.CreateParameter();
opcode.DbType = System.Data.DbType.Int32;
command.Parameters.Add(opcode);
var data = command.CreateParameter();
data.DbType = System.Data.DbType.Binary;
command.Parameters.Add(data);
using (var tSQLiteCommand = findcon.CreateCommand())
{
var t = DateTime.Now;
tSQLiteCommand.CommandText = "select * from packets ";
using (var tempreader = tSQLiteCommand.ExecuteReader())
{
bool badopcode = false;
try
{
while (tempreader.Read())
{
var _id = tempreader.GetInt32(0);
var _timestamp = tempreader.GetDateTime(1);
var _direction = tempreader.GetInt32(2);
var _opcode = tempreader.GetInt32(3);
var _blob = (byte[])tempreader.GetValue(4);
if (_opcode > 1311)
{
Console.WriteLine("Error: Invalid opcode {0}", _opcode);
break;
}
else if (!badopcode)
{
try
{
timestamp.Value = _timestamp;
direction.Value = _direction;
opcode.Value = _opcode;
data.Value = _blob;
if (command.ExecuteNonQuery() <= 0)
{
throw new Exception("record not inserted?");
}
}
catch (Exception exc)
{
Console.WriteLine("Error: {0}", exc.Message);
}
}
}
}
catch (Exception exc)
{
Console.WriteLine("Error: {0}", exc.Message);
}
tempreader.Close();
}
}
}
dbTrans.Commit();
}
}
catch (Exception exc)
{
Console.WriteLine("Error: {0}", exc.Message);
}
con.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());
}
