Beispiel #1
0
        public static IEnumerable <T> GetPage <T>(int page, int pageSize) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    var name = TableConvention.Resolve(typeof(T));

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = string.Format(@"with result as(select *, ROW_NUMBER() over(order by keyid desc) nr
                            from {0}
                    )
                    select  * 
                    from    result
                    where   nr  between (({1} - 1) * {2} + 1)
                            and ({1} * {2}) ", name, page, pageSize);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom <ReaderInjection>(dr);
                            yield return(o);
                        }
                    }
                }
            }
        }
Beispiel #2
0
        static string cs = SqlEasy.connString; //数据库连接字符串
        public static IEnumerable <T> GetWhere <T>(object where) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;

                    cmd.CommandText = "select * from " + TableConvention.Resolve(typeof(T)) + " where "
                                      .InjectFrom(new FieldsBy()
                                                  .SetFormat("{0}=@{0}")
                                                  .SetNullFormat("{0} is null")
                                                  .SetGlue("and"),
                                                  where);
                    cmd.InjectFrom <SetParamsValues>(where);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom <ReaderInjection>(dr);
                            yield return(o);
                        }
                    }
                }
            }
        }
Beispiel #3
0
        public static IEnumerable <T> GetAll <T>() where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from " + TableConvention.Resolve(typeof(T));
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom <ReaderInjection>(dr);
                            yield return(o);
                        }
                        dr.Close();
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
        }
Beispiel #4
0
        public static T Get <T>(long keyid) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from " + TableConvention.Resolve(typeof(T)) + " where keyid = " + keyid;
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom <ReaderInjection>(dr);
                            return(o);
                        }
                        dr.Close();
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return(default(T));
        }
Beispiel #5
0
        public static int UpdateWhatWhere <T>(object what, object where)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "update " + TableConvention.Resolve(typeof(T)) + " set "
                                      .InjectFrom(new FieldsBy().SetFormat("{0}=@{0}"), what)
                                      + " where "
                                      .InjectFrom(new FieldsBy()
                                                  .SetFormat("{0}=@wp{0}")
                                                  .SetNullFormat("{0} is null")
                                                  .SetGlue("and"),
                                                  where);

                    cmd.InjectFrom <SetParamsValues>(what);
                    cmd.InjectFrom(new SetParamsValues().Prefix("wp"), where);

                    conn.Open();
                    var retval = Convert.ToInt32(cmd.ExecuteNonQuery());
                    conn.Close();
                    return(retval);
                }
            }
        }
Beispiel #6
0
        public static int Insert(object o, string IgnoreFields)
        {
            string[] strarr = { };
            if (!string.IsNullOrEmpty(IgnoreFields))
            {
                strarr = IgnoreFields.Split(',');
            }
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "insert " + TableConvention.Resolve(o) + " ("
                                      .InjectFrom(new FieldsBy().IgnoreFields(strarr), o) + ") values("
                                      .InjectFrom(new FieldsBy().IgnoreFields(strarr).SetFormat("@{0}"), o)
                                      + ") ";

                    cmd.InjectFrom(new SetParamsValues().IgnoreFields(strarr), o);

                    conn.Open();
                    var retval = Convert.ToInt32(cmd.ExecuteNonQuery());
                    conn.Close();
                    return(retval);
                }
            }
        }
Beispiel #7
0
        public static int Delete <T>(string ids)
        {
            using (var conn = new SqlConnection(cs))
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "delete from " + TableConvention.Resolve(typeof(T)) + " where charindex(',' + cast(keyid AS varchar(50)) + ',',','  + @KeyID + ',') > 0";

                    cmd.InjectFrom <SetParamsValues>(new { KeyID = ids });
                    conn.Open();
                    return(cmd.ExecuteNonQuery());
                }
        }
Beispiel #8
0
        public static int Delete <T>(int id)
        {
            using (var conn = new SqlConnection(cs))
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "delete from " + TableConvention.Resolve(typeof(T)) + " where KeyID=@KeyID";

                    cmd.InjectFrom <SetParamsValues>(new { KeyID = id });
                    conn.Open();
                    return(cmd.ExecuteNonQuery());
                }
        }
Beispiel #9
0
        public static int Count <T>()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select count(*) from " + TableConvention.Resolve(typeof(T));
                    conn.Open();

                    return((int)cmd.ExecuteScalar());
                }
            }
        }
