how to store value in sql server table after comparing with local string value c#

Question

Here is my code...which gives a sorted (descending) list of cosine similarity values of different queries with a document.

   var dd = new List<Tuple<string, double, string>>();

            while ((line = file.ReadLine()) != null)
            {
                dd.Add(Tuple.Create(line, p.calculate_CS(line, document), document));
            }

            var top_value = dd.OrderByDescending(x => x.Item2).FirstOrDefault();

            if (top_value != null)
            { 
                // look up record using top_value.Item3, and then store top_value.Item1
                var abstrct = top_value.Item3.ToString();
                var r_field = top_value.Item1.ToString();

                write_To_Database(abstrct, r_field);
            }

This is the data insertion method......

static void write_To_Database(string document, string research_field)
        {
            try
            {
                SqlConnection con = new SqlConnection("Data Source=KHIZER;Initial Catalog=subset_aminer;Integrated Security=True;");

                con.Open();
                SqlCommand query = con.CreateCommand();
                query.CommandText = "select id from sub_aminer_paper where pid between 1 and 500 and DATALENGTH(p_abstract) != 0 and p_abstract = " + document;

                SqlDataReader reader = query.ExecuteReader();


                reader.Read();
                int id = reader.GetInt32(0);
                //reader.Close();

                query.Parameters.Add("@research_field", SqlDbType.Text);
                query.Parameters.Add("@id", SqlDbType.Int);

                query.CommandText = "insert into sub_aminer_paper (research_area) values(@research_field) where id = @id";
                query.ExecuteNonQuery();


                con.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception: " + e.Message);
            }
            finally
            {
                Console.WriteLine("Executing finally block.");

            }
        }

I want to store the value of top (highest cosine similarity value) query in database against the string document which is taken from the same table... Table structure is as follows...

--------------------------------------------------------------------------------
id | pid | p_title | p_author | p_year | p_venue | p_abstract | research_area
--------------------------------------------------------------------------------

string document is p_abstract and query is to be saved in the column research_area

Any suggestions will be highly appreciated.

this is too broad. You need to setup a server, a database then choose how you will access it from code... — yohannist, Jun 25 '15 at 05:45
When you say not at all, do you mean to say the tasks aren't broad or that i misunderstood your aim? — yohannist, Jun 25 '15 at 06:03
As in the code if you see, d is a List of type Tuple and it stores the research_areas (i.e. string) and similarity values (i.e. double) in descending order. I want to store the highest similarity valued research_area in the list 'd' in the database against the same document as in the code — maliks, Jun 25 '15 at 06:05
There's not a single line in your code that deals with the DB. Do you expect us to write such code for you? There are numerous examples online to help you in your endeavor; look into those and apply one and return here when you have an actual **problem**. — BCdotWEB, Jun 25 '15 at 08:17
I have updated my code @BCdotWEB, now you can re-check it..... — maliks, Jun 25 '15 at 18:25

score 0 · Answer 1 · edited May 23 '17 at 12:29

First a bit of code review:

Don't use underscores in names: write_To_Database, r_field, research_field, top_value, etc.
Don't abbreviate: r_field, abstrct.
SqlConnection, SqlCommand, etc. should always be encapsulated in a using so they get properly disposed of.
Don't do this: p_abstract = " + document;, always work with parameters. Otherwise you risk SQL injection.
Why do you do a SELECT first and an INSERT after? Why not simply add that WHERE to your INSERT?
Why are your names not consistent: research_area vs research_field, document vs abstrct vs p_abstract?

Now here's your main problem: you do query.Parameters.Add yet nowhere do you add the value. It's as simple as this: cmd.Parameters.Add("@research_field", SqlDbType.Text).Value = research_field;.

However, I'm a bit baffled by the logic of your code: first you go looking for an ID of a record, and then you insert a record with that ID? I find this highly suspect, since I would assume that the ID needs to be unique.

exception timeout expired the time period elapsed prior to completion of the operation or the server is not responding." this exception is displaying at console. Debugging is working fine but no data insertion in database — maliks, Jun 26 '15 at 10:04

how to store value in sql server table after comparing with local string value c#

1 Answers1