// Klasa zawierająca metody obsługujące operacje na bazie danych public List <List <string> > LoadData(string table, List <string> columnsName) { //Nawiązanie połączenia z bazą danych var auth = new Authentication(); auth.getConnection(); using (SQLiteConnection con = new SQLiteConnection(auth.connectionString)) { List <List <string> > RecordsList = new List <List <string> >(); List <string> ValueList = new List <string>(); con.Open(); SQLiteCommand cmd = new SQLiteCommand(); int Count = columnsName.Count(); string query = @"SELECT " + columnsName[0]; if (Count > 1) { for (int i = 1; i < Count; i++) { query += "," + columnsName[i]; } } query += " FROM " + table; cmd.CommandText = query; cmd.Connection = con; SQLiteDataReader read = cmd.ExecuteReader(); while (read.Read()) { int VariableCount = read.FieldCount; for (int i = 0; i < VariableCount; i++) { Type t = typeof(System.Int64); Type d = typeof(System.DateTime); if (read.GetFieldType(i).Equals(t)) { ValueList.Add(read.GetFieldValue <long>(i).ToString()); } else if (read.GetFieldType(i).Equals(d)) { ValueList.Add(read.GetFieldValue <DateTime>(i).ToString()); } else { ValueList.Add(read.GetFieldValue <string>(i)); } } RecordsList.Add(ValueList); ValueList = new List <string>(); } return(RecordsList); } }
private bool CheckColumnsProperties(SQLiteConnection dbUserConnection) { SQLiteCommand commandUser = new SQLiteCommand("SELECT * FROM tracks", dbUserConnection); commandUser.ExecuteNonQuery(); SQLiteDataReader readerUser = commandUser.ExecuteReader(); int nbColUser = readerUser.FieldCount; SQLiteCommand command = new SQLiteCommand("SELECT * FROM tracks", _dbConnection); command.ExecuteNonQuery(); SQLiteDataReader reader = command.ExecuteReader(); int nbCol = reader.FieldCount; if (nbColUser == nbCol) { for (int i = 0; i < nbCol; i++) { if (readerUser.GetName(i) == reader.GetName(i) && readerUser.GetFieldType(i) == reader.GetFieldType(i)) { } else { return(false); } } } else { return(false); } return(true); }
/// <summary> /// search TYBH in database(MSJB_ZJ_DCYX) /// </summary> /// <returns></returns> public DataTable Excute() { string TYBH = textBox1.Text; string ExcuteSql = "SELECT `TYBH` FROM `MSJB_ZJ_DCYX` WHERE `TYBH`=@TYBH"; SQLiteCommand command = new SQLiteCommand(ExcuteSql, connection); command.Parameters.AddWithValue("@TYBH", TYBH); SQLiteDataReader reader = command.ExecuteReader(); int num; DataTable table = new DataTable(); for (num = 0; num < reader.FieldCount; num++) { DataColumn column = new DataColumn(); column.DataType = reader.GetFieldType(num); column.ColumnName = reader.GetName(num); table.Columns.Add(column); } while (reader.Read()) { DataRow row = table.NewRow(); for (num = 0; num < reader.FieldCount; num++) { row[num] = reader[num].ToString(); } table.Rows.Add(row); row = null; } reader.Close(); return(table); }
public List <object> ReadRows(string table, string column, string value, object obj) { SQLiteConnection conn = new SQLiteConnection(db_file); try { conn.Open(); SQLiteCommand cmd = new SQLiteCommand(conn) { CommandText = string.Format("SELECT * FROM {0} WHERE {1} = '{2}'", table, column, value) }; SQLiteDataReader rdr = cmd.ExecuteReader(); Type ObjType = obj.GetType(); bool isFound = false; List <object> objList = new List <object>(); while (rdr.Read()) { if (!rdr.HasRows || rdr.FieldCount <= 0) { throw new Exception("No Row Returned"); } object instance = Activator.CreateInstance(ObjType); foreach (FieldInfo item in ObjType.GetRuntimeFields().Where(x => x.IsStatic == false)) { for (int i = 0; i < rdr.FieldCount; i++) { if (item.Name.Contains(string.Format("<{0}>", rdr.GetName(i))) && rdr.GetFieldType(i) == item.FieldType) { var v = rdr.GetValue(i); item.SetValue(instance, v); isFound = true; break; } } } if (!isFound) { throw new Exception("Error Reading Fields"); } objList.Add(instance); } rdr.Close(); CloseConnection(conn); return(objList); } catch (Exception e) { Console.WriteLine(e); CloseConnection(conn); return(null); } }
public static DataTable sQLiteDataReaderToDataTable(SQLiteDataReader sQLiteDataReader) { DataTable dataTable = new DataTable(); for (int i = 0; i < sQLiteDataReader.FieldCount; i++) { DataColumn myDataColumn = new DataColumn(); myDataColumn.DataType = sQLiteDataReader.GetFieldType(i); myDataColumn.ColumnName = sQLiteDataReader.GetName(i); dataTable.Columns.Add(myDataColumn); } while (sQLiteDataReader.Read()) { DataRow myDataRow = dataTable.NewRow(); for (int i = 0; i < sQLiteDataReader.FieldCount; i++) { Console.WriteLine(myDataRow[i]); Console.WriteLine(sQLiteDataReader[i]); myDataRow[i] = sQLiteDataReader[i].ToString(); } dataTable.Rows.Add(myDataRow); myDataRow = null; } return(dataTable); }
private unRetourRequete copieDonnees(ref SQLiteDataReader source) { unRetourRequete retour = new unRetourRequete(); try { retour.Tables.Add("Resultat1"); for (int i = 0; i <= source.FieldCount - 1; i++) { retour.Tables[0].Columns.Add(source.GetName(i), source.GetFieldType(i)); } while (source.Read()) { retour.Tables[0].Rows.Add(); for (int numColonne = 0; numColonne <= source.FieldCount - 1; numColonne++) { retour.Tables[0].Rows[retour.Tables[0].Rows.Count - 1][numColonne] = source[numColonne]; } } return(retour); } catch (Exception ex) { _lastError = ex.Message; } finally { try { source.Close(); } catch { } } return(null); }
/// <summary> /// Inicializa una nueva instancia de la clase <see cref="SelectionResult"/>. /// </summary> /// <param name="reader">El lector de datos de SQLite.</param> /// <param name="columnIndex">El indice de la columna a leer.</param> public SelectionResult(SQLiteDataReader reader, int columnIndex) { this.ColumnName = reader.GetName(columnIndex); this.Value = reader[columnIndex]; this.DotNetType = reader.GetFieldType(columnIndex); this.SQLiteType = reader.GetDataTypeName(columnIndex); }
public static QueryRow Read(SQLiteDataReader reader) { var result = new QueryRow(); reader.GetValues(); for (var i = 0; i < reader.FieldCount; i++) { result.queryDefinition.Fields.Add( reader.GetName(i), new FieldDefinition( reader.GetName(i), reader.GetFieldType(i).ToSqlDbType(), false, false ) ); result.values.Add( reader.GetName(i), reader.GetValue(i) ); } return(result); }
/// <summary> /// SELECT `JGLX`,`JGNJ`,`GDSD`,`JGCD` FROM `MSJB_ZJ_JGJG` /// </summary> /// <returns></returns> public DataTable Excute5() { //string QiaoShi = "桥式滤水管"; string ExcuteSql = "SELECT `JGLX`,`JGNJ`,`GDSD`,`JGCD` FROM `MSJB_ZJ_JGJG` WHERE `TYBH`=@TYBH "; SQLiteCommand command = new SQLiteCommand(ExcuteSql, connection); command.Parameters.AddWithValue("@TYBH", TYBH); //command.Parameters.AddWithValue("@QiaoShi",QiaoShi); SQLiteDataReader reader = command.ExecuteReader(); int num; DataTable table = new DataTable(); for (num = 0; num < reader.FieldCount; num++) { DataColumn column = new DataColumn(); column.DataType = reader.GetFieldType(num); column.ColumnName = reader.GetName(num); table.Columns.Add(column); } while (reader.Read()) { DataRow row = table.NewRow(); for (num = 0; num < reader.FieldCount; num++) { row[num] = reader[num].ToString(); } table.Rows.Add(row); row = null; } reader.Close(); return(table); }
private static void FillDatatable(SQLiteCommand command, DataTable dt) { using (SQLiteDataReader reader = command.ExecuteReader()) { var len = reader.FieldCount; // Create the DataTable columns for (int i = 0; i < len; i++) { dt.Columns.Add(reader.GetName(i), reader.GetFieldType(i)); } dt.BeginLoadData(); var values = new object[len]; // Add data rows while (reader.Read()) { for (int i = 0; i < len; i++) { values[i] = reader[i]; } dt.Rows.Add(values); } dt.EndLoadData(); reader.Close(); } }
// // 将OleDbDataReader中的数据转换为List // 向更高层的程序屏蔽底层的OleDb类,并且使用后关闭reader释放资源 public List <Dictionary <string, object> > GetRecord(string select) { // 首先使用reader从数据库获取数据 SQLiteDataReader reader = this.conn.GetRecord(select); try { // 定义结果集 var result = new List <Dictionary <string, object> >(); while (reader.Read()) { // reader的当前记录保存在Dictionary<string, object>中 var record = new Dictionary <string, object>(); for (int i = 0; i < reader.FieldCount; i++) { string name = reader.GetName(i); object value; if (reader.IsDBNull(i)) { // 对于数据库空值的情况,用DBValueNull()专门给一个默认值 value = DefaultValue4DBNull.DefaultValue(reader.GetFieldType(i)); } else { value = reader[i]; } record.Add(name, value); } // 当前记录全部保存完毕,添加到结果集中 result.Add(record); } // 全部记录保存完毕,返回结果集 return(result); } catch (SQLiteException ex) { string errorMessage = "从数据库SELECT数据集发生异常\n" + "发生错误的SQL语句:" + select; throw new Exception(errorMessage, ex); } finally { // 最后关闭reader reader.Close(); reader.Dispose(); } }
public ArrayList sendQuery() { theConnector.Connect(); // string state = conn.State.ToString(); // MessageBox.Show(state); try { SQLiteDataReader reader = null; reader = aCommand.ExecuteReader(); while (reader.Read()) { //MessageBox.Show(reader.GetFieldType(0).ToString()); switch (reader.GetFieldType(0).ToString()) { case "System.Int64": value.Add(reader.GetInt64(0)); break; case "System.String": value.Add(reader.GetString(0)); break; case "System.Double": value.Add(reader.GetDouble(0)); break; case "System.Int32": value.Add(reader.GetInt32(0)); break; } } theConnector.Disconnect(); return(value); } catch (SQLiteException ex) { MessageBox.Show(ex.Message); return(null); } catch (IOException e) { MessageBox.Show(e.Message); return(null); } catch { return(null); } }
public void getValues(string table, string column, string keyValue) { try{ dbcmd.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = {2}", table, column, keyValue); rdr = dbcmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine("Vale verga, regreso esto: {0}", rdr.GetFieldType(0)); } } catch (SQLiteException ex) { Console.WriteLine("Error: {0}", ex.ErrorCode); } }
/// <summary> /// 查询字段属性 /// </summary> /// <param name="TableName">表名</param> /// <param name="Field">字段名</param> /// <param name="FieldType">字段类型</param> /// <returns>true 字段存在,false 字段不存在</returns> public bool CheckField(string TableName, string Field, out System.Type FieldType, out bool CanBeNull, out bool IsPrimaryKey) { FieldType = typeof(object); CanBeNull = false; IsPrimaryKey = false; if (string.IsNullOrEmpty(TableName)) { throw new NullReferenceException("TableName Is Null Or Empty"); } else if (string.IsNullOrEmpty(Field)) { throw new NullReferenceException("Field Is Null Or Empty"); } try { SQLiteConnection conn = (SQLiteConnection)GetConnection(); bool res = false; DataTable dt = conn.GetSchema("Columns", new string[] { null, null, TableName }); int m = dt.Columns.IndexOf("COLUMN_NAME"); int n = dt.Columns.IndexOf("ISNULLABLE"); int o = dt.Columns.IndexOf("COLUMN_KEY"); for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; if (dr.ItemArray.GetValue(m).ToString().ToUpper() == Field.ToUpper()) { CanBeNull = dr.ItemArray.GetValue(n).ToString().ToUpper().Contains("YES"); IsPrimaryKey = dr.ItemArray.GetValue(o).ToString().ToUpper().Contains("PRI"); res = true; break; } } if (res) { SQLiteCommand cmd = new SQLiteCommand("select " + Field + " from " + TableName + "", conn); cmd.CommandTimeout = this.Timeout * 1000; SQLiteDataReader da = cmd.ExecuteReader(); FieldType = da.GetFieldType(0); } CloseConnection(conn); return(res); } catch { return(false); } }
public List <string[]> ExecuteToList(string SQL) { SQLiteDataReader rs = Execute(SQL, true); List <string[]> result = new List <string[]>(); int fieldsCount = rs.FieldCount; while (rs.Read()) { List <string> line = new List <string>(); for (int i = 0; i < fieldsCount; i++) { string fileType = rs.GetFieldType(i).ToString(); try { switch (fileType) { default: line.Add(""); break; case "System.String": line.Add(rs.GetString(i)); break; case "System.Int32": line.Add(rs.GetInt32(i).ToString()); break; case "System.Int16": line.Add(rs.GetInt16(i).ToString()); break; case "System.Int64": line.Add(rs.GetInt64(i).ToString()); break; case "System.DateTime": line.Add(rs.GetDateTime(i).ToString()); break; } } catch { line.Add(""); } } result.Add(line.ToArray()); } return(result); }
/// <summary> /// Using a DataReader object, this iterates thru it and returns an object array /// </summary> /// <param name="dataReader"></param> /// <returns></returns> private object[][] SQLRetrieveTableFromReader(SQLiteDataReader reader) { List <List <object> > retrieval = new List <List <object> >(); while (reader.Read()) { List <object> row = new List <object>(); for (int col = 0; col < reader.FieldCount; ++col) { row.Add(SQLReadFromType(reader.GetFieldType(col).ToString(), reader, col)); } retrieval.Add(row); } // Linq returning multidimensional array from List<List<>> return(retrieval.Select(a => a.ToArray()).ToArray()); }
protected override DataTable ExecuteReaderGetAll( String cmdText, Dictionary <String, Object> sqlParamDic = null) { DataTable dt = null; using (SQLiteConnection sqlCon = new SQLiteConnection(SQLOpenCmdText)) { sqlCon.Open(); using (SQLiteCommand sqlCmd = new SQLiteCommand(cmdText, sqlCon)) { if (sqlParamDic != null) { foreach (KeyValuePair <String, Object> kv in sqlParamDic) { sqlCmd.Parameters.AddWithValue(kv.Key, kv.Value); } } using (SQLiteDataReader sqlDataReader = sqlCmd.ExecuteReader()) { dt = new DataTable(); for (int i = 0; i < sqlDataReader.FieldCount; i++) { dt.Columns.Add(sqlDataReader.GetName(i), sqlDataReader.GetFieldType(i)); } while (sqlDataReader.Read()) { DataRow dr = dt.NewRow(); for (int i = 0; i < dt.Columns.Count; i++) { dr[i] = sqlDataReader[i]; } dt.Rows.Add(dr); } } } sqlCon.Close(); } return(dt); }
/// <summary> /// 使用占位符的执行语句 /// </summary> /// <param name="queryString"></param> /// <returns></returns> public DataTable ExecuteQuery(string queryString, Dictionary <string, string> dic) { using (var conn = new SQLiteConnection(_connectionString)) { conn.Open(); using (SQLiteCommand cmd = conn.CreateCommand()) { cmd.CommandText = queryString; foreach (string key in dic.Keys) { cmd.Parameters.Add(new SQLiteParameter(key, dic[key])); } using (dataReader = cmd.ExecuteReader()) { DataTable datatable = new DataTable(); ///动态添加表的数据列 for (int i = 0; i < dataReader.FieldCount; i++) { DataColumn myDataColumn = new DataColumn(); myDataColumn.DataType = dataReader.GetFieldType(i); myDataColumn.ColumnName = dataReader.GetName(i); datatable.Columns.Add(myDataColumn); } ///添加表的数据 while (dataReader.Read()) { DataRow myDataRow = datatable.NewRow(); for (int i = 0; i < dataReader.FieldCount; i++) { myDataRow[i] = dataReader[i].ToString(); } datatable.Rows.Add(myDataRow); myDataRow = null; } return(datatable); } } } }
private async Task <IList <Batch> > GetBatches(string cmdText) { bool needsUpdate = false; Type ColumnOld = typeof(long); Batch batch = new Batch(); IList <Batch> batches = new List <Batch>(); SQLiteConnection connection = await new Database(batch).Connect(); using (SQLiteCommand cmd = new SQLiteCommand(connection)) { cmd.CommandText = cmdText; using (SQLiteDataReader reader = cmd.ExecuteReader()) { if (reader.GetFieldType(3) == ColumnOld) { needsUpdate = true; } while (reader.Read()) { batches.Add(new Batch { ID = reader.GetInt32(0), CreatedBy = reader.GetString(1), CreatedOn = reader.GetDateTime(2), BatchNumber = reader.GetValue(3).ToString(), NeedsConfirmed = bool.Parse(reader.GetString(4)), Complete = bool.Parse(reader.GetString(5)) }); } } } if (needsUpdate == true) { Task.Run(() => CorrectColumn()); } return(batches); }
static public string GetData(SQLiteDataReader reader, string column) { switch (Type.GetTypeCode(reader.GetFieldType(reader.GetOrdinal(column)))) { case TypeCode.Boolean: return(reader.GetBoolean(reader.GetOrdinal(column)).ToString()); case TypeCode.Byte: return(reader.GetByte(reader.GetOrdinal(column)).ToString()); case TypeCode.Char: return(reader.GetChar(reader.GetOrdinal(column)).ToString()); case TypeCode.DateTime: return(reader.GetDateTime(reader.GetOrdinal(column)).ToString("yyyy/mm/dd HH:mm:ss")); case TypeCode.Decimal: return(reader.GetDecimal(reader.GetOrdinal(column)).ToString()); case TypeCode.Double: return(reader.GetDouble(reader.GetOrdinal(column)).ToString()); case TypeCode.Int16: return(reader.GetInt16(reader.GetOrdinal(column)).ToString()); case TypeCode.Int32: return(reader.GetInt32(reader.GetOrdinal(column)).ToString()); case TypeCode.Int64: return(reader.GetInt64(reader.GetOrdinal(column)).ToString()); case TypeCode.String: return(reader.GetString(reader.GetOrdinal(column))); default: return(""); } }
/* * Queries the entire database and populates the ListView */ private bool PopulateDatabaseList() { try { // Query database SQLiteConnection SqlConn = new SQLiteConnection(); SqlConn.ConnectionString = String.Format("Data Source={0};New=False;Version=3", m_strDBFileName); SqlConn.Open(); string strSQLQuery = "SELECT " + "TAG_ACCESSION_NUMBER as [Accession Number]," + "TAG_MODALITY as [Modality]," + "TAG_INSTITUTION_NAME as [Institution Name]," + "TAG_REFERRING_PHYSICIAN_NAME as [Referring Physician Name]," + "TAG_PATIENT_NAME as [Patient Name]," + "TAG_PATIENT_ID as [Patient ID]," + "TAG_PATIENT_BIRTH_DATE as [Patient Birth Date]," + "TAG_PATIENT_SEX as [Patient Sex]," + "TAG_PATIENT_WEIGHT as [Patient Weight]," + "TAG_STUDY_INSTANCE_UID AS [Study Instance UID]," + "TAG_REQUESTING_PHYSICIAN AS [Requesting Physician]," + "TAG_REQUESTED_PROCEDURE_DESCRIPTION AS [Requested Procedure Description]," + "TAG_ADMISSION_ID AS [Admission ID]," + "TAG_SCHEDULED_STATION_AE_TITLE AS [Scheduled Station AE Title]," + "TAG_SCHEDULED_PROCEDURE_STEP_START_DATE AS [Scheduled Procedure Step Start Date]," + "TAG_SCHEDULED_PROCEDURE_STEP_START_TIME AS [Scheduled Procedure Step Start Time]," + "TAG_SCHEDULED_PERFORMING_PHYSICIAN_NAME AS [Scheduled Performing Physician Name]," + "TAG_SCHEDULED_PROCEDURE_STEP_DESCRIPTION AS [Scheduled Procedure Step Description]," + "TAG_SCHEDULED_PROCEDURE_STEP_ID AS [Scheduled Procedure Step ID]," + "TAG_SCHEDULED_PROCEDURE_STEP_LOCATION AS [Scheduled Procedure Step Location]," + "TAG_REQUESTED_PROCEDURE_ID AS [Requested Procedure ID]," + "TAG_REASON_FOR_THE_REQUESTED_PROCEDURE AS [Reason for the Requested Procedure]," + "TAG_REQUESTED_PROCEDURE_PRIORITY AS [Requested Procedure Priority]," + "Item_ID " + "FROM MwlSCPTbl ORDER BY Item_ID;"; SQLiteCommand cmd = SqlConn.CreateCommand(); cmd.CommandText = strSQLQuery; SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.Default); // Create the columns ColumnHeader[] columnHeaders = new ColumnHeader[reader.FieldCount - 1]; //one less field because we don't care about displaying the Item_ID Graphics g = lstDatabase.CreateGraphics(); for (int i = 0; i < columnHeaders.Length; i++) { columnHeaders[i] = new ColumnHeader(); columnHeaders[i].Text = reader.GetName(i); columnHeaders[i].Width = Convert.ToInt32((g.MeasureString(columnHeaders[i].Text, lstDatabase.Font)).Width) + 10; } lstDatabase.Columns.AddRange(columnHeaders); // Create the rows while (reader.Read()) { string[] items = new string[reader.FieldCount]; // We use all fields here, but Item_ID is hidden for (int i = 0; i < reader.FieldCount; i++) { // SQLite stores dates as strings, so avoid the internal conversion if (reader.GetFieldType(i).ToString() == "System.DateTime") { items[i] = reader.GetString(i); } else { items[i] = reader.GetValue(i).ToString(); } } lstDatabase.Items.Add(new ListViewItem(items)); } SqlConn.Close(); } catch (Exception ex) { MessageBox.Show("Error populating listbox with database:\r\n\r\n" + ex.ToString()); return(false); } return(true); }
public List <object> ReadRows(string table, string[] columns, string[] values, object obj) { SQLiteConnection conn = new SQLiteConnection(db_file); try { if (columns == null || columns.Length == 0 || values == null || values.Length == 0 || columns.Length != values.Length) { throw new Exception("Invalid row parameters"); } if (columns.Where((t, idx) => string.IsNullOrEmpty(t) || string.IsNullOrEmpty(values[idx])).Any()) { throw new Exception("Invalid row comparison parameters"); } conn.Open(); StringBuilder sb = new StringBuilder(); sb.Append(string.Format("SELECT * FROM {0} WHERE ", table)); for (int idx = 0; idx < columns.Length; idx++) { if (idx > 0) { sb.Append(" AND "); } sb.Append(string.Format("{0} = '{1}'", columns[idx], values[idx])); } SQLiteCommand cmd = new SQLiteCommand(conn) { CommandText = sb.ToString() }; SQLiteDataReader rdr = cmd.ExecuteReader(); Type ObjType = obj.GetType(); bool isFound = false; List <object> objList = new List <object>(); while (rdr.Read()) { if (!rdr.HasRows || rdr.FieldCount <= 0) { throw new Exception("No Row Returned"); } object instance = Activator.CreateInstance(ObjType); foreach (FieldInfo item in ObjType.GetRuntimeFields().Where(x => x.IsStatic == false)) { for (int i = 0; i < rdr.FieldCount; i++) { if (item.Name.Contains(string.Format("<{0}>", rdr.GetName(i))) && rdr.GetFieldType(i) == item.FieldType) { var v = rdr.GetValue(i); item.SetValue(instance, v); isFound = true; break; } } } if (!isFound) { throw new Exception("Error Reading Fields"); } objList.Add(instance); } rdr.Close(); CloseConnection(conn); return(objList); } catch (Exception e) { Console.WriteLine(e); CloseConnection(conn); return(null); } }
private Note AddNote(string name, string text, int argbColor, DateTime date, DateTime updateDate, Type type, Image screen = null, DateTime alarmDate = new DateTime()) { SQLiteCommand cmd = new SQLiteCommand("SELECT MAX(id)+1 as nextid FROM notes", sqlConn); SQLiteDataReader er = cmd.ExecuteReader(); int nextid; if (er.HasRows) { er.Read(); if (er.GetFieldType(0) == typeof(System.Int64)) { nextid = er.GetInt32(0); } else { nextid = 1; } er.Close(); } else { nextid = 1; } string screnShotPath = screenShotFolderPath + @"\" + name + ".png"; string noteType = type.Name; cmd = new SQLiteCommand(" INSERT INTO `notes` (id, name, text, type, img, color, date, updateDate, isDelete, alarmDate) VALUES (@id, @name, @text, @type, @screnShotPath, @color, @date, @updateDate, @isDelete, @alarmDate);", sqlConn); SQLiteParameter param = new SQLiteParameter("@name", name); cmd.Parameters.Add(param); param = new SQLiteParameter("@text", text); cmd.Parameters.Add(param); param = new SQLiteParameter("@type", noteType); cmd.Parameters.Add(param); param = new SQLiteParameter("@screnShotPath", screnShotPath); cmd.Parameters.Add(param); param = new SQLiteParameter("@date", date); cmd.Parameters.Add(param); param = new SQLiteParameter("@updateDate", updateDate); cmd.Parameters.Add(param); param = new SQLiteParameter("@isDelete", false); cmd.Parameters.Add(param); param = new SQLiteParameter("@color", argbColor); cmd.Parameters.Add(param); param = new SQLiteParameter("@id", nextid); cmd.Parameters.Add(param); if (noteType.Equals("Alarm")) { param = new SQLiteParameter("@alarmDate", alarmDate); } else { param = new SQLiteParameter("@alarmDate", "none"); } cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); Note newNote = null; switch (noteType) { case "Common": newNote = new Common(); break; case "Screenshot": newNote = new Screenshot(screen, screnShotPath); break; case "Alarm": newNote = new Alarm(alarmDate); break; case "ToDoList": newNote = new ToDoList(); break; default: newNote = new Common(); break; } newNote.Id = nextid; newNote.Name = name; newNote.Text = text; newNote.RegDate = Convert.ToDateTime(date); newNote.UpdateDate = Convert.ToDateTime(updateDate); newNote.ARGBColor = argbColor; //string img = er.GetString(4); //string timer = er.GetString(5); return(newNote); }
void SqliteTest1() { WriteLog("Test Begins"); WriteLog("Creating a db in memory..."); string cs = "Data Source=:memory:"; var con = new SQLiteConnection(cs); con.Open(); WriteLog("Requiring SQLite Version..."); string stm = "SELECT SQLITE_VERSION()"; var cmd = new SQLiteCommand(stm, con); string version = cmd.ExecuteScalar().ToString(); WriteLog("SQLite version: " + version); cmd = new SQLiteCommand(con); cmd.CommandText = "DROP TABLE IF EXISTS cars"; cmd.ExecuteNonQuery(); cmd.CommandText = @"CREATE TABLE cars( id INTEGER PRIMARY KEY, name TEXT, price INT, code TEXT, x REAL )"; cmd.ExecuteNonQuery(); /* * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Audi',52642,'A')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Mercedes',57127,'A')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Skoda',9000,'C')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Volvo',29000,'A')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Bentley',350000,'B')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Citroen',21000,'B')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Hummer',41400,'C')"; * cmd.ExecuteNonQuery(); * * cmd.CommandText = "INSERT INTO cars(name, price,code) VALUES('Volkswagen',21600,'A')"; * cmd.ExecuteNonQuery(); */ cmd.CommandText = "INSERT INTO cars(name, price,code,x) VALUES(@name, @price,@code,@x)"; cmd.Parameters.AddWithValue("@name", "BMW"); cmd.Parameters.AddWithValue("@price", 36600); cmd.Parameters.AddWithValue("@code", "E"); cmd.Parameters.AddWithValue("@x", "1.2"); cmd.Prepare(); cmd.ExecuteNonQuery(); WriteLog("Renew the table"); //stm = "SELECT code, sum(price) as summary FROM cars group by code LIMIT 5"; stm = "select * from cars limit 5"; cmd = new SQLiteCommand(stm, con); SQLiteDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { //Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)}"); //var name = rdr.GetString(rdr.GetOrdinal("code")); //var price = rdr.GetDouble(rdr.GetOrdinal("summary")); //WriteLog("Result: name=" + name + " price=" + price); for (int i = 0; i < reader.FieldCount; i++) { WriteLog("Field: " + reader.GetName(i)); WriteLog("Type: " + reader.GetDataTypeName(i)); WriteLog("C#T: " + reader.GetFieldType(i)); WriteLog("Value: " + reader.GetValue(i)); } } }
private void bwExportExcel_DoWork(object sender, DoWorkEventArgs e) { e.Result = true; var fname = e.Argument as string; var fnameWithoutExt = Path.GetFileNameWithoutExtension(fname); var fileExt = Path.GetExtension(fname); var outputDir = Path.GetDirectoryName(fname); try { if (File.Exists(fname)) { File.Delete(fname); } using (FileStream zipToOpen = new FileStream(fname, FileMode.OpenOrCreate)) { using (ZipArchive archive = new ZipArchive(zipToOpen, ZipArchiveMode.Create)) { // Dirs var xlDir = $@"xl"; var worksheetDir = $@"{xlDir}\worksheets"; var relsDir = $@"{xlDir}\_rels"; var generalRelsDir = $@"_rels"; // FNames var contentTypesFileName = $@"[Content_Types].xml"; var workbookFileName = $@"{xlDir}\workbook.xml"; var sharedStringFileName = $@"{xlDir}\sharedStrings.xml"; var stylesFileName = $@"{xlDir}\styles.xml"; var relsFileName = $@"{relsDir}\workbook.xml.rels"; var generalRelsFileName = $@"{generalRelsDir}\.rels"; // Create workbook, styles and other files ZipArchiveEntry readmeEntry = archive.CreateEntry(stylesFileName, CompressionLevel.Optimal); using (var stylesFile = new StreamWriter(readmeEntry.Open())) { stylesFile.Write(excelTemplateStyles, excelTemplateStylesDateFormat); } readmeEntry = archive.CreateEntry(generalRelsFileName, CompressionLevel.Optimal); using (var generalRelsFile = new StreamWriter(readmeEntry.Open())) { generalRelsFile.Write(excelTemplateGeneralRels); } readmeEntry = archive.CreateEntry(sharedStringFileName, CompressionLevel.Optimal); using (var sharedStringFile = new StreamWriter(readmeEntry.Open())) { sharedStringFile.Write(excelTemplateSharedString); } var wbSheetsTemplBuilder = new StringBuilder(); var wbSheetsTemplSheetDefNameBuilder = new StringBuilder(); var ctWorkSheetOverrideBuilder = new StringBuilder(); var relTemplBuilder = new StringBuilder(); // Create worksheets var worksheetsCount = _gridView.RowCount / _maxRowsInFile + 1; using (SQLiteConnection conn = new SQLiteConnection(_connectionString)) { conn.Open(); var SQLWhereFromGrid = CriteriaToWhereClauseHelper.GetOracleWhere(_gridView.ActiveFilterCriteria); var SQLColumns = string.Join(",", _gridView.VisibleColumns.Select(x => x.Name.Substring(3))); if (SQLColumns != "") { var txtQuery = $"SELECT {SQLColumns} FROM {_tableName} {(SQLWhereFromGrid != "" ? ($"WHERE {SQLWhereFromGrid}") : "") }"; using (SQLiteCommand cmd = new SQLiteCommand(txtQuery, conn)) using (SQLiteDataReader rd = cmd.ExecuteReader()) for (var worksheetIndex = 0; worksheetIndex < worksheetsCount; worksheetIndex++) { var worksheetFileName = $@"{worksheetDir}\sheet{worksheetIndex + 1}.xml"; var wsName = worksheetsCount > 1 ? $"Billing #{worksheetIndex + 1}" : "Billing"; readmeEntry = archive.CreateEntry(worksheetFileName, CompressionLevel.Optimal); using (var worksheetFile = new StreamWriter(readmeEntry.Open())) { var startRowInGrid = worksheetIndex * _maxRowsInFile; var rowsInCurrentFile = _gridView.RowCount - startRowInGrid > _maxRowsInFile ? _maxRowsInFile : _gridView.RowCount - startRowInGrid; var endColumnsRange = NumberToLetters(_gridView.VisibleColumns.Count); // Add in builder cur sheet wbSheetsTemplBuilder.AppendFormat(excelTemplateWorkbookSheet, wsName, worksheetIndex + 1, worksheetIndex + 3); wbSheetsTemplSheetDefNameBuilder.AppendFormat(excelTemplateWorkbookSheetDefName, worksheetIndex, wsName, endColumnsRange, rowsInCurrentFile + 1); ctWorkSheetOverrideBuilder.AppendFormat(excelTemplateContentTypesWorkSheetOverride, worksheetIndex + 1); relTemplBuilder.AppendFormat(excelTemplateRelsForWorksheet, worksheetIndex + 3, worksheetIndex + 1); // Create header, coldata and open body in worksheet worksheetFile.Write(excelTemplateWorksheetHeader, endColumnsRange, rowsInCurrentFile + 1, 0); worksheetFile.Write(excelTemplateWorksheetColumn, _gridView.VisibleColumns.Count); worksheetFile.Write(excelTemplateWorksheetBeginBody); // Write header var j = 0; var rowBuilder = new StringBuilder(); foreach (string colName in _gridView.VisibleColumns.Select(x => x.Name)) { j++; rowBuilder.Append(string.Format(excelTemplateWorksheetCol, NumberToLetters(j), 1, 2, "t =\"inlineStr\"", $"<is><t>{System.Net.WebUtility.HtmlEncode(colName.Substring(3))}</t></is>")); } worksheetFile.Write(excelTemplateWorksheetRow, 1, rowBuilder.ToString()); // Export data rows var curProgress = 0; for (var i = 0; i < rowsInCurrentFile; i++) { var res = rd.Read(); if (!res) { break; } rowBuilder = new StringBuilder(); for (j = 0; j < rd.FieldCount; j++) { var value = rd.GetValue(j); var valueType = rd.GetFieldType(j); // <c r ="{0}{1}" s="{2}" {3}>{4}</c> var isDate = (valueType == typeof(DateTime)); var isInt = (valueType == typeof(int)); var useInlineStr = !isDate && !isInt; rowBuilder.Append(string.Format(excelTemplateWorksheetCol, NumberToLetters(j + 1), i + 2, isDate ? 3 : isInt ? 4 : 1, useInlineStr ? "t =\"inlineStr\"" : "", useInlineStr ? $"<is><t>{(value == null || value.GetType() == typeof(System.DBNull) ? "" : System.Net.WebUtility.HtmlEncode(value as string))}</t></is>" : $"<v>{ (value == null || value.GetType() == typeof(System.DBNull) ? "" : (isDate ? ((DateTime)value).ToOADate() : value))}</v>" )); } worksheetFile.Write(excelTemplateWorksheetRow, i + 2, rowBuilder.ToString()); if (bwExportExcel.CancellationPending) { e.Result = false; return; } _pauseManager.WaitOne(); if (100 * (startRowInGrid + i) / _gridView.RowCount > curProgress) { curProgress = 100 * (startRowInGrid + i) / _gridView.RowCount; bwExportExcel.ReportProgress(curProgress); } } worksheetFile.Write(excelTemplateWorksheetEndBody, endColumnsRange, rowsInCurrentFile + 1); } } } } readmeEntry = archive.CreateEntry(contentTypesFileName, CompressionLevel.Optimal); using (var contentTypesFile = new StreamWriter(readmeEntry.Open())) { contentTypesFile.Write(excelTemplateContentTypes, ctWorkSheetOverrideBuilder.ToString()); } readmeEntry = archive.CreateEntry(workbookFileName, CompressionLevel.Optimal); using (var workbookFile = new StreamWriter(readmeEntry.Open())) { workbookFile.Write(excelTemplateWorkbook, wbSheetsTemplBuilder.ToString(), wbSheetsTemplSheetDefNameBuilder.ToString()); } readmeEntry = archive.CreateEntry(relsFileName, CompressionLevel.Optimal); using (var relsFile = new StreamWriter(readmeEntry.Open())) { relsFile.Write(excelTemplateRels, relTemplBuilder); } bwExportExcel.ReportProgress(100); } } } catch (Exception ex) { e.Result = ex; } }
// 执行需要返回值的SQL 语句 // 抛出异常时返回null protected static List <Object> ExecuteSQL(string sql) { if (!File.Exists(Environment.CurrentDirectory + "\\db\\movie.db"))//检查数据库文件是否存在 { MessageBox.Show("本地数据库文件丢失, 请重新安装客户端。"); return(null); } List <Object> vRet = new System.Collections.Generic.List <Object>(); try { string path = Environment.CurrentDirectory + "/db/movie.db"; SQLiteConnection conn = new SQLiteConnection("Data Source=" + Environment.CurrentDirectory + "/db/movie.db"); conn.Open(); SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; //"SELECT * FROM CouponScanRcd"; using (SQLiteDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { Dictionary <string, string> map = new Dictionary <string, string>(); for (int k = 0; k < dr.FieldCount; k++) { string name = dr.GetName(k); string val = null; try { Type type = dr.GetFieldType(k); if (dr.IsDBNull(k)) { val = ""; } else if (StringUtils.equals("String", type.Name)) { val = dr.GetString(k); } else if (StringUtils.equals("Double", type.Name)) { val = dr.GetDouble(k) + ""; } else if (StringUtils.equals("Int64", type.Name)) { val = dr.GetInt64(k) + ""; } else { val = dr.GetString(k); } } catch (System.Exception ex) { Console.WriteLine(ex.StackTrace); throw ex; } if (val == null) { val = ""; } if (name == null) { continue; } map[name] = val; } vRet.Add(map); } } } catch (Exception e) { Console.WriteLine(e.StackTrace); throw e; } return(vRet); }
private void button1_Click(object sender, EventArgs e) { filechoose = false; OpenFileDialog openFileDialog1 = new OpenFileDialog(); openFileDialog1.Filter = "Файл базы данных|*.db"; openFileDialog1.Title = "Выберите файл базы данных"; if (openFileDialog1.ShowDialog() == DialogResult.OK) { filechoose = true; } if (filechoose) { button2.Enabled = true; button4.Enabled = true; button5.Enabled = true; button8.Enabled = true; button9.Enabled = true; dataGridView1.Rows.Clear(); dataGridView1.Columns.Clear(); dataGridView1.Visible = true; SQLiteConnection conn; conn = new SQLiteConnection("Data Source=" + openFileDialog1.FileName + "; Version=3;"); FileName = openFileDialog1.FileName; conn.Open(); DataTable schema = conn.GetSchema("Tables", new string[] { null, null, null, "table" }); tableName = schema.Rows[0][2].ToString(); SQLiteCommand comm = new SQLiteCommand("Select * From " + schema.Rows[0][2], conn); using (read = comm.ExecuteReader()) { xr = new Type[read.FieldCount]; for (var i = 0; i < read.FieldCount; i++) { dataGridView1.Columns.Add(read.GetName(i), read.GetName(i)); xr[i] = read.GetFieldType(i); } int c = 0; while (read.Read()) { dataGridView1.Rows.Add(); for (var i = 0; i < read.FieldCount; i++) { if (xr[i].ToString() != "System.DateTime") { dataGridView1.Rows[c].Cells[i].Value = read.GetValue(i).ToString(); } else { dataGridView1.Rows[c].Cells[i].Value = read.GetValue(i); } } c++; } } for (int j = 0; j < dataGridView1.Columns.Count; j++) { if (xr[j].ToString() == "System.DateTime") { for (int i = 0; i < dataGridView1.Rows.Count; i++) { DateTime rx = (DateTime)dataGridView1.Rows[i].Cells[j].Value; dataGridView1.Rows[i].Cells[j].Value = rx.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture); } } } graphic = new Form4(); graphic.Owner = this; graphic.paint(); dataGridView1.Columns[dataGridView1.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; } }
/// <summary> /// Creates an instance of SpatiaLite provider /// </summary> /// <param name="connectionStr">Connection String to SQLite database file /// ("http://www.connectionstrings.com/sqlite")</param> /// <param name="tablename">Name of the table with geometry information</param> /// <param name="geometryColumnName">Name of the desired geometry column</param> /// <param name="oidColumnName">Name of the object Id column</param> public SpatiaLite(string connectionStr, string tablename, string geometryColumnName, string oidColumnName) { ConnectionString = connectionStr; Table = tablename; GeometryColumn = geometryColumnName; //Name of column to store geometry ObjectIdColumn = oidColumnName; //Name of object ID column try { var op = UseLike ? "LIKE" : "="; using (SQLiteConnection cn = new SQLiteConnection(connectionStr)) { cn.Open(); SQLiteCommand cm = new SQLiteCommand( String.Format( "SELECT \"srid\", \"coord_dimension\", \"spatial_index_enabled\" FROM \"geometry_columns\" WHERE(\"f_table_name\" {2} '{0}' AND \"f_geometry_column\" {2} '{1}');", tablename, geometryColumnName, op), cn); SQLiteDataReader dr = cm.ExecuteReader(); if (dr.HasRows) { dr.Read(); _srid = dr.GetInt32(0); string coordDim; if (dr.GetFieldType(1) == typeof(long)) { coordDim = dr.GetInt64(1).ToString(); } else { coordDim = dr.GetString(1); } switch (coordDim) { case "2": case "XY": _numOrdinateDimensions = 2; break; case "3": case "XYZ": case "XYM": _numOrdinateDimensions = 3; break; case "4": case "XYZM": _numOrdinateDimensions = 4; break; default: throw new Exception("Cannot evaluate number of ordinate dimensions"); } switch (dr.GetInt32(2)) { case 1: //RTree String indexName = string.Format(@"idx_{0}_{1}", tablename, geometryColumnName); String whereClause = @"xmin < {0} AND xmax > {1} AND ymin < {2} AND ymax > {3}"; _spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT pkid FROM {0} WHERE {1})", indexName, whereClause); _spatiaLiteIndex = SpatiaLiteIndex.RTree; _useSpatialIndex = true; break; case 2: //MBRCache indexName = string.Format(@"cache_{0}_{1}", tablename, geometryColumnName); whereClause = "mbr=FilterMbrIntersects({1}, {3}, {0}, {2})"; _spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT ROWID FROM {0} WHERE {1})", indexName, whereClause); _spatiaLiteIndex = SpatiaLiteIndex.MbrCache; _useSpatialIndex = true; break; } } dr.Close(); } } catch (Exception) { _srid = -1; _spatiaLiteIndex = SpatiaLiteIndex.None; _useSpatialIndex = false; } }
/// <inheritdoc /> public Type GetFieldType(int i) { ThrowIfNoReader(); return(_reader.GetFieldType(i)); }
void PgOpcionesPropertyValueChanged(object s, PropertyValueChangedEventArgs e) { DataTable tablaT = null; string nombreTabla = ""; TablaDef Campos = new TablaDef(); OleDbCommand cmdt; string cns = ""; int cnt = 0; clbTablas.Items.Clear(); try { BaseDatos.Archivo = Opciones.ArchivoBD; //falta implementar usuario y clave //Abre base switch (Opciones.MotorBD) { case MotorBaseDatos.SQLite: { cns = string.Format("DataSource={0};", Opciones.ArchivoBD); if (Opciones.Usuario != "") { cns += string.Format("Usuario={0};", Opciones.Usuario); } if (Opciones.clave != "") { cns += string.Format("Password={0};", Opciones.clave); } SQLiteConnection SQCN = new SQLiteConnection(cns); SQCN.Open(); tablaT = SQCN.GetSchema("Tables", new string[] { }); foreach (DataRow ren in tablaT.Rows) { try{ nombreTabla = (string)ren.ItemArray[2]; foreach (object c in ren.ItemArray) { nombreTabla = (string)ren.ItemArray[2]; if (!nombreTabla.StartsWith("sqlite_")) { Campos = new TablaDef(); clbTablas.Items.Add(nombreTabla); SQLiteCommand cmdtsq = SQCN.CreateCommand(); cmdtsq.CommandText = string.Format("select * from {0}", nombreTabla); SQLiteDataReader sqDr = cmdtsq.ExecuteReader(); for (int i = 0; i < sqDr.FieldCount; i++) { Campos.Add(sqDr.GetName(i), sqDr.GetFieldType(i)); } Datos.Add(nombreTabla, Campos); sqDr.Close(); } } } catch (Exception Ex) { Console.WriteLine(Ex.Message); } } } break; case MotorBaseDatos.MsAccess: { cns = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;", Opciones.ArchivoBD); if (Opciones.Usuario != "") { cns += string.Format("Usuario={0};", Opciones.Usuario); } if (Opciones.clave != "") { cns += string.Format("Password={0};", Opciones.clave); } OleDbConnection Cn = new OleDbConnection(cns); Cn.Open(); DataTable aDT = Cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { }); Datos = new Dictionary <string, TablaDef>(); foreach (DataRow ren in aDT.Rows) { nombreTabla = (string)ren.ItemArray[2]; if (!nombreTabla.StartsWith("MSys")) { Campos = new TablaDef(); clbTablas.Items.Add(nombreTabla); cmdt = Cn.CreateCommand(); cmdt.CommandText = string.Format("select * from {0}", nombreTabla); OleDbDataReader aDr = cmdt.ExecuteReader(); for (int i = 0; i < aDr.FieldCount; i++) { Campos.Add(aDr.GetName(i), aDr.GetFieldType(i)); } Datos.Add(nombreTabla, Campos); aDr.Close(); } } } break; } } catch (Exception Ex) { Console.WriteLine(Ex.Message); } }