Transfering listviewitems into a MS SQL database

[ticktock]

Hey all,

having a little trouble transferring my listview items into a database. I have transfered all of the listview items into a List<> for use during the population of the items in my database

here is my dataaccessor class, it contains the reader writer and writer statements generator:

public class SQLDBDataAccessorContactOrganizer : IContactsOrganizerDataAccessor
    {
        private const string CONNECTION_STRING = @"Data Source=SERVER\INSTANCE;Initial Catalog=ContactList;User Id=SA;Password=SQL;";
        private const string SELECT_STATEMENT = @"SELECT * FROM ContactList";

        public void writeContactList(List<Contact> contactListing)
        {
            List<Contact> organizerContacts = new List<Contact>();
            SqlConnection connectionToContactDB = new SqlConnection(CONNECTION_STRING);

            connectionToContactDB.Open();

            SqlCommand commandLine;

            string[] statementsContainer = statementCreator(contactListing);

            for (int i = 0; i < statementsContainer.Length; i ++)
            {
                commandLine = new SqlCommand(statementsContainer[i], connectionToContactDB);
            }

            connectionToContactDB.Close();
        }

        public List<Contact> readContactList()
        {
            List<Contact> organizerContacts = new List<Contact>();
            SqlConnection connectionToContactDB = new SqlConnection(CONNECTION_STRING);
            
            connectionToContactDB.Open();

            SqlDataAdapter contactDataAccessor = new SqlDataAdapter(SELECT_STATEMENT, connectionToContactDB);
            DataSet contactDataSet = new DataSet();

            contactDataAccessor.Fill(contactDataSet);

            foreach (DataRow contactRow in contactDataSet.Tables[0].Rows)
            {
                Contact currentContactRecord = new Contact();

                currentContactRecord.lastNameSearchAssign(contactRow["Full Name"].ToString());
                currentContactRecord.firstNameSearchAssign(contactRow["Full Name"].ToString());
                currentContactRecord.middleNameSearchAssign(contactRow["Full Name"].ToString());

                currentContactRecord.CompanyName = contactRow["Company Name"].ToString();
                currentContactRecord.JobTitle = contactRow["Job Title"].ToString();
                currentContactRecord.HomeAddress = contactRow["Home Address"].ToString();

                currentContactRecord.EmailAddress = contactRow["E - Mail Address"].ToString();
                currentContactRecord.MessengerAddress = contactRow["IM Address"].ToString();

                currentContactRecord.BusinessPhoneNumber = Convert.ToInt32(contactRow["Business Phone Number"].ToString());
                currentContactRecord.HomePhoneNumber = Convert.ToInt32(contactRow["Home Phone Number"].ToString());
                currentContactRecord.FaxPhoneNumber = Convert.ToInt32(contactRow["Fax Phone Number"].ToString());
                currentContactRecord.MobilePhoneNumber = Convert.ToInt32(contactRow["Mobile Phone Number"].ToString());

                organizerContacts.Add(currentContactRecord);
            }
            connectionToContactDB.Close();

            return organizerContacts;
        }

        private string[] statementCreator(List<Contact> contactListing)
        {
            string[] sqlStatementContainer = new string [contactListing.Count];
            for (int i = 0; i < sqlStatementContainer.Length; i++)
            {
                sqlStatementContainer[i] = "INSERT INTO ContactList VALUES ('" +
                                             contactListing[i].LastName + "', '" +
                                             contactListing[i].FirstName + "', '" +
                                             contactListing[i].MiddleName + "', '" +
                                             contactListing[i].CompanyName + "', '" +
                                             contactListing[i].JobTitle + "', '" +
                                             contactListing[i].HomeAddress + "', '" +
                                             contactListing[i].EmailAddress + "', '" +
                                             contactListing[i].MessengerAddress + "', '" +
                                             contactListing[i].BusinessPhoneNumber + "', '" +
                                             contactListing[i].HomePhoneNumber + "', '" +
                                             contactListing[i].FaxPhoneNumber + "', '" +
                                             contactListing[i].MobilePhoneNumber + "')";
            }

            return sqlStatementContainer;
        }

I believe I having quite a problem with my writer function. This is my first time to use SQL with C# and have no idea what I am doing wrong. My reader function was just derived from a code that I saw in the internet about reading files from a database into a class and then into a list. Please show me how do I connect and write my data onto the database properly.

THANKYOU!

[finito]

which line are u getting your error. by glance i can tell your insert statement is wrong.

Also mention the error you are getting.

[ticktock]

there are no errors in my compilation it just does not fills up and reads from the database properly. :/

[ticktock]

there are no errors in my compilation it just does not fills up and reads from the database properly. :/

I mean it does not write properly. I just got the reader to work just now.

[kvprajapati]

You could use String.Format method.

string str=string.Format("INSERT INTO ContactList VALUES ('{0}','{1}','{2}')",                                             contactListing[i].LastName,contactListing[i].FirstName,contactListing[i].MiddleName)

[ticktock]

@ adatapost:

Thanks for the heads up! Will try that on my program now. I may get to see now if the SQL insert statements was the one that hindered me from adding new data into my database.

[ticktock]

You could use String.Format method.

string str=string.Format("INSERT INTO ContactList VALUES ('{0}','{1}','{2}')",                                             contactListing[i].LastName,contactListing[i].FirstName,contactListing[i].MiddleName)

Tried it just now, couldn't get it to work. I keep on getting an "Input string was not in correct format" error. I followed exactly the syntax here and it still gets errors. Here is my code:

private string[] statementCreator(List<Contact> contactListing)
        {
            string[] sqlStatementContainer = new string [contactListing.Count];
            for (int i = 0; i < sqlStatementContainer.Length; i++)
            {
                string nameContainer = contactListing[i].LastName + ", " + contactListing[i].FirstName + " " + contactListing[i].MiddleName;
                string stringFormat =
                string.Format
                (
                    "INSERT INTO ContactList VALUES ('{0}', '{{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}')",
                    
                    nameContainer, 
                    contactListing[i].CompanyName, 
                    contactListing[i].JobTitle, 
                    contactListing[i].HomeAddress,
                    contactListing[i].EmailAddress,
                    contactListing[i].MessengerAddress,
                    contactListing[i].BusinessPhoneNumber.ToString(),
                    contactListing[i].HomePhoneNumber.ToString(),
                    contactListing[i].FaxPhoneNumber.ToString(),
                    contactListing[i].MobilePhoneNumber.ToString()
                );

                sqlStatementContainer[i] = stringFormat;
            }

            return sqlStatementContainer;
        }

[Ketsuekiame]

Your insert statement is incorrectly formed.

You need to declare what columns are to be filled before telling it the data.

The w3Schools site will give you a lot of help with SQL Syntax

[ticktock]

Your insert statement is incorrectly formed.

You need to declare what columns are to be filled before telling it the data.

The w3Schools site will give you a lot of help with SQL Syntax

Thanks for the link but based on the tutorial at w3cschools (which I already read before I created this thread), the syntax I am following is this one:

INSERT INTO Persons
VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

So what could be wrong with my insert statement? As I understand this statement will fill up all the columns in the table with respect to the data I placed on my VALUES statement. Man I am really stumped.

[Ketsuekiame]

Ah, I didn't think MS SQL supported an empty column list. It appears it does, provided you get the order correct and it has an identity column.

The second point is that after you have created your command, you need to execute it.

On line 19, you create the command. But then it doesn't do anything with it =)

[finito]

you are forgetting the + "', '" + like in your original statement.

[ticktock]

Ah, I didn't think MS SQL supported an empty column list. It appears it does, provided you get the order correct and it has an identity column.

The second point is that after you have created your command, you need to execute it.

On line 19, you create the command. But then it doesn't do anything with it =)

so how should I do it? how should I add the contacts in the database? I just literally made a shot in the dark with that statement because I have no idea what so ever.

[Ketsuekiame]

Go intellisense fishing! :D

(Or look up SqlCommand in the MSDN)

[ticktock]

Go intellisense fishing! :D

(Or look up SqlCommand in the MSDN)

just skimmed through this page: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx

I ask, of the following methods, which should I use? I am having a gut instinct that I should use BeginExecuteNonQuery.

[Ketsuekiame]

You're almost correct.

The methods that start with "Begin" are Asynchronous methods which require you to know a little about threading and how to handle asynchronous queries.

For this application I recommend you just the plain ExecuteNonQuery(); This version is not asynchronous, meaning it "blocks" the code from executing until it's finished. (Which is probably what you want in this case)

Using Asynchronous methods can be quite complex and confusing at first. So I suggest writing a few small apps and read up in the MSDN before beginning to experiment with Asynchronous methods.

[ticktock]

You're almost correct.

The methods that start with "Begin" are Asynchronous methods which require you to know a little about threading and how to handle asynchronous queries.

For this application I recommend you just the plain ExecuteNonQuery(); This version is not asynchronous, meaning it "blocks" the code from executing until it's finished. (Which is probably what you want in this case)

Using Asynchronous methods can be quite complex and confusing at first. So I suggest writing a few small apps and read up in the MSDN before beginning to experiment with Asynchronous methods.

I see, thank you for that. I ask now, why should I instantiate an object of type SqlDataReader after I instantiate an object of type sqlcommand? This is what it says on the tutorial at MSDN but I have no idea why I should be doing that. Can you please explain it to me?

[Ketsuekiame]

Well an SqlDataReader is just that. It reads data from the SQL Connection.

To send data to the server, you don't need an SqlDataReader as there is nothing to read (such as when you do an INSERT/UPDATE statement). But when you do something like a SELECT statement, you need the SqlDataReader to read the result. You get the instance back when you call ExecuteReader();

SqlCommand myCommand = new SqlCommand("SELECT * FROM MyTable", sqlDbConnection);
SqlDataReader myDataReader = myCommand.ExecuteReader();
while(myDataReader.Read()) // This line will go through each "row" of results
{
   String myData = myDataReader.GetString("MyColumnName"); // Fetches the string stored in "MyColumnName" column
}

[ticktock]

Well an SqlDataReader is just that. It reads data from the SQL Connection.

To send data to the server, you don't need an SqlDataReader as there is nothing to read (such as when you do an INSERT/UPDATE statement). But when you do something like a SELECT statement, you need the SqlDataReader to read the result. You get the instance back when you call ExecuteReader();

SqlCommand myCommand = new SqlCommand("SELECT * FROM MyTable", sqlDbConnection);
SqlDataReader myDataReader = myCommand.ExecuteReader();
while(myDataReader.Read()) // This line will go through each "row" of results
{
   String myData = myDataReader.GetString("MyColumnName"); // Fetches the string stored in "MyColumnName" column
}

Ahh, thanks for the explanation. So in my case I won't be needing that for data writing then. So it's just the sqlcommand object in which I will use to access the ExecuteNonQuery method is it not? so it will be something like:

List<Contact> organizerContacts = new List<Contact>();
            SqlConnection connectionToContactDB = new SqlConnection(CONNECTION_STRING);

            connectionToContactDB.Open();

            SqlCommand commandLine;

            string[] statementsContainer = statementCreator(contactListing);

            for (int i = 0; i < statementsContainer.Length; i ++)
            {
                commandLine = new SqlCommand(statementsContainer[i], connectionToContactDB);
				commandLine.ExecuteNonQuery();
            }

            connectionToContactDB.Close();

I just added the executenonquery part there. I haven't tested it yet because I am working on a different part of the app, but is my work correct?

[ticktock]

OK it's just now that I tried my statement above and it works now, it places them all to the database, but I have come across a new problem. How do I overwrite the whole table? I mean whenever I load data from it and add or edit information it always appends my data with the previous data.

I know that I will be using something like:

DELETE * FROM table_name

but I do not know how to implement it in my program.

I have anticipated this but have no measures on how to work against it. Help will surely be appreciated.

[Ketsuekiame]

What you're asking is now "complex" behaviour. You shouldn't empty the table and then repopulate it. That's BAD. (Seriously, think about doing this with 10,000 rows of data...)

You now need to work out if something has been update and update that row where necessary, inserting new rows where necessary also.

Check out the UPDATE SQL Command.

If you insist you must only do it that way, then remember what I've told you before ^^

[ticktock]

What you're asking is now "complex" behaviour. You shouldn't empty the table and then repopulate it. That's BAD. (Seriously, think about doing this with 10,000 rows of data...)

You now need to work out if something has been update and update that row where necessary, inserting new rows where necessary also.

Check out the UPDATE SQL Command.

If you insist you must only do it that way, then remember what I've told you before ^^

Haha, I thought so. I actually thought about that 10,000 rows of data thingy, but I was looking for a quick fix for this since I know zilch when it comes to database manipulation via c# programming.

[ticktock]

Okay, so I just stuck with using the delete the whole table statement because I really couldn't figure out for now how I can check and update values in my database with reference to my listview. I sort of have the algorithm in the tip of my tongue but I am having quite a difficult time producing it.

So what I plan to do with this algorithm is:

1) read the files on the database using a for each loop
2) store the values from the for each loop to a temporary container of the same class as that of the values in my database.
3) I guess find the item similar to my item in the list view and check if the values were changed.
4) If it were delete the row and replace it with the new one.

