How do you perform basic CRUD on a database using windows forms?

I have used the .Net framework extensively for backend processes and web pages but I have never needed to use Windows Forms. The only experience I have with Windows form type technology is with Delphi 6&7.

I have searched for tutorial as they seem to be too basic for what I am looking for. Or it seems inappropriate for what I am trying to do.

I would like to have a grid display (for this I have been using DataGridView) on a form (which I have managed to do) so that the user can view, filter and search for data. Other things like pagination would also be involved but I think that I can work out how to do that for myself.

All of the examples I come across use the DataGridView for editing, adding and deleting as well. I am not that comfortable with the idea that user user the grid for everything. It seems confusing and likely to be quite error prone.

I would like to have buttons to Add, Edit and Delete the various types of data. So for example if I had a Form to manage customers, I would like to be able to select the row to edit and click the button. This should open a new windows form with all 开发者_开发百科the data preloaded in textboxes, radio buttons, checkboxes and dropdowns etc.

If they click add they would go to the same screen as the edit except all the information there would be blank. If they select a row and clicked delete it would then delete that customer and remove it from the DataGridView.

With some of the controls and databinding options I have tried it seems to fetch ALL the rows from the table. How would I be able to just get the row that I am interested in from the database. I am not sure what best practises are here.

I am suffering from information overload right now and would just appreciate someone point me in the right direction.

EDIT:

I should mention that from my Delphi days I am expecting to be able to set up something like a query or SqlCommand (Drag and drop on the gui) and set its SQL property, perhaps parameterize the SQL a little bit. Drag and drop a datasource on the form. Point the datasource to the SqlQuery/SqlCommand and click activate on the command. Now I can drag and drop compononents onto the form and set their datasource properties and to the field that they refer to.

---

Take a look at this sample. It shows basic ADO.NET binding to WinForm controls (not just the DataGridView).

---

You could do it in many ways. And there are a bunch of frameworks to help. Entity Framework, NHiberbnate.

but at a low level you can use the database related objects. SqlConnection, SqlCommand, DataReader. Below is SQL-Server related example to load a rows.

private IList<IPosition> PositionsLoad(SqlConnection connection, PositionsRequest request)
{

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = connection;
    cmd.CommandText = "select * from mytable WHERE x ";
    cmd.CommandType = CommandType.Text;

    //Get the reader
    SqlDataReader reader = cmd.ExecuteReader;
    IList<IPosition> ret = new List<IPosition>();

    if (reader.HasRows()) {
        //Create our converter to convert DataReader into a business object/s
        DataReaderToPosition readerConvert = new DataReaderToPosition();
        //loop rows
        while (reader.Read) {
            IPosition pos = readerConvert.DataReaderToBusinessObject(reader);
            ret.Add(pos);
        }
    }

    reader.Close();
    return ret;

}

using the datareader:

Public Overrides Function DataReaderToBusinessObject(ByVal reader As System.Data.IDataReader) As IPosition

    Dim res As IPosition = New Position

    res.ItemDate = reader.GetDateTime(reader.GetOrdinal("Date"))
    res.Strategy = reader.GetString(reader.GetOrdinal("Strategy"))
    res.SubStrategy = reader.GetString(reader.GetOrdinal("SubStrategy"))
    res.BrokerPrime = reader.GetString(reader.GetOrdinal("BrokerPrime"))
    res.BrokerExecuting = reader.GetString(reader.GetOrdinal("BrokerExecuting"))
    res.AccountName = reader.GetString(reader.GetOrdinal("AccountName"))
    res.ExpectedLoss = reader.GetDouble(reader.GetOrdinal("Expected_Loss"))
    res.RiskNotional = reader.GetDouble(reader.GetOrdinal("Risk_Notional"))
    res.ModelDelta = reader.GetDouble(reader.GetOrdinal("Model_Delta"))
    res.ExpectedTrancheLoss = reader.GetDouble(reader.GetOrdinal("Expected_Tranche_Loss"))
    res.BaseCorrelation = reader.GetDouble(reader.GetOrdinal("Base_Correlation"))
    res.LossOnSingleNameDefault = reader.GetDouble(reader.GetOrdinal("Loss_on_Single_Name_Default"))
    res.RiskCapitalAllocation = reader.GetDouble(reader.GetOrdinal("Risk_Capital_Allocation"))
    res.MarginFundingAllocation = reader.GetDouble(reader.GetOrdinal("Margin_Funding_Allocation"))
    res.DataSource = reader.GetString(reader.GetOrdinal("DataSource"))

    Return res
End Function