Fill Dataset with SqlDataAdapter and SQL query
First step to fill a dataset is to define the sql connection. The method below uses the definition in the web.config fie which looks like this:
<connectionStrings>
<clear />
<add name="LocalSqlServer" connectionString="Server=192.169.1.1;Initial Catalog=DatabaseName;User ID=username;Password=password;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Once you have this defined in the config file then call on it by setting a string variable equal to the connectionstring.
String strCon1 = System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer"].
ConnectionString;
Define a container for this connection string.
SqlConnection conn1 = new SqlConnection(strCon1);
Open the connection.
conn1.Open();
Next we set the select statement and put it into a string variable.
string sql1 = "SELECT [ApplicationId],[UserId],[UserName],[LoweredUserName],[MobileAlias],
[IsAnonymous],[LastActivityDate]
FROM [DatabaseName].[dbo].[table]
where [IsAnonymous] = '0' order by [LastActivityDate] desc";
Ok and now the magical adapter which will allow us to grab all of the data in the query and place it into an object that we can do some cool things with. Just set the adapter variable and execute a select command on it using the two string we just defined.
SqlDataAdapter dadapter = new SqlDataAdapter();
dadapter.SelectCommand = new SqlCommand(sql1, conn1);
Now we fill the dataset with all of the returned data from the select command. A great feature with this method is that the table structure(schema) is retained in the new dataset.
DataSet dset = new DataSet();
dadapter.Fill(dset);
Now you can call on the values in the dataset by using the following syntax.
DataRow dr = dset.Tables[0].Rows[i] ;
dr["userid"].tostring();
Another usefull method is to add the data to a Datatable. I find it easier to work with datatables when you need to adjust the table values and bind them to a gridview.
Datatable dt = new Datatable
dadapter.Fill(dt);
Good luck
