CODEWITHSUNDEEP
c#database
Hiru
Hiru

Reputation: 91

how to compare elements in a string with the database table values

In my project i have to give a string input through a text field, and i have to fill a database table with these values. I should first check the values of a specific table column, and add the input string only if it is not there in the table already.

I tried to convert the table values to a string array, but it wasn,t possible.

If anyone have an idea about this, your reply will be really valuable.

Thankx in advance.

Upvotes: 0

Views: 3963

Answers (1)

marc_s
marc_s

Reputation: 754220

Since you say your strings in the database table must be unique, just put a unique index on that field and let the database handle the problem.

CREATE UNIQUE INDEX UIX_YourTableName_YourFieldName
   ON dbo.YourTableName(YourFieldName)

Whenever you will try to insert another row with the same string, SQL Server (or any other decent RDBMS) will throw an exception and not insert the value. Problem solved.

If you need to handle the error on the front-end GUI already, you'll need to load the existing entries from your database, using whatever technology you're familiar with, e.g. in ADO.NET (C#, SQL Server) you could do something like:

public List<string> FindExistingValues()
{
   List<string> results = new List<string>();

   string getStringsCmd = "SELECT (YourFieldName) FROM dbo.YourTableName";

   using(SqlConnection _con = new SqlConnection("your connection string here"))
   using(SqlCommand _cmd = new SqlCommand(getStringsCmd, _con)
   {
      _con.Open();      

      using(SqlDataReader rdr = _con.ExecuteReader())
      {
         while(rdr.Read())
         {
             results.Add(rdr.GetString(0));
         }
         rdr.Close();
      }

      _con.Close();      
   }

   return results;
}

You would get back a List<string> from that method and then you could check in your UI whether a given string already exists in the list:

List<string> existing = FindExistingValues();

if(!existing.Contains(yournewstring))
{
    // store the new value to the database
}

Or third option: you could write a stored procedure that will handle the storing of your new string. Inside it, first check to see whether the string already exists in the database

IF NOT EXISTS(SELECT * FROM dbo.YourTableName WHERE YourFieldName = '(your new string)')
    INSERT INTO dbo.YourTableName(YourFieldName) VALUES(your-new-string-here)

and if not, insert it - you'll just need to find a strategy how to deal with the cases where the new string being passed in did indeed already exist (ignore it, or report back an error of some sorts).

Lots of options - up to you which one works best in your scenario!

Upvotes: 1

Related Questions