Insert data from a DataTable into an SQL table

Question

Insert data from a DataTable into an SQL table

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I need help on how to INSERT data from a DataTable to an SQL table. The code of my DataTable is as follows:

public DataTable dtDetalle = new DataTable("Detalle");

public DataTable filldata()
{
    dtDetalle.Columns.Add("idProducto", Type.GetType("System.Int32"));
    dtDetalle.Columns.Add("Producto", Type.GetType("System.String"));
    dtDetalle.Columns.Add("Cantidad", Type.GetType("System.Int32"));
    dtDetalle.Columns.Add("Precio", Type.GetType("System.Single"));
    dtDetalle.Columns.Add("Total", Type.GetType("System.Single"));
    return dtDetalle;
}

With this I add records to the DataTable

try
    {
        if (Session["dt"] == null)
        {
            DataTable dt = filldata();
            DataRow Row1;
            Row1 = dt.NewRow();
            Row1["idProducto"] = txtProducto.SelectedValue.ToString();
            Row1["Producto"] = txtProducto.SelectedItem.Text.ToString();
            Row1["Cantidad"] = txtCantidad.Text;
            Row1["Precio"] = txtTotal.Text;
            Row1["Total"] = TextBox1.Text;
            dt.Rows.Add(Row1);
            datalistadoDetalle.DataSource = dt;
            datalistadoDetalle.DataBind();
            Session["dt"] = dt;
            dtDetalle = dt;
            limpiarDetalle();
        }
        else
        {
            DataTable dt = (Session["dt"]) as DataTable;
            DataRow Row1;
            Row1 = dt.NewRow();
            Row1["idProducto"] = txtProducto.SelectedValue.ToString();
            Row1["Producto"] = txtProducto.SelectedItem.Text.ToString();
            Row1["Cantidad"] = txtCantidad.Text;
            Row1["Precio"] = txtTotal.Text;
            Row1["Total"] = TextBox1.Text;
            dt.Rows.Add(Row1);
            datalistadoDetalle.DataSource = dt;
            datalistadoDetalle.DataBind();
            Session["dt"] = dt;
            dtDetalle = dt;
            limpiarDetalle();
        }

sql c# asp.net gridview

asked by Aldo M. Capitanachi 30.07.2016 в 17:47

source

3 answers

Put a color to all the edges of a map in d3.js Save each row in an array when submitting a form

score 1 · Answer 1

Look, I was looking for information and found this: Table-Valued Parameters in SQL Server 2008 (ADO.NET) is this English why I will try to make a summary of what appears here due to the language.

The first thing you should have is a table in your SQL DB where you will save the values with the same fields of your data table then create a store procedure (procedure stored in Spanish) in the following way:

If your table is called dbo.CategoryTabletype

create the store procedure with one of the parameters like the following:

CREATE PROCEDURE usp_UpdateCategories 
    (@tvpNewCategories dbo.CategoryTableType READONLY)

and then to insert you should use something like this in your sp

INSERT INTO dbo.Categories (CategoryID, CategoryName)
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;

I hope it helps you.

score 0 · Answer 2

USE [database_name]
GO
procedimiento almacenado

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_CreateAccount]
    @firstName varchar(200) = NULL,
    @lastName varchar(200) = NULL,
    @email  varchar(200) = NULL,
    @password varchar(200) = NULL,
    @zip varchar(200) = NULL,
    @userStatus  varchar(200) = NULL,
    @createDate datetime = NULL
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO ACCOUNT (FirstName,LastName,Email,Password,Zip,UserStatus, CreateDate) VALUES (@firstName,@lastName,@email,@password,@zip,@userStatus,@createDate);
END
////////////////////////////////////

 MetodoDB.Sp_CreateAccount("sp_CreateAccount", "UNKNOW", "UNKNOW", txtMail2.Text.Trim().ToLower (), Sub.EncriptatePass(txtPassword2.Text.Trim()), "0000", "0", DateTime.Now);
//////////////////////////////////////////
     public Object Sp_CreateAccount(String sP, String firstName, String lastName, String email, String password, String zip, String userStatus, DateTime createDate)
        {
            SqlCommand comando = new SqlCommand(sP, Conexion1);
            Object Objeto = new Object();
            try
            {
                comando.CommandType = CommandType.StoredProcedure;
                comando.Connection.Open();
                comando.Parameters.Clear();
                comando.Parameters.AddWithValue("@firstName", firstName);
                comando.Parameters.AddWithValue("@lastName", lastName);
                comando.Parameters.AddWithValue("@email", email);
                comando.Parameters.AddWithValue("@password", password);
                comando.Parameters.AddWithValue("@zip",zip);
                comando.Parameters.AddWithValue("@userStatus", userStatus);
                comando.Parameters.AddWithValue("@createDate", createDate);
                Objeto = comando.ExecuteNonQuery();
                comando.Connection.Close();
            }

            catch (Exception ex) { comando.Connection.Close(); Error = ex.ToString(); }

            return Objeto;
        }

score 0 · Answer 3

Look at this example, you can adapt it to your needs:

public void ObtenerValores(DataTable dt)
{
   foreach(DataRow r in dt.Rows)
    {
      string a =r[0].ToString();
      string b=r[1].ToString();
      Insertar("INSERT INTO  NOBRE_TABLA (NOMBRE_CAMPO_a , NOMBRE_CAMPO_b) VALUES (@par1,@par2)",a,b);
     }
}

 public Boolean Insertar(String SQL, String par1, String par2)
        {
            Boolean r = false;
            SqlCommand comando = new SqlCommand(SQL, TuCadenaDeConexion);

            try
            {
                comando.Connection.Open();
                comando.Parameters.Clear();
                comando.Parameters.AddWithValue("@par1", par1);
                comando.Parameters.AddWithValue("@par2", par2);
                comando.ExecuteNonQuery();
                comando.Connection.Close();
                r = true;
            }

            catch (Exception ex) { comando.Connection.Close(); }

            return r;
        }