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
}
}
获取带 in 的sql参数列表
最新推荐文章于 2024-03-08 12:41:38 发布