public AddressInfo GetAddressByID()
        {
            SqlHelper objSqlHelper = new SqlHelper();
            SqlParameter[] objParams = new SqlParameter[1];
            objParams[0] = new SqlParameter("@userName", SqlDbType.VarChar);

            objParams[0].Value = GetUserName();
            SqlDataReader reader = objSqlHelper.ExecuteReader("je_Ad_GetAddressByID", objParams);
            AddressInfo item = new AddressInfo();
            while (reader.Read())
            {
                item.UserName = reader.GetString(reader.GetOrdinal("userName"));
                item.AddressName = reader.GetString(reader.GetOrdinal("addressName"));
                item.Address = reader.GetString(reader.GetOrdinal("address"));
                item.Post = reader.GetString(reader.GetOrdinal("Post"));
                item.Telephone = reader.GetString(reader.GetOrdinal("Telephone"));
                item.Province = reader.GetString(reader.GetOrdinal("Province"));
                item.City = reader.GetString(reader.GetOrdinal("City"));
                item.SendType = reader.GetInt32(reader.GetOrdinal("SendType"));
                item.PayType = reader.GetInt32(reader.GetOrdinal("PayType"));
                item.Name = reader.GetString(reader.GetOrdinal("Name"));
                item.Price = reader.GetDecimal(reader.GetOrdinal("Price"));
                item.Balance = reader.GetDecimal(reader.GetOrdinal("Balance"));
            }
            reader.Close();
            return item;
        }

        /// <summary>
        /// ������Ϣ��ID ��ѯ������Ϣ
        /// </summary>
        /// <param name="itemID"></param>
        /// <returns></returns>
        public List<BuilderObject> GetBuilderInfo(string ObjectID)
        {
            SqlHelper sh = new SqlHelper();
            ds = new DataSet();

            ds = sh.selectAll("builderObject", "buildObjectID='" + ObjectID + "'");
            list = new List<BuilderObject>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                BuilderObject boTemp = new BuilderObject();
                boTemp.BuilId           = int.Parse(ds.Tables[0].Rows[i][0].ToString());
                boTemp.BuilName         = ds.Tables[0].Rows[i][1].ToString();
                //������ǵ�i�еĵ�1�е�����
               // boTemp.BuilItemID       = ds.Tables[0].Rows[i][2].ToString();
                boTemp.BuilHeight       = ds.Tables[0].Rows[i][2].ToString();
                boTemp.BuilArea         = ds.Tables[0].Rows[i][3].ToString();
                boTemp.BuilInfo         = ds.Tables[0].Rows[i][4].ToString();
                boTemp.BuilLongitude    = ds.Tables[0].Rows[i][5].ToString();
                boTemp.BuilLatitude     = ds.Tables[0].Rows[i][6].ToString();
                boTemp.BuidObjectID = ds.Tables[0].Rows[i][7].ToString();
                //Tables��Rows���Ǽ��ϣ��������ǿ���ͨ���������������еij�Ա
                list.Add(boTemp);
            }

            return list;
        }

        /// <summary>
        /// Создает новую задачу.
        /// </summary>
        /// <param name="task">Задача.</param>
        /// <returns>Идентификатор созданной задачи.</returns>
        public int Create(TaskItem task)
        {
            using (var sqlh = new SqlHelper())
            {
                return sqlh.ExecScalar<int>(@"
            insert into Task.task
            (
            number,
            task_prefix,
            header,
            mem,
            deadline,
            priority,
            performer,
            author
            )
            values
            (
            @Number,
            (select top 1 p.task_prefix from Task.task_prefix p where p.code = @Prefix),
            @Header,
            @Mem,
            @Deadline,
            @Priority,
            @Performer,
            @Author
            )

            select scope_identity()", task);
            }
        }

 public int DeleteTitleCss(int titleCssID)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[1];
     objParams[0] = new SqlParameter("@cssID", titleCssID);
     return objSqlHelper.ExecuteNonQuery("je_Tc_DeleteTitleCss", objParams);
 }

       public IList<ReviewInfo> GetReview(ReviewType rt, int ID)
       {
           SqlHelper objSqlHelper = new SqlHelper();
           List<ReviewInfo> reviews = new List<ReviewInfo>();
           SqlParameter[] objParams = new SqlParameter[2];
           objParams[0] = new SqlParameter("@reviewType", SqlDbType.Int, 4);
           objParams[1] = new SqlParameter("@ID", SqlDbType.Int, 4);
           objParams[0].Value = (int)rt;
           objParams[1].Value = ID;
           SqlDataReader reader = objSqlHelper.ExecuteReader("je_Reviews_GetReview", objParams);
           while (reader.Read())
           {
               ReviewInfo item = new ReviewInfo();
               item.ReviewID = reader.GetInt32(reader.GetOrdinal("ReviewID"));
               item.BookID = reader.GetInt32(reader.GetOrdinal("bookID"));
               item.PostIP = reader.GetString(reader.GetOrdinal("postIP"));
               item.PostTime = reader.GetDateTime(reader.GetOrdinal("postTime"));
               item.RateID = reader.GetInt32(reader.GetOrdinal("rate"));
               item.UserName = reader.GetString(reader.GetOrdinal("userName"));
               item.StatusID = reader.GetInt32(reader.GetOrdinal("status"));
               item.Content = reader.GetString(reader.GetOrdinal("content"));
               reviews.Add(item);
           }
           reader.Close();

           return reviews;
       }

        public static SqlCommand CreateCommand(string sql, CommandType type, List<SqlParameter> param)
        {
            //connectionstring();
            //SqlConnection con = new SqlConnection(strConnection);
            //con.Open();
            SqlHelper SH = new SqlHelper();
            SH.openconnection();

            SqlCommand cmd = new SqlCommand(sql,SH.conn);
            cmd.CommandType = CommandType.StoredProcedure;

            if (param.Count > 0)
            {
                foreach (SqlParameter p in param)
                {
                    if (p != null)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
            }

            //sqlhelp.closeconnection();
            return cmd;
            SH.closeconnection();
        }

        /// <summary>
        /// Создает новый учебный план.
        /// </summary>
        /// <param name="plan">Учебный план.</param>
        /// <returns>Идентификатор созданного учебного плана.</returns>
        public int Create(PlanItem plan)
        {
            using (var sqlh = new SqlHelper())
            {
                return sqlh.ExecScalar<int>(@"
            insert into TrainingPlan.specialty_plan
            (
            name,
            specialty_detail,
            semester,
            lesson_type,
            discipline,
            auditory
            )
            values
            (
            @Name,
            @SpecialtyDetail,
            @Semester,
            @LessonType,
            @Discipline,
            @Auditory
            )

            select scope_identity()", plan);
            }
        }

        public string GetImpersonatedLoginFor(string login)
        {
            if (!_settings.IsImpersonationEnabled)
            {
                return null;
            }

            var sql = new SqlHelper("SELECT p.login_name, p.status FROM table_user u, table_user p WHERE u.login_name = {0} AND p.objid = u.user2proxy_user");
            sql.Parameters.Add("login", login);
            using (var result = sql.ExecuteReader())
            {
                while (result.Read())
                {
                    var status = Convert.ToInt32(result["status"]);
                    var impersonatedLoginFor = result["login_name"].ToString();

                    if (status == 1)
                    {
                        return impersonatedLoginFor;
                    }
                    else
                    {
                        _logger.LogDebug(
                            "Cancelling the impersonation of INACTIVE user {0} by user {1}.".ToFormat(impersonatedLoginFor,
                                login));
                        cancelImpersonation(login, impersonatedLoginFor);
                        return null;
                    }
                }

                return null;
            }
        }

 public static void setAllowProxy(string userLogin, bool canProxy)
 {
     var sql = new SqlHelper("update table_employee set allow_proxy = {0} where objid = (select objid from table_user where login_name = {1})");
     sql.Parameters.Add("canProxy", canProxy ? 1 : 0);
     sql.Parameters.Add("login", userLogin);
     sql.ExecuteNonQuery();
 }

    protected void InitData()
    {
        SqlHelper mydata = new SqlHelper();

        GridView1.DataSource = mydata.GetDataReader("select * from Musictype");
        GridView1.DataBind();
    }

 public static void createImpersonationFor(string impersonatingUserLogin, string userLoginBeingImpersonated)
 {
     var sql = new SqlHelper("update table_user set user2proxy_user = (select objid from table_user where login_name = {0}) where login_name = {1}");
     sql.Parameters.Add("login", userLoginBeingImpersonated);
     sql.Parameters.Add("proxylogin", impersonatingUserLogin);
     sql.ExecuteNonQuery();
 }

        /// <summary>
        /// Создает новую аудиторию.
        /// </summary>
        /// <param name="auditory">Аудитория.</param>
        /// <returns>Идентификатор созданной аудитории.</returns>
        public int Create(AuditoryItem auditory)
        {
            using (var sqlh = new SqlHelper())
            {
                return sqlh.ExecScalar<int>(@"
            insert into Auditory.auditory
            (
            number,
            full_name,
            memo,
            level,
            capacity
            )
            values
            (
            @Number,
            @FullName,
            @Memo,
            @Level,
            @Capacity
            )

            select scope_identity()", auditory);
            }
        }

        public IList<FriendLinkInfo> GetLinks(LinkItemType linkType)
        {
            SqlHelper objSqlHelper = new SqlHelper();
            List<FriendLinkInfo> friendlinks = new List<FriendLinkInfo>();
            SqlParameter[] objParams = new SqlParameter[1];
            objParams[0] = new SqlParameter("@linkType", SqlDbType.Int, 4);
            objParams[0].Value = (int)linkType;
            SqlDataReader reader = objSqlHelper.ExecuteReader("je_Link_GetLinks", objParams); ;

            objParams[0].Value = (int)linkType;

            while (reader.Read())
            {
                FriendLinkInfo item = new FriendLinkInfo();
                item.LinkID = reader.GetInt32(reader.GetOrdinal("linkID"));
                item.LinkName = reader.GetString(reader.GetOrdinal("linkName"));
                item.LinkURL = reader.GetString(reader.GetOrdinal("linkURL"));
                item.LinkLogo = reader.GetString(reader.GetOrdinal("linkLogo"));
                item.LinkOrder = reader.GetInt32(reader.GetOrdinal("linkOrder"));
                item.IsMain = reader.GetBoolean(reader.GetOrdinal("linkMain"));
                item.IsArrow = reader.GetBoolean(reader.GetOrdinal("linkArrow"));
                friendlinks.Add(item);
            }
            reader.Close();
            return friendlinks;
        }

 public void Import()
 {
     var mySqlHelper = new MySqlHelper(MySql);
     var sqlHelper = new SqlHelper(SqlServer);
     var mysql = string.Format("select * from qdt_deal");
     var dt = mySqlHelper.ExecuteDataTable(mysql);
     var sql = "";
     var n = 0;
     for (var i = 0; i < dt.Rows.Count; i++)
     {
         sql += "INSERT INTO qdt_deal (id,name,sub_name,cate_id,user_id,description,is_effect,is_delete,borrow_amount,min_loan_money," +
                "repay_time,rate,create_time,update_time,is_recommend,buy_count,load_money,repay_money,start_time,success_time," +
                "repay_start_time,last_repay_time,next_repay_time,bad_time,deal_status,enddate,services_fee,is_send_bad_msg,bad_msg,loantype," +
                "warrant,titlecolor,is_send_contract,max_loan_money,icon,repay_time_type) " +
                "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}');\r\n"
                .FormatWith(dt.Rows[i]["id"], dt.Rows[i]["name"], dt.Rows[i]["sub_name"], dt.Rows[i]["cate_id"], dt.Rows[i]["user_id"], dt.Rows[i]["description"].ToString().Replace('\'', '\"').Replace("./help/id-", "/Help/Index/").Replace("?", ""), dt.Rows[i]["is_effect"], dt.Rows[i]["is_delete"], dt.Rows[i]["borrow_amount"], dt.Rows[i]["min_loan_money"],
                dt.Rows[i]["repay_time"], dt.Rows[i]["rate"], GetTime(dt.Rows[i]["create_time"]), GetTime(dt.Rows[i]["update_time"]), dt.Rows[i]["is_recommend"], dt.Rows[i]["buy_count"], dt.Rows[i]["load_money"], dt.Rows[i]["repay_money"], GetTime(dt.Rows[i]["start_time"]), GetTime(dt.Rows[i]["success_time"]),
                GetTime(dt.Rows[i]["repay_start_time"]), GetTime(dt.Rows[i]["last_repay_time"]), GetTime(dt.Rows[i]["next_repay_time"]), GetTime(dt.Rows[i]["bad_time"]), dt.Rows[i]["deal_status"], dt.Rows[i]["enddate"], dt.Rows[i]["services_fee"], dt.Rows[i]["is_send_bad_msg"], dt.Rows[i]["bad_msg"], dt.Rows[i]["loantype"],
                dt.Rows[i]["warrant"], dt.Rows[i]["titlecolor"], dt.Rows[i]["is_send_contract"], dt.Rows[i]["max_loan_money"], dt.Rows[i]["icon"], dt.Rows[i]["repay_time_type"]).Replace("'NULL'","NULL");
         if ((i + 1) % 10 != 0) continue;
         n += sqlHelper.ExecuteNonQuery(sql);
         sql = "";
     }
     if (!string.IsNullOrWhiteSpace(sql))
     n += sqlHelper.ExecuteNonQuery(sql);
     Console.WriteLine("成功导入deal表：" + n + "条数据！");
 }

 public int DeleteHelp(int helpID)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[1];
     objParams[0] = new SqlParameter("@helpID", helpID);
   return objSqlHelper.ExecuteNonQuery("je_Help_DeleteHelp", objParams);
 }

 /// <summary>
 /// Получает задачу по идентификатору.
 /// </summary>
 /// <param name="id">Идентификатор.</param>
 /// <returns>Задача.</returns>
 public TaskItem Get(int id)
 {
     using (var sqlh = new SqlHelper())
     {
         return sqlh.ExecMapping<TaskItem>(@"
     select
     t.task Id,
     t.number Number,
     p.code Prefix,
     t.header Header,
     t.mem Mem,
     t.created Created,
     t.deadline Deadline,
     t.priority Priority,
     t.performer Performer,
     t.author Author,
     t.is_perform IsPerform,
     t.is_open IsOpen,
     t.difficult Difficult,
     t.pull_request_url PullRequestUrl
     from Task.task t
     join Task.task_prefix p on p.task_prefix = t.task_prefix
     where t.task = @id", new { id });
     }
 }

 public int DeleteSend(int SendID)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[1];
     objParams[0] = new SqlParameter("@sendID", SendID);
     return objSqlHelper.ExecuteNonQuery("je_Send_DeleteSend", objParams);
 }

 public IList<ValoresFuente> Valores(Ciudad city, int limite, dynamic options = null)
 {
     var top = limite > 0 ? "top " + limite : "";
     var sql = "select " + top + " * from sta_elena order by codigo_catastral";
     var str = ConfigurationManager.ConnectionStrings["riesgos"].ConnectionString;
     var conn = new SqlConnection(str);
     conn.Open();
     var reader = new SqlHelper(conn).ExecuteReader(sql);
     var mapa = new Dictionary<string, ValoresFuente>();
     var i = 0;
     while (reader.Read()) {
         i++;
         var temp = new ValoresFuente();
         Utils.FillDict(reader, temp);
         var clave = temp.GetString("codigo_catastral");
         if (mapa.ContainsKey(clave))
             continue;
         var manzana = GetManzana(clave);
         temp.Tipo = "construccion";
         temp.Codigo1 = clave;
         temp.Codigo2 = manzana;
         temp.IdItem = i;
         temp["clave"] = clave;
         temp["manzana"] = manzana;
         mapa[clave] = temp;
     }
     reader.Close();
     conn.Close();
     return mapa.Values.ToList();
 }

        public static string FindMaximumValue(int OperationValue)
        {
            string strMaxValue="";
            try
            {
                SqlHelper SH = new SqlHelper();
                SH.openconnection();

                SqlCommand cmdAuthors = new SqlCommand("sp_find_maximum", SH.conn);
                cmdAuthors.CommandType = CommandType.StoredProcedure;
                cmdAuthors.Parameters.Clear();

                cmdAuthors.Parameters.Add("@OutPutValue", SqlDbType.Int);
                cmdAuthors.Parameters["@OutPutValue"].Direction = ParameterDirection.Output;

                cmdAuthors.Parameters.Add("@Operation", SqlDbType.Int);
                cmdAuthors.Parameters["@Operation"].Value = OperationValue;

                cmdAuthors.ExecuteNonQuery();

                strMaxValue = cmdAuthors.Parameters["@OutPutValue"].Value.ToString();

                SH.closeconnection();
            }
            catch (Exception)
            {
                throw new System.NotImplementedException();
            }

            return strMaxValue;
        }

        public static SqlHelper Instance()
        {
            if (SqlHelper._instance == null)
                SqlHelper._instance = new SqlHelper();

            return SqlHelper._instance;
        }