Insert entire DataTable into database at once instead of row by row?
I discovered SqlBulkCopy is an easy way to do this, and does not require a stored procedure to be written in SQL Server.
Here is an example of how I implemented it:
// take note of SqlBulkCopyOptions.KeepIdentity , you may or may not want to use this for your situation.
using (var bulkCopy = new SqlBulkCopy(_connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
// my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
foreach (DataColumn col in table.Columns)
{
bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = destinationTableName;
bulkCopy.WriteToServer(table);
}
How to insert a data table into SQL Server database table?
Create a User-Defined TableType
in your database:
CREATE TYPE [dbo].[MyTableType] AS TABLE(
[Id] int NOT NULL,
[Name] [nvarchar](128) NULL
)
and define a parameter in your Stored Procedure
:
CREATE PROCEDURE [dbo].[InsertTable]
@myTableType MyTableType readonly
AS
BEGIN
insert into [dbo].Records select * from @myTableType
END
and send your DataTable
directly to sql server:
using (var command = new SqlCommand("InsertTable") {CommandType = CommandType.StoredProcedure})
{
var dt = new DataTable(); //create your own data table
command.Parameters.Add(new SqlParameter("@myTableType", dt));
SqlHelper.Exec(command);
}
To edit the values inside stored-procedure, you can declare a local variable with the same type and insert input table into it:
DECLARE @modifiableTableType MyTableType
INSERT INTO @modifiableTableType SELECT * FROM @myTableType
Then, you can edit @modifiableTableType
:
UPDATE @modifiableTableType SET [Name] = 'new value'
Inserting data into datatable based on row value
Is it possible to update the value column of a specific row based on
the value of the ID of that row
Yes, you can use DataRow.SetField
and a little bit of Linq:
var rowsToUdate = dt.AsEnumerable()
.Where(r => r.Field<int>("ID") == id);
foreach(DataRow row in rowsToUpDate)
row.SetField("Value", newValue);
...pretty much to be able to run a SQL query of there is faster or
better way?
So do you want to update the database instead of the DataTable
? This part is confusing.
Insert datatable into existing Access file
If at all possible, your best bet is to let Access do the work with built-in tools. I would link the SQL Server table to your Access DB, and then create a Make-Table query in Access that creates a new table from your linked SQL Server table. You can pull the whole table or just a subset of it, depending on how you set up your query.
Related Topics
Creating a Zip Archive in Memory Using System.Io.Compression
Get the Ip Address of the Remote Host
How to Handle Key Press Event in Console Application
How to Check for a Network Connection
Duplicate Keys in .Net Dictionaries
Nullable Type as a Generic Parameter Possible
Catching "Maximum Request Length Exceeded"
How to Write an Async Method with Out Parameter
Using .Net 4.5 Async Feature for Socket Programming
How to Get the Current Line Number
Configure the Authorization Server Endpoint
Pass Complex Parameters to [Theory]
C# Pass by Value VS. Pass by Reference
Change C# Dllimport Target Code Depending on X64/X86
Are There Any Fuzzy Search or String Similarity Functions Libraries Written for C#
Replace First Occurrence of Pattern in a String
How to Calculate the Angle Between a Line and the Horizontal Axis