Method design query

P

Paolo

I'm new to C# so apologies if this is a dumb question.

If I want to insert a new row into a SQLServer table I can have a method
something like this:

public void insertRow(int id, string description, datetime yearConstructed)
{
string sql = string.Format("Insert into <tableName>"+
"(idKey, makeDescription, yearBuilt) Values"+
"('{0}', '{1}', '{2}')", id, description, yearConstructed);

using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
cmd.ExecuteNonQuery();
}
}

This is fine but hard codes column and table names and data parameters into
the method.

What I want to do is write a generalised Insertion method that can take any
number of column names and their associated data parameters and table name
whose signature looks something like this:

public void insertRow(string tableName, string[] columnNames, <a parameter
that can take varying numbers of different data types>)

and within the method iterate through the column names and data names/types
to build up the required SQL command.

In essence I'm looking to write reusable code. With my limited knowledge of
C# I'm not sure how to tackle this, especially how to pass a bunch of
differing data types to the method. I'd envisage using a Stringbuilder to
build up the first part of the Insertion command but am not sure how to get
the data names into the command i.e. the equivalents of id, description and
yearConstructed in the initial example.

All contributions gratefully accepted.
 
P

Pavel Minaev

Paolo said:
I'm new to C# so apologies if this is a dumb question.

If I want to insert a new row into a SQLServer table I can have a method
something like this:

public void insertRow(int id, string description, datetime
yearConstructed)
{
string sql = string.Format("Insert into <tableName>"+
"(idKey, makeDescription, yearBuilt) Values"+
"('{0}', '{1}', '{2}')", id, description, yearConstructed);

using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
cmd.ExecuteNonQuery();
}
}

This is fine but hard codes column and table names and data parameters
into
the method.
Click to expand...

No, this is not fine at all. It's a classic example of SQL injection
vulnerability. Don't ever use plain string operations to produce SQL - use
parametrised statements instead:

string sql = "Insert into <tableName> (idKey, makeDescription, yearBuilt)
Values(@id,@descroption,@yearConstructed)";
SqlCommand cmd = new SqlCommand(sql, conn);

SqlParameter idParam = cmd.Parameters.Add("id", SqlDbType.Int);
idParam.Value = id;

SqlParameter descriptionParam = cmd.Parameters.Add("description",
SqlDbType.VarChar);
descriptionParam.Value = description;

...

cmd.ExecuteNonQuery();

Read http://en.wikipedia.org/wiki/Sql_injection for more detailed
explanation of SQL injection in general.
What I want to do is write a generalised Insertion method that can take
any
number of column names and their associated data parameters and table name
whose signature looks something like this:

public void insertRow(string tableName, string[] columnNames, <a parameter
that can take varying numbers of different data types>)

and within the method iterate through the column names and data
names/types
to build up the required SQL command.

In essence I'm looking to write reusable code. With my limited knowledge
of
C# I'm not sure how to tackle this, especially how to pass a bunch of
differing data types to the method. I'd envisage using a Stringbuilder to
build up the first part of the Insertion command but am not sure how to
get
the data names into the command i.e. the equivalents of id, description
and
yearConstructed in the initial example.
Click to expand...

Passing variable number of parameters of different types is actually easy -
look at how Console.WriteLine does it, for example.

void InsertRow(string tableName, string[] columnNames, params object[]
values);

Any value (well, except pointers) can be boxed to object, queried regarding
its actual type while boxed, and unboxed again, so "anything goes here" in
C# is object.

Even so, this signature is not particularly good - for one thing, because
the client can easily pass arrays of differing lengths to columnNames and
values. A better one could be:

void InsertRow(string tableName, params KeyValuePair<string, object>
values);

Here, the Key in each KeyValuePair would be the field name, and Value would
be the value.

Of course, you still should not use StringBuilder or anything like that, but
a parametrized statement, as described above.

Finally, if all you're trying to do is to save yourself typing a few lines
of code, and you don't actually have the situation when you don't know the
name of table and fields until run-time, then consider using just typed
datasets or LINQ to SQL instead of trying to do your own.
 
P

Paolo

Pavel: thank you for your comprehensive reply. I wasn't aware of the SQL
injection issue, although I have actually used parameterised statements in my
actual code - I posted in the form I did to provide a more concise statement
of what I was trying to achieve.

I will follow up your suggestion of using a KeyValuePair. It's not just a
question of saving a few lines of code - I'm trying to develop a 'toolset' to
allow me to put together database apps fairly quickly and using the
experience as a learning process.