But the part where I am having quite trouble is in number 3. Since I have not placed any control number for each of my listview items I cannot just search for them easily in my database and listview. And when the time comes that 2 items in the listview are of the exact same contents I'm screwed. Haha.

So the only solution that I found was to delete the whole table and repopulate it. Not the best idea but it works. This is only a contact organizer app that is posed as a laboratory assignment from my school. And even though I say that the delete the whole table thingy works perfectly for my case, I am not impressed by my approach.

[Ketsuekiame]

You need to look at your database design.

There should never be a case where you cannot identify between two different rows.

Things you need to look at are Identity Columns and Primary Keys. These will help differentiate between your rows and will eventually become your basis for searching through tables in the fastest possible manner.

When using ListView I imagine you're populating it with ListViewItem classes? The ListViewItem class has a property called ListViewItem.Tag . You can set the tag so any data you wish as it takes an Object property. My only recommendation is that if you decide to populate it, always use the same data type. (Int32 or String or MyClass but never a combination)

You can cast to and from object to get your data back.

For your app (and the interest of getting your assignment completed on time) I recommend looking up how to use Identity and Primary Keys in your database first. Although it's not an ideal solution, emptying and re-populating the database will work.
Once you have that working, save the source and binaries and copy your project. Then start using the ListViewItem to search through your database for the correct fields to update. At least this way if you can't figure it out in time, you still have a working program to hand in.

