public static string find_bookname(int bookid, out bool available)
{
string name = "Default";
available = false;
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
conn.Open();
SqlCommand cmd;
using (cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "select BookName,Available from L_Book where BookID= " + bookid;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
name = (string)dr[0];
available = (bool)dr[1];
}
}
}
}
return(name);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
Menu menu = Master.FindControl("NavigationMenu") as Menu;
menu.Items[2].ChildItems[0].ChildItems.RemoveAt(1);
if (Session.Contents["rol"].ToString().Equals("DSTADM"))
Response.Redirect("../Default.aspx");
Connection conexion = new Connection();
string query = String.Empty;
SqlDataSource1.ConnectionString = conexion.getConnectionString();
if (Session.Contents["rol"].ToString().Equals("KCPADM"))
query = String.Format("SELECT USER_ID, USER_NAME, STATUS, E_MAIL FROM USUARIOS AS U WHERE USER_ID <> '{0}' AND ID_COUNTRY = '{1}' AND 0 = (SELECT count(Kam.KAM_NAME) from Kam where Kam.KAM_ID = U.USER_ID AND Kam.KAM_ACTIVE = 1)",
Session.Contents["userid"].ToString(), conexion.getUserCountry(Session.Contents["userid"].ToString()));
else if (Session.Contents["rol"].ToString().Equals("KCPCCR"))
query = String.Format("SELECT USER_ID, USER_NAME, STATUS, E_MAIL FROM USUARIOS WHERE USER_ID <> '{0}' AND ID_COUNTRY = '{1}' AND (ID_ROL <> 'KCPADM' AND ID_ROL <> 'KCPCCR')",
Session.Contents["userid"].ToString(), conexion.getUserCountry(Session.Contents["userid"].ToString()));
SqlDataSource1.SelectCommand = query;
SqlDataSource1.ConflictDetection = ConflictOptions.CompareAllValues;
}
catch (Exception error)
{
RadAjaxManager1.ResponseScripts.Add(String.Format("errorEnvio('{0}');", error.Message));
}
}
public static string find_studentnm(int grno)
{
string name = "default";
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
conn.Open();
SqlCommand cmd;
using (cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "select s_fullname from student where Grno = " + grno;
using (SqlDataReader dr = cmd.ExecuteReader())
{
dr.Read();
if (dr.HasRows)
{
name = (string)dr[0];
}
return(name);
}
}
}
}
public void ExcluirFornecedor(int id)
{
con = new MySqlConnection();
fornecedorV = new V.FornecedorV();
conexao = new Connection();
con.ConnectionString = conexao.getConnectionString();
String query = "delete from fornecedor where codigo_fornecedor = ?id";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?id", id);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("Erro: " + ex);
}
finally
{
con.Close();
}
}
private void CarregarDados()
{
conexao = new Connection();
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
string connectionString = conexao.getConnectionString();
string query = "SELECT codigo_fornecedor, cnpj, razao_social, nome_fantasia, ddd, telefone, ramal, rua, numero, bairro, cidade, estado, pais, cep FROM fornecedor";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, conn))
{
try
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4], dataTable.Rows[i][5], dataTable.Rows[i][6], dataTable.Rows[i][7]
, dataTable.Rows[i][8], dataTable.Rows[i][9], dataTable.Rows[i][10], dataTable.Rows[i][11], dataTable.Rows[i][12], dataTable.Rows[i][13]);
}
}
catch (Exception ex)
{
MessageBox.Show("Erro: " + ex);
}
}
conn.Close();
}
}
private void CarregarDados()
{
conexao = new Connection();
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
string connectionString = conexao.getConnectionString();
string query = "SELECT codigo_produto, nome_produto, data_vencimento, preco_compra, preco_venda, codigo_barra, qnt_estoque, id_fornecedor, id_categoria FROM produto";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, conn))
{
try
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4], dataTable.Rows[i][5], dataTable.Rows[i][6], dataTable.Rows[i][7]
, dataTable.Rows[i][8]);
}
}
catch (Exception ex)
{
MessageBox.Show("Erro: " + ex);
}
}
conn.Close();
}
}
public IEnumerable<Patient> GetAllPatients()
{
List<Patient> PatientDataArray = new List<Patient>();
Connection conn = new Connection();
MySqlConnection connection = null;
try
{
connection = new MySqlConnection(conn.getConnectionString());
connection.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.CommandText = "SELECT * FROM measurement;";
cmd.Prepare();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int ID = reader.GetInt32("id");
decimal bloodsugar = reader.GetDecimal("bloodsugar");
decimal bloodsugardesired = reader.GetDecimal("bloodsugardesired");
string description = reader.GetString("description");
DateTime time = reader.GetDateTime("time");
PatientDataArray.Add(new Patient(ID, bloodsugar, bloodsugardesired, description, time));
}
}
finally
{
if (connection != null)
connection.Close();
}
return PatientDataArray;
}
public static DataTable LoadDataGridView(String type)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand())
{
if (type == "course")
{
cmd.CommandText = "load_courseDetails";
}
else if (type == "subject")
{
cmd.CommandText = "load_subjectDetails";
}
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
}
}
return(dt);
}
private void CarregarDados()
{
conexao = new Connection();
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
string connectionString = conexao.getConnectionString();
string query = "SELECT id, nome, sobrenome, cpf, cargo, username, email, ddd, telefone, idade, rua, numero, bairro, cidade, estado, pais, cep FROM funcionarios";
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, conn))
{
try
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4], dataTable.Rows[i][5], dataTable.Rows[i][6], dataTable.Rows[i][7]
, dataTable.Rows[i][8], dataTable.Rows[i][9], dataTable.Rows[i][10], dataTable.Rows[i][11], dataTable.Rows[i][12], dataTable.Rows[i][13], dataTable.Rows[i][14], dataTable.Rows[i][15], dataTable.Rows[i][16]);
}
}
catch (Exception ex)
{
MessageBox.Show("Erro: " + ex);
}
}
conn.Close();
}
}
private void loadSubject()
{
dt_subject = new DataTable();
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("load_subjectDetails", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dt_subject.Load(dr);
}
}
subject_dataGridView.DataSource = dt_subject;
subject_dataGridView.AutoGenerateColumns = false;
if (!subject_dataGridView.Columns.Contains("remove"))
{
DataGridViewLinkColumn dc = new DataGridViewLinkColumn()
{
Text = "Remove",
HeaderText = "Edit",
Name = "remove",
UseColumnTextForLinkValue = true,
LinkColor = Color.Crimson,
LinkBehavior = LinkBehavior.HoverUnderline
};
subject_dataGridView.Columns.Insert(subject_dataGridView.Columns.Count, dc);
}
}
public void ExcluirCliente(int id)
{
con = new MySqlConnection();
cadastrov = new V.ClienteV();
conexao = new Connection();
con.ConnectionString = conexao.getConnectionString();
String query = "delete from cliente where id = ?id";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?id", id);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("Erro: " + ex);
}
finally
{
con.Close();
}
}
public void AlterarProduto(int codigoProduto, string nomeProduto, decimal precoCompra, DateTime dataVenc, int idFornecedor, int idCategoria, int qtdEstoque, string codigoBarra, decimal precoVenda)
{
con = new MySqlConnection();
produtov = new V.ProdutoV();
conexao = new Connection();
con.ConnectionString = conexao.getConnectionString();
String query = "update produto set nome_produto = ?nomeProduto, preco_compra = ?precoCompra, data_vencimento = ?dataVenc, id_fornecedor = ?idFornecedor, id_categoria = ?idCategoria, qnt_estoque = ?qtdEstoque, codigo_barra = ?codigoBarra, preco_venda = ?precoVenda";
query += " where codigo_produto = ?codigoProduto";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?codigoProduto", codigoProduto);
cmd.Parameters.AddWithValue("?nomeProduto", nomeProduto);
cmd.Parameters.AddWithValue("?precoCompra", precoCompra);
cmd.Parameters.AddWithValue("?dataVenc", dataVenc);
cmd.Parameters.AddWithValue("?idFornecedor", idFornecedor);
cmd.Parameters.AddWithValue("?idCategoria", idCategoria);
cmd.Parameters.AddWithValue("?qtdEstoque", qtdEstoque);
cmd.Parameters.AddWithValue("?codigoBarra", codigoBarra);
cmd.Parameters.AddWithValue("?precoVenda", precoVenda);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("Erro: " + ex);
}
finally
{
con.Close();
}
}
private void PreencheComboFornecedor()
{
conexao = new Connection();
string connectionString = conexao.getConnectionString();
string query = "SELECT codigo_fornecedor, nome_fantasia FROM fornecedor";
DataTable dt = new DataTable();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, conn))
{
conn.Open();
try
{
adapter.Fill(dt);
}
catch (Exception e)
{
MessageBox.Show("Erro: " + e.ToString());
}
}
}
cbFornecedor.DataSource = dt;
cbFornecedor.ValueMember = "codigo_fornecedor";
cbFornecedor.DisplayMember = "nome_fantasia";
}
public static bool insertUsers(AdminSectionEntity ce)
{
bool flag = false;
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("insertUsers", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@usernm", ce.userName);
cmd.Parameters.AddWithValue("@pwd", ce.password);
cmd.Parameters.AddWithValue("@roleid", ce.userRole);
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
dr.Read();
flag = (bool)dr[0];
}
return(flag);
}
}
}
public static DataTable Load_CourseDetails(string type)
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand())
{
if (type == "subject")
{
cmd.CommandText = "select C_Alias as Alias,C_TotalSem as TotalSem from Courses";
}
else if (type == "fee")
{
cmd.CommandText = "select C_Alias as Alias,C_DurationDescription as duration_desc from Courses where C_Fees=0";
}
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable("CourseAlias");
dt.Load(dr);
return(dt);
}
}
}
private void PreencheComboCategoria()
{
conexao = new Connection();
string connectionString = conexao.getConnectionString();
string query = "SELECT id, tipo_categoria FROM categoria";
DataTable dt = new DataTable();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, conn))
{
conn.Open();
try
{
adapter.Fill(dt);
}
catch (Exception e)
{
MessageBox.Show("Erro: " + e.ToString());
}
}
}
cbCategoria.DataSource = dt;
cbCategoria.ValueMember = "id";
cbCategoria.DisplayMember = "tipo_categoria";
}
public void ListarFuncionarios()
{
con = new MySqlConnection();
cadastrov = new V.FuncionarioV();
conexao = new Connection();
con.ConnectionString = conexao.getConnectionString();
String query = "SELECT * FROM funcionarios";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("Erro: " + ex);
}
finally
{
con.Close();
}
}
public void ExcluirProduto(int id)
{
con = new MySqlConnection();
produtov = new V.ProdutoV();
conexao = new Connection();
con.ConnectionString = conexao.getConnectionString();
String query = "delete from produto where codigo_produto = ?codigoProduto";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?codigoProduto", id);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("Erro: " + ex);
}
finally
{
con.Close();
}
}
private void loadCourse_ComboBox()
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("select C_Id,C_Alias from Courses", conn))
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
}
}
if (dt.Rows.Count > 0)
{
select_course_combobox.DisplayMember = "C_Alias";
select_course_combobox.ValueMember = "C_Id";
select_course_combobox.DataSource = dt;
}
else
{
select_course_combobox.Items.Add("Select Course");
select_course_combobox.SelectedIndex = 0;
}
}
public static int getBatchYear(string courseAlias, int currentsem)
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("getBatchYear", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@alias", courseAlias);
cmd.Parameters.AddWithValue("@sem", currentsem);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
DateTime date = Convert.ToDateTime(dr[0]);
return(date.Year);
}
else
{
return(0);
}
}
}
}
private void VATReceipt_Load(object sender, EventArgs e)
{
this.xx_receiptTableAdapter = new ReceiptDataSetTableAdapters.xx_receiptTableAdapter();
this.xx_receipt_itemTableAdapter = new ReceiptDataSetTableAdapters.xx_receipt_itemTableAdapter();
this.xx_receipt_tenderTableAdapter = new ReceiptDataSetTableAdapters.xx_receipt_tenderTableAdapter();
this.receiptDataSet = new ReceiptDataSet();
xx_receiptTableAdapter.Connection.ConnectionString = Connection.getConnectionString();
xx_receipt_itemTableAdapter.Connection.ConnectionString = Connection.getConnectionString();
xx_receipt_tenderTableAdapter.Connection.ConnectionString = Connection.getConnectionString();
}
public static int TotalCourse()
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("select count(*) from Courses", conn))
{
conn.Open();
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
}
public static int deleteUser(int UserId)
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("delete from Users where UserId=" + UserId, conn))
{
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
}
public static int deleteStudent_for_Update(int grno)
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("delete_student_for_update", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Grno", grno);
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
}
public static void Add_category(Library_Entity le)
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand())
{
conn.Open();
cmd.CommandText = "Insert into BookCategory(Category_Name) values('" + le.category_name + "')";
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
}
}
public void InserirFuncionario(String nome, String sobrenome, String cargo, String cpf, int ddd, int telefone, String email, int idade, String pais, String estado,
String rua, int numero, String bairro, int cep, String cidade)
{
var senha = CreatePassword(6);
var nomeCompleto = nome + " " + sobrenome;
var username = nome.Substring(0, 1) + sobrenome.Substring(0, 5);
con = new MySqlConnection();
cadastrov = new V.FuncionarioV();
conexao = new Connection();
con.ConnectionString = conexao.getConnectionString();
String query = "INSERT INTO funcionarios(ID, nome, sobrenome, username, senha, nome_completo, idade, cargo, cpf, ddd, telefone, email, pais, estado, rua, numero, bairro, cep, cidade)";
query += " VALUES (null, ?nome, ?sobrenome, ?username, ?senha, ?nomeCompleto, ?idade, ?cargo, ?cpf, ?ddd, ?telefone, ?email, ?pais, ?estado, ?rua, ?numero, ?bairro, ?cep, ?cidade)";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?nome", nome);
cmd.Parameters.AddWithValue("?sobrenome", sobrenome);
cmd.Parameters.AddWithValue("?username", username);
cmd.Parameters.AddWithValue("?senha", senha);
cmd.Parameters.AddWithValue("?nomeCompleto", nomeCompleto);
cmd.Parameters.AddWithValue("?cargo", cargo);
cmd.Parameters.AddWithValue("?cpf", cpf);
cmd.Parameters.AddWithValue("?ddd", ddd);
cmd.Parameters.AddWithValue("?telefone", telefone);
cmd.Parameters.AddWithValue("?email", email);
cmd.Parameters.AddWithValue("?idade", idade);
cmd.Parameters.AddWithValue("?pais", pais);
cmd.Parameters.AddWithValue("?estado", estado);
cmd.Parameters.AddWithValue("?rua", rua);
cmd.Parameters.AddWithValue("?numero", numero);
cmd.Parameters.AddWithValue("?bairro", bairro);
cmd.Parameters.AddWithValue("?cep", cep);
cmd.Parameters.AddWithValue("?cidade", cidade);
cmd.ExecuteNonQuery();
cmd.Dispose();
Email.EnviarEmail(username, senha, email);
}
catch (Exception ex)
{
MessageBox.Show("Erro: " + ex);
}
finally
{
con.Close();
}
}
public static int insertEmployee(EmployeeEntity ey)
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("insertEmployee", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@empID", ey.empid);
cmd.Parameters.AddWithValue("@profile", ey.profile);
cmd.Parameters.AddWithValue("@name", ey.emp_firstName + " " + ey.emp_middleName + " " + ey.emp_lastName);
cmd.Parameters.AddWithValue("@fathername", ey.father_firstName + " " + ey.father_middleName + " " + ey.father_lastName);
cmd.Parameters.AddWithValue("@mothername", ey.mother_firstName + " " + ey.mother_middleName + " " + ey.mother_lastName);
cmd.Parameters.AddWithValue("@joindate", ey.joinDate);
cmd.Parameters.AddWithValue("@isteaching", ey.isTeaching);
cmd.Parameters.AddWithValue("@currentAddress", ey.currentAddress);
cmd.Parameters.AddWithValue("@permanentAddress", ey.permanentAddress);
cmd.Parameters.AddWithValue("@pincode", ey.pincode);
cmd.Parameters.AddWithValue("@cityid", ey.cityid);
cmd.Parameters.AddWithValue("@stateid", ey.stateid);
cmd.Parameters.AddWithValue("@countryid", ey.countryid);
cmd.Parameters.AddWithValue("@gender", ey.gender);
cmd.Parameters.AddWithValue("@castid", ey.castid);
cmd.Parameters.AddWithValue("@dob", ey.dob);
cmd.Parameters.AddWithValue("@birthplace", ey.birthplace);
cmd.Parameters.AddWithValue("@empmno", ey.empmobileNo);
cmd.Parameters.AddWithValue("@email", ey.email);
cmd.Parameters.AddWithValue("@telephone", ey.telephone);
cmd.Parameters.AddWithValue("@parentsmno", ey.perentsNo);
cmd.Parameters.AddWithValue("@degree", ey.degree);
cmd.Parameters.AddWithValue("@lastEmployeeAt", ey.lastEmployeeAt);
cmd.Parameters.AddWithValue("@yearofExpirience", ey.YearOfExperience);
cmd.Parameters.AddWithValue("@remark", ey.remark);
/*----------------Employee Bank Details------------*/
cmd.Parameters.AddWithValue("@bankid", ey.bankID);
cmd.Parameters.AddWithValue("@accountNo", ey.accountNo);
cmd.Parameters.AddWithValue("@branchName", ey.BranchName);
cmd.Parameters.AddWithValue("@IFSCCode", ey.IFSCCode);
/*----------------Employee IdentityProof Details------------*/
cmd.Parameters.AddWithValue("@aadharcard", ey.aadharCard);
cmd.Parameters.AddWithValue("@pancard", ey.panCard);
cmd.Parameters.AddWithValue("@experiencecertificate", ey.experienceCertificate);
cmd.Parameters.AddWithValue("@degreecertificate", ey.degreeCertificate);
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
}
public static int deleteEmployee(int empid)
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cnd = new SqlCommand("delete_employee", conn))
{
cnd.CommandType = CommandType.StoredProcedure;
cnd.Parameters.AddWithValue("@empid", empid);
conn.Open();
return(cnd.ExecuteNonQuery());
}
}
}
public static string getCourseAlias()
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(@"SELECT C_Alias
FROM Courses WHERE C_Id =
(SELECT MAX(C_Id)
FROM Courses)", conn))
{
conn.Open();
return(cmd.ExecuteScalar().ToString());
}
}
}
public static System.Data.DataTable getDepartment()
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("select C_Alias,C_TotalSem from Courses where C_Fees!=0", conn))
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
System.Data.DataTable dt = new System.Data.DataTable();
dt.Load(dr);
return(dt);
}
}
}
private static void setDefaultCategory(string nm)
{
using (SqlConnection conn = new SqlConnection(Connection.getConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("setDefaultBookCategory", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CategoryName", nm);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
private void cargarDataSources()
{
try
{
Connection conexion = new Connection();
sqlEstadoCiudad.ConnectionString = conexion.getConnectionString();
sqlEstadoCiudad.SelectCommand = String.Format("SELECT DIVISION_NAME, CITY_NAME FROM DIVISION_TERRITORIAL WHERE COUNTRY_ID = '{0}';", conexion.getUserCountry(Session.Contents["userid"].ToString()));
sqlSegmentos.ConnectionString = conexion.getConnectionString();
sqlSegmentos.SelectCommand = String.Format("SELECT SUB_SEGMENT_ID, SUB_SEGMENT_NAME FROM SUB_SEGMENTOS WHERE SUB_SEGMENT_ID <> 0 ORDER BY SUB_SEGMENT_ID ASC;");
sqlDispensadorProducto.ConnectionString = conexion.getConnectionString();
sqlDispensadorProducto.SelectCommand = String.Format("SELECT D.ID_DISPENSER, D.DESCRIPTION, D.DISPENSER_FACE FROM DISPENSADORES AS D JOIN DISPENSADOR_PAIS AS DP ON DP.DISPENSER_ID = D.ID_DISPENSER WHERE DP.COUNTRY_ID = '{0}' AND DP.DISPENSER_STATUS = 1 ORDER BY D.ID_DISPENSER ASC;",
conexion.getUserCountry(Session.Contents["userid"].ToString()));
}
catch (Exception error)
{
radajaxmanager.ResponseScripts.Add(String.Format("errorEnvio('{0}');", error.Message));
}
}
protected void setDataSources()
{
Connection conexion = new Connection();
sqlCiudad.ConnectionString = conexion.getConnectionString();
sqlCiudad.SelectCommand = String.Format("SELECT DIVISION_NAME, CITY_NAME FROM DIVISION_TERRITORIAL WHERE COUNTRY_ID = '{0}'", conexion.getUserCountry(Session.Contents["userid"].ToString()));
sqlMotivos.ConnectionString = conexion.getConnectionString();
sqlMotivos.SelectCommand = String.Format("SELECT ID_REASON, REASON_DESCRIP FROM RAZONES WHERE ID_REASON <> 3");
sqlClientes.ConnectionString = conexion.getConnectionString();
string clientid = conexion.getUsersInfo("CLIENT_ID", "USER_ID", Session["userid"].ToString());
sqlClientes.SelectCommand = String.Format("SELECT END_USER_ID, TRADE_NAME FROM CLIENTES_FINALES WHERE CLIENT_ID = '{0}' AND CLIENT_STATUS = 1", clientid);
sqlVendedores.ConnectionString = conexion.getConnectionString();
sqlVendedores.SelectCommand = String.Format("SELECT SALES_ID, SALES_NAME FROM VENDEDORES WHERE CLIENT_ID = '{0}'", clientid);
sqlSegmento.ConnectionString = conexion.getConnectionString();
sqlSegmento.SelectCommand = String.Format("SELECT SUB_SEGMENT_ID, SUB_SEGMENT_NAME FROM SUB_SEGMENTOS WHERE SUB_SEGMENT_ID <> 0 ORDER BY SUB_SEGMENT_ID ASC");
sqlCondicionesPago.ConnectionString = conexion.getConnectionString();
sqlCondicionesPago.SelectCommand = String.Format("SELECT ID_PAYMENT_CONDITION, PAYMENT_CONDITION FROM CONDICIONES_PAGO");
}
private void cargarProducto(string dispenserID)
{
try
{
Connection conexion = new Connection();
cmbProducto.EmptyMessage = "Seleccione un producto";
cmbProducto.Items.Clear();
string userCountry = conexion.getUserCountry(Session["userid"].ToString());
string query = String.Format("SELECT PRODUCT_ID, PRODUCT_DESCRIP FROM PRODUCTOS WHERE DISPENSER_ID = '{0}' AND COUNTRY_ID = '{1}'", dispenserID, userCountry);
try
{
SqlConnection bridge = new SqlConnection(conexion.getConnectionString());
SqlDataAdapter adapter = new SqlDataAdapter(query, bridge);
DataTable datatable = new DataTable();
adapter.Fill(datatable);
foreach (DataRow datarow in datatable.Rows)
{
RadComboBoxItem item = new RadComboBoxItem();
item.Text = datarow["PRODUCT_ID"].ToString() + " " + datarow["PRODUCT_DESCRIP"].ToString();
item.Value = datarow["PRODUCT_ID"].ToString();
cmbProducto.Items.Add(item);
item.DataBind();
}
}
catch (SqlException)
{
radajaxmanager.ResponseScripts.Add(@"alert('Error inesperado de conexion, no se logro cargar la lista de productos refresque la pagina" +
" o intentelo mas tarde.');");
return;
}
}
catch (Exception error)
{
radajaxmanager.ResponseScripts.Add(String.Format("errorEnvio('{0}');", error.Message));
}
}
