11 Replies - 5937 Views - Last Post: 04 January 2011 - 04:14 PM Rate Topic: -----

#1 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Extending a Textbox to be SQL Data Bound

Posted 14 December 2010 - 04:13 PM

ASP.Net 4.0 -- VB.Net for back-end.
Currently, whenever I want to display data from SQL I write my query and then cycle through a datareader and manually assign each value to the corresponding textbox.
When the page is saved, I then have to reverse this and *somehow* save anything changed back to the database.

I've gone through many iterations of how I save information over the last 3 years.
At first, I just used one massive update statement and saved everything back to the database. This failed because it is highly possible that 2 people are making changes to the same page at the same time (just different parts). So, I needed to ensure that only the parts they updated were saved.
My next attempt was on loading the information, I saved the value in both the textbox and an invisible label. On save, I compared the label to the textbox and only saved the information to the database if it was changed - but this is a lot of coding and doesn't seem efficient.
Most recently, I'm taking advantage of the "Text Changed" event for the textbox, and in that event I'm handling saving back to the database. The one downside here is that I potentially could be saving something that was not intended to be saved (no easy way to cancel).


Regardless of which way I save back to the database, this requires for every new field I add to the page, I have to add information in two spots (displaying, and again with saving).

What have other people done for this?
Note, some pages may have 50 different textboxes, pulling from different tables.

My thought, was somehow extending the textbox to have extra fields "Database" "Table" "Field" "Primary Key" "PK Value" - or something similiar.
The "PK Value" would need to dynamically pull from another area on the page (perhaps a DDL that a user uses to select what they are viewing).

In this extension, when a textbox is loaded it would automatically query for the proper information. When a textbox is changed (and postback occurs) it would save the information back.

Any feedback, ideas, telling me it's a bad idea, are all welcome. I'm not really sure where to go from here, but I'm hoping to reduce some of the extremely redundant work that I'm currently doing.

Thanks,

Is This A Good Question/Topic? 0
  • +

Replies To: Extending a Textbox to be SQL Data Bound

#2 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 446
  • View blog
  • Posts: 1,501
  • Joined: 28-April 09

Re: Extending a Textbox to be SQL Data Bound

Posted 14 December 2010 - 11:01 PM

might could use a listview, replace the labels in the itemtemplate with textboxes. Store the values of the textboxes in viewstate when they're loaded , using the itemdatabound event most likely. Then create a button that when clicked will iterate over the textboxes in the listview, compare their values with what's in viewstate and submit the changes to the database. Of course, that's as long as you can use one datasource for more than one text box rather than needing a datasource for every individual textbox.

This post has been edited by Nakor: 14 December 2010 - 11:04 PM

Was This Post Helpful? 0
  • +
  • -

#3 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: Extending a Textbox to be SQL Data Bound

Posted 15 December 2010 - 09:39 AM

I'm pulling from many different tables, and they are intertwined together.
For example, I might have some basic client information.
Followed by their addresses (separate table).
Back to client information.
Now contact information (separate table).
Back to client information.

(And it continues).
It seems that with the splitting up of information such as this, it would require many different controls each with similiar data sources.

This is why I was thinking it would be best to have the population/saving portion in the back-end, I was just hoping to streamline that portion.

I'm going to play around with listview and formview though as they may be useful for some of the smaller pages/interfaces.
Was This Post Helpful? 0
  • +
  • -

#4 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: Extending a Textbox to be SQL Data Bound

Posted 16 December 2010 - 06:51 PM

Having a datasource that you can bind your controls to so that you don't have to manually iterate through all of them on the page all of the time would be ideal if at all possible. You say that you tried this before with one big update statement, but ran into issues when more than one user was modifying the same record? If you were to try this approach again, is there a way that you could implement a record locking mechanism? So if a user is working with a particular record, you could flag it and set it to a "Locked" status to prevent any other user from modifying it (maybe read only), until the current user is finished?

