protected string generate_forum_name(string group_id)
{
string conn = System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;
SqlConnection connection = new SqlConnection(conn);
string sqlqry = "SELECT group_name FROM forum_group WHERE group_id=@p1";
SqlCommand command = new SqlCommand(sqlqry, connection);
SqlParameter param1 = new SqlParameter();
param1.SqlDbType = System.Data.SqlDbType.Int;
param1.ParameterName = "@p1";
param1.Value = group_id;
command.Parameters.Add(param1);
SqlDataReader Reader = null;
command.Connection.Open();
Reader = command.ExecuteReader();
Reader.Read();
string name = Reader[0].ToString();
command.Connection.Close();
return name;
}
public void PersistUpdateOf(IAggregateRoot entity)
{
Account account = entity as Account;
string sql = "UPDATE Account SET Balance=@Balance WHERE Id=@Id";
SqlParameter parameter1 = new SqlParameter("@Id", account.Id);
SqlParameter parameter2= new SqlParameter("@Balance", account.Balance);
using (SqlConnection conn = new SqlConnection(_connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.Add(parameter1);
cmd.Parameters.Add(parameter2);
int affectedCount = cmd.ExecuteNonQuery();
if (affectedCount <= 0)
{
throw new Exception("update Account failed");
}
}
}
}
public void DeleteUser(decimal UserId)
{
SqlParameter[] parameters=new SqlParameter[1];
parameters[0]=new SqlParameter("@UserId" , UserId);
DataBase.Instance.ExecuteCommand("dbo.sproc_DeleteUser" , CommandType.StoredProcedure , parameters , null);
}
public static bool AddCart(orderinfo item)
{
SqlParameter[] parms = new SqlParameter[7];
parms[0] = new SqlParameter("@id", SqlDbType.VarChar, 20);
parms[0].Direction = ParameterDirection.Output;
parms[1] = new SqlParameter("@productid", SqlDbType.NVarChar,50);
parms[1].Value = item.productid;
parms[2] = new SqlParameter("@userid", SqlDbType.NVarChar, 50);
parms[2].Value = item.userid;
parms[3] = new SqlParameter("adddate", SqlDbType.DateTime);
parms[3].Value = item.adddate;
parms[4] = new SqlParameter("@vipprice", SqlDbType.Decimal);
parms[4].Value = item.vipprice;
parms[5] = new SqlParameter("@buynum", SqlDbType.Int);
parms[5].Value =item.buynum;
parms[6] = new SqlParameter("@cartstate", SqlDbType.Int);
parms[6].Value = item.cartstate;
string sql = "exec dbo.www_getno 'usercart',10,@id output " +
"Insert into usercart(cartid,productid,userid,adddate,vipprice,buynum,cartstate) VALUES (@id,@productid,@userid,@adddate,@vipprice,@buynum,@cartstate)";
int result = 0;
try
{
result = SqlHelper.ExecuteNonQuery(SqlHelper.connectionstring, CommandType.Text, sql, parms);
return result > 0;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
}
}
public List<string> consultarMarca(string id)
{
List<string> marcas = new List<string>();
SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|DBTag.mdf';Integrated Security=True;User Instance=True");
// Abre a conexão
conn.Open();
try
{
SqlCommand select = new SqlCommand("SELECT * from TbMarca where id=@id", conn);
SqlParameter pID = new SqlParameter("id", id);
select.Parameters.Add(pID);
// Lê, linha a linha a tabela
SqlDataReader dr = select.ExecuteReader();
while (dr.Read())
{
marcas.Add(dr["id"].ToString());
marcas.Add(dr["nome_marca"].ToString());
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString(), ex);
}
return marcas;
}
public override List<SqlParameter> CreateInsertParameters(IModel obj, ref SqlParameter returnValue)
{
FinBusInvAllotDetail inv_finbusinvallotdetail = (FinBusInvAllotDetail)obj;
List<SqlParameter> paras = new List<SqlParameter>();
returnValue.Direction = ParameterDirection.Output;
returnValue.SqlDbType = SqlDbType.Int;
returnValue.ParameterName = "@DetailId";
returnValue.Size = 4;
paras.Add(returnValue);
SqlParameter allotidpara = new SqlParameter("@AllotId", SqlDbType.Int, 4);
allotidpara.Value = inv_finbusinvallotdetail.AllotId;
paras.Add(allotidpara);
SqlParameter businessinvoiceidpara = new SqlParameter("@BusinessInvoiceId", SqlDbType.Int, 4);
businessinvoiceidpara.Value = inv_finbusinvallotdetail.BusinessInvoiceId;
paras.Add(businessinvoiceidpara);
SqlParameter financeinvoiceidpara = new SqlParameter("@FinanceInvoiceId", SqlDbType.Int, 4);
financeinvoiceidpara.Value = inv_finbusinvallotdetail.FinanceInvoiceId;
paras.Add(financeinvoiceidpara);
SqlParameter allotbalapara = new SqlParameter("@AllotBala", SqlDbType.Decimal, 9);
allotbalapara.Value = inv_finbusinvallotdetail.AllotBala;
paras.Add(allotbalapara);
SqlParameter detailstatuspara = new SqlParameter("@DetailStatus", SqlDbType.Int, 4);
detailstatuspara.Value = (int)Common.StatusEnum.已生效;
paras.Add(detailstatuspara);
return paras;
}
/// <summary>
/// Get InvoiceNo Corresponding to Ledger For PurchaseReturn Register
/// </summary>
/// <param name="decLedgerId"></param>
/// <returns></returns>
public List<DataTable> GetInvoiceNoCorrespondingtoLedgerForPurchaseReturnReport(decimal decLedgerId, decimal decVoucherId)
{
List<DataTable> ListObj = new List<DataTable>();
DataTable dtbl = new DataTable();
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlDataAdapter sqldataadapter = new SqlDataAdapter("GetInvoiceNoCorrespondingtoLedgerForPurchaseReturnReport", sqlcon);
sqldataadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter sqlparameter = new SqlParameter();
sqlparameter = sqldataadapter.SelectCommand.Parameters.Add("@ledgerId", SqlDbType.Decimal);
sqlparameter.Value = decLedgerId;
sqlparameter = sqldataadapter.SelectCommand.Parameters.Add("@voucherTypeId", SqlDbType.Decimal);
sqlparameter.Value = decVoucherId;
sqldataadapter.Fill(dtbl);
ListObj.Add(dtbl);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
sqlcon.Close();
}
return ListObj;
}
public List<ClientOrder> GetClientOrderByClientId(int clientId)
{
List<ClientOrder> result = new List<ClientOrder>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@client_id", clientId)
};
string sql = "SELECT id, client_id, client_address_id, real_name, phone, email, postcode, address, remark, create_time, country, city, encode FROM client_orders WHERE client_id = @client_id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ClientOrder co = new ClientOrder();
co.Id = dr.GetInt32(0);
co.ClientId = dr.GetInt32(1);
co.ClientAddressId = dr.GetInt32(2);
co.RealName = dr.GetString(3);
co.Phone = dr.GetString(4);
co.Email = dr.GetString(5);
co.Postcode = dr.GetString(6);
co.Address = dr.GetString(7);
co.Remark = dr.GetString(8);
co.CreateTime = dr.GetDateTime(9);
co.Country = dr.GetString(10);
co.City = dr.GetString(11);
co.Encode = dr.GetString(12);
result.Add(co);
}
}
return result;
}
public static string SP_Tender_FindRecord(string ProcessId, ref DataSet ReturnDs)
{
SqlConnection sqlConn = new SqlConnection(); //defines database connection
SqlCommand sqlCmd = new SqlCommand(); //defines what to do
SqlDataAdapter sqlAdap = new SqlDataAdapter();
try
{
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings["AMS_MasterConnectionString"].ToString();
sqlConn.Open();
sqlCmd.CommandText = "SP_Tender_FindRecord";
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = sqlConn;
SqlParameter parm1 = new SqlParameter("@ProcessId", SqlDbType.VarChar);
parm1.Value = ProcessId;
parm1.Direction = ParameterDirection.Input;
sqlCmd.Parameters.Add(parm1);
sqlAdap.SelectCommand = sqlCmd;
sqlAdap.Fill(ReturnDs);
return string.Empty;
}
catch (Exception err)
{
return err.Message;
}
finally
{ sqlConn.Close(); sqlConn.Dispose(); sqlAdap.Dispose(); }
}
internal static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
try
{
if ((commandParameters == null) || (parameterValues == null))
{
return;
}
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
commandParameters[i].Value = parameterValues[i];
}
}
catch (SystemException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
public SigmaResultType AddEquipment(TypeEquipment objEquipment)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
SigmaResultType result = new SigmaResultType();
TransactionScope scope = null;
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@EquipmentCodeMain", objEquipment.EquipmentCodeMain.Trim()));
paramList.Add(new SqlParameter("@EquipmentCodeSub", objEquipment.EquipmentCodeSub.Trim()));
paramList.Add(new SqlParameter("@Description", objEquipment.Description.Trim()));
paramList.Add(new SqlParameter("@ThirdLevel", objEquipment.ThirdLevel.Trim()));
paramList.Add(new SqlParameter("@Spec", objEquipment.Spec.Trim()));
paramList.Add(new SqlParameter("@EquipmentType", objEquipment.EquipmentType.Trim()));
paramList.Add(new SqlParameter("@CreatedBy", userinfo.SigmaUserId.Trim()));
paramList.Add(new SqlParameter("@ModelNumber", objEquipment.ModelNumber.Trim()));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddEquipment", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
/// <summary>
/// Add Parameters for calling stored procedures
/// </summary>
/// <param name="paramName">Name of Parameter</param>
/// <param name="value">Value of Parameter</param>
public void AddParameter(string paramName, object value)
{
SqlParameter param = new SqlParameter();
param.ParameterName = paramName;
param.Value = value;
cmd.Parameters.Add(param);
}
public static void ExecuteNonQuery( CommandType cmdType, string strProcedureName, SqlParameter[] objParameters)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["db1ConnectionString"].ToString());
con.Open();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = cmdType;
cmd.CommandText = strProcedureName;
AttachParameters(cmd, objParameters);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
}
public static DataSet ExecuteDataSet(CommandType cmdType, string strProcedureName, SqlParameter[] objParameters)
{
DataSet dset = new DataSet();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["db1ConnectionString"].ToString());
con.Open();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = cmdType;
cmd.CommandText = strProcedureName;
AttachParameters(cmd, objParameters);
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(dset);
return dset;
}
catch (Exception ex)
{
return dset;
}
finally
{
con.Close();
}
}
public bool AddSearchStyle(int modelId, string content)
{
SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@modelid", SqlDbType.Int), new SqlParameter("@content", SqlDbType.NText) };
commandParameters[0].Value = modelId;
commandParameters[1].Value = content;
return (SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringKy, CommandType.StoredProcedure, "Up_Style_AddSearchStyle", commandParameters) > 0);
}
public DataSet SelectCandidateDetailsByAidClientId(int aid, int clientid)
{
SqlParameter[] objprmArray = new SqlParameter[2];
objprmArray[0] = new SqlParameter("@Aid", aid);
objprmArray[1] = new SqlParameter("@ClientId", clientid);
return SqlHelper.ExecuteDataset(ConnectionString, "USP_Job_SelectCandidateDetailsByAidClientId", objprmArray);
}
// This function will be used to execute R(CRUD) operation of parameterized commands
internal static DataTable ExecuteParamerizedSelectCommand(string CommandName, CommandType cmdType, SqlParameter[] param)
{
DataTable table = new DataTable();
using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
{
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(param);
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(table);
}
}
catch
{
throw;
}
}
}
return table;
}
// This function will be used to execute CUD(CRUD) operation of parameterized commands
internal static bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars)
{
int result = 0;
using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
{
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(pars);
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
result = cmd.ExecuteNonQuery();
}
catch
{
throw;
}
}
}
return (result > 0);
}
public SigmaResultType AddCWA(TypeCWA objCWA)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@ProjectId", Utilities.ToInt32(userinfo.CurrentProjectId.ToString().Trim())));
paramList.Add(new SqlParameter("@Name", objCWA.Name.Trim()));
paramList.Add(new SqlParameter("@Area", objCWA.Area));
paramList.Add(new SqlParameter("@Description", objCWA.Description.Trim()));
paramList.Add(new SqlParameter("@CreatedBy", userinfo.SigmaUserId.Trim()));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddCWA", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
public bool InserirDA(Usuarios usu)
{
SqlParameter[] paramsToSP = new SqlParameter[12];
paramsToSP[0] = new SqlParameter("@login", usu.Login);
paramsToSP[1] = new SqlParameter("@senha", utils.Criptografar(usu.Senha));
paramsToSP[2] = new SqlParameter("@nome", usu.Nome.ToUpper());
paramsToSP[3] = new SqlParameter("@status", usu.Status);
paramsToSP[4] = new SqlParameter("@dtinicio", usu.DtInicio);
paramsToSP[5] = new SqlParameter("@dtfim", usu.DtFim);
paramsToSP[6] = new SqlParameter("@tipo", usu.Tipo);
paramsToSP[7] = new SqlParameter("@email", usu.Email);
paramsToSP[8] = new SqlParameter("@pessoaid", usu.PessoaId);
paramsToSP[9] = new SqlParameter("@nrtentlogin", usu.NrTentLogin);
paramsToSP[10] = new SqlParameter("@dhtentlogin", usu.DhTentLogin);
paramsToSP[11] = new SqlParameter("@categoriaid", usu.CategoriaId);
try
{
SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["conexao"].ToString(), CommandType.StoredProcedure, "stp_insert_usuarios", paramsToSP);
return true;
}
catch (Exception e)
{
return false;
}
}
public DataSet PRC_PersonasListasDePrecios()
{
ArrayList Parameters=new ArrayList(0);
SqlParameter MODOParameter=new SqlParameter("@MODO",SqlDbType.Int);
MODOParameter.Size=0;
MODOParameter.Value=MODO;
Parameters.Add(MODOParameter);
SqlParameter PersonasListasDePrecios_IDParameter=new SqlParameter("@PersonasListasDePrecios_ID",SqlDbType.Int);
PersonasListasDePrecios_IDParameter.Size=0;
PersonasListasDePrecios_IDParameter.Value=PersonasListasDePrecios_ID;
Parameters.Add(PersonasListasDePrecios_IDParameter);
SqlParameter Personas_IDParameter=new SqlParameter("@Personas_ID",SqlDbType.Int);
Personas_IDParameter.Size=0;
Personas_IDParameter.Value=Personas_ID;
Parameters.Add(Personas_IDParameter);
SqlParameter ListasDePrecios_IDParameter=new SqlParameter("@ListasDePrecios_ID",SqlDbType.Int);
ListasDePrecios_IDParameter.Size=0;
ListasDePrecios_IDParameter.Value=ListasDePrecios_ID;
Parameters.Add(ListasDePrecios_IDParameter);
SqlParameter PersonasDirecciones_IDParameter=new SqlParameter("@PersonasDirecciones_ID",SqlDbType.Int);
PersonasDirecciones_IDParameter.Size=0;
PersonasDirecciones_IDParameter.Value=PersonasDirecciones_ID;
Parameters.Add(PersonasDirecciones_IDParameter);
DataSet dsResult=ExecuteStoredProcedure("[Grifo].[PRC_PersonasListasDePrecios]",ref Parameters);
return dsResult;
}
public void GetDBBackup(string backupDBName, string backupPath)
{
try
{
DataBaseUtility db = new DataBaseUtility();
SqlCommand cm = new SqlCommand();
string s = DateTime.Now.ToString("MMM/dd/yyyy");
SqlParameter[] sqlParams = new SqlParameter[]
{ new SqlParameter("@DBNAME", backupDBName),
new SqlParameter("@PATH", backupPath),
new SqlParameter("@BACKUPTYPE", 1),
new SqlParameter("@MSG", "Genrate Backup"),
new SqlParameter("@BACKUPFILENAME", s.Replace("/", "_") )};
db.ExecuteSP("DATABASE_BACKUP", sqlParams);
log.Info("Backup created success fully with name "+ s.Replace("/", "_"));
}
catch (Exception ex)
{
log.Info("Exception in GetDBBackup ",ex);
}
}
public ResultModel CheckContractSubBusinessInvoiceApplyConfirm(UserModel user, int subId)
{
ResultModel result = new ResultModel();
string cmdText = "select COUNT(*) from dbo.Inv_BusinessInvoice bi inner join dbo.Invoice inv on inv.InvoiceId = bi.InvoiceId where bi.SubContractId = 1 and inv.InvoiceStatus in (@entryStatus,@readyStatus)";
SqlParameter[] paras = new SqlParameter[3];
paras[0] = new SqlParameter("@subId", subId);
paras[1] = new SqlParameter("@entryStatus", (int)NFMT.Common.StatusEnum.已录入);
paras[1] = new SqlParameter("@readyStatus", (int)NFMT.Common.StatusEnum.已生效);
object obj = DBUtility.SqlHelper.ExecuteScalar(this.ConnectString, CommandType.Text, cmdText, paras);
int i = 0;
if (obj == null || !int.TryParse(obj.ToString(), out i) || i <= 0)
{
result.ResultStatus = -1;
result.Message = "检验业务发票失败";
return result;
}
if (i > 0)
{
result.ResultStatus = -1;
result.Message = "子合约中含有未完成的业务发票，不能进行确认完成操作。";
return result;
}
result.ResultStatus = 0;
result.Message = "业务发票全部完成";
return result;
}
public override List<SqlParameter> CreateInsertParameters(IModel obj, ref SqlParameter returnValue)
{
FinanceBusinessInvoice inv_financebusinessinvoice_ref = (FinanceBusinessInvoice)obj;
List<SqlParameter> paras = new List<SqlParameter>();
returnValue.Direction = ParameterDirection.Output;
returnValue.SqlDbType = SqlDbType.Int;
returnValue.ParameterName = "@RefId";
returnValue.Size = 4;
paras.Add(returnValue);
SqlParameter businessinvoiceidpara = new SqlParameter("@BusinessInvoiceId", SqlDbType.Int, 4);
businessinvoiceidpara.Value = inv_financebusinessinvoice_ref.BusinessInvoiceId;
paras.Add(businessinvoiceidpara);
SqlParameter financeinvoiceidpara = new SqlParameter("@FinanceInvoiceId", SqlDbType.Int, 4);
financeinvoiceidpara.Value = inv_financebusinessinvoice_ref.FinanceInvoiceId;
paras.Add(financeinvoiceidpara);
SqlParameter balapara = new SqlParameter("@Bala", SqlDbType.Decimal, 9);
balapara.Value = inv_financebusinessinvoice_ref.Bala;
paras.Add(balapara);
return paras;
}
protected void Delete(object sender, EventArgs e)
{
ImageButton img = (ImageButton)sender;
string HO_TYPE_ID = img.Attributes["PER_TYPE"].ToString();
string sql = "DELETE FROM HRM_VACATION_RULE WHERE per_type = @PER_TYPE";
SqlParameter[] param = new SqlParameter[1];
param[0] = OPM_BL.setParameter("@PER_TYPE", SqlDbType.VarChar, HO_TYPE_ID);
string ret = OPM_BL.ExecuteNoneQuery(sql, param);
if (ret == "true")
{
sql = "DELETE FROM PSST_PER_TYPE WHERE PER_TYPE = @PER_TYPE";
param = new SqlParameter[1];
param[0] = OPM_BL.setParameter("@PER_TYPE", SqlDbType.VarChar, HO_TYPE_ID);
ret = OPM_BL.ExecuteNoneQuery(sql, param);
}
ds_PER_TYPE.SelectParameters.Clear();
ds_PER_TYPE.SelectParameters.Add("PER_TYPE_NAME", System.Data.DbType.String, "%%");
CountData();
txtSearch.Text = "";
ScriptManager.RegisterStartupScript(this.Page, typeof(string), "Validation", "alert('ลบข้อมูลเรียบร้อย');", true);
}
public bool DeleteByID(string keywordId)
{
SqlParameter[] cmdParams = new SqlParameter[1];
cmdParams[0] = new SqlParameter("@KeywordId", SqlDbType.NVarChar,4000);
cmdParams[0].Value = keywordId;
return SqlHelper.ExecuteProc("PR_Keywords_Delete", cmdParams);
}
public Account GetById(int id)
{
string sql = "SELECT Id, Balance FROM Account WHERE Id=@Id";
SqlParameter parameter = new SqlParameter("@Id", id);
Account account = new Account();
using (SqlConnection conn = new SqlConnection(_connStr))
{
conn.Open();
using(SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.Add(parameter);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
account.Id = id;
account.Balance = Convert.ToDecimal(reader["Balance"]);
}
}
}
return account;
}
public List<getPlace> getallplace(string cityName)
{
try
{
List<getPlace> list = new List<getPlace>();
SqlConnection con = new SqlConnection();
con.ConnectionString = @"Data Source=MOHIT-PC\SQLEXPRESS;Initial Catalog=travelogue;User ID=sa;Password=patel";
string query = "select p.place_name from Place_Detail as p where p.CT_id = (select c.CT_id From City_Detail as c where c.CT_Name = @CityName) ";
SqlCommand cmd = new SqlCommand(query, con);
SqlParameter p = new SqlParameter("@CityName", cityName);
cmd.Parameters.Add(p);
con.Open();
SqlDataAdapter adp = new SqlDataAdapter();
DataTable dt = new DataTable();
adp.SelectCommand = cmd;
adp.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
getPlace obj = new getPlace();
obj.CPlace = dr["Place_Name"].ToString();
list.Add(obj);
}
return list;
}
catch (Exception e)
{
return new List<getPlace>();
}
}
//PHOTOS - INSERT
public static int PhotosInsert(PhotosAdd model)
{
int id = 0;//000-0000-0000-0000
DataProvider.ExecuteNonQuery(GetConnection, "dbo.Photos_Insert"
, inputParamMapper: delegate(SqlParameterCollection paramCollection)
{
paramCollection.AddWithValue("@URL", model.URL);
paramCollection.AddWithValue("@isPrimary", 0);
paramCollection.AddWithValue("@userId", model.userId);
SqlParameter p = new SqlParameter("@PhotoId", System.Data.SqlDbType.Int); //output parameter sending into proc to get value
p.Direction = System.Data.ParameterDirection.Output;
paramCollection.Add(p);
},
returnParameters: delegate(SqlParameterCollection param)
{
int.TryParse(param["@PhotoId"].Value.ToString(), out id);
}
);
return id;
}
public List<model1> getinfo(string placeName)
{
SqlConnection con = new SqlConnection();
List<model1> list = new List<model1>();
con.ConnectionString = @"Data Source=MOHIT-PC\SQLEXPRESS;Initial Catalog=travelogue;User ID=sa;Password=patel";
string query = "select intro,lat,long from Place_Detail where Place_Name = @PlaceName";
SqlCommand cmd = new SqlCommand(query, con);
SqlParameter p = new SqlParameter("@PlaceName", placeName);
cmd.Parameters.Add(p);
con.Open();
SqlDataAdapter adp = new SqlDataAdapter();
DataTable dt = new DataTable();
adp.SelectCommand = cmd;
adp.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
model1 m = new model1();
m.intro = dt.Rows[0]["intro"].ToString();
m.lat = dt.Rows[0]["lat"].ToString();
m.longi = dt.Rows[0]["long"].ToString();
list.Add(m);
}
con.Close();
return list;
}
public Sys.Model.Database.Negocios.TypFlx ListByName(Sys.Model.Database.Negocios.TypFlx model)
{
List <IDbDataParameter> listOfParameters = new System.Collections.Generic.List <IDbDataParameter>();
SqlParameter parameter = null;
parameter = new System.Data.SqlClient.SqlParameter("@TYPFLX", SqlDbType.VarChar)
{
Direction = ParameterDirection.Input,
Value = model.TypeFlow
};
listOfParameters.Add(parameter);
return(LoopDataReaderRows((SqlDataReader)ExecuteQuery("[Negocios].[Pr_TYPFLX_LIST002]", listOfParameters))?.ToList().FirstOrDefault());
}
public Sys.Model.Database.Usuarios.Credencials Delete(Sys.Model.Database.Usuarios.Credencials model)
{
List <IDbDataParameter> listOfParameters = new System.Collections.Generic.List <IDbDataParameter>();
SqlParameter parameter = null;
parameter = new System.Data.SqlClient.SqlParameter("@FK_USR", SqlDbType.VarChar)
{
Direction = ParameterDirection.Input,
Value = model.UserId
};
listOfParameters.Add(parameter);
return(LoopDataReaderRows((SqlDataReader)ExecuteQuery("[Usuarios].[Pr_CRED_DELETE]", listOfParameters)).LastOrDefault());
}
public System.Data.SqlClient.SqlDataReader GetPayrollByInstructor(DateTime startdate, DateTime enddate, bool isnull, DataSet1 ds)
{
try
{
System.Data.SqlClient.SqlCommand command = SqlHelper.CreateCommand(DAC.Connection, "GetPayrollByInstructor");
command.CommandType = CommandType.StoredProcedure;
//command.CommandText = sqlhelper.CreateMyCommand(DAC.ConnectionString, "InsertNewBanks", null);
{
System.Data.SqlClient.SqlParameter pStartDateTime = new System.Data.SqlClient.SqlParameter("@StartDateTime", System.Data.SqlDbType.DateTime);
pStartDateTime.Direction = ParameterDirection.Input;
if (!isnull)
{
pStartDateTime.Value = startdate;
}
else
{
pStartDateTime.Value = null;
}
command.Parameters.Add(pStartDateTime);
System.Data.SqlClient.SqlParameter pEndDateTime = new System.Data.SqlClient.SqlParameter("@EndDateTime", System.Data.SqlDbType.DateTime);
pEndDateTime.Direction = ParameterDirection.Input;
if (!isnull)
{
pEndDateTime.Value = enddate;
}
else
{
pEndDateTime.Value = null;
}
command.Parameters.Add(pEndDateTime);
}
if (DAC.Connection.State == ConnectionState.Closed)
{
DAC.Connection.Open();
}
System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
if (DAC.Connection.State == ConnectionState.Open)
{
DAC.Connection.Close();
}
return(reader);
}
catch (Exception ex)
{
throw ex;
}
}
protected void SignIn_Click(object sender, EventArgs e)
{
if (null != username.Text.Trim() && null != password.Text.Trim())
{
try
{
//根据输入框中的用户名来取出一个Dataset，搜索语句采用传参的方式
DataSet userDS = new DataSet();
string sql = "select * from Users where Dealer_Name = @name";
pdm.BLL.DealerInfo dealer = new pdm.BLL.DealerInfo();
System.Data.SqlClient.SqlParameter us = new System.Data.SqlClient.SqlParameter("@name", username.Text.Trim());
userDS = Maticsoft.DBUtility.DbHelperSQL.Query(sql, us);
//判断是否密码是否正确
if (userDS.Tables[0].Rows.Count > 0)
{
if (userDS.Tables[0].Rows[0]["Dealer_Psw"].ToString() == password.Text.Trim())
{
Session["UserID"] = userDS.Tables[0].Rows[0]["Dealer_ID"].ToString();
Session["UserName"] = userDS.Tables[0].Rows[0]["Dealer_Name"].ToString();
Session["UserLevel"] = userDS.Tables[0].Rows[0]["Dealer_Level"].ToString();
Session["Parent"] = userDS.Tables[0].Rows[0]["Parent_ID"].ToString();
if (int.Parse(userDS.Tables[0].Rows[0]["Parent_ID"].ToString()) == -1)
{
//添加操作日志
pdm.Model.UseLog log = new pdm.Model.UseLog();
log.Method = "login";
log.Time = DateTime.Now;
log.Dealer_ID = Convert.ToInt32(Session["userID"].ToString());
new pdm.BLL.UseLog().Add(log);
Response.Write("<script type='text/javascript'>alert('登陆成功');window.location.href='./SystemManagerForm/index.aspx';</script>");
}
else
{
Response.Write("<script type='text/javascript'>alert('登陆成功');window.location.href='./NormalUserForm/index.aspx'</script>");
}
}
else
{
Response.Write("<script type='text/javascript'>alert('请检查用户名或密码是否正确')</script>");
}
}
}
catch
{
Response.Write("<script type='text/javascript'>alert('登陆失败')</script>");
}
}
}
public DataTable Get_SlopchestReport_DL(int?VESSEL_ID, int?YEAR, int?MONTH)
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.SqlClient.SqlParameter[] obj = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@VESSEL_ID", VESSEL_ID),
new System.Data.SqlClient.SqlParameter("@YEAR", YEAR),
new System.Data.SqlClient.SqlParameter("@MONTH", MONTH)
};
System.Data.DataSet ds = SqlHelper.ExecuteDataset(_internalConnection, CommandType.StoredProcedure, "SLC_Get_Slopchest_Report", obj);
return(ds.Tables[0]);
}
public void Update_Progress(string QtnNumber)
{
try
{
System.Data.SqlClient.SqlParameter[] obj = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Qtnnumber", QtnNumber)
};
SqlHelper.ExecuteNonQuery(_internalConnection, CommandType.StoredProcedure, "PURC_WQTN_UPDATESENTRFQSTATUS", obj);
}
catch (Exception ex)
{
}
}
public DataTable Get_Delivery_History_DL(string ItemSystemcode, string Vesselcode)
{
DataTable dt = new DataTable();
System.Data.SqlClient.SqlParameter[] obj = new System.Data.SqlClient.SqlParameter[]
{
new SqlParameter("@ItemSubSystemcode", ItemSystemcode),
new SqlParameter("@VesselCode", Vesselcode)
};
DataSet ds = SqlHelper.ExecuteDataset(_internalConnection, CommandType.StoredProcedure, "PURC_Get_DeliveryHistory_Items", obj);
return(dt = ds.Tables[0]);
}
public Sys.Model.Database.Negocios.Tax ListByCompany(Sys.Model.Database.Negocios.Tax model)
{
List <IDbDataParameter> listOfParameters = new System.Collections.Generic.List <IDbDataParameter>();
SqlParameter parameter = null;
parameter = new System.Data.SqlClient.SqlParameter("@FK_EMPR", SqlDbType.Int)
{
Direction = ParameterDirection.Input,
Value = model.IdCompany
};
listOfParameters.Add(parameter);
return(LoopDataReaderRows((SqlDataReader)ExecuteQuery("[Negocios].[Pr_TAX_LIST001]", listOfParameters))?.ToList().FirstOrDefault());
}
public DataTable Get_WorklistReportWithAllGrouping_DL(int InspectionID)//,string TBODY)
{
System.Data.DataTable dt = new System.Data.DataTable();
//string @TBODY = "";
System.Data.SqlClient.SqlParameter[] obj = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@InspID", InspectionID),
};
//obj[obj.Length - 1].Direction = ParameterDirection.InputOutput;
System.Data.DataSet ds = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "INSP_Get_WorklistReportWithAllGroupingCHK", obj);
return(ds.Tables[0]);
}
public static DataTable QMSDBFoldes_ProcedureList(int?FleetId, int?VesselId, int?DepartmentId, int?UserId, int?FolderId)
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.SqlClient.SqlParameter[] obj = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@FLEET_ID", FleetId),
new System.Data.SqlClient.SqlParameter("@VESSEL_ID", VesselId),
new System.Data.SqlClient.SqlParameter("@DEPARTMENT_ID", DepartmentId),
new System.Data.SqlClient.SqlParameter("@USERID", UserId),
new System.Data.SqlClient.SqlParameter("@FOLDERID", FolderId)
};
System.Data.DataSet ds = SqlHelper.ExecuteDataset(DAL_QMSDB_Folders.ConnectionString, CommandType.StoredProcedure, "QMS_DB_GET_FOLDERPROCEDURELIST", obj);
return(ds.Tables[0]);
}
public Boolean UpdateSb_ProjectDetails()
{
try
{
System.Data.SqlClient.SqlParameter[] arrParameters = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Id", System.Data.DbType.Int32),
new System.Data.SqlClient.SqlParameter("@ProjectCategoryId", System.Data.DbType.Int32),
new System.Data.SqlClient.SqlParameter("@ProjectName", System.Data.DbType.String),
new System.Data.SqlClient.SqlParameter("@Cost", System.Data.DbType.Int32),
new System.Data.SqlClient.SqlParameter("@Features", System.Data.DbType.String),
new System.Data.SqlClient.SqlParameter("@Description", System.Data.DbType.String),
new System.Data.SqlClient.SqlParameter("@TechnologyId", System.Data.DbType.Int32),
new System.Data.SqlClient.SqlParameter("@Demo", System.Data.DbType.Boolean),
new System.Data.SqlClient.SqlParameter("@IsCustomizable", System.Data.DbType.Boolean),
new System.Data.SqlClient.SqlParameter("@Screenshot", System.Data.DbType.String),
new System.Data.SqlClient.SqlParameter("@Status", System.Data.DbType.Boolean),
new System.Data.SqlClient.SqlParameter("@IsFree", System.Data.DbType.Boolean),
new System.Data.SqlClient.SqlParameter("@CreatedId", System.Data.DbType.Int32),
};
arrParameters[0].Value = Id;
arrParameters[1].Value = ProjectCategoryId;
arrParameters[2].Value = ProjectName;
arrParameters[3].Value = Cost;
arrParameters[4].Value = Features;
arrParameters[5].Value = Description;
arrParameters[6].Value = Technology;
arrParameters[7].Value = Demo;
arrParameters[8].Value = IsCustomizable;
arrParameters[9].Value = Screenshot;
arrParameters[10].Value = Status;
arrParameters[11].Value = IsFree;
arrParameters[12].Value = CreatedId;
string sqlConnectionString = ConfigurationManager.AppSettings["sqlConnectionString"].ToString();
int i = SqlHelper.ExecuteNonQuery(sqlConnectionString, System.Data.CommandType.StoredProcedure, "Sb_ProjectDetails_UpdateRecord", arrParameters);
if (i >= 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable registerUser(string username, string surname, string useremail, string nickname, string password)
{
SqlCommand cmd = new SqlCommand();
System.Data.DataSet ds = new System.Data.DataSet();
try
{
connectp.myConnection2.Open();
System.Data.SqlClient.SqlParameter wParam1 = new System.Data.SqlClient.SqlParameter("@username", System.Data.SqlDbType.VarChar, 50);
wParam1.Value = username;
cmd.Parameters.Add(wParam1);
System.Data.SqlClient.SqlParameter wParam2 = new System.Data.SqlClient.SqlParameter("@surname", System.Data.SqlDbType.VarChar, 50);
wParam2.Value = surname;
cmd.Parameters.Add(wParam2);
System.Data.SqlClient.SqlParameter wParam3 = new System.Data.SqlClient.SqlParameter("@useremail", System.Data.SqlDbType.VarChar, 50);
wParam3.Value = useremail;
cmd.Parameters.Add(wParam3);
System.Data.SqlClient.SqlParameter wParam4 = new System.Data.SqlClient.SqlParameter("@nickname", System.Data.SqlDbType.VarChar, 50);
wParam4.Value = nickname;
cmd.Parameters.Add(wParam4);
System.Data.SqlClient.SqlParameter wParam5 = new System.Data.SqlClient.SqlParameter("@password", System.Data.SqlDbType.VarChar, 50);
wParam5.Value = password;
cmd.Parameters.Add(wParam5);
cmd.Connection = connectp.myConnection2;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "spRegisterUser";
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds, "Table");
}
finally
{
connectp.myConnection2.Close();
}
DataTableCollection dc = ds.Tables;
DataTable dTable = dc[0];
return(dTable);
}
public static int Insert_RestHours_Rules(string description, int?Rule_Value, int?Rule_Period, string rule_unit, int?Rules_TYPE, int?userid)
{
System.Data.SqlClient.SqlParameter[] obj = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@RULE_DESCRIPTION", description),
new System.Data.SqlClient.SqlParameter("@Rule_Value", Rule_Value),
new System.Data.SqlClient.SqlParameter("@Rule_Period", Rule_Period),
new System.Data.SqlClient.SqlParameter("@Rule_Unit", rule_unit),
new System.Data.SqlClient.SqlParameter("@Rules_TYPE", Rules_TYPE),
new System.Data.SqlClient.SqlParameter("@Created_By", userid),
};
return(SqlHelper.ExecuteNonQuery(_internalConnection, CommandType.StoredProcedure, "CRW_INS_RESTHOUR_RULES", obj));
}
public static DataSet Get_DeckLogBook_Thrashold_List(int Vessel_ID, int?LogBookId, int?Threshold_Id)
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.SqlClient.SqlParameter[] obj = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Vessel_ID", Vessel_ID),
new System.Data.SqlClient.SqlParameter("@LogBookId", LogBookId),
new System.Data.SqlClient.SqlParameter("@Threshold_Id", Threshold_Id),
};
System.Data.DataSet ds = SqlHelper.ExecuteDataset(_internalConnection, CommandType.StoredProcedure, "OPS_PR_DeckLog_Book_Threshold_List", obj);
return(ds);
}
public Sys.Model.Database.Usuarios.User ListById(Sys.Model.Database.Usuarios.User model)
{
List <IDbDataParameter> listOfParameters = new System.Collections.Generic.List <IDbDataParameter>();
SqlParameter parameter = null;
parameter = new System.Data.SqlClient.SqlParameter("@PK_USR", SqlDbType.Int)
{
Direction = ParameterDirection.Input,
Value = model.Id
};
listOfParameters.Add(parameter);
return(LoopDataReaderRows((SqlDataReader)ExecuteQuery("[Usuarios].[Pr_USR_LIST001]", listOfParameters))?.ToList().FirstOrDefault());
}
public static int QMSDBProcedure_Insert_UserAccess(int?AccesUserId, int?ProcedureId, int CanView, int CanAdd, int CanEdit, int CanDelete, int?UserId)
{
System.Data.SqlClient.SqlParameter[] obj = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@ACC_USERID", AccesUserId),
new System.Data.SqlClient.SqlParameter("@PROCEDURE_ID", ProcedureId),
new System.Data.SqlClient.SqlParameter("@ACCESS_VIEW", CanView),
new System.Data.SqlClient.SqlParameter("@ACCESS_ADD", CanAdd),
new System.Data.SqlClient.SqlParameter("@ACCESS_EDIT", CanEdit),
new System.Data.SqlClient.SqlParameter("@ACCESS_DELETE", CanDelete),
new System.Data.SqlClient.SqlParameter("@USERID", UserId)
};
return(SqlHelper.ExecuteNonQuery(DAL_QMSDB_Folders.ConnectionString, CommandType.StoredProcedure, "QMS_DB_INS_USER_PROCEDURE", obj));
}
public DataSet Purc_Get_ConfiguredPO_type(string userid, string Reqsn_Types)
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.SqlClient.SqlParameter[] obj = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@reqsnTypes", Reqsn_Types),
new System.Data.SqlClient.SqlParameter("@UserID", userid)
};
obj[obj.Length - 1].Direction = ParameterDirection.Input;
System.Data.DataSet ds = SqlHelper.ExecuteDataset(_internalConnection, CommandType.StoredProcedure, "Purc_Get_Configured_AccountCode", obj);
return(ds);
}
public Sys.Model.Database.Aplicativos.Scope ListByName(Sys.Model.Database.Aplicativos.Scope model)
{
List <IDbDataParameter> listOfParameters = new System.Collections.Generic.List <IDbDataParameter>();
SqlParameter parameter = null;
parameter = new System.Data.SqlClient.SqlParameter("@NOME", SqlDbType.VarChar)
{
Direction = ParameterDirection.Input,
Value = model.Name
};
listOfParameters.Add(parameter);
return(LoopDataReaderRows((SqlDataReader)ExecuteQuery("[Aplicativos].[Pr_SCOP_LIST001]", listOfParameters))?.ToList().FirstOrDefault());
}
protected override IDbDataParameter CreateParameter(String name, DataType dataType)
{
var p = new System.Data.SqlClient.SqlParameter(name, dataType.DbType.SqlServerDbType.Value);
if (dataType is SqlInputParameter dType)
{
p.Direction = dType.ParameterDirection;
}
if (p.Direction != ParameterDirection.Output)
{
p.Value = this.GetParameterValue(dataType, dataType.DbType.SqlServerDbType.Value);
}
return(p);
}
public Sys.Model.Database.Negocios.Empresa ListByFantasyName(Sys.Model.Database.Negocios.Empresa model)
{
List <IDbDataParameter> listOfParameters = new System.Collections.Generic.List <IDbDataParameter>();
SqlParameter parameter = null;
parameter = new System.Data.SqlClient.SqlParameter("@NOME_FATS", SqlDbType.VarChar)
{
Direction = ParameterDirection.Input,
Value = model.FantasyName
};
listOfParameters.Add(parameter);
return(LoopDataReaderRows((SqlDataReader)ExecuteQuery("[Negocios].[Pr_EMPR_LIST003]", listOfParameters))?.ToList().FirstOrDefault());
}
public Sys.Model.Database.Aplicativos.Client ListByUniqueKey(Sys.Model.Database.Aplicativos.Client model)
{
List <IDbDataParameter> listOfParameters = new System.Collections.Generic.List <IDbDataParameter>();
SqlParameter parameter = null;
parameter = new System.Data.SqlClient.SqlParameter("@UNIQ_KEY", SqlDbType.VarChar)
{
Direction = ParameterDirection.Input,
Value = model.UniqueKey
};
listOfParameters.Add(parameter);
return(LoopDataReaderRows((SqlDataReader)ExecuteQuery("[Aplicativos].[Pr_CLIT_LIST002]", listOfParameters))?.ToList().FirstOrDefault());
}
public void AddSqlOutputParameter(string ParameterName, SqlDbType DbType, int Size)
{
System.Data.SqlClient.SqlParameter stParam = null;
if (DbType == SqlDbType.NVarChar | DbType == SqlDbType.VarChar | DbType == SqlDbType.NChar | DbType == SqlDbType.Char)
{
stParam = new System.Data.SqlClient.SqlParameter(ParameterName, DbType, Size);
}
else
{
stParam = new System.Data.SqlClient.SqlParameter(ParameterName, DbType);
}
stParam.Direction = ParameterDirection.Output;
InputParams.Add(stParam);
}
public void Delete(Sys.Model.Database.Negocios.Finac model)
{
List <IDbDataParameter> listOfParameters = new System.Collections.Generic.List <IDbDataParameter>();
SqlParameter parameter = null;
parameter = new System.Data.SqlClient.SqlParameter("@PK_FINAC", SqlDbType.VarChar)
{
Direction = ParameterDirection.Input,
Value = model.Id
};
listOfParameters.Add(parameter);
ExecuteQuery("[Negocios].[Pr_FINAC_DELETE]", listOfParameters);
}
public Sys.Model.Database.Usuarios.Credencials ListByLogin(Sys.Model.Database.Usuarios.Credencials model)
{
List <IDbDataParameter> listOfParameters = new System.Collections.Generic.List <IDbDataParameter>();
SqlParameter parameter = null;
parameter = new System.Data.SqlClient.SqlParameter("@LOGIN", SqlDbType.VarChar)
{
Direction = ParameterDirection.Input,
Value = model.Login
};
listOfParameters.Add(parameter);
return(LoopDataReaderRows((SqlDataReader)ExecuteQuery("[Usuarios].[Pr_CRED_LIST002]", listOfParameters))?.ToList().FirstOrDefault());
}
/// <summary>
/// Формирование SqlCommand путем создания параметров из строк в qt.Params
/// и текста в qt.Text
/// </summary>
/// <param name="qt">Входные данные SqlQueryText</param>
/// <param name="conn">Подключение SqlConnection, связываемое с SqlCommand</param>
/// <returns>SqlCommand</returns>
private static SqlCommand GetSqlCommand(SqlQueryText qt, SqlConnection conn)
{
SqlCommand cmd = new SqlCommand(qt.Text, conn);
foreach (ParameterText param in qt.Params)
{
System.Data.SqlClient.SqlParameter sqlParam = cmd.CreateParameter();
sqlParam.ParameterName = param.Name;
object val = GetObject(param.Value, param.SqlType);
sqlParam.Value = val;
cmd.Parameters.Add(sqlParam);
}
return(cmd);
}
public static int POLog_Insert_Delivery_Item(string Delivery_ID, int?Supply_ID, DataTable dtItem, int?CreatedBy)
{
System.Data.SqlClient.SqlParameter[] obj = new System.Data.SqlClient.SqlParameter[]
{
new System.Data.SqlClient.SqlParameter("@Delivery_ID", Delivery_ID),
new System.Data.SqlClient.SqlParameter("@Supply_ID", Supply_ID),
new System.Data.SqlClient.SqlParameter("@dtExtraItems", dtItem),
new System.Data.SqlClient.SqlParameter("@Created_By", CreatedBy),
//new System.Data.SqlClient.SqlParameter("RETURN",SqlDbType.Int)
};
//obj[obj.Length - 1].Direction = ParameterDirection.ReturnValue;
return((int)SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "POLOG_INS_Delivery_Item_Details", obj));
//return Convert.ToInt32(obj[obj.Length - 1].Value);
}
//Register For Classes: INSERT
public void RegisterForClass(int StudentID, int ClassID)
{
//Get my configured connection
System.Data.SqlClient.SqlConnection objCon = GetConnection();
//Configure my command
System.Data.SqlClient.SqlCommand objCmd;
objCmd = new System.Data.SqlClient.SqlCommand();
objCmd.Connection = objCon;
objCmd.CommandType = System.Data.CommandType.StoredProcedure;
objCmd.CommandText = "pInsClassStudents";
//Setup the parameters
System.Data.SqlClient.SqlParameter objRC;
objRC = new System.Data.SqlClient.SqlParameter();
objRC.ParameterName = "@RC";
objRC.SqlDbType = System.Data.SqlDbType.Int;
objRC.Direction = System.Data.ParameterDirection.ReturnValue;
objCmd.Parameters.Add(objRC);
System.Data.SqlClient.SqlParameter objStudentID;
objStudentID = new System.Data.SqlClient.SqlParameter();
objStudentID.ParameterName = "@StudentId";
objStudentID.SqlDbType = System.Data.SqlDbType.Int;
objStudentID.Direction = System.Data.ParameterDirection.Input;
objStudentID.Value = StudentID;
objCmd.Parameters.Add(objStudentID);
System.Data.SqlClient.SqlParameter objClassID;
objClassID = new System.Data.SqlClient.SqlParameter();
objClassID.ParameterName = "@ClassId";
objClassID.SqlDbType = System.Data.SqlDbType.Int;
objClassID.Direction = System.Data.ParameterDirection.Input;
objClassID.Value = ClassID;
objCmd.Parameters.Add(objClassID);
try
{
objCon.Open();
objCmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
objCon.Close();
}
}
public ActionResult breadcrumbs_tree()
{
int currentClass = 0;
if (TempData["currentClass"] != null)
{
currentClass = int.Parse(TempData["currentClass"].ToString());
TempData.Keep();
}
System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter("currentClass", currentClass);
var I_classes = StoreDB.Database.SqlQuery <I_class>("GetBreadcrumbs_tree @currentClass", param);
return(PartialView(I_classes));
}
/// <summary>
/// Insere ou Altera os dados da Causa de Anomalia no Banco
/// </summary>
/// <param name="aca">Causa de Anomalia</param>
/// <param name="usu_id">Id do Usuário Logado</param>
/// <param name="ip">IP do Usuário Logado</param>
/// <returns>int</returns>
public int AnomCausa_Salvar(AnomCausa aca, int usu_id, string ip)
{
try
{
using (SqlConnection con = new SqlConnection(strConn))
{
con.Open();
SqlCommand com = new SqlCommand();
if (aca.aca_id > 0)
{
com.CommandText = "STP_UPD_ANOM_CAUSA";
}
else
{
com.CommandText = "STP_INS_ANOM_CAUSA";
}
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Clear();
System.Data.SqlClient.SqlParameter p_return = new System.Data.SqlClient.SqlParameter();
p_return.Direction = System.Data.ParameterDirection.ReturnValue;
com.Parameters.Add(p_return);
com.Parameters[0].Size = 32000;
if (aca.aca_id > 0)
{
com.Parameters.AddWithValue("@aca_id", aca.aca_id);
}
com.Parameters.AddWithValue("@leg_id", aca.leg_id);
com.Parameters.AddWithValue("@leg_codigo", aca.leg_codigo);
//com.Parameters.AddWithValue("@aca_codigo", aca.aca_codigo);
com.Parameters.AddWithValue("@aca_descricao", aca.aca_descricao);
com.Parameters.AddWithValue("@aca_ativo", aca.aca_ativo);
com.Parameters.AddWithValue("@usu_id", usu_id);
com.Parameters.AddWithValue("@ip", ip);
com.ExecuteScalar();
return(Convert.ToInt32(p_return.Value));
}
}
catch (Exception ex)
{
int id = 0;
new LogSistemaDAO().InserirLogErro(new LogErro(ex, this.GetType().Name, new StackTrace().GetFrame(0).GetMethod().Name), out id);
throw new Exception(ex.Message);
}
}
public List <Sys.Model.Database.Aplicativos.ClitScopes> ListById(Sys.Model.Database.Aplicativos.ClitScopes model)
{
List <IDbDataParameter> listOfParameters = new System.Collections.Generic.List <IDbDataParameter>();
SqlParameter parameter = null;
parameter = new System.Data.SqlClient.SqlParameter("@FK_APP", SqlDbType.VarChar)
{
Direction = ParameterDirection.Input,
Value = model.ClientId
};
listOfParameters.Add(parameter);
return(LoopDataReaderRows((SqlDataReader)ExecuteQuery("[Aplicativos].[Pr_CLITSCOP_LIST001]", listOfParameters))?.ToList());
}