动态SQL拼接"/>
SQL Server 动态SQL拼接
在多选项综合搜索数据时,大多会使用到动态SQL搜索,当搜索栏目中存在string,Guid,decimal等类型数据时,要注意拼接时数据类型转换,方法如下:
方法一:使用 SQL Server中的存储过程(StoredProcedure),在存储过程中拼接SQL,SQL拼接的语句为字符串,当我们去拼接Guid等特殊类型时就需要去转换成字符串,如果直接拼接,SQL会直接报错。
具体如下:
ALTER PROCEDURE [dbo].[GetProductList]
(@ProductID uniqueidentifier,@Price decimal(18, 2),@Description varchar(max)
)
AS
BEGINDeclare @strSql varchar(max);--SQL拼接SET @strSql='SELECT ProductID,Name,Price,Description FROM [test].[dbo].[Product] WHERE 1=1'IF(@ProductID is not null)begin SET @strSql=@strSql+' AND ProductID= ''' +convert(varchar(36),@ProductID)+ '''' --Guid类型转换end IF(@Description!='')begin SET @strSql=@strSql+' AND Description like ''%'+@Description+'%'''end EXEC(@strSql)
END
方法二:直接在后台代码中,拼接SQL后,去执行SQL
具体如下:
public static void SQLFunction(Product product) {//SQL拼接string sql = "SELECT ProductID,Name,Price,Description FROM [test].[dbo].[Product] WHERE 1=1";if (product.ProductID!=null) {sql = sql + " AND ProductID='"+ product.ProductID + "'";}if (product.Description != "") {sql = sql + " AND Description like '%" + product.Description + "%'";}using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))using (SqlCommand cmd = new SqlCommand()){cmd.CommandType = CommandType.Text; cmd.CommandText = sql;cmd.Connection = conn;conn.Open();SqlDataReader reader = cmd.ExecuteReader();while (reader.Read()){Product pro = new Product(); pro.Description = reader["Description"].ToString();//...}}}
更多推荐
SQL Server 动态SQL拼接
发布评论