Dispose() protected method

protected Dispose ( bool disposing ) : void
disposing bool
return void
Beispiel #1
1
    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();
        }
    }
Beispiel #2
1
        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;
        }
Beispiel #3
1
        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 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 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");

             }
        }
Beispiel #6
0
 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;
 }
Beispiel #7
0
 /// <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;
         }
     }
 }
Beispiel #8
0
        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 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;
            }
        }
        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();
            }
        }
Beispiel #11
0
 /// <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;
         }
     }
 }
        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;
            }
        }
Beispiel #13
0
        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();
                    }
                }
            }
        }
Beispiel #14
0
    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 = '" + pwd + "' ", 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";
        }
    }
    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();

        }
    }
Beispiel #16
0
        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();
        }
Beispiel #17
0
    private void LoadDateTime()
    {
        string strcon = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString();
        bool functionReturnValue = false;
        SqlCommand CmdCustomer = new SqlCommand();
        string QryStr = "";
        System.Data.SqlClient.SqlCommand UpdateCmd = null;
        int Status = 0;
        conn = new SqlConnection(strcon);
        conn.Open();
        CmdCustomer = new SqlCommand("spSearch", conn);
        CmdCustomer.CommandType = CommandType.StoredProcedure;

        CmdCustomer.Parameters.AddWithValue("@ColumnsTxt", "ModDateTime");
        CmdCustomer.Parameters.AddWithValue("@TableName", "InternalMemo");
        CmdCustomer.Parameters.AddWithValue("@flg", 1);

        CmdCustomer.Parameters.Add("@ReturnVal", SqlDbType.VarChar, 10).Direction = ParameterDirection.ReturnValue;

        try
        {
            string st1 = CmdCustomer.ExecuteScalar().ToString();
            lblDateTime.Text = st1;
            if (CmdCustomer.Parameters["@ReturnVal"].Value.ToString() == "1")
            {

                CmdCustomer.Dispose();
                Session["SaveFlag"] = 0;
                functionReturnValue = true;
            }
            else
            {
                CmdCustomer.Dispose();
                Session["SaveFlag"] = 0;
                functionReturnValue = false;
            }
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        catch (Exception ex)
        {

            functionReturnValue = false;
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
            }
        }
    }
        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();
        }
Beispiel #19
0
    protected void gvGroupDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Label lblGDNO = (Label)gvGroupDetails.Rows[e.RowIndex].FindControl("lblGD");
        string GDNO = lblGDNO.Text;

        bool functionReturnValue = false;
        SqlCommand CmdCustomer = new SqlCommand();
        string QryStr = "";
        System.Data.SqlClient.SqlCommand UpdateCmd = null;
        int Status = 0;
        conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString());
        conn.Open();
        CmdCustomer = new SqlCommand("spGroupDetail", conn);
        CmdCustomer.CommandType = CommandType.StoredProcedure;
        CmdCustomer.Parameters.AddWithValue("@GDNo", GDNO);

        CmdCustomer.Parameters.AddWithValue("@Trans", "d");
        CmdCustomer.Parameters.Add("@ReturnVal", SqlDbType.VarChar, 10).Direction = ParameterDirection.ReturnValue;
        try
        {

            CmdCustomer.ExecuteNonQuery();
            if (CmdCustomer.Parameters["@ReturnVal"].Value.ToString() == "0")
            {
                CmdCustomer.Dispose();
                Session["SaveFlag"] = 0;
                functionReturnValue = false;

            }
            else
            {

                    functionReturnValue = true;
                CmdCustomer.Dispose();
                //enable the text box

                //Session["SaveFlag"] = 0;
                //functionReturnValue = true;
            }
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        catch (Exception ex)
        {

            functionReturnValue = false;
        }
        UpdateView_GroupDetails(Session["GID"].ToString());
    }
Beispiel #20
0
        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 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;
        }
        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();
            }
        }
Beispiel #24
0
 /// <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;
 }
Beispiel #25
0
    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;
    }
Beispiel #26
0
        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();
         }
 }
Beispiel #28
0
 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;
 }
Beispiel #29
0
 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;
 }
 /// <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;
 }