EDIT: Your algorithm is fine with the exception of number 4, you can use UPDATE instead of DELETE unless of course, you actually want to remove the row.

[ticktock]

Isn't it not that the primary key is a unique identifier for each tuple?

If I correctly understand what you explained, I should use that listviewitem.tag method to place tags of type int to my items, so that I won't have to add another class member that will serve as my unique "id" for each object, and this listviewitem.tag will serve as my primary key. Did I understand you correctly? I do not quite understand your explanation for my usage of the listviewitem.tag method, sorry.

Although if I did, then does that mean whenever I save my items unto the database I should now add another column in which the id will be stored, right?

To answer your questions, yes I am using the listviewitem class to populate my listview. And all of my class members are of type string, even the telephone numbers and such.

Haha, the ironic thing is my assignment was due just last monday haha, it is now late for 2 days and if my calculations are correct I already have 20 points deduction. But this does not quite worry me because I have answered the bonus "requirement" for this exercise and my professor can just deduct my score from that haha.

I just plan to pass a complete and almost perfect work so that I have a good enough reason for passing it late. Although I plan on passing it before 12 midnight here in my side of the world, I would still like to learn about how to deal with this problem so that in the future I won't be panicking like apeshit like I have been doing for the past days haha.

EDIT:

Oh and here is my "temporary" write to database method:

