public List<Lotto> CheckLotto(bool incBonusNumber, params int[] numbers)
{
List<Lotto> resultSet = new List<Lotto>();
string inCondition = String.Empty; // in 조건에 사용될 컬럼이름
inCondition = "num1 , num2, num3, num4, num5, num6";
if (incBonusNumber)
{
inCondition += ", numBonus";
}
ParameterCollection parameters = new ParameterCollection();
string select = @"select * from lotto where 1 = 1";
for (int i = 0; i < numbers.Length; i++)
{
select += String.Format(" and @num{0} in ({1})", i + 1, inCondition);
parameters.Add(String.Format("@num{0}", i + 1), numbers[i]);
}
select += " order by Id desc";
DataService dao = new DataService();
dao.SetConnectionString(this.GetConnectionString());
dao.AddRequestService(CommandType.Text, select, parameters, ExecuteType.ExecuteQuery);
List<ResponseService> responses = dao.Execute();
if (dao.HasError)
{
Logger.Error(dao.GetType(), dao.ErrorMessage);
DebugHelper.WriteLine(dao.ErrorMessage);
}
else
{
if (responses[0].DataSet.Tables.Count == 0)
{
DebugHelper.WriteLine("Table is not exists.");
}
else
{
resultSet = responses[0].DataSet.Tables[0].Select().Select(r => new Lotto()
{
Id = Convert.ToInt32(r["Id"]),
Dt = String.Format("{0}", r["Dt"]),
Num1 = Convert.ToInt32(r["Num1"]),
Num2 = Convert.ToInt32(r["Num2"]),
Num3 = Convert.ToInt32(r["Num3"]),
Num4 = Convert.ToInt32(r["Num4"]),
Num5 = Convert.ToInt32(r["Num5"]),
Num6 = Convert.ToInt32(r["Num6"]),
NumBonus = Convert.ToInt32(r["NumBonus"]),
}).ToList();
}
}
return resultSet;
}
public bool TestTable()
{
DataService dao = new DataService();
dao.SetConnectionString(this.GetConnectionString());
dao.AddRequestService(CommandType.Text, "select * from lotto", new ParameterCollection(), ExecuteType.ExecuteQuery);
List<ResponseService> responses = dao.Execute();
if (dao.HasError)
{
Logger.Error(dao.GetType(), dao.ErrorMessage);
DebugHelper.WriteLine(dao.ErrorMessage);
return false;
}
else
{
if (responses[0].DataSet.Tables.Count == 0)
{
DebugHelper.WriteLine("Table is not exists.");
}
return responses[0].DataSet.Tables.Count > 0;
}
}
public bool Save(Lotto item)
{
string save = "";
if (this.GetData(item.Id, String.Empty, String.Empty).Count > 0)
{
// update
save += "update lotto ";
save += " set Dt = @dt";
save += " , Num1 = @num1";
save += " , Num2 = @num2";
save += " , Num3 = @num3";
save += " , Num4 = @num4";
save += " , Num5 = @num5";
save += " , Num6 = @num6";
save += " , NumBonus = @numBonus";
save += " where Id = @id";
}
else
{
// insert
save += "insert into lotto (";
save += " Id";
save += " , Dt";
save += " , Num1";
save += " , Num2";
save += " , Num3";
save += " , Num4";
save += " , Num5";
save += " , Num6";
save += " , NumBonus";
save += " ) ";
save += " values ";
save += " ( @id";
save += " , @dt";
save += " , @num1";
save += " , @num2";
save += " , @num3";
save += " , @num4";
save += " , @num5";
save += " , @num6";
save += " , @numBonus";
save += " )";
}
ParameterCollection parameters = new ParameterCollection();
parameters.Add("@id", item.Id);
parameters.Add("@dt", item.Dt);
parameters.Add("@num1", item.Num1);
parameters.Add("@num2", item.Num2);
parameters.Add("@num3", item.Num3);
parameters.Add("@num4", item.Num4);
parameters.Add("@num5", item.Num5);
parameters.Add("@num6", item.Num6);
parameters.Add("@numBonus", item.NumBonus);
DataService dao = new DataService();
dao.SetConnectionString(this.GetConnectionString());
dao.AddRequestService(CommandType.Text, save, parameters, ExecuteType.ExecuteNonQuery);
List<ResponseService> responses = dao.Execute();
if (dao.HasError)
{
Logger.Error(dao.GetType(), dao.ErrorMessage);
DebugHelper.WriteLine(dao.ErrorMessage);
return false;
}
else
{
return true;
}
}
public List<Lotto> GetData(int? id, string startDt, string endDt)
{
List<Lotto> resultSet = new List<Lotto>();
ParameterCollection parameters = new ParameterCollection();
string select = @"select * from lotto where 1 = 1";
if (id.HasValue)
{
select += " and id = @id";
parameters.Add("@id", id.Value);
}
if (!String.IsNullOrEmpty(startDt) && !String.IsNullOrEmpty(endDt))
{
select += " and Dt between @startDt and @endDt";
parameters.Add("@startDt", startDt);
parameters.Add("@endDt", endDt);
}
select += " order by Id desc";
DataService dao = new DataService();
dao.SetConnectionString(this.GetConnectionString());
dao.AddRequestService(CommandType.Text, select, parameters, ExecuteType.ExecuteQuery);
List<ResponseService> responses = dao.Execute();
if (dao.HasError)
{
Logger.Error(dao.GetType(), dao.ErrorMessage);
DebugHelper.WriteLine(dao.ErrorMessage);
}
else
{
if (responses[0].DataSet.Tables.Count == 0)
{
DebugHelper.WriteLine("No data.");
}
else
{
resultSet = responses[0].DataSet.Tables[0].Select().Select(r => new Lotto()
{
Id = Convert.ToInt32(r["Id"]),
Dt = String.Format("{0}", r["Dt"]),
Num1 = Convert.ToInt32(r["Num1"]),
Num2 = Convert.ToInt32(r["Num2"]),
Num3 = Convert.ToInt32(r["Num3"]),
Num4 = Convert.ToInt32(r["Num4"]),
Num5 = Convert.ToInt32(r["Num5"]),
Num6 = Convert.ToInt32(r["Num6"]),
NumBonus = Convert.ToInt32(r["NumBonus"])
}).ToList();
}
}
return resultSet;
}
public bool DeleteAll()
{
string delete = "delete from lotto ";
ParameterCollection parameters = new ParameterCollection();
DataService dao = new DataService();
dao.SetConnectionString(this.GetConnectionString());
dao.AddRequestService(CommandType.Text, delete, parameters, ExecuteType.ExecuteNonQuery);
List<ResponseService> responses = dao.Execute();
if (dao.HasError)
{
Logger.Error(dao.GetType(), dao.ErrorMessage);
DebugHelper.WriteLine(dao.ErrorMessage);
return false;
}
else
{
return true;
}
}
public bool Delete(Lotto item)
{
string delete = "";
if (this.GetData(item.Id, String.Empty, String.Empty).Count > 0)
{
// update
delete += "delete from lotto ";
delete += " where Id = @id";
}
else
{
return false;
}
ParameterCollection parameters = new ParameterCollection();
parameters.Add("@id", item.Id);
//parameters.Add("@dt", item.Dt);
//parameters.Add("@num1", item.Num1);
//parameters.Add("@num1", item.Num2);
//parameters.Add("@num1", item.Num3);
//parameters.Add("@num1", item.Num4);
//parameters.Add("@num1", item.Num5);
//parameters.Add("@num1", item.Num6);
DataService dao = new DataService();
dao.SetConnectionString(this.GetConnectionString());
dao.AddRequestService(CommandType.Text, delete, parameters, ExecuteType.ExecuteNonQuery);
List<ResponseService> responses = dao.Execute();
if (dao.HasError)
{
Logger.Error(dao.GetType(), dao.ErrorMessage);
DebugHelper.WriteLine(dao.ErrorMessage);
return false;
}
else
{
return true;
}
}
public bool CreateTable()
{
string createTable = String.Empty;
createTable = "Create table Lotto ( ";
createTable += " Id int primary key not null ";
createTable += " , Dt text not null ";
createTable += " , Num1 int not null ";
createTable += " , Num2 int not null ";
createTable += " , Num3 int not null ";
createTable += " , Num4 int not null ";
createTable += " , Num5 int not null ";
createTable += " , Num6 int not null ";
createTable += " , NumBonus int not null ";
createTable += " )";
DataService dao = new DataService();
dao.SetConnectionString(this.GetConnectionString());
dao.AddRequestService(CommandType.Text, createTable, new ParameterCollection(), ExecuteType.ExecuteNonQuery);
List<ResponseService> responses = dao.Execute();
if (dao.HasError)
{
Logger.Error(dao.GetType(), dao.ErrorMessage);
DebugHelper.WriteLine(dao.ErrorMessage);
return false;
}
else
{
return true;
}
}
