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?
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);
}
}
}
Any question about the above code just let me know
Dynamic DataBusiness Logic
See my blog C# Bits | Twitter @sjnaughton Always seeking an elegant solution.
Marked as answer by mdesoysa on Dec 10, 2008 02:25 AM
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.
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 [:D]
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: --------------------------------------------------------------//
// 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);
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.
Dynamic DataSPROC's
See my blog C# Bits | Twitter @sjnaughton Always seeking an elegant solution.
mdesoysa
Member
13 Points
60 Posts
Validating on insert
Dec 09, 2008 03:52 AM|LINK
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.
Mel
sjnaughton
All-Star
27308 Points
5458 Posts
MVP
Re: Validating on insert
Dec 09, 2008 10:43 AM|LINK
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); } } }Any question about the above code just let me knowDynamic Data Business Logic
Always seeking an elegant solution.
mdesoysa
Member
13 Points
60 Posts
Re: Validating on insert
Dec 10, 2008 02:26 AM|LINK
Thanks sjnaughton that was exactly what I was looking for.
Mel
davidfowl
Contributor
2676 Points
605 Posts
Microsoft
Re: Validating on insert
Dec 10, 2008 08:15 AM|LINK
Great solution! Two suggestions:
Senior SDE, ASP.NET Team, Microsoft
clement_911
Member
8 Points
24 Posts
Re: Validating on insert
Mar 13, 2009 03:02 AM|LINK
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 ?
clement_911
Member
8 Points
24 Posts
Re: Validating on insert
Mar 13, 2009 03:46 AM|LINK
mdesoysa
Member
13 Points
60 Posts
Re: Validating on insert
Apr 06, 2009 04:57 AM|LINK
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?
Mel
sjnaughton
All-Star
27308 Points
5458 Posts
MVP
Re: Validating on insert
Apr 06, 2009 09:59 AM|LINK
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 [:D]
Or you can bind them to the ExecuteDynamicInsert method in the designet see http://msdn.microsoft.com/en-us/library/bb386946.aspx
Dynamic Data SPROC's
Always seeking an elegant solution.
mdesoysa
Member
13 Points
60 Posts
Re: Validating on insert
Apr 06, 2009 09:32 PM|LINK
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.
Mel
sjnaughton
All-Star
27308 Points
5458 Posts
MVP
Re: Validating on insert
Apr 06, 2009 10:19 PM|LINK
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.
Dynamic Data SPROC's
Always seeking an elegant solution.