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