C# DataAccess类代码示例

    protected void Page_Load(object sender, EventArgs e)
    {
        Response.Write("{\"employees\": [");
        string connectionstring =
            System.Configuration.ConfigurationManager.ConnectionStrings["HuberGoDaddy"].ConnectionString;
        DataAccess da = new DataAccess();
        System.Data.DataSet ds = da.getDataSet(connectionstring,
            "select employeeid, firstname, lastname from huber_employees");

        //if there are no employees returned
        if (ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0)
        {
            Response.Write("{}");
        }
        else
        {
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                Response.Write("{\"employeeid\": \"" +
                    ds.Tables[0].Rows[i].ItemArray[0].ToString() +
                    "\",\"firstname\":" +
                    "\"" + ds.Tables[0].Rows[i].ItemArray[1].ToString() + "\"}");

                if (i<ds.Tables[0].Rows.Count-1)
                {
                    Response.Write(",");
                }

            }
        }

        Response.Write("]}");
    }

        public void Assignment_SelectAddSelectDeleteSelect_OK()
        {
            using (System.Transactions.TransactionScope updateTransaction =
             new System.Transactions.TransactionScope())
            {
                string connectionString = GetConnectionstring();
                DataAccess d2 = new DataAccess(connectionString);

                List<Assignment> assignmentsBefore = d2.GetAssignments();

                Assignment assignment = new Assignment();
                assignment.AssignmentId = -1;
                assignment.AssignmentName = "asfdasfasdfsaf";

                Assignment assignmentAfterSave = d2.SaveAssignment(assignment);

                Assignment assignmentGetById = d2.GetAssignmentByID(assignment.AssignmentId);

                Assert.AreNotEqual(assignment.AssignmentId, -1);
                Assert.AreEqual(assignment.AssignmentName, assignmentGetById.AssignmentName);

                List<Assignment> assignmentsAfterSave = d2.GetAssignments();

                Assert.AreEqual(assignmentsAfterSave.Count, assignmentsBefore.Count + 1);

                d2.DeleteAssignment(assignment.AssignmentId);

                List<Assignment> assignmentsAfterDelete = d2.GetAssignments();

                Assert.AreEqual(assignmentsBefore.Count, assignmentsAfterDelete.Count);

            }
        }

    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();
            }
        }
    }

    protected void btnEmployee_Click(object sender, EventArgs e)
    {
        Response.Write("{\"employees\": [");
        string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["OrtegaGoDaddy"].ConnectionString;
        DataAccess da = new DataAccess();

        System.Data.DataSet ds = da.getDataSet(connectionstring, "select employeeId, employeeFname, employeeLname from Ortega_Employees");

        if (ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0)
        {
            Response.Write("{}");
        }
        else
        {
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                Response.Write("{\"employeeid\": \"" +
                    ds.Tables[0].Rows[i].ItemArray[0].ToString() + "\",\"firstname\":" + "\"" + ds.Tables[0].Rows[i].ItemArray[1].ToString() + "\"}");

                if (i < ds.Tables[0].Rows.Count - 1)
                {
                    Response.Write(",");
                }

            }
        }

        Response.Write("]}");
    }

    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 DataAccess.DataContracts.IPerson AddPerson(DataAccess.DataContracts.IPerson person)
        {
            _people.Add(person);
            person.PersonId = _people.Count();

            return person;
        }

        public int CopyAndUpdateFields(int SourceID, int? NguoiDungID, int? DauGiaID, decimal? TraGia,
            DateTime? ThoiGianTraGia, string Bak1, string Bak2, int? Bak3, bool? Bak4)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                int iID = 0;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "CopyAndUpdateFieldsTraGia";

                sqlCmd.Parameters.Add("@SourceID", SqlDbType.Int).Value = SourceID;
                sqlCmd.Parameters.Add("@NguoiDungID", SqlDbType.Int).Value = NguoiDungID;

                sqlCmd.Parameters.Add("@DauGiaID", SqlDbType.Int).Value = DauGiaID;

                sqlCmd.Parameters.Add("@TraGia", SqlDbType.Money).Value = TraGia;

                sqlCmd.Parameters.Add("@ThoiGianTraGia", SqlDbType.DateTime).Value = ThoiGianTraGia;

                sqlCmd.Parameters.Add("@Bak1", SqlDbType.NVarChar, 50).Value = Bak1;

                sqlCmd.Parameters.Add("@Bak2", SqlDbType.NVarChar, 50).Value = Bak2;

                sqlCmd.Parameters.Add("@Bak3", SqlDbType.Int).Value = Bak3;

                sqlCmd.Parameters.Add("@Bak4", SqlDbType.Bit).Value = Bak4;

                iID = objDataAccess.ExecuteScalar(sqlCmd);
                return iID;
            }
        }

 protected void btn_Save_Click1(object sender, EventArgs e)
 {
     DataAccess da = new DataAccess();
     String res = da.updateAccountabilityInfo(Convert.ToInt32(hf_Accountability_Id.Value),
                                             Convert.ToInt32(ddl_Acct_Next_Level.SelectedValue),
                                             txt_Note.Text);
 }

    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            DataAccess data = new DataAccess();
            ddChuyenMuc.DataSource = data.GetAllChuyenMuc();
            ddChuyenMuc.DataValueField = "id";
            ddChuyenMuc.DataTextField = "tenCM";
            ddChuyenMuc.DataBind();
            BaiViet bv = new BaiViet();
            bv = data.GetABaiViet(Convert.ToInt32(Request.QueryString["id"]));
            txtTieuDe.Text = bv.tieuDe.ToString();
            ddChuyenMuc.Text = bv.id_cm.ToString();
            oldHinhAnh.ImageUrl = bv.hinhAnh.ToString();
            txtNoiDung.Text = bv.noiDung;
            txtMota.Text = bv.moTa;
            txtTuKhoa.Text = bv.tuKhoa;
            if (bv.trangThai == 1) rd1.Checked = true;
            else rd0.Checked = true;

            //if (rdv.Equals(1)
            //{
            //    rdtrangThai.Items.FindByValue("1").Selected = true;
            //    rdtrangThai.Items.FindByValue("0").Selected = false;
            //}
            //else
            //{
            //    rdtrangThai.Items.FindByValue("0").Selected = true;
            //    rdtrangThai.Items.FindByValue("1").Selected = false;
            //}

        }
    }

        public static string CalculateQRests(int SpareID)
        {
            SpareView spare = SpareContainer.Instance.GetSpare(SpareID);
            DataAccess da = new DataAccess();
            string xml = "";
            List<warehouse> warehouses = da.GetWarehouses();
            List<double> Qs = new List<double>();
            xml += "<r>";

            // Q total
            double q0 = spare.QRest.HasValue ? spare.QRest.Value : 0;
            Qs.Add(q0);

            // all actual incomes
            List<SpareInSpareIncomeView> incomes = da.GetActualIncomes();

            // Q by warehouses
            foreach (warehouse w in warehouses)
            {
                // incomes by warehouse
                decimal? q = incomes.Where(i => (i.WarehouseID == w.id && i.SpareID == SpareID)).Sum(i => i.QRest);
                if (q.HasValue)
                    Qs.Add((double)q.Value);
                else
                    Qs.Add(0);
            }

            foreach (double q in Qs)
                xml += "<w q=\"" + q.ToString() + "\"/>";
            xml += @"</r>";

            // save sml to db
            da.SpareEdit(SpareID, xml);
            return xml;
        }

        public int CopyAndUpdateFields(int SourceID, int? NguoiDungID, string HovaTenNguoiNhan, string DiaChiNguoiNhan,
            string EmailNguoiNhan, string DTDDNguoiNhan, string CMTND)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                int iID = 0;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "CopyAndUpdateFieldsDonHang";

                sqlCmd.Parameters.Add("@SourceID", SqlDbType.Int).Value = SourceID;
                sqlCmd.Parameters.Add("@NguoiDungID", SqlDbType.Int).Value = NguoiDungID;

                sqlCmd.Parameters.Add("@HovaTenNguoiNhan", SqlDbType.NVarChar, 50).Value = HovaTenNguoiNhan;

                sqlCmd.Parameters.Add("@DiaChiNguoiNhan", SqlDbType.NVarChar, 1000).Value = DiaChiNguoiNhan;

                sqlCmd.Parameters.Add("@EmailNguoiNhan", SqlDbType.NVarChar, 50).Value = EmailNguoiNhan;

                sqlCmd.Parameters.Add("@DTDDNguoiNhan", SqlDbType.NVarChar, 50).Value = DTDDNguoiNhan;

                sqlCmd.Parameters.Add("@CMTND", SqlDbType.VarChar, 9).Value = CMTND;

                iID = objDataAccess.ExecuteScalar(sqlCmd);
                return iID;
            }
        }

        /// <summary>
        /// Constructor (Solo para uso Externo) de la Clase BaseClass.
        /// </summary>
        /// <param name="bizSrv"></param>
        /// <param name="callingAssembly"></param>
        /// <param name="creteDBConnection"></param>
        protected BaseClass(BizServer bizSrv, string callingAssembly, bool createDBConnection)
        {
            if (callingAssembly == Assembly.GetExecutingAssembly().GetName().Name)
                throw new Exception("This constructor must be used only from external assembly.");

            try
            {
                if (bizSrv == null)
                    throw new ArgumentNullException("BizServer");

                _oBizServer = bizSrv;

                if (createDBConnection)
                {
                    _oDataAccess = EstudioDelFutbol.Data.ADONETDataAccess.DataAccess.GetSqlClientWrapper(bizSrv.DataBase.ConnectionString, new CacheHelper(), bizSrv.Log);

                    if (bizSrv.Usuario != null)
                        _oDataAccess.TrackingInfo = bizSrv.Usuario.RemoteEndpoint;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int CopyAndUpdateFields(int SourceID, string LoaiNguoiDung, int? SapXep, string Bak1, string Bak2,
            int? Bak3)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                int iID = 0;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "CopyAndUpdateFieldsLoaiNguoiDung";

                sqlCmd.Parameters.Add("@SourceID", SqlDbType.Int).Value = SourceID;
                sqlCmd.Parameters.Add("@LoaiNguoiDung", SqlDbType.NVarChar, 50).Value = LoaiNguoiDung;

                sqlCmd.Parameters.Add("@SapXep", SqlDbType.Int).Value = SapXep;

                sqlCmd.Parameters.Add("@Bak1", SqlDbType.NVarChar, 50).Value = Bak1;

                sqlCmd.Parameters.Add("@Bak2", SqlDbType.NVarChar, 50).Value = Bak2;

                sqlCmd.Parameters.Add("@Bak3", SqlDbType.Int).Value = Bak3;

                iID = objDataAccess.ExecuteScalar(sqlCmd);
                return iID;
            }
        }

        public int CopyAndUpdateFields(int SourceID, string TenKhuVuc, string GhiChu, int? SapXep, bool? HienThi,
            string Bak1, string Bak2, int? Bak3)
        {
            DataAccess objDataAccess = new DataAccess();
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                int iID = 0;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "CopyAndUpdateFieldsKhuVuc";

                sqlCmd.Parameters.Add("@SourceID", SqlDbType.Int).Value = SourceID;
                sqlCmd.Parameters.Add("@TenKhuVuc", SqlDbType.NVarChar, 50).Value = TenKhuVuc;

                sqlCmd.Parameters.Add("@GhiChu", SqlDbType.NVarChar, 1000).Value = GhiChu;

                sqlCmd.Parameters.Add("@SapXep", SqlDbType.Int).Value = SapXep;

                sqlCmd.Parameters.Add("@HienThi", SqlDbType.Bit).Value = HienThi;

                sqlCmd.Parameters.Add("@Bak1", SqlDbType.NVarChar, 50).Value = Bak1;

                sqlCmd.Parameters.Add("@Bak2", SqlDbType.NVarChar, 50).Value = Bak2;

                sqlCmd.Parameters.Add("@Bak3", SqlDbType.Int).Value = Bak3;

                iID = objDataAccess.ExecuteScalar(sqlCmd);
                return iID;
            }
        }

        /// <summary>
        /// Method that get the correct passthrough action based on the given request. 
        /// </summary>
        /// <param name="resourceExtension">The resource extention that was requested (ie. "/jobs").</param>
        /// <param name="request">The actual http request.</param>
        /// <param name="container">The container that action should be preformed on.</param>
        /// <param name="subscriptionId">The subscription Id that made the request.</param>
        /// <returns></returns>
        public IPassthroughAction GetPassthroughAction(string resourceExtension, HttpRequestMessage request, 
                                                       DataAccess.Context.ClusterContainer container, string subscriptionId)
        {
            Contract.AssertArgNotNull(request,"request");
            Contract.AssertArgNotNull(request.RequestUri,"request.RequestUri");

            switch (resourceExtension.ToLowerInvariant())
            {
                case "jobs":
                    switch (request.Method.ToString().ToLowerInvariant())
                    {
                        case "put":
                            var requestPayload = GetRequestPayload(request);
                            
                            var jobRequest = jobRequestFactory.CreateJobRequest(requestPayload);
                            if (jobRequest == null)
                            {
                                throw new PassthroughActionProcessingException(string.Format(JobSubmissionConstants.InvalidJobRequestLogMessage, requestPayload));
                            }
                            return CreateJobRequestAction(jobRequest, container, subscriptionId);
                        case "get":
                            return CreateJobHistoryAction(request.RequestUri, container, subscriptionId);
                    }
                    break;
            }
            throw new PassthroughActionProcessingException(string.Format(JobSubmissionConstants.PassThroughActionCreationFailedLogMessage, resourceExtension,request.Method));
        }