c# Using Parameters.AddWithValue in SqlDataAdapter
The string used to initialize the SqlDataAdapter becomes the CommandText
of the SelectCommand property of the SqlDataAdapter.
You could add parameters to that command with this code
da = new SqlDataAdapter("SELECT * FROM annotations WHERE annotation LIKE @search",
_mssqlCon.connection);
da.SelectCommand.Parameters.AddWithValue("@search","%" + txtSearch.Text + "%");
- First, remove the single quote around the parameter placeholder.
- Second, add the wildcard character directly in the Value parameter of
AddWithValue
You have asked to use AddWithValue, but remember that, while it is a useful shortcut, there are also numerous drawbacks and all well documented.
- First: Can we stop using AddWithValue() already? where the
author discuss how AddWithValue could give back wrong results in your
queries - Second: How Data Access Code Affects Database Performance where
the author presents evidences of strong performance problems for
AddWithValue
So, the same code without AddWithValue and using the Object and Collection Initializers syntax could be written as
da = new SqlDataAdapter("SELECT * FROM annotations WHERE annotation LIKE @search",
_mssqlCon.connection);
da.SelectCommand.Parameters.Add(new SqlParameter
{
ParameterName = "@search",
Value = "%" + txtSearch.Text + "%",
SqlDbType = SqlDbType.NVarChar,
Size = 2000 // Assuming a 2000 char size of the field annotation (-1 for MAX)
});
and, an even more simplified and one liner version of the above is:
da.SelectCommand.Parameters.Add("@search",SqlDbType.NVarChar,2000).Value = "%" + txtSearch.Text + "%";
How to pass parameters to SqlDataAdapter
here is an example of what you can use and how to pass Parameters
you have to make the changes where necessary
Public Shared Function GetCustomerInfo(stardate As DateTime, enddate As DateTime, Department As String, Active as String, Visits as Int33) As List(Of String)
Dim cszList = New List(Of String)()
Dim DSCityStateZipLookup As New DataSet()
'load the List one time to be used thru out the intire application
Dim ConnString = System.Configuration.ConfigurationManager.ConnectionStrings("CMSConnectionString").ConnectionString
Using connStr As New SqlConnection(ConnString)
Using cmd As New SqlCommand("your Stored Proc name goes here", connStr)
cmd.Parameters.AddWithValue("@stardate", stardate)//make sure you assign a value to startdate
cmd.Parameters.AddWithValue("@enddate", enddate)//make sure you assign a value to enddate
cmd.Parameters.AddWithValue("@Deparment", Deparment)//make sure you assign a value to //Department
cmd.Parameters.AddWithValue("@Active", Active)//make sure you assign a value to Active
cmd.Parameters.AddWithValue("@Visits", Visits)//make sure you assign a value to Visits
cmd.Connection.Open()
New SqlDataAdapter(cmd).Fill(DSCityStateZipLookup)
'If we get a record back from the above stored procedure call, that in itself means the information the user provided from
'the UI is in the database. On the other hand, if we do not get a record back from the stored procedure call, we should
'simply advise the user that the information they provided does not exist in the database, and to double check their spelling.
If DSCityStateZipLookup.Tables.Count = 0 OrElse (DSCityStateZipLookup.Tables.Count > 0 AndAlso DSCityStateZipLookup.Tables(0).Rows.Count = 0) Then
cszList.Add("Your Error Message goes here if any.")
End If
End Using
End Using
Return cszList
End Function
DataAdapter Sql Query with parameters - c#
Change it as follows so as to not parameterize the column name:
public int SearchCar(MainStore searchCars)
{
string connection = @"Data Source=(LocalDB)";
SqlConnection con = new SqlConnection(connection);
string sql = string.Format("SELECT car, model, year FROM store WHERE {0} like @search", search.GetCombo());
SqlDataAdapter sda = new SqlDataAdapter(sql, con);
// sdt.SelectCommand.Parameters.AddWithValue("@column", "%" + search.GetCombo());
sdt.SelectCommand.Parameters.AddWithValue("@search", "%" + search.GetSearch());
DataTable dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = data;
}
Also, you've got an extra quote at the end of your query:like @search '";
Rework using parameterized queries C#
You can use Parameters.AddWithValue
in SqlDataAdapter
sda.SelectCommand.Parameters.AddWithValue("@ParamName",value);
Check this: c# Using Parameters.AddWithValue in SqlDataAdapter
C# | SQL | Change string concatenation to parameters
How
See SqlCommand.Parameters
for more information on how to do parameter binding. For example in your case you would do
string commandText = "Insert into Usermst values (@name, @city, @email)";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@name", SqlDbType.Varchar);
command.Parameters["@name"].Value = name;
Since you are using SqlDataAdapter
SqlAdp.Parameters.Add("@name",
SqlDbType.NVarChar, "Josse", "Name");
Why
- To Prevent SQL Injection
- Parameter binding also improves performance since DB engine can use and execute the cached query instead parsing the query again which would happen if you use string concatenation.
Related Topics
How to Make Multi-Language App in Winforms
How to Update Textbox in Form1 from Form2
Purpose of Activator.Createinstance with Example
How to Create a Wpf Usercontrol with Named Content
How to Implement Real Time Data for a Web Page
Searching for File in Directories Recursively
Why Switch for Enum Accepts Implicit Conversion to 0 But No for Any Other Integer
Foreach VS Somelist.Foreach(){}
How to Get Command Line Arguments of Other Processes from .Net/C#
Nullable Type Is Not a Nullable Type
Order of Items in Classes: Fields, Properties, Constructors, Methods
C# Short/Long/Int Literal Format
JSON Convert Empty String Instead of Null
Why Use Simple Properties Instead of Fields in C#