internal LocalCommand(string commandText, SqlParameterCollection parameters,  int returnParameterIndex, CommandType cmdType) {
     Debug.Assert(0 <= commandText.Length, "no text");
     this.CommandText = commandText;
     this.Parameters = parameters;
     this.ReturnParameterIndex = returnParameterIndex;
     this.CmdType = cmdType;
 }

        public DataSet GenericStatementReturnDataSet(string strStoredProcedure, SqlParameterCollection sqlparCollection)
        {
            DataSet ds = new DataSet();
            try
            {
                OpenConnection();
                sqlcmd = new SqlCommand(strStoredProcedure, sqlcon);
                sqlcmd.CommandType = CommandType.StoredProcedure;

                foreach (SqlParameter param in sqlparCollection)
                {
                    sqlcmd.Parameters.AddWithValue(param.ParameterName, param.SqlValue);
                }

                sqlAdp = new SqlDataAdapter(sqlcmd);
                sqlAdp.Fill(ds);
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                cErrorLog.LogError(ex);
            }
            return ds;
        }

        public override void PersistAddExtraParameters(SqlParameterCollection parameters, Employee employee)
        {
            var emp = employee as EmployeeFullTime;

            if (emp != null)
            {
                parameters.AddWithValue("@AnnualSalary", emp.AnnualSalary);
            }
        }

        /// <summary>
        /// Iterates through a SqlParameterCollection to output the names and values in one line.
        /// </summary>
        /// <param name="sqlParameterCollection"></param>
        /// <returns></returns>
        public static string DumpSqlParameters(SqlParameterCollection sqlParameterCollection)
        {
            var buffer = new StringBuilder();
            foreach (SqlParameter p in sqlParameterCollection)
            {
                buffer.Append('{').Append("param_name=").Append(p.ParameterName).Append(',');
                buffer.Append("param_value=").Append(p.Value).Append('}');
            }

            return buffer.ToString();
        }

 internal static IEnumerable<SqlParameter> CopyParameters( SqlParameterCollection sqlParameterCollection )
 {
     foreach ( SqlParameter sqlParameter in sqlParameterCollection )
     {
         yield return new SqlParameter( sqlParameter.ParameterName, sqlParameter.SqlValue )
         {
             SqlValue = sqlParameter.SqlValue
             //, Direction = sqlParameter.Direction
         };
     }
 }

        public override void PersistAddExtraParameters(SqlParameterCollection parameters, Employee employee)
        {
            var emp = employee as EmployeePartTime;

            if (emp != null)
            {
                parameters.AddWithValue("@HourlyPay", emp.HourlyPay);
                parameters.AddWithValue("@HoursWorked", emp.HoursWorked);
            }

        }

 private static void AddSerializedProperty(ArraySegment<byte> source, SqlParameterCollection parameters, string parameterName)
 {
     int num = (source.Count > 0x1f40) ? source.Count : -1;
     object obj2 = ((num == -1) ? ((bool) GenerateByteArray(source)) : ((bool) source.Array)) ?? DBNull.Value;
     SqlParameter parameter = new SqlParameter {
         ParameterName = parameterName,
         SqlDbType = SqlDbType.VarBinary,
         Size = num,
         Value = obj2
     };
     parameters.Add(parameter);
 }

        public int ExecuteInsert(string sProc, SqlParameterCollection oArrParam)
        {
            int iRecordsAffected = 0;
                if (CheckDBConnStatus() == true)
                {
                    SqlTransaction oTrans;
                    oTrans = oConn.BeginTransaction();

                    try
                    {

                        SqlCommand oCmd = new SqlCommand(sProc, oConn, oTrans);
                        oCmd.CommandType = CommandType.StoredProcedure;

                        foreach (SqlParameter oParam in oArrParam)
                        {
                            oCmd.Parameters.Add(oParam.ParameterName, oParam.SqlDbType).Value = oParam.Value;
                        }

                        iRecordsAffected = oCmd.ExecuteNonQuery();
                        oTrans.Commit();

                        oTrans.Dispose();
                        oTrans = null;
                        oCmd.Dispose();
                        oCmd = null;

                        oConn.Close();
                        return iRecordsAffected;

                    }
                    catch (SqlException sqlerr)
                    {
                        oTrans.Rollback();
                        sErrMessage = "SQL Error: Number - " + sqlerr.Number + ", " + sqlerr.Message;
                        return iRecordsAffected;

                    }

                    catch (Exception err)
                    {
                        oTrans.Rollback();
                        sErrMessage = " Runtime Error: " + err.Message;
                        return iRecordsAffected;
                    }
                }
                else
                {
                    return iRecordsAffected;
                }
        }

 /// <summary>
 /// cập nhật chứng từ
 /// </summary>
 /// <param name="iID_MaCapPhat"></param>
 /// <param name="Params"></param>
 /// <param name="sMaND"></param>
 /// <param name="sIPSua"></param>
 /// <returns></returns>
 public static Boolean CapNhatBanGhi(String iID_MaCapPhat, SqlParameterCollection Params, String sMaND, String sIPSua)
 {
     Bang bang = new Bang("CP_CapPhat");
     bang.MaNguoiDungSua = sMaND;
     bang.IPSua = sIPSua;
     bang.GiaTriKhoa = iID_MaCapPhat;
     bang.DuLieuMoi = false;
     for (int i = 0; i < Params.Count; i++)
     {
         bang.CmdParams.Parameters.AddWithValue(Params[i].ParameterName, Params[i].Value);
     }
     bang.Save();
     return false;
 }

		private static void BuildQueryParameters (IDbCommand cmd,SqlParameterCollection parameterCollection){
		                                         
			if (parameterCollection != null && parameterCollection.Count > 0) {
				foreach (SqlParameter par in  parameterCollection) {
					var cmdPar = cmd.CreateParameter();
					cmdPar.ParameterName = par.ParameterName;
					if (par.DataType != System.Data.DbType.Binary) {
						cmdPar.DbType = par.DataType;
						cmdPar.Value = par.ParameterValue;

					} else {
						cmdPar.DbType = System.Data.DbType.Binary;
					}
					cmdPar.Direction = par.ParameterDirection;
					cmd.Parameters.Add(cmdPar);
				}
			}
		}

        public SqlDataReader ExecuteInsertWithIdentity(string sProc, SqlParameterCollection oArrParam)
        {
            SqlDataReader oRd = null;
                if (CheckDBConnStatus() == true)
                {
                    try
                    {
                        SqlCommand oCmd = new SqlCommand(sProc, oConn);
                        oCmd.CommandType = CommandType.StoredProcedure;

                        foreach (SqlParameter oParam in oArrParam)
                        {
                            oCmd.Parameters.Add(oParam.ParameterName, oParam.SqlDbType).Value = oParam.Value;
                        }

                        if (oConn.State == ConnectionState.Closed)
                        {
                            oConn.ConnectionString = this.FGetConnString();
                            oConn.Open();
                        }

                        oRd = oCmd.ExecuteReader();
                        return oRd;
                    }
                    catch (SqlException sqlerr)
                    {
                        sErrMessage = "SQL Error: Number - " + sqlerr.Number + ", " + sqlerr.Message;
                        return oRd;

                    }

                    catch (Exception err)
                    {
                        sErrMessage = " Runtime Error: " + err.Message;
                        return oRd;
                    }
                }
                else
                {
                    return oRd;
                }
        }

 public void ExecuteNonQuery(string sqlText, SqlParameterCollection spc)
 {
     try
     {
         if (objConn.State != ConnectionState.Open)
         {
             OpenConnection();
         }
         this.cm = new SqlCommand(sqlText, this.objConn);
         foreach (SqlParameter sp in spc)
         {
             this.cm.Parameters.Add(sp);
         }
         cm.ExecuteNonQuery();
     }
     catch (Exception e)
     {
         throw new Exception("Error in ExecuteQuery!!! Source:" + e.Source.ToString() + " Message:" + e.Message.ToString());
     }
 }

        internal void Append(SqlCommand command) {
            ADP.CheckArgumentNull(command, "command");
            Bid.Trace("<sc.SqlCommandSet.Append|API> %d#, command=%d, parameterCount=%d\n", ObjectID, command.ObjectID, command.Parameters.Count);

            string cmdText = command.CommandText;
            if (ADP.IsEmpty(cmdText)) {
                throw ADP.CommandTextRequired(ADP.Append);
            }

            CommandType commandType = command.CommandType;
            switch(commandType) {
            case CommandType.Text:
            case CommandType.StoredProcedure:
                break;
            case CommandType.TableDirect:
                Debug.Assert(false, "command.CommandType");
                throw System.Data.SqlClient.SQL.NotSupportedCommandType(commandType);
            default:
                Debug.Assert(false, "command.CommandType");
                throw ADP.InvalidCommandType(commandType);
            }

            SqlParameterCollection parameters = null;

            SqlParameterCollection collection = command.Parameters;
            if (0 < collection.Count) {
                parameters = new SqlParameterCollection();

                // clone parameters so they aren't destroyed
                for(int i = 0; i < collection.Count; ++i) {
                    SqlParameter p = new SqlParameter();
                    collection[i].CopyTo(p);
                    parameters.Add(p);

                    // SQL Injection awarene
                    if (!SqlIdentifierParser.IsMatch(p.ParameterName)) {
                        throw ADP.BadParameterName(p.ParameterName);
                    }
                }

                foreach(SqlParameter p in parameters) {
                    // deep clone the parameter value if byte[] or char[]
                    object obj = p.Value;
                    byte[] byteValues = (obj as byte[]);
                    if (null != byteValues) {
                        int offset = p.Offset;
                        int size = p.Size;
                        int countOfBytes = byteValues.Length - offset;
                        if ((0 != size) && (size < countOfBytes)) {
                            countOfBytes = size;
                        }
                        byte[] copy = new byte[Math.Max(countOfBytes, 0)];
                        Buffer.BlockCopy(byteValues, offset, copy, 0, copy.Length);
                        p.Offset = 0;
                        p.Value = copy;
                    }
                    else {
                        char[] charValues = (obj as char[]);
                        if (null != charValues) {
                            int offset = p.Offset;
                            int size = p.Size;
                            int countOfChars = charValues.Length - offset;
                            if ((0 != size) && (size < countOfChars)) {
                                countOfChars = size;
                            }
                            char[] copy = new char[Math.Max(countOfChars, 0)];
                            Buffer.BlockCopy(charValues, offset, copy, 0, copy.Length*2);
                            p.Offset = 0;
                            p.Value = copy;
                        }
                        else {
                            ICloneable cloneable = (obj as ICloneable);
                            if (null != cloneable) {
                                p.Value = cloneable.Clone();
                            }
                        }
                    }
                }
            }

            int returnParameterIndex = -1;
            if (null != parameters) {
                for(int i = 0; i < parameters.Count; ++i) {
                    if (ParameterDirection.ReturnValue == parameters[i].Direction) {
                        returnParameterIndex = i;
                        break;
                    }
                }
            }
            LocalCommand cmd = new LocalCommand(cmdText, parameters, returnParameterIndex, command.CommandType);
            CommandList.Add(cmd);
        }

		private static string FormatParameters (SqlParameterCollection sqlParameterCollection) 
		{
			StringBuilder sb = new StringBuilder ();
			foreach (SqlParameter p in sqlParameterCollection) {
				if (sb.Length > 0) {
					sb.Append (", ");
				}
				sb.AppendFormat ("{0}:{1}={2}", p.DbType, p.ParameterName, p.Value);
			}
			return sb.ToString ();
		}

 static void SetParameter(IEdmFunction func, JObject parameterValues, IEdmType edmType, SqlParameterCollection pars)
 {
     if (parameterValues == null)
         return;
     JToken token = null;
     Type colType = null;
     foreach (var p in func.Parameters)
     {
         if (parameterValues.TryGetValue(p.Name, out token))
         {
             if (p.Type.Definition.TypeKind == EdmTypeKind.Complex)
             {
                 DataTable dt = new DataTable();
                 var c = p.Type.AsComplex();
                 foreach (var item in c.StructuralProperties())
                 {
                     dt.Columns.Add(item.Name, item.Type.PrimitiveKind().ToClrType());
                 }
                 foreach (var item in token)
                 {
                     DataRow dr = dt.NewRow();
                     foreach (JProperty col in item)
                     {
                         if (!dt.Columns.Contains(col.Name))
                             continue;
                         colType = dt.Columns[col.Name].DataType;
                         if (colType == typeof(Boolean))
                         {
                             dr.SetField(col.Name, col.Value.ToString() == "0" ? false : true);
                         }
                         else
                             dr.SetField(col.Name, col.Value.ToString().ChangeType(colType));
                     }
                     dt.Rows.Add(dr);
                 }
                 pars.AddWithValue(p.Name, dt);
             }
             else
             {
                 if (string.IsNullOrEmpty(token.ToString()))
                     pars.AddWithValue(p.Name, DBNull.Value);
                 else
                     pars.AddWithValue(p.Name, token.ToString().ChangeType(p.Type.PrimitiveKind()));
             }
         }
     }
     if (edmType.TypeKind == EdmTypeKind.Entity)
     {
         foreach (var outp in (edmType as EdmEntityType).Properties())
         {
             if (outp.Name == "$Results")
                 continue;
             if (pars.Contains(outp.Name))
             {
                 pars[outp.Name].Direction = ParameterDirection.Output;
             }
             else
             {
                 pars.AddWithValue(outp.Name, DBNull.Value);
             }
         }
     }
 }

		void view_Deleting (object sender, SqlDataSourceCommandEventArgs e)
		{
			SqlDataSourceTest.CustomEventParameterCollection = (SqlParameterCollection) e.Command.Parameters;
			SqlDataSourceTest.PassedParameters = FormatParameters (SqlDataSourceTest.CustomEventParameterCollection);
			e.Cancel = true;
		}

		private SqlCommand(string commandText, SqlConnection connection, SqlTransaction transaction, CommandType commandType, UpdateRowSource updatedRowSource, bool designTimeVisible, int commandTimeout, SqlParameterCollection parameters)
		{
			this.commandText = commandText;
			this.connection = connection;
			this.transaction = transaction;
			this.commandType = commandType;
			this.updatedRowSource = updatedRowSource;
			this.designTimeVisible = designTimeVisible;
			this.commandTimeout = commandTimeout;
			this.parameters = new SqlParameterCollection(this);
			for (int i = 0;i < parameters.Count;i++)
				this.parameters.Add(((ICloneable)parameters[i]).Clone());
		}

        public static void ThemThongTinChiTietTaiChinhDoanhNghiepLayTruongTien(DataRow RMucLucChiTieu, DataTable dtDauKy, SqlParameterCollection Params)
        {
            //<--Thêm tham số từ bảng MucLucNganSach
            String strDSTruong = "sKyHieu,sTen,sThuyetMinh";
            String sXauNoiMa = "";
            String[] arrDSTruong = strDSTruong.Split(',');
            if (Params.IndexOf("@iID_MaChiTieu") >= 0)
            {
                Params["@iID_MaChiTieu"].Value = RMucLucChiTieu["iID_MaChiTieu"];
                Params["@iID_MaChiTieu_Cha"].Value = RMucLucChiTieu["iID_MaChiTieu_Cha"];
                Params["@bLaHangCha"].Value = RMucLucChiTieu["bLaHangCha"];
            }
            else
            {
                Params.AddWithValue("@iID_MaChiTieu", RMucLucChiTieu["iID_MaChiTieu"]);
                Params.AddWithValue("@iID_MaChiTieu_Cha", RMucLucChiTieu["iID_MaChiTieu_Cha"]);
                Params.AddWithValue("@bLaHangCha", RMucLucChiTieu["bLaHangCha"]);
            }
            String iID_MaChiTieu = Convert.ToString(RMucLucChiTieu["iID_MaChiTieu"]);
            String iID_MaChiTieu1 = "";
            for (int i = 0; i < dtDauKy.Rows.Count; i++)
            {
                iID_MaChiTieu1 = Convert.ToString(dtDauKy.Rows[i]["iID_MaChiTieu"]);
                if (iID_MaChiTieu.Equals(iID_MaChiTieu1))
                {
                    if (Params.IndexOf("@rSoDauNam") >= 0)
                    {
                        Params["@rSoDauNam"].Value = dtDauKy.Rows[i]["rSoCuoiNam"];
                    }
                    else
                    {
                        Params.AddWithValue("@rSoDauNam", dtDauKy.Rows[i]["rSoCuoiNam"]);
                    }
                    dtDauKy.Rows.RemoveAt(i);
                    break;
                }
            }

            for (int i = 0; i < arrDSTruong.Length; i++)
            {
                if (Params.IndexOf("@" + arrDSTruong[i]) >= 0)
                {
                    Params["@" + arrDSTruong[i]].Value = RMucLucChiTieu[arrDSTruong[i]];
                }
                else
                {
                    Params.AddWithValue("@" + arrDSTruong[i], RMucLucChiTieu[arrDSTruong[i]]);
                }
                if (i < arrDSTruong.Length - 1 && String.IsNullOrEmpty(Convert.ToString(RMucLucChiTieu[arrDSTruong[i]])) == false)
                {
                    if (sXauNoiMa != "") sXauNoiMa += "-";
                    sXauNoiMa += Convert.ToString(RMucLucChiTieu[arrDSTruong[i]]);
                }
            }
            if (Params.IndexOf("@sXauNoiMa") >= 0)
            {
                Params["@sXauNoiMa"].Value = sXauNoiMa;
            }
            else
            {
                Params.AddWithValue("@sXauNoiMa", sXauNoiMa);
            }
        }

        public static DataTable CallQueryProcedure(string ProcudereName, SqlParameterCollection Parms)
        {
            DataTable rows = new DataTable();

            try
            {

                string ConnectionString = connection();
                SqlConnection Conn = new SqlConnection(ConnectionString);

                SqlCommand cmd = new SqlCommand(ProcudereName, Conn);

                foreach (SqlParameter parmrow in Parms)
                {
                    cmd.Parameters.Add(parmrow.ParameterName, parmrow.Value);
                }

                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                Conn.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                rows.Load(rdr);

                //cmd.CommandText =ProcudereName;
                return rows;
            }
            catch (Exception ex)
            {
                return rows;
            }
        }

        /// <summary>
        /// Thêm một hàng dữ liệu vào bảng TN_ChungTu
        /// </summary>
        /// <param name="iID_MaDotPhanBo"></param>
        /// <param name="Params"></param>
        /// <param name="MaND"></param>
        /// <param name="IPSua"></param>
        /// <returns></returns>
        public static String InsertRecord(String iID_MaDotPhanBo, SqlParameterCollection Params, String MaND, String IPSua)
        {
            String MaChungTu = "";
            Bang bang = new Bang("TN_ChungTu");
            DataTable dtDotPhanBo = PhanBo_DotPhanBoModels.GetDotPhanBo(iID_MaDotPhanBo);
            bang.MaNguoiDungSua = MaND;
            bang.IPSua = IPSua;
            for (int i = 0; i < Params.Count; i++)
            {
                bang.CmdParams.Parameters.AddWithValue(Params[i].ParameterName, Params[i].Value);
            }
            bang.CmdParams.Parameters.AddWithValue("@iNamLamViec", dtDotPhanBo.Rows[0]["iNamLamViec"]);
            bang.CmdParams.Parameters.AddWithValue("@iID_MaNguonNganSach", dtDotPhanBo.Rows[0]["iID_MaNguonNganSach"]);
            bang.CmdParams.Parameters.AddWithValue("@iID_MaNamNganSach", dtDotPhanBo.Rows[0]["iID_MaNamNganSach"]);
            bang.CmdParams.Parameters.AddWithValue("@iID_MaTrangThaiDuyet", LuongCongViecModel.Get_iID_MaTrangThaiDuyetMoi(QuyetToanModels.iID_MaPhanHeQuyetToan));
            bang.DuLieuMoi = true;
            String MaChungTuAddNew = Convert.ToString(bang.Save());

            //Thêm chi tiết chỉ tiêu
            //PhanBo_ChungTuChiTietModels.ThemChiTiet(MaChungTuAddNew, MaND, IPSua);

            return MaChungTu;
        }