public DataTable getUserInfo(string[] searchParameter)
{
// { account, processID, userName };
string deptID = searchParameter[1];
MyCatParameter[] cp = new MyCatParameter[3];
MySqlParameter[] mp = new MySqlParameter[3];
if (deptID == "-1")
{
deptID = "";
}
if (MiddleWare == "1")
{
cp[0] = new MyCatParameter("account", searchParameter[0]);
cp[1] = new MyCatParameter("deptID", deptID);
cp[2] = new MyCatParameter("userName", searchParameter[2]);
}
else
{
mp[0] = new MySqlParameter("account", searchParameter[0]);
mp[1] = new MySqlParameter("deptID", deptID);
mp[2] = new MySqlParameter("userName", searchParameter[2]);
};
// account ,processID, userName
string sql = @"SELECT
ID,
account,
userName,
deptID,
marsk
FROM
mesusers
WHERE 1 = 1 and
if(@account ='',0=0, account = @account ) and
if(@deptID ='',0=0, deptID = @deptID ) and
if(@userName ='',0=0, userName = @userName )
ORDER BY
Id;" ;
DataTable users = new DataTable();
if (MiddleWare == "1")
{
users = MyCatfsg_SqlHelper.ExcuteTable(sql, cp);
}
else
{
users = Mysqlfsg_SqlHelper.ExcuteTable(sql, mp);
};
return(users);
}
/// <summary>
/// if mysql return MySqlParameter[] pars
/// if sqlerver return SqlParameter[] pars ...
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public override IDataParameter[] ToIDbDataParameter(params SugarParameter[] parameters)
{
if (parameters == null || parameters.Length == 0)
{
return(null);
}
MyCatParameter[] result = new MyCatParameter[parameters.Length];
int index = 0;
foreach (var parameter in parameters)
{
if (parameter.Value == null)
{
parameter.Value = DBNull.Value;
}
var sqlParameter = new MyCatParameter();
sqlParameter.ParameterName = parameter.ParameterName;
sqlParameter.Size = parameter.Size;
sqlParameter.Value = parameter.Value;
sqlParameter.DbType = parameter.DbType;
sqlParameter.Direction = parameter.Direction;
if (sqlParameter.Direction == 0)
{
sqlParameter.Direction = ParameterDirection.Input;
}
result[index] = sqlParameter;
if (sqlParameter.Direction.IsIn(ParameterDirection.Output, ParameterDirection.InputOutput, ParameterDirection.ReturnValue))
{
if (this.OutputParameters == null)
{
this.OutputParameters = new List <IDataParameter>();
}
this.OutputParameters.RemoveAll(it => it.ParameterName == sqlParameter.ParameterName);
this.OutputParameters.Add(sqlParameter);
}
++index;
}
return(result);
}
public DataTable getDelByS(string starDate, string stopDate, string cust_id, string tagNumber, string org, int pageRows, int pages)
{
MyCatParameter[] mc = new MyCatParameter[7];
MySqlParameter[] ms = new MySqlParameter[7];
if (MiddleWare == "1")
{
mc[0] = new MyCatParameter("starDate", starDate);
mc[1] = new MyCatParameter("stopDate", stopDate);
mc[2] = new MyCatParameter("cust_id", cust_id);
mc[3] = new MyCatParameter("tagNumber", tagNumber);
mc[4] = new MyCatParameter("org", org);
mc[5] = new MyCatParameter("pageRows", pageRows);
mc[6] = new MyCatParameter("pages", pages);
}
else
{
ms[0] = new MySqlParameter("starDate", starDate);
ms[1] = new MySqlParameter("stopDate", stopDate);
ms[2] = new MySqlParameter("cust_id", cust_id);
ms[3] = new MySqlParameter("tagNumber", tagNumber);
ms[4] = new MySqlParameter("org", org);
ms[5] = new MySqlParameter("pageRows", pageRows);
ms[6] = new MySqlParameter("pages", pages);
};
// account ,processID, userName
string sql = @"SELECT
id,
TagNumber,
Cust_id,
Location,
org,
scantime,
con_no,
create_pc
FROM
inv
WHERE 1 = 1 and
IF ( @org != '', org = @org , 1 = 1 ) AND
IF ( @starDate != '', scantime BETWEEN @starDate AND @stopDate , 1 = 1 ) AND
IF (@cust_id != '', cust_id = @cust_id , 1 = 1 ) AND
IF (@tagNumber != '', tagNumber = @tagNumber , 1 = 1 )
ORDER BY
scantime,
tagnumber,
cust_id,
id
LIMIT " + (pages - 1) * pageRows + @",
" + pageRows + ";";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql, mc);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql, ms);
};
return(dt);
}
public override async Task <DbDataReader> ExecuteReaderAsync(string commandText, MyCatParameterCollection parameterCollection,
CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken)
{
var cachedProcedure = await m_command.Connection.GetCachedProcedure(ioBehavior, commandText, cancellationToken).ConfigureAwait(false);
if (cachedProcedure != null)
{
parameterCollection = cachedProcedure.AlignParamsWithDb(parameterCollection);
}
MyCatParameter returnParam = null;
m_outParams = new MyCatParameterCollection();
m_outParamNames = new List <string>();
var inParams = new MyCatParameterCollection();
var argParamNames = new List <string>();
var inOutSetParams = "";
for (var i = 0; i < parameterCollection.Count; i++)
{
var param = parameterCollection[i];
var inName = "@inParam" + i;
var outName = "@outParam" + i;
switch (param.Direction)
{
case 0:
case ParameterDirection.Input:
case ParameterDirection.InputOutput:
var inParam = param.WithParameterName(inName);
inParams.Add(inParam);
if (param.Direction == ParameterDirection.InputOutput)
{
inOutSetParams += $"SET {outName}={inName}; ";
goto case ParameterDirection.Output;
}
argParamNames.Add(inName);
break;
case ParameterDirection.Output:
m_outParams.Add(param);
m_outParamNames.Add(outName);
argParamNames.Add(outName);
break;
case ParameterDirection.ReturnValue:
returnParam = param;
break;
}
}
// if a return param is set, assume it is a funciton. otherwise, assume stored procedure
commandText += "(" + string.Join(", ", argParamNames) + ")";
if (returnParam == null)
{
commandText = inOutSetParams + "CALL " + commandText;
if (m_outParams.Count > 0)
{
m_setParamsFlags = true;
m_cancellationToken = cancellationToken;
}
}
else
{
commandText = "SELECT " + commandText;
}
var reader = (MyCatDataReader)await base.ExecuteReaderAsync(commandText, inParams, behavior, ioBehavior, cancellationToken).ConfigureAwait(false);
if (returnParam != null && await reader.ReadAsync(ioBehavior, cancellationToken).ConfigureAwait(false))
{
returnParam.Value = reader.GetValue(0);
}
return(reader);
}
public void NormalizeParameterName(string input, string expectedName)
{
var normalized = MyCatParameter.NormalizeParameterName(input);
Assert.Equal(expectedName, normalized);
}
