0

This method retrieves the entire data. I'm trying to switch this method to a method that uses a dictionary but it doesn't work very well. INSERT, UPDATE, and DELETE completed but I'm having trouble with SELECT.

I want convert to the my method like below link source. http://gist.github.com/thorsman99/e788dd9cce36c26edd9076c9dac288dd

public static List<TestModel> GetList(string id, string subject, string UseYN, string createDate1, string createDate2)
    {
        using(SQLiteConnection connection = new SQLiteConnection(_connection))
        {
            connection.Open();

            using(SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText = @"SELECT ID, Subject,  CreateDate, UpdateDate FROM Test";

                command.Parameters.Add(new SQLiteParameter(@"ID"         , DbType.String) { Value = id          });
                command.Parameters.Add(new SQLiteParameter(@"Subject"    , DbType.String) { Value = subject     });
                command.Parameters.Add(new SQLiteParameter(@"CreateDate1", DbType.String) { Value = createDate1 });
                command.Parameters.Add(new SQLiteParameter(@"CreateDate2", DbType.String) { Value = createDate2 });

                SQLiteDataReader reader = command.ExecuteReader();

                List<TestModel> list = new List<TestModel>();

                while(reader.Read())
                {
                    TestModel item = new TestModel();

                    item.ID         =                    reader["ID"        ].ToString();
                    item.Subject    =                    reader["Subject"   ].ToString();
                    item.CreateDate = Convert.ToDateTime(reader["CreateDate"]);
                    item.UpdateDate = Convert.ToDateTime(reader["UpdateDate"]);

                    list.Add(item);
                }

                return list;
            }
        }
    }
  • What would you like the dictionary to contain? – Zohar Peled Nov 7 at 12:37
  • 4
    Save yourself from SQL injection and how by using dapper prevents this from happening; I bet there's no facts that support your case. The user is using parameters to prevent this, could you explain? On another note, where is your Where statement? If your're doing a select query you more than likely need one, based on some kind of id. Then your results could fill a table and or use the reader to get the columns you want... – Çöđěxěŕ Nov 7 at 12:40
  • 2
    @HariHaran Where do you see the possibility of SQL injection here? – Thomas Schremser Nov 7 at 12:40
  • you mean like var dict = list.ToDictionary(x => x.ID, y => y); ? – Innat3 Nov 7 at 12:44
  • On another note, switch this method to a method that uses a dictionary, if that's the case create a dictionary and change your return type? Currently, you have it as a list in which your adding items (TestModel ); why if you don't want that type? For example: Dictionary<int, TestModel> and in your routine create one to add to: Dictionary<int, TestModel> dictReturn = new Dictionary<int, TestModel>();. Now the question, why a Dictionary instead of a List<TestModel>? – Çöđěxěŕ Nov 7 at 12:48
0

I moved connection.Open to directly before the.ExecuteReader` Connections should be open for the shortest possible time. I moved the declare of the Dictionary outside the using block and then the return outside also. This again is to close the connection as soon as possible.

I combined the 2 using blocks to one. Just simplifies the code a bit and saves indenting.

For the dictionary, I used "ID" as the key. I assumed that this was the Primary Key and therefore unique.

        public Dictionary<string, TestModel> GetTestModelDictionary(string id, string subject, string UseYN, string createDate1, string createDate2)
        {
            Dictionary<string, TestModel> dict = new Dictionary<string, TestModel>();
            using (SQLiteConnection connection = new SQLiteConnection(_connection))
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText = @"SELECT ID, Subject,  CreateDate, UpdateDate FROM Test";

                command.Parameters.Add(new SQLiteParameter(@"ID", DbType.String) { Value = id });
                command.Parameters.Add(new SQLiteParameter(@"Subject", DbType.String) { Value = subject });
                command.Parameters.Add(new SQLiteParameter(@"CreateDate1", DbType.String) { Value = createDate1 });
                command.Parameters.Add(new SQLiteParameter(@"CreateDate2", DbType.String) { Value = createDate2 });
                connection.Open();
                SQLiteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    TestModel item = new TestModel();

                    item.ID = reader["ID"].ToString();
                    item.Subject = reader["Subject"].ToString();
                    item.CreateDate = Convert.ToDateTime(reader["CreateDate"]);
                    item.UpdateDate = Convert.ToDateTime(reader["UpdateDate"]);

                    dict.Add(item.ID, item);
                }
            }
            return dict;
        }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.