Microsoft Visual C# 2010 - Adding Data to Local Database

I'm coming over from PHP and am having a hard time with storing information into my newly created local database. I'm using Microsoft Visual C# 2010 to help me learn and develop.

I'm reading that many people do not like datasets and would opt to ignore them all together. That is fine if I am able to hard-wire into my local database. (I did not use the server database option 开发者_Go百科provided because I'll turn my completed product into a commercial solution and this will require the users to store their information into a local database that stores their project data.

I've made a video showing my windows form and my database, and the extent of my knowledge so far. Maybe you guys can help? http://screencast.com/t/x9Qt1NtOgo6X

There are many ways to access a database from your application. These range from low-level ado.net commands (SqlDataReader, etc..) to using an Object Relational Mapper (ORM) such as Entity Framework.

All of them will require that you learn the technologies, but you can start here:

http://windowsclient.net/learn/videos.aspx

Here's some code that uses SQLServer to do a direct insert, although you'll need a connection string to your database.

Include the SQL server database includes.

using System.Data.SqlClient; 
using System.Data.SqlTypes;

. . .

using (SqlConnection cn = new SqlConnection("XXXXX")) // must put a connection string to your database here
{
    cn.Open();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO Session(field1, field2) VALUES(@Value1, @Value2)"))
    {
        cmd.Parameters.AddWithValue("@Value1", 4);
        cmd.Parameters.AddWithValue("@Value2", "test");
        cmd.ExecuteNonQuery();
    }
}

Well, if you want a quick, almost close to the wire code like the way you used to have with PHP, the following code should work.

var conn = new SqlConnection("Your Connection String");
var command = conn.CreateCommand();
command.CommandText = "insert into sessions (id, name) values (@id, @name)";
command.Parameters.AddWithValue("@id", "");
command.Parameters.AddWithValue("@name", "test");
conn.Open();
command.ExecuteNonQuery();
command.Dispose();
conn.Close();

In the long run, it would be better if you get accustomed to one of the data-related / ORM frameworks such as Entity Framework, NHibernate and the likes. That would really help a lot in data manipulation and make your life a whole lot easier.

It depends on your requirments, but for most situations, I would highly recommend you use Entity Framework or Linq to Sql data classes. You'd be much better off... go with the latter as a start... hope it helps.

[Edited]

If you want to see how easy an ORM can be:

1. right-click on your project
2. select Add New Item
3. Choose Linq to Sql Data Classes
4. When you've added it, you'll have a blank .dbml file
5. Go to server explorer and add a connection to the sql db
6. Drag and drop the tables wherever you like

7. Start using the entities like this:

   using (DataClasses1DataContext db = new DataClasses1DataContext("Data Source=localhost\sqlexpress; Initial Catalog=myDBName; Integrated Security=true")) { IEnumerable citiesForUSA = db.Cities.Where(x => x.Country.Name == "United States");

   City city = new City();
   city.Name = "Metropolis";
   //etc
   db.Cities.InsertOnSubmit(city);
   db.SubmitChanges(); // <-- INSERT INTO completed
   
   //etc
   

   }

Good luck!

:-)