Пример #1
0
        public static CommonPage GetPageRaw(string connStr,string sql,int pageIndex,int pageSize)
        {
            if (string.IsNullOrEmpty(connStr))
                connStr = GetConnectionStr();


        


            DBHandler db = DBHandlerFactory.GetHandler(connStr);
            db.CommandText = sql;
            db.CommandType = CommandType.Text;
            DataTable dt = db.ExecuteDataTable();

            CommonPage cp = new CommonPage();
            cp.RecordCount = db.TotalCount;

            int pg = 0;
            pg = cp.RecordCount / pageSize;

            if (cp.RecordCount % pageSize > 0)
            {
                pg++;
            }

            cp.PageCount = pg;

            cp.DataTable = dt;

            db.Close();

            return cp;
        }
Пример #2
0
        public static CommonPage GetCombinePage(string connStr, string table1,string table2, string[] fields1,string[] fields2,string on, string where, string orderby, int pageIndex, int pageSize)
        {
            string sql = "select ";

            for (int i = 0; i < fields1.Length; i++)
            {
                sql += "" + table1 + "." + fields1[i];
                //if (i != fields1.Length - 1)
                    sql += ",";
                
            }
            for (int i = 0; i < fields2.Length; i++)
            {
                sql += "" + table2 + "." + fields2[i];
                if (i != fields2.Length - 1)
                    sql += ",";
            }

            if (fields1 == null || fields2 == null)
            {
                sql += " * ";
            }

            sql += " from " + table1 +" ";
            sql += " left outer join "+ table2+" on " + on;

            if (!string.IsNullOrEmpty(where))
                sql += " where " + where + " ";

            if (!string.IsNullOrEmpty(orderby))
                sql += " order by " + orderby;

            sql = sql.Trim();


            DBHandler db = DBHandlerFactory.GetHandler(connStr);
            db.CommandText = sql;
            db.CommandType = CommandType.Text;
            DataTable dt = db.ExecuteDataTable(pageSize, pageIndex);

            CommonPage cp = new CommonPage();
            cp.RecordCount = db.TotalCount;
            cp.DataTable = dt;

            db.Close();

            return cp;
        }
Пример #3
0
        public static int Insert(string connStr, string tableName, string[] fields, object[] values)
        {

            if (string.IsNullOrEmpty(connStr))
                connStr = GetConnectionStr();

            DBHandler db = DBHandlerFactory.GetHandler(connStr);

          
            string sql = "insert into " + tableName + " (";
 
            for (int i = 0; i < fields.Length; i++)
            {
                sql += fields[i];
                if (i != fields.Length - 1)
                    sql += ",";

                if (values[i] == null)
                    values[i] = "";

                db.AddParameter(fields[i], values[i]);
               
            }

            sql += ")values(";

            for (int i = 0; i < values.Length; i++)
            {
                sql += "@"+fields[i]+"";
                if (i != values.Length - 1)
                    sql += ",";
            }
            sql += ")";

            db.CommandText = sql;
            db.CommandType = CommandType.Text;


            int ret = db.ExecuteNonQuery();
            db.Close();

            return ret;
        }
Пример #4
0
        public static string GetValue(string connStr, string sql)
        {
            if (string.IsNullOrEmpty(connStr))
                connStr = GetConnectionStr();
 


            DBHandler db = DBHandlerFactory.GetHandler(connStr);
            db.CommandText = sql;
            db.CommandType = CommandType.Text;
            object str =  db.ExecuteScalar();
            db.Close();
            if (str == null)
                return null;
            else
               return  Convert.ToString(str);
 

        }
Пример #5
0
        public static CommonPageWithCat GetPageWithCat(string connStr, string tableName, string listfield, string where, string orderby, int pageIndex, int pageSize)
        {
            if (string.IsNullOrEmpty(connStr))
                connStr = GetConnectionStr();


            string sql = "select " + listfield + " from " + tableName + " ";

            if (!string.IsNullOrEmpty(where))
                sql += " where " + where + " ";

            if (!string.IsNullOrEmpty(orderby))
                sql += " order by " + orderby + " ";

            sql = sql.Trim();


            DBHandler db = DBHandlerFactory.GetHandler(connStr);
            db.CommandText = sql;
            db.CommandType = CommandType.Text;
            DataTable dt = db.ExecuteDataTable(pageSize, pageIndex);

            CommonPageWithCat cp = new CommonPageWithCat();
            cp.RecordCount = db.TotalCount;

            int pg = 0;
            pg = cp.RecordCount / pageSize;

            if (cp.RecordCount % pageSize > 0)
            {
                pg++;
            }

            cp.PageCount = pg;

            cp.DataTable = dt;

            db.Close();

            return cp;
        }
Пример #6
0
        private static DataRow SelectSingleModel(string connStr, string tableName, string listfield, string where)
        {
            if (string.IsNullOrEmpty(connStr))
                connStr = GetConnectionStr();

            int pageIndex = 1;
            int pageSize = 1;
            string orderby = "id desc";

            string sql = "select " + listfield + " from " + tableName + " ";

            if (!string.IsNullOrEmpty(where))
                sql += " where " + where + " ";

            if (!string.IsNullOrEmpty(orderby))
                sql += " order by " + orderby + " ";

            sql = sql.Trim();


            DBHandler db = DBHandlerFactory.GetHandler(connStr);
            db.CommandText = sql;
            db.CommandType = CommandType.Text;
            DataTable dt = db.ExecuteDataTable(pageSize, pageIndex);

            CommonPage cp = new CommonPage();
            cp.RecordCount = db.TotalCount;
            cp.DataTable = dt;

            db.Close();

            if (cp != null && cp.DataTable != null && cp.DataTable.Rows.Count > 0)
            {
                return cp.DataTable.Rows[0];
            }
            else
                return null;

             
        }
Пример #7
0
        public static int Delete(string connStr, string tableName, string where)
        {

            if (string.IsNullOrEmpty(connStr))
                connStr = GetConnectionStr();

            DBHandler db = DBHandlerFactory.GetHandler(connStr);


            string sql = "delete from " + tableName ;
            if (!string.IsNullOrEmpty(where))
                sql += " where " + where;


            db.CommandText = sql;
            db.CommandType = CommandType.Text;


            int ret = db.ExecuteNonQuery();
            db.Close();

            return ret;
            
        }
Пример #8
0
        public static int Update(string connStr, string tableName, string[] fields,object[] values, string where)
        {

            if (string.IsNullOrEmpty(connStr))
                connStr = GetConnectionStr();

            DBHandler db = DBHandlerFactory.GetHandler(connStr);


            string sql = "update " + tableName + "  set ";
            for (int i = 0; i < fields.Length; i++)
            {
                sql += fields[i] + "=@" + fields[i];
                if (i != fields.Length - 1)
                    sql += ",";

                string v = (string)values[i];
                if (v == null)
                    v = "";

                db.AddParameter(fields[i], v);
               
            }
            if (!string.IsNullOrEmpty(where))
                sql += " where " + where;


            db.CommandText = sql;
            db.CommandType = CommandType.Text;


            int ret = db.ExecuteNonQuery();
            db.Close();

            return ret;
        }