UPDATE {0} SET DisplayOrder=(SELECT COUNT(1) FROM {0} b WHERE b.DisplayOrder<a.DisplayOrder AND {1})

 

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace Chengsoft.DBUtility
{
    /// <summary>
    /// 数据库访问组件基础类
    /// </summary>
    public class SqlHelper
    {
        //连接字符串
        public static string connectionString =ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        /// <summary>执行带参数的SQL语句
        /// 执行带参数的SQL语句
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>执行查询语句，返回SqlDataReader对象
        /// 执行查询语句，返回SqlDataReader对象
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string connectionString, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlDataReader rdr;
            try
            {
                PrepareCommand(cmd, conn, null, cmdText, commandParameters);
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

     
        /// <summary>执行查询语句，返回受影响的行数
        /// 执行查询语句，返回受影响的行数
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string connectionString, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>初始化SqlCommand对象
        /// 初始化SqlCommand对象
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = CommandType.Text;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        /// <summary>更新列表顺序
        /// 更新列表顺序
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="ID">表ID</param>
        /// <param name="Offset">排序列</param>
        /// <param name="companyID">公司ID</param>
        public static void UpdateDisplayOrder(string tbName, int ID, int Offset, int companyID)
        {
            string strSQL = null;
            strSQL = "UPDATE {0} SET DisplayOrder=DisplayOrder+{2} WHERE ID={1} AND CompanyID={3}";
            SqlHelper.ExecuteNonQuery(SqlHelper.connectionString, string.Format(strSQL, tbName, ID, Offset, companyID), null);
        }

        /// <summary>重新排序
        /// 字段DisplayOrder按照1,3,5,7,9..的顺序重新排序,Where是限制条件,条件：表中一定要有DisplayOrder
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="SQL_WHERE1">条件一</param>
        /// <param name="SQL_WHERE2">条件二</param>
        /// <param name="companyID">公司ID</param>
        public static void RebuildOrder(string tbName, string SQL_WHERE1, string SQL_WHERE2, int companyID)
        {
            string strSQL = null;
            //sql2000可以使用,{0}是表名，{1}是WHREE语句
            strSQL = "UPDATE {0} SET DisplayOrder=(SELECT COUNT(1) FROM {0} b WHERE b.DisplayOrder<a.DisplayOrder AND {1})*2+1 FROM {0} a WHERE {2} AND CompanyID={3}";

            //SQL 2005可以使用
            //Private SQL_REBUILD_ORDER As String = "with CTE as (select rw=((Row_Number() OVER (ORDER BY DisplayOrder)) *2-1),* from tb_ProductSort) " & _
            //                                    "update CTE set DisplayOrder=rw"
            SqlHelper.ExecuteNonQuery(SqlHelper.connectionString, string.Format(strSQL, tbName, SQL_WHERE1, SQL_WHERE2, companyID), null);
        }
    }
}

sql  update{0} 的用法，在哪里能查到