Hi there,

I'm not sure if this is an obvious question... but does anybody know if theres a way to check whether the data about to be inserted already exists or not in the database and then show the user a message?

Normally I could do a check by searching for the entered details in the database using a sql query but how do I do this with dynamic data?

Appreciate any help.

Hi Mdesoysa, you could try someting like this: 

public partial class NWDataContext
{
    partial void InsertCustomer(Customer instance)
    {
        var DC = new NWDataContext();
        var customer = DC.Customers.FirstOrDefault(c => c.CustomerID == instance.CustomerID);

        if (customer != null)
        {
            throw new ValidationException(String.Format("{0} already exists as {1}.", instance.CustomerID, customer.CompanyName));
        }
        else
        {
            // finally send this to the DB
            this.ExecuteDynamicInsert(instance);
        }
    }
}

 Thanks sjnaughton that was exactly what I was looking for.

Great solution! Two suggestions:

1. If you don't need the result of the query and are just testing for the existance of something, use Any().
2. Since your in the partial of the datacontext class, no need to create a new datacontext, use this.Customers.

That works well for insertion but how to handle update ?

I did a test with DynamicData and the

partial void InsertActivity(Activity instance) gets indeed called on insertion but not the 

partial void UpdateActivity(Activity instance).

So how to validate that the unicity of a field on update ?

 Hi sjnaughton,

Is there any way of doing the insert without using ExecuteDynamicInsert? becuase I want to make sure that the user connecting to the database only has grant execute on stored procedures for security purposes. So any insert/updates etc are done via stored procs only.

 Any ideas?

Yes if you map the SPROC's to the correct table (by dragging them to the table int he L2S designer) then you can just call them instead with the relavent values

Or you can bind them to the ExecuteDynamicInsert method in the designet see http://msdn.microsoft.com/en-us/library/bb386946.aspx

 Hi Sjnaughton,

 When I drag the stored procs onto the table in the dbml I get this error: "One or more selected database objects  return a schema that does not match the schema of the target class".

In attempt to sot this out myself I have added insert and updates methods to my BLL file and have used the actual stored proc for insert.  I am using the same ListDetails template for a few pages so in the DetailsViewItemInserting event I find what table is being shown and then call my insert method for that table. I have the code below - how do I get a ContainerCode instance which is what the user has just entered? right now that line is returning a new instance which is null...

VALIDATION AND INSERT CODE in BLL:

// Method for inserting container code
        public void InsertContainerCode(ContainerCode instance)
        {
            //ContainerCode instance =  ContainerCode();
            var Fcc = new ParentFccOnlineNettingDataContext();

            // Check that the following are unique: --------------------------------------------------------------//

            // Container Code
            var container = Fcc.ContainerCodes.FirstOrDefault(c => c.FCCContainerCode == instance.FCCContainerCode);
            //---------------------------------------------------------------------------------------------------//

            // If the container code is unique
            if (container != null)
            {
                throw new ValidationException(String.Format("The Container Code {0} already exists. Please specify a different code.", instance.FCCContainerCode));
            }
            else
            {
               
                // finally send this to the DB
                int? insertedId = 0;
                Fcc.ContainerCode_Insert(ref insertedId, instance.FCCContainerCode, instance.IsActive);

            }

        }

 LISTDETAILS.ASPX.CS:

 protected void OnDetailsViewItemInserting(object sender, EventArgs e)
        {
            string tableName = GridDataSource.TableName;

            if (tableName == "ContainerCodes")
            {
                ContainerCode instance = new ContainerCode();
                ContainerCodeBLL container = new ContainerCodeBLL();
                container.InsertContainerCode(instance);
            }
            else if (tableName == "Supplier")  
            {
            }
            else if (tableName == "Market")
            {
            }
        }

Appreciate some help.

That means that the primary output of the sproc does not match the table, but you only need to do this for the select, you can just add the SPROC to the Methods panel and then click the table and use the properties to match the speoc to the relavent CRUD funtion OR right click the table and click Configure behavoir. I you do it this way you can still use ExecuteDynamicInsert etc as L2S will call the relevant SPROC.

Thanks, I changed the properties of the tables but now I am unable to use a partial class for validation as it says that the member is already defined.

So basically I want to use stored procs for insert/update/delete but prior to this I want to validate as I have been doing before.

Any ideas?

OK I forgot that . What you do is unmap them after you have testes that they work adn then call them in place of the ExecuteDynhamicInsert etc. also you need to return the PK if it is DB generated on the insert from your SPROC and set the value in the instance before you exit the partial method.

 Hi Steve

I tried the above code to the check duplicate 

It throw's error even for new unique entry

and in the error message the result.name shows the value of the current input and not the already existing value

Basically i can't insert a new record 

here is my code

public partial class TMSDataContext
{
    partial void InsertProject(Project instance)
    {
        //var DC = new TMSDataContext();
        var result = this.Projects.FirstOrDefault(c => c.ProjectID == instance.ProjectID);

        if (result != null)
        {
            throw new ValidationException(String.Format("{0} already exists as {1}.", instance.ProjectID, result.Name));
        }
        else
        {
            // finally send this to the DB
            this.ExecuteDynamicInsert(instance);
        }
    }
}

Thanks in advance

This is my 1st project  in ASP.NET

i am using Dynamic Data L2S

Hi can you post the entire yellow screen error please

Du! posted to wrong thread

Hi Sksmum, you sode looks good to my what6 I suspect is you still have the sproc's in place and they are doing the insert otherwise the result would be from the DB. Somthing weired is going on.

What I would do is create a new project, create the model, then add the business logic above and debug it test that result is comming from the DB.

My version with Northwind and Customer works fine. If you are still having issues give it a try with Northwind and post your results here.