|
Hello !
I have a vb.net program with sql server 2008r2 database
I'm using Entity Framework 6.
On each database's table there's a column "ToDelete" that may have the value true or false.
What is the shortest way to " Delete all records from all tables that have the ToDelete=True"
Thank you !
|
|
|
|
|
A statement like the following will yield the appropriate DELETE statements:
SELECT 'DELETE FROM ['
+ A.[name] + '].['
+ B.[name] + '] WHERE ['
+ C.[name] + ']=1'
FROM [sys].[schemas] A
INNER JOIN [sys].[objects] B
ON A.[schema_id]=B.[schema_id]
INNER JOIN [sys].[columns] C
ON B.[object_id]=C.[object_id]
INNER JOIN [sys].[types] D
ON C.[system_type_id]=D.[system_type_id]
WHERE B.[schema_id]!=4
AND C.[name]='ToDelete'
AND D.[name]='boolean'
In a procedure, you could use a cursor to iterate the results and use an EXECUTE statement on each.
Or in code, you could pass each to an ExecuteNonQuery call.
|
|
|
|
|
Hah that picks up views as well 
You have that in your model database don't you! I've seen some weird utilities and that one is right up there.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ok, that's a small whoops, but easily remedied. No, I don't use that, I just whipped it up in response to the question.
|
|
|
|
|
Sorry friend but I'm using Entity framework. I mean shortest way using entity framework.
|
|
|
|
|
Use the right tool for the right job.
|
|
|
|
|
you are saying that no way to do this with entity Framework ?
|
|
|
|
|
No. In fact I don't know enough about Entity Framework to know what it can and can't do, but I understand that it can execute a procedure.
|
|
|
|
|
Haven't we had this conversation recently!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Something like this should work:
Using transaction As DbContextTransaction = yourContext.Database.BeginTransaction()
Const query As String = "SELECT N'DELETE FROM ' + QUOTENAME(A.[name]) + N'.' + QUOTENAME(B.[name]) + N' WHERE ' + QUOTENAME(C.[name]) + N' = 1' As CommandText FROM sys.schemas As A INNER JOIN sys.objects As B ON B.schema_id = A.schema_id INNER JOIN sys.columns As C ON C.object_id = B.object_id INNER JOIN sys.types As D ON D.system_type_id = C.system_type_id WHERE A.[schema_id] != 4 And B.[type] = 'U' And C.[name] = 'ToDelete' And D.[name] = 'bit';"
Dim commandsToExecute As List(Of String) = context.Database.SqlQuery(Of String)(query).ToList()
For Each command As String In commandsToExecute
yourContext.Database.ExecuteSqlCommand(command)
Next
transaction.Commit()
End Using
Entity Framework : Raw SQL Queries[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
In the link on your post , I red that this can be applied on Entity Framework with Code First.
I'm using Database First ???
|
|
|
|
|
Does your context class inherit from DbContext or ObjectContext? If it's DbContext, then the code I posted should work.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Public Function isAdministrator(AdminLevelID As Integer) As Boolean
Dim DBComm As DbCommand
Dim strsql As String = ""
strsql = "SELECT IsAdministrator FROM tb_GE_AdminLevel WHERE SysID =" & AdminLevelID
Return (DBComm.ExecuteScalar(strsql))
End Function
Public Function GetAllAdminMenu(ByVal AdminLevelID As Integer, Optional ByVal CustomerID As Integer = 0) As DataTable
Dim dt As New DataTable
Dim dbcommSave As DbCommand
Dim strSql As String = ""
Try
If (isAdministrator(AdminLevelID)) Then
strSql = "SELECT AdminMenuID,AdminMenuName,ParentID,IsNull((Select 1 As Allow from [tb_GE_AdminLevelMenu] Where AdminMenuID = A.AdminMenuID AND SysID = " & AdminLevelID & " ),0) As checked FROM tb_GE_AdminMenu As A ORDER BY SrNo"
Else
strSql = "SELECT AdminMenuID,AdminMenuName,ParentID,IsNull((Select 1 As Allow from [tb_GE_AdminLevelMenu] Where AdminMenuID = A.AdminMenuID AND SysID=1),0) As checked FROM tb_GE_AdminMenu As A ORDER BY SrNo"
End If
''strSql = "SELECT MenuID,MenuName,ParentID FROM tb_GE_Menu ORDER BY SrNo"
dbcommSave = Db.GetSqlStringCommand(strSql)
dt.Load(Db.ExecuteReader(dbcommSave))
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Exception")
End Try
Return dt
End Function
|
|
|
|
|
You can't solve what? You have not asked a question.
Say what problem you are having, any error messages etc, and what you are trying to do, as we are not telepathic
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Sorry
The "isAdministrator" returned null value!
|
|
|
|
|
|
Solve what? You haven't said what the problem is. We can't read your mind or see your screen so you have to explain it to us.
|
|
|
|
|
Okay, Fine!
I'm sorry! 
|
|
|
|
|
Your code is vulnerable to SQL Injection[^].
NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Can I use WM_CTLCOLORSCROLLBAR for changing VSFlex Grid's Scroll Bar?
Please kindly give me some advice?
|
|
|
|
|
when some one changes the status: as 'Getting Daily Feed' in addition to saving the status as of now we need to call following URL is '(http://dev.bullseyeprospecting.com/api/dealer/saveevent?dealerGuid=&eventCode=A&userid=5212404' .
How can i do this in wcf .
|
|
|
|
|
Yeah, that made no sense at all.
We have no context information, nothing about your application, the details of exactly what you're trying to accomplish, what the problem is, ... nothing.
|
|
|
|
|
I have a dropdown name PER-PS 101 report. When I click view report it supposed to show the report if have values and "No records found for the selected criteria" if no data between the selected date. But in my case, if there is no data, its showing missing parameter error.
Below is the code:
Private Function ShowICDReport101() As Boolean
Try
Dim MessageString As String
Const ReportName As String = "ICD_InpatientReturn_Main.rpt"
Dim ReportDocumentObject As New ReportDocument
Dim ReportParametersListObject As List(Of ReportParameters)
Dim CrystalReportHelperObject As New CrystalReportHelper
'Dim ds As New DataSet
Dim ReportFilePath As String
ReportFilePath = CrystalReportHelperObject.DeploymentPath("CrystalReportsFilePath") + ReportName
ReportParametersListObject = SetReportParameters()
'To call the CheckReportData() function
If CheckReportData(ParentFormController.DateByMonthDateTimePicker.Value.Month.ToString(), ParentFormController.ToYearDateTimePicker.Value.Year.ToString(), ApplicationHelper.UserId) = True Then
ReportDocumentObject = CrystalReportHelperObject.GetCrystalReportDocument(ReportName, ReportParametersListObject)
If ReportDocumentObject.HasRecords = True Then
ParentFormController.ShowCrystalReportInViewer(ReportDocumentObject, "ICD Healthcare Facilities for Inpatien Returns", False, String.Empty)
Else
MessageBox.Show(MessageString, "Care21", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End If
Else
MessageString = "No records found for the selected criteria"
MessageBox.Show(MessageString, "Care21", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End If
Catch ex As Exception
Throw
End Try
End Function
Public Function CheckReportData(ByVal Month, ByVal Year, ByVal Userid) As Boolean
Dim ReturnCode As Boolean = False
Dim ReturnValues As New Dictionary(Of String, Object)
Dim result As Boolean
Try
Dim DbObject As Microsoft.Practices.EnterpriseLibrary.Data.Database = DatabaseFactory.CreateDatabase()
Dim DbCommand As DbCommand = DbObject.GetStoredProcCommand("usp_Rpt_InpatientReturns")
Data.AddInParameter(DbCommand, "@paMonthIn", DbType.String, Month, DbObject)
Data.AddInParameter(DbCommand, "@paYearIn", DbType.String, Year, DbObject)
Data.AddInParameter(DbCommand, "@paUserIdIn", DbType.String, Userid, DbObject)
Dim dt As DataTable
Dim ds As New DataSet("tt")
Dim DataReaderObject As IDataReader = DbObject.ExecuteReader(DbCommand)
dt.Load(DataReaderObject) 'If DataReaderObject.FieldCount = 0 Then
'Else
'End If
If dt.Rows.Count = 0 Then
result = False
Else
result = True
End If
DbCommand.Parameters.Clear()
'If ReturnValues("Result").ToString().Equals("1") Then
'Return True
'Else
' Return False
'End If
Catch ex As Exception
Throw
End Try
Return result
End Function
|
|
|
|
|
Running from your message that is displayed. I am assuming that this is the if check that is run before the message is displayed.
Member 11727014 wrote: If CheckReportData(ParentFormController.DateByMonthDateTimePicker.Value.Month.ToString(), ParentFormController.ToYearDateTimePicker.Value.Year.ToString(), ApplicationHelper.UserId) = True Then
Have you checked to see what the values that these parameters have before the function is run? Also what is ParentFormController.DateByMonthDateTimePicker and ParentFormController.ToYearDateTimePicker.Value.Year.ToString()? are these controls?
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
