Is there a better way than this?

Hi,
I am adding a row to my table with the code below. It seems to be
inefficient to me as I have to basically build my command with the string
builder. There has to be a better way, no?
What I am trying to do is to search my table for the 2 variables ln1 and
ln2. If they are not found, then one row is added to the table with the
respective values of ln1 and ln2. So for example, if my table contained 100
rows and I perform a search for the values ln1 and ln2 and they are not
found, then one row is added to my table with the values of ln1 and ln2.
Therefore, my table will now have 101 rows.
The code below works, it's just that I think there must be a more efficient
way of doing it.
Another thing that I find that is strange is that when a row is added to my
table, it is added in between rows so that the ascending order is
maintained. Why is that, I as have never asked to sort it or index it.
TIA
Roy
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
namespace testing
{
class Class1
{
// private System.Data.DataSet dataSet;
[STAThread]
static void Main(string[] args)
{
string strConnection = @."Data
Source=. \SQLEXPRESS;AttachDbFilename=D:\CSRBC\SQ
L_2\SQL_2.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=True";
int ln1 = 5;
int ln2 = 19;
StringBuilder strCommand = new StringBuilder("INSERT INTO tblSQL_2 (SOBN ,
BN1 ) SELECT ");
strCommand.Append(ln1).Append(" , ").Append(ln2).Append(" WHERE not exists
(select * from tblSQL_2 where SOBN = ");
strCommand.Append(ln1).Append(" AND BN1 = ").Append(ln2).Append(")");
SqlConnection oConnection = new SqlConnection(strConnection);
SqlCommand oCommand = new SqlCommand(strCommand.ToString(), oConnection);
oCommand.Connection.Open();
oCommand.ExecuteNonQuery();
oCommand.Connection.Close();
}
}
}I suggest you use a parameterized SQL statement instead of building a SQL
statement string with the literal values. For example:
SqlCommand oCommand = new SqlCommand(
"INSERT INTO tblSQL_2 (SOBN ,BN1 ) " +
" SELECT @.In1, @.In2" +
" WHERE NOT EXISTS(" +
" SELECT *" +
" FROM tblSQL_2" +
" WHERE SOBN = @.In1 AND" +
" BN1 = @.In2)",
oConnection);
oCommand.Parameters.Add("@.In1", In1);
oCommand.Parameters.Add("@.In2", In2);

> Another thing that I find that is strange is that when a row is added to
> my
> table, it is added in between rows so that the ascending order is
> maintained. Why is that, I as have never asked to sort it or index it.
A table in a relational database is an unordered set of rows. Regardless of
insert order or indexing, you must specify ORDER BY to return data in a
particular sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:RREff.8561$0u1.70303@.weber.videotron.net...
> Hi,
> I am adding a row to my table with the code below. It seems to be
> inefficient to me as I have to basically build my command with the string
> builder. There has to be a better way, no?
> What I am trying to do is to search my table for the 2 variables ln1 and
> ln2. If they are not found, then one row is added to the table with the
> respective values of ln1 and ln2. So for example, if my table contained
> 100
> rows and I perform a search for the values ln1 and ln2 and they are not
> found, then one row is added to my table with the values of ln1 and ln2.
> Therefore, my table will now have 101 rows.
> The code below works, it's just that I think there must be a more
> efficient
> way of doing it.
> Another thing that I find that is strange is that when a row is added to
> my
> table, it is added in between rows so that the ascending order is
> maintained. Why is that, I as have never asked to sort it or index it.
> TIA
> Roy
>
> using System;
> using System.Data;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Text;
>
> namespace testing
> {
> class Class1
> {
> // private System.Data.DataSet dataSet;
> [STAThread]
>
> static void Main(string[] args)
> {
> string strConnection = @."Data
> Source=. \SQLEXPRESS;AttachDbFilename=D:\CSRBC\SQ
L_2\SQL_2.mdf;Integrated
> Security=True;Connect Timeout=30;User Instance=True";
> int ln1 = 5;
> int ln2 = 19;
> StringBuilder strCommand = new StringBuilder("INSERT INTO tblSQL_2 (SOBN ,
> BN1 ) SELECT ");
> strCommand.Append(ln1).Append(" , ").Append(ln2).Append(" WHERE not exists
> (select * from tblSQL_2 where SOBN = ");
> strCommand.Append(ln1).Append(" AND BN1 = ").Append(ln2).Append(")");
> SqlConnection oConnection = new SqlConnection(strConnection);
> SqlCommand oCommand = new SqlCommand(strCommand.ToString(), oConnection);
> oCommand.Connection.Open();
> oCommand.ExecuteNonQuery();
> oCommand.Connection.Close();
> }
> }
> }
>
>|||"Roy Gourgi" <royng@.videotron.ca> wrote in
news:RREff.8561$0u1.70303@.weber.videotron.net:

> Hi,
> I am adding a row to my table with the code below. It seems to be
> inefficient to me as I have to basically build my command with the
> string builder. There has to be a better way, no?
> What I am trying to do is to search my table for the 2 variables ln1
> and ln2. If they are not found, then one row is added to the table
> with the respective values of ln1 and ln2. So for example, if my table
> contained 100 rows and I perform a search for the values ln1 and ln2
> and they are not found, then one row is added to my table with the
> values of ln1 and ln2. Therefore, my table will now have 101 rows.
> The code below works, it's just that I think there must be a more
> efficient way of doing it.
> Another thing that I find that is strange is that when a row is added
> to my table, it is added in between rows so that the ascending order
> is maintained. Why is that, I as have never asked to sort it or index
> it.
> TIA
> Roy
>
> using System;
> using System.Data;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Text;
>
> namespace testing
> {
> class Class1
> {
> // private System.Data.DataSet dataSet;
> [STAThread]
>
> static void Main(string[] args)
> {
> string strConnection = @."Data
> Source=. \SQLEXPRESS;AttachDbFilename=D:\CSRBC\SQ
L_2\SQL_
2.mdf;Integrate
> d Security=True;Connect Timeout=30;User Instance=True";
> int ln1 = 5;
> int ln2 = 19;
> StringBuilder strCommand = new StringBuilder("INSERT INTO tblSQL_2
> (SOBN , BN1 ) SELECT ");
> strCommand.Append(ln1).Append(" , ").Append(ln2).Append(" WHERE not
> exists (select * from tblSQL_2 where SOBN = ");
> strCommand.Append(ln1).Append(" AND BN1 = ").Append(ln2).Append(")");
> SqlConnection oConnection = new SqlConnection(strConnection);
> SqlCommand oCommand = new SqlCommand(strCommand.ToString(),
> oConnection);
> oCommand.Connection.Open();
> oCommand.ExecuteNonQuery();
> oCommand.Connection.Close();
> }
> }
> }
Why use CLR at all, this can perfectly well be done with pure T-SQL. I
leave handling of NULLs to the reader.
create table demo (
val_a int,
val_b int
)
insert into demo values (1,1)
insert into demo values (1,2)
insert into demo values (2,1)
select count(1) from demo where (val_a = 1) and (val_b = 0)
create procedure prDemo @.a int, @.b int as
begin
declare
@.count int;
select
@.count = count(1)
from
demo
where
(val_a = @.a) and (val_b = @.b);
if @.count = 0
insert into demo values (@.a,@.b)
end;
exec prDemo 1,2
exec prDemo 2,2
select * from Demo
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Hi Dan,
Your code works great, but I do get some warning that says that this
line:
oCommand.Parameters.Add("@.ln1", ln1);
has been deprecated and to use:
AddWithValue(string ParameterName, Object value);
You said:
A table in a relational database is an unordered set of rows. Regardless
of
insert order or indexing, you must specify ORDER BY to return data in a
particular sequence.
That is what I thought too, but when I add the row it adds it in between
rows and preserving the ascending order. I never asked it to ORDER BY a
particular sequence.
BTW are you not from Montreal? I think that I once bought a computer
from you. Small world.
Thanks
Roy
*** Sent via Developersdex http://www.examnotes.net ***|||> has been deprecated and to use:
> AddWithValue(string ParameterName, Object value);
I agree that you should avoid the deprecated method. I didn't know which
version of .Net you are using.

> BTW are you not from Montreal? I think that I once bought a computer
> from you. Small world.
Not me - never been to Canada. A bit too cold for my blood :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:OO7pB8Q7FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi Dan,
> Your code works great, but I do get some warning that says that this
> line:
> oCommand.Parameters.Add("@.ln1", ln1);
> has been deprecated and to use:
> AddWithValue(string ParameterName, Object value);
> You said:
> A table in a relational database is an unordered set of rows. Regardless
> of
> insert order or indexing, you must specify ORDER BY to return data in a
> particular sequence.
> That is what I thought too, but when I add the row it adds it in between
> rows and preserving the ascending order. I never asked it to ORDER BY a
> particular sequence.
> BTW are you not from Montreal? I think that I once bought a computer
> from you. Small world.
> Thanks
> Roy
>
> *** Sent via Developersdex http://www.examnotes.net ***|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:ObF82qQ7FHA.1000@.tk2msftngp13.phx.gbl:

> I suggest you use a parameterized SQL statement instead of building a
> SQL statement string with the literal values. For example:
I agree that parameterized is better in this case, but I do not see the
need of CLR. This appears to me to be a very good example of a case in
which CLR should not be used, but T-SQL.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Hi Ole,
Sorry for asking but what is T-SQL?
How do I implement this into my program. I am using C# and SQL 2005 Express
edition.
Do I just put this into my code?
Roy

> Why use CLR at all, this can perfectly well be done with pure T-SQL. I
> leave handling of NULLs to the reader.
> create table demo (
> val_a int,
> val_b int
> )
> insert into demo values (1,1)
> insert into demo values (1,2)
> insert into demo values (2,1)
> select count(1) from demo where (val_a = 1) and (val_b = 0)
> create procedure prDemo @.a int, @.b int as
> begin
> declare
> @.count int;
> select
> @.count = count(1)
> from
> demo
> where
> (val_a = @.a) and (val_b = @.b);
> if @.count = 0
> insert into demo values (@.a,@.b)
> end;
> exec prDemo 1,2
> exec prDemo 2,2
> select * from Demo
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns971392C765414olekristianbangaas@.
207.46.248.16...
> "Roy Gourgi" <royng@.videotron.ca> wrote in
> news:RREff.8561$0u1.70303@.weber.videotron.net:
>
> 2.mdf;Integrate
> Why use CLR at all, this can perfectly well be done with pure T-SQL. I
> leave handling of NULLs to the reader.
> create table demo (
> val_a int,
> val_b int
> )
> insert into demo values (1,1)
> insert into demo values (1,2)
> insert into demo values (2,1)
> select count(1) from demo where (val_a = 1) and (val_b = 0)
> create procedure prDemo @.a int, @.b int as
> begin
> declare
> @.count int;
> select
> @.count = count(1)
> from
> demo
> where
> (val_a = @.a) and (val_b = @.b);
> if @.count = 0
> insert into demo values (@.a,@.b)
> end;
> exec prDemo 1,2
> exec prDemo 2,2
> select * from Demo
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||One more thing Ole, would this code be faster than the other code?
Thanks
Roy
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns971392C765414olekristianbangaas@.
207.46.248.16...
> "Roy Gourgi" <royng@.videotron.ca> wrote in
> news:RREff.8561$0u1.70303@.weber.videotron.net:
>
> 2.mdf;Integrate
> Why use CLR at all, this can perfectly well be done with pure T-SQL. I
> leave handling of NULLs to the reader.
> create table demo (
> val_a int,
> val_b int
> )
> insert into demo values (1,1)
> insert into demo values (1,2)
> insert into demo values (2,1)
> select count(1) from demo where (val_a = 1) and (val_b = 0)
> create procedure prDemo @.a int, @.b int as
> begin
> declare
> @.count int;
> select
> @.count = count(1)
> from
> demo
> where
> (val_a = @.a) and (val_b = @.b);
> if @.count = 0
> insert into demo values (@.a,@.b)
> end;
> exec prDemo 1,2
> exec prDemo 2,2
> select * from Demo
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Roy Gourgi" <royng@.videotron.ca> wrote in
news:FBHff.8901$0u1.153785@.weber.videotron.net:

> Sorry for asking but what is T-SQL?
> How do I implement this into my program. I am using C# and SQL 2005
> Express edition.
> Do I just put this into my code?
T-SQL is Transact SQL, the SQL variant used by SQL Server. By using code
like the one that I wrote on the SQL Server, you can call the stored
procedure multiple times later. If you do not have any experience with SQL,
I would strongly suggest that you learn a bit of SQL before you continue.
So, no you don't put this directly into your code, but make call to the
stored proceudre from your code.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Roy Gourgi" <royng@.videotron.ca> wrote in
news:HDHff.8902$0u1.155354@.weber.videotron.net:

> One more thing Ole, would this code be faster than the other code?
As far as I've understood .NET CLR, it possibly would since the stored
procedure is precompiled. There are no way that the .NET code will be
faster.
The greatest advantage for you as a developer however, is the option to
modify the logic of the stored procedure in only one location.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messagingsql