只有一条写入sqlserver+组合框的记录未刷新
本文关键字：记录 刷新 组合 sqlserver+ 一条 | 更新日期: 2023-09-27 18:26:21
我是.net编程的新手，在将intput从表单写入sql server时遇到问题。只有一条记录被写入数据库，对于其他记录，它会显示"数据未写入数据库"。此外，在数据写入数据库后，我的cmbbox不会更新，尽管我运行UpdateInitialWeek（）方法。我不想写"意大利面条代码"，我希望我的程序是结构化的。因此，我们非常感谢任何建议（我已经知道最好使用实体框架来处理数据，这是我最终会学到的；）。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Write_to_database
{
public partial class WriteToDatabase : Form
{
SqlServer sql = new SqlServer();
public WriteToDatabase()
{
sql.OpenSqlConnection();
InitializeComponent();
this.UpdateInitialWeek();
sql.CloseSqlConnection();
}
private void btnWrite_Click(object sender, EventArgs e)
{
WriteToOutput(sql.OpenSqlConnection());
if (txtMilitaryPress.Text != "")
WriteToOutput(sql.InsertToTraining(ConvertDate(dtMilitaryPress.Value), "Military Press", txtMilitaryPress.Text.ToString(), txtRepMilitaryPress.Text.ToString(), cmbMilitaryPress.Text.ToString()));
if (txtDeadlift.Text != "")
WriteToOutput(sql.InsertToTraining(dtDeadlift.Value.ToString(), "Deadlift", txtDeadlift.Text.ToString(), txtRepDeadlift.Text.ToString(), cmbDeadlift.Text.ToString()));
if (txtBenchPress.Text != "")
WriteToOutput(sql.InsertToTraining(dtBenchPress.Value.ToString(), "Bench Press", txtBenchPress.Text.ToString(), txtRepBenchPress.Text.ToString(), cmbBenchPress.Text.ToString()));
if (txtBackSquat.Text != "")
WriteToOutput(sql.InsertToTraining(dtBackSquat.Value.ToString(), "Back Squat", txtBackSquat.Text.ToString(), txtRepBackSquat.Text.ToString(), cmbBackSquat.Text.ToString()));
this.UpdateInitialWeek();
WriteToOutput(sql.CloseSqlConnection());
}
//Write output to textbox
public void WriteToOutput(string output)
{
this.txtOutput.AppendText(output + Environment.NewLine);
}
//Convert date for sql server
public string ConvertDate(DateTime date)
{
return date.ToString("MM/dd/yyyy");
}
//Update comboboxes to set right training week
public void UpdateInitialWeek()
{
this.cmbBackSquat.Text = CheckWeek(sql.GetDataTraining("Back Squat"));
this.cmbBenchPress.Text = CheckWeek(sql.GetDataTraining("Bench Press"));
this.cmbDeadlift.Text = CheckWeek(sql.GetDataTraining("Deadlift"));
this.cmbMilitaryPress.Text = CheckWeek(sql.GetDataTraining("Military Press"));
}
//Training week +1 except for week 4 --> back to 1
public string CheckWeek(string trainingWeek)
{
int trWeek = Int32.Parse(trainingWeek);
if (trWeek == 4)
trWeek = 1;
else
trWeek += 1;
return trWeek.ToString();
}
}
public class SqlServer
{
SqlConnection con = new SqlConnection("Data Source=WINSERVER;Initial Catalog=TRAINING;Integrated Security=SSPI;");
public string OpenSqlConnection()
{
try
{
con.Open();
return "Connection to: " + "'Data Source=WINSERVER;Initial Catalog=TRAINING;Integrated Security=SSPI;'" + " successful.";
}
catch
{
return "Connection to: " + "'Data Source=WINSERVER;Initial Catalog=TRAINING;Integrated Security=SSPI;'" + " failed.";
}
}
public string CloseSqlConnection()
{
try
{
con.Close();
return "Connection to: " + "'Data Source=WINSERVER;Initial Catalog=TRAINING;Integrated Security=SSPI;'" + " successfully closed";
}
catch
{
return "Connection to: " + "'Data Source=WINSERVER;Initial Catalog=TRAINING;Integrated Security=SSPI;'" + " not closed.";
}
}
public string InsertToTraining(string date, string lift, string weight, string reps, string week)
{
try
{
using (SqlCommand command = new SqlCommand("INSERT INTO LIFT_HISTORY VALUES(@date,@lift,@weight,@reps,@week)", con))
{
command.Parameters.Add(new SqlParameter("weight", weight.ToString())); //SqlDbType.NVarChar
command.Parameters.Add(new SqlParameter("date", date.ToString()));
command.Parameters.Add(new SqlParameter("week", week.ToString()));
command.Parameters.Add(new SqlParameter("reps", reps.ToString()));
command.Parameters.Add(new SqlParameter("lift", lift.ToString()));
command.ExecuteNonQuery();
}
return "Data successfully written to database.";
}
catch
{
return "Data not written to database.";
}
}
public string GetDataTraining(string where)
{
int trainingWeek;
//using (SqlCommand command = new SqlCommand("SELECT WEEK_OF_TRAINING FROM dbo.LIFT_HISTORY WHERE [DATE] = (SELECT MAX([DATE]) FROM dbo.LIFT_HISTORY WHERE LIFT = 'Deadlift') AND LIFT = 'Deadlift')", con))
using (SqlCommand command = new SqlCommand("SELECT WEEK_OF_TRAINING FROM dbo.LIFT_HISTORY WHERE LIFT = '"+ where +"' ORDER BY [DATE] DESC", con))
{
trainingWeek = (Int32)command.ExecuteScalar();
}
return trainingWeek.ToString();
}
}
}
您的代码有一些问题，但现在您仍在学习，这是可以的，例如：
public WriteToDatabase()
{
sql.OpenSqlConnection();
InitializeComponent();
this.UpdateInitialWeek();
sql.CloseSqlConnection();
}
应该是：
public void WriteToDatabase()
{
sql.OpenSqlConnection();
InitializeComponent();
this.UpdateInitialWeek();
sql.CloseSqlConnection();
}
这是因为你没有返回任何东西，相反，你应该声明你返回的变量的类型
首先，我建议您使用面向层的编码。例如：
我将开始创建一个实体类：
namespace Entities
{
public class LiftingStory
{
public string Weight { get; set; }
public string Date { get; set; }
public string Week { get; set; }
public string Reps { get; set; }
public string Lift { get; set; }
}
}
然后开始创建"数据访问"层
using System.Data;
using System.Configuration;
using Entities;
namespace DataAccess
{
public class DataLiftingStory
{
public bool insertLifting(LiftingStory obj) //correction: should be LiftingStory instead of DataLiftingStory because I'm retrieving a LiftingStory objecto to be proccesed.
{
//we're creating a new connection to Database, but it will need string parameter
//you can get it directly from the connectionstring on the Web.config in this way
// ConfigurationManager.ConnectionStrings["nameParameterOfYourConnString"].ConnectionString
//instead of that I'll do it with a string for making more easier to understand
string connectionString = "Data Source=WINSERVER;Initial Catalog=TRAINING;Integrated Security=SSPI;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
//now I'll create the command
using (SqlCommand command = new SqlCommand())
{
//so now I've to say what type of command I'm making up. In your case is "Text" because you're being explicit with the query
//I suggest you to use stored procedures btw.
command.CommandType = CommandType.Text;
//now the command text will be your query
command.CommandText = "INSERT INTO LIFT_HISTORY VALUES(@date,@lift,@weight,@reps,@week)";
//now we set the parameters
command.Parameters.Add(new SqlParameter("date", obj.Date));
command.Parameters.Add(new SqlParameter("lift", obj.Lift));
command.Parameters.Add(new SqlParameter("weight", obj.Weight));
command.Parameters.Add(new SqlParameter("reps", obj.Reps));
command.Parameters.Add(new SqlParameter("week", obj.Week));
try
{
command.Connection = connection;
command.Connection.Open();
//now we're executing the query and if we get more than 0 that will means that it inserted or modified a row
//then it will return true and going out from method.
if (command.ExecuteNonQuery() > 0)
return true;
}
catch (Exception)
{
//If it fails return false
return false;
throw;
}
finally
{
//then we close the connection
command.Connection.Close();
}
//if not failed but it didn't anything, it will return false
return false;
}
}
}
现在是商业的简单部分。
using System.Web;
using Entities;
using DataAccess;
namespace Business
{
public class BusinessLiftingStory
{
public bool insertLifting(LiftingStory obj)
{
DataLiftingStory dataLifting = new DataLiftingStory();
dataLifting.insertLifting(obj);
}
}
}
因此，最后一步是在"视图层"中填充对象，并从Business:调用方法
LiftingStory obj = new LiftingStory();
obj.Weight = string.Empty;
obj.Date = string.Empty; //put values from comboBoxes
obj.Reps = string.Empty;
obj.Lift = string.Empty;
obj.Week = string.Empty;
BusinessLiftingStory busObj = new BusinessLiftingStory();
busObj.insertLifting(obj);
组合框不会刷新数据，因为DataBind（）方法，不要忘记，当你想"重新绘制"你的组合框时，你必须设置DataSource = null，然后再次获取数据源，然后再获取DataBind。
如果需要，可以使用Init（）方法。
private void Init()
{
cmbWeight.DataSource = null;
cmbWeight.DataSource = //new Datasource
//dont forget to set the values and text fields
cmbWeight.DataBind();
}
这样，你的代码中就会有一个订单，我希望它能对你有所帮助。
问候：）
附言：很抱歉回答得太长了。
也许可以尝试将异常详细信息打印到返回字符串或控制台等。
而不是接住{return"数据未写入数据库"；}
catch( Exception ex )
{
return "Data not written to database." + ex.Message;
}
https://msdn.microsoft.com/en-us/library/system.exception.message（v=vs.110）.aspx