//执行select查询语句
public string Query(string queryString)
{
OleDbCommand command = _conn.CreateCommand();
command.CommandText = queryString;
OleDbDataReader reader = command.ExecuteReader();
if (!reader.HasRows)
{
return(""); //一条数据都没有直接返回空字符串
}
DynDBHelper dynDb = new DynDBHelper();
while (reader.Read())
{
List <FieldUnit> row = new List <FieldUnit>();
for (int i = 0; i < reader.FieldCount; i++)
{
FieldUnit unitTemp = new FieldUnit();
unitTemp.FieldName = reader.GetName(i);
unitTemp.FieldValue = reader[i].ToString();
row.Add(unitTemp);
}
dynDb.addRow(row);
}
string json = CommonFunction.ToJson(dynDb.getDataList());
return(json);
}
public string QueryProc(string procName, string argumentJson)
{
try
{
if (procName.IndexOf("SEARCH") < 0 && procName.IndexOf("INSERT") < 0 && procName.IndexOf("UPDATE") < 0)
{
throw new Exception("ERROR, QueryProc name invalid!");
}
#region 读取数据
//创建数据库命令,通过procName取出实际的proc命令
OleDbCommand command = _conn.CreateCommand();
command.CommandText = GetProcText(procName, argumentJson);
//从数据库中读取数据流存入reader中
OleDbDataReader reader = command.ExecuteReader();
//从reader中读取下一行数据,如果没有数据,reader.Read()返回flase
if (!reader.HasRows)
{
return(""); //一条数据都没有直接返回空字符串
}
DynDBHelper dynDb = new DynDBHelper();
while (reader.Read())
{
List <FieldUnit> row = new List <FieldUnit>();
for (int i = 0; i < reader.FieldCount; i++)
{
FieldUnit unitTemp = new FieldUnit();
unitTemp.FieldName = reader.GetName(i);
unitTemp.FieldValue = reader[i].ToString();
row.Add(unitTemp);
}
dynDb.addRow(row);
}
string json = CommonFunction.ToJson(dynDb.getDataList());
return(json);
#endregion
}
catch
{
throw new Exception(String.Format("ERROR, OleDB DataContext error while do QueryProc: {0}", procName));
}
finally
{
//do nothing yet
}
}
public string QueryProc(string procName, string argumentJson)
{
try
{
if (procName.IndexOf("SEARCH") < 0)
{
throw new Exception("ERROR, QueryProc name invalid!");
}
#region 读取数据
//创建数据库命令
MySqlCommand cmd = _conn.CreateCommand();
//创建查询语句
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
//组织存储过程参数,目前只支持输入型参数，输出及return稍后添加
if (argumentJson.Trim() != "")
{
List <Proc_Argument> args = CommonFunction.DeserializeJsonToList <Proc_Argument>(argumentJson);
foreach (Proc_Argument arg in args)
{
Console.Write(String.Format("name is {0}, value is {1}, direct is {2}", arg.name, arg.value, arg.direct));
MySqlParameter paramTemp = new MySqlParameter(arg.name, arg.value);
if (arg.direct == GlobalVariables.INT_SQL_PARAM_DIRECTION_INPUT)
{
paramTemp.Direction = ParameterDirection.Input;
}
else if (arg.direct == GlobalVariables.INT_SQL_PARAM_DIRECTION_OUTPUT)
{
paramTemp.Direction = ParameterDirection.Output;
}
else if (arg.direct == GlobalVariables.INT_SQL_PARAM_DIRECTION_RETURN)
{
paramTemp.Direction = ParameterDirection.ReturnValue;
}
cmd.Parameters.Add(paramTemp);
}
}
//从数据库中读取数据流存入reader中
MySqlDataReader reader = cmd.ExecuteReader();
//从reader中读取下一行数据,如果没有数据,reader.Read()返回flase
if (!reader.HasRows)
{
return(""); //一条数据都没有直接返回空字符串
}
DynDBHelper dynDb = new DynDBHelper();
while (reader.Read())
{
List <FieldUnit> row = new List <FieldUnit>();
for (int i = 0; i < reader.FieldCount; i++)
{
FieldUnit unitTemp = new FieldUnit();
unitTemp.FieldName = reader.GetName(i);
unitTemp.FieldValue = reader[i].ToString();
row.Add(unitTemp);
}
dynDb.addRow(row);
}
string json = CommonFunction.ToJson(dynDb.getDataList());
return(json);
#endregion
}
catch
{
throw new Exception(String.Format("ERROR, Mysql DataContext error while do QueryProc: {0}", procName));
}
finally
{
//do nothing yet
}
}