Exemple #1
0
        /// <summary>
        /// 获取总记录数
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="where">查询条件</param>
        /// <param name="dbname">使用数据库服务名</param>
        public int SelectDataCount(string tablename, string where = "", DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            string selectSql = string.Format("Select count(*) From {0} {1}", tablename, where);
            string strCount  = DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).GetScalar(selectSql).ToString();

            return(int.Parse(strCount));
        }
Exemple #2
0
 /// <summary>
 /// 保存数据
 /// </summary>
 public static bool SaveEntity(aspnet_ManagersEntity entity, bool isAdd, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
 {
     try
     {
         string execSql = (isAdd) ?
                          "Insert Into aspnet_Managers(RoleId,ClientUserId,state,CreateDate,UserName,Password,Email,AgentName,poi_id)values(@RoleId,@ClientUserId,@state,@CreateDate,@UserName,@Password,@Email,@AgentName,@poi_id)" :
                          "Update aspnet_Managers Set RoleId=@RoleId,ClientUserId=@ClientUserId,state=@state,CreateDate=@CreateDate,UserName=@UserName,Password=@Password,Email=@Email,AgentName=@AgentName,poi_id=@poi_id Where UserId=@UserId";
         SqlParameter[] para = new SqlParameter[]
         {
             new SqlParameter("@UserId", entity.UserId),
             new SqlParameter("@RoleId", entity.RoleId),
             new SqlParameter("@ClientUserId", entity.ClientUserId),
             new SqlParameter("@state", entity.State),
             (entity.CreateDate == null || entity.CreateDate == DateTime.MinValue)?new SqlParameter("@CreateDate", DBNull.Value):new SqlParameter("@CreateDate", entity.CreateDate),
             (entity.UserName == null)?new SqlParameter("@UserName", DBNull.Value):new SqlParameter("@UserName", entity.UserName),
             (entity.Password == null)?new SqlParameter("@Password", DBNull.Value):new SqlParameter("@Password", entity.Password),
             (entity.Email == null)?new SqlParameter("@Email", DBNull.Value):new SqlParameter("@Email", entity.Email),
             (entity.AgentName == null)?new SqlParameter("@AgentName", DBNull.Value):new SqlParameter("@AgentName", entity.AgentName),
             (entity.Poi_id == null)?new SqlParameter("@poi_id", DBNull.Value):new SqlParameter("@poi_id", entity.Poi_id),
         };
         DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).Execute(execSql, para);
         return(true);
     }
     catch
     {
         return(false);
     }
 }
Exemple #3
0
        /// <summary>
        /// 根据主键查询数据实体
        /// </summary>
        public static aspnet_ManagersEntity LoadEntity(int ID, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            string selectSql = string.Format(@"Select * From aspnet_Managers Where UserId={0}", ID);

            using (IDataReader reader = DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).GetReader(selectSql))
            {
                return(ReaderConvert.ReaderToModel <aspnet_ManagersEntity>(reader));
            }
        }
Exemple #4
0
        /// <summary>
        /// 根据主键查询数据集
        /// </summary>
        public static DataTable LoadData(int ID, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            string  selectSql = string.Format(@"Select * From aspnet_Managers Where UserId={0}", ID);
            DataSet ds        = DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).GetDataset(selectSql);

            ds.Tables[0].TableName  = "aspnet_Managers";
            ds.Tables[0].PrimaryKey = new DataColumn[] { ds.Tables[0].Columns["UserId"] };
            return(ds.Tables[0]);
        }
Exemple #5
0
        /// <summary>
        /// 根据条件删除
        /// </summary>
        public static void DelListData(string where = null, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            if (!string.IsNullOrEmpty(where))
            {
                where = " Where " + where;
            }
            string deleteSql = string.Format(@"Delete From aspnet_Managers {0}", where);

            DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).Execute(deleteSql);
        }
Exemple #6
0
        /// <summary>
        /// 获取分页数据(oracle里有row_number虚列。此方法是MS效仿oracle而增加的内置游标分标取虚列方法,分页快)
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="orderFields">排序字段</param>
        /// <param name="selectFields">查询字段</param>
        /// <param name="currPage">当前页码</param>
        /// <param name="pagesize">页容量</param>
        /// <param name="where">查询条件</param>
        /// <param name="dbname">使用数据库服务名</param>
        public DataTable SelectPageData(string tablename, string orderFields, string selectFields, int currPage, int pagesize, string where, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            string selectSql  = @"Select * From (
                                    Select ROW_NUMBER() over(order by {1} ) as rid ,{2} From {0} {5}
                                )t Where t.rid>={3} And t.rid<{4}";
            int    startIndex = (currPage - 1) * pagesize + 1;
            int    endIndex   = startIndex + pagesize;

            selectSql = string.Format(selectSql, tablename, orderFields, selectFields, startIndex, endIndex, where);
            return(DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).GetDataset(selectSql).Tables[0]);
        }
