Newbie help with TableAdapters

[techturtle]

I’m fairly new to C#, especially with databases. I’ve worked in VBA and Delphi doing databases, so I was hoping to just use this like a RecordSet or a Tcursor, but so far it’s not panning out like that.

I have a SQL database added as a dataset in my project (Windows forms). For a given table (“HTC”) I have a TableAdapter set up, and it includes 2 custom queries. One gets the active record and the other gets the cancelled records. Both of these queries have a parameter so they only pull for a single account (“GPN”). Right now I have the TableAdapter filling like this:

this.htcActiveTableAdapter.ActiveFillBy(cramdDataSet.htc, gpn);
this.htcCancelledTableAdapter.CancelledFillBy(cramdDataSet.htc, gpn);

After that, I’m not sure where to go. I need to get it to a state where I can count the number of records returned as well as display some of the data in textboxes on the form. Eventually, the plan is to allow editing the data and sending it back to the database, but for now I’m trying to just get the displaying part done. I’ve tried following along with various tutorials I’ve found online, but I have been unable to figure out how to alter their code so it works with my setup. They also usually deal with bound controls, which I don’t have. Maybe binding the controls is the way to go, but I don’t know what that would look like either.

Sorry if this sounds like a “gimme code” request. It’s really not; I’m just looking for a push in the right direction. I’m also all for just being given a link that you think explains how I need to do this (I’ve read so many over the last week or so I’ve been working on this, so don’t be surprised if I’ve already read the one you attach :))

[mcriscolo]

Have you seen this article? (Not sure what the goofy photos are all about). Seems to cover some of the basic aspects of working with table adapters. It's in VB.NET, but should be easily translated to C#.

[abelLazm]

Following is a very simple code to load data from database to datagridview. Manipulating data in deatagridview is much more easier than any other control.

private static SqlConnection Conn = new SqlConnection();
private static SqlDataAdapter Adapter1;
private static SqlCommand Command1 = new SqlCommand();
Conn.ConnectionString = "Data Source=your_database_server; Initial Catalog=DatabaseName;Integrated Security=true";
Conn.Open();
Command1.Parameters.Clear();
Command1.Connection = Conn;
Command1.CommandText = "Select * from TableName";
Command1.CommandType = CommandType.Text;
Command1.CommandTimeout = 0;
Adapter1 = new SqlDataAdapter(DbCommand);
Adapter1.Fill(dataset1, TableName);
dataGridView1.DataSource = null;
dataGridView1.DataSource = dataset1.Tables[0];

[techturtle]

Following is a very simple code to load data from database to datagridview. Manipulating data in deatagridview is much more easier than any other control.

private static SqlConnection Conn = new SqlConnection();
private static SqlDataAdapter Adapter1;
private static SqlCommand Command1 = new SqlCommand();
Conn.ConnectionString = "Data Source=your_database_server; Initial Catalog=DatabaseName;Integrated Security=true";
Conn.Open();
Command1.Parameters.Clear();
Command1.Connection = Conn;
Command1.CommandText = "Select * from TableName";
Command1.CommandType = CommandType.Text;
Command1.CommandTimeout = 0;
Adapter1 = new SqlDataAdapter(DbCommand);
Adapter1.Fill(dataset1, TableName);
dataGridView1.DataSource = null;
dataGridView1.DataSource = dataset1.Tables[0];

Long story short, but in this case a datagridview will not work. There is another part of my program where I am using a datadrigview, and I agree, they are much easier to work with. Thank you for the code. As I am still learning this, it's nice to see some of the variations to how things can be done so I can figure out best ways to do things for the future. For the record, here is the code that I used (since it is different than yours):

SqlDataAdapter dataAdapter = new SqlDataAdapter(query, myConnectionString);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

DataTable table = new DataTable();
dataAdapter.Fill(table);
searchBindingSource.DataSource = table;
ResultsSearchGrid.DataSource = searchBindingSource;
ResultsSearchGrid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);

query is a string variable passed in that contains the SQL query for the data I want. myConnectionString is a form-level string variable that contains the database's connection string values, set elsewhere in the program.

[techturtle]

Have you seen this article? (Not sure what the goofy photos are all about). Seems to cover some of the basic aspects of working with table adapters. It's in VB.NET, but should be easily translated to C#.

Thanks for the link. Yeah, weird pics there. While it was informative, most of what it covered was not related to what I was trying to do, unfortunately. I did manage to find a video tutorial (still VB) that covered a little closer to what I wanted to do: http://windowsclient.net/learn/video.aspx?v=30533 Between that and using a VB-to-C# converter (here) I managed to make it do at least the first part of what I needed. I set up a dataset in my project that connects to the database I am using. Within the built-in table adapters, I added a few custom queries that allow me to filter the results using a combination of hard-coded and run-time parameters. Then using the datasets and tableadapters, I came up with the following code:

/***** Setup bindingsource using the custom queries *****/
htcActiveBindingSource.DataSource = this.htcActiveTableAdapter.ActiveGetDataBy(gpn);

/***** Count rows to make sure data was returned *****/
if (htcActiveBindingSource.Count >= 1)
	ActiveHtcPanel.Visible = true;
else 
	ActiveHtcPanel.Visible = false;

/***** Fill in the form data by setting and reading from a row object *****/
cramdDataSet.htcRow htcActiveRow = default(cramdDataSet.htcRow);
htcActiveRow = (cramdDataSet.htcRow)((DataRowView)this.htcActiveBindingSource.Current).Row;
DateHtcSignedActiveHtcDatepicker.Value = htcActiveRow.datehtcsigned;
Owner1ActiveHtcTextbox.Text = htcActiveRow.owner1;
// and so on...

This manages to do the main things I needed right now, namely count the rows returned and put specific field data from a given row into specific controls on the form. It seems a rather unwieldy way to do it, plus I don't fully understand why it works the way it does. I don't know... it seems weird to me that the counting is done in the bindingsource, and that to advance rows (to search for a specific row) I have to do a bindingsounce.MoveNext() then reassign the "current" row in the binding source to my row variable. Can anyone weigh in on if there is a better way to do any of this? I'm going to leave the thread "unsolved" for a few more days in hopes that someone can shed some additional light on this code. Thanks!