Is there a better way than this?

[Roy Gourgi]

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\SQL_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();

}

}

}

 

[tomb]

Can't you just append things manually to a string object?

T

 

[jeremiah johnson]

Can't you just append things manually to a string object?

T

yeah, do it this way:

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 ";
strConnection += "Source=.\SQLEXPRESS;AttachDbFilename=";
strConnection += "D:\CSRBC\SQL_2\SQL_2.mdf;Integrated "
strConnection +=Security=True;Connect Timeout=30;User Instance=True";

int ln1 = 5;

int ln2 = 19;

string strCommand = "INSERT INTO tblSQL_2 (SOBN , BN1 ) SELECT ";
strCommand += ln1 + " , " + ln2 + " WHERE not exists ";
strCommand += "(select * from tblSQL_2 where SOBN = ";
strCommand += ln1 + " AND BN1 = " + ln2 + ")";

SqlConnection oConnection = new SqlConnection(strConnection);

SqlCommand oCommand = new SqlCommand(strCommand, oConnection);

oCommand.Connection.Open();

oCommand.ExecuteNonQuery();

oCommand.Connection.Close();

}

}

}