Lisp连接SQLite数据库
本帖最后由 cabinsummer 于 2024-5-17 08:58 编辑以前工作中用Access数据库，经常导致某些软件崩溃，自从换了SQLite数据库后，天空都晴朗了。
但是Lisp如何访问SQLite找遍论坛，没有特别好的办法，有些借助第三方的工具直接被杀毒软件杀掉了，只好借助.NET做了一个中间件，用dll的形式加载进来。本来这个该放到.NET版块，但是由于是LISP用，还是分享到LISP为好。上代码
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.Runtime;
using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
namespace SQLite
{
public static class SQLiteLsp
{
public static ResultBuffer DoLite(ResultBuffer rbArgs)
{
ResultBuffer ret = new ResultBuffer();
TypedValue[] tb = rbArgs.AsArray();
if (tb.Length != 1)
{
ret.Add(new TypedValue((int)LispDataType.Nil));
return ret;
}
if (tb.TypeCode != (int)LispDataType.Text)
{
ret.Add(new TypedValue((int)LispDataType.Nil));
return ret;
}
string sql = tb.Value.ToString().ToUpper().Trim();
if (sql.Length < 6)
{
ret.Add(new TypedValue((int)LispDataType.Nil));
return ret;
}
switch (Regex.Match(sql, @"^+\b").Value)
{
case "SELECT":
System.Data.DataTable dt = DAL.ExecuteDataTable(sql);
if (dt == null)
{
ret.Add(new TypedValue((int)LispDataType.Nil));
return ret;
}
ret.Add(new TypedValue((int)LispDataType.ListBegin));
ret.Add(new TypedValue((int)LispDataType.ListBegin));
for (int i = 0; i < dt.Columns.Count; i++)
{
ret.Add(new TypedValue((int)LispDataType.Text, dt.Columns.ColumnName));
}
ret.Add(new TypedValue((int)LispDataType.ListEnd));
foreach (DataRow dr in dt.Rows)
{
ret.Add(new TypedValue((int)LispDataType.ListBegin));
for (int k = 0; k < dt.Columns.Count; k++)
{
switch (dr.GetType().ToString())
{
case ("System.String"):
case ("System.DateTime"):
ret.Add(new TypedValue((int)LispDataType.Text, dr.ToString()));
break;
case ("System.Int32"):
ret.Add(new TypedValue((int)LispDataType.Int32, (int)dr));
break;
case ("System.Int16"):
ret.Add(new TypedValue((int)LispDataType.Int16, (short)dr));
break;
case ("System.Boolean"):
if (Convert.ToBoolean(Convert.ToInt32(dr)))
ret.Add(new TypedValue((int)LispDataType.T_atom));
else
ret.Add(new TypedValue((int)LispDataType.Nil));
break;
default:
ret.Add(new TypedValue((int)LispDataType.Nil));
break;
}
}
ret.Add(new TypedValue((int)LispDataType.ListEnd));
}
ret.Add(new TypedValue((int)LispDataType.ListEnd));
break;
case "CREATE":
case "RENAME":
case "INSERT":
case "DELETE":
case "UPDATE":
case "DROP":
case "ALTER":
if (DAL.ExecuteNonQuery(sql))
ret.Add(new TypedValue((int)LispDataType.T_atom));
else
ret.Add(new TypedValue((int)LispDataType.Nil));
break;
default:
ret.Add(new TypedValue((int)LispDataType.Nil));
break;
}
return ret;
}
}
public static class DAL
{
private static string LiteSetting = "Data Source=C:\MyData.db3"; // 自己的数据库
public static System.Data.DataTable ExecuteDataTable(string sql)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
using (SQLiteConnection cn = new SQLiteConnection(LiteSetting))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, cn))
{
using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter())
{
dataAdapter.SelectCommand = cmd;
dataAdapter.Fill(dt);
}
}
}
}
catch
{
return null;
}
return dt;
}
public static bool ExecuteNonQuery(string sql)
{
bool ret = false;
using (SQLiteConnection cn = new SQLiteConnection(LiteSetting))
{
cn.Open();
using (SQLiteTransaction trans = cn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, cn))
{
try
{
cmd.ExecuteNonQuery();
trans.Commit();
ret = true;
}
catch
{
trans.Rollback();
}
}
}
cn.Close();
}
return ret;
}
}
}
Reference里需添加accoremgd.dll、acdbmgd.dll/System.Data.SQLite，同时SQLite.Interop.dll也要复制到输出文件夹内。
用NETLOAD加载生成的SQLiteLsp.dll，在命令行键入
(DoLite "SELECT * FROM ")
返回结果被我美化了
(("ColorCode" "Red" "Green" "Blue" "Chinese" "English" "IsRal")
("B01" "51" "65" "65" "深灰" "" nil)
("B02" "80" "92" "90" "中灰" "" nil)
("B03" "106" "117" "112" "淡灰" "" nil)
("B04" "122" "135" "125" "银灰" "" nil)
("B05" "152" "167" "160" "海灰" "" nil)
("B06" "194" "234" "225" "淡天蓝(酞)" "" nil)
("B07" "175" "216" "200" "蛋青" "" nil)
("B08" "156" "213" "204" "稚蓝" "" nil)
("B09" "58" "134" "147" "宝石蓝" "" nil)
("B10" "35" "107" "157" "鲜蓝" "" nil)
("B11" "40" "92" "113" "淡海蓝(铁)" "" nil)
("B12" "29" "75" "90" "中海蓝(铁)" "" nil)
("B13" "23" "48" "55" "深海蓝(铁)" "" nil)
("B14" "22" "75" "91" "景蓝" "" nil)
("B15" "31" "99" "102" "艳蓝" "" nil)
("BG01" "113" "130" "114" "中绿灰" "" nil)
("BG02" "128" "178" "140" "湖绿" "" nil)
("BG03" "83" "159" "131" "宝绿" "" nil)
("BG04" "42" "119" "101" "鲜绿" "" nil)
("BG05" "144" "205" "172" "淡湖绿" "" nil)
("G01" "152" "208" "143" "苹果绿" "" nil)
("G02" "22" "99" "37" "淡绿" "" nil)
("G03" "21" "83" "32" "艳绿" "" nil)
("G04" "20" "51" "19" "中绿" "" nil)
("G05" "14" "41" "22" "深绿" "" nil)
("G06" "16" "30" "17" "橄榄绿" "" nil)
("G07" "197" "222" "190" "蛋壳绿" "" nil)
("G08" "156" "207" "150" "淡苹果绿" "" nil)
("G09" "62" "113" "57" "深豆绿" "" nil)
("G10" "141" "158" "143" "飞机灰" "" nil)
("GY01" "157" "210" "122" "豆绿" "" nil)
("GY02" "85" "107" "61" "纺绿" "" nil)
("GY03" "92" "102" "75" "橄榄灰" "" nil)
("GY04" "38" "49" "15" "草绿" "" nil)
("GY05" "42" "47" "24" "褐绿" "" nil)
("GY06" "44" "60" "23" "军车绿" "" nil)
("GY07" "111" "182" "87" "豆蔻绿" "" nil)
("GY08" "177" "224" "146" "果绿(酞)" "" nil)
("GY09" "199" "205" "179" "冰灰" "" nil)
("GY10" "82" "94" "79" "机床灰" "" nil)
("GY11" "148" "164" "127" "玉灰" "" nil)
("P01" "208" "197" "211" "淡紫" "" nil)
("P02" "140" "129" "185" "紫色" "" nil)
("PB01" "7" "13" "29" "深蓝(铁)" "" nil)
("PB02" "11" "21" "57" "深蓝(酞)" "" nil)
("PB03" "11" "25" "55" "中蓝(铁)" "" nil)
("PB04" "10" "28" "83" "中蓝(酞)" "" nil)
("PB05" "26" "71" "136" "海蓝" "" nil)
("PB06" "33" "95" "170" "淡蓝(酞)" "" nil)
("PB07" "56" "103" "156" "淡蓝(铁)" "" nil)
("PB08" "38" "74" "98" "蓝灰" "" nil)
("PB09" "88" "162" "208" "天蓝(酞)" "" nil)
("PB10" "119" "183" "201" "天蓝(铁)" "" nil)
("PB11" "15" "57" "136" "孔雀蓝" "" nil)
("R01" "86" "20" "13" "铁红" "" nil)
("R02" "195" "23" "19" "朱红" "" nil)
("R03" "199" "13" "20" "大红" "" nil)
("R04" "102" "11" "16" "紫红" "" nil)
("R05" "223" "44" "18" "桔红" "" nil)
("RAL 1000" "190" "189" "127" "米绿" "Green beige" T)
("RAL 1001" "194" "176" "120" "米色" "Beige" T)
("RAL 1002" "198" "166" "100" "沙黄" "Sand yellow" T)
("RAL 1003" "229" "190" "1" "信号黄" "Signal yellow" T)
("RAL 1004" "205" "164" "52" "金黄" "Golden yellow" T)
("RAL 1005" "169" "131" "7" "蜜黄" "Honey yellow" T)
("RAL 1006" "228" "160" "16" "玉米黄" "Maize yellow" T)
("RAL 1007" "220" "156" "0" "灰黄" "Daffodil yellow" T)
("RAL 1011" "138" "102" "66" "米褐" "Brown beige" T)
("RAL 1012" "199" "180" "70" "柠檬黄" "Lemon yellow" T)
("RAL 1013" "234" "230" "202" "牡蛎白" "Oyster white" T)
("RAL 1014" "225" "204" "79" "象牙" "Ivory" T)
("RAL 1015" "230" "214" "144" "亮象牙" "Light ivory" T)
("RAL 1016" "237" "255" "33" "硫磺" "Sulfur yellow" T)
("RAL 1017" "245" "208" "51" "深黄" "Saffron yellow" T)
("RAL 1018" "248" "243" "53" "绿黄" "Zinc yellow" T)
("RAL 1019" "158" "151" "100" "米灰" "Grey beige" T)
("RAL 1020" "153" "153" "80" "橄榄黄" "Olive yellow" T)
("RAL 1021" "243" "218" "11" "油菜黄" "Rape yellow" T)
("RAL 1023" "250" "210" "1" "交通黄" "Traffic yellow" T)
("RAL 1024" "174" "160" "75" "赭黄" "Ochre yellow" T)
("RAL 1026" "255" "255" "0" "亮黄" "Luminous yellow" T)
("RAL 1027" "157" "145" "1" "咖喱" "Curry" T)
("RAL 1028" "244" "169" "0" "浅橙黄" "Melon yellow" T)
("RAL 1032" "214" "174" "1" "金雀花黄" "Broom yellow" T)
("RAL 1033" "243" "165" "5" "大丽花黄" "Dahlia yellow" T)
("RAL 1034" "239" "169" "74" "粉黄" "Pastel yellow" T)
("RAL 1035" "106" "93" "77" "米珍珠" "Pearl beige" T)
("RAL 1036" "112" "83" "53" "金黄珍珠" "Pearl gold" T)
("RAL 1037" "243" "159" "24" "日光黄" "Sun yellow " T)
("RAL 2000" "237" "118" "14" "黄橙" "Yellow orange" T)
("RAL 2001" "201" "60" "32" "橘红" "Red orange" T)
("RAL 2002" "203" "40" "33" "朱红" "Vermilion" T)
("RAL 2003" "255" "117" "20" "淡橙" "Paster orange" T)
("RAL 2004" "244" "70" "17" "纯橙" "Pure orange" T)
("RAL 2005" "255" "35" "0" "亮橙" "Luminous orange" T)
("RAL 2007" "255" "164" "32" "亮浅橙" "Luminous bright orange" T)
("RAL 2008" "247" "94" "37" "浅红橙" "Bright red orange" T)
("RAL 2009" "245" "64" "33" "交通橙" "Traffic orange" T)
("RAL 2010" "216" "75" "32" "信号橙" "Signal orange" T)
("RAL 2011" "236" "124" "38" "深橙" "Deep orange" T)
("RAL 2012" "235" "106" "14" "鲑鱼橙" "Salmon orange" T)
("RAL 2013" "195" "88" "49" "珍珠橙" "Pearl orange" T)
("RAL 3000" "175" "43" "30" "火焰红" "Flame red" T)
("RAL 3001" "165" "32" "25" "信号红" "Signal red" T)
("RAL 3002" "162" "35" "29" "胭脂红" "Carmine red" T)
("RAL 3003" "155" "17" "30" "宝石红" "Ruby red" T)
("RAL 3004" "117" "21" "30" "紫红" "Purple red" T)
("RAL 3005" "94" "33" "41" "酒红" "Wine red" T)
("RAL 3007" "65" "34" "39" "黑红" "Black red" T)
("RAL 3009" "100" "36" "36" "氧化红" "Oxide red" T)
("RAL 3011" "120" "31" "25" "棕红" "Brown red" T)
("RAL 3012" "193" "135" "107" "米红" "Beige red" T)
("RAL 3013" "161" "35" "18" "番茄红" "Tomato red" T)
("RAL 3014" "211" "110" "112" "古粉红" "Antique pink" T)
("RAL 3015" "234" "137" "154" "淡粉红" "Light pink" T)
("RAL 3016" "179" "40" "33" "珊瑚红" "Coral red" T)
("RAL 3017" "230" "50" "68" "玫瑰" "Rose" T)
("RAL 3018" "213" "34" "50" "草莓红" "Strawberry red" T)
("RAL 3020" "204" "6" "5" "交通红" "Traffic red" T)
("RAL 3022" "217" "80" "48" "鲑鱼粉红" "Salmon pink" T)
("RAL 3024" "248" "0" "0" "亮红" "Luminous red" T)
("RAL 3026" "254" "0" "0" "淡亮红" "Luminous bright red" T)
("RAL 3027" "197" "29" "52" "悬钩子红" "Raspbery red" T)
("RAL 3031" "203" "50" "52" "戈亚红" "Orient red" T)
("RAL 3032" "114" "20" "34" "红宝石珍珠红" "Pearl ruby red" T)
("RAL 3033" "180" "76" "67" "珍珠红" "Pearl pink" T)
("RAL 4001" "222" "76" "138" "丁香红" "Red lilac" T)
("RAL 4002" "146" "43" "62" "紫红" "Red violet" T)
("RAL 4003" "222" "76" "138" "石南紫" "Heather violet" T)
("RAL 4004" "110" "28" "52" "酒红紫" "Claret violet" T)
("RAL 4005" "108" "70" "117" "丁香蓝" "Blue lilac" T)
("RAL 4006" "160" "52" "114" "交通紫" "Traffic purple" T)
("RAL 4007" "74" "25" "44" "紫红蓝" "Purple violet" T)
("RAL 4008" "146" "78" "125" "信号紫罗兰" "Signal violet" T)
("RAL 4009" "164" "125" "144" "崧蓝紫" "Pastel violet" T)
("RAL 4010" "215" "45" "109" "电视品红" "Telemagenta" T)
("RAL 4011" "134" "115" "161" "珍珠紫" "Pearl violet" T)
("RAL 4012" "108" "104" "129" "珍珠黑" "Peal blackberry " T)
("RAL 5000" "42" "46" "75" "紫蓝" "Violet blue" T)
("RAL 5001" "31" "52" "56" "蓝绿" "Green blue" T)
("RAL 5002" "32" "33" "79" "群青蓝" "Ultramarine blue" T)
("RAL 5003" "29" "30" "51" "蓝宝石蓝" "Sapphire blue" T)
("RAL 5004" "32" "33" "79" "蓝黑色" "Black blue" T)
("RAL 5005" "30" "45" "110" "信号蓝" "Signal blue" T)
("RAL 5007" "62" "95" "138" "亮蓝" "Brillant blue" T)
("RAL 5008" "38" "37" "45" "灰蓝" "Grey blue" T)
("RAL 5009" "2" "86" "105" "天青蓝" "Azure blue" T)
("RAL 5010" "14" "41" "75" "龙胆蓝" "Gentian blue" T)
("RAL 5011" "35" "26" "36" "钢蓝" "Steel blue" T)
("RAL 5012" "59" "131" "189" "淡蓝" "Light blue" T)
("RAL 5013" "37" "41" "74" "钴蓝" "Cobalt blue" T)
("RAL 5014" "96" "111" "140" "鸽蓝" "Pigeon blue" T)
("RAL 5015" "34" "113" "179" "天蓝" "Sky blue" T)
("RAL 5017" "6" "57" "113" "交通蓝" "Traffic blue" T)
("RAL 5018" "63" "136" "143" "绿松石蓝" "Turquoise blue" T)
("RAL 5019" "27" "85" "131" "卡布里蓝" "Capri blue" T)
("RAL 5020" "29" "51" "74" "海蓝" "Ocean blue" T)
("RAL 5021" "37" "109" "123" "不来梅蓝" "Water blue" T)
("RAL 5022" "37" "40" "80" "夜蓝" "Night blue" T)
("RAL 5023" "73" "103" "141" "冷蓝" "Distant blue" T)
("RAL 5024" "93" "155" "155" "崧蓝蓝" "Pastel blue" T)
("RAL 5025" "42" "100" "120" "珍珠龙胆蓝" "Pearl gentian blue" T)
("RAL 5026" "16" "44" "84" "珍珠夜蓝" "Pearl night blue" T)
("RAL 6000" "49" "102" "80" "铜锈绿" "Patina green" T)
("RAL 6001" "40" "114" "51" "翡翠绿" "Emerald greet" T)
("RAL 6002" "45" "87" "44" "叶绿" "Leaf green" T)
("RAL 6003" "66" "70" "50" "橄榄绿" "Olive green" T)
("RAL 6004" "31" "58" "61" "蓝绿" "Blue green" T)
("RAL 6005" "47" "69" "56" "苔藓绿" "Moss green" T)
("RAL 6006" "62" "59" "50" "橄榄灰绿" "Grey olrve" T)
("RAL 6007" "52" "59" "41" "瓶绿" "Bottle green" T)
("RAL 6008" "57" "53" "42" "褐绿" "Brown green" T)
("RAL 6009" "49" "55" "43" "冷杉绿" "Fir greet" T)
("RAL 6010" "53" "104" "45" "草绿" "Grass greet" T)
("RAL 6011" "88" "114" "70" "淡橄榄绿" "Reseda green" T)
("RAL 6012" "52" "62" "64" "墨绿" "Black green" T)
("RAL 6013" "108" "113" "86" "芦苇绿" "Reed green" T)
("RAL 6014" "71" "64" "46" "橄榄黄" "Yellow olive" T)
("RAL 6015" "59" "60" "54" "黑齐墩果" "Black olive" T)
("RAL 6016" "30" "89" "69" "绿松石绿" "Turquoise green" T)
("RAL 6017" "76" "145" "65" "五月红" "May green" T)
("RAL 6018" "87" "166" "57" "黄绿" "Yellow green" T)
("RAL 6019" "189" "236" "182" "崧蓝绿" "Pastel green" T)
("RAL 6020" "46" "58" "35" "铭绿" "Chrome green" T)
("RAL 6021" "137" "172" "118" "浅绿" "Pale green" T)
("RAL 6022" "37" "34" "27" "橄榄土褐" "Olive drab" T)
("RAL 6024" "48" "132" "70" "交通绿" "Traffic green" T)
("RAL 6025" "61" "100" "45" "蕨绿" "Fern green" T)
("RAL 6026" "1" "93" "82" "蛋白石绿" "Opal green" T)
("RAL 6027" "132" "195" "190" "浅绿" "Light green" T)
("RAL 6028" "44" "85" "69" "松绿" "Pine green" T)
("RAL 6029" "32" "96" "61" "薄荷绿" "Mint green" T)
("RAL 6032" "49" "127" "67" "信号绿" "Signal green" T)
("RAL 6033" "73" "126" "118" "薄荷绿蓝" "Mint turquoise" T)
("RAL 6034" "127" "181" "181" "崧蓝绿松石" "Pastel turquoies" T)
("RAL 6035" "28" "84" "45" "珍珠绿" "Pearl green" T)
("RAL 6036" "22" "53" "55" "不透明蓝白绿" "Pearl opal green" T)
("RAL 6037" "0" "143" "57" "纯绿" "Pure green" T)
("RAL 6038" "0" "187" "45" "亮绿" "Luminous green" T)
("RAL 7000" "120" "133" "139" "松鼠灰" "Squirrel grey" T)
("RAL 7001" "138" "149" "151" "银灰" "Silver grey" T)
("RAL 7002" "126" "123" "82" "橄榄灰绿" "Olive grey" T)
("RAL 7003" "108" "112" "89" "苔藓绿" "Moss grey" T)
("RAL 7004" "150" "153" "146" "信号灰" "Signal grey" T)
("RAL 7005" "100" "107" "99" "鼠灰" "Mouse grey" T)
("RAL 7006" "109" "101" "82" "米灰" "Beige grey" T)
("RAL 7008" "106" "95" "49" "土黄灰" "Khaki grey" T)
("RAL 7009" "77" "86" "69" "绿灰" "Green grey" T)
("RAL 7010" "76" "81" "74" "油布灰" "Tarpaulin grey" T)
("RAL 7011" "67" "75" "77" "铁灰" "Iron grey" T)
("RAL 7012" "78" "87" "84" "玄武石灰" "Basalt grey" T)
("RAL 7013" "70" "69" "49" "褐灰" "Brown grey" T)
("RAL 7015" "67" "71" "80" "浅橄榄灰" "Slate grey" T)
("RAL 7016" "41" "49" "51" "煤灰" "Anthracite grey" T)
("RAL 7021" "35" "40" "43" "黑灰" "Black grey" T)
("RAL 7022" "51" "47" "44" "暗灰" "Umbra grey" T)
("RAL 7023" "104" "108" "94" "混凝土灰" "Concrete grey" T)
("RAL 7024" "71" "74" "81" "石墨灰" "Graphite grey" T)
("RAL 7026" "47" "53" "59" "花岗灰" "Granite grey" T)
("RAL 7030" "139" "140" "122" "石灰" "Stone grey" T)
("RAL 7031" "71" "75" "78" "蓝灰" "Blue grey" T)
("RAL 7032" "184" "183" "153" "卵石灰" "Pebble grey" T)
("RAL 7033" "125" "132" "113" "水泥灰" "Cement grey" T)
("RAL 7034" "143" "139" "102" "黄灰" "Yellow grey" T)
("RAL 7035" "215" "215" "215" "浅灰" "Light grey" T)
("RAL 7036" "127" "118" "121" "铂灰" "Platinum grey" T)
("RAL 7037" "125" "127" "120" "土灰" "Dusty grey" T)
("RAL 7038" "195" "195" "195" "玛瑙灰" "Agate grey" T)
("RAL 7039" "108" "105" "96" "石英灰" "Quartz grey" T)
("RAL 7040" "157" "161" "170" "窗灰" "Window grey" T)
("RAL 7042" "141" "148" "141" "交通灰A" "Traffic grey A" T)
("RAL 7043" "78" "84" "82" "交通灰B" "Traffic grey B" T)
("RAL 7044" "202" "196" "176" "深铭灰" "Silk grey" T)
("RAL 7045" "144" "144" "144" "电视灰1" "Telegrey 1" T)
("RAL 7046" "130" "137" "143" "电视灰2" "Telegrey 2" T)
("RAL 7047" "208" "208" "208" "电视灰4" "Telegrey 4" T)
("RAL 7048" "137" "129" "118" "珍珠鼠灰" "Pearl mouse grey" T)
("RAL 8000" "130" "108" "52" "绿褐" "Green brown" T)
("RAL 8001" "149" "95" "32" "赭石棕" "Ochre brown" T)
("RAL 8002" "108" "59" "42" "信号褐" "Signal brown" T)
("RAL 8003" "115" "66" "34" "土棕褐" "Clay brown" T)
("RAL 8004" "143" "64" "42" "铜棕" "Coper brown" T)
("RAL 8007" "89" "53" "31" "鹿褐" "Fawn brown" T)
("RAL 8008" "111" "79" "40" "橄榄棕" "Olive brown" T)
("RAL 8011" "91" "58" "41" "深棕" "Nut brown" T)
("RAL 8012" "89" "35" "33" "红褐" "Red brown" T)
("RAL 8014" "56" "44" "30" "乌贼棕" "Sepia brown" T)
("RAL 8015" "99" "58" "52" "粟棕" "Chestnut brown" T)
("RAL 8016" "76" "47" "39" "桃花心木褐" "Mahogany brown" T)
("RAL 8017" "69" "50" "46" "巧克力棕" "Chocolate brown" T)
("RAL 8019" "64" "58" "58" "灰褐" "Grey brown" T)
("RAL 8022" "33" "33" "33" "黑褐" "Black brown" T)
("RAL 8023" "166" "94" "46" "桔黄褐" "Orange brown" T)
("RAL 8024""121" "85" "61" "哔叽棕" "Beige brown" T)
("RAL 8025" "117" "92" "73" "浅褐" "Pale brown" T)
("RAL 8028" "78" "59" "49" "浅灰褐" "Terra brown" T)
("RAL 8029" "118" "60" "40" "珍珠铜棕" "Pearl copper" T)
("RAL 9001" "250" "244" "227" "彩黄" "Cream" T)
("RAL 9002" "231" "235" "218" "灰白" "Grey white" T)
("RAL 9003" "244" "244" "244" "信号白" "Signal white" T)
("RAL 9004" "40" "40" "40" "信号黑" "Signal black" T)
("RAL 9005" "10" "10" "10" "墨黑" "Jet black" T)
("RAL 9006" "165" "165" "165" "白铝灰" "White Aluminium" T)
("RAL 9007" "143" "143" "143" "灰铝" "Grey Aluminium" T)
("RAL 9010" "255" "255" "255" "纯白" "Pure white" T)
("RAL 9011" "28" "28" "28" "石墨黑" "Graphiack" T)
("RAL 9016" "246" "246" "246" "交通白" "Traffic white" T)
("RAL 9017" "30" "30" "30" "交通黑" "Traffic black" T)
("RAL 9018" "215" "215" "215" "草纸白" "Papyrus white" T)
("RAL 9022" "156" "156" "156" "珍珠浅灰" "Pearl light grey" T)
("RAL 9023" "130" "130" "130" "珍珠深灰" "Pearl dark grey" T)
("RP01" "242" "164" "186" "粉红" "" nil)
("RP02" "251" "194" "211" "淡粉红" "" nil)
("RP03" "150" "29" "74" "玫瑰红" "" nil)
("RP04" "192" "74" "124" "淡玫瑰" "" nil)
("Y01" "126" "121" "83" "驼灰" "" nil)
("Y02" "241" "228" "158" "珍珠" "" nil)
("Y03" "241" "223" "149" "奶油" "" nil)
("Y04" "243" "220" "116" "象牙" "" nil)
("Y05" "241" "220" "21" "柠黄" "" nil)
("Y06" "240" "212" "15" "淡黄" "" nil)
("Y07" "238" "165" "1" "中黄" "" nil)
("Y08" "250" "152" "1" "深黄" "" nil)
("Y09" "163" "102" "20" "铁黄" "" nil)
("Y10" "61" "57" "19" "军黄" "" nil)
("Y11" "248" "234" "195" "乳白" "" nil)
("Y12" "234" "216" "154" "米黄" "" nil)
("Y13" "142" "148" "120" "淡黄灰" "" nil)
("YR01" "98" "47" "16" "淡棕" "" nil)
("YR02" "54" "39" "19" "赭黄" "" nil)
("YR03" "30" "16" "13" "紫棕" "" nil)
("YR04" "239" "60" "13" "桔黄" "" nil)
("YR05" "46" "21" "15" "棕色" "" nil)
("YR06" "147" "71" "11" "棕黄" "" nil)
("YR07" "71" "41" "18" "深棕黄" "" nil))
完美解决！
本帖最后由 ggosgg 于 2024-8-31 21:31 编辑
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.Runtime;
using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
namespace SQLite
{
public static class SQLiteLsp
{
public static ResultBuffer DoLite(ResultBuffer rbArgs)
{
ResultBuffer ret = new ResultBuffer();
TypedValue[] tb = rbArgs.AsArray();
if (tb.Length != 1)
{
ret.Add(new TypedValue((int)LispDataType.Nil));
return ret;
}
if (tb.TypeCode != (int)LispDataType.Text)
{
ret.Add(new TypedValue((int)LispDataType.Nil));
return ret;
}
string sql = tb.Value.ToString().ToUpper().Trim();
if (sql.Length < 6)
{
ret.Add(new TypedValue((int)LispDataType.Nil));
return ret;
}
switch (Regex.Match(sql, @"^+\b").Value)
{
case "SELECT":
System.Data.DataTable dt = DAL.ExecuteDataTable(sql);
if (dt == null)
{
ret.Add(new TypedValue((int)LispDataType.Nil));
return ret;
}
ret.Add(new TypedValue((int)LispDataType.ListBegin));
ret.Add(new TypedValue((int)LispDataType.ListBegin));
for (int i = 0; i < dt.Columns.Count; i++)
{
ret.Add(new TypedValue((int)LispDataType.Text, dt.Columns[有个i].ColumnName));
}
ret.Add(new TypedValue((int)LispDataType.ListEnd));
foreach (DataRow dr in dt.Rows)
{
ret.Add(new TypedValue((int)LispDataType.ListBegin));
for (int k = 0; k < dt.Columns.Count; k++)
{
switch (dr.GetType().ToString())
{
case ("System.String"):
case ("System.DateTime"):
ret.Add(new TypedValue((int)LispDataType.Text, dr.ToString()));
break;
case ("System.Int32"):
ret.Add(new TypedValue((int)LispDataType.Int32, (int)dr));
break;
case ("System.Int16"):
ret.Add(new TypedValue((int)LispDataType.Int16, (short)dr));
break;
case ("System.Boolean"):
if (Convert.ToBoolean(Convert.ToInt32(dr)))
ret.Add(new TypedValue((int)LispDataType.T_atom));
else
ret.Add(new TypedValue((int)LispDataType.Nil));
break;
default:
ret.Add(new TypedValue((int)LispDataType.Nil));
break;
}
}
ret.Add(new TypedValue((int)LispDataType.ListEnd));
}
ret.Add(new TypedValue((int)LispDataType.ListEnd));
break;
case "CREATE":
case "RENAME":
case "INSERT":
case "DELETE":
case "UPDATE":
case "DROP":
case "ALTER":
if (DAL.ExecuteNonQuery(sql))
ret.Add(new TypedValue((int)LispDataType.T_atom));
else
ret.Add(new TypedValue((int)LispDataType.Nil));
break;
default:
ret.Add(new TypedValue((int)LispDataType.Nil));
break;
}
return ret;
}
}
public static class DAL
{
private static string LiteSetting = "Data Source=D:\\SqltiteData.db"; // 自己的数据库
public static System.Data.DataTable ExecuteDataTable(string sql)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
using (SQLiteConnection cn = new SQLiteConnection(LiteSetting))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, cn))
{
using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter())
{
dataAdapter.SelectCommand = cmd;
dataAdapter.Fill(dt);
}
}
}
}
catch
{
return null;
}
return dt;
}
public static bool ExecuteNonQuery(string sql)
{
bool ret = false;
using (SQLiteConnection cn = new SQLiteConnection(LiteSetting))
{
cn.Open();
using (SQLiteTransaction trans = cn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, cn))
{
try
{
cmd.ExecuteNonQuery();
trans.Commit();
ret = true;
}
catch
{
trans.Rollback();
}
}
}
cn.Close();
}
return ret;
}
}
} 夏生生 发表于 2024-5-17 10:35
先生大才，请问只能执行select语句吗？create table或insert这些是否也能执行，想加个参数，如(DoLite file ...
可以执行INSERT等语句。
如果你想对不同数据库操作，建议将这个程序改一下，
if (tb.Length != 1)
{
ret.Add(new TypedValue((int)LispDataType.Nil));
return ret;
}
这一段改成
if (tb.Length > 0)
{
string database = tb.Value.ToString();
// 其它代码
}
后面的ExecuteDataTable，ExecuteNonQuery方法里的连接字符串根据这个database来实现 烟盒迷唇 发表于 2024-5-17 06:39
这个DLL有版本限制嘛
附件里的DLL是引用的，64位，程序编译后的DLL最好也是x64，这样保证在64位环境下使用。至于32位的，要重新下载附件里同名32位DLL 本帖最后由 自贡黄明儒 于 2024-5-16 10:37 编辑
大师又归来了，改语言了呀？数据库查询语言最伤人脑了。。。 sql没有ORM真的蛮麻烦的... 谢谢分享{:1_1:} 杜老板分享的exe，用go语言写的，很好用 大师又归来了 幸事儿！ 希望多分享点独门绝技:lol cad里面有Connectivity Automation Objects (ActiveX/CAO)可以连接数据库。不过很少有人用，除了CAD帮助，参考资料和案例不多。
https://help.autodesk.com/view/OARX/2021/ENU/?guid=GUID-86E44F95-2372-461D-862C-426038A6A24F 这个DLL有版本限制嘛 膜拜中~~~~
页:
[1]
2