我想写一个通用的存储过程来在我的数据库的任何表中插入数据 [英] I want to write one common stored procedure to insert data in any table of my database

问题描述

我有 6 个不同字段的表.我想动态访问表名.有什么想法可以做到吗?我的代码在下面这是一个简单的过程，我想让它在 c# 中动态使用.怎么办?

I have 6 table with different fields. I want to access table name dynamically. is there any idea to do it? My code is below this is simple procedure which I want to make dynamic to use in c#. how to do it?

Create procedure [dbo].[Insert_Data] (@Id int,@FeesHead nchar(20),@Fees int,@Remarks nchar(20))
as 
   begin 
      Insert into FeesHead(ID,FeesHead,Fees,Remarks)values(@Id,@FeesHead,@Fees,@Remarks)
   End

推荐答案

不要去那里.
这是一个坏主意，因为您最终会得到一个冗长、低效的存储过程，该过程容易受到 SQL 注入攻击并出现性能问题.
为每个表编写一个插入存储过程是要走的路.

Don't go there.
It's a bad idea since you will end up with a long, inefficient stored procedure that will be vulnerable to SQL injection attacks and have performance issues.
Writing an insert stored procedure for each table is the way to go.

你写了六个不同的表，有不同的列，所以编写一个存储过程来处理所有列的插入将需要你发送所有列的所有参数以及表名的参数，以及一个嵌套的if...else 有 6 个可能的路径，每个表一个.
这将最终成为一个冗长、凌乱、写得不好的代码，在每个参数上都很糟糕:安全性、性能、代码可读性和可维护性.

You wrote you have six different tables with different columns, so writing a stored procedure to handle inserts for all of them will require you to send all the parameters for all columns as well as a parameter for the table name, and a nested if...else with 6 possible paths, one for each table.
This will end up as a long, messy, poorly written code at best, bad in each parameter: security, performance, code readability and maintainability.

实现这样一个目标的唯一方法是为每个表编写单独的插入存储过程，然后编写一个存储过程，该存储过程将采用所有可能的参数和表名，并在其中决定什么根据表名参数的值插入要执行的存储过程.但是，您最好将这些条件留给 SQL 客户端(在本例中为您的 c# 代码)，然后再留给 SQL Server.

The only way that makes some sense to achieve such a goal is to write individual insert stored procedures for each table, and then write a stored procedure that will take all of the possible parameters and the table name and inside of it decide what insert stored procedure to execute based on the value of the table name parameter. However, you will be better off leaving conditions like these to the SQL client (your c# code in this case) then to SQL Server.

这篇关于我想写一个通用的存储过程来在我的数据库的任何表中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！