Exemple #1
0
        public static int Delete <T>(IConnection conn, ICriteria <T> criteria) where T : EntityBase, new()
        {
            SQLBuilder sql          = new TSQLBuilder();
            string     sqlStatement = string.Empty;

            try
            {
                sql.BuildDelete(SQLSyntax.Delete, (new T()).Mapping);
                Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
                foreach (var item in sqlSyntaxDictionary)
                {
                    sql.BuildDelete(item.Key, item.Value);
                }

                sqlStatement = sql.Delete;
                return(conn.ExecuteNonQuery(sqlStatement));
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "{" + sqlStatement + "}");
            }
            finally
            {
            }
        }
Exemple #2
0
        public static string QueryBy <T>(IConnection pConn, string pField, string pCondition) where T : EntityBase, new()
        {
            T           dataObject = new T();
            IDataReader reader     = null;
            SQLBuilder  sql        = new TSQLBuilder();
            string      value      = string.Empty;

            try
            {
                sql.BuildQuery(SQLSyntax.Select, pField);
                sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
                sql.BuildUpdate(SQLSyntax.Where, pCondition);

                reader = pConn.ExecuteReader(sql.Query);
                if (reader.Read())
                {
                    value = reader[0].ToString().Trim();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((reader != null) || (!reader.IsClosed))
                {
                    reader.Close();
                }
            }
            return(value);
        }
Exemple #3
0
        public static int Update <T>(IConnection conn, T entity, ICriteria <T> criteria) where T : EntityBase
        {
            SQLBuilder sql          = new TSQLBuilder();
            string     sqlStatement = string.Empty;

            try
            {
                sql.BuildUpdate(SQLSyntax.Update, entity.Mapping);
                sql.BuildUpdate(SQLSyntax.Set, entity.UpdateContent);
                Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
                foreach (var item in sqlSyntaxDictionary)
                {
                    sql.BuildUpdate(item.Key, item.Value);
                }

                //if (entity.NeedAudit) Audit<T>(conn, entity, SQLSyntax.Update);
                sqlStatement = sql.Update;
                return(conn.ExecuteNonQuery(sqlStatement));
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "{" + sqlStatement + "}");
            }
            finally
            {
            }
        }
