C# SQLite 欄位(Column) 存在與否判斷(搜尋) / 新增(補上)
C# SQLite 欄位(Column) 存在與否判斷(搜尋) / 新增(補上)
資要來源:
https://www.codegrepper.com/code-examples/sql/android+sqlite+add+column+if+not+exists
https://stackoverflow.com/questions/2557937/how-do-i-get-column-names-to-print-in-this-c-sharp-program
C# 條列資料表內所有欄位名稱
foreach (DataColumn column in loadDT.Columns)
{
Console.Write("Item: ");
Console.Write(column.ColumnName);
Console.Write(" ");
Console.WriteLine(row[column]);
}
Android新增欄位SQL語法
Cursor cursor = database.rawQuery("SELECT * FROM MY_TABLE", null); // grab cursor for all data
int deleteStateColumnIndex = cursor.getColumnIndex("MISSING_COLUMN"); // see if the column is there
if (deleteStateColumnIndex < 0) {
// missing_column not there - add it
database.execSQL("ALTER TABLE MY_TABLE ADD COLUMN MISSING_COLUMN int null;");
}
=====================
自己開發的C#函數
public static void DBColumnsPadding(String StrTable, String StrColumn,String StrType,String StrPreset="null")
{
String SQL = "";
bool blnCheck = false;
SQL = String.Format("SELECT * FROM {1} LIMIT 0,1", StrColumn, StrTable);
DataTable chaekDataTable = GetDataTable(SQL);
if(chaekDataTable != null)
{
foreach (DataColumn column in chaekDataTable.Columns)
{
if(column.ColumnName == StrColumn)
{
blnCheck = true;
break;
}
}
}
if(!blnCheck)
{
//---
//新增對應欄位
//ALTER TABLE MY_TABLE ADD COLUMN MISSING_COLUMN int null
SQL = String.Format("ALTER TABLE {1} ADD COLUMN {0} {2} DEFAULT {3}", StrColumn, StrTable, StrType,StrPreset);
SQLiteInsertUpdateDelete(SQL);
//---新增對應欄位
//---
//新增欄位後，清空該資料表內容，藉此強迫觸發資料全部更新機制
SQL = String.Format("DELETE FROM {0}", StrTable);
SQLiteInsertUpdateDelete(SQL);
//---新增欄位後，清空該資料表內容，藉此強迫觸發資料全部更新機制
}
}
自己開發的C#完整函數庫
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;//SQLiteConnection
using Dapper;//DynamicParameters
using System.Data;//IDbConnection
using System.Configuration;//ConfigurationManager
namespace CS_VPOS
{
public class SQLDataTableModel
{
private static bool m_blnlogfile = true;
public static string ConnectionStringLoad(string id = "Default")
{
return ConfigurationManager.ConnectionStrings[id].ConnectionString;
}
//---
//C# 通用操作語法 ~ for System.Data.SQLite.Core & Finisar.SQLite 元件
//https://einboch.pixnet.net/blog/post/248187728
public static SQLiteConnection OpenConn(string Database)//資料庫連線程式
{
string cnstr = ConnectionStringLoad(Database);
SQLiteConnection icn = new SQLiteConnection();
icn.ConnectionString = cnstr;
if (icn.State == ConnectionState.Open) icn.Close();
icn.Open();
return icn;
}
public static SQLiteConnection OpenConn()//資料庫連線程式
{
string cnstr = ConnectionStringLoad("Default");
SQLiteConnection icn = new SQLiteConnection();
icn.ConnectionString = cnstr;
if (icn.State == ConnectionState.Open) icn.Close();
icn.Open();
return icn;
}
public static void CreateSQLiteDatabase(string Database)//建立資料庫程式
{
string cnstr = string.Format("Data Source=" + Database + ";Version=3;New=True;Compress=True;");
SQLiteConnection icn = new SQLiteConnection();
icn.ConnectionString = cnstr;
icn.Open();
icn.Close();
}
public static void CreateSQLiteTable( string CreateTableString)//建立資料表程式
{
SQLiteConnection icn = OpenConn();//OpenConn(Database);
SQLiteCommand cmd = new SQLiteCommand(CreateTableString, icn);
SQLiteTransaction mySqlTransaction = icn.BeginTransaction();
try
{
cmd.Transaction = mySqlTransaction;
cmd.ExecuteNonQuery();
mySqlTransaction.Commit();
}
catch (Exception ex)
{
mySqlTransaction.Rollback();
//throw (ex);
}
if (icn.State == ConnectionState.Open) icn.Close();
}
public static void SQLiteInsertUpdateDelete(string Database, string SqlSelectString)//新增資料程式
{
SyncThread.m_blnWait = true;
SQLiteConnection icn = OpenConn(Database);
SQLiteCommand cmd = new SQLiteCommand(SqlSelectString, icn);
SQLiteTransaction mySqlTransaction = icn.BeginTransaction();
try
{
cmd.Transaction = mySqlTransaction;
cmd.ExecuteNonQuery();
mySqlTransaction.Commit();
if (m_blnlogfile)
{
String StrLog = String.Format("{0}: {1};{2}", "sync_SQLiteInsertUpdateDelete", SqlSelectString, "success");
FileLib.logFile("SQLNormal.log", StrLog);
}
}
catch (Exception ex)
{
mySqlTransaction.Rollback();
//throw (ex);
if (m_blnlogfile)
{
String StrLog = String.Format("{0}: {1};{2}", "sync_SQLiteInsertUpdateDelete", SqlSelectString, ex.ToString());
FileLib.logFile("SQLError.log", StrLog);
}
}
if (icn.State == ConnectionState.Open) icn.Close();
SyncThread.m_blnWait = false;
}
public static void SQLiteInsertUpdateDelete(string SqlSelectString)//新增資料程式
{
// The critical section.
SQLiteConnection icn = OpenConn();//OpenConn(Database);
SQLiteCommand cmd = new SQLiteCommand(SqlSelectString, icn);
SQLiteTransaction mySqlTransaction = icn.BeginTransaction();
try
{
cmd.Transaction = mySqlTransaction;
cmd.ExecuteNonQuery();
mySqlTransaction.Commit();
if (m_blnlogfile)
{
String StrLog = String.Format("{0}: {1};{2}", "vpos_SQLiteInsertUpdateDelete", SqlSelectString, "success");
FileLib.logFile("SQLNormal.log", StrLog);
}
}
catch (Exception ex)
{
mySqlTransaction.Rollback();
if (m_blnlogfile)
{
String StrLog = String.Format("{0}: {1};{2}", "vpos_SQLiteInsertUpdateDelete", SqlSelectString, ex.ToString());
FileLib.logFile("SQLError.log", StrLog);
}
}
if (icn.State == ConnectionState.Open) icn.Close();
}
public static DataTable GetDataTable(string Database, string SQLiteString)//讀取資料程式
{
SyncThread.m_blnWait = true;
DataTable myDataTable = new DataTable(Database);
try
{
SQLiteConnection icn = OpenConn(Database);
SQLiteDataAdapter da = new SQLiteDataAdapter(SQLiteString, icn);
DataSet ds = new DataSet();
ds.Clear();
da.Fill(ds);
myDataTable = ds.Tables[0];
if (icn.State == ConnectionState.Open) icn.Close();
if (m_blnlogfile)
{
String StrLog = String.Format("{0}: {1};{2}", "sync_GetDataTable", SQLiteString, "success");
FileLib.logFile("SQLNormal.log", StrLog);
}
}
catch (Exception ex)
{
if (m_blnlogfile)
{
String StrLog = String.Format("{0}: {1};{2}", "sync_GetDataTable", SQLiteString, ex.ToString());
FileLib.logFile("SQLError.log", StrLog);
}
}
SyncThread.m_blnWait = false;
return myDataTable;
}
public static DataTable GetDataTable(string SQLiteString)//讀取資料程式
{
DataTable myDataTable = new DataTable();
try
{
SQLiteConnection icn = OpenConn();//OpenConn(Database);
SQLiteDataAdapter da = new SQLiteDataAdapter(SQLiteString, icn);
DataSet ds = new DataSet();
ds.Clear();
da.Fill(ds);
myDataTable = ds.Tables[0];
if (icn.State == ConnectionState.Open) icn.Close();
if (m_blnlogfile)
{
String StrLog = String.Format("{0}: {1};{2}", "vpos_GetDataTable", SQLiteString, "success");
FileLib.logFile("SQLNormal.log", StrLog);
}
}
catch (Exception ex)
{
if (m_blnlogfile)
{
String StrLog = String.Format("{0}: {1};{2}", "vpos_GetDataTable", SQLiteString, ex.ToString());
FileLib.logFile("SQLError.log", StrLog);
}
}
return myDataTable;
}
public static void DBBackupAndClean()//做一次實體DB檔案備份，並清空所有資料表資料
{
if(SqliteDataAccess.m_terminal_data.Count==0)
{
return;
}
String DBName = "vpos";
String StrSourceFilePath = FileLib.path + DBName + ".db";
String StrDestFilePath = FileLib.path + DBName + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".db";
File.Copy(StrSourceFilePath, StrDestFilePath, true);
String SQL = "SELECT name FROM sqlite_schema WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY 1;";//https://database.guide/2-ways-to-list-tables-in-sqlite-database/
DataTable DataTableBuf = GetDataTable(SQL);
if((DataTableBuf != null)&& (DataTableBuf.Rows.Count > 0))
{
for(int i = 0; i < DataTableBuf.Rows.Count; i++)
{
SQL = String.Format("DELETE FROM {0};", DataTableBuf.Rows[i][0].ToString());
SQLiteInsertUpdateDelete(SQL);
}
}
}
public static void DBColumnsPadding(String StrTable, String StrColumn,String StrType,String StrPreset="null")
{
String SQL = "";
bool blnCheck = false;
SQL = String.Format("SELECT * FROM {1} LIMIT 0,1", StrColumn, StrTable);
DataTable chaekDataTable = GetDataTable(SQL);
if(chaekDataTable != null)
{
foreach (DataColumn column in chaekDataTable.Columns)
{
if(column.ColumnName == StrColumn)
{
blnCheck = true;
break;
}
}
}
if(!blnCheck)
{
//---
//新增對應欄位
//ALTER TABLE MY_TABLE ADD COLUMN MISSING_COLUMN int null
SQL = String.Format("ALTER TABLE {1} ADD COLUMN {0} {2} DEFAULT {3}", StrColumn, StrTable, StrType,StrPreset);
SQLiteInsertUpdateDelete(SQL);
//---新增對應欄位
//---
//新增欄位後，清空該資料表內容，藉此強迫觸發資料全部更新機制
SQL = String.Format("DELETE FROM {0}", StrTable);
SQLiteInsertUpdateDelete(SQL);
//---新增欄位後，清空該資料表內容，藉此強迫觸發資料全部更新機制
}
}
//---C# SQLite通用操作語法 ~ for System.Data.SQLite.Core & Finisar.SQLite 元件
///應用
///產生SQLite的資料庫文件，副檔名為.db
///CreateSQLiteDatabase("data.db");
///建立資料表test
///string createtablestring = "create table test (speed double, dist double);";
///CreateSQLiteTable("data.db", createtablestring);
///插入資料到test表中
///string insertstring = "insert into test (speed,dist) values ('10','100');insert into test (speed,dist) values ('20','200');";
///SQLiteInsertUpdateDelete("data.db", insertstring);
///讀取資料
///DataTable dt = GetDataTable("data.db", "select * from test");
///dataGridView1.DataSource = dt;
//---
//DB2DataTable Function
public static bool user_dataLoad()
{
bool blnResult=false;
m_intLoginSID = -1;
m_user_dataDataTable = GetDataTable("SELECT * FROM user_data");
if( ((m_user_dataDataTable != null) && (m_user_dataDataTable.Rows.Count > 0)) )
{
blnResult = true;
}
return blnResult;
}
public static void productLoad()
{
//m_productDataTable = GetDataTable("select a.sort as product_sort,a.SID as product_sid,a.product_code,a.barcode,a.product_type,a.product_name,a.product_shortname,a.price_mode,a.product_price,a.unit_sid,a.tax_sid,b.category_sid,c.tax_rate,c.tax_type,IFNULL(e.spec_sid,0) as spec_sid,d.spec_name,d.init_product_sid from product_data a JOIN product_category_relation b ON b.product_sid=a.SID LEFT JOIN tax_data c ON c.SID=a.tax_sid LEFT JOIN product_spec_data d ON d.init_product_sid=a.SID AND d.del_flag='N' LEFT JOIN product_spec_relation e ON e.product_sid=a.SID where a.del_flag='N' and a.stop_flag='N' order by a.sort,a.SID ;");
m_productDataTable = GetDataTable("select a.sort as product_sort,a.SID as product_sid,a.product_code,a.barcode,a.product_type,a.product_name,a.product_shortname,a.price_mode,a.product_price,a.unit_sid,a.tax_sid,b.category_sid,c.tax_rate,c.tax_type,IFNULL(e.spec_sid,0) as spec_sid,d.spec_name,d.init_product_sid,e.alias_name from product_data a JOIN product_category_relation b ON b.product_sid=a.SID LEFT JOIN tax_data c ON c.SID=a.tax_sid LEFT JOIN product_spec_data d ON d.init_product_sid=a.SID AND d.del_flag='N' LEFT JOIN product_spec_relation e ON e.product_sid=a.SID where a.del_flag='N' and a.stop_flag='N' order by a.sort,a.SID ;");
}
public static void condimentLoad()
{
m_condimentDataTable = GetDataTable("Select a.*,b.group_name,b.required_flag,b.single_flag,b.newline_flag,b.count_flag,b.min_count,b.max_count,c.product_sid,a.SID as condiment_sid From condiment_data a,condiment_group b,product_condiment_relation c where b.SID=a.group_sid AND a.del_flag='N' AND a.stop_flag='N' AND a.SID=c.condiment_sid Order by b.sort,a.group_sid,a.sort ;");
}
//---DB2DataTable Function
//---
//DB2DataTable Var
public static int m_intLoginSID = -1;
public static DataTable m_productDataTable = new DataTable();
public static DataTable m_user_dataDataTable = new DataTable();
public static List<product_spec_Var> m_product_spec_Var=new List<product_spec_Var>();
public static DataTable m_condimentDataTable = new DataTable();
public static List<condiment_Var> m_condiment_Var = new List<condiment_Var>();
public static DataTable m_product_price_type_relationDataTable = new DataTable();
//---DB2DataTable Var
}//SQLDataTableModel
}
2 thoughts on “C# SQLite 欄位(Column) 存在與否判斷(搜尋) / 新增(補上)”
SQLITE 新增(補上) 欄位
測試過 語法收藏
CREATE TABLE devices (
name TEXT NOT NULL,
model TEXT NOT NULL,
Serial INTEGER NOT NULL UNIQUE
);
INSERT INTO devices (name, model, serial) VALUES(‘HP ZBook 17 G3 Mobile Workstation’,’ZBook’,’SN-2015′);
ALTER TABLE devices ADD COLUMN01 text;
ALTER TABLE devices ADD COLUMN02 INTEGER;
ALTER TABLE devices ADD COLUMN03 INT;
ALTER TABLE devices ADD COLUMN04 CHAR(13);
ALTER TABLE devices ADD COLUMN05 INT DEFAULT 0;
ALTER TABLE devices ADD COLUMN06 CHAR(13) DEFAULT ‘N’;
sqlite 判斷 資料表是否存在 [驗證過]
SELECT name FROM sqlite_master WHERE type='table' AND name='order_data';//資料表名稱order_data
SELECT name FROM sqlite_master WHERE type='table' AND name='modules_data';