接受用户输入并以分层方式将其存储在C＃应用程序的数据库中 [英] Accepting user input and storing it in a hierarchical manner in database in C# application
问题描述
我是初学者，正在尝试开发一个C＃窗体应用程序。
我准备了在数据库中存储3个输入的代码：
3个字段是：lvl，posid，upperposid。
我现在需要修改代码，这样只有在upperposid上才会接受用户输入已经存在于数据库中。并且upperposid值尚未存储在数据库中。只存储一个upperposid值，用户输入的所有细节都将是upperposid的子元素
例如，如果用户输入的内容为：
lvl = 2，posid =child和upperposid =parent，
然后只有值parent在upperposid列中至少存在一次才会接受数据。
另外一个例子，如果用户提供输入：
lvl =3
posid = CIV
upperposid =RET
然后，如果RET在upperposid中至少存在一次，则只接受并存储数据栏目。
请指导。如果可以的话，请提供C＃代码。我在下面给出了代码。谢谢
我的尝试：
I am a beginner, trying to develop a C# windows form application.
I have prepared the code to store 3 inputs in a database:
3 fields are: lvl, posid, upperposid.
I need to now modify the code such that the user input will be accepted only if the "upperposid" already exists in the database. And "upperposid" values are not already stored in database. Only one "upperposid" value will be stored and all the details entered by user will be children of that "upperposid"
For example, if user gives input as:
lvl=2, posid="child" and upperposid="parent",
then data will only be accepted if the value "parent" exists atleast once in upperposid column.
So as another example, if user gives input:
lvl="3"
posid="CIV"
upperposid="RET"
Then data will only be accepted and stored if "RET" exists at least once in "upperposid column".
Please guide on this. Please provide the C# code itself, if you can. I have given code below. Thanks
What I have tried:
//Code:
//This is Class called "WBSMASTERDB"
namespace Actual_Project
{
public class WBSMASTERDB
{
public static SqlConnection GetConnection()
{
string connStr = @"Data Source=
(LocalDB)\v11.0;AttachDbFilename=C:\Users\ABC\Documents\Visual Studio
2013\Projects\Actual Project\Actual Project\ActualProj_DB.mdf;Integrated
Security=True;";
SqlConnection conn = new SqlConnection(connStr);
return conn;
}
public static void AddData(string posid, string lvl, string upperposid)
{
string insStmt = "INSERT INTO WBS_MASTER (posid, upperposid,lvl)
VALUES (@lvl, @upperposid, @posid)";
SqlConnection conn = GetConnection();
SqlCommand insCmd = new SqlCommand(insStmt, conn);
insCmd.Parameters.AddWithValue("@posid", posid);
insCmd.Parameters.AddWithValue("@upperposid", upperposid);
insCmd.Parameters.AddWithValue("@lvl", lvl);
try { conn.Open(); insCmd.ExecuteNonQuery(); }
catch (SqlException ex) { throw ex; }
finally { conn.Close(); }
}
public static List<wbsmastercls> GetData()
{
List<wbsmastercls> DataList = new List<wbsmastercls>();
SqlConnection conn = GetConnection();
string selStmt = "SELECT * FROM WBS_MASTER";
SqlCommand selCmd = new SqlCommand(selStmt, conn);
try
{
conn.Open();
SqlDataReader reader = selCmd.ExecuteReader();
while (reader.Read())
{
WBSMASTERCLS apc = new WBSMASTERCLS();
apc.posid = (string)reader["posid"].ToString();
apc.upperposid = (string)reader["upperposid"].ToString();
apc.lvl = (string)reader["lvl"].ToString();
DataList.Add(apc);
}
reader.Close();
}
catch (SqlException ex) { throw ex; }
finally { conn.Close(); }
return DataList;
} } }
//Code
//This is Class called "WBSMASTER [Here the AddData() is called]"
namespace Actual_Project
{
public partial class WBSMASTER : Form
{
public WBSMASTER()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
WBSMASTERDB.AddData(textBox1.Text, textBox2.Text,textBox3.Text);
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
}
推荐答案
这样的事情：
Something like this:
string sql = "SELECT COUNT(*) FROM WBS_MASTER WHERE upperposid = @upperposid";
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ParametersAddWithValue("@upperposid", upperposid);
try
{
conn.Open();
Int32 count = (Int32) cmd.ExecuteScalar();
return (count > 0);
}
catch (Exception ex)
{
Debug.Print(ex.Message);
return false;
}
}
通常，如果在数据库中创建分层数据关系，则可以使用多个表来执行此操作。
在你的情况下，上位词可以更好地表示为一个单独的值列表，无论你以何种方式创建上位值，你都可以管理它的内容。
另一个表是lvl和posid值以及对包含upperposid的表的引用。
它们应该是两个表之间的外键关系，因此你不能添加任何不在upperposid中的值。
请注意，这全部由SQL处理，而不是您的代码。
但是，您的代码应该使用upperposid表来提供列表可接受的输入值 - 因此用户不会拼错或以其他方式弄乱。
Generally, if your creating hierarchical data relationships in database, you have more than a single table to do it.
In your case, upperposid could better be represented as a separate list of values which you manage as to its content in whatever way you create upperposid values.
The other table would be the lvl and posid values along with a reference to the upperposid containing table.
Their should be a foreign-key relationship between the two tables so you cannot add any values that aren't in upperposid.
Note that this is all handled by SQL, not your code.
Your code should, however, use the upperposid table to offer a list of acceptable values for input - so the user cannot misspell or otherwise mess things up.
这篇关于接受用户输入并以分层方式将其存储在C＃应用程序的数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
