SQL Server 2008的表值参数
3.14/5 (3投票s)
如何创建通用的存储过程来将数据插入到任何表中。
引言
表值参数 (TVP) 是 SqlServer 最有用的功能之一。我们可以创建一个用户定义的 TVP，并将其作为参数在任何存储过程中使用。当我们在大型项目上工作时，我们需要创建许多存储过程来插入数据，有时一个表需要多个存储过程。在这里，我尝试创建一个通用的存储过程，用于将数据插入到具有 n 列的任何表中。
背景
请了解集合的使用，以便更好地理解本文。
使用代码
要使用此功能，我们需要遵循以下步骤。
1- 我们需要创建一个表来插入数据。
/* Create table Emp */
create table Emp(
EmpCode int,
EmpName varchar(20)
)
Go
2- 使用以下脚本创建一个用户定义的表值参数。
/* Create the User defined Table type */
CREATE TYPE [dbo].[pTableData] AS TABLE(
[ColName] [varchar](50) NULL,
[ColValue] [varchar](100) NULL
)
GO
执行以上脚本后，应该在 Programmability>Types>User-Defined Table Type 中显示一个新的用户定义对象，如下屏幕所示。
3- 现在我们正在创建一个通用的存储过程，用于使用我们在第二点中创建的用户定义表类型来插入数据。
/* Create procedure to insert data in any table with n number of columns */
Create procedure LB_SP_InsertData
(
@pTableName varchar(100), --Table Name
@pData pTableData readonly --Table data
)
as
Declare @SqlText as varchar(5000)
SET @SqlText='insert into '+@pTableName+'('
select @SqlText += ColName+',' from @pData
SET @SqlText=SUBSTRING(@SqlText,0,LEN(@SqlText))
SET @SqlText+=')values('
select @SqlText +=''''+ColValue+''''+',' from @pData
SET @SqlText=SUBSTRING(@SqlText,0,LEN(@SqlText))
SET @SqlText+=')'
PRINT (@SqlText)
Exec(@SqlText)
4- 为了在后端测试该存储过程，我们需要使用以下脚本-
/*To test the procedure at back end use following script*/
declare @par as pTableData
insert into @par values('EmpCode','50')
insert into @par values('EmpName','Rahul Pratap Singh')
--select * from @par
Exec LB_SP_InsertData 'Emp', @par
好的，我们已经完成了数据库的工作，现在是时候了解如何从 dotnet 客户端应用程序调用此存储过程了。我们需要完成以下步骤来调用此存储过程-
1- 首先，我们需要设计一个包含两个文本框和一个按钮的网页，如下屏幕所示-
2 - 上述网页的代码文件应包含以下代码 -
using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using LoopBreak;
using System.Data;
public partial class UserRegistration : System.Web.UI.Page
{
Emp objUser = new Emp();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
objUser.EmpCode = Convert.ToInt32(txtEmpCode.Text);
objUser.EmpName = txtEmpName.Text;
objUser.RegisterUser();
Response.Redirect("Success.aspx");
}
}
2- 在业务逻辑文件中，我正在创建一个名为 Emp 的类，其中包含所有属性和关于 Emp 类的业务逻辑。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Collections;
/// <summary>
/// Summary description for CategoryMaster
/// </summary>
///
///
namespace LoopBreak
{
public class Emp
{
//Object declaration section
DataCollection objUserDataCollection = new DataCollection();
DataDetail objDataDetails = new DataDetail();
#region "Property Declaration"
public int EmpCode { get; set; }
public string EmpName { get; set; }
#endregion
public void RegisterUser()
{
objDataDetails._DataCollection = this.objUserDataCollection;
objDataDetails._strTableName = clsConstant.cnsttblUser_Master;
objDataDetails.AddDataToDataCollection("EmpCode", this.EmpCode.ToString());
objDataDetails.AddDataToDataCollection("EmpName", this.EmpName);
DBInsert.InsertData(objDataDetails);
}
}
}
我们正在创建 DataCollection 类的对象，该类是用户数据的集合，就像 Ado.Net 中的 DataTable 一样。我们也可以在这里使用 DataTable 来执行相同的任务来存储所有要插入的行。我们已经在 DataCollection 类中实现了 List 和 IEnumerable 类型，以便为从类中迭代数据添加额外的功能。
/// <summary>
/// DataCollection class stores the data to insert.
/// </summary>
public class DataCollection : List<DataRow>, IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
var sdr = new SqlDataRecord(
new SqlMetaData("ColName", SqlDbType.VarChar, 50),
new SqlMetaData("ColValue", SqlDbType.VarChar, 5000)
);
foreach (DataRow objUserData in this)
{
sdr.SetString(0, objUserData.StrColName);
sdr.SetString(1, objUserData.StrColValue);
yield return sdr;
}
}
}
DataDetail 类用于存储有关插入数据的的信息。表名、数据集合和一种将数据添加到数据集合的方法。
/// <summary>
/// DataDetails class contains the inserted data details.
/// </summary>
public class DataDetail
{
string strTableName;
public string _strTableName
{
get { return strTableName; }
set { strTableName = value; }
}
private DataCollection DataCollection;
public DataCollection _DataCollection
{
get { return DataCollection; }
set { DataCollection = value; }
}
public void AddDataToDataCollection(string strColName, string strColValue)
{
DataRow objDataRow = new DataRow();
objDataRow.StrColName = strColName;
objDataRow.StrColValue = strColValue;
DataCollection.Add(objDataRow);
}
}
数据访问层包含以下代码，用于使用该过程插入数据-
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using Microsoft.SqlServer.Server;
/// <summary>
/// Summary description for DAL
/// </summary>
///
namespace LoopBreak
{
public class DAL
{
public DAL() { }
/// <summary>
/// Returns the type of data provider that the application is using. The application will read from it's config file in the Provider element
/// of the AppSettings node.
/// </summary>
/// <returns></returns>
public static string GetProviders()
{
string provider = ConfigurationSettings.AppSettings["conStr"];
return provider;
}
}
#region "Procedure to insert the data"
public class DBInsert
{
public static void InsertData(DataDetail pUserDetails)
{
SqlConnection objSqlConnection = null;
SqlCommand objSQLComman=null;
try
{
objSQLComman = new SqlCommand(clsConstant.cnstLB_SP_InsertData);
objSQLComman.CommandType = CommandType.StoredProcedure;
objSqlConnection = new SqlConnection(DAL.GetProviders());
objSQLComman.Connection = objSqlConnection;
SqlParameter parTableName = new SqlParameter("@pTableName", SqlDbType.VarChar, 100);
parTableName.Value = pUserDetails._strTableName;
objSQLComman.Parameters.Add(parTableName);
SqlParameter parData = new SqlParameter("@pData", SqlDbType.Structured);
parData.TypeName = "dbo.pTableData";
parData.Value = pUserDetails._DataCollection;
objSQLComman.Parameters.Add(parData);
objSqlConnection.Open();
objSQLComman.ExecuteNonQuery();
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
if (objSQLComman != null)
{
objSQLComman.Dispose();
}
if (objSqlConnection .State == ConnectionState.Open)
{
objSqlConnection.Close();
}
objSqlConnection.Close();
}
}
}
/// <summary>
/// DataDetails class contains the inserted data details.
/// </summary>
public class DataDetail
{
string strTableName;
public string _strTableName
{
get { return strTableName; }
set { strTableName = value; }
}
private DataCollection DataCollection;
public DataCollection _DataCollection
{
get { return DataCollection; }
set { DataCollection = value; }
}
public void AddDataToDataCollection(string strColName, string strColValue)
{
DataRow objDataRow = new DataRow();
objDataRow.StrColName = strColName;
objDataRow.StrColValue = strColValue;
DataCollection.Add(objDataRow);
}
}
/// <summary>
/// DataRow class used to add a new row in DataCollection class.
/// </summary>
public class DataRow
{
string strColName;
public string StrColName
{
get { return strColName; }
set { strColName = value; }
}
string strColValue;
public string StrColValue
{
get { return strColValue; }
set { strColValue = value; }
}
}
/// <summary>
/// DataCollection class stores the data to insert.
/// </summary>
public class DataCollection : List<DataRow>, IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
var sdr = new SqlDataRecord(
new SqlMetaData("ColName", SqlDbType.VarChar, 50),
new SqlMetaData("ColValue", SqlDbType.VarChar, 5000)
);
foreach (DataRow objUserData in this)
{
sdr.SetString(0, objUserData.StrColName);
sdr.SetString(1, objUserData.StrColValue);
yield return sdr;
}
}
}
#endregion
}
我们正在使用 DBInsert 类的 InsertData 方法将数据插入到任何表中。这将插入到具有 n 列的任何表中的数据。
希望这有助于减少代码，并为修改代码提供更大的灵活性。
谢谢。
Rahul
值得关注的点
在编写代码时，您是否学到了任何有趣/有趣/令人恼火的东西？ 您是否做了任何特别聪明或疯狂或异想天开的事情？
历史