如何將datatable傳入存儲過程中
2 回答
牛魔王的故事
TA貢獻1830條經驗 獲得超3個贊
一、測試環境 1、Windows Server 2008 R2 DataCenter 2、Visual Studio 2008 Team System With SP1 3、SQL Server 2008 Enterprise Edition With SP1 由于是SQL Server 2008新特性，所以只能用2008。 二、測試概述 測試項目很簡單，就是添加新用戶 三、準備數據 1、建立數據庫、表、類型、存儲過程
IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('Users') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE dbo.Users ( UserID INT IDENTITY(-1, -1) NOT NULL, UserName VARCHAR(20) NOT NULL, UserPass VARCHAR(20) NOT NULL, Sex BIT NULL, Age SMALLINT NULL, CONSTRAINT PK_Users_UserID PRIMARY KEY(UserID) ) END IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'UserTable' AND is_user_defined = 1) BEGIN CREATE TYPE UserTable AS TABLE ( UserName VARCHAR(20) NOT NULL, UserPass VARCHAR(20) NOT NULL, Sex BIT NULL, Age SMALLINT NULL ) END GO
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('sp_InsertSingleUser') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) BEGIN DROP PROCEDURE dbo.sp_InsertSingleUser END GO CREATE PROCEDURE dbo.sp_InsertSingleUser ( @User UserTable READONLY ) AS SET XACT_ABORT ON BEGIN TRANSACTION INSERT INTO dbo.Users(UserName, UserPass, Sex, Age) SELECT UserName, UserPass, Sex, Age FROM @User COMMIT TRANSACTION SET XACT_ABORT OFF GO
前臺搭建好表單，后臺主要是一個函數：
| 123456789101112131415161718 | public void fnInsertSingleUser(DataTable v_dt) { try { SqlConnection cn = new SqlConnection(CONN); SqlCommand cmd = cn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = @"sp_InsertSingleUser"; SqlParameter p = cmd.Parameters.AddWithValue("@User", v_dt); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); } catch (Exception ex) { throw ex; } } |
翻過高山走不出你
TA貢獻1875條經驗 獲得超3個贊
表結構
CREATE TABLE [dbo].[loader] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[truckID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[billID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[warehouseID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[source] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[forweight] [float] NULL ,
[startweight] [float] NULL ,
[endweight] [float] NULL ,
[weight] [float] NULL ,
[loadtime] [datetime] NULL
) ON [PRIMARY]
下面是存儲過程
存儲過程主要實現按車號和日期查詢記錄
CREATE PROCEDURE proc_Query
@StartTime datetime,
@EndTime datetime,
@TruckNumber varchar(50)
as
select * from loader where truckID = @TruckNumber and loadtime between @StartTime and @EndTime+1
GO
- 2 回答
- 0 關注
- 787 瀏覽
添加回答
舉報
0/150
提交
取消