/// <summary>
/// 循环里面 每次获取10个分类 isLeaf=1 AND ID >{0}
/// </summary>
/// <param name="id"> </param>
/// <returns></returns>
public static List <CategoryInfoEntity> GetList_9()
{
string sqlText = "SELECT top 10 * FROM dbo.CategoryInfo WHERE isLeaf=1 and IsProcess=0 ";
DataSet ds = DBCommon.DBHelper.Query(ConnString, sqlText);
return(DALUtil.ConvertDataTableToEntityList <CategoryInfoEntity>(ds.Tables[0]));
}
public bool isProc(string procName)
{
bool isConnected = chkConnection();
if (isConnected)
{
string sqlIsProc = "select * from sysobjects where xtype='p' and name='" + procName + "'";
SqlCommand comm = new SqlCommand(sqlIsProc, this.Con);
comm.CommandType = CommandType.Text;
SqlDataReader sdr = comm.ExecuteReader();
if (!sdr.IsClosed)
{
int i = 0;
//判断是否有下一行数据
while (sdr.Read())
{
DALUtil.Output(this, "No." + (i++) + " 所查存储过程名为: " + sdr[0]);
if (procName.Equals(sdr[0]))
{
sdr.Close();
return(true);
}
}
}
sdr.Close();
return(false);
}
else
{
return(false);
}
}
/// <summary>
/// 修改
/// </summary>
/// <param name="pramsModify">修改参数集合</param>
/// <param name="id">ID</param>
/// <returns>成功返回影响行数,失败返回0</returns>
public static int Modify(SqlParameter[] pramsModify, int id)
{
SqlParameter[] pramsWhere =
{
DALUtil.MakeInParam("@ID", SqlDbType.Int, 4, id)
};
return(DBCommon.DBHelper.Modify(ConnString, TableName, pramsModify, pramsWhere));
}
/// <summary>
/// 根据ID删除
/// </summary>
/// <param name="ID">ID</param>
/// <returns>成功返回影响行数,失败返回0</returns>
public static int Delete_1(int ID)
{
SqlParameter[] pramsWhere =
{
DALUtil.MakeInParam("@ID", SqlDbType.Int, 4, ID)
};
return(DBCommon.DBHelper.Delete(ConnString, TableName, pramsWhere));
}
/// <summary>
///新增
/// <summary>
/// <param name="pramsAdd">参数</param>
/// <returns>成功返回自增ID</returns>
public static int Add(long categoryID, long attrID)
{
SqlParameter[] pramsAdd =
{
DALUtil.MakeInParam("@categoryID", SqlDbType.BigInt, 4, categoryID),
DALUtil.MakeInParam("@attrID", SqlDbType.BigInt, 4, attrID),
};
return(DBCommon.DBHelper.Add2(ConnString, TableName, pramsAdd));
}
/// <summary>
///新增
/// <summary>
/// <param name="pramsAdd">参数</param>
/// <returns>成功返回自增ID</returns>
public static int Add(long fid, long subID, int attrType)
{
SqlParameter[] pramsAdd =
{
DALUtil.MakeInParam("@fid", SqlDbType.BigInt, 4, fid),
DALUtil.MakeInParam("@subID", SqlDbType.BigInt, 4, subID),
DALUtil.MakeInParam("@attrType", SqlDbType.Int, 4, attrType),
};
return(DBCommon.DBHelper.Add2(ConnString, TableName, pramsAdd));
}
public static bool IsExist(long attrValueID)
{
SqlParameter[] pramsWhere =
{
DAL.DALUtil.MakeInParam("@attrValueID", System.Data.SqlDbType.Int, 4, attrValueID),
};
int count = DALUtil.ConvertToInt32(DBCommon.DBHelper.GetSingle(ConnString, TableName, "Count(0)", pramsWhere));
return(count > 0);
}
/// <summary>
/// 获取集合
/// </summary>
/// <param name="SelectIF">需要查询的字段</param>
/// <param name="pramsWhere">条件集合</param>
/// <param name="OrderName">排序无需带Order by</param>
public static List <CategoryInfoEntity> GetList(string SelectIF, int level, string OrderName = "")
{
SqlParameter[] pramsWhere =
{
DALUtil.MakeInParam("@level", SqlDbType.Int, 4, level)
};
DataTable dt = DBCommon.DBHelper.GetDataTable2(ConnString, TableName, SelectIF, pramsWhere, OrderName);
return(DALUtil.ConvertDataTableToEntityList <CategoryInfoEntity>(dt));
}
/// <summary>
///新增
/// <summary>
/// <param name="pramsAdd">参数</param>
/// <returns>成功返回自增ID</returns>
public static int Add(long categoryID, string name, long parentID, int level)
{
SqlParameter[] pramsAdd =
{
DALUtil.MakeInParam("@categoryID", SqlDbType.Int, 4, categoryID),
DALUtil.MakeInParam("@name", SqlDbType.NVarChar, 100, name),
DALUtil.MakeInParam("@parentID", SqlDbType.Int, 4, parentID),
DALUtil.MakeInParam("@level", SqlDbType.Int, 4, level),
};
return(DBCommon.DBHelper.Add2(ConnString, TableName, pramsAdd));
}
/// <summary>
/// 获取自定义参数数据
/// </summary>
/// <param name="ID">ID</param>
/// <param name="SelectIF">查询字段</param>
public static AttributeValueInfoEntity Get_98(long attrValueID, string SelectIF)
{
try
{
//参数Where条件
SqlParameter[] pramsWhere =
{
DALUtil.MakeInParam("@attrValueID", SqlDbType.BigInt, 4, attrValueID)
};
return(Get1(SelectIF, pramsWhere));
}
catch { return(null); }
}
/// <summary>
/// 获取自定义参数数据
/// </summary>
/// <param name="ID">ID</param>
/// <param name="SelectIF">查询字段</param>
public static CategoryInfoEntity Get_99(int ID, string SelectIF)
{
try
{
//参数Where条件
SqlParameter[] pramsWhere =
{
DALUtil.MakeInParam("@ID", SqlDbType.Int, 4, ID)
};
return(Get1(SelectIF, pramsWhere));
}
catch { return(null); }
}
/// <summary>
/// 获取数据总数量
/// 条件：a=1 and b=2
/// </summary>
/// <param name="strWhere">条件(a=1 and b=2)</param>
/// <param name="valuesList">条件集合值</param>
public static int GetRecordCount(string strWhere, List <object> valuesList)
{
string SelectIF = " count(1) ";
object obj = DBCommon.DBHelper.GetSingle(ConnString, TableName, SelectIF, strWhere, valuesList.ToArray());
if (obj == null)
{
return(0);
}
else
{
return(DALUtil.ConvertToInt32(obj));
}
}
public static int Modify(long attrValueID, string name, string enName, int isSKU)
{
SqlParameter[] pramsModify =
{
DAL.DALUtil.MakeInParam("@name", System.Data.SqlDbType.NVarChar, 100, name),
DAL.DALUtil.MakeInParam("@enName", System.Data.SqlDbType.NVarChar, 100, enName),
DAL.DALUtil.MakeInParam("@isSKU", System.Data.SqlDbType.Int, 4, isSKU),
};
SqlParameter[] pramsWhere =
{
DALUtil.MakeInParam("@attrValueID", SqlDbType.BigInt, 4, attrValueID)
};
return(DBCommon.DBHelper.Modify(ConnString, TableName, pramsModify, pramsWhere));
}
/// <summary>
/// 修改
/// </summary>
/// <param name="pramsModify">修改参数集合</param>
/// <param name="pramsWhere">条件集合</param>
/// <returns>成功返回影响行数,失败返回0</returns>
public static int Modify(long categoryID, string name, long parentID, int level, bool isLeaf)
{
SqlParameter[] pramsModify =
{
DALUtil.MakeInParam("@categoryID", SqlDbType.Int, 4, categoryID),
DALUtil.MakeInParam("@name", SqlDbType.NVarChar, 100, name),
DALUtil.MakeInParam("@parentID", SqlDbType.Int, 4, parentID),
DALUtil.MakeInParam("@level", SqlDbType.Int, 4, level),
DALUtil.MakeInParam("@isLeaf", SqlDbType.Int, 4, (isLeaf == true?1:0)),
};
SqlParameter[] pramsWhere =
{
DALUtil.MakeInParam("@categoryID", SqlDbType.BigInt, 4, categoryID)
};
return(DBCommon.DBHelper.Modify(ConnString, TableName, pramsModify, pramsWhere));
}
//检测连接是否打开
public bool chkConnection()
{
try
{
if (this.con.State == ConnectionState.Closed)
{
con.Open();
DALUtil.Output(this, "连接成功");
return(true);
}
else
{
return(true);
}
}
catch (Exception e)
{
DALUtil.Output(this, " " + e.Message);
MessageBox.Show("数据库连接失败，请检查该服务相关配置！\n 1.数据库登录名和密码是否正确;\n 2.数据库服务是否配置", "提示", MessageBoxButtons.RetryCancel, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button2);
}
return(false);
}
/// <summary>
/// 由账号密码查询管理员信息，验证登陆
/// </summary>
/// <param name="a">AdminInfo实体</param>
/// <returns>查询到的用户信息</returns>
public AdminInfo SelectAdminInfo(AdminInfo admininfo)
{
bool isConn = db.chkConnection();
if (isConn)
{
string sqlGetAdmin = @"select * from Users where Username = '******' and PassWord = '******'";
DataSet ds = db.ExcuteQuery(sqlGetAdmin);
if (ds.Tables[0].Rows.Count > 0)
{
DALUtil.Output(this, ds.Tables[0].Rows[0][0].ToString());
admininfo.Uid = int.Parse(ds.Tables[0].Rows[0][0].ToString());
return(admininfo);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
//#region 修改
///// <summary>
///// 修改
///// </summary>
///// <param name="pramsModify">修改参数集合</param>
///// <param name="pramsWhere">条件集合</param>
///// <returns>成功返回影响行数,失败返回0</returns>
//public static int Modify(long categoryID, string name, long parentID, int level, bool isLeaf)
//{
// SqlParameter[] pramsModify =
// {
// DALUtil.MakeInParam("@categoryID",SqlDbType.Int,4,categoryID),
// DALUtil.MakeInParam("@name",SqlDbType.NVarChar,100,name),
// DALUtil.MakeInParam("@parentID",SqlDbType.Int,4,parentID),
// DALUtil.MakeInParam("@level",SqlDbType.Int,4,level),
// DALUtil.MakeInParam("@isLeaf",SqlDbType.Int,4,(isLeaf==true?1:0)),
// };
// SqlParameter[] pramsWhere =
// {
// DALUtil.MakeInParam("@categoryID",SqlDbType.BigInt,4,categoryID)
// };
// return DBCommon.DBHelper.Modify(ConnString, TableName, pramsModify, pramsWhere);
//}
///// <summary>
///// 修改
///// </summary>
///// <param name="pramsModify">修改参数集合</param>
///// <param name="id">ID</param>
///// <returns>成功返回影响行数,失败返回0</returns>
//public static int Modify(SqlParameter[] pramsModify, int id)
//{
// SqlParameter[] pramsWhere =
// {
// DALUtil.MakeInParam("@ID",SqlDbType.Int,4,id)
// };
// return DBCommon.DBHelper.Modify(ConnString, TableName, pramsModify, pramsWhere);
//}
//#endregion
#region 获取一个数据
/// <summary>
/// 获取一个数据[判断是否存在,获取最大值]
/// 条件 :a=1
/// </summary>
/// <param name="SelectIF">查询数据</param>
/// <param name="sqlWhere">条件 a=1</param>
/// <param name="values">值</param>
/// <returns>返回数据</returns>
public static int GetSingle(string SelectIF, string sqlWhere, params object[] values)
{
object obj = DBCommon.DBHelper.GetSingle(ConnString, TableName, SelectIF, sqlWhere, values);
return(DALUtil.ConvertToInt32(obj));
}
/// <summary>
/// 获取集合
/// </summary>
/// <param name="SelectIF">需要查询的字段</param>
/// <param name="pramsWhere">条件集合</param>
/// <param name="OrderName">排序无需带Order by</param>
public static List <AttributeValueInfoEntity> GetList(string SelectIF, SqlParameter[] pramsWhere, string OrderName = "")
{
DataTable dt = DBCommon.DBHelper.GetDataTable2(ConnString, TableName, SelectIF, pramsWhere, OrderName);
return(DALUtil.ConvertDataTableToEntityList <AttributeValueInfoEntity>(dt));
}
/// <summary>
/// 分页获取数据
///条件如：a=1 and b=2
/// </summary>
/// <param name="PageIndex">页码</param>
/// <param name="PageSize">数量</param>
/// <param name="SelectIF">查询字段</param>
/// <param name="OrderName">排序值</param>
/// <param name="strWhere">条件(a=1 and b=2)</param>
/// <param name="values">条件对应值</param>
public static List <AttributeValueInfoEntity> GetPageList(int PageIndex, int PageSize, string SelectIF, string OrderName, string strWhere, List <object> valuesList)
{
DataTable dt = DBCommon.DBHelper.GetDataTablePage(ConnString, TableName, SelectIF, PageIndex, PageSize, OrderName, strWhere, valuesList.ToArray());
return(DALUtil.ConvertDataTableToEntityList <AttributeValueInfoEntity>(dt));
}
/// <summary>
/// 分页获取数据
///条件如：a=1 and b=2
/// </summary>
/// <param name="PageIndex">页码</param>
/// <param name="PageSize">数量</param>
/// <param name="SelectIF">查询字段</param>
/// <param name="OrderName">排序值</param>
/// <param name="strWhere">条件(a=1 and b=2)</param>
/// <param name="values">条件对应值</param>
public static List <CategoryInfoEntity> GetPageList(int PageIndex, int PageSize, string SelectIF, string OrderName, string strWhere, params object[] values)
{
DataTable dt = DBCommon.DBHelper.GetDataTablePage(ConnString, TableName, SelectIF, PageIndex, PageSize, OrderName, strWhere, values);
return(DALUtil.ConvertDataTableToEntityList <CategoryInfoEntity>(dt));
}
/// <summary>
/// 查询一条数据
/// </summary>
/// <param name="SelectIF">需要查询的字段</param>
/// <param name="pramsWhere">条件集合</param>
public static CategoryInfoEntity Get1(string SelectIF, SqlParameter[] pramsWhere, string OrderName = "")
{
DataTable dt = DBCommon.DBHelper.GetDataTable1(ConnString, TableName, SelectIF, pramsWhere, OrderName);
return(DALUtil.ConvertDataTableToEntity <CategoryInfoEntity>(dt));
}