学步园

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using Common;

namespace ConsoleApplicationTest
{
    class Program_8
    {
        static void Main(string[] args)
        {
            string sqlQuery   = "SELECT * FROM [user] WHERE id IN ( {0} )";
            string ids = "1,2,3";
            SqlParameter[] spArr = DBHelper.GetWithInSqlParameters(ref sqlQuery, ids);
            DataTable dt = DBHelper.ExecuteDataTable(sqlQuery, spArr);
            Console.WriteLine("得到DataTable的行数：{0}", dt==null?0:dt.Rows.Count);
            Console.Read();
        }
    }

    public static class DBHelper
    {
        #region [ 简单的增删改查， 自己可另扩充 ]
        public static string ConnectionString = "Data Source=leaf-home\\sqlserver2005;Initial Catalog=Test;Persist Security Info=True;User ID=??;Password=??";

        public static SqlConnection GetConnection()
        {
            return new SqlConnection(ConnectionString);
        }

        /// <summary>
        /// 执行cmd得到 DataTable. by ngye, on 2013-08-01
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        private static DataTable ExecuteDataTable(this SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                adapter.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// 根据sql语句和参数，返回DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = GetConnection())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                cmd.Parameters.AddRange(spArr);
                DataTable dt = cmd.ExecuteDataTable();
                return dt;
            }
        }

        ///// <summary>
        /// 根据sql语句和参数，返回受影响行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="spArr">可变参数</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] spArr)
        {
            using (SqlConnection conn = GetConnection())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
                cmd.Parameters.AddRange(spArr);
                return cmd.ExecuteNonQuery();
            }
        }
        #endregion

        #region [ 带 in 不确定参数的执行方法 ]
        /// <summary>
        /// 获取带 in 的sql参数列表
        /// </summary>
        /// <param name="sql">带in ( {0} )的sql</param>
        /// <param name="ids">以逗号分隔的id字符串</param>
        /// <returns>sql参数列表</returns>
        public static SqlParameter[] GetWithInSqlParameters(ref string sql, string ids)
        {
            if (string.IsNullOrEmpty(ids)) 
            {
                return null;
            }

            string[] idArr = ids.Split(',');
            //组建sql在in中的字符串
            StringBuilder sbCondition = new StringBuilder();
            List<SqlParameter> spList = new List<SqlParameter>();
            for (int i = 0; i < idArr.Length; i++)
            {
                string id = idArr[i];
                sbCondition.Append("@id" + i + ",");
                spList.Add(new SqlParameter("@id" + i.ToString(), id)); 
            }
            //重新构建sql
            sql = string.Format(sql, sbCondition.ToString().TrimEnd(','));
            return spList.ToArray();
        }
        #endregion
    }
}