Obtaining a dataset from a SQL Server database

Question

I am a newbie to C# and I need to obtain a dataset from a dummy database I have made. I'm used to coding in objective-c and using php/mysql to deal with the data insertion/extraction....

Can anyone tell me how to obtain an entire table of data from a SQL Server database? Or at least point me in the direction of a reliable defacto source?

Solution 1

You have to use provider for MSSQL - SqlDataAdapter class.

string CnStr=@"put_here_connection_string";
SqlDataAdapter adp=new SqlDataAdapter("select * from tableName",CnStr);
DataSet ds=new DataSet();
adp.Fill(ds,"TableName");

Solution 2

There are plenty of ways to do this; look at tutorials like:

Introduction to SqlClient

A typical code example to just return a data table might look like:

public static DataTable GetTable(string tableName, string connectionString)
{

  using (SqlConnection myConnection = new SqlConnection(connectionString))
  {
    using (SqlCommand myCommand = new SqlCommand(tableName))
    {
      myCommand.Connection = myConnection;
      myCommand.CommandType = CommandType.TableDirect;
      using (SqlDataReader reader = myCommand.ExecuteReader())
      {
        DataTable table = new DataTable();
        table.Load(reader);
        return table;
      }
    }

  }
}

Note the use of the using keyword. This will ensure your connection is disposed when you are finished with it.

There's example code for obtaining a DataSet here.

You can also vary how you execute your command; you can use myCommand.CommandType = CommandType.Text and set the CommandString to "SELECT * FROM myTable". You can also use CommandType.StoredProcedure and use the name of a stored procedure.

You might also want to look at abstracting all of this away using one of the many solutions available. Microsoft have the Application Data blocks, Entity Framework, and there are plenty of other alternatives.

Solution 3

This should get you started:

Here is a video what you are looking for but not up to date: http://www.asp.net/web-forms/videos/how-do-i/how-do-i-create-data-driven-web-sites

maybe you should check out the getting started section: http://www.asp.net/web-forms/videos

Another choice would be ASP.NET MVC if you have some MVC insides (Rails, PHP, etc) http://www.asp.net/mvc

This is a complete walk through application:

http://www.asp.net/mvc/tutorials/mvc-music-store

I would recommend looking at mvc and the music store example HTH

Comments

[user559142]

hmm, when adding my table name is throws an exception saying that it requires a stored procedure...

[dash]

You need to set the command type on the SelectCommand to be CommandType.Table