ADO.Net INSERT 不插入数据 [英] ADO.Net INSERT not inserting data
问题描述
我有一个 c#.Net 4.5 控制台应用程序，我正在尝试将数据从 DataTable 插入到 SQL Server 2008R2 数据库表.我没有收到错误，但没有插入数据.这是我的代码:
I've got a c#.Net 4.5 console application and I'm trying to insert data from a DataTable to a SQL Server 2008R2 database table. I get no errors, but no data gets inserted. Here's my code:
Int32 newID = 0;
DataTable dtMaxUserID = GeneralClassLibrary.GeneralDataAccessLayer.ExecuteSelect("SELECT MAX(UserID)+1 AS NewID FROM TIUser", false, "TrackitCN");
newID = Convert.ToInt32(dtMaxUserID.Rows[0]["NewID"].ToString());
//Get new users from AllUserData
DataColumn dcRowID = new DataColumn("RowID", typeof(Int32));
//dcRowID.AllowDBNull = false;
dcRowID.AutoIncrement = true;
dcRowID.AutoIncrementSeed = 1;
dcRowID.AutoIncrementStep = 1;
//dcRowID.Unique = true;
//dcRowID.ColumnName = "RowID";
DataTable dtNewUsers = new DataTable();
dtNewUsers.Columns.Add(dcRowID);
dtNewUsers.Columns.Add("MaxID");
dtNewUsers.Columns.Add("UserID");
dtNewUsers.Columns.Add("FullName");
dtNewUsers.Columns.Add("Title");
dtNewUsers.Columns.Add("Phone");
dtNewUsers.Columns.Add("EMailAddr");
dtNewUsers.Columns.Add("Fax");
dtNewUsers.Columns.Add("Dept");
dtNewUsers.Columns.Add("Dept_Num");
dtNewUsers.Columns.Add("Location");
dtNewUsers.Columns.Add("UserDef_1");
dtNewUsers.Columns.Add("UserDef_2");
dtNewUsers.Columns.Add("Login");
dtNewUsers.Columns.Add("Password");
dtNewUsers.Columns.Add("PasswordFlags");
dtNewUsers.Columns.Add("LanguageID");
dtNewUsers.Columns.Add("NTAuthentication");
dtNewUsers.Columns.Add("NTAccount");
dtNewUsers.Columns.Add("SID");
dtNewUsers.Columns.Add("SelfServiceAccess");
dtNewUsers.Columns.Add("ImagePath");
dtNewUsers.Columns.Add("CompFlag");
dtNewUsers.Columns.Add("Employee_ID");
dtNewUsers.Columns.Add("SessionID");
string strQuery = "SELECT " + newID + " AS MaxID, LName" + ", " + "FName AS FullName, Title, Phone, EMail AS EmailAddr, Fax, Department AS Dept, Office AS Location, [Login] AS Employee_ID FROM [User] WHERE StartDate >= CAST(DATEPART(year,getdate()) AS varchar) + '-' + CAST(DATEPART(month,getdate()) AS varchar) + '-' + CAST(DATEPART(day,getdate())-1 AS varchar)";
DataTable dtTemp = GeneralClassLibrary.GeneralDataAccessLayer.ExecuteSelect(strQuery, false, "AllUserDataCN");
foreach (DataRow row in dtTemp.Rows)
dtNewUsers.ImportRow(row);
SqlTransaction tran = null;
try
{
connection.Open();
tran = connection.BeginTransaction();
//Insert new users into TIUser
SqlDataAdapter TIUser_adapter = new SqlDataAdapter();
string queryString = "SELECT * FROM TIUser WHERE 1 = 0";
TIUser_adapter.SelectCommand = new SqlCommand(queryString, connection, tran);
TIUser_adapter.Fill(dtNewUsers);
dtNewUsers.AcceptChanges();
TIUser_adapter.Update(dtNewUsers);
tran.Commit();
}
catch (System.Exception ex)
{
tran.Rollback();
throw ex;
}
finally
{
connection.Close();
}
GeneralClassLibrary 是我们在这里用于许多事情的类库；这里它只是在数据库上执行一个 SELECT 语句.TIUser 是数据库表.数据表 dtNewUsers 包含一行.我已经通过调试代码并在 ImportRow 完成后检查 DataTable 来验证这一点.
GeneralClassLibrary is a class library we use here for a number of things; here it just executes a SELECT statement on a database. TIUser is the database table. The DataTable, dtNewUsers contains one row. I've verified that by debugging the code and inspecting the DataTable after the ImportRow is done.
按照 user3787557 的回复(谢谢！)，我更接近了，但我遇到了并发冲突.我正在开发一个数据库，我所做的只是插入一条记录，所以我不知道为什么会出现并发冲突.一种可能性:我通过添加两列来更改 DataTable dtNewUsers 的结构.但是，在进行更新之前，我删除了这些列.InsertCommand 很好；我已经在 SSMS 中检查过它并进行了解析.这是我的新代码:
After following the reply from user3787557 (THANK YOU!), I'm closer, but I'm getting a concurrency violation. I'm working on a development database, and all I'm doing is inserting a record, so I have no idea why there'd be a concurrency violation. One possibility: I alter the structure of the DataTable dtNewUsers by adding two columns. However, before I do the update, I remove those columns. The InsertCommand is fine; I've checked it in SSMS and it parses. Here's my new code:
using (SqlConnection connection = new SqlConnection(GeneralClassLibrary.GeneralConfigurationManager.ConnectionStrings["TrackitCN"].ConnectionString))
{
SqlTransaction tran = null;
connection.Open();
tran = connection.BeginTransaction();
try
{
//Create empty TIUser Data Adapter
SqlDataAdapter TIUser_adapter = new SqlDataAdapter();
SqlCommandBuilder TIUser_builder = new SqlCommandBuilder(TIUser_adapter);
string queryString = "SELECT * FROM TIUser WHERE 1 = 0";
TIUser_adapter.SelectCommand = new SqlCommand(queryString, connection, tran);
TIUser_adapter.InsertCommand = TIUser_builder.GetInsertCommand();
TIUser_adapter.UpdateCommand = TIUser_builder.GetUpdateCommand();
//Get new users from AllUserData
DataTable dtNewUsers = new DataTable();
TIUser_adapter.Fill(dtNewUsers);
DataColumn dcRowID = new DataColumn("RowID", typeof(Int32));
dcRowID.AutoIncrement = true;
dcRowID.AutoIncrementSeed = 1;
dcRowID.AutoIncrementStep = 1;
dtNewUsers.Columns.Add(dcRowID);
dtNewUsers.Columns.Add("MaxID"); string strQuery = "SELECT " + newID + " AS MaxID, LName + ', ' + FName AS FullName, Title, Phone, EMail AS EmailAddr, Fax, Department AS Dept, Office AS Location, [Login] AS Employee_ID FROM [User] WHERE StartDate >= CAST(DATEPART(year,getdate()) AS varchar) + '-' + CAST(DATEPART(month,getdate()) AS varchar) + '-' + CAST(DATEPART(day,getdate())-1 AS varchar)";
DataTable dtTemp = GeneralClassLibrary.GeneralDataAccessLayer.ExecuteSelect(strQuery, false, "AllUserDataCN");
foreach (DataRow row in dtTemp.Rows)
dtNewUsers.ImportRow(row);
//Make sure new users aren't already in Trackit
foreach (DataRow row in dtNewUsers.Rows)
{
row["UserID"] = (Convert.ToInt32(row["RowID"]) + Convert.ToInt32(row["MaxID"])).ToString();
DataTable dtOverlap = GeneralClassLibrary.GeneralDataAccessLayer.ExecuteSelect("SELECT * FROM TIUser WHERE Employee_ID = '" + row["Employee_ID"].ToString() + "'", false, "TrackitCN");
if (dtOverlap.Rows.Count > 0)
dtNewUsers.Rows.Remove(row);
}
//Remove MaxID and RowID Columns
dtNewUsers.Columns.Remove("MaxID");
dtNewUsers.Columns.Remove("RowID")
TIUser_adapter.Update(dtNewUsers);
tran.Commit();
}
catch (System.Exception ex)
{
GeneralClassLibrary.GeneralEmail.ExceptionNotification(System.Reflection.Assembly.GetExecutingAssembly().ToString(), ex.Message, ex.StackTrace);
tran.Rollback();
throw ex;
}
finally
{
connection.Close();
}
}
我得到的堆栈跟踪是:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at UpdateTrackitUsers.Program.Main(String[] args) in c:\Users\06717\Documents\Visual Studio 2012\Projects\UpdateTrackitUsersConsole\UpdateTrackitUsersConsole\Program.cs:line 91
第 87 行是:
TIUser_adapter.Update(dtNewUsers);
推荐答案
这是对我有用的代码.基本上，您在代码中缺少的是使用 SqlCommandBuilder 创建 InsertCommand 和 UpdateCommand.还 GET RID 的 AcceptChanges().这将导致新行不会发送到数据库.最后一件事:确保将 MDF 数据库文件(如果您正在使用一个)属性标记为请勿复制"，否则每次编译时数据库都会被覆盖并且您看不到更改.
This is the code that worked for me. Basically what you're missing on your code is to use SqlCommandBuilder to create InsertCommand and UpdateCommand. Also GET RID of AcceptChanges(). That is going to cause the new row to NOT be sent to the database. One last thing: make sure you mark the MDF database file (if you're using one) properties with "Do Not Copy" otherwise the database gets overridden every time you compile and you can't see your changes.
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.SelectCommand = new SqlCommand(queryString, connection);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
DataTable dt = new DataTable();
adapter.Fill(dt);
DataRow row = dt.NewRow();
row["RegionID"] = 5;
row["RegionDescription"] = "Some region";
dt.Rows.Add(row);
//dt.AcceptChanges();
int counter = adapter.Update(dt);
}
这篇关于ADO.Net INSERT 不插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
