问题描述
您好我的名字是vishal我想知道如何使用sql server 2008从c＃windows窗体中的另一个表中获取我的全局变量接受值？所以我有一个名为Mini Project的应用程序，我也有一个名为： frmLogin 的登录表单。下面给出的是 c＃code ：
Hi my name is vishal i was wondering on how to make my global variable accept value from another table in c# windows forms with sql server 2008? So i have a application named:Mini Project,i also have login form named:frmLogin. Given below is it's c# code:
namespace Mini_Project
{
public partial class frmLogin : Form
{
public frmLogin()
{
InitializeComponent();
}
private void frmLogin_Load(object sender, EventArgs e)
{
}
private bool ManagerUser(string username, string password)
{
bool success = false;
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd = new SqlCommand("Select * from [dbo].[ManagerDetail2] where username='"+txtUsername.Text+"' and password='"+txtPassword.Text+"' and LoginAttempts< 3", conn);
SqlDataReader rd = cmd.ExecuteReader();
while(rd.Read())
{
success=true;
Module.MUser_ID = Convert.ToInt32(rd[0].ToString());
Module.MUserName = rd[1].ToString();
}
rd.Close();
conn.Close();
return success;
}
private bool ValidateUser(string username, string password)
{
bool success = false;
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd = new SqlCommand("Select * from [dbo].[UserDetail2] where username='"+txtUsername.Text+"' and password='"+txtPassword.Text+"' and LoginAttempts< 3", conn);
SqlDataReader rd = cmd.ExecuteReader();
while(rd.Read())
{
success=true;
Module.User_ID = Convert.ToInt32(rd[0].ToString());
Module.UserName = rd[1].ToString();
}
rd.Close();
conn.Close();
return success;
}
private void btnLogin_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
if ((txtPassword.Text == "password") && (txtUsername.Text.ToLower() == "admin"))
{
Module.AUser_ID=1;
MDIParent1 h = new MDIParent1();
h.Show();
this.Close();
}
else
{
string username = txtUsername.Text;
string password = txtPassword.Text;
bool validUser = ValidateUser(username, password);
bool validmanager = ManagerUser(username, password);
if (validUser)
{
cmd = new SqlCommand("Update [dbo].[UserDetail2] set LoginAttempts=0 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
Module.User_ID = 1;
MDIParent1 m = new MDIParent1();
m.Show();
this.Close();
}
if (validmanager)
{
cmd = new SqlCommand("Update [dbo].[ManagerDetail2] set LoginAttempts=0 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
Module.MUser_ID = 1;
MDIParent1 g = new MDIParent1();
g.Show();
this.Close();
}
else
{
cmd = new SqlCommand("Update [dbo].[UserDetail2] set LoginAttempts=LoginAttempts+1 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("Update [dbo].[ManagerDetail2] set LoginAttempts=LoginAttempts+1 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
MessageBox.Show("Invalid user name or password. Please try tomorow ", "Task", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtUsername.Focus();
}
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
Application.Exit();
}
上面的代码工作正常！所以使用默认用户名： admin 和默认密码：密码我登录/以管理员身份进入应用程序，然后我为任务创建一个新的管理员。下面是我的c＃表格代码（ frmManager ）：
The above code works OK! So using default username:admin and default password:password i login/enter into application as admin,then i create a new manager for tasks. Given below is my c# code of form(frmManager):
namespace Mini_Project
{
public partial class frmManager : Form
{
public int bGenId = -1;
public frmManager()
{
InitializeComponent();
}
private void btnCreate_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd = new SqlCommand("Insert into [dbo].[ManagerDetail2](manager_first_name,manager_last_name,manager_dob,manager_sex,email,username,password,status,created_by,LoginAttempts,row_upd_date)" + "Values(@manager_first_name,@manager_last_name,@manager_dob,@manager_sex,@email,@username,@password,@status,@created_by,@LoginAttempts,GetDate()); Select @autoGenId=SCOPE_IDENTITY();", conn);
cmd.Parameters.AddWithValue("@manager_first_name", txtFName.Text);
cmd.Parameters.AddWithValue("@manager_last_name", txtLName.Text);
cmd.Parameters.AddWithValue("@manager_dob", dtDOB.Value);
if (cboSex.SelectedIndex == 0)
{
cmd.Parameters.AddWithValue("@manager_sex", "Male");
}
else if (cboSex.SelectedIndex == 1)
{
cmd.Parameters.AddWithValue("@manager_sex", "Female");
}
else if (cboSex.SelectedIndex == 2)
{
cmd.Parameters.AddWithValue("@manager_sex", "Transgender");
}
cmd.Parameters.AddWithValue("@email", txtEmailID.Text);
cmd.Parameters.AddWithValue("@username", txtUsername.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@Created_by", 1);
cmd.Parameters.AddWithValue("@LoginAttempts", 0);
cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
bGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
cmd = new SqlCommand("Update [dbo].[ManagerDetail2] set LoginAttempts=0 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
((MDIParent1)this.MdiParent).updateUserActivities(bGenId, 2, txtFName.Text.ToString() + "Manager detail was added successfully");
MessageBox.Show("Manager Detail was added successfully", "Task", MessageBoxButtons.OK, MessageBoxIcon.Information);
conn.Close();
this.Close();
}
上面的代码工作正常！下面给出的是我在sql server 2008中名为： ManagerDetail2 的表结构，我从表单中插入值（frmManager）：
ColumnName DataType AllowNulls
manager_id（自动增量主键）Int No
manager_first_name nvarchar（50）是
manager_last_name nvarchar（50）是
manager_dob日期是
manager_sex nvarchar（20）是
电子邮件nvarchar（60）是
用户名nvarchar（25）是
密码nvarchar（15）是
状态位是
created_by Int Yes
LoginAttempts Int Yes
row_upd_date datetime Yes
使用ManagerDetail2中的用户名和密码我登录/进入应用程序作为管理员并创建新用户（普通用户）将来到我之下。以下是表格的c＃代码（ frmUser ）：
The above code works fine! Given below is my structure of table named:ManagerDetail2 in sql server 2008 to which i insert values from form(frmManager):
ColumnName DataType AllowNulls
manager_id(auto-increment primary key) Int No
manager_first_name nvarchar(50) Yes
manager_last_name nvarchar(50) Yes
manager_dob date Yes
manager_sex nvarchar(20) Yes
email nvarchar(60) Yes
username nvarchar(25) Yes
password nvarchar(15) Yes
status bit Yes
created_by Int Yes
LoginAttempts Int Yes
row_upd_date datetime Yes
Using username and password from ManagerDetail2 i login/enter into application as a manager and create new users(normal user) who will come under me.Given below is c# code of form(frmUser):
namespace Mini_Project
{
public partial class frmUser : Form
{
public int autoGenId = -1;
public frmUser()
{
InitializeComponent();
}
private void btnCreate_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd = new SqlCommand("Insert into [dbo].[UserDetail2](user_first_name,user_last_name,user_dob,user_sex,email,username,password,status,row_upd_date,created_by,LoginAttempts)" + "Values(@user_first_name,@user_last_name,@user_dob,@user_sex,@email,@username,@password,@status,GetDate(),@created_by,@LoginAttempts); Select @autoGenId=SCOPE_IDENTITY();", conn);
cmd.Parameters.AddWithValue("@user_first_name", txtFName.Text);
cmd.Parameters.AddWithValue("@user_last_name", txtLName.Text);
cmd.Parameters.AddWithValue("@user_dob", dtDOB.Value);
if (cboSex.SelectedIndex == 0)
{
cmd.Parameters.AddWithValue("@user_sex", "Male");
}
else if (cboSex.SelectedIndex == 1)
{
cmd.Parameters.AddWithValue("@user_sex", "Female");
}
else if (cboSex.SelectedIndex == 2)
{
cmd.Parameters.AddWithValue("@user_sex", "Transgender");
}
cmd.Parameters.AddWithValue("@email", txtEmailID.Text);
cmd.Parameters.AddWithValue("@username", txtUsername.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@created_by",Module.Manager);
cmd.Parameters.AddWithValue("@LoginAttempts", 0);
cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
autoGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
cmd = new SqlCommand("Update [dbo].[UserDetail2] set LoginAttempts=0 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
((MDIParent1)this.MdiParent).updateUserActivities(autoGenId, 1, txtFName.Text + "User detail was added successfully");
MessageBox.Show("User Detail was added successfully", "Task", MessageBoxButtons.OK, MessageBoxIcon.Information);
conn.Close();
this.Close();
}
以上代码仅在某种程度上正常工作！下面给出了表的结构： UserDetail2 在sql server 2008中我从表单中插入值（frmUser）：
ColumnName DataType AllowNulls
user_id（自动增量主键）Int No
user_first_name nvarchar（50）是
user_last_name nvarchar（50）是
user_dob日期是
user_sex nvarchar（20）是
电子邮件nvarchar（60）是
用户名nvarchar（25）是
密码nvarchar（15）是
状态位是
created_by Int Yes
LoginAttempts Int Yes
row_upd_date datetime Yes
以下是我的c＃类代码：模块
The above code works OK to some extent only! Given below is structure of table:UserDetail2 in sql server 2008 to which i insert values from form(frmUser):
ColumnName DataType AllowNulls
user_id(auto-increment primary key) Int No
user_first_name nvarchar(50) Yes
user_last_name nvarchar(50) Yes
user_dob date Yes
user_sex nvarchar(20) Yes
email nvarchar(60) Yes
username nvarchar(25) Yes
password nvarchar(15) Yes
status bit Yes
created_by Int Yes
LoginAttempts Int Yes
row_upd_date datetime Yes
Given below is my c# code of class named:Module
namespace Mini_Project
{
class Module
{
public static int AUser_ID;
public static int Admin
{
get { return AUser_ID; }
set { AUser_ID = value; }
}
public static int User_ID;
public static int User
{
get { return User_ID; }
set { User_ID = value; }
}
public static int MUser_ID;
public static int Manager
{
get { return MUser_ID; }
set { MUser_ID = value; }
}
public static string UserName="";
public static string GlobalUser
{
get { return UserName; }
set { UserName = value; }
}
public static string MUserName="";
public static string GlobalManager
{
get { return MUserName; }
set { MUserName = value; }
}
public static string AUserName="";
public static string GlobalAdmin
{
get { return AUserName; }
set { AUserName = value; }
}
}
上面的代码工作正常！仅在某种程度上。
我面临的问题是在作为经理进入应用程序后，当我在我下面创建一个新用户（普通用户）时，我得到的值 1 对于我的字段中的所有用户 created_by 在表格中： UserDetail2 。
我想要的是当我作为经理（使用ManagerDetail的用户名和密码）进入应用程序并根据我输入的经理的ID（ manager_id ）我应该得到表格中 created_by 的字段中的manager_id值的值相同： UserDetail2 。
但是我为所有用户和我的领域获得了价值1：表格中的created_by：UserDetail2。
任何人都可以帮助我！任何人都可以帮助我/指导我达到了我要求的结果！任何人都可以告诉我在我的c＃代码中需要做哪些修改以及在哪里！？任何帮助/指导解决这个问题将不胜感激！
The above code works OK! to some extent only.
The problem i am facing is after entering into application as a manager and when i create a new user(normal user) under me i get value of 1 for all users in my field created_by in table:UserDetail2.
What i want is when i enter into application as a manager(using username and password from ManagerDetail) and based on my id(manager_id) of entered manager i should get the same value of manager_id value in my field created_by in table:UserDetail2.
But instead i am getting value 1 for all users and also in my field:created_by in table:UserDetail2.
Can anyone help me please!Can anyone help me/guide me to achieve my required result! Can anyone tell me what modifications must i need to do in my c# code and where!? Any help/guidance in solving of this problem would be greatly appreciated!
推荐答案
这篇关于如何使用sql server 2008使全局变量接受来自c＃windows窗体中另一个表的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！