how to store the list string values in one short to database

hi sir i have a gridview and that containes some list of rows, i'm storing all that gridview data to datbase, but here i'm looping each row of the gridview and storing it.

it is taking too much of time to store.

is there any other way so that i can store all griview data to database in one short of query

note: Here i'm using mysql database

plz help me to fix this problem

Please hold all gridView data to a string

dont call save function inside loop

like this

StringBuilder UserRoles = new StringBuilder();
UserRoles.Append(");
UserRoles.Append(" EmployeeID = \"" + EmployeeID + "\"");
UserRoles.Append(" RoleID = \"" + RoleID + "\"");
UserRoles.Append(" CanView = \""+(checkView.Checked == true ? 1 : 0).ToString() + "\"");
UserRoles.Append(" CanModify = \"" +(checkModify.Checked ==true ? 1: 0).ToString()+ "\"");
UserRoles.Append(" CanDelete = \"" +(checkDelete.Checked == true ? 1 : 0).ToString()+"\"");
UserRoles.Append(" CanApprove = \"" + (checkApprove.Checked == true?1 : 0).ToString()+"\"");
UserRoles.Append(" />");

after that pass this string to database

and insert to database like this

ALTER PROCEDURE [dbo].[P_INSERT_USER_ROLE]
@xml_user_roles nvarchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @xml xml = cast(@xml_user_roles as xml)
INSERT INTO [dbo].[T_USER_ROLE]
([EmployeeID]
,[RoleID]
,[CanView]
,[CanModify]
,[CanDelete]
,[CanApprove]
,[InsertBy]
,[InsertDate])
(SELECT
@as_employee_id,
s.a.value('@EmployeeID','int') 'EmployeeID',
s.a.value('@RoleID','int') 'RoleID',
s.a.value('@CanView','int') 'CanView',
s.a.value('@CanModify','int') 'CanModify',
s.a.value('@CanDelete','int') 'CanDelete',
s.a.value('@CanApprove','int') 'CanApprove'
from
@xml.nodes('UserRole') s(a))
END

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

shafeek ck9y ago
Please hold all gridView data to a string

dont call save function inside loop

like this
StringBuilder UserRoles = new StringBuilder();
UserRoles.Append(");
UserRoles.Append(" EmployeeID = \"" + EmployeeID + "\"");
UserRoles.Append(" RoleID = \"" + RoleID + "\"");
UserRoles.Append(" CanView = \""+(checkView.Checked == true ? 1 : 0).ToString() + "\"");
UserRoles.Append(" CanModify = \"" +(checkModify.Checked ==true ? 1: 0).ToString()+ "\"");
UserRoles.Append(" CanDelete = \"" +(checkDelete.Checked == true ? 1 : 0).ToString()+"\"");
UserRoles.Append(" CanApprove = \"" + (checkApprove.Checked == true?1 : 0).ToString()+"\"");
UserRoles.Append(" />");
after that pass this string to database

and insert to database like this
ALTER PROCEDURE [dbo].[P_INSERT_USER_ROLE]
@xml_user_roles nvarchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @xml xml = cast(@xml_user_roles as xml)
INSERT INTO [dbo].[T_USER_ROLE]
([EmployeeID]
,[RoleID]
,[CanView]
,[CanModify]
,[CanDelete]
,[CanApprove]
,[InsertBy]
,[InsertDate])
(SELECT
@as_employee_id,
s.a.value('@EmployeeID','int') 'EmployeeID',
s.a.value('@RoleID','int') 'RoleID',
s.a.value('@CanView','int') 'CanView',
s.a.value('@CanModify','int') 'CanModify',
s.a.value('@CanDelete','int') 'CanDelete',
s.a.value('@CanApprove','int') 'CanApprove'
from
@xml.nodes('UserRole') s(a))
END
+1