How to pass a data table in sp using ado.net

Question

Hi I need to work on ado.net. How to pass a data table in sp using ado.net Thanks

Sachin Singh · Answer

You can learn the complete ADO.NET here https://www.sharpencode.com/article/ADONET/stored-procedure-in-ado-net

Mayooran Navamany · Answer

I can explain my code Database name: SIS, Table name: student_Info table has (name, age, address, phone) you can follow these steps. I'll provide you with a simple example assuming you have a stored procedure named InsertStudent : Create a DataTable: Create a DataTable with columns corresponding to your student_Info table (name, age, address, phone). DataTable studentDataTable = new DataTable(); studentDataTable.Columns.Add("Name", typeof(string)); studentDataTable.Columns.Add("Age", typeof(int)); studentDataTable.Columns.Add("Address", typeof(string)); studentDataTable.Columns.Add("Phone", typeof(string)); // Add a row with sample data DataRow row = studentDataTable.NewRow(); row["Name"] = "John Doe"; row["Age"] = 20; row["Address"] = "123 Main St"; row["Phone"] = "555-1234"; studentDataTable.Rows.Add(row); Create a SqlCommand with CommandType as StoredProcedure: Create a SqlCommand object with the CommandType set to StoredProcedure and the name of your stored procedure. using (SqlConnection connection = new SqlConnection("your_connection_string")) { connection.Open(); using (SqlCommand command = new SqlCommand("InsertStudent", connection)) { command.CommandType = CommandType.StoredProcedure; // Assuming you have a stored procedure parameter named @StudentData of type DataTable SqlParameter parameter = new SqlParameter("@StudentData", SqlDbType.Structured); parameter.Value = studentDataTable; command.Parameters.Add(parameter); // Execute the stored procedure command.ExecuteNonQuery(); } } Ensure that the stored procedure InsertStudent exists and accepts a parameter of type DataTable (e.g., @StudentData ). Modify your Stored Procedure: Your stored procedure should be able to handle the DataTable parameter. For example: CREATE PROCEDURE InsertStudent @StudentData dbo.YourTableType READONLY AS BEGIN -- Your insert logic here INSERT INTO student_Info (name, age, address, phone) SELECT name, age, address, phone FROM @StudentData; END Ensure that dbo.YourTableType is a table type that matches the structure of your DataTable (name, age, address, phone).

How to pass a data table in sp using ado.net

Answers (2)