SQL Server tutorial for beginners - Implement search web page using ASP NET and Dynamic SQL

Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/implement-search-web-page-using-aspnet.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2017/04/implement-search-web-page-using-aspnet_3.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss implementing a search web page using ASP.NET and Dynamic SQL. This is continuation to Part 139. Please watch Part 139 from SQL Server Tutorial before proceeding. Step 1 : Add a WebForm to the web project. Name it "SearchPageWithDynamicSQL.aspx" Step 2 : Copy and paste the HTML on the ASPX page using the link below. http://csharp-video-tutorials.blogspot.com/2017/04/implement-search-web-page-using-aspnet.html Step 3 : Copy and paste the following code in the code-behind page. Notice we are using dynamic sql instead of the stored procedure "spSearchEmployees". using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Text; namespace DynamicSQLDemo { public partial class SearchPageWithDynamicSQL : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) {} protected void btnSearch_Click(object sender, EventArgs e) { string strConnection = ConfigurationManager .ConnectionStrings["connectionStr"].ConnectionString; using (SqlConnection con = new SqlConnection(strConnection)) { SqlCommand cmd = new SqlCommand(); cmd.Connection = con; StringBuilder sbCommand = new StringBuilder("Select * from Employees where 1 = 1"); if (inputFirstname.Value.Trim() != "") { sbCommand.Append(" AND FirstName=@FirstName"); SqlParameter param = new SqlParameter("@FirstName", inputFirstname.Value); cmd.Parameters.Add(param); } if (inputLastname.Value.Trim() != "") { sbCommand.Append(" AND LastName=@LastName"); SqlParameter param = new SqlParameter("@LastName", inputLastname.Value); cmd.Parameters.Add(param); } if (inputGender.Value.Trim() != "") { sbCommand.Append(" AND Gender=@Gender"); SqlParameter param = new SqlParameter("@Gender", inputGender.Value); cmd.Parameters.Add(param); } if (inputSalary.Value.Trim() != "") { sbCommand.Append(" AND Salary=@Salary"); SqlParameter param = new SqlParameter("@Salary", inputSalary.Value); cmd.Parameters.Add(param); } cmd.CommandText = sbCommand.ToString(); cmd.CommandType = CommandType.Text; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); gvSearchResults.DataSource = rdr; gvSearchResults.DataBind(); } } } } At this point, run the application and SQL profiler. To run SQL profiler 1. Open SQL Server Management Studio 2. Click on "Tools" and select "SQL Server Profiler" 3. Click the "Connect" button to connect to local SQl Server instance 4. Leave the "Defaults" on "Trace Properties" window and click on "Run" button 5. We now have the SQL Profiler running and in action On the "Search Page" set "Gender" filter to Male and click the "Search" button. Notice we get all the Male employees as expected. Also in the SQL Server profiler you can see the Dynamic SQL statement is executed using system stored procedure sp_executesql. In our next video, we will discuss the differences between using Dynamic SQL and Stored Procedures