473,228 Members | 1,756 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,228 software developers and data experts.

Method design query

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
9 1406
"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
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
"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
"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
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
"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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

24
by: el_roachmeister | last post by:
Is there a way to make a text link post to a form without passing all the parameters in the url? The urls tend to get very long and messy. I often wonder if there is a limit to how long they can...
9
by: sk | last post by:
I have an applicaton in which I collect data for different parameters for a set of devices. The data are entered into a single table, each set of name, value pairs time-stamped and associated with...
2
by: Test User | last post by:
Hi all, (please excuse the crosspost as I'm trying to reach as many people as possible) I am somewhat familiar with Access 2000, but my latest project has me stumped. So, I defer to you...
2
by: Pete | last post by:
Before I get started with the question, does anyone have a (single) good book recommendation for database design? Not an Access-specific book, but something geared toward helping me figure out...
6
by: NB | last post by:
Hi Is there any way to call up the query design view from code? In my compiled-as-MDE app everything is hidden from end users. However, I want advanced user to have access to the query design...
2
by: Ray | last post by:
Stop me if you've heard this, but I am running Access 2002 and all of a sudden, if I design a particular form (it's been working fine for ages), Access crashes rather than open it in design view. ...
0
by: YellowFin Announcements | last post by:
Introduction Usability and relevance have been identified as the major factors preventing mass adoption of Business Intelligence applications. What we have today are traditional BI tools that...
5
by: rdemyan via AccessMonster.com | last post by:
I have a need to add another field to all of my tables (over 150). Not data, but an actual field. Can I code this somehow. So the code presumabley would loop through all the tables, open each...
4
by: JB | last post by:
Hi All, I need to write my first "distributed" application and due to my lack of knowledge and experience in that area, I'm stuck on the first big design decision. Reading a lot on distributed...
2
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.