我如何重构这两种方法
问题描述:
您好,我有两个方法,如和三个以上的方法(在这个问题上没有提及)..我如何重构这两种方法
我如何重构这些的..
方法1:
public DataTable GetVisits(System.DateTime startdate , System.DateTime enddate)
{
const string sql = @"SELECT CONCAT(UPPER(SUBSTRING(visit_Status, 1, 1)), SUBSTRING(visit_Status FROM 2)) as Status, COUNT('x') AS Visits
FROM visits
WHERE visit_Date BETWEEN @startdate AND @enddate
GROUP BY visit_Status";
var tblvisits = new DataTable();
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(connectionstring))
{
conn.Open();
var cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
var ds = new DataSet();
var parameter = new MySql.Data.MySqlClient.MySqlParameter("@startdate", MySql.Data.MySqlClient.MySqlDbType.DateTime);
parameter.Direction = ParameterDirection.Input;
parameter.Value = startdate.ToString(dateformat);
cmd.Parameters.Add(parameter);
var parameter2 = new MySql.Data.MySqlClient.MySqlParameter("@enddate", MySql.Data.MySqlClient.MySqlDbType.DateTime);
parameter2.Direction = ParameterDirection.Input;
parameter2.Value = enddate.ToString(dateformat);
cmd.Parameters.Add(parameter2);
var da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
da.Fill(ds);
try
{
tblvisits = ds.Tables[0];
}
catch
{
tblvisits = null;
}
}
return tblvisits;
}
方法2::
public DataTable sales(System.DateTime startdate, System.DateTime enddate)
{
const string sql = @"SELECT memberAccTran_Source as Category, sum(memberAccTran_Value) as Value
FROM memberacctrans
WHERE memberAccTran_DateTime BETWEEN @startdate AND @enddate
GROUP BY memberAccTran_Source";
var memacctvalues = new DataTable();
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(connectionstring))
{
conn.Open();
var cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
var ds = new DataSet();
var parameter = new MySql.Data.MySqlClient.MySqlParameter("@startdate", MySql.Data.MySqlClient.MySqlDbType.DateTime);
parameter.Direction = ParameterDirection.Input;
parameter.Value = startdate.ToString(dateformat);
cmd.Parameters.Add(parameter);
var parameter2 = new MySql.Data.MySqlClient.MySqlParameter("@enddate", MySql.Data.MySqlClient.MySqlDbType.DateTime);
parameter2.Direction = ParameterDirection.Input;
parameter2.Value = enddate.ToString(dateformat);
cmd.Parameters.Add(parameter2);
var da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
da.Fill(ds);
try
{
memacctvalues = ds.Tables[0];
}
catch
{
memacctvalues = null;
}
}
return memacctvalues;
}
会不会有人有任何想法我怎么可以重构这两种方法
(
我有三个方法更喜欢这一点,但唯一不同的是SQL查询 )
答
如果方法的所有风格之间的唯一区别是SQL代码,那么为每个方法创建一个公共方法,并让它们全部调用一个私有方法,该私有方法具有通信在代码中传入正确的sql字符串。
private DataTable PerformQuery(string sql, System.DateTime startdate, System.DateTime enddate)
{
var tblvisits = new DataTable();
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(connectionstring))
{
conn.Open();
var cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
var ds = new DataSet();
var parameter = new MySql.Data.MySqlClient.MySqlParameter("@startdate", MySql.Data.MySqlClient.MySqlDbType.DateTime);
parameter.Direction = ParameterDirection.Input;
parameter.Value = startdate.ToString(dateformat);
cmd.Parameters.Add(parameter);
var parameter2 = new MySql.Data.MySqlClient.MySqlParameter("@enddate", MySql.Data.MySqlClient.MySqlDbType.DateTime);
parameter2.Direction = ParameterDirection.Input;
parameter2.Value = enddate.ToString(dateformat);
cmd.Parameters.Add(parameter2);
var da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
da.Fill(ds);
try
{
tblvisits = ds.Tables[0];
}
catch
{
tblvisits = null;
}
}
return tblvisits;
}
然后对公立:
public DataTable Sales(System.DateTime startdate, System.DateTime enddate)
{
const string sql = @"SELECT memberAccTran_Source as Category, sum(memberAccTran_Value) as Value
FROM memberacctrans
WHERE memberAccTran_DateTime BETWEEN @startdate AND @enddate
GROUP BY memberAccTran_Source";
return PerformQuery(sql, startdate, enddate);
}
public DataTable GetVisits(System.DateTime startdate , System.DateTime enddate)
{
const string sql = @"SELECT CONCAT(UPPER(SUBSTRING(visit_Status, 1, 1)), SUBSTRING(visit_Status FROM 2)) as Status, COUNT('x') AS Visits
FROM visits
WHERE visit_Date BETWEEN @startdate AND @enddate
GROUP BY visit_Status";
return PerformQuery(sql, startdate, enddate);
}
,并重复每个人的。正如您似乎表明的那样,假设所有参数都是相同的,唯一的区别是正在运行的SQL。
+0
非常感谢詹姆斯..... –
+0
没问题!乐意效劳。 –
答
只需将SQL查询放在方法外并将其作为参数传递。
你觉得你需要重构它们的原因是什么?你想达到什么。 – Kolja