Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Wednesday, January 11, 2017 12:05 AM
I have a C# datatable that I am creating with syntax, and filling. I need a way to iterate the datatable and update a SQL Server 2008 table with the data. I think the fields names match up pretty close. What would be the way to do such using VS2015 and SQL Server 2008 R2?
DataTable dt = new DataTable()
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("name",typeof(string));
dt.Columns.Add("address1",typeof(string));
dt.Columns.Add("address2",typeof(string));
dt.Columns.Add("city",typeof(string));
dt.Columns.Add("state",typeof(string));
dt.Columns.Add("zip",typeof(string));
dt.Columns.Add("orderdate",typeof(DateTime));
dt.Columns.Add("ordertime",typeof(DateTime));
dt.Columns.Add("fulfilldate",typeof(DateTime));
dt.Columns.Add("fulfilltime",typeof(DateTime));
Create Table dbo.saleinfo
(
salesID int IDENTITY(1,1) PRIMARY KEY NOT NULL
,ID int
,name nvarchar(250)
,address1 nvarchar(250)
,address2 nvarchar(500)
,city nvarchar(500)
,state1 nvarchar(10)
,zip nvarchar(10)
,orderdate datetime
,ordertime datetime
,fulfilldate datetime
,fulfilltime datetime
)
All replies (4)
Wednesday, January 11, 2017 2:22 AM ✅Answered
If you are retrieving data from an outside source then you would then (and I'm guessing here) would, if the data from the outside source does not exists in the current table do an insert. When doing the insert if you need the primary key you would execute the INSERT along with the following SQL appended to the INSERT
SELECT CAST(scope_identity() AS int);
Use cmd.ExecuteScalar() which when cast to int will have the new primary key. To see this in action (in this demo I use AddWithValue for parameters because I'm doing one record) check out this MSDN code sample I wrote.
If there is a question of, does the record already exists? If so, you would first do a SELECT statement with a WHERE condition to determine this, also using parameters as I did in the UPDATE. If the record does exists, options, update changed data which can get complicated or simply update the entire records, if the record does not exists then do a INSERT.
Bottom line, the different in your last reply is things have become more complicated since we are dealing with an outside source but using logic I mentioned above you have the tools to do this.
Lastly, it's always good to supply all details up front as I interpreted your question as a typical read from database table, make changes, update.
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator
Wednesday, January 11, 2017 12:24 AM
I recommend you use table value parameters, this can avoid multiple connections to the database, you should only consider that the name of the fields in the datatable are the same as the type that you create in sql
Si la respuesta te fue útil vótala como tal,y si fue respuesta márcala. Solo dejo en el foro mis post si fueron útiles , de lo contrario y por mantener el orden los borro. Saludos. Lima-Perú.
Is the TableValuedTypeExample something that must be created each time I want to dump data, or is it something that once you create once it is always there? I ask, as my procedure will be run 2x daily and was trying to get a good feel on exactly how the set-up should be.
CREATE TYPE dbo.TableValuedTypeExample AS TABLE
(
CustomerId INT NOT NULL,
CustomerName VARCHAR(MAX),
PRIMARY KEY (CustomerId)
)
Wednesday, January 11, 2017 12:49 AM
There are many ways to do this, here is one that is not fully complete but should give you a clue to how to move forward. DataAdapters and TableAdapters are other options.
Class to load and save/update
using System;
using System.Data;
using System.Data.SqlClient;
namespace ForumExample
{
public class Operations
{
string Server = "KARENS-PC"; // change for your server
string Catalog = "ForumExamples"; // change for your database
string ConnectionString;
public bool HasErrors { get; set; }
public string ExceptionMessage { get; set; }
public DataTable DataTable;
public Operations()
{
// if not using VS2015, replace this with String.Format with two arguments
ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True";
}
public bool GetData()
{
DataTable = new DataTable();
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn, CommandText = "SELECT salesID ,ID , name , address1 , address2 , city , state1 , zip , orderdate , ordertime , fulfilldate , fulfilltime FROM saleinfo;" })
{
try
{
cn.Open();
DataTable.Load(cmd.ExecuteReader());
DataTable.Columns["salesID"].ColumnMapping = MappingType.Hidden;
}
catch (Exception Ex)
{
HasErrors = true;
ExceptionMessage = Ex.Message;
return false;
}
}
}
return true;
}
public void Update(DataTable table)
{
DataTable ModifiedRows = table.GetChanges(DataRowState.Modified);
if (ModifiedRows != null)
{
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
// I did two fields, you need to do one for each field.
using (SqlCommand cmd = new SqlCommand { Connection = cn, CommandText = "UPDATE saleinfo SET ID = @Id,name = @Name WHERE salesID = @salesID" })
{
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Id", DbType = DbType.Int32 });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Name", DbType = DbType.String });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@salesID", DbType = DbType.Int32 });
cn.Open();
foreach (DataRow row in ModifiedRows.Rows)
{
cmd.Parameters[0].Value = row.Field<int>("ID");
cmd.Parameters[1].Value = row.Field<string>("Name");
cmd.Parameters[2].Value = row.Field<int>("salesID");
cmd.ExecuteNonQuery(); // you could also check the result to see if one row was affected
}
}
}
}
}
}
}
Form code
using System;
using System.Data;
using System.Windows.Forms;
namespace ForumExample
{
public partial class Form1 : Form
{
private BindingSource bsData = new BindingSource();
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
var ops = new Operations();
if (ops.GetData())
{
bsData.DataSource = ops.DataTable;
dataGridView1.DataSource = bsData;
}
}
private void button1_Click(object sender, EventArgs e)
{
var ops = new Operations();
ops.Update((DataTable)bsData.DataSource);
}
}
}
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator
Wednesday, January 11, 2017 2:12 AM
@Karen Payne -> it looks like you are populating the datatable with data from the SQL Table dbo.saleinfo.
My datatable is being populated by an outside process, I am needing to update the SQL Table dbo.saleinfo
with the data from my datatable. I used the same names as I thought that it would allow for easier field mapping(s)
in the update statement.
Will your syntax work for that? I can not wrap my head around how to do such...