public String updt_status(string GENERATE_ID
, int prev_STATUS_ID
, int next_STATUS_ID
, int MODE
)
{
try
{
DataSet ds = new DataSet();
string strConn = GetConnectionString();
string select = " exec sp_updt_status "
+ "'" + GENERATE_ID + "'"
+ ", " + prev_STATUS_ID
+ ", " + next_STATUS_ID
+ ", " + MODE
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
da.Fill(ds, "updt_status");
return(ds.Tables[0].Rows[0][0].ToString());
}
catch (SqlException ex)
{
return("Error : " + ex.ToString());
}
}
public IDictionary <string, object> ProcForDictionary(string sqlStatement, IDictionary <string, object> paramIN = null, IDictionary <string, TypeCode> paramOUT = null)
{
IDictionary <string, object> Result = new Dictionary <string, object>();
using (SqlDataAdapter sqlData = new SqlDataAdapter(this.GetCommandProc(sqlStatement, paramIN, paramOUT)))
{
DataSet dataSet = new DataSet();
int nCount = sqlData.Fill(dataSet);
if (nCount > 0)
{
Result["DATA"] = dataSet;
}
IDataParameter[] aResultParams = sqlData.GetFillParameters();
if (paramOUT != null && paramOUT.Count > 0 && aResultParams != null && aResultParams.Length > 0)
{
IList <IDataParameter> ListDataOut = aResultParams.Where(p => paramOUT.Keys.Contains(p.ParameterName)).ToList();
if (ListDataOut != null && ListDataOut.Count > 0)
{
foreach (IDataParameter DataOut in ListDataOut)
{
Result[DataOut.ParameterName] = DataOut.Value;
}
}
}
}
return(Result);
}
public void Button2_Click(object sender, EventArgs e)
{
string id = login_email.Text;
string password = login_password.Text;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("select * from [User] where LoginID=\'" + id + "\' and Password=\'" + password + "\' and IsActive=\'1\'", con);
sda.GetFillParameters();
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count >= 1)
{
//Session["username"] = DataBinder.Eval(e.Item.DataItem, "customer_ID").ToString();
using (SqlCommand command = new SqlCommand("select * from [User] where LoginID=\'" + id + "\' and Password=\'" + password + "\' and IsActive=\'1\'", con))
{
con.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string roleid = reader.GetInt32(1).ToString();
string username = reader.GetString(2);
string loginid = reader.GetString(3);
}
}
Response.Redirect("~/Homepage.aspx");
}
else
{
Response.Redirect("UserLogin.aspx");
}
}
public List <ReceitaModel> consultaTodasReceita()
{
List <ReceitaModel> lista = new List <ReceitaModel>();
SqlDataAdapter sqlConsulta = new SqlDataAdapter("SELECT * FROM Receita", conexao.conectarBanco());
DataTable dt = new DataTable();
sqlConsulta.GetFillParameters();
sqlConsulta.Fill(dt);
foreach (DataRow linha in dt.Rows)
{
ReceitaModel receita = new ReceitaModel();
receita.informacaoNutricional = new InformacaoNutricionalModel();
receita.idReceita = Convert.ToInt32(linha["idReceita"]);
receita.tituloReceita = Convert.ToString(linha["tituloReceita"]);
receita.modoPreparo = Convert.ToString(linha["modoPreparo"]);
if (!linha["imagem"].ToString().Equals(""))
{
receita.imagem = (byte[])linha["imagem"];
//Converte imagem para base 64
string imageBase64Data = Convert.ToBase64String(receita.imagem);
receita.imagemArrayBytes = string.Format("data:image/png;base64,{0}", imageBase64Data);
}
receita.informacaoNutricional.calorias = Convert.ToDecimal(linha["calorias"]);
lista.Add(receita);
}
return(lista);
}
public String ins_dokumen_pengajuan(string GENERATE_ID
, int DOC_TYPE_ID
, string DOC_NAME_ORI
, string DOC_NAME_MOD
, string DOC_LOC
, string ENTRY_USER)
{
try
{
DataSet ds = new DataSet();
string strConn = GetConnectionString();
string select = " exec sp_ins_dokumen_pengajuan "
+ "'" + GENERATE_ID + "'"
+ ", " + DOC_TYPE_ID
+ ", " + "'" + DOC_NAME_ORI + "'"
+ ", " + "'" + DOC_NAME_MOD + "'"
+ ", " + "'" + DOC_LOC + "'"
+ ", " + "'" + ENTRY_USER + "'"
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
da.Fill(ds, "ins_dokumen_pengajuan");
return(ds.Tables[0].Rows[0][0].ToString());
}
catch (SqlException ex)
{
return("Error : " + ex.ToString());
}
}
public List <IngredienteModel> consultaIngrendientePorIdReceita(int idReceita)
{
SqlDataAdapter sqlConsulta = new SqlDataAdapter("SELECT * FROM Ingrediente i where i.idReceita = @idReceita", conexao.conectarBanco());
DataTable dt = new DataTable();
sqlConsulta.SelectCommand.Parameters.AddWithValue("@idReceita", idReceita);
sqlConsulta.GetFillParameters();
sqlConsulta.Fill(dt);
List <IngredienteModel> lista = new List <IngredienteModel>();
foreach (DataRow linha in dt.Rows)
{
IngredienteModel ingrediente = new IngredienteModel();
ingrediente.idReceita = Convert.ToInt32(linha["idReceita"]);
ingrediente.nomeIngrediente = Convert.ToString(linha["nomeIngrediente"]);
ingrediente.qtda = Convert.ToString(linha["qtda"]);
lista.Add(ingrediente);
}
return(lista);
}
public void GetFillParametersTest()
{
string query = "select id, type_bit from numeric_family where id > @param1";
adapter = new SqlDataAdapter(query, connectionString);
IDataParameter[] param = adapter.GetFillParameters();
Assert.AreEqual(0, param.Length, "#1 size shud be 0");
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@param1";
param1.Value = 2;
adapter.SelectCommand.Parameters.Add(param1);
param = adapter.GetFillParameters();
Assert.AreEqual(1, param.Length, "#2 count shud be 1");
Assert.AreEqual(param1, param[0], "#3 Params shud be equal");
}
private void button8_Click(object sender, EventArgs e)
{
DataSet DS = new DataSet();
SqlDataAdapter DA = new SqlDataAdapter();
DA.SelectCommand = new SqlCommand("select a.cod_produto, b.des_produto, a.ITP_QTDE, a.ITP_VALOR, a.ITP_TOTAL from ITEMPEDIDO a join produtos b on a.cod_produto = b.cod_produto where ped_codigo = "
+ txtControle.Text, new SqlConnection(Properties.Settings.Default.Ducaun));
DA.GetFillParameters();
dataGridView1.DataSource = DA.SelectCommand;
//DA.SelectCommand = new SqlCommand("select a.cod_produto, b.des_produto, a.ITP_QTDE, a.ITP_VALOR, a.ITP_TOTAL from ITEMPEDIDO a join produtos bon a.cod_produto = b.cod_produto where ped_codigo = "
// +txtControle.Text, new SqlConnection(Properties.Settings.Default.Ducaun));
}
public DataSet get_news_type(int NEWS_TYPE)
{
string strConn = GetConnectionString();
string select = " exec sp_get_news_type " + NEWS_TYPE
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
DataSet ds = new DataSet();
da.Fill(ds, "get_news_type");
return(ds);
}
public DataSet get_ddl_list_premium_type(string NO_POLIS)
{
string strConn = GetConnectionString();
string select = " exec sp_get_ddl_list_premium_type "
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
DataSet ds = new DataSet();
da.Fill(ds, "get_ddl_list_premium_type");
return(ds);
}
public DataSet get_ddl_list_jenis_dokumen_pengajuan()
{
string strConn = GetConnectionString();
string select = " exec sp_get_ddl_list_jenis_dokumen_pengajuan "
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
DataSet ds = new DataSet();
da.Fill(ds, "get_ddl_list_jenis_dokumen_pengajuan");
return(ds);
}
public IDataParameter[] GetFillParameters()
{
IDataParameter[] ida;
if (SDA != null)
{
ida = SDA.GetFillParameters();
}
else
{
ida = ODA.GetFillParameters();
}
return(ida);
}
public DataSet get_ddl_polis_login_userid(string username)
{
string strConn = GetConnectionString();
string select = " exec sp_get_ddl_polis_login_userid "
+ "'" + username + "'"
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
DataSet ds = new DataSet();
da.Fill(ds, "get_ddl_polis_login_userid");
return(ds);
}
public DataSet get_data_detail_pengajuan(string NO_PENGAJUAN)
{
string strConn = GetConnectionString();
string select = " exec sp_get_data_detail_pengajuan "
+ "'" + NO_PENGAJUAN + "'"
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
DataSet ds = new DataSet();
da.Fill(ds, "get_data_detail_pengajuan");
return(ds);
}
public static DataSet BindTrash(string Uname)
{
SqlCommand cmd = new SqlCommand("BindTrash", con);
cmd.Parameters.AddWithValue("@username", Uname);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.GetFillParameters();
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
public DataSet get_userid_info(string username, string password)
{
string strConn = GetConnectionString();
string select = " exec sp_get_userid_info "
+ "'" + username + "'"
+ ", " + "'" + password + "'"
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
DataSet ds = new DataSet();
da.Fill(ds, "get_ddl_list_jenis_dokumen_pengajuan");
return(ds);
}
public bool validate_userpass(string user, string pass)
{
DataSet ds = new DataSet();
string strConn = GetConnectionString();
string select = " exec sp_validate_userpass "
+ "'" + user + "'"
+ ",'" + pass + "'"
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
da.Fill(ds, "validate_userpass");
return(Convert.ToBoolean(ds.Tables[0].Rows[0][0]));
}
public void Button2_Click(object sender, EventArgs e)
{
string id = login_email.Text;
string password = login_password.Text;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("select * from [User] where LoginID=\'" + id + "\' and Password=\'" + password + "\' and IsActive=\'1\'", con);
sda.GetFillParameters();
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count >= 1)
{
string roleid = null, username = null, loginid = null;
//Session["username"] = DataBinder.Eval(e.Item.DataItem, "customer_ID").ToString();
using (SqlCommand command = new SqlCommand("select * from [User] where LoginID=\'" + id + "\' and Password=\'" + password + "\' and IsActive=\'1\'", con))
{
con.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
roleid = reader["RoleID"].ToString();
username = reader["Username"].ToString();
loginid = reader["ID"].ToString();
}
}
Session["username"] = username;
Session["userID"] = loginid;
if (roleid == "1")
{
Response.Redirect("Homepage.aspx");
}
else
{
Response.Redirect("AdminPanel.aspx");
}
}
else
{
//Response.Redirect("UserLogin.aspx");
ScriptManager.RegisterStartupScript(this, GetType(), "alertMessage", "alert('Check username/password. If error persists contact the administrator <*****@*****.**>');", true);
}
}
protected void Page_Load(object sender, EventArgs e)
{
string connetionString; //connection
SqlConnection cnn; //connection
connetionString = WebConfigurationManager.ConnectionStrings["constr"].ConnectionString;
cnn = new SqlConnection(connetionString);
cnn.Open();
SqlDataAdapter sqlData = new SqlDataAdapter("SELECT A.description as d1,A.location,AP.link,AV.link,U.first_name,U.middle_name,U.last_name,E.description,E.location,E.city,E.time,E.entertainer,EP.link,EV.link FROM Advertisement A inner join [User] U on U.ID=A.viewer_id left outer join Ads_Photos_Link AP on AP.advertisement_id=A.id left outer join Ads_Video_Link AV on AV.advertisement_id=A.id left outer join Event E on E.id=A.event_id left outer join Event_Photos_Link EP on EP.event_id=E.id left outer join Event_Videos_Link EV on EV.event_id =E.id ", cnn); //CheckThat
sqlData.GetFillParameters();
DataTable dataTable = new DataTable();
sqlData.Fill(dataTable);
advertisements.DataSource = dataTable;
advertisements.DataBind();
}
public DataSet get_list_pengajuan_underwriting(string NO_POLIS, string NO_PENGAJUAN, string NAMA, string PERIODE_MASUK_DARI, string PERIODE_MASUK_SAMPAI, string NO_BATCH)
{
string strConn = GetConnectionString();
string select = " exec sp_get_list_pengajuan_underwriting "
+ "'" + NO_POLIS + "'"
+ "," + "'" + NO_PENGAJUAN + "'"
+ "," + "'" + NAMA + "'"
+ "," + "'" + PERIODE_MASUK_DARI + "'"
+ "," + "'" + PERIODE_MASUK_SAMPAI + "'"
+ "," + "'" + NO_BATCH + "'"
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
DataSet ds = new DataSet();
da.Fill(ds, "get_list_pengajuan_underwriting");
return(ds);
}
protected void Page_Load(object sender, EventArgs e)
{
string connetionString; //connection
SqlConnection cnn; //connection
connetionString = WebConfigurationManager.ConnectionStrings["constr"].ConnectionString;
cnn = new SqlConnection(connetionString);
cnn.Open();
int ID = Convert.ToInt32(Session["ID"]);
int NotifID = 0;
SqlCommand cm = new SqlCommand("getNotifiedID", cnn);
cm.CommandType = System.Data.CommandType.StoredProcedure;
cm.Parameters.Add(new SqlParameter("@ID", ID));
cm.Parameters.Add("@notifID", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;
using (SqlDataReader rdrid = cm.ExecuteReader())
{
NotifID = Convert.ToInt32(cm.Parameters["@notifID"].Value);
}
if (NotifID == 0)
{
Label1.Text = "" + NotifID;
}
else
{
SqlCommand cmd = new SqlCommand("updateSeen", cnn);
cmd.Parameters.Add(new SqlParameter("@ID", NotifID));
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader rdr = cmd.ExecuteReader();
cnn.Close();
cnn.Open();
String query = "Show_Notification " + ID;
SqlDataAdapter sqlData = new SqlDataAdapter(query, cnn);
sqlData.GetFillParameters();
DataTable dataTable = new DataTable();
sqlData.Fill(dataTable);
view.DataSource = dataTable;
view.DataBind();
}
cnn.Close();
}
//Métodos de consulta -------------------------------------------------------------
public ReceitaModel consultaReceitaEspecifica(string tituloReceita)
{
ReceitaModel receita = new ReceitaModel();
SqlDataAdapter sqlConsulta = new SqlDataAdapter("SELECT * FROM Receita r where r.tituloReceita = @tituloReceita", conexao.conectarBanco());
DataTable dt = new DataTable();
sqlConsulta.SelectCommand.Parameters.AddWithValue("@tituloReceita", tituloReceita);
sqlConsulta.GetFillParameters();
sqlConsulta.Fill(dt);
foreach (DataRow linha in dt.Rows)
{
receita.idReceita = Convert.ToInt32(linha["idReceita"]);
receita.tituloReceita = Convert.ToString(linha["tituloReceita"]);
receita.modoPreparo = Convert.ToString(linha["modoPreparo"]);
}
return(receita);
}
protected void Page_Load(object sender, EventArgs e)
{
string connectionString;
SqlConnection cnn;
connectionString = WebConfigurationManager.ConnectionStrings["constr"].ConnectionString;
cnn = new SqlConnection(connectionString);
cnn.Open();
SqlDataAdapter sqlData = new SqlDataAdapter("Select E.description,E.location,E.city,E.time,E.entertainer,U.first_name,U.middle_name,U.last_name,EP.link,EV.link from Event E inner join [User] U on U.ID=E.viewer_id left outer join Event_Photos_Link EP on EP.event_id=E.id left outer join Event_Videos_Link EV on EV.event_id=E.id", cnn);
sqlData.GetFillParameters();
DataTable dataTable = new DataTable();
sqlData.Fill(dataTable);
view.DataSource = dataTable;
view.DataBind();
cnn.Close();
}
public static DataSet get_list(string iSql)
{
try
{
string strConn = GetConnectionString();
string select = iSql;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
DataSet ds = new DataSet();
da.Fill(ds, "get_list");
return(ds);
}
catch (Exception ex)
{
//System.Diagnostics.EventLog.WriteEntry("New Agency", "ERROR at get_list with message : " + ex.Message);
return(null);
}
}
public ReceitaModel consultaCaloriaReceita(string tituloReceita)
{
ReceitaModel receita = new ReceitaModel();
SqlDataAdapter sqlConsulta = new SqlDataAdapter("SELECT * FROM Receita r where r.tituloReceita = @tituloReceita", conexao.conectarBanco());
DataTable dt = new DataTable();
sqlConsulta.SelectCommand.Parameters.AddWithValue("@tituloReceita", tituloReceita);
sqlConsulta.GetFillParameters();
sqlConsulta.Fill(dt);
receita.informacaoNutricional = new InformacaoNutricionalModel();
foreach (DataRow linha in dt.Rows)
{
receita.informacaoNutricional.calorias = Convert.ToInt32(linha["calorias"]);
}
return(receita);
}
public string del_dokumen_pengajuan(string NO_PENGAJUAN, string NAMA_DOKUMEN)
{
try
{
DataSet ds = new DataSet();
string strConn = GetConnectionString();
string select = " exec sp_del_dokumen_pengajuan "
+ "'" + NO_PENGAJUAN + "'"
+ ", " + "'" + NAMA_DOKUMEN + "'"
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
da.Fill(ds, "del_dokumen_pengajuan");
return(ds.Tables[0].Rows[0][0].ToString());
}
catch (SqlException ex)
{
return("Error : " + ex.ToString());
}
}
public ReceitaModel consultaReceitaPorId(int idReceita)
{
ReceitaModel receita = new ReceitaModel();
SqlDataAdapter sqlConsulta = new SqlDataAdapter("SELECT * FROM Receita r where r.idReceita = @idReceita", conexao.conectarBanco());
DataTable dt = new DataTable();
sqlConsulta.SelectCommand.Parameters.AddWithValue("@idReceita", idReceita);
sqlConsulta.GetFillParameters();
sqlConsulta.Fill(dt);
receita.informacaoNutricional = new InformacaoNutricionalModel();
foreach (DataRow linha in dt.Rows)
{
receita.idReceita = Convert.ToInt32(linha["idReceita"]);
receita.tituloReceita = Convert.ToString(linha["tituloReceita"]);
receita.modoPreparo = Convert.ToString(linha["modoPreparo"]);
if (!linha["imagem"].ToString().Equals(""))
{
receita.imagem = (byte[])linha["imagem"];
//Converte imagem para base 64
string imageBase64Data = Convert.ToBase64String(receita.imagem);
receita.imagemArrayBytes = string.Format("data:image/png;base64,{0}", imageBase64Data);
}
receita.informacaoNutricional.calorias = Convert.ToInt32(linha["calorias"]);
receita.informacaoNutricional.carboidratos = Convert.ToDecimal(linha["carboidratos"]);
receita.informacaoNutricional.gordurasTotais = Convert.ToDecimal(linha["gordurasTotais"]);
receita.informacaoNutricional.gordurasSaturadas = Convert.ToDecimal(linha["gordurasSaturadas"]);
receita.informacaoNutricional.fibra = Convert.ToDecimal(linha["fibra"]);
receita.informacaoNutricional.sodio = Convert.ToDecimal(linha["sodio"]);
receita.informacaoNutricional.acucar = Convert.ToDecimal(linha["acucar"]);
}
return(receita);
}
public System.Data.IDataParameter [] GetFillParameters()
{
return(_da.GetFillParameters());
}
public DataTable Buscar(string tipoPesquisa, string pesquisa, string strConn)
{
#region Declaração
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataTable dt = null;
string sql = string.Empty;
#endregion
#region Implementação
switch (tipoPesquisa)
{
default:
sql = @"SELECT funci.idFuncionario, funci.nome, funci.dataNascimento, funci.email, funci.cpf, funci.rg, funci.ctps, funci.ctpsSerie, funci.telefone, funci.celular,
funci.cargo, funci.rua, funci.casaNum, funci.Referencia, funci.idCidade, funci.idEstado, funci.LastEditDate, funci.CreationDate, funci.excluido, cid.Nome
FROM dbo.Funcionario AS funci INNER JOIN Cidade AS cid ON cid.CidadeId = funci.idCidade";
break;
case "Matricula":
sql = @"SSELECT funci.idFuncionario, funci.nome, funci.dataNascimento, funci.email, funci.cpf, funci.rg, funci.ctps, funci.ctpsSerie, funci.telefone, funci.celular,
funci.cargo, funci.rua, funci.casaNum, funci.Referencia, funci.idCidade, funci.idEstado, funci.LastEditDate, funci.CreationDate, funci.excluido, cid.Nome
FROM dbo.Funcionario AS funci INNER JOIN Cidade AS cid ON cid.CidadeId = funci.idCidade WHERE funci.idFuncionario = " + pesquisa;
break;
case "Nome":
sql = @"SELECT funci.idFuncionario, funci.nome, funci.dataNascimento, funci.email, funci.cpf, funci.rg, funci.ctps, funci.ctpsSerie, funci.telefone, funci.celular,
funci.cargo, funci.rua, funci.casaNum, funci.Referencia, funci.idCidade, funci.idEstado, funci.LastEditDate, funci.CreationDate, funci.excluido, cid.Nome
FROM dbo.Funcionario AS funci INNER JOIN Cidade AS cid ON cid.CidadeId = funci.idCidade WHERE funci.nome LIKE '" + pesquisa + "'";
break;
case "CPF":
sql = @"SELECT funci.idFuncionario, funci.nome, funci.dataNascimento, funci.email, funci.cpf, funci.rg, funci.ctps, funci.ctpsSerie, funci.telefone, funci.celular,
funci.cargo, funci.rua, funci.casaNum, funci.Referencia, funci.idCidade, funci.idEstado, funci.LastEditDate, funci.CreationDate, funci.excluido, cid.Nome
FROM dbo.Funcionario AS funci INNER JOIN Cidade AS cid ON cid.CidadeId = funci.idCidade WHERE funci.cpf LIKE '" + pesquisa + "'";
break;
case "RG":
sql = @"SELECT funci.idFuncionario, funci.nome, funci.dataNascimento, funci.email, funci.cpf, funci.rg, funci.ctps, funci.ctpsSerie, funci.telefone, funci.celular,
funci.cargo, funci.rua, funci.casaNum, funci.Referencia, funci.idCidade, funci.idEstado, funci.LastEditDate, funci.CreationDate, funci.excluido, cid.Nome
FROM dbo.Funcionario AS funci INNER JOIN Cidade AS cid ON cid.CidadeId = funci.idCidade WHERE funci.rg LIKE '" + pesquisa + "'";
break;
case "Ativos":
sql = @"SELECT funci.idFuncionario, funci.nome, funci.dataNascimento, funci.email, funci.cpf, funci.rg, funci.ctps, funci.ctpsSerie, funci.telefone, funci.celular,
funci.cargo, funci.rua, funci.casaNum, funci.Referencia, funci.idCidade, funci.idEstado, funci.LastEditDate, funci.CreationDate, funci.excluido, cid.Nome
FROM dbo.Funcionario AS funci INNER JOIN Cidade AS cid ON cid.CidadeId = funci.idCidade WHERE funci.excluido = 0";
break;
case "Inativos":
sql = @"SELECT funci.idFuncionario, funci.nome, funci.dataNascimento, funci.email, funci.cpf, funci.rg, funci.ctps, funci.ctpsSerie, funci.telefone, funci.celular,
funci.cargo, funci.rua, funci.casaNum, funci.Referencia, funci.idCidade, funci.idEstado, funci.LastEditDate, funci.CreationDate, funci.excluido, cid.Nome
FROM dbo.Funcionario AS funci INNER JOIN Cidade AS cid ON cid.CidadeId = funci.idCidade WHERE funci.excluido = 1";
break;
}
try
{
conn = new SqlConnection();
conn.ConnectionString = strConn;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.GetFillParameters();
dt = new DataTable();
da.Fill(dt);
return(dt);
}
catch (Exception ex)
{
return(dt);
}
finally
{
conn.Close();
}
#endregion
}
public String upd_pengajuan(int BRANCH_ID
, string BANK_ID
, string NO_REK
, string NAMA_PEMILIK_REK
, decimal PENGHASILAN_PER_TAHUN
, string SUMBER_PENDANAAN
, string TUJUAN_PENGAJUAN_MENJADI_PESERTA
, decimal UANG_PERTANGGUNGAN
, int PREMI_SEKALIGUS
, string TINGKAT_BUNGA_PER_TAHUN
, string MULAI_PERTANGGUNGAN
, string AKHIR_PERTANGGUNGAN
, string NAMA_PENERIMA_MANFAAT_ASURANSI
, string NO_POLIS_1
, decimal JUMLAH_1
, string NAMA_PERUSAHAAN_1
, string NO_POLIS_2
, decimal JUMLAH_2
, string NAMA_PERUSAHAAN_2
, string DEBITUR_NAMA
, string DEBITUR_JENIS_KELAMIN
, string DEBITUR_NIK
, string DEBITUR_LAHIR_TEMPAT
, string DEBITUR_LAHIR_TANGGAL
, string DEBITUR_EMAIL
, string DEBITUR_PEKERJAAN
, string PESERTA_ALAMAT
, string PESERTA_KOTA
, string PESERTA_KODE_POS
, string PESERTA_TELP
, string PESERTA_FAX
, string KORESPONDENSI_ALAMAT
, string KORESPONDENSI_KOTA
, string KORESPONDENSI_KODE_POS
, string KORESPONDENSI_TELP
, string KORESPONDENSI_FAX
, int Q1_BADAN_BERAT
, int Q1_BADAN_TINGGI
, int Q2_PERAWATAN_OPSI
, string Q2_PERAWATAN_DESC
, int Q3_DIRAWAT_OPSI
, string Q3_DIRAWAT_DESC
, int Q4_MENDERITA_OPSI
, string Q4_MENDERITA_DESC
, string SES_NO_POLIS
, string NO_REGISTRATION)
{
try
{
DataSet ds = new DataSet();
string strConn = GetConnectionString();
string select = " exec sp_upd_pengajuan " + BRANCH_ID
+ ", " + "'" + BANK_ID + "'"
+ ", " + "'" + NO_REK + "'"
+ ", " + "'" + NAMA_PEMILIK_REK + "'"
+ ", " + PENGHASILAN_PER_TAHUN
+ ", " + "'" + SUMBER_PENDANAAN + "'"
+ ", " + "'" + TUJUAN_PENGAJUAN_MENJADI_PESERTA + "'"
+ ", " + UANG_PERTANGGUNGAN
+ ", " + PREMI_SEKALIGUS
+ ", " + "'" + TINGKAT_BUNGA_PER_TAHUN + "'"
+ ", " + "'" + MULAI_PERTANGGUNGAN + "'"
+ ", " + "'" + AKHIR_PERTANGGUNGAN + "'"
+ ", " + "'" + NAMA_PENERIMA_MANFAAT_ASURANSI + "'"
+ ", " + "'" + NO_POLIS_1 + "'"
+ ", " + JUMLAH_1
+ ", " + "'" + NAMA_PERUSAHAAN_1 + "'"
+ ", " + "'" + NO_POLIS_2 + "'"
+ ", " + JUMLAH_2
+ ", " + "'" + NAMA_PERUSAHAAN_2 + "'"
+ ", " + "'" + DEBITUR_NAMA + "'"
+ ", " + "'" + DEBITUR_JENIS_KELAMIN + "'"
+ ", " + "'" + DEBITUR_NIK + "'"
+ ", " + "'" + DEBITUR_LAHIR_TEMPAT + "'"
+ ", " + "'" + DEBITUR_LAHIR_TANGGAL + "'"
+ ", " + "'" + DEBITUR_EMAIL + "'"
+ ", " + "'" + DEBITUR_PEKERJAAN + "'"
+ ", " + "'" + PESERTA_ALAMAT + "'"
+ ", " + "'" + PESERTA_KOTA + "'"
+ ", " + "'" + PESERTA_KODE_POS + "'"
+ ", " + "'" + PESERTA_TELP + "'"
+ ", " + "'" + PESERTA_FAX + "'"
+ ", " + "'" + KORESPONDENSI_ALAMAT + "'"
+ ", " + "'" + KORESPONDENSI_KOTA + "'"
+ ", " + "'" + KORESPONDENSI_KODE_POS + "'"
+ ", " + "'" + KORESPONDENSI_TELP + "'"
+ ", " + "'" + KORESPONDENSI_FAX + "'"
+ ", " + Q1_BADAN_BERAT
+ ", " + Q1_BADAN_TINGGI
+ ", " + Q2_PERAWATAN_OPSI
+ ", " + "'" + Q2_PERAWATAN_DESC + "'"
+ ", " + Q3_DIRAWAT_OPSI
+ ", " + "'" + Q3_DIRAWAT_DESC + "'"
+ ", " + Q4_MENDERITA_OPSI
+ ", " + "'" + Q4_MENDERITA_DESC + "'"
+ ", " + "'" + SES_NO_POLIS + "'"
+ ", " + "'" + NO_REGISTRATION + "'"
;
SqlDataAdapter da = new SqlDataAdapter(select, strConn);
da.GetFillParameters();
da.Fill(ds, "upd_pengajuan");
return(ds.Tables[0].Rows[0][0].ToString());
}
catch (SqlException ex)
{
return("Error : " + ex.ToString());
}
}