#region Method For Writing The Contact List To A MS SQL DATABASE TABLE
        private const string DELETE_STATEMENT = @"DELETE FROM ContactList";         
        public void writeContactList(List<Contact> contactListing)
        {
            List<Contact> organizerContacts = new List<Contact>();
            SqlConnection connectionToContactDB = new SqlConnection(CONNECTION_STRING);
            
            connectionToContactDB.Open();

            SqlCommand commandLine;

            commandLine = new SqlCommand(DELETE_STATEMENT, connectionToContactDB);
            commandLine.ExecuteNonQuery();

            string[] statementsContainer = insertStatementCreator(contactListing);

            for (int i = 0; i < statementsContainer.Length; i++)
            {
                commandLine = new SqlCommand(statementsContainer[i], connectionToContactDB);
                commandLine.ExecuteNonQuery();
            }

            connectionToContactDB.Close();
        } 
        #endregion

[Ketsuekiame]

Yes you understood me correctly =)

the Primary Key is indeed unique and should be used to identify each unique row in the table.

So even if you have two rows of identical data, the primary key will be different. Using the primary key, you can select the correct row.

You can do this using the WHERE keyword. SELECT * FROM MyTable WHERE Id = 12; would select all the columns from the table, but only the row where the Id is 12. The Primary Key can be called whatever you want, I just chose Id for that example.

