public static void ExecuteNonQuery(string commandText, MySqlParameter[] commandParameters = null, CommandType commandType = CommandType.Text)
{
try
{
if (commandParameters == null)
{
commandParameters = new MySqlParameter[] { new MySqlParameter() }
}
;
using (var connection = new MySqlConnection(connectionString))
using (var command = new MySqlCommand(commandText, connection))
{
command.CommandType = commandType;
command.Parameters.AddRange(commandParameters.ToArray());
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception ex)
{
applogManager.Error(ex);
throw ex;
}
}
//
// GET: /Home/
public ActionResult Index()
{
eftestEntities ctx = new eftestEntities();
// var students= ctx.Database.SqlQuery<student>("select * from student",new object()).ToList();
//students.FirstOrDefault().Age = 89;
//ctx.SaveChanges();
//ctx.student.Where(x=>x.Name==EntityFunctions.AsNonUnicode())
//var stus = ctx.Set<student>().SqlQuery("select * from student", new object()).ToList();
//stus.FirstOrDefault().Age = 89;
//ctx.SaveChanges();
var sql = "select Age from Student where Name = @Name and Age = @Age";
//var tst= ctx.Database.SqlQuery<int>(
// sql,
// new MySqlParameter("@Name", "fxp"),
// new MySqlParameter("@Age", 89)).ToList();
MySqlParameter[] parameters = new MySqlParameter[]
{
new MySqlParameter()
{
Direction = ParameterDirection.Input,
DbType = DbType.Int32,
Value = 1,
ParameterName = "@id"
},
new MySqlParameter()
{
// Direction = ParameterDirection.Output,
DbType = DbType.Int32,
ParameterName = "@cnt"
}
};
//var ttttt= ((IObjectContextAdapter) ctx).ObjectContext.ExecuteStoreQuery<student>("call getage(@id,@cnt)",
// parameters.ToArray()).ToList();
// var str= ctx.Database.SqlQuery<student>("call getage(@id,@cnt)", parameters.ToArray()).ToList();
var list =
ctx.ExecuteStoredProcedureList <student>("call getage(@id)", parameters.ToArray()).ToList();
return(View());
}
/// <summary>
/// 根据产品父类别，查询对应的产品数量
/// </summary>
/// <param name="classid"></param>
/// <returns></returns>
public int CetProductBySupClassid(int supclassid)
{
string sql = " SELECT count(productid) as totalCount FROM product WHERE isDelete=0 and supclassid=?supclassid";
MySqlParameter[] parameterList = new MySqlParameter[1];
parameterList[0] = new MySqlParameter("?supclassid", MySqlDbType.Int32);
parameterList[0].Value = supclassid;
using (MySqlDataReader sqlDataReader = PKMySqlHelper.ExecuteReader(sql, parameterList.ToArray()))
{
if (sqlDataReader != null)
{
if (sqlDataReader.Read())
{
return((sqlDataReader["totalCount"] != DBNull.Value) ? Convert.ToInt32(sqlDataReader["totalCount"].ToString()) : 0);
}
}
}
return(0);
}
/// <summary>
/// 输入店铺名称或者手机号模糊查询店铺
/// </summary>
/// <param name="name"></param>
/// <param name="aid"></param>
/// <returns></returns>
public List <PlatStore> GetListByNameOrPhone(string name, int aid)
{
List <PlatStore> list = new List <PlatStore>();
MySqlParameter[] parms = new MySqlParameter[] {
new MySqlParameter("@name", $"%{name}%"),
new MySqlParameter("@phone", $"%{name}%")
};
string sql = $"select s.*,c.userid from platstore s left join platmycard c on s.MyCardId=c.id where s.bindplataid={aid} and (s.Name like @name or c.Phone like @phone)";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, parms.ToArray()))
{
while (dr.Read())
{
PlatStore model = base.GetModel(dr);
if (dr["userid"] != DBNull.Value)
{
model.UserId = Convert.ToInt32(dr["userid"]);
}
list.Add(model);
}
}
return(list);
}