Is 'LINQ To SQL' the generic name for the LINQ facilities within C#3.0 or
something different altogether?

Thanks

Pavel Minaev said:
Paolo said:
I'm new to C# so apologies if this is a dumb question.

If I want to insert a new row into a SQLServer table I can have a method
something like this:

public void insertRow(int id, string description, datetime
yearConstructed)
{
string sql = string.Format("Insert into <tableName>"+
"(idKey, makeDescription, yearBuilt) Values"+
"('{0}', '{1}', '{2}')", id, description, yearConstructed);

using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
cmd.ExecuteNonQuery();
}
}

This is fine but hard codes column and table names and data parameters
into
the method.
Click to expand...

No, this is not fine at all. It's a classic example of SQL injection
vulnerability. Don't ever use plain string operations to produce SQL - use
parametrised statements instead:

string sql = "Insert into <tableName> (idKey, makeDescription, yearBuilt)
Values(@id,@descroption,@yearConstructed)";
SqlCommand cmd = new SqlCommand(sql, conn);

SqlParameter idParam = cmd.Parameters.Add("id", SqlDbType.Int);
idParam.Value = id;

SqlParameter descriptionParam = cmd.Parameters.Add("description",
SqlDbType.VarChar);
descriptionParam.Value = description;

...

cmd.ExecuteNonQuery();

Read http://en.wikipedia.org/wiki/Sql_injection for more detailed
explanation of SQL injection in general.
What I want to do is write a generalised Insertion method that can take
any
number of column names and their associated data parameters and table name
whose signature looks something like this:

public void insertRow(string tableName, string[] columnNames, <a parameter
that can take varying numbers of different data types>)

and within the method iterate through the column names and data
names/types
to build up the required SQL command.

In essence I'm looking to write reusable code. With my limited knowledge
of
C# I'm not sure how to tackle this, especially how to pass a bunch of
differing data types to the method. I'd envisage using a Stringbuilder to
build up the first part of the Insertion command but am not sure how to
get
the data names into the command i.e. the equivalents of id, description
and
yearConstructed in the initial example.
Click to expand...

Passing variable number of parameters of different types is actually easy -
look at how Console.WriteLine does it, for example.

void InsertRow(string tableName, string[] columnNames, params object[]
values);

Any value (well, except pointers) can be boxed to object, queried regarding
its actual type while boxed, and unboxed again, so "anything goes here" in
C# is object.

Even so, this signature is not particularly good - for one thing, because
the client can easily pass arrays of differing lengths to columnNames and
values. A better one could be:

void InsertRow(string tableName, params KeyValuePair<string, object>
values);

Here, the Key in each KeyValuePair would be the field name, and Value would
be the value.

Of course, you still should not use StringBuilder or anything like that, but
a parametrized statement, as described above.

Finally, if all you're trying to do is to save yourself typing a few lines
of code, and you don't actually have the situation when you don't know the
name of table and fields until run-time, then consider using just typed
datasets or LINQ to SQL instead of trying to do your own.
Click to expand...
 
P

Pavel Minaev

Paolo said:
I will follow up your suggestion of using a KeyValuePair. It's not just a
question of saving a few lines of code - I'm trying to develop a 'toolset'
to
allow me to put together database apps fairly quickly and using the
experience as a learning process.
Click to expand...

If you're looking for a generalized DB framework, then I would even stronger
urge you to use an existing solution for that in a production project. It's
not a simple thing to write on your own, and don't forget about need to
maintain that extra code in the future.
Is 'LINQ To SQL' the generic name for the LINQ facilities within C#3.0 or
something different altogether?
Click to expand...

It's a thing on its own, which basically maps tables in SQL Server databases
to .NET classes and collections of those classes (via code generation), and
then allows you to use LINQ to query them. Such queries are translated to
proper SQL in background. More details here:

http://msdn.microsoft.com/en-us/library/bb425822.aspx

Then there's also LINQ to Entities, which is somewhat higher on the
abstraction ladder (arguably, high enough to be considered an ORM):

http://msdn.microsoft.com/en-us/library/bb386964.aspx

Then there are 3rd-party solutions, such as NHibernate ORM. This one doesn't
have LINQ in stable version yet, unfortunately, but otherwise it is a widely
used and mature solution:

http://sourceforge.net/projects/nhibernate
 
A

Alberto Poblacion

Paolo said:
Is 'LINQ To SQL' the generic name for the LINQ facilities within C#3.0 or
something different altogether?
Click to expand...