Exemple #7
0
        /// <summary>
        /// 根据条件查询首行首列
        /// </summary>
        public object SelectScalar(string tablename, string where = null, string selectFields = "*", string orderby = null, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            if (!string.IsNullOrEmpty(where))
            {
                where = " Where " + where;
            }
            string selectSql = string.Format(@"Select {1} From {2} {0}", where, selectFields, tablename);

            if (!string.IsNullOrEmpty(orderby))
            {
                selectSql += " Order By " + orderby;
            }
            return(DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).GetScalar(selectSql));
        }
Exemple #8
0
        /// <summary>
        /// 根据条件查询数据集
        /// </summary>
        public DataTable SelectListData(string tablename, string where = null, string selectFields = "*", string orderby = null, int top = 0, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            if (!string.IsNullOrEmpty(where))
            {
                where = " Where " + where;
            }
            string selectSql = string.Format(@"Select {3} {1} From {2} {0}", where, selectFields, tablename, top == 0 ? "" : "top " + top);

            if (!string.IsNullOrEmpty(orderby))
            {
                selectSql += " Order By " + orderby;
            }
            DataSet ds = DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).GetDataset(selectSql);

            ds.Tables[0].TableName = tablename;
            return(ds.Tables[0]);
        }
Exemple #9
0
        /// <summary>
        /// 根据条件查询数据实体
        /// </summary>
        public static IList <aspnet_ManagersEntity> SelectListEntity(string where = null, string selectFields = "*", string orderby = null, int top = 0, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            if (!string.IsNullOrEmpty(where))
            {
                where = " Where " + where;
            }
            string selectSql = string.Format(@"Select {2} {1} From aspnet_Managers {0}", where, selectFields, top == 0 ? "" : "top " + top);

            if (!string.IsNullOrEmpty(orderby))
            {
                selectSql += " Order By " + orderby;
            }
            using (IDataReader reader = DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).GetReader(selectSql))
            {
                return(ReaderConvert.ReaderToList <aspnet_ManagersEntity>(reader));
            }
        }
Exemple #10
0
        /// <summary>
        /// 根据条件查询数据集
        /// </summary>
        public static DataTable SelectListData(string where = null, string selectFields = "*", string orderby = null, int top = 0, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            if (!string.IsNullOrEmpty(where))
            {
                where = " Where " + where;
            }
            string selectSql = string.Format(@"Select {2} {1} From aspnet_Managers {0}", where, selectFields, top == 0 ? "" : "top " + top);

            if (!string.IsNullOrEmpty(orderby))
            {
                selectSql += " Order By " + orderby;
            }
            DataSet ds = DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).GetDataset(selectSql);

            ds.Tables[0].TableName  = "aspnet_Managers";
            ds.Tables[0].PrimaryKey = new DataColumn[] { ds.Tables[0].Columns["UserId"] };
            return(ds.Tables[0]);
        }
Exemple #11
0
        /// <summary>
        /// 批量提交数据集
        /// </summary>
        public bool CommitDataSet(DataSet dsData, string[] arraySelectSql, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            int execCount = arraySelectSql.Length;

            using (SqlConnection connection = new SqlConnection(DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).ConnectionString))
            {
                SqlTransaction tran = null;
                try
                {
                    connection.Open();
                    tran = connection.BeginTransaction();
                    for (int i = 0; i < dsData.Tables.Count; i++)
                    {
                        SqlCommand        cmd     = new SqlCommand(arraySelectSql[i], connection, tran);
                        SqlDataAdapter    da      = new SqlDataAdapter(cmd);
                        SqlCommandBuilder builder = new SqlCommandBuilder(da);
                        da.Update(dsData.Tables[i]);
                        execCount--;
                    }
                    if (execCount == 0)
                    {
                        tran.Commit();
                    }
                }
                catch
                {
                    if (tran != null)
                    {
                        tran.Rollback();
                    }
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }
            return((execCount == 0) ? true : false);
        }
