public static string InsUpdDel(string query)
{
SqlConnection sqlcon = new SqlConnection(connstring);
SqlCommand sqlcom = new SqlCommand(query, sqlcon);
//sqlcom.CommandType = CommandType.Text;
sqlcon.Open();
try
{
// string connstring = ConfigurationManager.ConnectionStrings["connid"].ToString(); //ConfigurationManager.AppSettings["connid"];
sqlcom.ExecuteNonQuery();
query = "";
sqlcom.Dispose();
sqlcon.Close();
return "true";
}
catch(Exception prajwol)
{
query = "";
sqlcom.Dispose();
sqlcon.Close();
return prajwol.Message.ToString();
}
}
public DataSet FilterData(ArsonFilterModel filterData)
{
var connectionString = ConfigurationManager.ConnectionStrings["UCR_DataEntities"];
DataSet dsResult = new DataSet();
using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
{
try
{
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = GenerateArsonORQuery(filterData);
command.CommandType = System.Data.CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
conn.Open();
adapter.Fill(dsResult);
conn.Close();
adapter.Dispose();
command.Dispose();
}
catch (Exception ex)
{
StorageClient.LogError(ex);
}
}
return dsResult;
}
public string InsertKhachHang(string TenKH, string Gioitinh, string Diachi, string SDT, string LoaiKH, string Ghichu)
{
string str = "";
string sql = "InsertKH";
// string sql = "insert NhomSP values (@Manhom, @Tennhom, @NgayCN)";
SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TenKH", TenKH);
cmd.Parameters.AddWithValue("@gt", Gioitinh);
cmd.Parameters.AddWithValue("@DC", Diachi);
cmd.Parameters.AddWithValue("@SDT", SDT);
cmd.Parameters.AddWithValue("@LoaiKH", LoaiKH);
cmd.Parameters.AddWithValue("@ghichu", Ghichu);
//cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
str = dt.Rows[0].ItemArray[0].ToString();
cmd.Dispose();
con.Close();
return str;
}
public void LinkAbsenceWithLeaveRequest(List<ReportedAbsenceEntity> t, LeaveRequestEntity request)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = DALHelper.CreateSqlDbConnection();
foreach (ReportedAbsenceEntity ae in t)
{
cmd = new SqlCommand("usp_LinkAbsenceWithLeaveRequest", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ReportedAbsenceId", ae.Id);
cmd.Parameters.AddWithValue("@LeaveRequestId", request.Id);
conn.Close();
cmd.Dispose();
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
cmd.Dispose();
conn.Dispose();
}
}
public static string connstring = ConfigurationManager.ConnectionStrings["connid"].ToString(); //ConfigurationManager.AppSettings["connid"];
public static string GetString(string query)
{
string retvalue = "";
SqlConnection sqlcon = new SqlConnection(connstring);
SqlCommand sqlcom = new SqlCommand(query, sqlcon);
//sqlcom.CommandType = CommandType.Text;
sqlcon.Open();
SqlDataReader sqldr = sqlcom.ExecuteReader();
sqldr.Read();
try
{
retvalue = sqldr[0].ToString();
sqldr.Dispose();
sqlcom.Dispose();
sqlcon.Close();
}
catch
{
sqldr.Dispose();
sqlcom.Dispose();
sqlcon.Close();
return "";
}
return retvalue;
}
private void AgregarReunion_Load(object sender, EventArgs e)
{
using (var conn = new System.Data.SqlClient.SqlConnection(Properties.Settings.Default.ConnectionString))
{
var cmd = new System.Data.SqlClient.SqlCommand(
"select max(ReunionID) + 1 from Reuniones "
, conn);
try
{
conn.Open();
SqlDataReader Reader = cmd.ExecuteReader();
while (Reader.Read())
{
txtReunionID.Text = Reader.GetInt32(0).ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
cmd.Dispose();
conn.Close();
return;
}
cmd.Dispose();
conn.Close();
}//using
}
public bool IsValid(string _username, string _password)
{
string npiconstring = ConfigurationManager.ConnectionStrings["npi_con_string"].ConnectionString;
using (var cs = new SqlConnection(npiconstring))
{
string _sql = @"SELECT [username] FROM [dbo].[System_Users]" + @"WHERE [username] = @u AND [password]= @p";
//Ejecutar comando
var cmd = new SqlCommand(_sql, cs);
cmd.Parameters
.Add(new SqlParameter("@u", SqlDbType.NVarChar))
.Value = _username;
cmd.Parameters
.Add(new SqlParameter("@p", SqlDbType.NVarChar))
//.Value = Helpers.SHA1.Encode(_password); <--- Verificar porque no esta agarrando el encoding
.Value = _password;
//Abrir conexion
cs.Open();
//Leer datos
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Dispose();
cmd.Dispose();
return true;
}
else
{
reader.Dispose();
cmd.Dispose();
return false;
}
}
}
public static void ExecuteMyQuery(string Query)
{
string conString = System.Configuration.ConfigurationManager.ConnectionStrings["constrww"].ToString();
SqlConnection Connection = new SqlConnection(conString);
Connection.Open();
SqlCommand cmdXls = new SqlCommand(Query, Connection);
try
{
cmdXls.ExecuteNonQuery();
}
catch (Exception e)
{
cmdXls.Dispose();
Connection.Dispose();
}
finally
{
cmdXls.Dispose();
Connection.Dispose();
}
}
/// <summary>
/// Checks if user with given password exists in the database
/// </summary>
/// <param name="_username">User name</param>
/// <param name="_password">User password</param>
/// <returns>True if user exist and password is correct</returns>
public bool IsValid(string _username, string _password)
{
using (var cn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename" +
@"='C:\Tutorials\1 - Creating a custom user login form\Creating " +
@"a custom user login form\App_Data\Database1.mdf';Integrated Security=True"))
{
string _sql = @"SELECT [Username] FROM [dbo].[System_Users] " +
@"WHERE [Username] = @u AND [Password] = @p";
var cmd = new SqlCommand(_sql, cn);
cmd.Parameters
.Add(new SqlParameter("@u", SqlDbType.NVarChar))
.Value = _username;
cmd.Parameters
.Add(new SqlParameter("@p", SqlDbType.NVarChar))
.Value = Helpers.SHA1.Encode(_password);
cn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Dispose();
cmd.Dispose();
return true;
}
else
{
reader.Dispose();
cmd.Dispose();
return false;
}
}
}
private void btnGuardar_Click(object sender, EventArgs e)
{
using (var conn = new System.Data.SqlClient.SqlConnection(Properties.Settings.Default.ConnectionString))
{
var cmd = new System.Data.SqlClient.SqlCommand(
" insert into Reuniones (ReunionID, Nombre, Lugar, FechaInicio, FecharTermino ) values ( @ReunionID, @Nombre, @Lugar, @FechaInicio, @FechaTermino)"
, conn);
try
{
cmd.Parameters.AddWithValue("@ReunionID", txtReunionID.Text);
cmd.Parameters.AddWithValue("@Nombre", txtNombre.Text);
cmd.Parameters.AddWithValue("@Lugar", txtLugar.Text);
cmd.Parameters.Add("@FechaInicio", SqlDbType.DateTime).Value = dtpickerInicio.Value;
cmd.Parameters.Add("@FechaTermino", SqlDbType.DateTime).Value = dtpickerFin.Value;
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
cmd.Dispose();
conn.Close();
}
cmd.Dispose();
conn.Close();
}
MessageBox.Show("Reunion Agregada");
Close();
}
private bool ExisteRegistro(int NumeroControl, int Reunion)
{
int Contador = 0;
using (var conn = new System.Data.SqlClient.SqlConnection(Properties.Settings.Default.ConnectionString))
{
var cmd = new System.Data.SqlClient.SqlCommand(
" select count (NumeroControl) as Cuantos from Asistencia where PersonaID = @NumeroControl and ReunionID = @ReunionID "
, conn);
try
{
cmd.Parameters.AddWithValue("@NumeroControl", NumeroControl);
cmd.Parameters.AddWithValue("@ReunionID", Reunion);
contador = Utilities.checkCount(conn, cmd);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
cmd.Dispose();
conn.Close();
return(false);
}
cmd.Dispose();
conn.Close();
}//using
if (Contador > 0)
{
return(true);
}
else
{
return(false);
}
}
public District FindDistrict(string name)
{
string query = "SELECT * FROM tbl_district WHERE name='" + name + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
if (ASqlDataReader.HasRows)
{
District aDistrict = new District();
ASqlDataReader.Read();
aDistrict.Id = (int) ASqlDataReader["id"];
aDistrict.Name = ASqlDataReader["name"].ToString();
aDistrict.Population = (int) ASqlDataReader["population"];
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return aDistrict;
}
else
{
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return null;
}
}
public string methodregister(string userid, string firstname, string lastname, string pswrd, string sex, string streeet, string town, string stat, string zipcode, string phonenum, string que, string ans)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("Select * from userinfo where uid = '"+ userid +"'", conn);
SqlDataReader read = cmd.ExecuteReader();
if( (read.HasRows))
{
cmd.Dispose();
read.Close();
conn.Close();
return "fail";
}
else
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1"].ConnectionString);
con.Open();
SqlCommand c = new SqlCommand("Insert into userinfo(uid,fname,lname,phone,password,gender,street,city,state,zip,secQ,secAns) values ( '" + userid + "', '" + firstname + "','" + lastname + "','" + phonenum + "','" + pswrd + "', '" + sex + "','" + streeet + "','" + town + "','" + stat + "','" + zipcode + "','" + que + "','" + ans + "')", con);
int n = c.ExecuteNonQuery();
cmd.Dispose();
c.Dispose();
read.Close();
con.Close();
conn.Close();
return ("success");
}
}
public string log(string uname, string pwd)
{
//if (uname == "admin" && pwd == "admin")
//{
// return "success";
//}
//else
//{
// return "fail";
//}
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbString"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("Select * from Table2 where Userid='" + uname + "' AND Password = '******' ", conn);
SqlDataReader data = null;
data = cmd.ExecuteReader();
if (data.HasRows)
{
data.Dispose();
cmd.Dispose();
conn.Close(); return "Y";
}
else
{
data.Dispose();
cmd.Dispose();
conn.Close(); return "N";
}
}
private bool RegistrarAsistencia()
{
using (var conn = new System.Data.SqlClient.SqlConnection(Properties.Settings.Default.ConnectionString))
{
var cmd = new System.Data.SqlClient.SqlCommand(
" insert into Asistencia (PersonaID,ReunionID,TomaAsistencia) values (@NumeroControl, @ReunionID, sysdatetime())"
, conn);
try
{
cmd.Parameters.AddWithValue("@NumeroControl", txtNumeroControl.Text);
cmd.Parameters.AddWithValue("@ReunionID", cmbReuniones.SelectedValue);
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
cmd.Dispose();
conn.Close();
return(false);
}
cmd.Dispose();
conn.Close();
}
return(true);
}
/// <summary>
/// Checks if user with given password exists in the database
/// </summary>
/// <param name="_username">User name</param>
/// <param name="_password">User password</param>
/// <returns>True if user exist and password is correct</returns>
public bool IsValid(string _username, string _password)
{
using (var cn = new SqlConnection(@"Data Source=jeff.cedarville.edu; Persist Security Info=True;User ID=itm3500;Password=itm3500; MultipleActiveResultSets=True"))
{
string _sql = @"SELECT [Username] FROM [VanDintel-HarrisProject_Security].[System_Users] " +
@"WHERE [Username] = @u AND [Password] = @p";
var cmd = new SqlCommand(_sql, cn);
cmd.Parameters
.Add(new SqlParameter("@u", SqlDbType.NVarChar))
.Value = _username;
cmd.Parameters
.Add(new SqlParameter("@p", SqlDbType.NVarChar))
.Value = System.Web.Helpers.Crypto.SHA1(_password);
cn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Dispose();
cmd.Dispose();
return true;
}
else
{
reader.Dispose();
cmd.Dispose();
return false;
}
}
}
public Patient Find(long voterId)
{
string query = "SELECT * FROM tbl_patient WHERE voter_id='" + voterId + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
if (ASqlDataReader.HasRows)
{
Patient aPatient = new Patient();
ASqlDataReader.Read();
aPatient.Id = (int)ASqlDataReader["id"];
aPatient.VoterId = (long)ASqlDataReader["voter_id"];
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return aPatient;
}
else
{
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return null;
}
}
public void ProcessRequest(HttpContext context)
{
if (!String.IsNullOrEmpty(context.Request.QueryString["term"].ToString()))
{
string connStr = ConfigurationManager.ConnectionStrings["DBCONNSTRING"].ToString();
SqlConnection sqlconn = new SqlConnection(connStr);
SqlCommand sqlcmd = new SqlCommand();
try
{
if (sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();
}
sqlcmd.Connection = sqlconn;
sqlcmd.CommandType = CommandType.Text;
sqlcmd.CommandText = "SELECT top 10 x.CountryName as cn FROM Countries as x WHERE x.CountryName LIKE '%' + @cn + '%'";
sqlcmd.Parameters.AddWithValue("@cn", context.Request.QueryString["term"].ToString());
sqlcmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
string[] items = new string[dt.Rows.Count];
int ctr = 0;
foreach (DataRow row in dt.Rows)
{
items[ctr] = (string)row["cn"];
ctr++;
}
//convert the string array to Javascript and send it out
context.Response.Write(new JavaScriptSerializer().Serialize(items));
}
if (sqlconn.State == ConnectionState.Open)
{
sqlcmd.Dispose();
sqlconn.Close();
sqlconn.Dispose();
}
}
catch (Exception)
{
throw;
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlcmd.Dispose();
sqlconn.Close();
sqlconn.Close();
}
}
}
}
public Medicine Find(string name)
{
string query = "SELECT * FROM tbl_medicine WHERE name='" + name + "'";
ASqlCommand = new SqlCommand(query, ASqlConnection);
ASqlConnection.Open();
ASqlDataReader = ASqlCommand.ExecuteReader();
if (ASqlDataReader.HasRows)
{
Medicine aMedicine = new Medicine();
ASqlDataReader.Read();
aMedicine.Id = (int)ASqlDataReader["id"];
aMedicine.Name = ASqlDataReader["name"].ToString();
aMedicine.Power = Convert.ToDecimal(ASqlDataReader["power"]);
aMedicine.Type = ASqlDataReader["type"].ToString();
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return aMedicine;
}
else
{
ASqlDataReader.Close();
ASqlCommand.Dispose();
ASqlConnection.Close();
return null;
}
}
private void button1_Click(object sender, EventArgs e)
{
int Row = dataGridView1.CurrentRow.Index;
String ID = dataGridView1[0, Row].Value.ToString();
int affectedRows = 0;
string connetionString = null;
connetionString = "Data Source=MD\\SQLEXPRESS;Initial Catalog=lab;Integrated Security=True";
// connetionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
SqlConnection con = new SqlConnection(connetionString);
SqlCommand cmd = new SqlCommand();
//string myQuery = "INSERT INTO dbo.Employees (Name, DoB, Gender, Address, AddedBy, AddedDate) VALUES(@Name, @DoB, @Gender, @Address, @AddedBy, @AddedDate)";
string myQuery = "dbo.deleteEmployee";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = myQuery;
cmd.Parameters.Add("@id", SqlDbType.NChar);
cmd.Parameters["@id"].Value = ID;
SqlParameter sqlP = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
sqlP.Direction = ParameterDirection.ReturnValue;
try
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
affectedRows = (int)cmd.Parameters["@ReturnValue"].Value;
if (affectedRows == 0)
{
MessageBox.Show("Амжилттай устгалаа ");
cmd.Dispose();
con.Close();
}
else if (affectedRows != 0)
{
MessageBox.Show("Хадгалагдсангүй", "Алдаа");
}
else
{
cmd.Dispose();
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
cmd.Dispose();
con.Close();
}
conn();
}
protected void Page_Load(object sender, EventArgs e)
{
string venid = Request.QueryString["id"];
// int vid = Convert.ToInt32(venid);
/* itemfind.find s1 = new itemfind.find();
ArrayList a = new ArrayList(s1.item_retrieve(vid));*/
ArrayList c = new ArrayList();
ArrayList b = new ArrayList();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbString"].ConnectionString);
//Open the connection
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * from item_menus where item_id = " + venid + "", conn);
SqlDataReader reader = cmd.ExecuteReader();
string a = "no rows";
while (reader.Read())
{
a = reader["item_name"].ToString();
a = a + '^';
a = a + reader["item_price"].ToString();
a = a + '^';
a = a + reader["vendor_name"].ToString();
b.Add(a);
}
cmd.Dispose();
conn.Close();
if (Session["menulist"] != null)
{
c = (ArrayList)(Session["menulist"]);
}
foreach (string ss in b)
{
c.Add(ss);
}
Session["menulist"] = c;
if (!IsPostBack)
{
string prevPage = Request.UrlReferrer.ToString();
Response.Redirect(prevPage);
}
cmd.Dispose();
conn.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.Cookies["VC"] != null)
{
string VC = Request.Cookies["VC"].Values["VC"];
Classes.LoginSession ls = new Classes.LoginSession();
int UserId = ls.getUserId(VC);
if (UserId == 0)
{
Response.Redirect("~/Login/Requests");
}
else
{
Session["UserId"] = UserId.ToString();
}
}
else
{
Response.Redirect("~/Login/Requests");
}
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ProfilesConnectionString"].ConnectionString);
SqlCommand sqlCmd = new SqlCommand("sp_requestsAllRead", sqlConn);
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.Add("@UserId", SqlDbType.Int).Value = Convert.ToInt32(Session["UserId"]);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
sqlConn.Close();
sqlCmd.Dispose();
sqlConn.Dispose();
}
sqlCmd.Dispose();
sqlConn.Dispose();
if (GridViewFriends.Rows.Count == 0)
{
LabelFriendsNoRecord.Visible = true;
}
}
public JsonResult get_DanhSachDonViTheoBangLuong(String iID_MaBangLuong ,String GiaTri)
{
List<Object> list = new List<Object>();
String SQL = "SELECT sDanhSachMaDonVi FROM L_BangLuong WHERE iTrangThai=1 AND iID_MaBangLuong=@iID_MaBangLuong";
SqlCommand cmd = new SqlCommand(SQL);
cmd.Parameters.AddWithValue("@iID_MaBangLuong", iID_MaBangLuong);
String dsMaDonVi = Connection.GetValueString(cmd, "");
cmd.Dispose();
String[] arrMaDonVi = dsMaDonVi.Split(',');
cmd = new SqlCommand();
String DK = "";
int i;
for (i = 0; i < arrMaDonVi.Length; i++)
{
if (arrMaDonVi[i] != "")
{
if (DK != "") DK += " OR ";
DK += "iID_MaDonVi=@iID_MaDonVi" + i;
cmd.Parameters.AddWithValue("@iID_MaDonVi" + i, arrMaDonVi[i]);
}
}
DK += " AND iNamLamViec_DonVi=@iNamLamViec";
cmd.Parameters.AddWithValue("@iNamLamViec", NguoiDungCauHinhModels.iNamLamViec);
SQL = String.Format("SELECT iID_MaDonVi, sTen FROM NS_DonVi WHERE iTrangThai=1 AND ({0}) AND (iID_MaDonVi + ' - ' + sTen) LIKE @iID_MaDonVi ORDER BY iID_MaDonVi", DK);
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@iID_MaDonVi", GiaTri + "%");
DataTable dt = Connection.GetDataTable(cmd);
cmd.Dispose();
if (dt.Rows.Count == 0)
{
dt.Dispose();
SQL = String.Format("SELECT iID_MaDonVi, sTen FROM NS_DonVi WHERE iTrangThai=1 AND ({0}) ORDER BY iID_MaDonVi", DK);
cmd.CommandText = SQL;
dt = Connection.GetDataTable(cmd);
cmd.Dispose();
}
for (i = 0; i < dt.Rows.Count; i++)
{
Object item = new
{
value = String.Format("{0}", dt.Rows[i]["iID_MaDonVi"]),
label = String.Format("{0} - {1}", dt.Rows[i]["iID_MaDonVi"], dt.Rows[i]["sTen"])
};
list.Add(item);
}
dt.Dispose();
return Json(list, JsonRequestBehavior.AllowGet);
}
public bool CheckIsRecordExist(string a_strChkSqlCommand)
{
System.Data.SqlClient.SqlCommand objCommand;
bool RecordExists = false;
try
{
bool AttemptToConnect = OpenSqlConnection();
objCommand = new System.Data.SqlClient.SqlCommand(a_strChkSqlCommand);
objCommand.Connection = myConn;
//objCommand.CommandTimeout=ConnectionTimeOut;
objCommand.CommandType = CommandType.Text;
string result = Convert.ToString(objCommand.ExecuteScalar());
objCommand.Dispose();
if (result != "")
{
RecordExists = true;
}
return(RecordExists);
}
catch (SqlException ex)
{
ex.Data.Clear();
return(RecordExists);
}
catch (Exception ex)
{
ex.Data.Clear();
return(RecordExists);
}
finally
{ CloseConnection(); }
}
public string ExecuteQuery(string a_strSqlCommand) //Insert and update
{
if (OpenSqlConnection())
{
CloseConnection();
OpenSqlConnection();
}
System.Data.SqlClient.SqlCommand objCommand = new System.Data.SqlClient.SqlCommand(a_strSqlCommand);
try
{
objCommand.Connection = myConn;
objCommand.CommandType = CommandType.Text;
objCommand.ExecuteNonQuery();
objCommand.Dispose();
return("Executed successfully");
}
catch (Exception ex)
{
return(ex.Message.Trim());
}
finally
{
CloseConnection();
}
}
public string[] yread(string query, string[] columns) /*For Select*/
{
string[] y = new string[columns.Length];
var cn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString);
var cmd = new System.Data.SqlClient.SqlCommand(query, cn);
cn.Open();
var reader = cmd.ExecuteReader();
int i = 0;
while (reader.Read())
{
try
{
if (i > columns.Length)
{
break;
}
y[i] = reader[0].ToString().Trim();
i++;
}
catch
{
y[i] = "N/A";
}
}
reader.Close(); cmd.Dispose(); cn.Close(); cn.Dispose();
return(y);
}
//新增參數式SQL的執行方法
public int ExecNonQuerySQLCommand(System.Data.SqlClient.SqlCommand SqlComUpdate, ref int AffectedRowCount)
{
AffectedRowCount = 0;
if (CheckSession() == 1)
{
try
{
AffectedRowCount = SqlComUpdate.ExecuteNonQuery();
nRet = 0;
}
catch (Exception ex)
{
nRet = -1;
outMsg = ex.Message;
}
finally
{
SqlComUpdate.Dispose();
SqlComUpdate = null;
}
}
else
{
nRet = -1;
outMsg = "SQL Connection is Disconnect.";
}
return(nRet);
}
public int ExecuteSql(string SQLString, string content)
{
int result;
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString))
{
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(SQLString, sqlConnection);
System.Data.SqlClient.SqlParameter sqlParameter = new System.Data.SqlClient.SqlParameter("@content", System.Data.SqlDbType.NText);
sqlParameter.Value = content;
sqlCommand.Parameters.Add(sqlParameter);
try
{
sqlConnection.Open();
int num = sqlCommand.ExecuteNonQuery();
result = num;
}
catch (System.Data.SqlClient.SqlException ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
sqlConnection.Close();
}
}
return(result);
}
public int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
int result;
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString))
{
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(strSQL, sqlConnection);
System.Data.SqlClient.SqlParameter sqlParameter = new System.Data.SqlClient.SqlParameter("@fs", System.Data.SqlDbType.Image);
sqlParameter.Value = fs;
sqlCommand.Parameters.Add(sqlParameter);
try
{
sqlConnection.Open();
int num = sqlCommand.ExecuteNonQuery();
result = num;
}
catch (System.Data.SqlClient.SqlException ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
sqlConnection.Close();
}
}
return(result);
}
//Grabado del detalle del arqueo
private void guardaDetalleArqueo(int Celula, int AñoPed, int Pedido, double Saldo)
{
SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandText = "spCyCGuardaArqueoCobranzaPedido";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = _connection;
cmd.Transaction = tr;
cmd.Parameters.Add("@Arqueo", SqlDbType.Int).Value = _arqueo;
cmd.Parameters.Add("@Celula", SqlDbType.TinyInt).Value = Celula;
cmd.Parameters.Add("@AñoPed", SqlDbType.SmallInt).Value = AñoPed;
cmd.Parameters.Add("@Pedido", SqlDbType.Int).Value = Pedido;
cmd.Parameters.Add("@Saldo", SqlDbType.Money).Value = Saldo;
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
}
}
protected void editOrderButton_Click(object sender, EventArgs e)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_UPS"].ConnectionString))
{
int loopCounter = 0;
System.Data.SqlClient.SqlDataReader reader = null;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
//check for duplicates
String query = "SELECT * FROM [ROReport_ProductArrival]";
cmd.CommandTimeout = 300;
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;
conn.Open();
//cmd.Parameters.AddWithValue("@Order_Number", messageLabel1.Text);
Console.Write("Executing Reader");
reader = cmd.ExecuteReader();
while (reader.Read())
{
loopCounter++;
}
reader.Close();
cmd.Parameters.Clear();
cmd.Cancel();
cmd.Dispose();
conn.Close();
}
}
public DataSet getDataSet(SqlCommand cmd)
{
DataSet dsRet = new DataSet();
try
{
cmd.Connection = getConnect();
cmd.CommandType = CommandType.StoredProcedure;
using (DataSet ds = new DataSet())
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = cmd;
da.Fill(ds);
dsRet = ds;
}
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally // chay bt hoac chay co loi cung chay vao finnaly nay de dong ket noi va giai phong bo nho
{
if ((cmd.Connection != null) && (cmd.Connection.State == ConnectionState.Open))
cmd.Connection.Close(); //dong ket noi
cmd.Dispose(); //
}
return dsRet;
}
/// <summary>
/// Lấy danh sách doanh nghiệp
/// </summary>
/// <param name="Quy">Quý</param>
/// <param name="Nam">Năm</param>
/// <param name="All">Lấy doanh nghiệp theo năm hay theo quý và năm</param>
/// <returns></returns>
public static DataTable GetDoanhNghiep(String Quy,String Nam)
{
String DKQuy = "";
if (Quy.Equals("5") || Quy.Equals("6"))
DKQuy = "AND TCDN.iQuy=@iQuy-3";
else if (Quy.Equals("7"))
DKQuy = "";
else
DKQuy = "AND TCDN.iQuy=@iQuy";
DataTable dtDN = new DataTable();
String SQL = String.Format(@"SELECT TC.iID_MaDoanhNghiep,TC.sTenDoanhNghiep
FROM TCDN_DoanhNghiep AS TC
WHERE TC.iTrangThai=1
AND TC.iID_MaDoanhNghiep IN(
SELECT TCDN.iID_MaDoanhNghiep
FROM TCDN_KinhDoanh_ChungTuChiTiet AS TCDN
WHERE TCDN.iTrangThai=1
AND TCDN.iNamLamViec=@iNamLamViec
{0}
)", DKQuy);
SqlCommand cmd = new SqlCommand(SQL);
cmd.Parameters.AddWithValue("@iNamLamViec", Nam);
if (!String.IsNullOrEmpty(DKQuy))
{
cmd.Parameters.AddWithValue("@iQuy", Quy);
}
dtDN = Connection.GetDataTable(cmd);
cmd.Dispose();
return dtDN;
}
public List <District> GetAllByStsCity(int StateId, int CityId, bool All = false)
{
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlDataReader SDR = null;
List <District> ListTmp = new List <District>();
System.Data.SqlClient.SqlConnection Con = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ToString());
Con.Open();
try
{
string Query = "SELECT * FROM District where StateId=" + StateId.ToString() + " and CityId=" + CityId.ToString();
if (All)
{
Query = "SELECT * FROM District";
}
cmd = new System.Data.SqlClient.SqlCommand(Query, Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
District ObjTmp = new District
{
Id = int.Parse(SDR["Id"].ToString()),
Name = SDR["Name"].ToString(),
StateId = int.Parse(SDR["StateId"].ToString()),
CityId = int.Parse(SDR["CityId"].ToString()),
};
ListTmp.Add(ObjTmp);
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); SDR.Close(); Con.Close(); Con.Dispose(); Con = null; }
return(ListTmp);
}
// Protected implementation of Dispose pattern.
protected virtual void Dispose(bool disposing)
{
if (disposed)
{
return;
}
if (disposing)
{
// Free any other managed objects here.
objdb.Dispose();
dstCommon.Dispose();
adpCommon.Dispose();
cmd.Dispose();
objReturnObject.Dispose();
}
// Free any unmanaged objects here.
//objdb.Dispose();
//dstCommon.Dispose();
//adpCommon.Dispose();
//cmd.Dispose();
//objReturnObject.Dispose();
disposed = true;
}
protected DataTable ExecuteDataTable(string procName, SqlParameter[] parameters)
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
cmd = new System.Data.SqlClient.SqlCommand(procName, _connection);
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
cmd.CommandType = CommandType.StoredProcedure;
da.SelectCommand = cmd;
da.Fill(dt);
}
catch (Exception x)
{
dt.Rows.Add(9, x.Message);
}
finally
{
cmd.Dispose();
_connection.Close();
}
return(dt);
}
public List <Settings> GetAll()
{
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlDataReader SDR = null;
System.Collections.Generic.List <Settings> ListTmp = new System.Collections.Generic.List <Settings>();
Settings ObjTmp = null;
DBCon Obj = new DBCon();
try
{
string Query = "SELECT * FROM Settings ";
cmd = new System.Data.SqlClient.SqlCommand(Query, Obj.Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
ObjTmp = new Settings
{
SettingId = SDR.GetInt32(0),
KeyName = SDR.GetString(1),
KeyValue = SDR.GetString(2),
KeyDiscription = SDR.GetString(3),
};
ListTmp.Add(ObjTmp);
}
}
catch (System.Exception e) { e.ToString(); }
finally { cmd.Dispose(); SDR.Close(); Obj.Con.Close(); Obj.Con.Dispose(); Obj.Con = null; }
return(ListTmp);
}
internal DataTable DataQuerying(string CmdString, string TableName)
{
SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable(TableName);
cmd.CommandText = CmdString;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = _connection;
da.SelectCommand = cmd;
try
{
OpenConnection();
da.Fill(dt);
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
da.Dispose();
cmd.Dispose();
}
return(dt);
}
//Llamada al procedimiento de cancelación de arqueos
private int cancelaArqueo()
{
SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandText = "spCyCCancelaArqueoCobranza";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = _connection;
cmd.Transaction = tr;
cmd.Parameters.Add("@Arqueo", SqlDbType.Int).Value = _arqueo;
int rowsAffected = 0;
try
{
rowsAffected = cmd.ExecuteNonQuery();
_arqueo = Convert.ToInt32(cmd.Parameters["@Arqueo"].Value);
}
catch (SqlException ex)
{
rowsAffected = -1;
throw ex;
}
catch (Exception ex)
{
rowsAffected = -1;
throw ex;
}
finally
{
cmd.Dispose();
}
return(rowsAffected);
}
public void Delete(OrganizationalUnitEntity t)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = DALHelper.CreateSqlDbConnection();
cmd = new SqlCommand("usp_DeleteOrganizationalUnit", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", t.Id);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
cmd.Dispose();
conn.Dispose();
}
}
public void ConsultaListaCobranza()
{
SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
cmd.CommandText = "spCyCArqueoConsultaCobranza";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = _connection;
da.SelectCommand = cmd;
try
{
OpenConnection();
da.Fill(dsArqueo.Tables["PedidoCobranza"]);
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
da.Dispose();
cmd.Dispose();
}
}
/// <summary>
/// return identity value
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public long DoInsertReturnID(DataCollections _DataCol)
{
String sSQL;
object _ReturnObject = null;
sSQL = ToSQLString(DatabaseDAO.QueryTypes.Insert, _DataCol);
sSQL += ";SELECT @@IDENTITY AS 'Identity'";
if (ConnectValid() == true)
{
try
{
System.Data.SqlClient.SqlCommand _SqlCommand = new System.Data.SqlClient.SqlCommand(sSQL, Sqlcon);
if (hasTransaction == true)
{
_SqlCommand.Transaction = objTransaction;
}
_ReturnObject = _SqlCommand.ExecuteScalar();
_SqlCommand.Dispose();
_SqlCommand = null;
if (hasTransaction == false)
{
Sqlcon.Close();
}
return(long.Parse(_ReturnObject.ToString()));
}
catch (Exception obj)
{
throw new Exception(obj.Message);
}
}
else
{
return(0);
}
}
public object ExecuteSqlGet(string SQLString, string content)
{
object result;
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString))
{
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(SQLString, sqlConnection);
System.Data.SqlClient.SqlParameter sqlParameter = new System.Data.SqlClient.SqlParameter("@content", System.Data.SqlDbType.NText);
sqlParameter.Value = content;
sqlCommand.Parameters.Add(sqlParameter);
try
{
sqlConnection.Open();
object obj = sqlCommand.ExecuteScalar();
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
result = null;
}
else
{
result = obj;
}
}
catch (System.Data.SqlClient.SqlException ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
sqlConnection.Close();
}
}
return(result);
}
} //{0 city/district 1 means tehsil/TALUKA
public List <City> GetAllByState(int StateId)
{
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlDataReader SDR = null;
List <City> ListTmp = new List <City>();
System.Data.SqlClient.SqlConnection Con = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ToString());
Con.Open();
try
{
string Query = "SELECT * FROM City where StateId=" + StateId.ToString() + " ORDER BY Name ASC";
cmd = new System.Data.SqlClient.SqlCommand(Query, Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
City ObjTmp = new City
{
CityId = int.Parse(SDR["CityId"].ToString()),
Name = SDR["Name"].ToString(),
StateId = int.Parse(SDR["StateId"].ToString()),
Type = int.Parse(SDR["Type"].ToString())
};
ListTmp.Add(ObjTmp);
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); SDR.Close(); Con.Close(); Con.Dispose(); Con = null; }
return(ListTmp);
}
private void btnReadDBs_Click(object sender, EventArgs e)
{
lbDatabases.Items.Clear();
try
{
string Connect = GenerateConnectionString();
SqlConnection con = new SqlConnection(Connect);
con.Open();
SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_databases";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
lbDatabases.Items.Add((dr.GetString(0)));
}
dr.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public City GetOne(int Cityid)
{
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlDataReader SDR = null;
City ObjTmp = new City();
System.Data.SqlClient.SqlConnection Con = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ToString());
Con.Open();
try
{
string Query = "SELECT TOP 1 * FROM City where CityId=" + Cityid.ToString();
cmd = new System.Data.SqlClient.SqlCommand(Query, Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
ObjTmp.CityId = int.Parse(SDR["CityId"].ToString());
ObjTmp.Name = SDR["Name"].ToString();
ObjTmp.StateId = int.Parse(SDR["StateId"].ToString());
ObjTmp.Type = int.Parse(SDR["Type"].ToString());
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); SDR.Close(); Con.Close(); Con.Dispose(); Con = null; }
return(ObjTmp);
}
public IEnumerable<TipoUsuario> ObterTodos()
{
//Cria e abre a conexão com o banco de dados (essa string só serve para acessar o banco localmente)
//Ver mais strings de conexão em http://www.connectionstrings.com/
SqlConnection conn = new SqlConnection("Server=SERVIDOR\\INSTANCIA;Integrated Security=true;");
conn.Open();
SqlCommand cmd;
SqlDataReader reader;
List<TipoUsuario> lista = new List<TipoUsuario>();
//Cria um comando para selecionar registros da tabela, trazendo todas as
//pessoas que nasceram depois de 1/1/1900
cmd = new SqlCommand("SELECT ID_TIPO, DESCRICAO FROM TB_TIPO_USUARIO", conn);
reader = cmd.ExecuteReader();
//Obtém os registros, um por vez
while (reader.Read())
{
TipoUsuario t = new TipoUsuario();
t.id_tipo = reader.GetInt32(0);
t.descricao = reader.GetString(1);
lista.Add(t);
}
reader.Close();
reader.Dispose();
cmd.Dispose();
//Fecha a conexão ao final pois ela não é mais necessária
conn.Close();
conn.Dispose();
return lista;
}
private void loadCombo()
{
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["strConn"].ToString());
if ((Conn.State == ConnectionState.Closed))
{
Conn.Open();
}
System.Data.SqlClient.SqlCommand cmbreadOfficeLocations = new System.Data.SqlClient.SqlCommand("SELECT LocationID, CAST(LocationID AS nvarchar(5)) + ' - ' + LocationDes AS LocationDes FROM RefLocation WHERE (BlockLocation = 0)", Conn);
SqlDataReader cmbreader;
SqlCommand cmbUserCategory1 = new SqlCommand("SELECT UserCategoryCode, UserCategory FROM UserCategory where Active = 1", Conn);
SqlDataReader cmbUserCategoryReader;
cmbOffice.Items.Clear();
cmbUserCategory.Items.Clear();
cmbreader = cmbreadOfficeLocations.ExecuteReader();
cmbOffice.DataMember = "RefLocation";
cmbOffice.DataSource = cmbreader;
cmbOffice.DataValueField = "LocationID";
cmbOffice.DataTextField = "LocationDes";
cmbOffice.DataBind();
cmbreader.Close();
cmbUserCategoryReader = cmbUserCategory1.ExecuteReader();
cmbUserCategory.DataMember = "UserCategory";
cmbUserCategory.DataSource = cmbUserCategoryReader;
cmbUserCategory.DataValueField = "UserCategoryCode";
cmbUserCategory.DataTextField = "UserCategory";
cmbUserCategory.DataBind();
cmbUserCategory.Dispose();
cmbreadOfficeLocations.Dispose();
cmbUserCategoryReader.Close();
Conn.Close();
}
public Boolean ExcuteNonquery(SqlCommand cmd)
{
Boolean bRet = false;
try
{
cmd.Connection = getConnect();
//cmd.CommandText = "";
cmd.CommandType = CommandType.StoredProcedure;
//
if (cmd.ExecuteNonQuery() > 0) //kiem tra so luong ban ghi tac dung
bRet = true;
else
bRet = false;
}
catch (Exception ex)
{
bRet = false;
throw new Exception(ex.ToString());
}
finally // chay bt hoac chay co loi cung chay vao finnaly nay de dong ket noi va giai phong bo nho
{
if ((cmd.Connection != null) && (cmd.Connection.State == ConnectionState.Open))
cmd.Connection.Close(); //dong ket noi
cmd.Dispose(); //
}
return bRet;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
if (con.State != ConnectionState.Open)
con.Open();
for (int i = 0; i < dataGridView2.Rows.Count; i++)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE GRADOS SET NOMBRE= '" + Grados.Text + "' WHERE ID_GRADO = '" + dataGridView2.Rows[i].Cells[0].Value.ToString() + "'";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
catch (Exception ene)
{
MessageBox.Show(ene.ToString());
}
finally
{
if (con.State != ConnectionState.Closed)
con.Close();
}
this.Close();
}
// To Change status of 'Group' record of specific GroupId from database by stored procedure
public int ChangeGroupStatus(int GroupId, int LoggedInUser, string returnmsg, bool IsActive)
{
SqlConnection Conn = new SqlConnection(ConnStr);
Conn.Open();
// 'uspUpdateGroupStatus' stored procedure is used to Chnage Status of record in Group table
SqlCommand DCmd = new SqlCommand("uspUpdateGroupStatus", Conn);
DCmd.CommandType = CommandType.StoredProcedure;
DCmd.Parameters.AddWithValue("@GroupId", GroupId);
DCmd.Parameters.AddWithValue("@LoggedInUser", LoggedInUser);
DCmd.Parameters.AddWithValue("@IsActive", IsActive);
DCmd.Parameters.AddWithValue("@RetMsg", returnmsg);
try
{
return DCmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
DCmd.Dispose();
Conn.Close();
Conn.Dispose();
}
}
public List <string> GetDBs()
{
List <string> items = new List <string>();
try
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
sqlConn.Open();
SqlCommand sqlCom = new System.Data.SqlClient.SqlCommand();
sqlCom.Connection = sqlConn;
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom.CommandText = "sp_databases";
SqlDataReader sqlDR;
sqlDR = sqlCom.ExecuteReader();
while (sqlDR.Read())
{
items.Add(sqlDR.GetString(0));
}
sqlCom.Dispose();
}
catch (Exception ex) { }
return(items);
}
public bool tblPackageTime_Delete(int packageTimeId)
{
int i = 0;
try
{
if (ConnectionData._MyConnection.State == ConnectionState.Closed)
{
ConnectionData._MyConnection.Open();
}
SqlCommand cmd = new SqlCommand("DELETE FROM tblpackageTime WHERE packageTimeId = @packageTimeId",
ConnectionData._MyConnection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@packageTimeId", SqlDbType.Int).Value = packageTimeId;
i = cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
}
finally
{
ConnectionData._MyConnection.Close();
}
return i > 0;
}
public System.Collections.Generic.List <UsersMenu> GetAll()
{
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlDataReader SDR = null;
System.Collections.Generic.List <UsersMenu> ListTmp = new System.Collections.Generic.List <UsersMenu>();
UsersMenu ObjTmp = null;
SqlConnection Con = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ToString());
Con.Open();
try
{
string Query = "SELECT * FROM Menu ORDER BY Menu_Id ASC";
cmd = new System.Data.SqlClient.SqlCommand(Query, Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
ObjTmp = new UsersMenu
{
Menu_Id = int.Parse(SDR["Menu_Id"].ToString()),
MenuDisplayName = SDR["MenuDisplayName"].ToString(),
ParentMenuId = int.Parse(SDR["ParentMenuId"].ToString()),
MenuOrderNo = int.Parse(SDR["MenuOrderNo"].ToString()),
MenuLink = SDR["MenuLink"].ToString(),
Menu_Icon = SDR["Menu_Icon"].ToString(),
User_Types = SDR["User_Types"].ToString(),
Section = SDR["Section"].ToString()
};
ListTmp.Add(ObjTmp);
}
}
catch (System.Exception e) { e.ToString(); }
finally { cmd.Dispose(); SDR.Close(); Con.Close(); Con.Dispose(); Con = null; }
return(ListTmp);
}
/// <summary>
/// Danh sách đơn vị có dữ liệu
/// </summary>
/// <param name="NamBangLuong">Năm bảng lương</param>
/// <param name="ThangBangLuong">Tháng bảng lương</param>
/// <param name="iID_MaTrangThaiDuyet">Mã trạng thái duyệt</param>
/// <returns></returns>
public static DataTable DanhSach_DonVi(String NamBangLuong, String ThangBangLuong, String iID_MaTrangThaiDuyet)
{
String DKTrangThaiDuyet = "";
if (iID_MaTrangThaiDuyet == "2")
{
DKTrangThaiDuyet = " AND iID_MaTrangThaiDuyet=@iID_MaTrangThaiDuyet";
}
if (iID_MaTrangThaiDuyet == "-100")
{
DKTrangThaiDuyet = " AND iID_MaTrangThaiDuyet=-100";
}
String SQLDonVi = String.Format(@"SELECT DISTINCT iID_MaDonVi,sTenDonVi,sTenDonVi as TenHT
FROM l_BangLuongChiTiet
WHERE iNamBangLuong=@NamBangLuong AND
iThangBangLuong=@ThangBangLuong AND
iTrangThai=1 {0}", DKTrangThaiDuyet);
SqlCommand cmd = new SqlCommand(SQLDonVi);
cmd.Parameters.AddWithValue("@NamBangLuong", NamBangLuong);
cmd.Parameters.AddWithValue("ThangBangLuong", ThangBangLuong);
if (iID_MaTrangThaiDuyet != "-1")
{
cmd.Parameters.AddWithValue("@iID_MaTrangThaiDuyet", LuongCongViecModel.Get_iID_MaTrangThaiDuyet_DaDuyet(PhanHeModels.iID_MaPhanHeLuong));
}
DataTable dtDonVi = Connection.GetDataTable(cmd);
cmd.Dispose();
DataRow r = dtDonVi.NewRow();
r[2] = "-- Chọn đơn vị --";
dtDonVi.Rows.InsertAt(r, 0);
// dtDonVi.Dispose();
return dtDonVi;
}
protected void populateCompList()
{
using (var con = new System.Data.SqlClient.SqlConnection(conString))
{
using (var cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM tb_Company", con))
{
try
{
con.Open();
}
catch (Exception e)
{
//lblErrorMsg.Text = e.Message;
}
var reader = cmd.ExecuteReader();
ftCompany.DataSource = reader;
ftCompany.DataValueField = "companyName";
ftCompany.DataTextField = "companyName";
ftCompany.DataBind();
cmd.Dispose();
reader.Close();
}
}
}
}// fin FS_CLI_05_CPA_GET_PLANT_CENTER_ID
/// <summary>
/// Metdodo que devuelve los datos de un usuario
/// </summary>
/// <param name="USUARIO"></param>
/// <returns></returns>
public DataSet SP_04_GET_DATOS_USUARIO(string USUARIO)
{
Conectar();
DataSet result = new DataSet();
try
{
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("SP_04_GET_DATOS_USUARIO", cnn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = _commandTimeout;
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@USUARIO", System.Data.SqlDbType.NVarChar));
command.Parameters["@USUARIO"].Value = USUARIO;
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(command);
da.Fill(result);
command.Dispose();
}
catch (Exception e)
{
result = null;
Console.WriteLine(e.StackTrace);
}
finally
{
Desconectar();
}
return(result);
}// fin SP_04_GET_DATOS_USUARIO
protected void Button2_Click(object sender, EventArgs e)
{
//Rentalid = new List<string>();
string strConnectionString = "Addr =192.168.10.172;User Id=sa;password=P@ssw0rd;Initial Catalog=DVDRentalDB_B;Integrated Security =false";
SqlConnection hConnection = (new SqlConnection(strConnectionString));
hConnection.Open();
System.Data.SqlClient.SqlCommand hCommand = hConnection.CreateCommand();
hCommand.CommandText = "SELECT [dbo].[DVDMaster].[Name],[dbo].[Member].[LoginName],[dbo].[Rental].[Id] FROM [dbo].[Rental] INNER JOIN [dbo].[DVDMaster] ON [dbo].[DVDMaster].[id] = [dbo].[Rental].[DvdId] INNER JOIN [dbo].[Member] ON [dbo].[Rental].[UserId] = [dbo].[Member].[Id]";
System.Data.SqlClient.SqlDataReader hReader = hCommand.ExecuteReader();
hCommand.Dispose();
while (hReader.Read())
{
if (hReader["LoginName"].ToString() == TextBox1.Text)
{
cbRentalId.Items.Add(hReader["Id"].ToString());
CheckBoxList1.Items.Add(hReader["Name"].ToString());
}
}
if (CheckBoxList1.Items.Count == 0)
{
_Labl3.Text = "レンタルされている商品はありません";
}
hReader.Close();
hConnection.Close();
hConnection.Dispose();
}
