Hi friends, In this tutorial, I will show a complete example of passing DataTable as a parameter to Stored Procedure in C#.
How to Pass DataTable as a parameter to Stored Procedure in C#:
This is a very common scenario for any application that you need to insert bulk data to SQL Table or you need to pass whole DataTable as a parameter to SQL Server Stored Procedure in C#. For such a case, you need to create User Defined Table Type in SQL Server and it must have the same schema as that of the DataTable that we need to pass to the Stored Procedure as the parameter.
Example:
I am going to Create a Windows Application that will insert a list of products in SQL Server.
Create Database and Table:
CREATE DATABASE [Debugonweb] USE [Debugonweb] CREATE TABLE [dbo].[ProductDetails]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [ProductName] [varchar](100) NULL, [Price] [float] NULL, [Quantity] [float] NULL, [TotalPrice] [float] NULL ) ON [PRIMARY] GO
CREATE TABLE TYPE:
USE [Debugonweb] GO CREATE TYPE [dbo].[tblProductDetails] AS TABLE( [ProductName] [varchar](100) NULL, [Price] [float] NULL, [Quantity] [float] NULL, [TotalPrice] [float] NULL ) GO
Create Stored Procedure:
USE [Debugonweb] GO CREATE PROCEDURE [dbo].[uspAddProductDetails] @tblProductDetails AS tblProductDetails READONLY AS BEGIN SET NOCOUNT ON; INSERT INTO [dbo].[ProductDetails] ([ProductName] ,[Price] ,[Quantity] ,[TotalPrice]) SELECT [ProductName] ,[Price] ,[Quantity] ,[TotalPrice] FROM @tblProductDetails SET NOCOUNT OFF; END GO
Create a Windows Application Project:
Step #1: Open Visual Studio and Create a Windows Application Project called PassDataTable.
Step #2: Drag Four Labels, Four Textboxes, Three Buttons, and one DataGridView On Form Area.
Step #3: Change Text Property of Labels as:
- Label1 → Product Name
- Label2 → Price
- Label3 → Quantity
- Label4 → Total Price
Step #4: Change Name property of Textboxes as:
- Textbox1→ txtProductName
- Textbox2→ txtPrice
- Textbox3→ txtQuantity
- Textbox4→ txtTotalPrice
Step #4: Change Name property of Buttons as:
- Button1→ btnAdd
- Button2→ btnSaveAll
- Button3→ btnClear
Step #5: Change the Name Property of DataGridView as :
- DataGridView1→ dgvProduct
Step #6: Your Form should look like this.
Step #7: Double Click the Form and add the following code.
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.SqlClient; namespace PassDataTable { public partial class Form1 : Form { DataTable _dtProduct = new DataTable(); public Form1() { _dtProduct = DefineProductDataTable(); InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { DefineProductDataTable(); dgvProduct.DataSource = _dtProduct; } private void btnAdd_Click(object sender, EventArgs e) { DataRow drNewRow = _dtProduct.NewRow(); drNewRow["ProductName"] = txtProductName.Text; drNewRow["Price"] = txtPrice.Text; drNewRow["Quantity"] = txtQuantity.Text; drNewRow["TotalPrice"] = txtTotalPrice.Text; _dtProduct.Rows.Add(drNewRow); txtProductName.Clear(); txtPrice.Text = "0.0"; txtQuantity.Text = "0.0"; txtTotalPrice.Text = "0.0"; } public DataTable DefineProductDataTable() { DataTable dtProduct = new DataTable(); DataColumn dcProductName = new DataColumn("ProductName"); DataColumn dcPrice = new DataColumn("Price"); DataColumn dcQuantity = new DataColumn("Quantity"); DataColumn dcTotalPrice = new DataColumn("TotalPrice"); dtProduct.Columns.Add(dcProductName); dtProduct.Columns.Add(dcPrice); dtProduct.Columns.Add(dcQuantity); dtProduct.Columns.Add(dcTotalPrice); return dtProduct; } private void txtQuantity_TextChanged(object sender, EventArgs e) { try { if (!String.IsNullOrEmpty(txtPrice.Text) && !String.IsNullOrEmpty(txtQuantity.Text)) { double dQuantity = Convert.ToDouble(txtQuantity.Text); double dPrice = Convert.ToDouble(txtPrice.Text); double dTotalPrice = dQuantity * dPrice; txtTotalPrice.Text = dTotalPrice.ToString(); } } catch (Exception ex) { MessageBox.Show("Invalid value."); } } private void txtPrice_TextChanged(object sender, EventArgs e) { try { if (!String.IsNullOrEmpty(txtPrice.Text) && !String.IsNullOrEmpty(txtQuantity.Text)) { double dQuantity = Convert.ToDouble(txtQuantity.Text); double dPrice = Convert.ToDouble(txtPrice.Text); double dTotalPrice = dQuantity * dPrice; txtTotalPrice.Text = dTotalPrice.ToString(); } } catch (Exception ex) { MessageBox.Show("Invalid value."); } } public bool SaveAllRecord() { bool bFlag = false; try { using (SqlConnection dbCon = new SqlConnection("Data Source=DESKTOP-CN5IQI9\\SQLEXPRESS;Initial Catalog=Debugonweb;Integrated Security=true")) { using (SqlCommand cmdAddProductDetails = new SqlCommand("[dbo].[uspAddProductDetails]", dbCon)) { cmdAddProductDetails.CommandType = CommandType.StoredProcedure; cmdAddProductDetails.Parameters.AddWithValue("@tblProductDetails", _dtProduct); if (dbCon.State == ConnectionState.Closed) dbCon.Open(); cmdAddProductDetails.ExecuteNonQuery(); bFlag = true; } } } catch (Exception ex) { MessageBox.Show("Error while saving records..!!"); } return bFlag; } public void ClearFields() { txtProductName.Clear(); txtPrice.Text="0.0"; txtQuantity.Text = "0.0"; txtTotalPrice.Text = "0.0"; _dtProduct.Rows.Clear(); } private void btnClear_Click(object sender, EventArgs e) { ClearFields(); } private void btnSaveAll_Click(object sender, EventArgs e) { if (SaveAllRecord() == true) { MessageBox.Show("Records Saved successfully"); ClearFields(); } } } }
Step #8: Bind the events to Form Controls.
Step #9: Replace Your-DataSource with your SQL Server Name.
Step #10: Run the application and verify the output.
Output:
View More:
- Output Parameter in SQL Server.
- Table Variable in SQL Server.
- User Defined Function to Calculate Age from Date Of Birth in SQL Server.
- How to Call SQL Server Stored Procedure in C#.
Conclusion:
I hope you would love this post. Please don’t hesitate to comment for any technical issues. Your feedback and suggestions are important to me.
Thank You.
viagra europe over the counter
levitra 10 mg price
save on pharmacy
viagra 100mg price comparison
buy viagra online
lopressor 50 mg daily
cialis canada coupon
buy women viagra online
where to get over the counter viagra
best rated canadian pharmacy
loans in utah
order viagra online usa
ed medications best drugstore setting powder ed pills that work quickly
viagra 2018
compare viagra
stromectol ivermectin
best value pharmacy
ivermectin drug
viagra online usa cheap
generic female viagra 100mg
pharmacy cheap erection pills best drug store foundation
canadian pharmacy viagra no prescription
trusted online pharmacy
cost cialis 5 mg
cialis tablets generic
personal loans with bad credit
loans no credit
uk pharmacy no prescription
alli diet pill
tadalafil 20 mg usa
stromectol pills
buy viagra pills online uk
cheap viagra buy
20 mg tadalafil best price
buy female viagra pills
order generic tadalafil
cheap tadalafil online
http://bambulapharmacy.com
online loan direct lenders
tadacip
order alli pills
ivermectin 1
drugs from canada ed meds online without doctor prescription online drugstore pharmacy
Hakan Kalkan 1982 yılında İzmir’de doğdu.Astroloji eğitimlerini Amerika ve Hindistan’da aldı.Dünya’nın çeşitli ülkelerinden aralarında ünlü isimlerinde bulunduğu binlerce danışanı vardır.
sildenafil 50 mg coupon
purchase viagra india
buy women viagra online
cialis 20mg sale
sildenafil 20mg prescription cost
https://levitraye.com
buy valtrex without a prescription
viagra online pills
kamagra online buy no prescription
tadalafil
top payday loans
cheap generic nolvadex
hydroxychloroquine 500 mg
tadalafil canadian
best canadian online pharmacy ed meds online canadian pharmacies
cialis 100mg online
viagra uk order
where can i buy over the counter cialis
zanaflex 6 mg
tadalafil in canada
buy cialis 80 mg
Facebook Instagram Reklamları – Sosyal Medya Reklamcılığı Eğitim Seti – Dopinger.com
viagra for sale without prescription
discount generic cialis online
cialis prescription cost
good online mexican pharmacy
buy hydroxychloroquine sulfate
clomid fertility drug
cialis 5mg price australia
i need a payday loan immediately
buy generic sildenafil uk
cialis from us pharmacy
azithromycin pills 250 mg
buy prednisolone online
plaquenil 100mg tablets
propecia price usa
plaquenil tab 200mg cost
plaquenil tablets 200mg
where can i get lexapro brand medication
clomid canada
personal loan eligibility calculator
You actually suggested that really well. female viagra
lyrica 75 mg cost
http://sansordonnancefr.com – pharmacie lafayette
where can i buy retin a cream online
super pharmacy
lipitor 4 mg
cialis 5 mg tablet
buy augmentin online canada
where can i buy prednisone
tadalafil cheap uk
sildenafil 20 mg price in india
plaquenil drug
lipitor generic over the counter
can i buy 40mg doxycycline online
installment payday loans
levitra 10mg
brand name levitra cost
weekly payday loans
zithromax 4 pills
lipitor 10mg
cheap cialis generic
canadian pharmacy prescription viagra
purchase levitra canada
buy augmentin
dealing with heat stroke buy viagra
synthroid 275 mcg
plaquenil hydroxychloroquine cost
chloroquine sulphate 200mg tablet
buy generic viagra 50mg online
prednisolone 10 mg
plaquenil 400 mg
chloroquine 250 mg tablets
canadian pharmacy levitra 20 mg
sildenafil pills uk
buy synthroid online cheap
3 viagra
prescription viagra 100mg
otc viagra 2018
zitromax
plaquenil discount
quineprox 750
where can i get viagra over the counter
real cialis canada
payday loans in pa
usa pharmacy
need cash now
63 mg albuterol
albuterol tablets canada
plaquenil cost without insurance
personal loan agreement
sildenafil 200mg price
aralen 250 mg
buy generic lexapro cheap
generic viagra price
how to buy viagra
buy generic viagra europe
where to buy cheap plaquenil
where can i get propecia in india
can you buy clomid in canada
buy cialis 40 mg online
propecia finasteride
synthroid 137 mcg
credit loans
finpecia
Superb stuff. Kudos. generic cialis
cheapest prices for generic amoxicillin
how to purchase viagra tablet
buy albuterol inhaler
generic viagra europe
vardenafil 60mg
what is considered high blood pressure during pregnancy generic name for viagra
prescription cialis prices
https://tadalafilrembo.com – mas potente viagra o cialis
cialis daily uk
otc generic cialis
clomid uk sale
online female viagra
buy levitra online cheap
rates on personal loans
loans columbus ohio
zithromax for sale 500 mg
amoxicillin tablet 500mg
lexapro capsules
Hey! I just wanted to ask if you ever have any issues with hackers? My last blog (wordpress) was hacked and I ended up losing months of hard work due to no data backup. Do you have any solutions to prevent hackers?|
can i buy real cialis online
zithromax brand name
viagra lowest price canada
buy tetracycline
azithromycin 250mg generic cost
buy celexa 20 mg
where can i buy diflucan 1
celexa pill
order clomid pills online
best generic wellbutrin
tetracycline price uk
cymbalta buy
cialis online india pharmacy
atarax 50 mg tablet price
buy cialis in usa online
zestoretic coupon
levitra tab 20mg
http://canadianvolk.com
allopurinol 300mg cost
cialis canadian
I’m impressed, I have to admit. Seldom do I come across a blog that’s both equally educative and interesting, and without a doubt, you’ve hit the nail on the head. The issue is an issue that too few folks are speaking intelligently about. I’m very happy that I stumbled across this during my search for something concerning this.|
tretinoin cream canada pharmacy
https://viaprescription.com/
Yetişkin Sepeti – Türkiye’nin En Güvenilir Cinsel Sağlık Sitesi
buy metfornin without prescription drugs online
credit loans guaranteed approval
tetracycline capsule 250mg
online prescriptions for viagra
low interest loans
buy viagra in india online
Yetişkin Sepeti – Türkiye’nin En Güvenilir Cinsel Sağlık Sitesi
canadadrugpharmacy com
dapoxetine 10 mg
gabapentin 1000
how much is sildenafil 100mg
dapoxetine 30mg
cialis online mexico
generic lexapro price comparison
lariago
retin a 0.05 cream price
resochina
zovirax cold sore cream
azithromycin 500 mg tablet buy online
buy atarax 10mg
amoxil 500 tablets
starter loans
cialis generics
buy viagra through paypal
propecia 1mg tablets in india
buy indocin