Beispiel #10
0
 /// <summary>
 /// 清空表
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <returns></returns>
 public static int Delete <T>()
 {
     using (var conn = new SqlConnection(cs))
     {
         using (var cmd = conn.CreateCommand())
         {
             cmd.CommandType = CommandType.Text;
             cmd.CommandText = "TRUNCATE TABLE  " + TableConvention.Resolve(typeof(T));
             conn.Open();
             var retval = cmd.ExecuteNonQuery();
             conn.Close();
             return(retval);
         }
     }
 }
Beispiel #11
0
        public static int Update(object o, params string[] fields)
        {
            using (var conn = new SqlConnection(cs))
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "update " + TableConvention.Resolve(o) + " set "
                                      .InjectFrom(new FieldsBy().IgnoreFields(fields).SetFormat("{0}=@{0}"), o)
                                      + " where KeyID = @KeyID";

                    cmd.InjectFrom <SetParamsValues>(o);

                    conn.Open();
                    return(Convert.ToInt32(cmd.ExecuteNonQuery()));
                }
        }
Beispiel #12
0
        ///<returns> the id of the inserted object </returns>
        public static int Insert(object o)
        {
            using (var conn = new SqlConnection(cs))
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "insert " + TableConvention.Resolve(o) + " ("
                                      .InjectFrom(new FieldsBy().IgnoreFields("keyid"), o) + ") values("
                                      .InjectFrom(new FieldsBy().IgnoreFields("keyid").SetFormat("@{0}"), o)
                                      + ") select @@identity";

                    cmd.InjectFrom(new SetParamsValues().IgnoreFields("keyid"), o);

                    conn.Open();
                    return(Convert.ToInt32(cmd.ExecuteScalar()));
                }
        }
Beispiel #13
0
        /// <summary>
        /// 根据条件获取记录
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="page">页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="sort">排序 如:keyid desc </param>
        /// <param name="where">查询条件</param>
        /// <returns></returns>
        public static IEnumerable <T> GetPage <T>(int page, int pageSize, string sort, object where) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    var name = TableConvention.Resolve(typeof(T));

                    if (string.IsNullOrEmpty(sort))
                    {
                        sort = "keyid desc";
                    }

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "with result as(select *, ROW_NUMBER() over(order by {3}) nr from {0} where "
                                      .InjectFrom(new FieldsBy()
                                                  .SetFormat("{0}=@{0}")
                                                  .SetNullFormat("{0} is null")
                                                  .SetGlue("and"),
                                                  where) + @" )
                    select  * 
                    from    result
                    where   nr  between (({1} - 1) * {2} + 1)
                            and ({1} * {2}) ";

                    cmd.CommandText = string.Format(cmd.CommandText, name, page, pageSize, sort);
                    cmd.InjectFrom <SetParamsValues>(where);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom <ReaderInjection>(dr);
                            yield return(o);
                        }
                        dr.Close();
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
        }
Beispiel #14
0
        public static int CountWhere <T>(object where) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select count(*) from " + TableConvention.Resolve(typeof(T)) + " where "
                                      .InjectFrom(new FieldsBy()
                                                  .SetFormat("{0}=@{0}")
                                                  .SetNullFormat("{0} is null")
                                                  .SetGlue("and"),
                                                  where);
                    cmd.InjectFrom <SetParamsValues>(where);
                    conn.Open();

                    return((int)cmd.ExecuteScalar());
                }
            }
        }
Beispiel #15
0
        public static int InsertNoIdentity(object o)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "insert " + TableConvention.Resolve(o) + " ("
                                      .InjectFrom(new FieldsBy().IgnoreFields("keyid"), o) + ") values("
                                      .InjectFrom(new FieldsBy().IgnoreFields("keyid").SetFormat("@{0}"), o) + ")";

                    cmd.InjectFrom <SetParamsValues>(o);

                    conn.Open();
                    var retval = Convert.ToInt32(cmd.ExecuteNonQuery());
                    conn.Close();
                    return(retval);
                }
            }
        }
Beispiel #16
0
        public static int DeleteWhere <T>(object where)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "delete from " + TableConvention.Resolve(typeof(T)) + " where "
                                      .InjectFrom(new FieldsBy()
                                                  .SetFormat("{0}=@{0}")
                                                  .SetNullFormat("{0} is null")
                                                  .SetGlue("and"),
                                                  where);

                    cmd.InjectFrom <SetParamsValues>(where);
                    conn.Open();
                    var retval = cmd.ExecuteNonQuery();
                    conn.Close();
                    return(retval);
                }
            }
        }