Same goes for UPDATE and DELETE. UPDATE MyTable SET Name = "Bob" WHERE Id = 12 would update the row that has an Id of 12 and set the Name column to "Bob". DELETE FROM MyTable WHERE Id = 12 would delete the row with Id 12 from the table.

So you can do something akin to this;

{
   ... // Code that selects the ListViewItem
   Int32 id = (Int32)lsvItem.Tag;
   String name = lsvItem.SubItems[0];
   String myQuery = String.Format("UPDATE MyTable SET Name = '{0}' WHERE Id = {1}", name, id);
   SqlCommand myCommand = new SqlCommand(myQuery, myDbConnection);
   myCommand.ExecuteNonQuery();
}

Hope that helps.

[ticktock]

Yes you understood me correctly =)

the Primary Key is indeed unique and should be used to identify each unique row in the table.

So even if you have two rows of identical data, the primary key will be different. Using the primary key, you can select the correct row.

You can do this using the WHERE keyword. SELECT * FROM MyTable WHERE Id = 12; would select all the columns from the table, but only the row where the Id is 12. The Primary Key can be called whatever you want, I just chose Id for that example.

Same goes for UPDATE and DELETE. UPDATE MyTable SET Name = "Bob" WHERE Id = 12 would update the row that has an Id of 12 and set the Name column to "Bob". DELETE FROM MyTable WHERE Id = 12 would delete the row with Id 12 from the table.

So you can do something akin to this;

{
   ... // Code that selects the ListViewItem
   Int32 id = (Int32)lsvItem.Tag;
   String name = lsvItem.SubItems[0];
   String myQuery = String.Format("UPDATE MyTable SET Name = '{0}' WHERE Id = {1}", name, id);
   SqlCommand myCommand = new SqlCommand(myQuery, myDbConnection);
   myCommand.ExecuteNonQuery();
}

Hope that helps.

