C# DataAccess.OpenConnection方法代码示例

    public void LoadJobDetails()
    {
        DataAccess dataaccess = new DataAccess();
        SqlConnection Sqlcon = dataaccess.OpenConnection();

        String SQLQuery = "SELECT p.PostJobId, p.JobTitle,p.JobDesc,p.QulificationId,p.SpecificationId,p.ExpInYear,p.ExpInMonth,p.JobTypeId,p.CompanyId,p.Active,p.Location,C.CompanyName,C.CompanyLogo,Q.QualificationName from PostJobMaster as p inner join CompanyMaster as C on C.CompanyId = p.CompanyId inner join QualificationMaster as Q on Q.QualificationId=p.QulificationId where p.CompanyId =" + PostJobId + "";

        SqlCommand command = new SqlCommand(SQLQuery, Sqlcon);
        SqlDataReader Dr;

        Dr = command.ExecuteReader();

        while (Dr.Read())
        {

            lblJobTitle.Text = Dr["JobTitle"].ToString();
            lblCompName.Text = Dr["CompanyName"].ToString();
            imgCompLogo.ImageUrl = "~/CompanyLogo/" + Dr["CompanyLogo"].ToString();
            lblQuil.Text = Dr["QualificationName"].ToString();
            lblExpYear.Text = Dr["ExpInYear"].ToString();
            lblExpMonths.Text = Dr["ExpInMonth"].ToString();
            lblLocation.Text = Dr["Location"].ToString();
            lblJobDesc.Text = Dr["JobDesc"].ToString();
        }

        Dr.Close();
    }

    private bool YourValidationFunction(string UserName, string Password, int Active)
    {
        DataAccess dataaccess = new DataAccess();
            SqlConnection Sqlcon = dataaccess.OpenConnection();
            bool boolReturnValue = false;
            String SQLQuery = "SELECT CompanyId, CompanyEmail, Password, Active FROM CompanyMaster where CompanyEmail='"+Login1.UserName+"' and Password='"+Login1.Password+"' and Active='"+Active+"'";
            SqlCommand command = new SqlCommand(SQLQuery, Sqlcon);
            SqlDataReader Dr;

        Dr = command.ExecuteReader();

        while (Dr.Read())

        {

            if ((UserName == Dr["CompanyEmail"].ToString()) & (Password == Dr["Password"].ToString()) )

            {
                Session["CompanyId"] = Dr["CompanyId"].ToString();

                boolReturnValue = true;

            }

            Dr.Close();

            return boolReturnValue;

        }

        return boolReturnValue;
    }

    public void LoadRejectedCompany()
    {
        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "getCompany";
                cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@Active", SqlDbType.Int));

                cmd.Parameters["@Active"].Value = 2;

                cmd.Parameters["@Action"].Value = "select";
                cmd.Parameters.Add("@Exists", SqlDbType.Int).Direction = ParameterDirection.Output;
                SqlAda = new SqlDataAdapter(cmd);
                ds = new DataSet();
                SqlAda.Fill(ds);

                grdRejected.DataSource = ds;
                grdRejected.DataBind();

            }
        }
    }

    public void LoadJobType()
    {
        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "GetJobType";
                cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
                cmd.Parameters["@Action"].Value = "select";
                cmd.Parameters.Add("@Exists", SqlDbType.Int).Direction = ParameterDirection.Output;
                SqlAda = new SqlDataAdapter(cmd);
                ds = new DataSet();
                SqlAda.Fill(ds);
                ddlJobType.DataSource = ds;
                ddlJobType.DataTextField = "JobTypeName";
                ddlJobType.DataValueField = "JobTypeId";
                ddlJobType.DataBind();
                if (ddlJobType.Items.Count >= 1)
                {
                    ListItem lstitem = new ListItem();
                    lstitem.Text = "[Select]";
                    lstitem.Value = "0";
                    ddlJobType.Items.Insert(0, lstitem);
                }
            }
        }
    }

    public void LoadPostedJob()
    {
        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "getPostJob";
                cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@CompanyId", SqlDbType.Int));

                cmd.Parameters["@CompanyId"].Value = int.Parse(CompanyId);

                cmd.Parameters["@Action"].Value = "select";
                cmd.Parameters.Add("@Exists", SqlDbType.Int).Direction = ParameterDirection.Output;
                SqlAda = new SqlDataAdapter(cmd);
                ds = new DataSet();
                SqlAda.Fill(ds);
                GrdLoadJobCompany.DataSource = ds;
                GrdLoadJobCompany.DataBind();
            }
        }
    }

    public void LoadStatus()
    {
        string StudentId = Session["StudentId"].ToString();

        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "getStatus";
                cmd.Parameters.Add(new SqlParameter("@StudentId",SqlDbType.Int));

                cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
                cmd.Parameters["@Action"].Value = "select";
                cmd.Parameters["@StudentId"].Value = StudentId;
                cmd.Parameters.Add("@Exists", SqlDbType.Int).Direction = ParameterDirection.Output;
                SqlAda = new SqlDataAdapter(cmd);
                ds = new DataSet();
                SqlAda.Fill(ds);
                GrdLoadJobCompany.DataSource = ds;
                GrdLoadJobCompany.DataBind();
            }
        }
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "InsertITJob";
                cmd.Parameters.Add(new SqlParameter("@Exists", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@ITJobHeading", SqlDbType.VarChar, 100));
                cmd.Parameters.Add(new SqlParameter("@ITJobDesc", SqlDbType.NVarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@ITJobDate", SqlDbType.DateTime));

                cmd.Parameters["@ITJobHeading"].Value = txtITJob.Text;
                cmd.Parameters["@ITJobDesc"].Value = txtITDesc.Text;
                cmd.Parameters["@ITJobDate"].Value = System.DateTime.Now;

                cmd.Parameters["@Exists"].Value = 0;
                cmd.ExecuteNonQuery();
                int retVal = (int)cmd.Parameters["@Exists"].Value;
            }
        }
        LoadData();
        PanelShow.Visible = true;
        PanelAdd.Visible = false;
        txtITJob.Text = "";
        txtITDesc.Text = "";
    }

    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        string filename = string.Empty;

        if (FileUpload1.PostedFile != null && FileUpload1.PostedFile.FileName != "")
        {
            filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
            FileUpload1.SaveAs(Server.MapPath("~/CompanyLogo/" + filename));

        }

        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "InsertCompany";

                cmd.Parameters.Add(new SqlParameter("@Exists", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@Active", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.VarChar, 100));
                cmd.Parameters.Add(new SqlParameter("@CompanyDesc", SqlDbType.VarChar, 5000));
                cmd.Parameters.Add(new SqlParameter("@CompanyAdd", SqlDbType.VarChar, 500));
                cmd.Parameters.Add(new SqlParameter("@CompanyLogo", SqlDbType.VarChar, 500));
                cmd.Parameters.Add(new SqlParameter("@CompanyRegNo", SqlDbType.VarChar, 100));
                cmd.Parameters.Add(new SqlParameter("@CompanyMobNo", SqlDbType.VarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@CompanyEmail", SqlDbType.VarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar, 100));

                cmd.Parameters["@CompanyDesc"].Value = txtCompDesc.Text;
                cmd.Parameters["@Password"].Value = txtPass.Text;
                cmd.Parameters["@CompanyName"].Value = txtcmpName.Text;
                cmd.Parameters["@CompanyAdd"].Value = txtcmpAdd.Text;
                cmd.Parameters["@CompanyLogo"].Value = filename;
                cmd.Parameters["@CompanyRegNo"].Value = txtCmpRegNo.Text;
                cmd.Parameters["@CompanyMobNo"].Value = txtcmpMobNo.Text;
                cmd.Parameters["@CompanyEmail"].Value = txtcmpEmail.Text;
                cmd.Parameters["@Active"].Value = 0;

                cmd.Parameters["@Exists"].Value = 0;
                int retVal = cmd.ExecuteNonQuery();
                if (retVal == 1)
                {
                    txtcmpAdd.Text = "";
                    txtcmpEmail.Text = "";
                    txtcmpMobNo.Text = "";
                    txtcmpName.Text = "";
                    txtCmpRegNo.Text = "";
                    txtPass.Text = "";

                }
            }
        }
    }

    public void loadData()
    {
        string StudentId = Session["StudentId"].ToString();
        DataAccess dataaccess = new DataAccess();
        SqlConnection Sqlcon = dataaccess.OpenConnection();
        // bool boolReturnValue = false;
        String SQLQuery = @"SELECT StudentId, FirstName,LastName ,
        Email,Password,NationalityId,LocationId,MobNo,
        Gender,JobTypeId,TotalYearExp,TotalMonthExp,
        IndustryId, FunctionId, KeySkills,QualificationId,
        SpecificationId, YearOfPassing, InstituteId, OtherInstitute,
        ResumeTitle, Resume, Active,ProfileImage,DOB,Address from StudentMaster where StudentId='" + StudentId + "'";
        SqlCommand command = new SqlCommand(SQLQuery, Sqlcon);
        SqlDataReader Dr;
        Dr = command.ExecuteReader();

        while (Dr.Read())
        {

            txtFname.Text = Dr["FirstName"].ToString();
            txtLname.Text = Dr["LastName"].ToString();
            txtEmail.Text = Dr["Email"].ToString();
            txtMobNo.Text = Dr["MobNo"].ToString();
            txtOtherInsti.Text = Dr["OtherInstitute"].ToString();
            txtDOB.Text = Dr["DOB"].ToString();
            txtPAdd.Text = Dr["Address"].ToString();
            txtResumeT.Text = Dr["ResumeTitle"].ToString();
            txtSkills.Text = Dr["KeySkills"].ToString();
            ddlCIndustry.SelectedValue = Dr["IndustryId"].ToString();
            ddlFunction.SelectedValue = Dr["FunctionId"].ToString();
            ddlInstitute.SelectedValue = Dr["InstituteId"].ToString();
            ddlJobType.SelectedValue = Dr["JobTypeId"].ToString();
            ddlLocation.SelectedValue = Dr["LocationId"].ToString();
            ddlMExp.SelectedValue = Dr["TotalMonthExp"].ToString();
            ddlNationality.SelectedValue = Dr["NationalityId"].ToString();
            ddlPassingY.SelectedValue = Dr["YearOfPassing"].ToString();
            ddlQul.SelectedValue = Dr["QualificationId"].ToString();
            ddlSpeci.SelectedValue = Dr["SpecificationId"].ToString();
            ddlYExp.SelectedValue = Dr["TotalYearExp"].ToString();
            //   FileUpload1.FileName = Dr["Resume"].ToString();

            imgPro.ImageUrl = "~/Profile Image/" + Dr["ProfileImage"].ToString();
            resumeTitle = Dr["Resume"].ToString();
            ImageName = Dr["ProfileImage"].ToString();

        }
        Dr.Close();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        string StudentId = Session["StudentId"].ToString();

        DataAccess dataaccess = new DataAccess();
        SqlConnection Sqlcon = dataaccess.OpenConnection();
           // bool boolReturnValue = false;
        String SQLQuery = @"select FirstName,LastName,Email,Password,SM.NationalityId,NM.Nationality,SM.LocationId,
        LM.LocationName,MobNo,Gender,SM.JobTypeId,JTM.JobTypeName,TotalYearExp,TotalMonthExp,
        SM.IndustryId,I.IndustryName, SM.FunctionId,FM.FunctionName, KeySkills,SM.QualificationId,
        QM.QualificationName,SM.SpecificationId,SPM.SpecificationName,YearOfPassing,
        SM.InstituteId,IM.InstituteName,OtherInstitute,ResumeTitle,Resume, Active,ProfileImage,DOB,Address
        from StudentMaster as SM
        inner join IndustryMaster as I on I.IndustryId=SM.IndustryId
        inner join NationalityMaster as NM on NM.NationalityId=SM.NationalityId
        inner join LocationMaster as LM on LM.LocationId=SM.LocationId
        inner join JobTypeMaster as JTM on JTM.JobTypeId=SM.JobTypeId
        inner join FunctionMaster as FM on FM.FunctionId=SM.FunctionId
        inner join QualificationMaster as QM on QM.QualificationId=SM.QualificationId
        inner join SpecificationMaster as SPM on SPM.SpecificationId=SM.SpecificationId
        inner join InstituteMaster as IM on IM.InstituteId=SM.InstituteId
        where StudentId ='" + StudentId + "'";
        SqlCommand command = new SqlCommand(SQLQuery, Sqlcon);
        SqlDataReader Dr1;
        Dr1 = command.ExecuteReader();
        while (Dr1.Read())
        {
            lblGender.Text = Dr1["Gender"].ToString();
            lblName.Text = (Dr1["FirstName"].ToString()) +" "+ (Dr1["LastName"].ToString());
            lblAdd.Text = Dr1["Address"].ToString();
            lblEmail.Text = Dr1["Email"].ToString();
            lblExpMonth.Text = Dr1["TotalMonthExp"].ToString();
            lblExpYear.Text = Dr1["TotalYearExp"].ToString();
            lblFunction.Text = Dr1["FunctionName"].ToString();
            lblDOB.Text = Dr1["DOB"].ToString();
            lblIndustry.Text = Dr1["IndustryName"].ToString();
            lblMob.Text = Dr1["MobNo"].ToString();
            lblLocation.Text = Dr1["LocationName"].ToString();
            lblNat.Text = Dr1["Nationality"].ToString();
            lblSkill.Text = Dr1["KeySkills"].ToString();
            lblYearOfPassing.Text = Dr1["YearOfPassing"].ToString();
            lblJobType.Text = Dr1["JobTypeName"].ToString();
            lblSpeci.Text = Dr1["SpecificationName"].ToString();
            Image1.ImageUrl = "~/Profile Image/" + Dr1["ProfileImage"].ToString();
        }
        Dr1.Close();
    }

    protected void GrdLoadJobCompany_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        string StudentId = Session["StudentId"].ToString();

        int indexrow = e.RowIndex;
        string tid = ((Label)GrdLoadJobCompany.Rows[indexrow].FindControl("PostJobId")).Text;
           // Session["PostJobId"] = tid;
        string Cid = ((Label)GrdLoadJobCompany.Rows[indexrow].FindControl("CompanyId")).Text;
           // Response.Redirect("~/MyAccount.aspx");
        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "InsertJobApplied";
                cmd.Parameters.Add(new SqlParameter("@Exists", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@StudentId", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@CompanyId", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@PostJobId", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar,500));

                cmd.Parameters["@StudentId"].Value = int.Parse( StudentId);
                cmd.Parameters["@CompanyId"].Value = int.Parse(Cid);
                cmd.Parameters["@PostJobId"].Value = int.Parse(tid);
                cmd.Parameters["@Status"].Value = "In Process";

                cmd.Parameters["@Exists"].Value = 0;
                cmd.ExecuteNonQuery();
                int retVal = (int)cmd.Parameters["@Exists"].Value;
                if (retVal == 1)
                {
                    Response.Redirect("~/Student/AppliedSuccess.aspx");

                }
            }
        }
    }

    private void LoadMgtJOb()
    {
        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "GetMgtJob";
                cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
                cmd.Parameters["@Action"].Value = "select";
                cmd.Parameters.Add("@Exists", SqlDbType.Int).Direction = ParameterDirection.Output;
                SqlAda = new SqlDataAdapter(cmd);
                ds = new DataSet();
                SqlAda.Fill(ds);

                Repeater3.DataSource = ds;
                Repeater3.DataBind();
            }
        }
    }

    public void LoadCompanyDetails()
    {
        DataAccess dataaccess = new DataAccess();
                SqlConnection Sqlcon = dataaccess.OpenConnection();

                String SQLQuery = "SELECT CompanyId , CompanyName , CompanyAdd, CompanyLogo,CompanyDesc,CompanyRegNo, CompanyMobNo, CompanyEmail, Active from CompanyMaster where  CompanyId = "+CompanyId+"";
                SqlCommand command = new SqlCommand(SQLQuery, Sqlcon);
                SqlDataReader Dr;

                Dr = command.ExecuteReader();

                while (Dr.Read())
                {

                        lblCompanyName.Text = Dr["CompanyName"].ToString();
                        lblCompanyDesc.Text=  Dr["CompanyDesc"].ToString();
                        imgCompanyLogo.ImageUrl="~/CompanyLogo/"+ Dr["CompanyLogo"].ToString();
                        lblCompanyNo.Text=Dr["CompanyMobNo"].ToString();
                        lblCompRegNo.Text=Dr["CompanyRegNo"].ToString();
                        lblEmail.Text=Dr["CompanyEmail"].ToString();
                }

                    Dr.Close();
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "insertPostJob";
                cmd.Parameters.Add(new SqlParameter("@Exists", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@QulificationId", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@JobTitle", SqlDbType.VarChar,100));
                cmd.Parameters.Add(new SqlParameter("@JobDesc", SqlDbType.VarChar, 500));
                cmd.Parameters.Add(new SqlParameter("@SpecificationId", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@ExpInYear", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@ExpInMonth", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@JobTypeId", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@CompanyId", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@Active", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@Location", SqlDbType.VarChar, 100));

                cmd.Parameters["@Location"].Value = txtLocation.Text;
                cmd.Parameters["@QulificationId"].Value = ddlQul.SelectedValue;
                cmd.Parameters["@JobTitle"].Value = txtJobTitle.Text;
                cmd.Parameters["@JobDesc"].Value = txtJobDesc.Text;
                cmd.Parameters["@SpecificationId"].Value = ddlSpeci.SelectedValue;
                cmd.Parameters["@ExpInYear"].Value = ddlYear.SelectedValue;
                cmd.Parameters["@ExpInMonth"].Value = ddlMonth.SelectedValue;
                cmd.Parameters["@JobTypeId"].Value = ddlJobType.SelectedValue;
                cmd.Parameters["@CompanyId"].Value = Session["CompanyId"].ToString();
                cmd.Parameters["@Active"].Value = 1;

                cmd.Parameters["@Exists"].Value = 0;
                cmd.ExecuteNonQuery();
                int retVal = (int)cmd.Parameters["@Exists"].Value;
            }
        }
        LoadPostedJob();
        PanelAdd.Visible = false;
        PanelShow.Visible = true;
           // txtQualificationName.Text = "";
    }

    protected void grdRejected_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        //kuldeep
        int indexrow = e.RowIndex;
        short tid = short.Parse(((Label)grdRejected.Rows[indexrow].FindControl("CompanyId")).Text);

        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "getCompany";
                cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@CompanyId", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@Active", SqlDbType.Int));

                cmd.Parameters["@Active"].Value = 3;
                cmd.Parameters["@CompanyId"].Value = tid;
                cmd.Parameters["@Action"].Value = "Modify";
                cmd.Parameters.Add("@Exists", SqlDbType.Int).Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                LoadRejectedCompany();
            }
        }
    }