public static List <SqlScript> InsertSelectScript(this iTable obj, string paramchar, string randomidcharacter)
{
Type type = obj.GetType();
SqlScript sqlscript = new SqlScript();
string into = "";
string value = "";
string set = "";
string where = "";
updatesql(type, paramchar, obj, ref set, ref where, ref sqlscript.dbparameters);
insertsql(type, paramchar, randomidcharacter, obj, ref into, ref value, ref sqlscript.dbparameters);
if (type.Name.IndexOf('`') > 0)
{
sqlscript.sql = $@"
begin tran
UPDATE {type.Name.Remove(type.Name.IndexOf('`'))} SET {set} WHERE {where}
if @@rowcount = 0
begin
INSERT INTO {type.Name.Remove(type.Name.IndexOf('`'))} ({into}) VALUES ({value})
end
commit tran";
}
else
{
sqlscript.sql = $@"
begin tran
UPDATE {type.Name.Replace("`1", "")} SET {set} WHERE {where}
if @@rowcount = 0
begin
INSERT INTO {type.Name.Replace("`1", "")} ({into}) VALUES ({value})
end
commit tran";
}
return(new List <SqlScript>()
{
sqlscript
});
}
public static List <SqlScript> DeleteByPKScript(this iPrimaryKey obj, string paramchar)
{
Type type = obj.GetType();
SqlScript sqlscript = new SqlScript();
string where = "";
deletesql(type, paramchar, obj, ref where, ref sqlscript.dbparameters);
if (type.Name.IndexOf('`') > 0)
{
sqlscript.sql = $"DELETE FROM {type.Name.Remove(type.Name.IndexOf('`')).Replace("PK_","")} WHERE {where}";
}
else
{
sqlscript.sql = $"DELETE FROM {type.Name.Replace("`1", "").Replace("PK_", "")} WHERE {where}";
}
return(new List <SqlScript>()
{
sqlscript
});
}
/// <summary>
/// Insert Data
/// </summary>
/// <param name="obj"></param>
/// <param name="paramchar"></param>
/// <param name="randomidcharacter"></param>
/// <returns></returns>
public static List <SqlScript> InsertScript(this iTable obj, string paramchar, string randomidcharacter, eInsertUpdateType inserttype = eInsertUpdateType.Normal)
{
Type type = obj.GetType();
SqlScript sqlscript = new SqlScript();
string into = "";
string value = "";
insertsql(type, paramchar, randomidcharacter, obj, ref into, ref value, ref sqlscript.dbparameters);
string insert = "INSERT";
switch (inserttype)
{
case eInsertUpdateType.Normal:
break;
case eInsertUpdateType.Replace:
insert += " OR REPLACE ";
break;
case eInsertUpdateType.Ignore:
insert += " OR IGNORE ";
break;
default:
break;
}
if (type.Name.IndexOf('`') > 0)
{
sqlscript.sql = $"{insert} INTO { type.Name.Remove(type.Name.IndexOf('`'))} ({into}) VALUES ({value})";
}
else
{
sqlscript.sql = $"{insert} INTO {type.Name.Replace("`1", "")} ({into}) VALUES ({value})";
}
// sqlscript.sql = $"INSERT INTO {type.Name.Replace("`1", "")} ({into}) VALUES ({value})";
// sqlscript.sql = $"INSERT INTO { type.Name.Remove(type.Name.IndexOf('`'))} ({into}) VALUES ({value})";
return(new List <SqlScript>()
{
sqlscript
});
}
/// <summary>
/// Update Data By PK
/// </summary>
/// <param name="obj"></param>
/// <param name="paramchar"></param>
/// <returns></returns>
public static List <SqlScript> UpdateByPKScript(this iTable obj, string paramchar)
{
Type type = obj.GetType();
SqlScript sqlscript = new SqlScript();
string set = "";
string where = "";
updatesql(type, paramchar, obj, ref set, ref where, ref sqlscript.dbparameters);
if (type.Name.IndexOf('`') > 0)
{
sqlscript.sql = $"UPDATE {type.Name.Remove(type.Name.IndexOf('`'))} SET {set} WHERE {where}";
}
else
{
sqlscript.sql = $"UPDATE {type.Name.Replace("`1", "")} SET {set} WHERE {where}";
}
return(new List <SqlScript>()
{
sqlscript
});
}
/// <summary>
/// Table 物件 --> SQL Script for select all
/// </summary>
/// <param name="obj"></param>
/// <param name="paramchar"></param>
/// <returns></returns>
public static SqlScript SelectAllScript(this iTable obj, string paramchar)
{
SqlScript dbsqlscript = new SqlScript();
string wheresql = "";
foreach (var item in typeof(object).GetProperties())
{
object provalue = item.GetValue(obj);
if (provalue == null)
{
continue;
}
wheresql = $"{item.Name}={paramchar}{item.Name}";
dbsqlscript.dbparameters.Add(new SqlParam(item.Name, provalue));
}
dbsqlscript.sql = $@"
SELECT *
FROM {obj.TableName()}
{wheresql}";
return(dbsqlscript);
}
/// <summary>
/// Table 物件 --> SQL Script for select by PK
/// </summary>
/// <param name="obj"></param>
/// <param name="paramchar"></param>
/// <returns></returns>
public static SqlScript SelectAllWhereByPKScript(this iTable obj, string paramchar)
{
SqlScript dbsqlscript = new SqlScript();
string PKWhere = "";
foreach (var item in obj.GetType().GetPrimaryKeys())
{
if (string.IsNullOrEmpty(PKWhere))
{
PKWhere = $"{item.Name}={paramchar}{item.Name}";
}
else
{
PKWhere = $"{PKWhere} AND {item.Name}={paramchar}{item.Name}";
}
dbsqlscript.dbparameters.Add(new SqlParam(name: item.Name, value: item.GetValue(obj)));
}
dbsqlscript.sql = $@"
SELECT *
FROM {obj.TableName()}
WHERE {PKWhere}";
return(dbsqlscript);
}
public DataTable ReadDataTableBySql(SqlScript dbsqlscript)
{
System.Data.DataTable dt = null;
SQLiteConnection scnn = new SQLiteConnection(ConnString);
try
{
dt = new System.Data.DataTable();
scnn.Open();
SQLiteCommand command = new SQLiteCommand(dbsqlscript.sql, scnn);
if (dbsqlscript.dbparameters != null)
{
foreach (var item in dbsqlscript.dbparameters)
{
if (item.value is Enum)
{
command.Parameters.Add(new SQLiteParameter(item.name, item.value.ToString()));
}
else
{
command.Parameters.Add(new SQLiteParameter(item.name, item.value));
}
}
}
new SQLiteDataAdapter(command).Fill(dt);
}
finally
{
if (scnn != null)
{
scnn.Close();
scnn.Dispose();
}
}
return(dt);
}
public static List <SqlScript> InsertOrReplace <T, TSYSID, TBASEDATATYPE>(this T obj, string modelnamevalue, bool replace = false) where T : SysData <TSYSID>
{
List <SqlScript> sqls = new List <SqlScript>();
foreach (var item in typeof(T).GetProperties().Where(x => x.GetCustomAttribute <ConfigAttribute>() != null &&
x.GetCustomAttribute <ConfigAttribute>().IsSettingColumn))
{
SqlScript sql = new SqlScript();
string value = obj.prefix + nameof(iSysDataTable <TSYSID, TBASEDATATYPE> .DATAVALUE);
string ssid = nameof(iSysDataTable <TSYSID, TBASEDATATYPE> .SYSID);
string modelname = nameof(iSysDataTable <TSYSID, TBASEDATATYPE> .MODELNAME);
string datatype = obj.prefix + nameof(iSysDataTable <TSYSID, TBASEDATATYPE> .DATATYPE);
string dataid = obj.prefix + nameof(iSysDataTable <TSYSID, TBASEDATATYPE> .DATAID);
string replacestr = "";
if (replace)
{
replacestr = "OR REPLACE";
}
sql.sql = $@"INSERT {replacestr} INTO {obj.tablename} ({ssid},{modelname},{datatype},{dataid},{value}) VALUES(:{ssid},:{modelname},:{datatype},:{dataid},:{value})";
if (item.PropertyType.IsArray &&
item.PropertyType.Name.Contains(",") == false)
{
sql.dbparameters.Add(new SqlParam(value, item.GetValue(obj).ToArrayString()));
}
else if (item.PropertyType.IsEquivalentTo(typeof(double[, ])))
{
double[,] ary = item.GetValue(obj) as double[, ];
if (ary == null)
{
continue;
}
string val = "";
for (int i = 0; i < ary.GetLength(0); i++)
{
val = val + ";";
for (int j = 0; j < ary.GetLength(1); j++)
{
if (j < ary.GetLength(1) - 1)
{
val = val + ary[i, j].ToString() + ",";
}
else
{
val = val + ary[i, j].ToString();
}
}
}
val = val + ";";
val = val.Remove(0, 1);
sql.dbparameters.Add(new SqlParam(value, val.ToString()));
}
else
{
sql.dbparameters.Add(new SqlParam(value, item.GetValue(obj).ToString()));
}
sql.dbparameters.Add(new SqlParam(ssid, obj.sysid));
sql.dbparameters.Add(new SqlParam(modelname, modelnamevalue));
sql.dbparameters.Add(new SqlParam(datatype, typeof(T).Name));
sql.dbparameters.Add(new SqlParam(dataid, item.Name));
sqls.Add(sql);
}
return(sqls);
}
public static List <SqlScript> Update <T, TSYSID, TBASEDATATYPE>(this T obj) where T : SysData <TSYSID>
{
List <SqlScript> sqls = new List <SqlScript>();
foreach (var item in typeof(T).GetProperties().Where(x => x.GetCustomAttribute <ConfigAttribute>() != null &&
x.GetCustomAttribute <ConfigAttribute>().IsSettingColumn))
{
SqlScript sql = new SqlScript();
string value = nameof(iSysDataTable <TSYSID, TBASEDATATYPE> .DATAVALUE);
string ssid = nameof(iSysDataTable <TSYSID, TBASEDATATYPE> .SYSID);
string model = nameof(iSysDataTable <TSYSID, TBASEDATATYPE> .MODELNAME);
string datatype = nameof(iSysDataTable <TSYSID, TBASEDATATYPE> .DATATYPE);
string dataid = nameof(iSysDataTable <TSYSID, TBASEDATATYPE> .DATAID);
sql.sql = $@"UPDATE {obj.tablename} SET {obj.prefix}{value}=@{value}
WHERE {ssid}=@{ssid}
AND {model}='*'
AND {obj.prefix}{datatype}=@{datatype}
AND {obj.prefix}{dataid}=@{dataid}";
if (item.PropertyType.IsArray &&
item.PropertyType.Name.Contains(",") == false)
{
sql.dbparameters.Add(new SqlParam(value, item.GetValue(obj).ToArrayString()));
}
else if (item.PropertyType.IsEquivalentTo(typeof(double[, ])))
{
double[,] ary = item.GetValue(obj) as double[, ];
if (ary == null)
{
continue;
}
string val = "";
for (int i = 0; i < ary.GetLength(0); i++)
{
val = val + ";";
for (int j = 0; j < ary.GetLength(1); j++)
{
if (j < ary.GetLength(1) - 1)
{
val = val + ary[i, j].ToString() + ",";
}
else
{
val = val + ary[i, j].ToString();
}
}
}
val = val + ";";
val = val.Remove(0, 1);
sql.dbparameters.Add(new SqlParam(value, val.ToString()));
}
else
{
sql.dbparameters.Add(new SqlParam(value, item.GetValue(obj).ToString()));
}
sql.dbparameters.Add(new SqlParam(ssid, obj.sysid));
sql.dbparameters.Add(new SqlParam(datatype, typeof(T).Name));
sql.dbparameters.Add(new SqlParam(dataid, item.Name));
sqls.Add(sql);
}
return(sqls);
}
public static SqlScript CreateScript(this Type obj)
{
SqlScript dbsqlscript = new SqlScript()
{
sql = $@"
CREATE TABLE {obj.Name.IsLetter()} (
"
};
string wheresql = "";
string pk = "";
List <PropertyInfo> props = obj.GetProperties().Where(item => item.GetCustomAttribute <DBColumnAttribute>() != null &&
item.GetCustomAttribute <DBColumnAttribute>().IsColumn).ToList();
for (int i = 0; i < props.Count(); i++)
{
string itemstr = props[i].Name;
if (props[i].PropertyType.IsEnum || props[i].PropertyType == typeof(string))
{
itemstr += " TEXT ";
}
else if (props[i].PropertyType == typeof(int))
{
itemstr += " INTEGER ";
}
else if (props[i].PropertyType == typeof(double))
{
itemstr += " REAL ";
}
if (props[i].IsPrimaryKey())
{
if (i < props.Count() - 1)
{
pk += $" {props[i].Name}, ";
}
else
{
pk += $" {props[i].Name} ";
}
}
if (props[i].IsNotNull())
{
itemstr += " NOT NULL ";
}
if (i < props.Count() - 1)
{
wheresql += itemstr + @",
";
}
else
{
wheresql += itemstr;
}
}
if (string.IsNullOrEmpty(pk) == false)
{
wheresql += $@",
PRIMARY KEY({pk})";
}
dbsqlscript.sql += wheresql + @"
)";
return(dbsqlscript);
}
public IEnumerable <TMain> Read <TMain>(SqlScript dbsqlscript) where TMain : class, iTable, new()
{
System.Data.DataTable dt = ReadDataTableBySql(dbsqlscript);
return(dt.ToiDBTable <TMain>());
}