Using a parametrized sql wrapper

Question

Can someone teach me how I can use this sql wrapper? I am confused what to put on the second parameter argument... thanks =)

public void Insert(string strSQL, List<MySqlParameter> params)
{
    if(this.OpenConnection() == true)
    {
        MySqlCommand cmd = new MySqlCommand(strSQL, connection)
        foreach(MySqlParameter param in params)
           cmd.Parameters.Add(param);

        cmd.ExecuteNonQuery();
        this.CloseConnection();
    }
 }

I just need an example on how to use this sql wrapper thanks =) — Netorica, Oct 08 '11 at 07:05
Are you sure this compiles? 'params' is a keyword so I doubt the compiler will accept that as an argument name? — Chris Taylor, Oct 08 '11 at 07:12
I just found this sql wrapper somewhere here the stackoverflow and paste it here... just wait a sec... I will put the link where I found it — Netorica, Oct 08 '11 at 07:15
http://stackoverflow.com/questions/2775692/c-and-mysql-net-connector-any-way-of-preventing-sql-injection-attacks-in-a-ge#_=_ — Netorica, Oct 08 '11 at 07:16
I will be honest, I do not think this is a great wrapper. 1 - It implies insert statement when it in fact can run any arbitrary SQL command, 2 - it should rather use a params array for the second argument, 3 - Since based on your question you are still comming to grips with C# this is not complete enough to show the proper connection handling etc. — Chris Taylor, Oct 08 '11 at 07:20

score 2 · Answer 1 · answered Oct 08 '11 at 07:10

2

You can use it like this,

List<MySqlParameter> sqlParams = new List<MySqlParameter>();

SqlParameter param1  = new SqlParameter();
param.ParameterName = "@City";
param.Value = inputCity;

SqlParameter param2  = new SqlParameter();
param.ParameterName = "@Country";
param.Value = inputCountry;

sqlParams.Add(param1);
sqlParams.Add(param2);

Insert("SELECT * FROM customers WHERE city = @City AND country = @Country", sqlParams);

answered Oct 08 '11 at 07:10

evilone

22,410
7
80
107

is there any shorter way that I can use it? or maybe some recode with the sql wrapper? because if I will use that approach I will not use the sql wrapper anymore because its the same length of codes need to use to use the parameterized querying without using the wrapper – Netorica Oct 08 '11 at 07:14
1

what you mean, shorter??? You asked how to use this wrapper in your example and I showed you an example... – evilone Oct 08 '11 at 07:15

Marc Gravell · Answer 2 · 2011-10-08T08:14:05.073

It sounds (comments) like the issue is the c# calling code, in which case dapper-dot-net might help - it is a highly optimised utility layer that makes calling easier, for example:

int id = 123;
string name = "Fred";
connection.Execute(@"insert into TableName (Id, Name) values (?id, ?name)",
    new {id, name});

It handles all the command and parameter code for you, so you just worry about the connection, the SQL and the values. There are also similar methods (Query[<T>] etc) for reading data. Example:

int customerId = 12345;
List<Order> orders = connection.Query<Order>(@"
    select * from Orders where CustomerId = ?customerId", new {customerId})
     .ToList();

thanks.... this seems to be a nice code but how I can use SELECT with this code? Ill just assign it on a SQLAdapter? — Netorica, Oct 08 '11 at 07:28

AndrewR · Answer 3 · 2011-10-08T07:19:36.400

0

Here is a sample code.

List<MySqlParameter> parameters = new List<MySqlParameter>();
string sql = "INSERT INTO table1 VALUES(?Name, ?Age)";
parameters.Add(new MySqlParameter() { ParameterName = "?Name", Value = "Mahan"});
parameters.Add(new MySqlParameter() { ParameterName = "?Age", Value = "??"});

Insert(sql, parameters);

edited Oct 08 '11 at 07:19

answered Oct 08 '11 at 07:12

AndrewR

6,668
1
24
38

Using a parametrized sql wrapper

3 Answers3