Exemple #4
0
        public static T QueryByKey <T>(IConnection conn, string keyValue) where T : EntityBase, new()
        {
            T           dataObject = new T();
            IDataReader reader     = null;
            SQLBuilder  sql        = new TSQLBuilder();

            try
            {
                sql.BuildQuery(SQLSyntax.Select, (new T()).Fields);
                sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
                sql.BuildUpdate(SQLSyntax.Where, (new T()).GetKeyCondition(keyValue));

                reader = conn.ExecuteReader(sql.Query);
                if (reader.Read())
                {
                    ORMapping(dataObject, reader);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((reader != null) || (!reader.IsClosed))
                {
                    reader.Close();
                }
            }
            return(dataObject);
        }
Exemple #5
0
        public static List <T> QueryMultiBy <T>(IConnection pConn, string pCondition) where T : EntityBase, new()
        {
            List <T>    dataObjectList = new List <T>();
            IDataReader reader         = null;
            SQLBuilder  sql            = new TSQLBuilder();

            try
            {
                sql.BuildQuery(SQLSyntax.Select, (new T()).Fields);
                sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
                sql.BuildUpdate(SQLSyntax.Where, pCondition);

                reader = pConn.ExecuteReader(sql.Query);
                while (reader.Read())
                {
                    T dataObject = new T();
                    ORMapping(dataObject, reader);
                    dataObjectList.Add(dataObject);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((reader != null) || (!reader.IsClosed))
                {
                    reader.Close();
                }
            }
            return(dataObjectList);
        }
Exemple #6
0
        public static T QueryByKey <T>(IConnection conn, ICriteria <T> criteria) where T : IDataObject, new()
        {
            T           dataObject = new T();
            IDataReader reader     = null;
            SQLBuilder  sql        = new TSQLBuilder();

            try
            {
                sql.BuildQuery(SQLSyntax.Select, (new T()).Fields);
                sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
                Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
                foreach (var item in sqlSyntaxDictionary)
                {
                    sql.BuildQuery(item.Key, item.Value);
                }

                reader = conn.ExecuteReader(sql.Query);
                if (reader.Read())
                {
                    ORMapping(dataObject, reader);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((reader != null) || (!reader.IsClosed))
                {
                    reader.Close();
                }
            }
            return(dataObject);
        }
Exemple #7
0
        public static List <string> Distinct <T>(IConnection conn, string fieldName, ICriteria <T> criteria) where T : IDataObject, new()
        {
            List <string> distinctList = new List <string>();
            IDataReader   reader       = null;
            SQLBuilder    sql          = new TSQLBuilder();

            try
            {
                sql.BuildQuery(SQLSyntax.Select, string.Format(" DISTINCT {0} ", fieldName));
                sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
                Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
                foreach (var item in sqlSyntaxDictionary)
                {
                    sql.BuildQuery(item.Key, item.Value);
                }

                reader = conn.ExecuteReader(sql.Query);
                while (reader.Read())
                {
                    distinctList.Add(reader.GetValue(reader.GetOrdinal(fieldName)).ToString().Trim());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((reader != null) || (!reader.IsClosed))
                {
                    reader.Close();
                }
            }
            return(distinctList);
        }
Exemple #8
0
        public static int Count <T>(IConnection conn, ICriteria <T> criteria) where T : IDataObject, new()
        {
            int        count = 0;
            SQLBuilder sql   = new TSQLBuilder();

            try
            {
                sql.BuildQuery(SQLSyntax.Select, " COUNT(*) ");
                sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
                Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
                foreach (var item in sqlSyntaxDictionary)
                {
                    sql.BuildQuery(item.Key, item.Value);
                }

                count = Convert.ToInt32(conn.ExecuteScalar(sql.Query));
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
            }
            return(count);
        }
Exemple #9
0
        // Parameter with Transaction or NOT
        public static int Update <T>(IConnection conn, T entity, bool transact) where T : EntityBase
        {
            SQLBuilder sql          = new TSQLBuilder();
            string     sqlStatement = string.Empty;

            try
            {
                sql.BuildUpdate(SQLSyntax.Update, entity.Mapping);
                sql.BuildUpdate(SQLSyntax.Set, entity.UpdateContent);
                sql.BuildUpdate(SQLSyntax.Where, entity.KeyCondition);

                sqlStatement = sql.Update;

                if (transact)
                {
                    conn.BeginTransaction();
                    int result = conn.ExecuteNonQuery(sqlStatement);

                    if (result == 0)
                    {
                        conn.Rollback();
                    }
                    else
                    {
                        conn.Commit();
                    }

                    return(result);
                }
                else
                {
                    return(conn.ExecuteNonQuery(sqlStatement));
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "{" + sqlStatement + "}");
            }
            finally
            {
            }
        }
Exemple #10
0
        public static int Delete <T>(IConnection conn, T entity) where T : EntityBase
        {
            SQLBuilder sql          = new TSQLBuilder();
            string     sqlStatement = string.Empty;

            try
            {
                sql.BuildDelete(SQLSyntax.Delete, entity.Mapping);
                sql.BuildDelete(SQLSyntax.Where, entity.KeyCondition);

                //if (entity.NeedAudit) Audit<T>(conn, entity, SQLSyntax.Delete);
                sqlStatement = sql.Delete;
                return(conn.ExecuteNonQuery(sqlStatement));
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "{" + sqlStatement + "}");
            }
            finally
            {
            }
        }
Exemple #11
0
        public static List <T> QueryTop <T>(IConnection conn, ICriteria <T> criteria, int topCount = 1) where T : IDataObject, new()
        {
            List <T>    dataObjectList = new List <T>();
            IDataReader reader         = null;
            SQLBuilder  sql            = new TSQLBuilder();

            try
            {
                sql.BuildQuery(SQLSyntax.Top, topCount.ToString(), (new T()).Fields);
                sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);

                Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
                foreach (var item in sqlSyntaxDictionary)
                {
                    sql.BuildQuery(item.Key, item.Value);
                }

                reader = conn.ExecuteReader(sql.Query);
                while (reader.Read())
                {
                    T dataObject = new T();
                    ORMapping(dataObject, reader);
                    dataObjectList.Add(dataObject);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((reader != null) || (!reader.IsClosed))
                {
                    reader.Close();
                }
            }

            return(dataObjectList);
        }
Exemple #12
0
//        private static void Audit<T>(IConnection conn, T entity, SQLSyntax sqlsyntax) where T : EntityBase
//        {
//            string auditSql = @"INSERT INTO [AUDIT] (AUD_SERNO, FUT_GROUP, FUT_NAME, BRN_NAME, ACC_NAME,
//AUD_DTTM, AUD_OLDCOLUMN, AUD_NEWCOLUMN, AUD_COMMENT) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}',
//'{5}', '{6}', '{7}', '{8}')";
//            auditSql = string.Format(auditSql,
//                My.GenSerNo(),
//                entity.Description,
//                entity.Description,
//                "",
//                "",
//                My.DatetimeToDateTimeStr(DateTime.Now),
//                "",
//                "",
//                sqlsyntax.ToString()
//            );
//            conn.ExecuteNonQuery(auditSql);
//        }
        #endregion

        #region CRUD - Create
        public static bool Add <T>(IConnection conn, T entity) where T : EntityBase
        {
            SQLBuilder sql          = new TSQLBuilder();
            string     sqlStatement = string.Empty;

            try
            {
                sql.BuildInsert(SQLSyntax.Insert, entity.Mapping);
                sql.BuildInsert(SQLSyntax.Fields, entity.Fields);
                sql.BuildInsert(SQLSyntax.Values, entity.InsertValues);

                //if (entity.NeedAudit) Audit<T>(conn, entity, SQLSyntax.Insert);
                sqlStatement = sql.Insert;
                return(conn.ExecuteNonQuery(sqlStatement) > 0);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "{" + sqlStatement + "}");
            }
            finally
            {
            }
        }