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
– Kobi, Commented Jun 26, 2018 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: learn.microsoft.com/en-us/sql/t-sql/statements/… else see "IF NOT EXISTS ..." stackoverflow.com/questions/1175217/… — Vinko Vrsalovic
– Vinko Vrsalovic, Commented Jun 26, 2018 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
– Phill, Commented Jun 26, 2018 at 14:01

bitsNbytes · Accepted Answer · 2018-06-26 13:48:43Z

1

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();
}

answered Jun 26, 2018 at 13:48

bitsNbytes

646 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

apomene · Accepted Answer · 2018-06-26 13:42:10Z

0

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());
...

answered Jun 26, 2018 at 13:42

apomene

14.4k10 gold badges51 silver badges76 bronze badges

Comments

Rafalon · Accepted Answer · 2018-06-26 14:04:28Z

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.

Collectives™ on Stack Overflow

Reading sql one row at a time c#

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related