LINQ to SQL is just one of the varieties of LINQ. LINQ is the "language
integrated query" that let's you use sql-like statements inside your code.
The C# compiler translates those queries into calls to methods on the
queried object. Those methods will typically be extension methods, which
will depend on the libraries that you bring into scope. Depending on which
libraries you use in that way, the LINQ queries could operate on different
kinds of objects. This gives us things like LINQ to XML, LINQ to ADO.NET,
LINQ to Objects... or LINQ to SQL.
 
M

Marc Gravell

Is 'LINQ To SQL'  the generic name for the LINQ facilities within C#3.0or
something different altogether?
Click to expand...

LINQ to SQL is the simpler of 2 ORM-related technologies that are
in .NET 3.5 (LINQ to SQL) and .NET 3.5 SP 1 (Entity Framework / LINQ
to EF) respectively. LINQ to SQL provides rich tooling for operations
against SQL Server - probably everything you are looking for and a lot
more besides. The main difference between this and your code is that
it is more object-oriented (you add on object with properties that are
mapped to database columns, and the runtime converts that into TSQL
for you). LINQ to EF has a /much/ more complex* mapping between the
underlying database and the object model, and supports different back-
end providers (not just SQL Server).

Marc

*=which can be a good thing or a bad thing, or both at the same time.
 
A

Alberto Poblacion

Paolo said:
What I want to do is write a generalised Insertion method that can take
any
number of column names and their associated data parameters and table name
whose signature looks something like this:

public void insertRow(string tableName, string[] columnNames, <a parameter
that can take varying numbers of different data types>)
Click to expand...

Here it is (untested, written from memory):

public void insertRow(string tableName, string[] columnNames, params
object[] values)
{
System.Diagnostics.Debug.Assert(columnNames.Length==values.Length);
StringBuilder sql = new StringBuilder();
sql.Append("Insert into "+tableName(");
foreach (string s in columnNames) sql.Append(s+",");
sql.Remove(sql.Length-1,1);
sql.Append(") values (";
for (int i=0; i<values.Length; i++) sql.Append("@P"+i+",");
sql.Remove(sql.Length-1,1);
sql.Append(")");
using(SqlCommand cmd = new SqlCommand(sql.ToString(), this.sqlCn))
{
for (int i=0; i<values.Length; i++)
cmd.Parameters.AddWithValue("@P"+i, values);
cmd.ExecuteNonQuery();
}
}
 
P

Paolo

Pavel: you've just made me realise how much I've got to learn about
C#/LINQ/.NET! and I will follow up your links.

I understand your comment about complexity. This is not a production
project. As I mentioned initially, it's a way of giving me a concrete project
through which to learn C# (although I can now see the learning curve becoming
somewhat steeper, but I shall start relatively simply)

Thanks once again.
 
P

Paolo

Marc: thank you. As I said in my reply to Pavel, I shall be taking small
steps before I submerge myself in the rather more advanced concepts you
describe.
 
P

Paolo

Alberto: thank you for your explanation of LINQ etc and for your sample
solution which will provide me with a template for developing my own code.

Alberto Poblacion said:
Paolo said:
What I want to do is write a generalised Insertion method that can take
any
number of column names and their associated data parameters and table name
whose signature looks something like this:

public void insertRow(string tableName, string[] columnNames, <a parameter
that can take varying numbers of different data types>)
Click to expand...

Here it is (untested, written from memory):

public void insertRow(string tableName, string[] columnNames, params
object[] values)
{
System.Diagnostics.Debug.Assert(columnNames.Length==values.Length);
StringBuilder sql = new StringBuilder();
sql.Append("Insert into "+tableName(");
foreach (string s in columnNames) sql.Append(s+",");
sql.Remove(sql.Length-1,1);
sql.Append(") values (";
for (int i=0; i<values.Length; i++) sql.Append("@P"+i+",");
sql.Remove(sql.Length-1,1);
sql.Append(")");
using(SqlCommand cmd = new SqlCommand(sql.ToString(), this.sqlCn))
{
for (int i=0; i<values.Length; i++)
cmd.Parameters.AddWithValue("@P"+i, values);
cmd.ExecuteNonQuery();
}
}
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CLR stored procedure fails to rollback the transaction? 9
creating ExecuteQuery method 34
Testing a class method 2
Pass param to an invoked method 1
Preserving column names in a data set 2
Generic Method Help 12
NorthWind Database, can't view images 2
System.NullReferenceException - Object reference not set to an instance of an object 17

Top