CODEWITHSUNDEEP
c#asp.netsql-servergridviewdataset
itsmedavid
itsmedavid

Reputation: 43

dataset and gridview interaction (updating data)

I'm pretty new to asp.net; just wanted to let you guys know before reading my question. I'm creating an address book and I'm using a dataset to display data from my SQL database in a gridview that's in the aspx page. I have this code so far 

            SqlConnection conn = new SqlConnection(conectionstring);
            conn.Open();
            string sql = "SELECT * FROM Directorio";
            SqlDataAdapter myCommand = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds);
            DataView source = new DataView(ds.Tables[0]);
            GridView1.DataSource = source;
            GridView1.DataBind();

This code gets data from the database, fills the dataset, and displays it in the gridview. My question is how can I make changes to the gridview, and then update the dataset so I can apply the changes in the SQL database. I've found many tutorials, but they do things differently. they link the database to the project and don't code any of this, I wanna learn how to do it manually. I also tried enabling editing in the gridview properties that created a column with an edit button but when I click on it the app crashes. thanks my fellow programmers :)

PS: I do undestand the ADO.NET Architecture

Upvotes: 1

Views: 3437

Answers (2)

rlcrews
rlcrews

Reputation: 3562

you have quite a few options. I am assuming you are using webforms and asp.net vs using asp.net MVC.

Depending upon how robust your project is or how you plan to implement it you can go a few routes. If you have a data access layer in place you could use EF (Entity Framework) to handle the retrieval and submission of data to the database. EF is really nice in that it will handle a lot of the CRUD operations for you outside of your webform. Not full abstraction but it gets the sql out of your codebehind and into a separate/manageable layer.

If this is a small project or something you are just playing around with you can insert the data back into the tables using the same ado/sql 

public int CreateNewUser(string username, string password)
    {
        int insertUser = DatabaseUtilities.Perform_CRUD_Operation(
            String.Format(
                "INSERT INTO ContactsUser (UserName, Password) " +
                "VALUES ('{0}', '{1}',
                username, password),
            ConnectionString);
    }


//factored out the Perform operation for reuse in other classes you could do all this in one call 

public static int Perform_CRUD_Operation(string sqlStatement, string connectionString)
    {
        OleDbConnection con = new OleDbConnection("Provider=SQLOLEDB;" + connectionString);
        OleDbCommand cmd = new OleDbCommand(sqlStatement, con);

        try
        {
            con.Open();
            int affectedRows = cmd.ExecuteNonQuery();
            if (affectedRows == 1)
            {
                return 0;
            }
            else
            {
                return AFFECTED_ROWS_ERROR;
            }
        }
        catch (Exception)
        {
            return UNHANDLED_ERROR;
        }
        finally
        {
            con.Close();
        }
    }

This above is just an example. If you are doing anything for production code I would strongly suggest either EF or the use of stored procedures. Using code like above is okay for testing or proof of concept but it is really vulnerable for sql injections, and can be very unsecure.

As for the grid view without seeing code from the UI or code behind it would be next to impossible to make a suggestion on what to do or where to look.

Upvotes: 0

Aaron Anodide
Aaron Anodide

Reputation: 17196

Basically, a datasource control needs orchestrate the changes because its kind of complicated in context of the asp.net architecture.

I think I know where your coming from though, and I have totally been there - you might enjoy this article: http://msdn.microsoft.com/en-us/library/aa581776.aspx

Upvotes: 1

Related Questions