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).