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