public static SqlParameter TransProperty2Param(PropertyVal p)
{
SqlParameter param = new SqlParameter();
param = SafeSQL.CreateInputParam(p.pName, GetDbType(p.pType), p.pValue);
return(param);
}
public DataTable GetShopCollectDt(ShopCollectDailyFilter shopCollectDailyFilter)
{
SqlCommand cmd = new SqlCommand();
string strSql = @"SELECT
SCD03 AS '日期',
SCD04 AS '店取短名',
SCD05 AS '郵遞區號',
SCD06 AS '地址1',
SCD07 AS '地址2',
SCD08 AS '店家名稱',
SCD09 AS '包裹放置天數',
SCD10 AS '店取類型',
SCD13 AS '國家'
FROM ShopCollectDaily
WHERE SCD12=1 AND SCD03 BETWEEN @ST AND @ET AND SCD13=@CODE ";
if (!string.IsNullOrEmpty(shopCollectDailyFilter.ShortName))
{
strSql += " AND SCD04=@SCD04";
cmd.Parameters.Add(SafeSQL.CreateInputParam("SCD04", SqlDbType.VarChar, shopCollectDailyFilter.ShortName));
}
if (!string.IsNullOrEmpty(shopCollectDailyFilter.PostCode))
{
strSql += " AND SCD05=@SCD05";
cmd.Parameters.Add(SafeSQL.CreateInputParam("SCD05", SqlDbType.VarChar, shopCollectDailyFilter.PostCode));
}
cmd.CommandText = strSql;
cmd.Parameters.Add(SafeSQL.CreateInputParam("ST", SqlDbType.Date, shopCollectDailyFilter.STime.ToString("yyyy-MM-dd")));
cmd.Parameters.Add(SafeSQL.CreateInputParam("ET", SqlDbType.Date, shopCollectDailyFilter.ETime.ToString("yyyy-MM-dd")));
cmd.Parameters.Add(SafeSQL.CreateInputParam("CODE", SqlDbType.VarChar, shopCollectDailyFilter.Country));
return(SqlDbmanager.queryBySql(cmd, System.Web.Configuration.WebConfigurationManager.ConnectionStrings["NormalConn"].ConnectionString));
}
/// <summary>
/// Excute主從表
/// </summary>
/// <param name="mcmd">Master Cmd</param>
/// <param name="ccmdList">Detail Cmd (主資料表ID SET=@mid)</param>
/// <param name="conStr">連線字串</param>
/// <returns></returns>
public static int ExcuteNonQryMasterDetail(SqlCommand mcmd, List <SqlCommand> ccmdList, String conStr = "")
{
if (conStr.Equals(""))
{
conStr = DefaultConStr;
}
int rval = -1;
using (SqlConnection con = new SqlConnection(conStr))
{
CommittableTransaction ct = new CommittableTransaction();
con.Open();
con.EnlistTransaction(ct);
try
{
mcmd.CommandText = mcmd.CommandText + ";SELECT SCOPE_IDENTITY()";
mcmd.Connection = con;
mcmd.CommandTimeout = 120;
rval = Convert.ToInt32(mcmd.ExecuteScalar());
foreach (SqlCommand cmd in ccmdList)
{
try
{
//cmd.CommandText = cmd.CommandText.Replace("@mid", rval.ToString());
cmd.Parameters.Add(SafeSQL.CreateInputParam("@mid", SqlDbType.Int, rval));
cmd.Connection = con;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
//addErrorLog(cmd, ex.ToString());
ct.Rollback();
rval = -1;
return(rval);
}
}
ct.Commit();
}
finally
{
mcmd.Dispose();
con.Close();
}
}
return(rval);
}