How to declare a count parameter then check it before inserting data in StringBuilder

Question

Using String Builder, I need to check whether the text in 3 fields in my app are already in 3 corresponding columns in my database table before inserting any of them.

First I put the text from the 3 fields into object properties called LastName, StreetAddress and ZipCode. Then I created my parameters. hen I did this:

var sb = new StringBuilder();

sb.Append("DECLARE @countLastName INT;");   

sb.Append("DECLARE @countStreetAddress INT;");

sb.Append("DECLARE @countZipCode INT;");

sb.Append("SELECT @countLastName = COUNT (*) FROM tblCustomer WHERE LastName = @LastName;");

sb.Append("SELECT @countStreetAddress = COUNT (*) FROM tblCustomer WHERE StreetAddress = @StreetAddress;");

sb.Append("SELECT @countZipCode = COUNT (*) FROM tblCustomer WHERE ZipCode = @ZipCode;");

if (@countLastName < 1 || @countStreetAddress < 1 || @countZipCode < 1)
{                  
    sb.Append("INSERT INTO tblCustomer (");

    sb.Append("LastName, ");

    sb.Append("StreetAddress, ");

    sb.Append("ZipCode) ");

    sb.Append("VALUES (@LastName, @StreetAddress, @ZipCode) ");
}
else 
{
    [Code that results in 1 of 3 messages being displayed to user based on which field is already in database. IE "LastName already exists"]
}

reader.CommandText = sb + "";
}

reader.Execute();

I assigned the String Builder to my reader.CommandText, then did a reader.Execute();, as I've done for all my database hits.

Visual Studio doesn't like that I put the @count variables into my IF statement. There's a red squiggly lines under each one of them.

So how would I check using String Builder that my count variables are less than 1 before inserting my values?

Thank you!

String Builder does not retrieve data from a database, so there is no way for you to do it this way. All you are doing here is creating a string with those sql statements in it. It seems clear to me that you have not researched how to select data from a database in C#. You should do that and then come back when you have a better idea of what you're trying to do — Kevin, Dec 21 '15 at 23:01
See [this question and answer](http://stackoverflow.com/questions/14171794/retrieve-data-from-a-sql-server-database-in-c-sharp) to get an idea of how to get data from sql server in C# — Kevin, Dec 21 '15 at 23:05
My apologies. I didn't say that I then inserted my string into reader.CommandText and then did a reader.Execute(), the same as I do for all my database access methods that work great. I really thought this is just an issue of how to declare a parameter in StringBuilder and then check it before inserting, if I can do that at all. Does that help you? — user2980343, Dec 21 '15 at 23:19
I think you need to run ExecuteScalar on each of those SELECT statements, place the return value into a variable. That variable is what gets used in your IF condition. You don't need the DECLARE lines, just add the parameter information to your COMMAND object before you execute it. — LarsTech, Dec 21 '15 at 23:23
If you are doing that `CommandText` and `Execute` you should include that in your code sample since those are crucial to what you are doing here — Kevin, Dec 21 '15 at 23:29
Thank you, @Kevin. I updated my code. Still isn't the entire method but I hope it's enough. Trying to balance between too little and too much. — user2980343, Dec 21 '15 at 23:34
If you are actually doing the read after you try to access the results that would be a major problem. You have to execute commands, get the results from those commands, put those results in a c# variable, then check the contents of the variables in your `if` statement. Right now you are not doing that in the right order or at all — Kevin, Dec 21 '15 at 23:36

score 0 · Accepted Answer · answered Dec 22 '15 at 00:08

Well, you cant execute within StringBuilder but you can change your command to do whole logic in SQL Server.

For example I would do it this way (ignoring which column is duplicate):

IF(select COUNT (*) FROM tblCustomer WHERE LastName = @LastName or StreetAddress = @StreetAddress or ZipCode = @ZipCode) = 0 BEGIN
    INSERT INTO tblCustomer (LastName, StreetAddress, ZipCode) VALUES (@LastName, @StreetAddress, @ZipCode)
END

After I execute this with ExecuteNonQuery method, I'll get affected row count in return value and know if any inserts are made. (Note that triggers may change affected row count)

Another way is to change command that checks and selects a result value (like 0: ok, 1: last name already exists, 2: address already exists etc). So that you can use ExecuteScalar or ExecuteReader and get selected value as result.

IF(select COUNT (*) FROM tblCustomer WHERE LastName = @LastName) > 0 BEGIN
    SELECT 1
END ELSE IF(select COUNT (*) FROM tblCustomer WHERE StreetAddress = @StreetAddress) > 0 BEGIN
    SELECT 2
END ELSE IF(select COUNT (*) FROM tblCustomer WHERE ZipCode = @ZipCode) > 0 BEGIN
    SELECT 3
END ELSE BEGIN
    INSERT INTO tblCustomer (LastName, StreetAddress, ZipCode) VALUES (@LastName, @StreetAddress, @ZipCode)
    SELECT 0
END

Third way is to create a SP to do whole logic like above.

Neither methods uses StringBuilder, a single string assigned to command.CommandText is enough I guess..

Thank you for your help, @Ramazan. I tried your second suggestion but I ran into trouble because the app didn't like the selects ("Select 1", "Select 2" ...), I think because it's wired to some external code that would need to be changed. Instead I just simplified things and used your first suggestion. Worked perfectly! — user2980343, Jan 08 '16 at 21:40

How to declare a count parameter then check it before inserting data in StringBuilder

1 Answers1