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()); }
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()); }
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(); } } } }