public IEnumerable <Product> GetAllProducts()
{
string sqlExecute = string.Empty;
List <Product> ProductList = new List <Product>();
try
{
#region SQL 語法
sqlExecute = @"select * from Product ";
#endregion SQL 語法
#region SQL 執行
using (var conn = SQLFunc.OpenConnection(DBList.Test))
{
ProductList = conn.Query <Product>(sqlExecute).ToList();
}
var builder = new ODataConventionModelBuilder()
{
Namespace = "Default"
};
builder.EntitySet <Product>("Products");
var model = builder.GetEdmModel();
return(ProductList.AsQueryable());
#endregion
}
catch (Exception ex)
{
}
return(ProductList.AsQueryable());
}
// GET: api/TestTour/5
public Tour Get(string ToursType, string Country = "TW", int?LineLevel = 0, string BuType = "T", int ShowType = 1, string LineID = "", string TravelID = "", string WebAreaID = "", int PageStart = 0, int PageCount = 0)
{
string strLine = string.Empty;
string strwhere = string.Empty;
if (ToursType == TT.NEW.ToString())
{
strwhere = "and hspd10_line <> 9";
}
if (ToursType == TT.NEWDOM.ToString())
{
strwhere = "and hspd10_line=9";
}
DynamicParameters sqlParm = new DynamicParameters();
sqlParm.Add("@Country", Country);
sqlParm.Add("@BuType", BuType);
string sqlQuery = @"select hspd10_line AS LineID,hspd10_line_nm AS LineName,hspd10_travel AS TravelID,hspd10_travel_nm AS TravelName,hspd10_webarea AS WebAreaID, hspd10_warea_nm AS WebAreaName from [dbo].[hspdm10] where hspd10_bu=@BuType and hspd10_country=@Country {0} ";
using (var conn = SQLFunc.OpenConnection(DBList.LionAction))
{
sqlQuery = string.Format(sqlQuery, strwhere);
return(conn.Query <Tour>(sqlQuery, sqlParm).FirstOrDefault());
}
}
//用int 參數 串入 where in(SqlCommand 方式用)
public string GetCommand(int d)
{
CommittableTransaction ct = new CommittableTransaction();
string outString = string.Empty;
try
{
SqlCommand cmd = new SqlCommand();
//加入 System.Transactions.dll, v4.0.0.0 參考
string strQuery = string.Empty;
string strIn = string.Empty;
#region SQL 執行
using (var con = SQLFunc.OpenConnection(DBList.LionExAPIDB))
{
string dd = "1,2,3";
con.EnlistTransaction(ct);
cmd.Connection = con;
List <string> list = new List <string>();
int intN = 0;
foreach (string item in dd.Split(','))
{
//SqlParameter parameter = new SqlParameter("@dd" + intN, SqlDbType.Int, 50, item);
//parameter.IsNullable = true;
//parameter.Direction = ParameterDirection.Output;
//cmd.Parameters.Add("@dd" + intN, System.Data.DbType.Int32).Value = item;
strIn += "@dd" + intN;
cmd.Parameters.Add(strIn, SqlDbType.Int).Value = item;
list.Add(item);
intN++;
}
strQuery = string.Format("select createname from [dbo].[Application] where id in ({0});", string.Join(",", list.ToArray()));
cmd.CommandText = strQuery;
var Data = cmd.ExecuteReader();
while ((Data.Read()))
{
//5.判斷資料列是否為空
if (!Data[0].Equals(DBNull.Value))
{
outString = Data[0].ToString();
}
}
}
#endregion
ct.Commit();
}
catch (Exception e)
{ ct.Rollback(); }
return(outString);
}