参数化SQL语句，分页SQL语句SQLHELP

来源：互联网发布：微信编辑器php源代码编辑：程序博客网时间：2024/04/28 10:01

string strSql="insert into table() values(@param1,@param2)"

SqlParameter[] parameters=new SqlParameter[]{

new SqlParameter("@param1",param1),

new SqlParameter("@param2",param2)

如果出错，@可以去掉

}

分页SQL语句：

string sql = @" select * from( select row_number() over(order by {0}) as num,* from books {1}) as t where num>=@start and num<=@end";

注： * 用具体字段

{0} 排序方式

{1} 查询条件

SQLHELP：

public static SqlDataReader ExecuteReader(string strConn, string strSql,CommandType type, params SqlParameter[] pms)
        {
            SqlDataReader reader;
            SqlConnection conn = new SqlConnection(strConn);
            using (SqlCommand cmd = new SqlCommand(strSql, conn))
            {
                if (pms != null) {
                    cmd.Parameters.AddRange(pms);
                }
                try
                {
                    conn.Open();
                    cmd.CommandType = type;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (Exception)
                {
                    conn.Dispose();
                    throw;
                }
                finally {

                }
            }
            return reader;
        }

public static int ExecuteNonQuery(string strConn,string strSql,CommandType type, params SqlParameter[] pms) {

            using (SqlConnection conn = new SqlConnection(strConn)) {
                using (SqlCommand cmd = new SqlCommand(strSql, conn)) {
                    if (pms != null) {
                        cmd.Parameters.AddRange(pms);
                    }
                    cmd.CommandType = type;
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }

}

public static object ExecuteScalar(string strConn, string strSql, CommandType type, params SqlParameter[] pms)
{

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = new SqlCommand(strSql, conn))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    cmd.CommandType = type;
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }

}