Thank you very much for your suggestion! I will try this immediately during the weekend! I passed my activity by the way, the teacher accepted it even though it was late, don't know what score I will garner though. But I am still hoping for the best.

[ticktock]

Hey, Ketsuekiame

just wanted to let you know that I tried your suggestion on a different program of mine and it works! I have now learned how to update. But I am having a little trouble with updating, please see my code below:

string updateStatement = "UPDATE Students " +
                                     "SET [LastName] = ?, [FirstName] = ?, [MiddleName] = ? " +
                                     "WHERE [StudentID] = ?";
            OleDbConnection oledbGate = new OleDbConnection(OLEDB_CONNECTION_STRING);

            OleDbCommand commandLine = new OleDbCommand(updateStatement, oledbGate);

            commandLine.Parameters.Add("LastName", OleDbType.VarWChar, 255).Value = currentStudent.StudentLastName;
            commandLine.Parameters.Add("FirstName", OleDbType.VarWChar, 255).Value = currentStudent.StudentFirstName;
            commandLine.Parameters.Add("MiddleName", OleDbType.VarWChar, 255).Value = currentStudent.StudentMiddleName;
            commandLine.Parameters.Add("StudentID", OleDbType.BigInt, 64).Value = currentStudent.StudentID;

            oledbGate.Open();
            commandLine.ExecuteNonQuery();
            oledbGate.Close();

Please take note of my commandLine string, I placed [] on my columns to avoid any confusion for whatever reserved words there and to just plainly let my statement run without having any statement errors.

As I have understood in a forum post that I read, when you put [] on your column names it makes the statement reader disregard any spaces and hinders it from being confused with reserved words (correct me if I am wrong though). So as a practice I put [] on my column names to avoid any hassle during coding. But in the case of my update statement when I put them [] in my column names it wouldn't update my database.

Yes it runs and does not crash at any point during the program but it won't update my data. But after I removed the [] it now updates my data. So now I ask why is this?

I have used the [] only in my insert and select statements and this is the first time I have used update in any platform for that matter so I maybe missing something here.

[finito]

Update statement is wrong.

Update Students Set (LastName, FirstName, MiddleName) Values (@LastName, @FirstName, @MiddleName) Where WHERE StudentID = @StudentID

Also

commandLine.Parameters.Add("[B]@[/B]LastName", OleDbType.VarWChar, 255).Value = currentStudent.StudentLastName;

[ticktock]

Update statement is wrong.

Update Students Set (LastName, FirstName, MiddleName) Values (@LastName, @FirstName, @MiddleName) Where WHERE StudentID = @StudentID

Also

commandLine.Parameters.Add("[B]@[/B]LastName", OleDbType.VarWChar, 255).Value = currentStudent.StudentLastName;

Thank you for your reply. How is my statement wrong? Is it only just, in some part syntactically wrong? My statement works fine when updating and I was only bothered with the fact that I couldn't use the brackets for separating my column names from reserved words or other hassles in access.

I based my update statement from the update statement tutorial at w3schools. As I can see our statements are only different when it comes to setting the values. You used the

Set(column1, column2, etc) Values (value1, value2)

while I only used

Set columnname1 = value1, columnname2 = value2

And what is the use of the @ symbol beside the value names? Is it to let the compiler know that when using the parameter.add method that the .value will replace that text with the @ symbol inside my commandtext?

[finito]

sorry I assumed it wasn't working I didn't read your post.

Yes it runs and does not crash at any point during the program but it won't update my data. But after I removed the [] it now updates my data. So now I ask why is this?

[] is only for reserved names. i.e. SELECT, WHEN, WHERE, DELETE, DATE, MONTH, THEN, ELSE, END, etc.

And what is the use of the @ symbol beside the value names? Is it to let the compiler know that when using the parameter.add method that the .value will replace that text with the @ symbol inside my commandtext?

From what I understand yes.