How to use data adapters in .net

K_Joe · February 12th, 2014, 12:08 PM

Im trying to make a duplicate page of the Genres.aspx page in the Planet Wrox site from the Management folder, where we are able to Update, Delete, and Insert into the Genres table.

I have the Html set up the same way as the Genres.aspx except without the sqlDataSource of course.

My question is to which event do I connect each operation to, and how to do them

my select statement for outputting is this:

SqlConnection myConnection = new SqlConnection();
SqlCommand myCommand = new SqlCommand();
SqlDataAdapter myAdapter = new SqlDataAdapter();
DataTable myTable = new DataTable();

myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["PlanetWroxConnectionString1"].ConnectionString;
myCommand.CommandText = "SELECT * FROM Genre";
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myAdapter.SelectCommand = myCommand;
myAdapter.Fill(myTable);

GridView1.DataSource = myTable.DefaultView;
GridView1.DataBind();

myAdapter.Dispose();
myCommand.Dispose();
myConnection.Dispose();

Imar · February 15th, 2014, 10:47 AM

Hi there,

You can hook into events such as Inserting and Updating to set the relevant data. Alternatively, you can look into Model Binding: http://blogs.msdn.com/b/webdev/archi...web-forms.aspx (which requires recent versions of .NET and Visual Studio).

Cheers,

Imar

K_Joe · February 16th, 2014, 05:30 PM

I understand that my question how does the event handler need to be written I had a sql data adapter built with the event handlers with the following code:

public SqlDataAdapter crud()
{
SqlConnection connect = new SqlConnection();
connect.ConnectionString = ConfigurationManager.ConnectionStrings["PlanetWroxConnectionString1"].ConnectionString;
SqlDataAdapter adapt = new SqlDataAdapter();
SqlCommand command = new SqlCommand();

command = new SqlCommand("INSERT INTO Genre ([Name], [SortOrder]) VALUES ( @Name, @SortOrder)", connect);
command.Parameters.Add("@Name", SqlDbType.NVarChar, 200, "Name");
command.Parameters.Add("@SortOrder", SqlDbType.Int, 32, "SortOrder");
adapt.InsertCommand = command;

command = new SqlCommand("UPDATE Genre SET Name = @Name, SortOrder = @SortOrder WHERE Id = @oldId", connect);
command.Parameters.Add("@Id", SqlDbType.Int, 32, "Id");
command.Parameters.Add("@Name", SqlDbType.NVarChar, 200, "Name");
command.Parameters.Add("@SortOrder", SqlDbType.Int, 32, "SortOrder");
SqlParameter para = command.Parameters.Add("@oldId", SqlDbType.Int, 32, "Id");
para.SourceVersion = DataRowVersion.Original;
adapt.UpdateCommand = command;

command = new SqlCommand("DELETE FROM Genre WHERE Id = @Id", connect);
para = command.Parameters.Add("@Id", SqlDbType.Int, 32, "Id");
para.SourceVersion = DataRowVersion.Original;
adapt.DeleteCommand = command;

return adapt;
}

protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{
string comstr = ConfigurationManager.ConnectionStrings["PlanetWroxConnectionString1"].ConnectionString;
using (SqlConnection con = new SqlConnection(comstr))
{
using (SqlCommand cmd = new SqlCommand())
{

}
}

function1();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
SqlConnection connect = new SqlConnection();
connect.ConnectionString = ConfigurationManager.ConnectionStrings["PlanetWroxConnectionString1"].ConnectionString;

}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
SqlConnection connect = new SqlConnection();
connect.ConnectionString = ConfigurationManager.ConnectionStrings["PlanetWroxConnectionString1"].ConnectionString;

}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
SqlConnection connect = new SqlConnection();
connect.ConnectionString = ConfigurationManager.ConnectionStrings["PlanetWroxConnectionString1"].ConnectionString;

}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
SqlConnection connect = new SqlConnection();
connect.ConnectionString = ConfigurationManager.ConnectionStrings["PlanetWroxConnectionString1"].ConnectionString;

}

Imar · February 17th, 2014, 04:47 AM

I am not sure I understand what you are asking then. Can you elaborate? Did you look into model binding?

Imar

K_Joe · February 17th, 2014, 11:15 AM

I did, though they don't use data adapters explicitly, but the model bind is similar to data bind and instead create data sets in the namespace (that is what DbSet is right?). So then what I want to do is bind data to the data set manipulate through the UI I have set up then push those changes from the data set made onto the actual database, correct?

Imar · February 17th, 2014, 06:15 PM

Yes, that's correct.

Any reason you are using DataSets? They are considered a dated concept, and there are much better and easier alternatives.

Cheers,

Imar

K_Joe · February 17th, 2014, 06:19 PM

would it be better just to use a data table instead of a data set because im only manipulating one table in the database or would I need a data set for different instance of the data table?

Imar · February 17th, 2014, 06:25 PM

Same thing; a DataSet represents the database, and the DataTable represents a table. If I recall correctly, you always have to submit changes to a table back in the parent DataSet.

Cheers,

Imar

K_Joe · February 18th, 2014, 12:01 PM

I have the logic all set up in the code behind file but everytime I click insert in on the details view, the grid view refresh doesn't refresh with the new insert value. Am I missing something?

Details view onItemInserting is set to function2,

protected void function2(object sender, EventArgs e)
{
SqlDataAdapter adapt = new SqlDataAdapter();
SqlConnection connect = new SqlConnection();
connect.ConnectionString = ConfigurationManager.ConnectionStrings["PlanetWroxConnectionString1"].ConnectionString;
SqlCommand command = new SqlCommand();
DataTable table = new DataTable();

command = new SqlCommand("INSERT INTO Genre (Name, SortOrder) VALUES (@Name, @SortOrder)");
//command.Parameters.Add("@Id",SqlDbType.Int,32,"Id" );
command.Parameters.Add("@Name", SqlDbType.NVarChar, 200, "Name");
command.Parameters.Add("@SortOrder", SqlDbType.Int, 32, "SortOrder");
command.Connection = connect;

adapt.InsertCommand = command;
//adapt.InsertCommand.ExecuteNonQuery();
command = new SqlCommand("SELECT * FROM Genre", connect);
adapt.SelectCommand = command;
adapt.Fill(table);

GridView1.DataSource = table.DefaultView;
GridView1.DataBind();

adapt.Dispose();
command.Dispose();
connect.Dispose();
}

Imar · February 19th, 2014, 04:10 AM

Hi there,

I don't see where you are getting the values from the form controls, and I don't see you execute the query so I am not surprised this doesn't work. All you do is set up a command, and then execute the select. Where does this code come from? You may need to find a better Insert example that does the parameter-based insert.

I'll try it again: any reason you are using DataSets? They are considered dated and there are much better alternatives.

Cheers,

Imar