Tuesday, May 22, 2012

Inserting a List<> in SQL Table

I have an entity "report" whose values i want to insert in a Database table. Following attributes of the Report have to be inserted -



reportID - int
RoleID - int
Created_BY = SYSTEM(default)
CURRENT_TIMESTAMP


Now the problem is with the 2nd attribute. I have a report with the LIST<ROLES> attributes. ROLES is a well defined entity which has an ID and a NAME. From this list I have to extract every role and insert each role's ID into the table.



So my query presently looks as below :



INSERT INTO REPORT_MARJORIE_ROLE(REPORT_ID, ROLE_ID, CREATED_BY, CREATED)
VALUES({0}, {1}, 'SYSTEM', CURRENT_TIMESTAMP)


The C# code from where I am parsing these values is as follows :



try
{
StringBuilder _objSQL = new StringBuilder();
_objSQL.AppendFormat(Queries.Report.ReportQueries.ADD_NEW_ROLES, report.ID, "report.MarjorieRoles.Add(MarjorieRole"));
_objDBWriteConnection.ExecuteQuery(_objSQL.ToString());
_objDBWriteConnection.Commit();
_IsRolesAdded = true;
}


So please guide me how to add roles from C# function





No comments:

Post a Comment