Tuesday, 13 August 2013

How to use both parameter and function in SQL (C#)?

How to use both parameter and function in SQL (C#)?

I'm new to C# and parameter passing in SQL but i know it's critical to
implement.
The scenario is:
I have a function built in the system already but do not know how to edit
it in order to be able to use parameter passing unto it.
My function to edit via INSERT or UPDATE is:
namespace SQLFunc
{
class SQLClass
{
public void SQLEdit(string var_SQLCommand)
{
using (SqlConnection myConn = new
SqlConnection(this.SQLConnectString))
using (SqlCommand var_command = new SqlCommand(var_SQLCommand,
myConn))
{
myConn.Open();
try
{
var_command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("An error occurred: " + ex.Message + "
using SQL Query: " + var_SQLCommand, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
myConn.Close();
}
}
}
...
my common command to use my function are:
using SQLFunc.SQLClass
....
//command to variable
var_Command = "UPDATE tbl_Table SET fld_Active = 'YES' WHERE fld_IDNo =
" + var_A;
//execute function
var_SQLClass.SQLEdit(var_Command);
using parameter, i want to go for code:
using SQLFunc.SQLClass
....
//command to variable
var_Command = "UPDATE tbl_Table SET fld_Active = 'YES' WHERE fld_IDNo =
@var_A_";
// need to pass this entire line after the SQLCommand in the function
SQLEdit
var_Command.Parameters.AddWithValue("var_A_", var_A );
var_SQLClass.SQLEdit(var_Command);
I wanted to be able to utilize parameter passing in function. I could pass
the variable var_A but i wanted my code to cater for adaptability like it
should work for one field ( update or insert) or even 10 field (update or
insert) without changing the code for function every time the number of
fields changes.
Is this achievable? If so, how?

No comments:

Post a Comment