Exemple #12
0
        /// <summary>
        /// 调用存储过程创建(团购)订单
        /// </summary>
        public int[] CreateOrderTuan(string orderid, int uid, int businessId, int typeid, int q_end_time_second, string recordcode, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            int state = -1;
            int businesstype = -1;
            int tid = -1; int tuanlistId = -1;

            SqlParameter[] para =
            {
                new SqlParameter("@orderid",           SqlDbType.VarChar),
                new SqlParameter("@uid",               SqlDbType.Int),
                new SqlParameter("@businessId",        SqlDbType.Int),
                new SqlParameter("@typeid",            SqlDbType.Int),
                new SqlParameter("@q_end_time_second", SqlDbType.Int),
                new SqlParameter("@recordcode",        SqlDbType.VarChar),
                new SqlParameter("@state",             SqlDbType.Int),
                new SqlParameter("@businesstype",      SqlDbType.Int),
                new SqlParameter("@tid",               SqlDbType.Int),
                new SqlParameter("@tuanlistId",        SqlDbType.Int)
            };
            para[0].Value     = orderid; para[1].Value = uid; para[2].Value = businessId; para[3].Value = typeid; para[4].Value = q_end_time_second; para[5].Value = recordcode;
            para[6].Direction = ParameterDirection.Output; para[7].Direction = ParameterDirection.Output; para[8].Direction = ParameterDirection.Output; para[9].Direction = ParameterDirection.Output;  //设定参数的输出方向
            using (SqlCommand command = CreateDbCommand(DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).ConnectionString, "CreateOrderTuan", para, CommandType.StoredProcedure))
            {
                command.Connection.Open();
                object result = command.ExecuteScalar();
                state        = int.Parse(command.Parameters[6].Value.ToString());
                businesstype = int.Parse(command.Parameters[7].Value.ToString());
                tid          = int.Parse(command.Parameters[8].Value.ToString());
                tuanlistId   = int.Parse(command.Parameters[9].Value.ToString());
                command.Connection.Close();
            }
            return(new int[] { state, businesstype, tid, tuanlistId });
        }
Exemple #13
0
        /// <summary>
        /// 调用存储过程创建直购订单
        /// </summary>
        public int CreateQuanOrder(string orderid, int uid, int businessId, int redpaper, string ip, string pay_type, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            int state = 0;

            SqlParameter[] para =
            {
                new SqlParameter("@orderid",    SqlDbType.VarChar),
                new SqlParameter("@uid",        SqlDbType.Int),
                new SqlParameter("@businessId", SqlDbType.Int),
                new SqlParameter("@redpaper",   SqlDbType.Int),
                new SqlParameter("@ip",         SqlDbType.VarChar),
                new SqlParameter("@pay_type",   SqlDbType.VarChar),
                new SqlParameter("@state",      SqlDbType.Int),
            };
            para[0].Value     = orderid; para[1].Value = uid; para[2].Value = businessId;
            para[3].Value     = redpaper; para[4].Value = ip; para[5].Value = pay_type;
            para[6].Direction = ParameterDirection.Output; //设定参数的输出方向
            using (SqlCommand command = CreateDbCommand(DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).ConnectionString, "CreateOrderQuan", para, CommandType.StoredProcedure))
            {
                command.Connection.Open();
                object result = command.ExecuteScalar();
                state = int.Parse(command.Parameters[6].Value.ToString());
                command.Connection.Close();
            }
            return(state);
        }
Exemple #14
0
 /// <summary>
 /// 批量提交数据表
 /// </summary>
 public bool CommitDataTable(DataTable dtData, string selectSql, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
 {
     using (SqlConnection connection = new SqlConnection(DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).ConnectionString))
     {
         connection.Open();
         using (SqlCommand cmd = new SqlCommand(selectSql, connection))
         {
             using (SqlDataAdapter da = new SqlDataAdapter(cmd))
             {
                 int returnValue = 0;
                 try
                 {
                     SqlCommandBuilder builder = new SqlCommandBuilder(da);
                     returnValue = da.Update(dtData);
                 }
                 catch (Exception ex)
                 {
                     returnValue = -1;
                 }
                 return((returnValue != -1) ? true : false);
             }
         }
     }
 }
Exemple #15
0
 /// <summary>
 /// 查询自定义SQL
 /// </summary>
 public DataSet SelectSql(string execSql, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
 {
     return(DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).GetDataset(execSql));
 }
Exemple #16
0
 /// <summary>
 /// 执行自定义SQL
 /// </summary>
 public void ExecuteSql(string execSql, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
 {
     DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).Execute(execSql);
 }
Exemple #17
0
 /// <summary>
 /// 获取当前数据库服务连接字符串
 /// </summary>
 public static string GetConnectionString(DbServers.DbServerName currDbName = DbServers.DbServerName.ReadHistoryDB)
 {
     return(DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).ConnectionString);
 }
Exemple #18
0
        /// <summary>
        /// 根据主键删除
        /// </summary>
        public static void Del(int ID, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
        {
            string deleteSql = string.Format(@"Delete From aspnet_Managers Where UserId={0}", ID);

            DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).Execute(deleteSql);
        }