C# + SQL Server常用操作

 C#连接SQL Server数据库常用操作：增、删、改、查

public static class SqlHelper
{
	private static readonly string conStr = "Data Source=.;Initial Catalog=.;Integrated Security=true";

	/// <summary>
	/// 执行增、删、改，并返回所影响的行数
	/// </summary>
	/// <param name="sql"></param>
	/// <param name="type"></param>
	/// <param name="pms"></param>
	/// <returns></returns>
	public static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] pms)
	{
		using (SqlConnection con = new SqlConnection(conStr))
		{
			using (SqlCommand cmd = new SqlCommand(sql, con))
			{
				cmd.CommandType = type;
				if (pms != null)
				{
					cmd.Parameters.AddRange(pms);
				}
				con.Open();
				int count = cmd.ExecuteNonQuery();
				cmd.Parameters.Clear();
				return count;
			}
		}
	}

	/// <summary>
	/// 执行返回单个值的方法
	/// </summary>
	/// <param name="sql"></param>
	/// <param name="type"></param>
	/// <param name="pms"></param>
	/// <returns></returns>
	public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] pms)
	{
		using (SqlConnection con = new SqlConnection(conStr))
		{
			using (SqlCommand cmd = new SqlCommand(sql, con))
			{
				cmd.CommandType = type;
				if (pms != null)
				{
					cmd.Parameters.AddRange(pms);
				}
				con.Open();
				object obj = cmd.ExecuteScalar();
				cmd.Parameters.Clear();
				return obj;
			}
		}
	}

	/// <summary>
	/// 返回SqlDataReader对象的方法
	/// </summary>
	/// <param name="sql"></param>
	/// <param name="type"></param>
	/// <param name="pms"></param>
	/// <returns></returns>
	public static SqlDataReader ExecuteReader(string sql, CommandType type, params SqlParameter[] pms)
	{
		//SqlDataReader要求使用的时候必须保证连接保持打开
		SqlConnection con = new SqlConnection(conStr);
		using (SqlCommand cmd = new SqlCommand(sql, con))
		{
			cmd.CommandType = type;
			if (pms != null)
			{
				cmd.Parameters.AddRange(pms);
			}
			try
			{
				con.Open();
				//CommandBehavior.CloseConnection执行该命令时，如果关闭关联的DataReader对象，则关联的Connection对象也将关闭
				SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				cmd.Parameters.Clear();
				return reader;
			}
			catch (Exception)
			{
				//发生异常时关闭连接
				con.Close();
				con.Dispose();
				throw;
			}
		}
	}

	/// <summary>
	/// 返回DataTable的方法
	/// </summary>
	/// <param name="sql"></param>
	/// <param name="type"></param>
	/// <param name="pms"></param>
	/// <returns></returns>
	public static DataTable ExecuteDataTable(string sql, CommandType type, params SqlParameter[] pms)
	{
		DataTable dt = new DataTable();
		using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
		{
			adapter.SelectCommand.CommandType = type;
			if (pms != null)
			{
				adapter.SelectCommand.Parameters.AddRange(pms);
			}
			adapter.Fill(dt);
			adapter.SelectCommand.Parameters.Clear();
		}
		return dt;
	}
}

 

转载于:https://my.oschina.net/huanglii/blog/880881