By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,146 Members | 1,631 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,146 IT Pros & Developers. It's quick & easy.

Method design query

P: n/a
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.
Sep 2 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"Paolo" <Pa***@discussions.microsoft.comwrote in message
news:91**********************************@microsof t.com...
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.
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.
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.
Sep 2 '08 #2

P: n/a
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" wrote:
"Paolo" <Pa***@discussions.microsoft.comwrote in message
news:91**********************************@microsof t.com...
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.

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.

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.
Sep 2 '08 #3

P: n/a
"Paolo" <Pa***@discussions.microsoft.comwrote in message
news:C3**********************************@microsof t.com...
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.
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?
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
Sep 2 '08 #4

P: n/a
"Paolo" <Pa***@discussions.microsoft.comwrote in message
news:C3**********************************@microsof t.com...
Is 'LINQ To SQL' the generic name for the LINQ facilities within C#3.0 or
something different altogether?
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.

Sep 2 '08 #5

P: n/a
Is 'LINQ To SQL' *the generic name for the LINQ facilities within C#3.0or
something different altogether?
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.
Sep 2 '08 #6

P: n/a
"Paolo" <Pa***@discussions.microsoft.comwrote in message
news:91**********************************@microsof t.com...
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>)
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[i]);
cmd.ExecuteNonQuery();
}
}
Sep 2 '08 #7

P: n/a
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.

"Pavel Minaev" wrote:
"Paolo" <Pa***@discussions.microsoft.comwrote in message
news:C3**********************************@microsof t.com...
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.

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?

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
Sep 2 '08 #8

P: n/a
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.

"Marc Gravell" wrote:
Is 'LINQ To SQL' the generic name for the LINQ facilities within C#3.0 or
something different altogether?

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.
Sep 2 '08 #9

P: n/a
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" wrote:
"Paolo" <Pa***@discussions.microsoft.comwrote in message
news:91**********************************@microsof t.com...
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>)

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[i]);
cmd.ExecuteNonQuery();
}
}
Sep 2 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.