/// <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));
}
/// <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);
}
}
/// <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));
}
}
/// <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]);
}
/// <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);
}
/// <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]);
}
/// <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));
}
/// <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]);
}
/// <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));
}
}
/// <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]);
}
/// <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);
}
/// <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 });
}
/// <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);
}
/// <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);
}
}
}
}
/// <summary>
/// 查询自定义SQL
/// </summary>
public DataSet SelectSql(string execSql, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
{
return(DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).GetDataset(execSql));
}
/// <summary>
/// 执行自定义SQL
/// </summary>
public void ExecuteSql(string execSql, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
{
DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).Execute(execSql);
}
/// <summary>
/// 获取当前数据库服务连接字符串
/// </summary>
public static string GetConnectionString(DbServers.DbServerName currDbName = DbServers.DbServerName.ReadHistoryDB)
{
return(DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).ConnectionString);
}
/// <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);
}