public string UpdateStamp(string stampCode, string inoutTranID, string inoutTranStamp)
{
string mes = "";
DataTable dt_Single_stamp = new DataTable();
DataTable dt_pkinouttran = new DataTable();
DataTable dt_stamp = new DataTable();
string sql_PkStamp = "select * from PkStamp t1 where t1.StampCode = '" + stampCode + "' and t1.SingleStampFlg = 1 ";
string sql_pkinouttranstamp = "select * from pkinouttranstamp t2 where t2.InOutTran_ID = '" + inoutTranID + "' and t2.SingleStampFlg = 1";
string sql_stamp = "select * from pkinouttranstamp t3 where t3.InOutTran_ID = '" + inoutTranID + "' and t3.StampCode = '" + stampCode + "' ";
dt_Single_stamp = db.QueryDataTable(sql_PkStamp);
dt_pkinouttran = db.QueryDataTable(sql_pkinouttranstamp);
dt_stamp = db.QueryDataTable(sql_stamp);
if (dt_Single_stamp.Rows.Count > 0 & dt_pkinouttran.Rows.Count > 0)
{
mes = "HaveSingleStamp";
return(mes);
}
if (dt_stamp.Rows.Count > 0)
{
mes = "StampDuplicate";
return(mes);
}
SqlParameterCollection parameter1 = new SqlCommand().Parameters;
parameter1.Add("@stampCode", SqlDbType.VarChar).Value = stampCode;
parameter1.Add("@inouttranID", SqlDbType.VarChar).Value = inoutTranID;
parameter1.Add("@inoutTranStampID", SqlDbType.VarChar).Value = inoutTranStamp;
db.QueryExecuteNonQueryStor("ChangeStampProsess", parameter1);
mes = "ok";
return(mes);
}
public List <MemberModel> memberlist(int cardtype, string terncode, string ternsubCode)
{
SqlParameterCollection parameter = new SqlCommand().Parameters;
DataTable dt = new DataTable();
List <MemberModel> list_member = new List <MemberModel>();
parameter.Add("@Cartype", SqlDbType.Int).Value = cardtype;
parameter.Add("@terncode", SqlDbType.NVarChar).Value = terncode;
parameter.Add("@ternSupcode", SqlDbType.NVarChar).Value = ternsubCode;
dt = db.StoreQuery("rptReportMemberWebStamp", parameter);
list_member = (from c in dt.AsEnumerable() select new MemberModel {
BADGENUMBER = c["BADGENUMBER"].ToString(),
Carbill = c["Carbill"].ToString(),
Cartype = c["Cartype"].ToString(),
Expdate = c["Expdate"].ToString(),
Firstdate = c["Firstdate"].ToString(),
Model = c["Model"].ToString(),
Model1 = c["Model1"].ToString(),
Model2 = c["Model2"].ToString(),
name1 = c["name1"].ToString(),
vehicalID = c["vehicalID"].ToString(),
vehicalID1 = c["vehicalID1"].ToString(),
vehicalID2 = c["vehicalID2"].ToString()
}).ToList();
return(list_member);
}
//cria o método para inserir registro na tabela de Itens vendas
public void InserirItens(int iVendas, int iProduto)
{
//Criar a variavel do tipo de paramentro sql
SqlParameterCollection parametros = new SqlCommand().Parameters;
//Passar os parametros para a procedure
parametros.Add(new SqlParameter("@VendaID", iVendas));
parametros.Add(new SqlParameter("@ProdutoID", iProduto));
objAcessaDados.ExecuteSQL("spInserirItens", parametros);
}
public void InserirItensOrcamento(int iOrcamento, int iProduto)
{
//Criar a variavel do tipo de paramentro sql
SqlParameterCollection parametros = new SqlCommand().Parameters;
//Passar os parametros para a procedure
parametros.Add(new SqlParameter("@OrcamentoID", iOrcamento));
parametros.Add(new SqlParameter("@ProdutoID", iProduto));
//Executa a procedure passando os parametros
//OrcamentoID e ProdutoID
objAcessaDados.ExecuteSQL("spInserirItensOrcamento", parametros);
}
public ChangeStampModel CheangeStamp(string ternCode, string ternSubCode, string inoutTrainStamp, string visitor_id, int?adminLevel)
{
ChangeStampModel model = new ChangeStampModel();
DataTable data_card_type = new DataTable();
List <stampCodeList> stampcodelist = new List <stampCodeList>();
string CardType = "";
SqlParameterCollection parameter1 = new SqlCommand().Parameters;
parameter1.Add("@inoutTranstamp", SqlDbType.NVarChar).Value = inoutTrainStamp;
var data_ChangeStamp = this.db.StoreQuery("Change_Stamp_Detail", parameter1);
string sql_cardType = "select Cartype from PkCard where CscMain_ID = '" + visitor_id + "' ";
data_card_type = db.QueryDataTable(sql_cardType);
CardType = data_card_type.Rows[0]["Cartype"].ToString();
string sql_StampCodeList = "SELECT StampCode, StampCode+' '+ZDesc as StampDese FROM [dbo].[PkStamp] where Cartype = '" + CardType + "' and TernCode = '" + ternCode + "' and TernSubCode = '" + ternSubCode + "' and admin_level_id >= " + adminLevel + " ";
var data_StampCodeList = db.QueryDataTable(sql_StampCodeList);
stampcodelist = (from c in data_StampCodeList.AsEnumerable() select new stampCodeList {
stampcode = c["StampCode"].ToString(),
stampCodeName = c["StampDese"].ToString()
}).ToList();
model.stampCodeList = stampcodelist;
model.inoutTranID = data_ChangeStamp.Rows[0]["InOutTran_ID"].ToString();
model.inoutTrainStampID = data_ChangeStamp.Rows[0]["InOutTranstamp_ID"].ToString();
model.DateTimeStamp = Convert.ToDateTime(data_ChangeStamp.Rows[0]["datetimestamp"]);
model.stampCode = data_ChangeStamp.Rows[0]["StampCode"].ToString();
model.stampCodeOld = data_ChangeStamp.Rows[0]["StampCode"].ToString();
model.VisitorId = visitor_id;
model.licensePlate = data_ChangeStamp.Rows[0]["CarID"].ToString();
return(model);
}
public DataTable ConsultarProduto(string sNome)
{
SqlParameterCollection parametros = new SqlCommand().Parameters;
parametros.Add(new SqlParameter("@Nome", sNome));
return(objAcessaDados.ConsultaDados("spConsultaProduto", parametros));
}
/// <summary>
/// 用户登录
/// </summary>
/// <param name="account"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public DataTable getUserLogin(string account, string pwd)
{
SqlParameterCollection spc = new SqlCommand().Parameters;
StringBuilder strSql = new StringBuilder("", 400);
strSql.AppendFormat($@"
SELECT MAS_ADMIN_ID,MAS_ADMIN_NAME,ISNULL(MAS_ADMIN_NICKNAME,'点读书小编') MAS_ADMIN_NICKNAME
FROM {SystemDBConfig.MAS_ADMIN_ACCOUNT}
WHERE MAS_ADMIN_USED='0' AND MAS_ADMIN_NAME = @MAS_ADMIN_NAME and IS_VALID=1 and IS_PASS=1
AND MAS_ADMIN_PWD= sys.fn_VarBinToHexStr(hashbytes('MD5', @MAS_ADMIN_PWD)) ");
spc.Add("@MAS_ADMIN_NAME", SqlDbType.VarChar, 50).Value = account;
spc.Add("@MAS_ADMIN_PWD", SqlDbType.VarChar, 34).Value = pwd;
DataTable dt = SqlDataAccess.sda.ExecSqlTableQuery(strSql.ToString(), spc);
return(dt);
}
public List <ActiveStampModel> ActiveStampList(string terncode, string tersubcode, string adminLevel)
{
DataTable dt_active_stamp = new DataTable();
List <ActiveStampModel> active_stamp_list = new List <ActiveStampModel>();
SqlParameterCollection parameter1 = new SqlCommand().Parameters;
parameter1.Add("@ternCode", SqlDbType.VarChar).Value = terncode;
parameter1.Add("@terSubCode", SqlDbType.VarChar).Value = tersubcode;
parameter1.Add("@adminLevel", SqlDbType.VarChar).Value = adminLevel;
dt_active_stamp = db.StoreQuery("ActiveStampList", parameter1);
active_stamp_list = (from c in dt_active_stamp.AsEnumerable() select new ActiveStampModel {
InoutTrainStamp = c["InOutTranstamp_ID"].ToString(),
TimeStamp = Convert.ToDateTime(c["datetimestamp"]),
license_plate = c["CarID"].ToString(),
StampCode = c["StampCode"].ToString(),
visitor_id = c["CscMain_ID"].ToString()
}).ToList();
return(active_stamp_list);
}
//用户列表
public DataSet GetUserDs(int pageIndex, int pageSize, Hashtable ht)
{
SqlParameterCollection spc = new SqlCommand().Parameters;
PagerData pd = PagerData.GetInstance();
SqlParameterCollection sp1 = new SqlCommand().Parameters;
pd.Table = @" DWZUser u ";
pd.Field = @" u.* ";
pd.Where = " WHERE 1=1 ";
pd.SearchWhere = " 1=1 ";
string sOrder = " ORDER BY u.CreateTime " + ht["Sort"];
pd.Order = sOrder;// " ORDER BY u.CreateTime DESC ";
pd.PageSize = pageSize;
pd.Conn = ConfigurationManager.ConnectionStrings["Conn_DWZ"].ToString();
pd.CurrentPageIndex = pageIndex;
if (ht != null && ht.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (string key in ht.Keys)
{
#region 搜索条件
if (key == "UserID")
{
sb.Append(" AND u.UserID = @UserID ");
spc.Add(new SqlParameter("@UserID", ht[key].ToString()));
}
else if (key == "UserType")
{
sb.Append(" AND u.UserType = @UserType ");
spc.Add(new SqlParameter("@UserType", ht[key].ToString()));
}
#endregion
}
pd.SearchWhere += sb.ToString();
}
pd.Collection = spc;
DataSet ds = pd.GetPage(pageIndex);
return(ds);
}
public DataTable ConsultarProdutoID(int ProdutoID)
{
//cria um objeto do tipo sqlparametercollection
SqlParameterCollection parametros = new SqlCommand().Parameters;
//passa o valor da variavel para o parametro do banco de dados(procedure)
parametros.Add(new SqlParameter("@ProdutoID", ProdutoID));
//Chama o método para executar a procedure no banco de dados
DataTable dtRetorno = objAcessaDados.ConsultaDados("spConsultaProdutoID", parametros);
return(dtRetorno);
}
public void DeleteStamp(string stampcode, string inoutTrainStamp)
{
try
{
DataTable dt_inoutTranId = new DataTable();
SqlParameterCollection parameter = new SqlCommand().Parameters;
string sql_check_inoutID = "select InOutTran_ID from pkinouttranstamp where InOutTranstamp_ID = '" + inoutTrainStamp + "' ";
dt_inoutTranId = db.QueryDataTable(sql_check_inoutID);
if (dt_inoutTranId.Rows.Count > 0)
{
string inoutTranId = dt_inoutTranId.Rows[0]["InOutTran_ID"].ToString();
parameter.Add("@stampcode", SqlDbType.VarChar).Value = stampcode;
parameter.Add("@inouttranstamp", SqlDbType.VarChar).Value = inoutTrainStamp;
parameter.Add("@inouttran", SqlDbType.VarChar).Value = inoutTranId;
db.QueryExecuteNonQueryStor("delete_stamp", parameter);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Executa uma procedure
/// </summary>
/// <param name="querySql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataTable ExecuteProcedure(string querySql, params SqlParameter[] parameters)
{
SqlParameterCollection sqlParameterCollection = new SqlCommand().Parameters;
if (parameters != null)
{
foreach (SqlParameter p in parameters)
{
sqlParameterCollection.Add(p);
}
}
return(ExecuteManipulation(CommandType.StoredProcedure, querySql, sqlParameterCollection));
}
public void insertStamp(string stampCode, string inout_tran, string ternCode, string ternsubCode, int?level, string custom, string admin_name, string custom_stamp)
{
SqlParameterCollection parameter = new SqlCommand().Parameters;
parameter.Add("@stampCode", SqlDbType.VarChar).Value = stampCode;
parameter.Add("@inout_tran", SqlDbType.VarChar).Value = inout_tran;
parameter.Add("@ternCode", SqlDbType.VarChar).Value = ternCode;
parameter.Add("@ternsubCode", SqlDbType.VarChar).Value = ternsubCode;
parameter.Add("@custom", SqlDbType.NVarChar).Value = custom;
parameter.Add("@admin_name", SqlDbType.NVarChar).Value = admin_name;
parameter.Add("@level", SqlDbType.Int).Value = level;
parameter.Add("@custom_stamp", SqlDbType.NVarChar).Value = custom_stamp == null ? "":custom_stamp;
this.db.StoreQuery("StampProsess", parameter);
}
public DataTable ConsultarItensComprados(int iOrcamento)
{
//Criar a variavel do tipo de paramentro sql
SqlParameterCollection parametros = new SqlCommand().Parameters;
//Criar a variavel DataTable
DataTable dtRetorno = new DataTable();
// Passar os parametros para a procedure
parametros.Add(new SqlParameter("@OrcamentoID", iOrcamento));
//Executa a procedure passando os parametros
//OrcamentoID
dtRetorno = objAcessaDados.ConsultaDados("spConsultarItensComprados", parametros);
return(dtRetorno);
}
protected DataTable BindSSZ()
{
Hashtable ht = new Hashtable();
ht.Add("CategoryID", 1);
PagerData pd = PagerData.GetInstance();
SqlParameterCollection spc = new SqlCommand().Parameters;
pd.Conn = AppConfig.Conn_O2O2;
pd.Field = @" SysNo,PropertyName ";
pd.Table = @" dbo.P_Property ";
pd.Where = " WHERE 1=1 And Status = '0'";
pd.SearchWhere = " 1=1 ";
pd.Order = " ORDER BY SysNo ASC ";
pd.PageSize = PageSize;
pd.CurrentPageIndex = PageIndex;
if (ht != null && ht.Count > 1)
{
StringBuilder sb = new StringBuilder();
foreach (string key in ht.Keys)
{
#region 搜索条件
if (key == "PropertyName")
{
//sb.Append(" and PropertyName ").Append(" LIKE ").Append(Util.ToSqlLikeString(ht[key].ToString()));
sb.Append(" and PropertyName LIKE '%'+@PropertyName+'%' ");
spc.Add(new SqlParameter("@PropertyName", ht[key].ToString()));
}
#endregion
}
pd.SearchWhere += sb.ToString();
}
pd.Collection = spc;
DataSet ds = pd.GetPage(PageIndex);
//下面是商品类别模版存在的所有组
string sSql = "SELECT DISTINCT a.SysNo,a.GroupDesc FROM dbo.P_Property_Group AS a INNER JOIN dbo.P_CategoryTemplateProperty AS b ON a.SysNo = b.GroupSysNo WHERE b.C3SysNo = @CategoryID ORDER BY a.SysNo ASC";
SqlParameter[] spp =
{
new SqlParameter("@CategoryID", ht["CategoryID"].ToString()),
};
DataSet dsF = SqlHelper.ExecuteDataSet(AppConfig.Conn_O2O2, sSql, spp);
DataTable dts = new DataTable("ddd");
dts = dsF.Tables[0];
dts.TableName = "ddd";
dsF.Tables.Clear();
ds.Tables.Add(dts);
return(ds.Tables[2]);
}
public List <stampCodeList> StampCodeList(string visitorId, string terncode, string ternsubcode, string adminLevel)
{
List <stampCodeList> list = new List <stampCodeList>();
DataTable dt = new DataTable();
SqlParameterCollection parameter = new SqlCommand().Parameters;
parameter.Add("@visitorId", SqlDbType.VarChar).Value = visitorId;
parameter.Add("@ternCode", SqlDbType.VarChar).Value = terncode;
parameter.Add("@terSubCode", SqlDbType.VarChar).Value = ternsubcode;
parameter.Add("@adminLevel_Id", SqlDbType.VarChar).Value = adminLevel;
dt = this.db.StoreQuery("PkStampCodeList", parameter);
list = (from c in dt.AsEnumerable() select new stampCodeList {
stampcode = c["StampCode"].ToString(),
stampCodeName = c["StampCode"].ToString() + " " + c["ZDesc"].ToString()
}).ToList();
return(list);
}
public void InseriProduto(string sNome, decimal dValorCusto, decimal dValorVenda, decimal dValorLucro, decimal dEstoque, decimal dPeso)
{
//cria um objeto do tipo sqlparametercollection
SqlParameterCollection parametros = new SqlCommand().Parameters;
parametros.Add(new SqlParameter("@Nome", sNome));
parametros.Add(new SqlParameter("@ValorCusto", dValorCusto));
parametros.Add(new SqlParameter("@ValorVenda", dValorVenda));
parametros.Add(new SqlParameter("@ValorLucro", dValorLucro));
parametros.Add(new SqlParameter("@Estoque", dEstoque));
parametros.Add(new SqlParameter("@Peso", dPeso));
objAcessaDados.ExecuteSQL("spCadastroProduto", parametros);
}
public void InseriCliente(string sNome, string sEndereco, string sCPF, string sRG, string sEstado, string sCidade)
{
//cria um objeto do tipo sqlparametercollection
SqlParameterCollection parametros = new SqlCommand().Parameters;
parametros.Add(new SqlParameter("@Nome", sNome));
parametros.Add(new SqlParameter("@Endereco", sEndereco));
parametros.Add(new SqlParameter("@CPF", sCPF));
parametros.Add(new SqlParameter("@RG", sRG));
parametros.Add(new SqlParameter("@Cidade", sCidade));
parametros.Add(new SqlParameter("@Estado", sEstado));
objAcessaDados.ExecuteSQL("spInserirCliente", parametros);
}
public DataSet GetDs(int sPageIndex, int sPageSize, Hashtable ht)
{
PagerData pd = PagerData.GetInstance();
SqlParameterCollection spc = new SqlCommand().Parameters;
pd.Conn = AppConfig.Conn_O2O2;
pd.Field = @" a.SysNo,c.PropertyName,d.GroupDesc,a.IsInAdvSearch,a.WebDisplayStyle,a.IsMustInput,a.SortNo,a.GroupSysNo ";
pd.Table = @" dbo.P_CategoryTemplateProperty AS a
INNER JOIN dbo.P_Category AS b ON a.C3SysNo = b.SysNo AND ISNULL(b.C3Name,'') != ''
INNER JOIN dbo.P_Property AS c ON a.PropertySysNo = c.SysNo
INNER JOIN dbo.P_Property_Group AS d ON a.GroupSysNo = d.SysNo ";
pd.Where = " WHERE 1=1 ";
pd.SearchWhere = " 1=1 ";
pd.Order = " ORDER BY a.SysNo ASC ";
pd.PageSize = sPageSize;
pd.CurrentPageIndex = sPageIndex;
if (ht != null && ht.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (string key in ht.Keys)
{
#region 搜索条件
if (key == "CategoryID")
{
//sb.Append(" and b.SysNo ").Append(" = ").Append(Util.ToSqlString(ht[key].ToString()));
sb.Append(" and b.SysNo=@CategoryID ");
spc.Add(new SqlParameter("@CategoryID", ht[key].ToString()));
}
#endregion
}
pd.SearchWhere += sb.ToString();
}
pd.Collection = spc;
DataSet ds = pd.GetPage(PageIndex);
return(ds);
}
public static void CodeCoverageSqlClient()
{
SqlParameterCollection opc = new SqlCommand().Parameters;
Assert.True(opc.Count == 0, string.Format("FAILED: Expected count: {0}. Actual count: {1}.", 0, opc.Count));
Assert.False(((IList)opc).IsReadOnly, "FAILED: Expected collection to NOT be read only.");
Assert.False(((IList)opc).IsFixedSize, "FAILED: Expected collection to NOT be fixed size.");
Assert.False(((IList)opc).IsSynchronized, "FAILED: Expected collection to NOT be synchronized.");
DataTestClass.AssertEqualsWithDescription("Object", ((IList)opc).SyncRoot.GetType().Name, "FAILED: Incorrect SyncRoot Name");
{
string failValue;
DataTestClass.AssertThrowsWrapper<IndexOutOfRangeException>(() => failValue = opc[0].ParameterName, "Invalid index 0 for this SqlParameterCollection with Count=0.");
DataTestClass.AssertThrowsWrapper<IndexOutOfRangeException>(() => failValue = opc["@p1"].ParameterName, "An SqlParameter with ParameterName '@p1' is not contained by this SqlParameterCollection.");
}
DataTestClass.AssertThrowsWrapper<ArgumentNullException>(() => opc.Add(null), "The SqlParameterCollection only accepts non-null SqlParameter type objects.");
opc.Add((object)new SqlParameter());
IEnumerator enm = opc.GetEnumerator();
Assert.True(enm.MoveNext(), "FAILED: Expected MoveNext to be true");
DataTestClass.AssertEqualsWithDescription("Parameter1", ((SqlParameter)enm.Current).ParameterName, "FAILED: Incorrect ParameterName");
opc.Add(new SqlParameter());
DataTestClass.AssertEqualsWithDescription("Parameter2", opc[1].ParameterName, "FAILED: Incorrect ParameterName");
opc.Add(new SqlParameter(null, null));
opc.Add(new SqlParameter(null, SqlDbType.Int));
DataTestClass.AssertEqualsWithDescription("Parameter4", opc["Parameter4"].ParameterName, "FAILED: Incorrect ParameterName");
opc.Add(new SqlParameter("Parameter5", SqlDbType.NVarChar, 20));
opc.Add(new SqlParameter(null, SqlDbType.NVarChar, 20, "a"));
opc.RemoveAt(opc[3].ParameterName);
DataTestClass.AssertEqualsWithDescription(-1, opc.IndexOf(null), "FAILED: Incorrect index for null value");
SqlParameter p = opc[0];
DataTestClass.AssertThrowsWrapper<ArgumentException>(() => opc.Add((object)p), "The SqlParameter is already contained by another SqlParameterCollection.");
DataTestClass.AssertThrowsWrapper<ArgumentException>(() => new SqlCommand().Parameters.Add(p), "The SqlParameter is already contained by another SqlParameterCollection.");
DataTestClass.AssertThrowsWrapper<ArgumentNullException>(() => opc.Remove(null), "The SqlParameterCollection only accepts non-null SqlParameter type objects.");
string pname = p.ParameterName;
p.ParameterName = pname;
p.ParameterName = pname.ToUpper();
p.ParameterName = pname.ToLower();
p.ParameterName = "@p1";
p.ParameterName = pname;
opc.Clear();
opc.Add(p);
opc.Clear();
opc.AddWithValue("@p1", null);
DataTestClass.AssertEqualsWithDescription(-1, opc.IndexOf(p.ParameterName), "FAILED: Incorrect index for parameter name");
opc[0] = p;
DataTestClass.AssertEqualsWithDescription(0, opc.IndexOf(p.ParameterName), "FAILED: Incorrect index for parameter name");
Assert.True(opc.Contains(p.ParameterName), "FAILED: Expected collection to contain provided parameter.");
Assert.True(opc.Contains(opc[0]), "FAILED: Expected collection to contain provided parameter.");
opc[0] = p;
opc[p.ParameterName] = new SqlParameter(p.ParameterName, null);
opc[p.ParameterName] = new SqlParameter();
opc.RemoveAt(0);
new SqlCommand().Parameters.Clear();
new SqlCommand().Parameters.CopyTo(new object[0], 0);
Assert.False(new SqlCommand().Parameters.GetEnumerator().MoveNext(), "FAILED: Expected MoveNext to be false");
DataTestClass.AssertThrowsWrapper<InvalidCastException>(() => new SqlCommand().Parameters.Add(0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");
DataTestClass.AssertThrowsWrapper<InvalidCastException>(() => new SqlCommand().Parameters.Insert(0, 0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");
DataTestClass.AssertThrowsWrapper<InvalidCastException>(() => new SqlCommand().Parameters.Remove(0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");
DataTestClass.AssertThrowsWrapper<ArgumentException>(() => new SqlCommand().Parameters.Remove(new SqlParameter()), "Attempted to remove an SqlParameter that is not contained by this SqlParameterCollection.");
}
public static void CodeCoverageSqlClient()
{
SqlParameterCollection opc = new SqlCommand().Parameters;
Assert.True(opc.Count == 0, string.Format("FAILED: Expected count: {0}. Actual count: {1}.", 0, opc.Count));
Assert.False(((IList)opc).IsReadOnly, "FAILED: Expected collection to NOT be read only.");
Assert.False(((IList)opc).IsFixedSize, "FAILED: Expected collection to NOT be fixed size.");
Assert.False(((IList)opc).IsSynchronized, "FAILED: Expected collection to NOT be synchronized.");
DataTestUtility.AssertEqualsWithDescription("Object", ((IList)opc).SyncRoot.GetType().Name, "FAILED: Incorrect SyncRoot Name");
{
string failValue;
DataTestUtility.AssertThrowsWrapper <IndexOutOfRangeException>(() => failValue = opc[0].ParameterName, "Invalid index 0 for this SqlParameterCollection with Count=0.");
DataTestUtility.AssertThrowsWrapper <IndexOutOfRangeException>(() => failValue = opc["@p1"].ParameterName, "An SqlParameter with ParameterName '@p1' is not contained by this SqlParameterCollection.");
}
DataTestUtility.AssertThrowsWrapper <ArgumentNullException>(() => opc.Add(null), "The SqlParameterCollection only accepts non-null SqlParameter type objects.");
opc.Add((object)new SqlParameter());
IEnumerator enm = opc.GetEnumerator();
Assert.True(enm.MoveNext(), "FAILED: Expected MoveNext to be true");
DataTestUtility.AssertEqualsWithDescription("Parameter1", ((SqlParameter)enm.Current).ParameterName, "FAILED: Incorrect ParameterName");
opc.Add(new SqlParameter());
DataTestUtility.AssertEqualsWithDescription("Parameter2", opc[1].ParameterName, "FAILED: Incorrect ParameterName");
opc.Add(new SqlParameter(null, null));
opc.Add(new SqlParameter(null, SqlDbType.Int));
DataTestUtility.AssertEqualsWithDescription("Parameter4", opc["Parameter4"].ParameterName, "FAILED: Incorrect ParameterName");
opc.Add(new SqlParameter("Parameter5", SqlDbType.NVarChar, 20));
opc.Add(new SqlParameter(null, SqlDbType.NVarChar, 20, "a"));
opc.RemoveAt(opc[3].ParameterName);
DataTestUtility.AssertEqualsWithDescription(-1, opc.IndexOf(null), "FAILED: Incorrect index for null value");
SqlParameter p = opc[0];
DataTestUtility.AssertThrowsWrapper <ArgumentException>(() => opc.Add((object)p), "The SqlParameter is already contained by another SqlParameterCollection.");
DataTestUtility.AssertThrowsWrapper <ArgumentException>(() => new SqlCommand().Parameters.Add(p), "The SqlParameter is already contained by another SqlParameterCollection.");
DataTestUtility.AssertThrowsWrapper <ArgumentNullException>(() => opc.Remove(null), "The SqlParameterCollection only accepts non-null SqlParameter type objects.");
string pname = p.ParameterName;
p.ParameterName = pname;
p.ParameterName = pname.ToUpper();
p.ParameterName = pname.ToLower();
p.ParameterName = "@p1";
p.ParameterName = pname;
opc.Clear();
opc.Add(p);
opc.Clear();
opc.AddWithValue("@p1", null);
DataTestUtility.AssertEqualsWithDescription(-1, opc.IndexOf(p.ParameterName), "FAILED: Incorrect index for parameter name");
opc[0] = p;
DataTestUtility.AssertEqualsWithDescription(0, opc.IndexOf(p.ParameterName), "FAILED: Incorrect index for parameter name");
Assert.True(opc.Contains(p.ParameterName), "FAILED: Expected collection to contain provided parameter.");
Assert.True(opc.Contains(opc[0]), "FAILED: Expected collection to contain provided parameter.");
opc[0] = p;
opc[p.ParameterName] = new SqlParameter(p.ParameterName, null);
opc[p.ParameterName] = new SqlParameter();
opc.RemoveAt(0);
new SqlCommand().Parameters.Clear();
new SqlCommand().Parameters.CopyTo(new object[0], 0);
Assert.False(new SqlCommand().Parameters.GetEnumerator().MoveNext(), "FAILED: Expected MoveNext to be false");
DataTestUtility.AssertThrowsWrapper <InvalidCastException>(() => new SqlCommand().Parameters.Add(0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");
DataTestUtility.AssertThrowsWrapper <InvalidCastException>(() => new SqlCommand().Parameters.Insert(0, 0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");
DataTestUtility.AssertThrowsWrapper <InvalidCastException>(() => new SqlCommand().Parameters.Remove(0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");
DataTestUtility.AssertThrowsWrapper <ArgumentException>(() => new SqlCommand().Parameters.Remove(new SqlParameter()), "Attempted to remove an SqlParameter that is not contained by this SqlParameterCollection.");
}
public string SendEmailUploadFile(string reqNo1)
{
try
{
DataTable data_mail_list = new DataTable();
string sql_mail_list = " SELECT Smtpid, EmailAddress,SMTPServer,SSL,smtppassword,Portdefault from Pkemaillessor where Isactive = 1 ";
data_mail_list = this.db.QueryDataTable(sql_mail_list);
var email_list = (from c in data_mail_list.AsEnumerable() select new {
Smtpid = c["Smtpid"].ToString(),
EmailAddress = c["EmailAddress"].ToString(),
SMTPServer = c["SMTPServer"].ToString(),
SSL = Convert.ToInt32(c["SSL"]),
smtppassword = c["smtppassword"].ToString(),
Portdefault = c["Portdefault"].ToString()
}).ToList();
foreach (var _item_email in email_list)
{
List <DocList> doc_list = new List <DocList>();
DataTable dt = new DataTable();
string sql = " SELECT [No1] ";
sql += " ,[Documentype] ";
sql += " ,[Documentimage] ";
sql += " ,[pathFile] ";
sql += " ,[Last_Upd] ";
sql += " ,[Updateby] ";
sql += " ,[filename] ";
sql += " FROM[dbo].[Pkrequirechangememberdoc] ";
sql += " where No1 = '" + reqNo1 + "' ";
dt = this.db.QueryDataTable(sql);
doc_list = (from c in dt.AsEnumerable()
select new DocList
{
docType = c["Documentype"].ToString(),
DocName = c["filename"].ToString(),
PathFile = c["pathFile"].ToString()
}).ToList();
DataTable data_mail = new DataTable();
SqlParameterCollection parameterMail = new SqlCommand().Parameters;
parameterMail.Add("@p_No1", SqlDbType.VarChar).Value = reqNo1;
data_mail = db.StoreQuery("GetMessageMail", parameterMail);
var getMesessMail = (from c in data_mail.AsEnumerable() select new { subject = c["SubjectMail"].ToString(), mes = c["MesMail"].ToString() }).SingleOrDefault();
///this._dbWarp.GetMessageMail(reqNo1).Select(c => new { subject = c.SubjectMail, mes = c.MesMail }).SingleOrDefault();
string from = _item_email.Smtpid;
using (MailMessage mail = new MailMessage(from, _item_email.EmailAddress))
{
mail.Subject = getMesessMail.subject;
mail.Body = getMesessMail.mes;
foreach (var dataFile in doc_list)
{
System.Net.Mail.Attachment attachment;
attachment = new System.Net.Mail.Attachment(dataFile.PathFile);
mail.Attachments.Add(attachment);
}
mail.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = _item_email.SMTPServer;
smtp.EnableSsl = _item_email.SSL == 1 ? true : false;
NetworkCredential networkCredential = new NetworkCredential(from, _item_email.smtppassword);
smtp.UseDefaultCredentials = true;
smtp.Credentials = networkCredential;
smtp.Port = Convert.ToInt32(_item_email.Portdefault);
smtp.Send(mail);
}
}
return("OK");
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string RequestCheangMember(string No1, string terncod, string Statustyp, string Custno, string Fname, string Lname, string MemberType, bool Changecontacttype, string Contacttype_New, string Contacttype_Old, string Paytype, bool Changecarid, string CarID_New, string CarModel_New, string Carcolor_New, string CarID1_New, string CarModel1_New, string Carcolor1_New, string CarID2_New, string CarModel2_New, string Carcolor2_New, string CarID_Old, string CarModel_Old, string Carcolor_Old, string CarID2_Old, string CarModel2_Old, string Carcolor2_Old, string CarID3_Old, string CarModel3_Old, string Carcolor3_Old, string Dateactive, string Updateby, string New_Name, string New_lastName, bool Changename)
{
try
{
SqlParameterCollection parameter = new SqlCommand().Parameters;
parameter.Add("@No1", SqlDbType.NVarChar).Value = No1;
parameter.Add("@terncode", SqlDbType.NVarChar).Value = terncod;
parameter.Add("@Statustype", SqlDbType.NVarChar).Value = Statustyp;
parameter.Add("@Custno", SqlDbType.NVarChar).Value = Custno;
parameter.Add("@Fname", SqlDbType.NVarChar).Value = Fname;
parameter.Add("@Lname", SqlDbType.NVarChar).Value = Lname;
parameter.Add("@MemberType", SqlDbType.NVarChar).Value = MemberType;
parameter.Add("@Changecontacttype", SqlDbType.NVarChar).Value = Changecontacttype;
parameter.Add("@Contacttype_New", SqlDbType.NVarChar).Value = Contacttype_New;
parameter.Add("@Contacttype_Old", SqlDbType.NVarChar).Value = Contacttype_Old;
parameter.Add("@Paytype", SqlDbType.NVarChar).Value = Paytype;
parameter.Add("@Changecarid", SqlDbType.NVarChar).Value = Changecarid;
parameter.Add("@CarID_New", SqlDbType.NVarChar).Value = CarID_New;
parameter.Add("@CarModel_New", SqlDbType.NVarChar).Value = CarModel_New;
parameter.Add("@Carcolor_New", SqlDbType.NVarChar).Value = Carcolor_New;
parameter.Add("@CarID1_New", SqlDbType.NVarChar).Value = CarID1_New;
parameter.Add("@CarModel1_New", SqlDbType.NVarChar).Value = CarModel1_New;
parameter.Add("@Carcolor1_New", SqlDbType.NVarChar).Value = Carcolor1_New;
parameter.Add("@CarID2_New", SqlDbType.NVarChar).Value = CarID2_New;
parameter.Add("@CarModel2_New", SqlDbType.NVarChar).Value = CarModel2_New;
parameter.Add("@Carcolor2_New", SqlDbType.NVarChar).Value = Carcolor2_New;
parameter.Add("@CarID_Old", SqlDbType.NVarChar).Value = CarID_Old;
parameter.Add("@CarModel_Old", SqlDbType.NVarChar).Value = CarModel_Old;
parameter.Add("@Carcolor_Old", SqlDbType.NVarChar).Value = Carcolor_Old;
parameter.Add("@CarID2_Old", SqlDbType.NVarChar).Value = CarID2_Old;
parameter.Add("@CarModel2_Old", SqlDbType.NVarChar).Value = CarModel2_Old;
parameter.Add("@Carcolor2_Old", SqlDbType.NVarChar).Value = Carcolor2_Old;
parameter.Add("@CarID3_Old", SqlDbType.NVarChar).Value = CarID3_Old;
parameter.Add("@CarModel3_Old", SqlDbType.NVarChar).Value = CarModel3_Old;
parameter.Add("@Carcolor3_Old", SqlDbType.NVarChar).Value = Carcolor3_Old;
parameter.Add("@Dateactive", SqlDbType.DateTime).Value = Convert.ToDateTime(Dateactive);
parameter.Add("@Updateby", SqlDbType.NVarChar).Value = Updateby;
parameter.Add("@New_Name", SqlDbType.NVarChar).Value = New_Name;
parameter.Add("@New_lastName", SqlDbType.NVarChar).Value = New_lastName;
parameter.Add("@Changename", SqlDbType.NVarChar).Value = Changename;
db.QueryExecuteNonQueryStor("require_chaeng_member", parameter);
return("ok");
}
catch (Exception ex)
{
return(ex.Message);
}
throw new NotImplementedException();
}
public string CancelMember(string No1, string terncode, int Statustype, string Custno, string Fname, string Lname, string MemberType, string Dateactive, string Updateby, string CarID_Old, string CarModel_Old, string Carcolor_Old, string CarID2_Old, string CarModel2_Old, string Carcolor2_Old, string CarID3_Old, string CarModel3_Old, string Carcolor3_Old)
{
try
{
SqlParameterCollection parameter = new SqlCommand().Parameters;
parameter.Add("@No1", SqlDbType.VarChar).Value = No1;
parameter.Add("@terncode", SqlDbType.VarChar).Value = terncode;
parameter.Add("@Statustype", SqlDbType.Int).Value = Statustype;
parameter.Add("@Custno", SqlDbType.VarChar).Value = Custno;
parameter.Add("@Fname", SqlDbType.NVarChar).Value = Fname;
parameter.Add("@Lname", SqlDbType.VarChar).Value = Lname;
parameter.Add("@MemberType", SqlDbType.VarChar).Value = MemberType;
parameter.Add("@Dateactive", SqlDbType.DateTime).Value = Convert.ToDateTime(Dateactive);
parameter.Add("@Updateby", SqlDbType.NVarChar).Value = Updateby;
parameter.Add("@CarID_Old", SqlDbType.NVarChar).Value = CarID_Old;
parameter.Add("@CarModel_Old", SqlDbType.NVarChar).Value = CarModel_Old;
parameter.Add("@Carcolor_Old", SqlDbType.NVarChar).Value = CarModel_Old;
parameter.Add("@CarID2_Old", SqlDbType.NVarChar).Value = CarModel2_Old;
parameter.Add("@CarModel2_Old", SqlDbType.NVarChar).Value = CarModel2_Old;
parameter.Add("@Carcolor2_Old", SqlDbType.NVarChar).Value = Carcolor2_Old;
parameter.Add("@CarID3_Old", SqlDbType.NVarChar).Value = CarID3_Old;
parameter.Add("@CarModel3_Old", SqlDbType.NVarChar).Value = CarModel3_Old;
parameter.Add("@Carcolor3_Old", SqlDbType.NVarChar).Value = Carcolor3_Old;
db.QueryExecuteNonQueryStor("requestCancelMember", parameter);
return("ok");
}
catch (Exception ex)
{
return(ex.Message);
}
}
/// <summary>
/// Добавление параметра в коллекцию параметров
/// </summary>
/// <param name="cmd">SqlCommand</param>
/// <param name="param">Наименование параметра</param>
/// <param name="value">Значнение параметра</param>
public void AddParam(string param, string value)
{
cmd.Add(param, value);
}
public string insert_RequestMember(string No1, string terncode, int?Statustype, string Fname, string Lname, string MemberType, int?Contacttype_New, int?Paytype, string CarID_New, string CarModel_New, string Carcolor_New, string CarID1_New, string CarModel1_New, string Carcolor1_New, string CarID2_New, string CarModel2_New, string Carcolor2_New, DateTime?Dateactive, string Updateby, string Telephone)
{
try
{
SqlParameterCollection parameter = new SqlCommand().Parameters;
parameter.Add("@No1", SqlDbType.NVarChar).Value = No1;
parameter.Add("@terncode", SqlDbType.NVarChar).Value = terncode;
parameter.Add("@Statustype", SqlDbType.NVarChar).Value = Statustype;
parameter.Add("@Fname", SqlDbType.NVarChar).Value = Fname;
parameter.Add("@Lname", SqlDbType.NVarChar).Value = Lname;
parameter.Add("@MemberType", SqlDbType.NVarChar).Value = MemberType;
parameter.Add("@Contacttype_New", SqlDbType.NVarChar).Value = Contacttype_New;
parameter.Add("@Paytype", SqlDbType.NVarChar).Value = Paytype;
parameter.Add("@CarID_New", SqlDbType.NVarChar).Value = CarID_New;
parameter.Add("@CarModel_New", SqlDbType.NVarChar).Value = CarModel_New;
parameter.Add("@Carcolor_New", SqlDbType.NVarChar).Value = Carcolor_New;
parameter.Add("@CarID1_New", SqlDbType.NVarChar).Value = CarID1_New;
parameter.Add("@CarModel1_New", SqlDbType.NVarChar).Value = CarModel1_New;
parameter.Add("@Carcolor1_New", SqlDbType.NVarChar).Value = Carcolor1_New;
parameter.Add("@CarID2_New", SqlDbType.NVarChar).Value = CarID2_New;
parameter.Add("@CarModel2_New", SqlDbType.NVarChar).Value = CarModel2_New;
parameter.Add("@Carcolor2_New", SqlDbType.NVarChar).Value = Carcolor2_New;
parameter.Add("@Dateactive", SqlDbType.DateTime).Value = Convert.ToDateTime(Dateactive);
parameter.Add("@Updateby", SqlDbType.NVarChar).Value = Updateby;
parameter.Add("@Telephone", SqlDbType.NVarChar).Value = Telephone;
db.QueryExecuteNonQueryStor("Add_Requirechangemember", parameter);
return("OK");
}
catch (Exception ex)
{
return(ex.Message);
}
}
List <sosportalcliente_Dommain> ListarMenuPorIdPessoaLoja
(int idPessoaLoja)
{
try
{
var connectionString = ConfigurationManager.ConnectionStrings["DB_Contexto_Procedure"].ToString();
/* Criar conexão com banco de dados */
var _connection = new SqlConnection(connectionString);
/* Abrir conexão */
_connection.Open();
/* Criar comando que vai enviar informações para o banco */
SqlCommand _command = _connection.CreateCommand();
/* Adicionar as informações dentro do comando que vai enviar para o banco */
_command.CommandType = CommandType.StoredProcedure;
/* Recebe o nome da procedure que esta sendo executada */
_command.CommandText = "uspListarPorCompras";
/* Defini o tempo que a conexão ficará aberta (Em Segundos [7200] = 2 horas) */
_command.CommandTimeout = 7200;
// var _parameterCollection = new SqlCommand();
var _parameterCollection = new SqlCommand().Parameters;
/* Adicionar os paramentros no comando */
_parameterCollection.Add("@IdPessoaLoja", idPessoaLoja);
/* Criar um adptador */
var sqlDataAdapter = new SqlDataAdapter(_command);
/* Criar datatable vasia aonde vou adicionar os valores que serão retornados do banco */
var dataTable = new DataTable();
/* Mandar comando ir ate o banco buscar os daods e o o adptador preencher a datatable */
sqlDataAdapter.Fill(dataTable);
/* Finaliza Conexão com banco de Dados */
_connection.Close();
_connection.Dispose();
var retorno = new List <sosportalcliente_Dommain>();
/* O foreach varre o datarow e adiciona cada linha em uma lista ate acabar coleção */
foreach (DataRow linha in dataTable.Rows)
{
/* Adiciona as informações na entidade */
var entity = new sosportalcliente_Dommain
{
IdCliente = int.Parse(linha["IdCliente"].ToString()),
IdPessoaLoja = int.Parse(linha["IdPessoaLoja"].ToString()),
};
var ent = new sosportalconfiguracaopessoa_Dommain
{
IdConfiguracao = int.Parse(linha["IdConfiguracao"].ToString()),
};
var entityFinal = new sosportalperfil_Dommain
{
IdPerfil = int.Parse(linha["IdPerfil"].ToString()),
};
var confi = new sosportalconfiguracao_Dommain
{
IdConfiguracao = int.Parse(linha["IdConfiguracao"].ToString()),
ControllerAction = linha["ControllerAction"].ToString(),
IdController = int.Parse(linha["IdController"].ToString()),
};
retorno.Add(entity);
}
/* Retorna a tabela preenchida */
return(retorno.ToList());
}
catch (Exception exception)
{
throw new Exception(exception.ToString());
}
}
