无法获取提交到SQL Server的数据
本文关键字:Server 数据 SQL 获取 提交 | 更新日期: 2023-09-27 18:18:00
我一直在网上寻找答案,但还没有找到。
我有一个小的控制台应用程序,我试图插入一些数据到SQL Server数据库(.mdf
数据库文件),一切运行没有错误,但是当我在服务器资源管理器中打开DB后,数据不存在。
这是代码:
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["FrameBudgetDB"].ToString()))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open();
cmd.CommandText = string.Format("SELECT TOP 1 category_id FROM businesses WHERE '{0}' LIKE CONCAT('%',description,'%')", transDescription.Replace("'", "''"));
SqlDataReader reader = cmd.ExecuteReader();
try
{
if (reader.HasRows)
{
while (reader.Read())
{
categoryId = (int)reader[0];
}
}
else
{
categoryId = 44; // Unknown
}
}
finally
{
reader.Close();
}
// Get Transaction Type
int transTypeId = 0;
cmd.CommandText = string.Format("SELECT trans_type_id FROM transaction_types WHERE description = '{0}'", transType);
reader = cmd.ExecuteReader();
try
{
if (reader.HasRows)
{
while (reader.Read())
{
transTypeId = (int)reader[0];
}
}
}
finally
{
reader.Close();
}
SqlTransaction trans = conn.BeginTransaction("InsertTransactiolns");
try
{
cmd.Transaction = trans;
cmd.CommandText = string.Format(
"BEGIN " +
"IF NOT EXISTS(SELECT * FROM transactions " +
"WHERE transaction_date = '{0}' " +
"AND description = '{1}' " +
"AND trans_type_id = {2} " +
"AND amount = {3} " +
"AND(category_id = {5} OR previous_category_id = {5} )) " +
"BEGIN " +
"INSERT INTO transactions(transaction_date, description, trans_type_id, import_date, category_id, amount) " +
"VALUES('{0}', '{1}', {2}, '{4}', {5}, {3}) " +
"END " +
"END", transDate, transDescription.Replace("'", "''"), transTypeId, amount, DateTime.Now, categoryId);
rowsInserted = rowsInserted + cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
try
{
trans.Rollback();
}
catch (Exception e2)
{
Console.WriteLine(e2.Message);
}
}
}
}
数据库连接字符串为:
<connectionStrings>
<add name="FrameBudgetDB"
connectionString="Data Source=(LocalDB)'MSSQLLocalDB;AttachDbFilename=|DataDirectory|FrameBudget.mdf;Integrated Security=True;Connect Timeout=30"
providerName="System.Data.SqlClient" />
</connectionStrings>
,数据目录为:
AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"'FrameBudget'"));
真的不清楚为什么你有一个TransactionScope实例和一个调用BeginTransaction。我只会用其中一个。然而这一行
using (TransactionScope scope = new TransactionScope())
{
...
如果
成功,则需要完成。
scope.Complete();
}
如果不调用Complete,退出using块意味着回滚。
我将通过调用
SqlTransaction trans = conn.BeginTransaction("InsertTransactiolns");
和相对的Rollback或Commit调用只留下TransactionScope实例(处理起来简单得多)。
作为旁注。您的代码容易受到Sql注入的攻击。该字符串。格式是字符串连接的一种形式我们都知道用字符串连接的方式来构建sql查询