Reading sql one row at a time c#

Question

public void addintovisitor()
{
string companyname = (txtvisitor.Text.ToUpper());
DataSet result = new DataSet();
visitorcompany vc = new visitorcompany();
string Location1 = Convert.ToString(Session["location"]);
vc.checksamecompanyname(ref result, Location1);
for (int i = 0; i < result.Tables["details"].Rows.Count; i++)
{
if (companyname == result.Tables["details"].Rows[i]["Companyname"].ToString())
{

}
else
{
 string strConn = Convert.ToString(ConfigurationManager.ConnectionStrings["connectionstring"]);
 SqlConnection conn = new SqlConnection(strConn);
 SqlCommand cmd = new SqlCommand(
                  "INSERT INTO tblVisitorcompany ([CompanyName], " +
                  "[Location1]) " +
                  "VALUES(@CompanyName, @Location1)", conn);

                cmd.Parameters.AddWithValue("@Companyname", companyname);
                cmd.Parameters.AddWithValue("@Location1", Location1);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
 }
 }
 }

My visitorcompany class:

public int checksamecompanyname(ref DataSet result, string Location1)
{        
  string strConn = Convert.ToString(
  ConfigurationManager.ConnectionStrings
  ["connectionstring"]);
  SqlConnection conn = new SqlConnection(strConn);
  SqlCommand cmd = new SqlCommand
  ("select Companyname from tblVisitorcompany where Location1 ='" + Location1 + "'", conn);
  SqlDataAdapter da = new SqlDataAdapter(cmd);
  conn.Open();
  da.Fill(result, "details");
  conn.Close();
  //Return 0 when no error occurs.
  return 0;
  }

I am trying to search one row at a time to check whether the sql table got the same companyname. if there is already exisiting companyname, the program will do nothing. If this is a new companyname, the program will add companyname into the sql table. However, when adding new companyname, the program will add more than once. Can someone please help me to re-edit my program such that it only add one new companyname. Many thanks.

If you want to read data, you should use a `SELECT` statement and perform an `ExecuteReader` instead of an `ExecuteNonQuery`. To answer the real probleme of duplicate, you should create a `unique key` on the `companyname` column, then you can handle the exception in your C#. — Kobi, Jun 26 '18 at 13:40
a) You should have your database prevent you from inserting twice a row you don't want,you can use an autogenerated id as primary key and have a unique constraint on the company name b) You need to decide if you want to insert once and then skip if repeated, or update other fields. If you want to update, see Merge: https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017 else see "IF NOT EXISTS ..." https://stackoverflow.com/questions/1175217/sql-server-if-not-exists-usage — Vinko Vrsalovic, Jun 26 '18 at 13:45
With respect to the comments above, which would hint at a better database design, searching the database like this isn't very efficient, it would be better to let the database do the hard work for you. i.e. "SELECT COUNT(*) FROM tblVistorCompany WHERE Companyname like '" + name + "'"; — Phill, Jun 26 '18 at 14:01

score 1 · Answer 1 · answered Jun 26 '18 at 13:48

using( var connection = new SqlConnection( "my connection string" ) ) {
using( var command = connection.CreateCommand() ) {
    command.CommandText = "SELECT Column1, Column2, Column3 FROM myTable";

    connection.Open();
    using( var reader = command.ExecuteReader() ) {
        var indexOfColumn1 = reader.GetOrdinal( "Column1" );
        var indexOfColumn2 = reader.GetOrdinal( "Column2" );
        var indexOfColumn3 = reader.GetOrdinal( "Column3" );

        while( reader.Read() ) {
            var value1 = reader.GetValue( indexOfColumn1 );
            var value2 = reader.GetValue( indexOfColumn2 );
            var value3 = reader.GetValue( indexOfColumn3 );

            // now, do something what you want
        }
    }
    connection.Close();
}

score 0 · Answer 2 · answered Jun 26 '18 at 13:42

dont use companyname as an argument of your insert command, since it is stays the same in for loop. Use result.Tables["details"].Rows[i]["Companyname"].ToString() instead:

...
cmd.Parameters.AddWithValue("@Companyname", result.Tables["details"].Rows[i]["Companyname"].ToString());
...

Rafalon · Answer 3 · 2018-06-26T14:04:28.837

Check if the value exists, then add it if not.
A simple change in your code:

bool valueFound = false;

// check if the value exists
for (int i = 0; i < result.Tables["details"].Rows.Count; i++)
{
    if (companyname == result.Tables["details"].Rows[i]["Companyname"].ToString())
    {
        // it exists so we exit the loop
        valueFound = true;
        break;
    }
}

// we have looped all the way without finding the value, so we can insert
if(!valueFound)
{
    string strConn = Convert.ToString(ConfigurationManager.ConnectionStrings["connectionstring"]);
    SqlConnection conn = new SqlConnection(strConn);
    SqlCommand cmd = new SqlCommand(
          "INSERT INTO tblVisitorcompany ([CompanyName], " +
          "[Location1]) " +
          "VALUES(@CompanyName, @Location1)", conn);

     cmd.Parameters.AddWithValue("@Companyname", companyname);
     cmd.Parameters.AddWithValue("@Location1", Location1);
     conn.Open();
     cmd.ExecuteNonQuery();
     conn.Close();
}

Off course you could check if the value exists in a more efficient way, but this should at least solve your specific problem.

Reading sql one row at a time c#

3 Answers3