private void pullData(String sql) { try { SQLiteConnection connection = getConnection(); connection.Open(); sCommand = new SQLiteCommand(sql, connection); sAdapter = new SQLiteDataAdapter(sCommand); sBuilder = new SQLiteCommandBuilder(sAdapter); sDs = new DataSet(); sAdapter.Fill(sDs, "student"); sTable = sDs.Tables["student"]; connection.Close(); dataGridView1.DataSource = sDs.Tables["student"]; dataGridView1.ReadOnly = true; dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; } catch (Exception e) { MessageBox.Show("Error Fetching Data!\n" + e.Message); } }
// Summary: // Load tables from database into a DataSet // WARNING consumes a lot of memory since it loads the entire database into memory! public void LoadDatabase() { if (m_OpenTables != null) { return; } m_OpenTables = new List <OpenTable>(); foreach (DataTable currTable in m_Dataset.Tables) { OpenTable newTable = new OpenTable(); newTable.Table = currTable; newTable.Adapter = new SQLiteDataAdapter("Select * from " + currTable.TableName, m_DbConnection); newTable.Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; newTable.Adapter.Fill(m_Dataset, currTable.TableName); SQLiteCommandBuilder cmdBuilder = new SQLiteCommandBuilder(newTable.Adapter); m_OpenTables.Add(newTable); } }
public static bool SQLiteUpdate(SQLiteDataAdapter SQLAdaptor, DataTable SQLTable) { try{ SQLiteCommandBuilder cmdBuilder = new SQLiteCommandBuilder(SQLAdaptor); cmdBuilder.ConflictOption = ConflictOption.OverwriteChanges; SQLAdaptor.UpdateCommand = cmdBuilder.GetUpdateCommand(); SQLAdaptor.InsertCommand = cmdBuilder.GetInsertCommand(); SQLAdaptor.DeleteCommand = cmdBuilder.GetDeleteCommand(); SQLAdaptor.MissingSchemaAction = MissingSchemaAction.AddWithKey; SQLAdaptor.Update(SQLTable); SQLTable.AcceptChanges(); return(true); }catch (Exception ex) { if (ex.Message.ToString().IndexOf("conflicted with the REFERENCE constraint") != 0) { SQLTable.RejectChanges(); System.Windows.MessageBox.Show("Mẫu tin đã được sử dụng. Bạn không thể xóa...", "Thông báo", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error); } return(false); } }
public void selectQuery(string query) { try { DataTable DT; using (SQLiteDataAdapter sqlDa = new SQLiteDataAdapter(query, connection)) { SQLiteCommandBuilder cb = new SQLiteCommandBuilder(sqlDa); DT = new DataTable(); sqlDa.Fill(DT); numRows = DT.Rows.Count; //sqlDataTable.Clear(); sqlDataTable = DT; //DT.Clear(); } } catch (Exception ex) { handleError(ex); } }
public static void InputRecord(DataTable dt) { try { using (SQLiteConnection conn = new SQLiteConnection(Dbhelper.Db.ConnectionStr)) { SQLiteCommand comm = new SQLiteCommand(" select * from CBTempChargeRecord limit 1", conn); conn.Open(); SQLiteTransaction ts = conn.BeginTransaction(); SQLiteDataAdapter dadpter = new SQLiteDataAdapter(comm); SQLiteCommandBuilder buider = new SQLiteCommandBuilder(dadpter); dadpter.InsertCommand = buider.GetInsertCommand(); dadpter.Update(dt); ts.Commit(); conn.Close(); } } catch (Exception ex) { throw ex; } }
public bool Save(string path) { try { conn = new SQLiteConnection("Data Source=" + path); conn.Open(); SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = string.Format("SELECT * FROM {0}", TableName); SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter); adapter.Update(table); return(true); } catch (Exception) { return(false); } finally { conn.Close(); } }
private static void DeleteAlbum(int albumId) { Console.WriteLine("delete album {0}", albumId); SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter("SELECT * FROM Album", _connection); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); DataTable albums = dataSet.Tables[0]; IEnumerable <DataRow> albumsQuery = from album in albums.AsEnumerable() where album["AlbumId"].ToString() == albumId.ToString() select album; DataRow albumRow = albumsQuery.ToArray()[0]; albumRow.Delete(); SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(dataAdapter); var rowsAffected = dataAdapter.Update(dataSet); Console.WriteLine("{0} rows deleted", rowsAffected); }
private void CveDataGridView_KeyDown(object sender, KeyEventArgs e) { switch (e.KeyCode) { case Keys.F5: SelectAll(); break; case Keys.Delete: DialogResult result; result = MessageBox.Show("确定要删除!" + "ID=" + CveDataGridView.Rows[CveDataGridView.CurrentRow.Index].Cells["ID"].Value, "删除", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); if (result != DialogResult.OK) { CveDataGridView.ClearSelection(); } else { try { this.ds.AcceptChanges(); SQLiteCommandBuilder scb = new SQLiteCommandBuilder(this.mAdapter); ds.Tables[0].Rows[CveDataGridView.CurrentRow.Index].Delete(); // dt.Rows[CveDataGridView.CurrentRow.Index].Delete(); this.mAdapter.DeleteCommand = scb.GetDeleteCommand(); this.mAdapter.Update(ds, this.t_name); CveDataGridView.ClearSelection(); } catch (Exception) { } } break; } }
public SQLiteDataAdapter GetDataAdapter(string sql, Boolean setupCommandBuilder = true) { var adapter = new SQLiteDataAdapter(sql, this.conn); // or alternatively (same as above) //var adapter = new SQLiteDataAdapter(); //adapter.SelectCommand = new SQLiteCommand(sql, this.conn); // We want adapter to be writable // We need to set these properties // //adapter.UpdateCommand //adapter.InsertCommand; //adapter.DeleteCommand // // In order you want to truncateDatabase adapter manualy, continue to this link: // http://msdn.microsoft.com/cs-cz/library/33y2221y(v=vs.71).aspx // In this app, we will use only one table, so take advantage of CommandBuilder // Something to read: http://msdn.microsoft.com/cs-cz/library/tf579hcz(v=vs.71).aspx // In order to use CommandBuilder, we need to use: // - select return primary column // - select will be executed; affects performace // - Great! When database was changed before I call update DBConcurrencyException is thrown. // - Only for stand-alone tables; no foreign keys! // Make adapter writable using CommandBuilder if (setupCommandBuilder) { SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); adapter.DeleteCommand = builder.GetDeleteCommand(); } return(adapter); }
// /// <summary> /// 查询并返回datatable. /// 注意; 一个Form窗口中仅一个GridView,且只能用一个“缓存对象”。不然无法使用update()更新,并报“对于多个基表不支持动态SQL生成”异常。 /// 多个GridView,其它GridView请使用“单例模式”。 /// /// sql语句,不支直接传数组参数。 /// 例: /// select * from tablename where id in (@ids) /// ids必须自行组合成字符串,在通过SQLiteParameter传字符串类型绑定参数,不能直接把数组传给ids. /// </summary> /// <param name="commandText">SQL语句</param> /// <param name="commandParameters">不支持数组。对于in子句的数组查询,需要自动组合成字符串</param> /// <returns></returns> public DataTable ExecuteDataTable(string commandText, params SQLiteParameter[] commandParameters) { if (command.Connection.State == ConnectionState.Closed) { command.Connection.Open(); } command.CommandText = commandText; if (commandParameters != null) { command.Parameters.AddRange(commandParameters); } adapter = new SQLiteDataAdapter(command); //必须设置,不然不能用 adapter.Update() 方法 SQLiteCommandBuilder scb = new SQLiteCommandBuilder(adapter); //adapter.UpdateCommand = scb; DataTable data = new DataTable(); adapter.Fill(data); return(data); }
public void AddFlightPlan(FlightPlan flightPlan) { SQLiteCommandBuilder builder = new SQLiteCommandBuilder(this.planDataAdapter); DataRow row = planDataSet.Tables["FlightPlanTable"].NewRow(); row["Flight_id"] = flightPlan.Flight_id; row["Passengers"] = flightPlan.Passengers; row["Initial_Longitude"] = flightPlan.Initial_Location.Initial_Longitude; row["Initial_Latitude"] = flightPlan.Initial_Location.Initial_Latitude; row["Initial_Date_time"] = flightPlan.Initial_Location.Initial_Date_time.ToString("yyyy-MM-ddTHH:mm:ssZ"); row["End_Flight_Time"] = flightPlan.End_Flight_Time.ToString("yyyy-MM-ddTHH:mm:ssZ"); row["Company_name"] = flightPlan.Company_name; this.planDataSet.Tables["FlightPlanTable"].Rows.Add(row); builder.GetInsertCommand(); try { this.planDataAdapter.Update(planDataTable); } catch { throw new IDataBaseFlightPlan.ErrorMissingInformationObject(); } try { AddFlightPlanSegmants(flightPlan); } catch (Exception e) { row.Delete(); throw e; } }
//TODO improve with sql injection protection public DataSet selectStatement(String columns, String from, String where) { String sql = "SELECT " + columns + " FROM " + from; if (where != null) { sql += " WHERE " + where; } //SQLiteCommand command = new SQLiteCommand(sql, dbh1); var dataAdapter = new SQLiteDataAdapter(sql, dbh1); var commandBuilder = new SQLiteCommandBuilder(dataAdapter); var ds = new DataSet(); dataAdapter.Fill(ds, from); //SQLiteDataReader reader = command.ExecuteReader(); //Console.WriteLine("====================", reader["txtStandbyWarning"].ToString()); return(ds); }
private void writeToSQL() { try { sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;"); sqlite_conn.Open(); sqlite_cmd = sqlite_conn.CreateCommand(); sqlite_cmd.CommandText = "CREATE TABLE IF NOT EXISTS files_watched (file_name varchar(50), path_name varchar(100), event_occurred varchar(10), day_time date, extension varchar(10));"; sqlite_cmd.ExecuteNonQuery(); var adapter = new SQLiteDataAdapter("select * from files_watched", sqlite_conn); var builder = new SQLiteCommandBuilder(adapter); adapter.Update(dataSet, "Files_Watched"); sqlite_conn.Close(); } catch (Exception e) { MessageBox.Show(e.Message); } }
public override int BlockCommand(System.Data.DataTable dt) { lock (lockObject) { if (!CheckConn()) { return(0); } if (dt.TableName == "") { All.Class.Error.Add("无法完成批量数据更新,数据表名不能为空"); All.Class.Error.Add(Environment.StackTrace); return(0); } int result = 0; try { using (SQLiteCommand cmd = new SQLiteCommand(string.Format("select * from {0}", dt.TableName), conn)) { using (SQLiteDataAdapter ada = new SQLiteDataAdapter(cmd)) { using (SQLiteCommandBuilder scb = new SQLiteCommandBuilder(ada)) { ada.InsertCommand = scb.GetInsertCommand(); ada.DeleteCommand = scb.GetDeleteCommand(); ada.UpdateCommand = scb.GetUpdateCommand(); result = ada.Update(dt); } } } } catch (Exception e) { All.Class.Error.Add(string.Format("出错Table为:{0}", dt.TableName)); All.Class.Error.Add(e);//数据库中一定要有主键,不然当前方法会出错。即没有办法生成删除命令 } return(result); } }
public int Update(DataSet ds, string tablename, ref SQLiteCommand SqlItecmd) { int result = -1; this.Open(); using (SQLiteDataAdapter sqladapter = new SQLiteDataAdapter(SqlItecmd)) { using (SQLiteCommandBuilder sqlcommandbuilder = new SQLiteCommandBuilder(sqladapter)) { if (string.Empty.Equals(tablename)) { result = sqladapter.Update(ds); } else { result = sqladapter.Update(ds, tablename); } } } this.Close(); return(result); }
public void insertDefaultData(DataSet data) { int count = 0; SQLiteConnection db = new SQLiteConnection(connectionString); try { foreach (DataTable tabla in data.Tables) { count++; tabla.TableName = getname(count); db.Open(); SQLiteCommand command = db.CreateCommand(); command.CommandText = (string.Format("DELETE FROM {0}", tabla.TableName)); command.ExecuteNonQuery(); var cmd = db.CreateCommand(); cmd.CommandText = string.Format("SELECT * FROM {0}", tabla.TableName); SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd); adapter.AcceptChangesDuringFill = true; SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter); int asdf = adapter.Update(tabla); db.Close(); } } catch (Exception ex) { log.Error(ex); throw; } finally { db.Dispose(); } }
public static int UpdateTableInTranscation(string sql, DataTable table) { SQLiteDataAdapter dta = new SQLiteDataAdapter(sql, transConn); SQLiteCommandBuilder scb = new SQLiteCommandBuilder(dta); dta.InsertCommand = scb.GetInsertCommand(); dta.UpdateCommand = scb.GetUpdateCommand(); DataSet DS = new DataSet(); dta.FillSchema(DS, SchemaType.Source, "Temp");//加载表架构 注意 dta.Fill(DS, "Temp"); DataTable DT = DS.Tables["Temp"]; foreach (DataRow dr in table.Rows) { DataRow _dr = DT.NewRow(); foreach (DataColumn col in DT.Columns) { if (table.Columns.Contains(col.ColumnName)) { _dr[col.ColumnName] = dr[col.ColumnName]; } } DT.Rows.Add(_dr); } //插入数据 int result = dta.Update(DT); DS.AcceptChanges(); dta.Dispose(); DS.Clear(); return(result); }
void checkingPassword() { SQLiteConnection oSQLiteConnection = new SQLiteConnection("Data Source=D:\\Projects\\PPIU-Lab2\\PPIU-Lab2\\Database.s3db"); SQLiteCommand oCommand = oSQLiteConnection.CreateCommand(); oCommand.CommandText = "SELECT Haslo FROM User WHERE Haslo='" + psbHaslo.Password + "'"; m_oDataAdapter = new SQLiteDataAdapter(oCommand.CommandText, oSQLiteConnection); SQLiteCommandBuilder oCommandBuilder = new SQLiteCommandBuilder(m_oDataAdapter); m_oDataSet = new DataSet(); m_oDataAdapter.Fill(m_oDataSet); m_oDataTable = m_oDataSet.Tables[0]; DataRow count = null; bool czyUdaloSie = true; try { count = m_oDataTable.Rows[0]; } catch (Exception) { count = null; czyUdaloSie = false; } if (count != null && czyUdaloSie == true) { succesHaslo = true; } else { MessageBox.Show("Podane Hasło jest nieprawidłowe!"); } m_oDataAdapter.Dispose(); m_oDataAdapter = null; }
/// <summary> /// Selects all rows from a table where all of a list of predicates are true /// </summary> /// <typeparam name="T"></typeparam> /// <param name="tableName">name of the table to select from</param> /// <param name="predicates">list of predicates, all of which must evaluate to true</param> /// <param name="predicateConjunction">how the list of predicates should be joined when performing the query</param> /// <returns>a list of data containers, each containing one row of the results, or null if there were no results</returns> public List <T> Select <T>(string tableName, List <DBPredicate> predicates, DBDataContainerCreator <T> dataContainerCreator, DBConjunction predicateConjunction) where T : DBDataContainer <T> { lock ( m_databaseLock ) { _VerifyConnection(); List <T> queryResults = new List <T>(); try { m_sqlConnection.Open(); using (SQLiteCommand command = m_sqlConnection.CreateCommand()) { SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); command.CommandText = $"SELECT * FROM {builder.QuoteIdentifier(tableName)} WHERE "; _AppendPredicates(command, predicates, predicateConjunction.ToString()); command.CommandText += ";"; queryResults = _ExecuteSelectCommand <T>(command, dataContainerCreator); } } catch (Exception crap) { m_sqlConnection.Close(); throw crap; } finally { m_sqlConnection.Close(); } return(queryResults); } }
public void AddFlightPlanSegmants(FlightPlan flightPlan) { SQLiteCommandBuilder builderSeg = new SQLiteCommandBuilder(this.segmantsDataAdapter); int numSeg = flightPlan.Segments.Length; for (int i = 0; i < numSeg; i++) { DataRow segRow = segmantsDataSet.Tables["Segmants"].NewRow(); segRow["id"] = flightPlan.Flight_id + i.ToString(); segRow["Flight_id"] = flightPlan.Flight_id; segRow["Segments_Longitude"] = flightPlan.Segments[i].Segments_Longitude; segRow["Segments_Latitude"] = flightPlan.Segments[i].Segments_Latitude; segRow["Time_Span_Second"] = flightPlan.Segments[i].Time_Span_Second; this.segmantsDataSet.Tables["Segmants"].Rows.Add(segRow); builderSeg.GetInsertCommand(); try { this.segmantsDataAdapter.Update(segmantsDataTable); } catch { segRow.Delete(); throw new IDataBaseFlightPlan.ErrorMissingInformationObject(); } } }
public static int ExecuteInsert(string tableName, params Dictionary <string, object>[] dic) { using (SQLiteConnection connection = GetSQLiteConnection()) { SQLiteDataAdapter a = new SQLiteDataAdapter("select * from " + tableName + " where 1=2", connection); SQLiteCommandBuilder b = new SQLiteCommandBuilder(a); DataSet ds = new DataSet(); a.Fill(ds); for (int i = 0; i < dic.Length; i++) { DataRow dr = ds.Tables[0].NewRow(); IDictionaryEnumerator Mycollection = dic[i].GetEnumerator(); while (Mycollection.MoveNext()) { dr[Mycollection.Key.ToString()] = Mycollection.Value != null ? (Mycollection.Value.ToString() != "" ? Mycollection.Value : DBNull.Value) : DBNull.Value; } ds.Tables[0].Rows.Add(dr); } lock (writeLock) a.Update(ds); return(dic.Length); } }
private void Form2_Load(object sender, EventArgs e) { if (connection.State == ConnectionState.Closed) { connection.Open(); } SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(adapter); // SQLiteDataAdapter adapter = new SQLiteDataAdapter(command); adapter.Fill(dataSt); dataGridView1.DataSource = dataSt.Tables[0]; bindingSrc = new BindingSource(); bindingSrc.DataSource = dataSt.Tables[0]; if (connection.State == ConnectionState.Open) { connection.Close(); } Form1 f2 = new Form1(); f2.Close(); }
private void _AppendPredicates(SQLiteCommand command, List <DBPredicate> predicates, string conjunction) { if (command == null) { return; } SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); int valueIndex = command.Parameters.Count; for (int i = 0; i < predicates.Count; i++) { command.CommandText += $"{builder.QuoteIdentifier(predicates[i].Column)} {_OperatorToString(predicates[i].Operator)} @value{valueIndex}"; command.Parameters.AddWithValue($"@value{valueIndex}", predicates[i].Value); if (i < predicates.Count - 1) { command.CommandText += $" {conjunction} "; } valueIndex++; } }
public void DeleteAlbum(string AlbumName) { SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter("SELECT * FROM Album", connection); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); DataTable albums = dataSet.Tables[0]; IEnumerable <DataRow> albumsQuery = from album in albums.AsEnumerable().AsParallel() where album["Title"].ToString() == AlbumName select album; DataRow albumRow = albumsQuery.ToArray()[0]; albumRow.Delete(); SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(dataAdapter); dataAdapter.Update(dataSet); }
public bool Execute(DataTable dtExcute) { sqlDataAdapter = null; try { if (openConn()) { sqlDataAdapter = new SQLiteDataAdapter(string.Format("SELECT * FROM [{0}]", dtExcute.TableName), sqlConnection); SQLiteCommandBuilder cmdBd = new SQLiteCommandBuilder(sqlDataAdapter); cmdBd.ConflictOption = ConflictOption.OverwriteChanges; sqlDataAdapter.DeleteCommand = cmdBd.GetDeleteCommand(true); sqlDataAdapter.InsertCommand = cmdBd.GetInsertCommand(true); sqlDataAdapter.UpdateCommand = cmdBd.GetUpdateCommand(true); int iRS = sqlDataAdapter.Update(dtExcute); } } catch (Exception ex) { closeConn(); return(false); } return(true); }
internal void event_CommitDataTableChanges(string tableName, DataTable dataTable) { event_OpenConnection(); string query = "SELECT * FROM [" + tableName + "]"; SQLiteDataAdapter dataAdapter; dataAdapter = new SQLiteDataAdapter(query, connection); SQLiteCommandBuilder commandBuilder; commandBuilder = new SQLiteCommandBuilder(dataAdapter); dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand(); dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(); dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); dataAdapter.Update(dataTable); commandBuilder.Dispose(); dataAdapter.Dispose(); event_CloseConnection(); }
private void button1_Click(object sender, EventArgs e) { if (comboBox1.Text.Length > 0) { inputFile = "database.s3db"; dbConnection = String.Format("Data Source={0};Version=3;Password=KraQlin;", inputFile); //using ( cnn = new SQLiteConnection(dbConnection); // ) // { // string sql = "SELECT * FROM incomehddetails where isubhd != '" + null + "' and imainhd = '" + comboBox3.Text + "' ;"; // SQLiteCommand sqlCmd = new SQLiteCommand(sql, cnn); cnn.Open(); // SQLiteDataReader sqlReader = sqlCmd.ExecuteReader(); nam = comboBox1.Text; a = dateTimePicker1.Value.ToString("yyyy-MM-dd"); b = dateTimePicker2.Value.ToString("yyyy-MM-dd"); ad = new SQLiteDataAdapter("SELECT * FROM addindetails WHERE date >='" + a + "' AND date <= '" + b + "'AND name = '" + comboBox1.Text + "'", cnn); SQLiteCommandBuilder sqlCommandBuilder = new SQLiteCommandBuilder(ad); dataTable = new DataTable(); ad.Fill(dataTable); BindingSource bindingSource = new BindingSource(); bindingSource.DataSource = dataTable; dataGridView1.DataSource = bindingSource; // DataTable dt = new DataTable(); // ad.Fill(dt); // dataGridView1.DataSource = dt; // sqlReader.Close(); //} } else { MessageBox.Show("please fill data"); } }
/// <summary> /// Add part into db, return new part id for inserting points /// </summary> /// <param name="parentId">pid of directory</param> /// <param name="newName">new name for part</param> /// <param name="newPartId">returned new part id</param> /// <returns></returns> public bool AddPart(int parentId, string newName, out int newPartId) { newPartId = -1; try { using (var ap = new SQLiteDataAdapter("SELECT * FROM HCParts;", _conn)) { var dt = new DataTable(); ap.Fill(dt); int partId = dt.Rows.Count == 0 ? 0 : dt.AsEnumerable().Select(t => Convert.ToInt32(t["PartID"])).Max(); int sortPos = dt.Rows.Count == 0 ? 0 : dt.AsEnumerable().Select(t => Convert.ToInt32(t["SortPos"])).Max(); var dr = dt.NewRow(); dr["PartID"] = partId + 1; dr["PartName"] = newName; dr["DictID"] = parentId; dr["SortPos"] = sortPos + 1; dr["PartType"] = 0; dt.Rows.Add(dr); var sqlCmdBuilder = new SQLiteCommandBuilder(ap); if (ap.Update(dt) > 0) { newPartId = partId + 1; } } } catch { return(false); } return(true); }
public bool UpdateDataSet(DataSet dataSet, bool setInsertOn = true, bool setModifyOn = true, string additionalMessage = "") { try { using (var con = new SQLiteConnection(Settings.ConnectionString)) { foreach (DataTable tbl in dataSet.Tables) { using (var adapter = new SQLiteDataAdapter(string.Format(@"SELECT * FROM {0} WHERE 1=0", tbl.TableName), con)) { using (var cmd = new SQLiteCommandBuilder(adapter)) { adapter.Update(tbl); cmd.Dispose(); } adapter.Dispose(); } } } return(true); } catch (Exception ex) { SLLog.WriteError(new LogData { Source = ToString(), FunctionName = "UpdateDataSet Error!", AdditionalMessage = additionalMessage, Ex = ex, }); if (Settings.ThrowExceptions) { throw new Exception("UpdateDataSet Error!", ex); } return(false); } }
private DataSet Sqlite() { DataSet _dsResource = new DataSet(); try { string DBPath = string.Format(Application.StartupPath + @"\DB\Test.db"); string connString = String.Format("Data Source={0};New=False;Version=3", DBPath); SQLiteConnection sqlconn = new SQLiteConnection(connString); sqlconn.Open(); // 20140901 string CommandText = string.Format("SELECT * FROM T_Driver ;"); SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(CommandText, sqlconn); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(dataAdapter); //2018.03.06 Gideon #22899 인사이트 개별실행 DataTable dt_CHN_AREACODE = new DataTable("Driver"); dataAdapter.FillSchema(dt_CHN_AREACODE, SchemaType.Source); foreach (DataColumn dc in dt_CHN_AREACODE.Columns) { if (dc.DataType == typeof(long)) { dc.DataType = typeof(Int32); } } dataAdapter.Fill(dt_CHN_AREACODE); _dsResource.Tables.Add(dt_CHN_AREACODE); return(_dsResource); //DataRowCollection dataRowCol = _dsResource.Tables["MA_CHN_AREACODE"].Rows; } catch { return(null); } }
/// <summary> /// Turn a datatable into a table in the temporary database for the connection /// </summary> /// <param name="cnn">The connection to make the temporary table in</param> /// <param name="table">The table to write out</param> /// <param name="dest">The temporary table name to write to</param> private void DataTableToTable(SQLiteConnection cnn, DataTable table, string dest) { StringBuilder sql = new StringBuilder(); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); using (SQLiteCommand cmd = cnn.CreateCommand()) using (DataTable source = new DataTable()) { sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest)); string separator = String.Empty; SQLiteConnectionFlags flags = cnn.Flags; foreach (DataColumn dc in table.Columns) { DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType); string typeName = SQLiteConvert.DbTypeToTypeName(cnn, dbtypeName, flags); sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator); separator = ", "; } sql.Append(")"); cmd.CommandText = sql.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest)); using (SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd)) { builder.DataAdapter = adp; adp.Fill(source); foreach (DataRow row in table.Rows) { object[] arr = row.ItemArray; source.Rows.Add(arr); } adp.Update(source); } } }