Exemple #1
0
        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
            });
        }
Exemple #3
0
        /// <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
            });
        }
Exemple #4
0
        /// <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
            });
        }
Exemple #5
0
        /// <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);
        }
Exemple #6
0
        /// <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);
        }
Exemple #10
0
        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>());
 }