Hi,
I want to insert in two table at the same time.
I write this:
DALBase MyDALBase = new DALBase();
int categoryId=Convert.ToInt32(MyDALBase.ExecuteScaler(System.Data.CommandType.StoredProcedure, "insertmainmenu", new SqlParameter[]{
new SqlParameter("@NodeName",txt_mainmenuname.Text),
new SqlParameter("@MenuType","Dynamic"),
new SqlParameter("@MenuGroup","Other"),
new SqlParameter("@PageId",txt_MainEnName.Text)
}));
string url = txt_MainEnName.Text + ".aspx" + "&CategoryId=" + categoryId;
MyDALBase.ExecuteNoneQuery(System.Data.CommandType.StoredProcedure, "InsertUrl", new SqlParameter[]{
new SqlParameter("@Url",url)
});
and this is DALBase Calss:
public object ExecuteScaler(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = commandType;
cmd.CommandText = commandText;
cmd.Parameters.AddRange(commandParameters);
con.Open();
object retVal = cmd.ExecuteScalar();
con.Close();
return retVal;
}
}
I know I must use Transaction so I tried this code:
string url = txt_MainEnName.Text + ".aspx" + "&CategoryId=" + categoryId;
int cateogyId= MyDALBase.ExecuteNoneQuery(System.Data.CommandType.StoredProcedure, "insertmainmenu", new SqlParameter[]{
new SqlParameter("@NodeName",txt_mainmenuname.Text),
new SqlParameter("@MenuType","Dynamic"),
new SqlParameter("@MenuGroup","Other"),
new SqlParameter("@PageId",txt_MainEnName.Text),
new SqlParameter("@Url",url)
});
USE [st]
GO
/****** Object: StoredProcedure [dbo].[insertmainmenu] Script Date: 12/28/2012 10:32:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[insertmainmenu]
(
@NodeName varchar(50),
@MenuType nvarchar(100),
@MenuGroup nvarchar(100),
@PageId nvarchar(100),
@Url nvarchar(255),
@CategoryID int
)
as
begin
begin transaction
insert into MenuItem (ParentID,Name,MenuType,MenuGroup,PageID) values('0',@NodeName,@MenuType,@MenuGroup,@PageId)
set @CategoryID=SCOPE_IDENTITY()
insert into tbl_pages (PageName)values(@Url)
commit transaction
end
but the problem is I havent access to categoryId before Insert
I need categoryId to make a Url
string url = txt_ MainEnName.Text + ".aspx" + "&CategoryId=" + categoryId;
@Url =url
How can i do it?
thanks a lot