先說Stored Procedure 之5種基本用法，Get、List、Insert、Update、Delete
假設資料表用MyTest
//以下是刪除
ALTER proc [dbo].[sp_MyTest_Delete]
(
//以下的變數是由外面的程式傳進來的
@變數1 as varchar(20),//型別可以自己改成要的
@變數2 as varchar(20),
@變數3 as varchar(20),
@MyReturn as varchar(20) output,//這有加output代表傳入輸出的意思
@MyMessage as varchar(20) output
)
as
begin
----------------------------------------------
--變數區//宣告在這個SP中會用到的變數
----------------------------------------------
declare @S_Table as varchar(100)
declare @S_Type as varchar(20)
set @S_Table = 'MyTest'
set @S_Type = 'Delete'
set @MyReturn= ''
----------------------------------------------
--執行區
----------------------------------------------
//傳變數到另外一個SP裡
exec sp_S_Table_Delete @變數A,@變數B,@變數C
----------------------------------------------
--傳回值區
----------------------------------------------
//RowCount是這個SP影響了幾比資料列
if @@RowCount = 0 set @MyReturn = 'Failed' else set @MyReturn = 'Success'
set @MyMessage = [dbo].fn_Msg_GetName(@變數,@變數,@變數)//這裡也可呼叫Function
//然後呼叫個SP去插入一些Log檔
exec [dbo].sp_Log_Insert @變數,@變數
end
//=============================================================
//以下是Get，Get是Get一，時機是當我們要修改一筆資料時，要先把這筆資料先Get到介面上
ALTER PROCEDURE [dbo].[sp_MyTest_Get]
(
@F_Language as varchar(20),
@F_Company_ID as varchar(20),
@F_User_ID as varchar(20),
@F_SeqNo as varchar(20),
@ReturnCode as varchar(20) output,
@ReturnMsg as nvarchar(200) output
)
as
begin
----------------------------------------------
--變數區
----------------------------------------------
declare @S_Table as varchar(100)
declare @S_Type as varchar(20)
set @S_Table = 'MyTest'
set @S_Type = 'Get'
set @ReturnCode = ''
----------------------------------------------
--執行區
----------------------------------------------
--新增時的初始值
if @F_SeqNo = '0'
begin
select F_ID = '',
F_Name = 'abc',
F_Year = '',
F_Year_2 = '',
F_Type = '',
F_Area = '',
F_Introduction = '',
F_Publish = '',
F_Note = '',
F_IsPublic = '',
end
else
begin
select F_SeqNo = a.F_SeqNo,
F_CreateTime = a.F_CreateTime,
F_UpdatgeTime = a.F_UpdateTime,
F_PrintTime = a.F_PrintTime,
F_ExportTime = a.F_ExportTime,
F_Stat = a.F_Stat,
F_Security = dbo.fn_CodeTable_GetName(@F_Language,'Security',a.F_Security),
F_Company = dbo.fn_Company_GetName(@F_Language,a.F_Company_ID),
F_Owner = dbo.fn_User_GetName(a.F_Company_ID,a.F_Owner),
F_Keyin = dbo.fn_User_GetName(a.F_Company_ID,a.F_Keyin),
F_Icon = a.F_Icon,
F_Priority = a.F_Priority,
F_ID = right(a.F_ID,6),
F_Name = a.F_Name,
F_Year = a.F_Year,
F_Year_2 = a.F_Year_2,
F_Type = a.F_Type,
F_Area = a.F_Area,
F_Introduction = a.F_Introduction,
F_Publish = a.F_Publish,
F_Note = a.F_Note,
F_IsPublic = a.F_IsPublic,
from MyTest a
where a.F_Stat = '1'
and a.F_SeqNo = @F_SeqNo
end
----------------------------------------------
--傳回值區
----------------------------------------------
//@@Error是看有沒有錯誤，有錯就不會等於0
if @@Error <> 0 set @ReturnCode = 'FG01' else set @ReturnCode = 'SG01'
set @ReturnMsg = [dbo].fn_Msg_GetName(@F_Language,@S_Table,@ReturnCode)
exec [dbo].sp_Log_Insert @F_Company_ID,@F_User_ID,@S_Table,@F_SeqNo,@S_Type,@ReturnCode
end
//===========================================================
//以下是新增
ALTER PROCEDURE [dbo].[sp_MyTest_Insert]
(
@F_Language as varchar(20),
@F_Company_ID as varchar(20),
@F_User_ID as varchar(20),
@F_SeqNo as varchar(20) output,
@_Icon as varbinary(Max),
@F_Name as nvarchar(200),
@F_Type as varchar(20),
@F_Area as varchar(20),
@F_Year as nvarchar(200),
@F_Year_2 as nvarchar(200),
@F_Ispublic as char(1),
@F_Introduction as nvarchar(Max),
@F_Publish as nvarchar(Max),
@F_Note as nvarchar(Max),
@ReturnCode as varchar(20) output,
@ReturnMsg as nvarchar(200) output
)
as
begin
----------------------------------------------
--變數區
----------------------------------------------
declare @S_Table as varchar(100)
declare @S_Type as varchar(20)
set @S_Table = 'MyTest'
set @S_Type = 'Insert'
set @ReturnCode = ''
----------------------------------------------
--檢查區
/*
----------------------------------------------
if exists(select * from MyTest where F_Stat = '1' and F_Company_ID = @F_Company_ID and F_Name = @F_Name)
begin
Set @ReturnCode = 'CI01'
end
*/
----------------------------------------------
--執行區
----------------------------------------------
if @ReturnCode = ''
begin
Begin Transaction
--給號開始
declare @F_ID as varchar(20)
set @F_ID = ''
select top 1 @F_ID = F_ID
from MyTest
where F_Company_ID = @F_Company_ID
order by F_ID desc
//以下是如果F_ID沒東西的話，就給他第一個編號，如果已經有編號的話，就用一點程式
的技巧讓編號繼續下去
if @F_ID = '' set @F_ID = @F_Company_ID + '_XD_000001' else set @F_ID =
@F_Company_ID + '_XD_' + right(cast(cast(right(@F_ID,6) as integer) +
1000001 as char(7)),6)
--給號結束
exec [dbo].sp_S_Table_Insert @F_Language,@F_Company_ID,@F_User_ID,@S_Table
set @F_SeqNo = @@IDENTITY//這是傳一個剛剛新增資料的識別值給
//先Insert一些資料，再用Update去更新那筆資料
update F_SeqNo
set F_ID = @F_ID,
F_Icon = @_Icon,
F_Name = @F_Name,
F_Type = @F_Type,
F_Area = @F_Area,
F_Year = @F_Year,
F_Year_2 = @F_Year_2,
F_Ispublic = @F_Ispublic,
F_Introduction = @F_Introduction,
F_Publish = @F_Publish,
F_Note = @F_Note
where F_SeqNo = @F_SeqNo
if @@ERROR != 0
begin
Rollback Transaction
set @ReturnCode = 'FI01'
end
else
begin
Commit Transaction
set @ReturnCode = 'SI01'
end
end
----------------------------------------------
--傳回值區
----------------------------------------------
set @ReturnMsg = [dbo].fn_Msg_GetName(@F_Language,@S_Table,@ReturnCode)
exec [dbo].sp_S_User_Log_Insert @F_Company_ID,@F_User_ID,@S_Table,@F_SeqNo,@S_Type,@ReturnCode
end
//============================================================
//以下是List把資料都列出來
ALTER PROCEDURE [dbo].[sp_MyTest_List]
(
@F_Language as varchar(20),
@F_Company_ID as varchar(20),
@F_User_ID as varchar(20),
@F_Name as nvarchar(200),
@F_IsPublic as char(1),
@ReturnCode as varchar(20) output,
@ReturnMsg as nvarchar(200) output
)
as
begin
----------------------------------------------
--變數區
----------------------------------------------
declare @S_Table as varchar(100)
declare @F_SeqNo as varchar(20)
declare @S_Type as varchar(20)
set @S_Table = 'MyTest'
set @F_SeqNo = ''
set @S_Type = 'List'
set @ReturnCode = ''
//加%是要讓之後的LIKE語法好做
set @F_Name = '%' + @F_Name + '%'
set @F_IsPublic = '%' + @F_IsPublic + '%'
----------------------------------------------
--執行區
----------------------------------------------
select
F_No = row_number() Over(Order by a.F_ID asc),
F_Alert_New = dbo.fn_Alert_Get(a.F_CreateTime),
F_Alert_Modify = dbo.fn_Alert_Get(a.F_UpdateTime),
F_Alert_Print = dbo.fn_Alert_Get(a.F_PrintTime),
F_Alert_Export = dbo.fn_Alert_Get(a.F_ExportTime),
F_SeqNo = a.F_SeqNo,
F_Security = dbo.fn_CodeTable_GetName(@F_Language,'Security',a.F_Security),
F_Company = dbo.fn_Company_GetName(@F_Language,a.F_Company_ID),
F_Owner = dbo.fn_User_GetName(a.F_Company_ID,a.F_Owner),
F_Keyin = dbo.fn_User_GetName(a.F_Company_ID,a.F_Keyin),
F_Icon = a.F_Icon,
F_Priority = a.F_Priority,
F_ID = right(a.F_ID,6),
F_Name = a.F_Name,
F_Year = a.F_Year + ' - ' + a.F_Year_2,
F_Area = a.F_Area,
F_IsPublic = a.F_IsPublic,
F_ArtWork_Count= 0
from MyTest a
where a.F_Stat = '1'
and a.F_Company_ID = @F_Company_ID
//isnull的意思是如果a.F_Name是NULL的話，就給他一個''空值
and isnull(a.F_Name,'') like @F_Name
and isnull(a.F_IsPublic,'') like @F_IsPublic
order by a.F_ID asc
----------------------------------------------
--傳回值區
----------------------------------------------
if @@RowCount = 0 set @ReturnCode = 'FL01' else set @ReturnCode = 'SL01'
set @ReturnMsg = [dbo].fn_Msg_GetName(@F_Language,@S_Table,@ReturnCode)
exec [dbo].sp_S_User_Log_Insert @F_Company_ID,@F_User_ID,@S_Table,@F_SeqNo,@S_Type,@ReturnCode
end
//========================================================
//以下是更新
ALTER PROCEDURE sp_MyTest_Update
(
@F_Language as varchar(20),
@F_Company_ID as varchar(20),
@F_User_ID as varchar(20),
@F_SeqNo as varchar(20),
@F_Icon as varbinary(Max),
@F_Name as nvarchar(200),
@F_Type as varchar(20),
@F_Area as varchar(20),
@F_Year as nvarchar(200),
@F_Year_2 as nvarchar(200),
@F_Ispublic as char(1),
@F_Introduction as nvarchar(Max),
@F_Publish as nvarchar(Max),
@F_Note as nvarchar(Max),
@ReturnCode as varchar(20) output,
@ReturnMsg as nvarchar(200) output
)
as
begin
----------------------------------------------
--變數區
----------------------------------------------
declare @S_Table as varchar(100)
declare @S_Type as varchar(20)
set @S_Table = 'MyTest'
set @S_Type = 'Update'
set @ReturnCode = ''
----------------------------------------------
--檢查區
----------------------------------------------
/*
if exists(select * from MyTest where F_Stat = '1' and F_Company_ID =
@F_Company_ID and F_Name = @F_Name and F_SeqNo <> @F_SeqNo)
begin
Set @ReturnCode = 'CU01'
end
*/
----------------------------------------------
--執行區
----------------------------------------------
if @ReturnCode = ''
begin
Begin Transaction
exec [dbo].sp_S_Table_Update @F_User_ID,@S_Table,@F_SeqNo
update MyTest
set
F_Icon = @F_Icon,
F_Name = @F_Name,
F_Type = @F_Type,
F_Area = @F_Area,
F_Year = @F_Year,
F_Year_2 = @F_Year_2,
F_Ispublic = @F_Ispublic,
F_Introduction = @F_Introduction,
F_Publish = @F_Publish,
F_Note = @F_Note
where F_SeqNo = @F_SeqNo
if @@ERROR != 0
begin
Rollback Transaction
set @ReturnCode = 'FU01'
end
else
begin
Commit Transaction
set @ReturnCode = 'SU01'
end
end
----------------------------------------------
--傳回值區
----------------------------------------------
set @ReturnMsg = [dbo].fn_Msg_GetName(@F_Language,@S_Table,@ReturnCode)
exec [dbo].sp_S_User_Log_Insert @F_Company_ID,@F_User_ID,@S_Table,@F_SeqNo,@S_Type,@ReturnCode
end
//==============================================================
//接下來是一些SP會用到的另外SP或函式
ALTER uFUNCTION [dbo].[fn_Msg_GetName] (@F_Language as varchar(20),@F_Type as varchar(20),@F_ReturnCode as varchar(20))
RETURNS nvarchar(200)
BEGIN
declare @F_Value as nvarchar(200)
select @F_Value = F_ReturnMsg from S_Msg where F_Langage = @F_Language and F_Type = @F_Type and F_ReturnCode = @F_ReturnCode
set @F_Value = '[' + @F_ReturnCode + ']' + isnull(@F_Value,'')
return isnull(@F_Value,'')
END
ALTER proc [dbo].[sp_S_User_Log_Insert]
(
@F_Company_ID as varchar(20),
@F_User_ID as varchar(20),
@F_Master_Table as varchar(100),
@F_Master_ID as varchar(20),
@F_Type as varchar(20),
@F_ReturnCode as varchar(20)
)
as
begin
----------------------------------------------
--變數區
----------------------------------------------
declare @F_Table as varchar(100)
set @F_Table = 'S_User_Log'
exec sp_S_Table_Insert '',@F_Company_ID,@F_User_ID,@F_Table
declare @F_SeqNo as int
set @F_SeqNo = @@IDENTITY
update S_User_Log
set F_Master_Table = @F_Master_Table,
F_Master_ID = @F_Master_ID,
F_Type = @F_Type,
F_ReturnCode = @F_ReturnCode
where F_SeqNo = @F_SeqNo
end
ALTER proc [dbo].[sp_S_Table_Insert]
(
@F_Language as varchar(20),
@F_Company_ID as varchar(20),
@F_User_ID as varchar(20),
@F_Table as varchar(100)
)
as
begin
declare @F_SQLStr as nvarchar(3000)
declare @F_ParamStr as nvarchar(3000)
set @F_SQLStr = N''
set @F_SQLStr = @F_SQLStr + ' Insert ' + @F_Table
set @F_SQLStr = @F_SQLStr + '('
set @F_SQLStr = @F_SQLStr + 'F_CreateTime,'
set @F_SQLStr = @F_SQLStr + 'F_UpdateTime,'
set @F_SQLStr = @F_SQLStr + 'F_Stat,'
set @F_SQLStr = @F_SQLStr + 'F_Security,'
set @F_SQLStr = @F_SQLStr + 'F_Language,'
set @F_SQLStr = @F_SQLStr + 'F_Company_ID,'
set @F_SQLStr = @F_SQLStr + 'F_Owner,'
set @F_SQLStr = @F_SQLStr + 'F_Keyin,'
set @F_SQLStr = @F_SQLStr + 'F_Master_Stat'
set @F_SQLStr = @F_SQLStr + ')'
set @F_SQLStr = @F_SQLStr + ' Values '
set @F_SQLStr = @F_SQLStr + '('
set @F_SQLStr = @F_SQLStr + ' GetDate(),'
set @F_SQLStr = @F_SQLStr + ' GetDate(),'
set @F_SQLStr = @F_SQLStr + '''1'','
set @F_SQLStr = @F_SQLStr + '''C'','
set @F_SQLStr = @F_SQLStr + '@F_Language,'
set @F_SQLStr = @F_SQLStr + '@F_Company_ID,'
set @F_SQLStr = @F_SQLStr + '@F_User_ID,'
set @F_SQLStr = @F_SQLStr + '@F_User_ID,'
set @F_SQLStr = @F_SQLStr + '''1'''
set @F_SQLStr = @F_SQLStr + ' )'
set @F_ParamStr = N'@F_Language as varchar(20),@F_Company_ID as varchar(20),@F_User_ID as varchar(20)'
exec sp_executesql @F_SQLStr,@F_ParamStr,@F_Language,@F_Company_ID,@F_User_ID
end
ALTER proc [dbo].[sp_S_Table_Update]
(
@F_User_ID as varchar(20),
@F_Table as varchar(100),
@F_SeqNo as varchar(20)
)
as
begin
declare @F_SQLStr as nvarchar(3000)
declare @F_ParamStr as nvarchar(3000)
set @F_SQLStr = N''
set @F_SQLStr = @F_SQLStr + 'Update ' + @F_Table
set @F_SQLStr = @F_SQLStr + ' Set F_UpdateTime = GetDate(),'
set @F_SQLStr = @F_SQLStr + 'F_Keyin = @F_User_ID'
set @F_SQLStr = @F_SQLStr + ' Where F_SeqNo = @F_SeqNo'
set @F_ParamStr = N'@F_SeqNo as varchar(20),@F_User_ID as varchar(20)'
exec sp_executesql @F_SQLStr,@F_ParamStr,@F_SeqNo,@F_User_ID
end
//============================================================
//接下來下面是用Visual Studio去呼叫寫好的Stored Procedure
public override bool Insert(out string F_SeqNo, out string ReturnCode, out string ReturnMsg)
{
using (SqlConnection cn = new SqlConnection())
{
///宣告區
///
SqlCommand cmd = new SqlCommand();
cn.ConnectionString = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString.ToString();
cn.Open();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
///指定sp名稱
///
cmd.CommandText = "sp_MyTest_Insert";
///共用參數區
///
//下面這句CommandBuilder是直接把在SP裡變數的型態拿過來用，如此一來就不用在這邊再宣告變數的型態了，好像也不用去New變數出來耶
SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["@F_Language"].Value = ConfigurationManager.AppSettings["Language"].ToString();
cmd.Parameters["@F_Company_ID"].Value = ConfigurationManager.AppSettings["CompanyID"].ToString();
cmd.Parameters["@F_User_ID"].Value = ConfigurationManager.AppSettings["UserID"].ToString();
cmd.Parameters["@F_SeqNo"].Value = "";
cmd.Parameters["@ReturnCode"].Value = "";
cmd.Parameters["@ReturnMsg"].Value = "";
//這是設定變數是output變數
cmd.Parameters["@F_SeqNo"].Direction = ParameterDirection.Output;
cmd.Parameters["@ReturnCode"].Direction = ParameterDirection.Output;
cmd.Parameters["@ReturnMsg"].Direction = ParameterDirection.Output;
///新增參數區
///
下面這行就是原本要寫成這樣有型態的樣子
//cmd.Parameters.Add("@F_Icon", SqlDbType.VarBinary).Value = "";
//如果變數是INT的話，這裡可能就要用.Value的方法了
cmd.Parameters["@F_Name"].Value = F_Name.Text;
cmd.Parameters["@F_Type"].Value = F_Type.Text;
cmd.Parameters["@F_Area"].Value = F_Area.Text;
cmd.Parameters["@F_Year"].Value = F_Year.Text;
cmd.Parameters["@F_Year_2"].Value = F_Year_2.Text;
cmd.Parameters["@F_IsPublic"].Value = F_IsPublic.Text;
cmd.Parameters["@F_Introduction"].Value = F_Introduction.Text;
cmd.Parameters["@F_Publish"].Value = F_Publish.Text;
cmd.Parameters["@F_Note"].Value = F_Note.Text;
///執行sp
///
cmd.ExecuteNonQuery();
cn.Close();
///取得回傳值
///
F_SeqNo = cmd.Parameters["@F_SeqNo"].Value.ToString();
ReturnCode = cmd.Parameters["@ReturnCode"].Value.ToString();
ReturnMsg = cmd.Parameters["@ReturnMsg"].Value.ToString();
///回傳值
///
if (ReturnCode.Substring(0, 1) == "S")
{
return true;
}
else
{
return false;
}
}
}
//=============================================================
public override bool Get(int SeqNo, out string ReturnCode, out string ReturnMsg)
{
using (SqlConnection cn = new SqlConnection())
{
///宣告區
///---------------------
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
cn.ConnectionString = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString.ToString();
cn.Open();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
da.SelectCommand = cmd;
///指定sp名稱
cmd.CommandText = "sp_MyTest_Get";
///共用參數區
///---------------------
///
SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["@F_Language"].Value = ConfigurationManager.AppSettings["Language"].ToString();
cmd.Parameters["@F_Company_ID"].Value = ConfigurationManager.AppSettings["CompanyID"].ToString();
cmd.Parameters["@F_User_ID"].Value = ConfigurationManager.AppSettings["UserID"].ToString();
cmd.Parameters["@F_SeqNo"].Value = SeqNo;
cmd.Parameters["@ReturnCode"].Value = "";
cmd.Parameters["@ReturnMsg"].Value = "";
cmd.Parameters["@ReturnCode"].Direction = ParameterDirection.Output;
cmd.Parameters["@ReturnMsg"].Direction = ParameterDirection.Output;
///執行sp
///
da.Fill(ds);
///取得回傳值
///
ReturnCode = cmd.Parameters["@ReturnCode"].Value.ToString();
ReturnMsg = cmd.Parameters["@ReturnMsg"].Value.ToString();
///回傳值
///---------------------
if (ReturnCode.Substring(0, 1) == "S")
{
///設定值到控制項理
///
DataRow dr = ds.Tables[0].Rows[0];
F_ID.Text = dr["F_ID"].ToString();
F_Name.Text = dr["F_Name"].ToString();
F_Type.Text = dr["F_Type"].ToString();
F_Year.Text = dr["F_Year"].ToString();
F_Year_2.Text = dr["F_Year_2"].ToString();
F_Area.Text = dr["F_Area"].ToString();
F_IsPublic.Text = dr["F_IsPublic"].ToString();
F_Introduction.Text = dr["F_Introduction"].ToString();
F_Publish.Text = dr["F_Publish"].ToString();
F_Note.Text = dr["F_Note"].ToString();
return true;
}
else
{
return false;
}
}
}
//==============================================================
public override bool Update(int SeqNo, out string ReturnCode, out string ReturnMsg)
{
using (SqlConnection cn = new SqlConnection())
{
///宣告區
///---------------------
SqlCommand cmd = new SqlCommand();
cn.ConnectionString = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString.ToString();
cn.Open();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
///指定sp名稱
///
cmd.CommandText = "sp_MyTest_Update";
///共用參數區
///
SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["@F_Language"].Value = ConfigurationManager.AppSettings["Language"].ToString();
cmd.Parameters["@F_Company_ID"].Value = ConfigurationManager.AppSettings["CompanyID"].ToString();
cmd.Parameters["@F_User_ID"].Value = ConfigurationManager.AppSettings["UserID"].ToString();
cmd.Parameters["@F_SeqNo"].Value = "";
cmd.Parameters["@ReturnCode"].Value = "";
cmd.Parameters["@ReturnMsg"].Value = "";
cmd.Parameters["@F_SeqNo"].Direction = ParameterDirection.Output;
cmd.Parameters["@ReturnCode"].Direction = ParameterDirection.Output;
cmd.Parameters["@ReturnMsg"].Direction = ParameterDirection.Output;
///新增參數區
///
//cmd.Parameters.Add("@F_Icon", SqlDbType.VarBinary).Value = "";
cmd.Parameters["@F_Name"].Value = F_Name.Text;
cmd.Parameters["@F_Type"].Value = F_Type.Text;
cmd.Parameters["@F_Area"].Value = F_Area.Text;
cmd.Parameters["@F_Year"].Value = F_Year.Text;
cmd.Parameters["@F_Year_2"].Value = F_Year_2.Text;
cmd.Parameters["@F_IsPublic"].Value = F_IsPublic.Text;
cmd.Parameters["@F_WebSite_Type"].Value = F_WebSite_Type.Text;
cmd.Parameters["@F_Chronology"].Value = F_Chronology.Text;
cmd.Parameters["@F_Introduction"].Value = F_Introduction.Text;
cmd.Parameters["@F_Exhibition"].Value = F_Exhibition.Text;
cmd.Parameters["@F_Publish"].Value = F_Publish.Text;
cmd.Parameters["@F_Note"].Value = F_Note.Text;
///執行sp
///
cmd.ExecuteNonQuery();
cn.Close();
///取得回傳值
///
ReturnCode = cmd.Parameters["@ReturnCode"].Value.ToString();
ReturnMsg = cmd.Parameters["@ReturnMsg"].Value.ToString();
///回傳值
///---------------------
if (ReturnCode.Substring(0, 1) == "S")
{
return true;
}
else
{
return false;
}
}
}
//================================================================
public override bool Search(ref DataSet ds, out string ReturnCode, out string ReturnMsg)
{
using (SqlConnection cn = new SqlConnection())
{
//宣告區
//
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
cn.ConnectionString = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString.ToString();
cn.Open();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
da.SelectCommand = cmd;
//指定sp名稱
cmd.CommandText = "sp_MyTest_List";
//共用參數區
///---------------------
SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["@F_Language"].Value = ConfigurationManager.AppSettings["Language"].ToString();
cmd.Parameters["@F_Company_ID"].Value = ConfigurationManager.AppSettings["CompanyID"].ToString();
cmd.Parameters["@F_User_ID"].Value = ConfigurationManager.AppSettings["UserID"].ToString();
cmd.Parameters["@ReturnCode"].Value = "";
cmd.Parameters["@ReturnMsg"].Value = "";
cmd.Parameters["@ReturnCode"].Direction = ParameterDirection.Output;
cmd.Parameters["@ReturnMsg"].Direction = ParameterDirection.Output;
//查詢參數區
cmd.Parameters["@F_Name"].Value = F_Name.Text;
cmd.Parameters["@F_IsPublic"].Value = F_IsPublic.Text;
//執行sp
da.Fill(ds);
///取得回傳值
///
ReturnCode = cmd.Parameters["@ReturnCode"].Value.ToString();
ReturnMsg = cmd.Parameters["@ReturnMsg"].Value.ToString();
///回傳值
///
if (ReturnCode.Substring(0, 1) == "S")
{
return true;
}
else
{
return false;
}
}
留言列表