Once upon a time I did have to support an application that was similar in nature (similar as in a highly normalized database). The feature I described was implemented to handle that scenario.
Was This Post Helpful? 0
  • +
  • -

#5 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: Extending a Textbox to be SQL Data Bound

Posted 16 December 2010 - 08:36 PM

I don't think that would go over very well with the end-users :-).
What this is, is our CRM/Workflow system.
There are some "pages" that might be viewed many times per day by many people - possibly at the same time (perhaps they are all working on the same client).
Many times they are only "looking" at the page - so I wouldn't want to lock it every time a page was viewed.
They also wouldn't like having to "lock" and "unlock" a page manually.

In 3 years I've never had a complaint that an update went "missing" - so I have apparently done a decent enough job accounting for it at this point.
I've been looking into FormView's though and on many of the pages they may be useful. Some pages are viewed much less regularly (for example, the 'edit/new physical address page).
Was This Post Helpful? 0
  • +
  • -

#6 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 446
  • View blog
  • Posts: 1,501
  • Joined: 28-April 09

Re: Extending a Textbox to be SQL Data Bound

Posted 16 December 2010 - 10:22 PM

even if you can group two or three textboxes into a datacontrol, like a FormView, it would be better than dealing with 50 individual textboxes. At the least then you're dealing with 25 controls rather than 50.
Was This Post Helpful? 0
  • +
  • -

#7 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: Extending a Textbox to be SQL Data Bound

Posted 17 December 2010 - 11:20 AM

Idea (and if this would be very bad, someone please slap me) - I did kind of ask this initially but just got around to attempting it.
Create a custom class (e.g. dbTextbox).
Inherit the textbox control.
Add properties for "Table" "Column" "PK" "PK_Value."

Now, I can simply drag one of these controls into my project and fill out these values.

I've got this much already working, and I can fill out and access the different settings; however, I'm having the following problems.

1. How can I assign the property "PK_Value" the selectedvalue of a drop down list? Is this possible to do directly in the aspx code, or does it need to be in the code-behind?
2. Is it possible in the code-behind iterate through all controls of a certain type on the page?

My thinking is, once the page has mostly loaded, I have something similar to:

for each dbTextbox on the page
dbTextbox.loaddata(pk_value)
next

Then, when the page experiences a postback I would want to reverse the process
for each dbTextbox on the page
dbTextbox.savedata(pk_value)
next

Of course, I wouldn't want to save the data for all, only ones that have had the text changed.
Now, i could query the DB to see if it has changed - but that is a lot of extra queries.
Since the textbox control has postback data (and knows when it's text has changed) is there someway to reference this in code?
for each dbTextbox on the page
if dbTextbox.textchanged then dbTextbox.savedata(pk_value)
next
Was This Post Helpful? 0
  • +
  • -

#8 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 446
  • View blog
  • Posts: 1,501
  • Joined: 28-April 09

Re: Extending a Textbox to be SQL Data Bound

Posted 17 December 2010 - 04:56 PM

you could add an oldData property to it which could be used to store the text that is in the textbox when it's loaded. Then compare the current text with what's in the oldData property to see if the text has changed since the control loaded.
Was This Post Helpful? 0
  • +
  • -

#9 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 446
  • View blog
  • Posts: 1,501
  • Joined: 28-April 09

Re: Extending a Textbox to be SQL Data Bound

Posted 17 December 2010 - 09:58 PM

Hope you don't mind, but I decided to give a go at creating a similar control, sounded like an interesting project. I don't use VB but here is the beginnings of a databound textbox in C# that does some of what you want I think. You could work on converting it to VB if you'd like, but maybe it'll help some. It will extract the value for the given ColumnName where the primarykey column has a value equal to the primarykeyvalue. You could possibly extend this with a built in update method. It should also allow you to compare OldText with NewText on postback. It only works with sqldatasources.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Data;
using System.Collections;

namespace AjaxControlSet.DataBoundTextBox
{
    public class DataBoundTextBox : CompositeDataBoundControl
    {
        private TextBox textbox = new TextBox();

        public string Text
        {
            get { return textbox.Text; }
            set { textbox.Text = value; }
        }

        public string ColumnName 
        {
            get
            {
                object temp = ViewState["ColumnName"];
                return temp == null ? string.Empty : (string)temp;
            }
            set { ViewState["ColumnName"] = value; }
        }

        public string OldText
        {
            get
            {
                object temp = ViewState["OldText"];
                return temp == null ? string.Empty : (string)temp;
            }
            private set { ViewState["OldText"] = value; }
        }

        public string PrimaryKey
        {
            get
            {
                object temp = ViewState["PrimaryKey"];
                return temp == null ? string.Empty : (string)temp;
            }
            set { ViewState["PrimaryKey"] = value; }
        }

        public string PrimaryKeyStringValue
        {
            get
            {
                object temp = ViewState["PrimaryKeyStringValue"];
                return temp == null ? string.Empty : (string)temp;
            }
            set { ViewState["PrimaryKeyStringValue"] = value; }
        }

        /*
         * Create Child Controls
         */
        protected override int CreateChildControls(System.Collections.IEnumerable dataSource, bool dataBinding)
        {
            int count = 0;

            this.Controls.Add(textbox);

            if (dataSource != null)
            {
                if (dataBinding)
                {
                    IEnumerator obj = dataSource.GetEnumerator();

                    while (obj.MoveNext())
                    {
                        if (obj.Current.GetType() == typeof(DataRowView))
                        {
                            DataRowView view = obj.Current as DataRowView;
                            DataView dv = view.DataView;

                            if (view.Row.Field<string>(this.PrimaryKey) ==
                                                        this.PrimaryKeyStringValue)
                            {
                                this.Text = view.Row.Field<string>(this.ColumnName);
                            }
                        }
                        count++;
                    }
                }
            }
            ChildControlsCreated = true;

            return count;
        }
    }
}



The aspx page then looked like this when I was testing it.

<form id="form1" runat="server">
    <div>
        Last Name:
        <cc1:DataBoundTextBox ID="DataBoundTextBox1" runat="server" 
                              ColumnName="au_lname" 
                              DataSourceID="SqlDataSource1" 
                              PrimaryKey="au_id" 
                              PrimaryKeyStringValue="172-32-1176" 
                              Text="" />
        <br />
    </div>
    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
    <p>
        &nbsp;</p>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:pubsConnectionString %>" 
        SelectCommand="SELECT * FROM [authors]">
    </asp:SqlDataSource>
    </form>



At the least hopefully this will give you some more idea's to work with.

for the dropdownlist you should be able to use the selectedindexchanged event to set the primary key value, you'd might have to set the dropdownlist's autopostback property to true

This post has been edited by Nakor: 17 December 2010 - 10:00 PM

Was This Post Helpful? 0
  • +
  • -

#10 Guest_Guest*


Reputation:

Re: Extending a Textbox to be SQL Data Bound

Posted 03 January 2011 - 09:42 PM

Will there be an issue with multiple controls retrieving from and assigning to the same element in the viewstate? This is in the case where there is more then one custom control on the page
Was This Post Helpful? 0

#11 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 446
  • View blog
  • Posts: 1,501
  • Joined: 28-April 09

Re: Extending a Textbox to be SQL Data Bound

Posted 04 January 2011 - 05:59 AM

if I understand your question and you have multiple of the textbox controls then they would each have their own viewstate
Was This Post Helpful? 0
  • +
  • -

#12 Guest_Guest*


Reputation:

Re: Extending a Textbox to be SQL Data Bound

Posted 04 January 2011 - 04:14 PM

Thankyou, that is the answer that i was looking for?
Was This Post Helpful? 0

Page 1 of 1