如何使用sql server2008通过c＃windows窗体中的sql select查询 [英] how to use not in sql select query through c# windows forms with sql server2008

问题描述

我的名字是vishal。

在过去的10天里，我一直在讨论如何使用sql server2008从c＃windows表单中选择不使用sql select查询？

以下是我的一个表格的代码： frmPatient ：

hi my name is vishal.
For past 10days i have been breaking my head on how to use Not in sql select query from c# windows forms with sql server2008?
Given below is code of one of my form:frmPatient:

 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 DRRS_CSharp
{
    public partial class frmPatient : Form
    {
 int pUserID;
 public frmPatient()
        {
            InitializeComponent();
            SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true");
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            string SqlDataPull = ("Select p.doctor_id as doctor_id,n.doctor_first_name as doctor_fname,n.doctor_last_name as doctor_lname,n.doctor_middle_name as doctor_mname from doctordetail n,doctor p where n.doctor_id=p.doctor_id and n.status=1");
            SqlCommand cmd = new SqlCommand(SqlDataPull);
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                SqlDataPull = dr[0].ToString() + dr[1].ToString() + dr[2].ToString() + dr[3].ToString();
                cboDoctor.Items.Add(SqlDataPull);
            }
            dr.Close();
        }
 private void btnCreate_Click(object sender, EventArgs e)
        {
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true");
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            int autoGenId = -1;
cmd = new SqlCommand("Insert into patient_id(patient_sex,patient_dob,row_upd_date,user_id)" + "Values(@patient_sex,@patient_dob,GetDate(),@user_id);Select @autoGenId = SCOPE_IDENTITY();",conn);
                if (cboSex.SelectedIndex == 0)
                {
                    cmd.Parameters.AddWithValue("@patient_sex", "M");
                }
                else
                {
                    cmd.Parameters.AddWithValue("@patient_sex", "F");
                }
                cmd.Parameters.AddWithValue("@patient_dob", dtDOB.Value);
                cmd.Parameters.AddWithValue("@user_id", pUserID);
                cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                autoGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
 cmd = new SqlCommand("Update patient_name set status=0 where patient_id=" + patientID, conn);
cmd = new SqlCommand("Insert into patient_name(patient_id,patient_first_name,patient_middle_name,patient_last_name,virology,status,row_upd_date,user_id)" + "Values(@patient_id,@patient_first_name,@patient_middle_name,@patient_last_name,@virology,@status,GetDate(),@user_id)", conn);
                cmd.Parameters.AddWithValue("@patient_id",autoGenId);
                cmd.Parameters.AddWithValue("@patient_first_name", txtFName.Text.ToString());
                cmd.Parameters.AddWithValue("@patient_middle_name", txtMName.Text.ToString());
                cmd.Parameters.AddWithValue("@patient_last_name", txtLName.Text.ToString());
                cmd.Parameters.AddWithValue("@virology", cboVirology.SelectedIndex);
                cmd.Parameters.AddWithValue("@status", 1);
                cmd.Parameters.AddWithValue("@user_id", pUserID);
                cmd.ExecuteNonQuery();
if ((txtHNumber.Text != "") || (txtMNumber.Text != ""))
            {
                    cmd = new SqlCommand("Update patient_contact set status=0 where patient_id=" +patientID, conn);
                }
                    cmd = new SqlCommand("Insert into patient_contact(patient_id,homenumber,mobilenumber,row_upd_date,status,user_id)" + "Values(@patient_id,@homenumber,@mobilenumber,GetDate(),@status,@user_id)", conn);
                    cmd.Parameters.AddWithValue("@patient_id",autoGenId);
                    cmd.Parameters.AddWithValue("@homenumber", txtHNumber.Text);
                    cmd.Parameters.AddWithValue("@mobilenumber", txtMNumber.Text);
                    cmd.Parameters.AddWithValue("@status", 1);
                    cmd.Parameters.AddWithValue("@user_id", pUserID);
                    cmd.ExecuteNonQuery();    
            }
cmd = new SqlCommand("Update address set status=0 where patient_id=" + patientID, conn);
cmd = new SqlCommand("Insert into address(apartment_name,door_number,street_name_1,Street_name_2,Street_name_3,village,city,state,country,apartment_number,row_upd_date,patient_id,status,pincode,user_id)" + "Values(@apartment_name,@door_number,@street_name_1,@Street_name_2,@Street_name_3,@village,@city,@state,@country,@apartment_number,GetDate(),@patient_id,@status,@pincode,@user_id)", conn);
                cmd.Parameters.AddWithValue("@apartment_name", txtApartmentName.Text.ToString());
                cmd.Parameters.AddWithValue("@door_number", txtDoorNo.Text);
                cmd.Parameters.AddWithValue("@street_name_1", txtStreet1.Text.ToString());
                cmd.Parameters.AddWithValue("@Street_name_2", txtStreet2.Text.ToString());
                cmd.Parameters.AddWithValue("@Street_name_3", txtStreet3.Text.ToString());
                cmd.Parameters.AddWithValue("@village", txtVillageArea.Text.ToString());
                cmd.Parameters.AddWithValue("@city", txtCity.Text.ToString());
                cmd.Parameters.AddWithValue("@state", txtState.Text.ToString());
                cmd.Parameters.AddWithValue("@country", txtCountry.Text.ToString());
                cmd.Parameters.AddWithValue("@apartment_number", txtApartmentNo.Text);
                cmd.Parameters.AddWithValue("@patient_id",autoGenId);
                cmd.Parameters.AddWithValue("@status", 1);
                cmd.Parameters.AddWithValue("@pincode", txtPCode.Text);
                cmd.Parameters.AddWithValue("@user_id", pUserID);
                cmd.ExecuteNonQuery();
cmd = new SqlCommand("Update doctorpatient set status=0 where patient_id=" + patientID, conn);
cmd = new SqlCommand("Insert into doctorpatient(patient_id,doctor_id,row_upd_date,status,user_id)" + "Values(@patient_id,@doctor_id,GetDate(),@status,@user_id)", conn);
                cmd.Parameters.AddWithValue("@patient_id",autoGenId);
                cmd.Parameters.AddWithValue("@doctor_id", cboDoctor.GetItemText(cboDoctor.SelectedIndex));
                cmd.Parameters.AddWithValue("@status", 1);
                cmd.Parameters.AddWithValue("@user_id", pUserID);
                cmd.ExecuteNonQuery();
((MDIParent1)this.MdiParent).updateUserActivities(autoGenId, 1, txtFName.Text.ToString() + "patient detail was added successfully");
            MessageBox.Show("patient Detail was added successfully", "DRRS", MessageBoxButtons.OK, MessageBoxIcon.Information);
            this.Close();
}

正如你可以看到我如何在frmDialyzer中将我的组合框（cboPatientID）从表server_id和patient_name从sql server2008填充到c＃windows窗体中

As you can see how i populate my combobox(cboPatientID) in frmDialyzer from tables patient_id and patient_name from sql server2008 into c# windows forms

sing 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;
using System.IO;
using System.Drawing.Printing;
namespace DRRS_CSharp
{
    public partial class frmDialyzer : Form
    {
        int dStepIndex;
        int pUserID;
string PatientPull = ("select p.patient_id as patient_id,n.patient_first_name as patient_fname,n.patient_last_name as patient_lname from patient_name n,patient_id p where n.patient_id=p.patient_id and n.status =1 and p.patient_id Not in (Select patient_id from dialyser where deleted_status=0)");
            SqlCommand cmd = new SqlCommand(PatientPull);
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                PatientPull = dr[1].ToString() + "_" + dr[2].ToString() + "(" + "0000" + dr[0].ToString() + ")";
                  cboPatientID.Items.Add(PatientPull);
            }
            dr.Close(); 
}

下面给出的是我在 frmDialyzer 中的sql select查询中的查询，这是我的表单的名称，其中有一个名为的组合框： cboPatientID

Given below is my query in sql select query in frmDialyzer which is name of my form which has a combobox named:cboPatientID

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;
using System.IO;
using System.Drawing.Printing;
namespace DRRS_CSharp
{
    public partial class frmDialyzer : Form
    {
public frmDialyzer()
        {
            InitializeComponent();
            string PatientPull = ("select p.patient_id as patient_id,n.patient_first_name as patient_fname,n.patient_last_name as patient_lname from patient_name n,patient_id p where n.patient_id=p.patient_id and n.status =1 and p.patient_id Not In(Select patient_id from dialyser where deleted_status=0 and closed_status=0)");
            SqlCommand cmd = new SqlCommand(PatientPull);
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                PatientPull = dr[1].ToString() + "_" + dr[2].ToString() + "(" + "0000" + dr[0].ToString() + ")";
                  cboPatientID.Items.Add(PatientPull);
            }
            dr.Close(); 
}

以下是sql server2008中我的表 dialyser 的结构。

ColumnName DataType AllowNulls

mfr_ref_number nvarchar（20）是

mfr_lot_number nvarchar（20）是

mfr_date日期是

exp_date日期是

packed_volume Int是

patient_id Int Yes

start_date datetime是

end_date datetime是

row_upd_date datetime是

制造商nvarchar（50）是

dialyzer_size nvarchar（20）是

deleted_status位是

deleted_date datetime是

dialyserID nvarchar（20）是

closed_status位是

closed_date datetime是

agn Int否（自动增加主键）

下面给出了sql server2008中表 patient_id 的结构

ColumnName DataType AllowNulls

patient_id Int否（自动增加主键）

patient_sex nvarchar（10）是

patient_dob date是

row_upd_date datetime是

user_id Int是

下面给出了sql server2008中表 patient_name 的结构。

ColumnName DataType AllowNulls

patient_id Int Yes

patient_first_name nvarchar（50）是

patient_middle_name nvarchar（50）是

patient_last_name nvarchar（50）是

病毒学Int是

row_upd_date datetime是

状态位是

agn Int否（自动增加主键）

user_id Int是

下面给出的是 frmDialyzer <中的代码/ b>其中我将值插入表 dialyser 并将透析器分配给患者：

Given below is structure of my table dialyser in sql server2008.
ColumnName DataType AllowNulls
mfr_ref_number nvarchar(20) Yes
mfr_lot_number nvarchar(20) Yes
mfr_date date Yes
exp_date date Yes
packed_volume Int Yes
patient_id Int Yes
start_date datetime Yes
end_date datetime Yes
row_upd_date datetime Yes
manufacturer nvarchar(50) Yes
dialyzer_size nvarchar(20) Yes
deleted_status bit Yes
deleted_date datetime Yes
dialyserID nvarchar(20) Yes
closed_status bit Yes
closed_date datetime Yes
agn Int No(Since auto-increment primary key)
Given below is structure of table patient_id in sql server2008
ColumnName DataType AllowNulls
patient_id Int No(Since auto-increment primary key)
patient_sex nvarchar(10) Yes
patient_dob date Yes
row_upd_date datetime Yes
user_id Int Yes
Given below is structure of table patient_name in sql server2008.
ColumnName DataType AllowNulls
patient_id Int Yes
patient_first_name nvarchar(50) Yes
patient_middle_name nvarchar(50) Yes
patient_last_name nvarchar(50) Yes
virology Int Yes
row_upd_date datetime Yes
status bit Yes
agn Int No(since auto-increment primary key)
user_id Int Yes
Given below is my code in frmDialyzer in which i insert values into table dialyser and assigning dialyzer to patient:

private void btnAssign_Click(object sender, EventArgs e)
        {
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true");
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            int autoGenId = -1;
            cmd = new SqlCommand("Insert into dialyser(dialyserID,manufacturer,mfr_ref_number,mfr_lot_number,mfr_date,exp_date,start_date,closed_status,deleted_status,packed_volume,dialyzer_size,patient_id,row_upd_date,user_id)" + "Values(@dialyserID,@manufacturer,@mfr_ref_number,@mfr_lot_number,@mfr_date,@exp_date,@start_date,@closed_status,@deleted_status,@packed_volume,@dialyzer_size,@patient_id,GetDate(),@user_id);Select @autoGenId = SCOPE_IDENTITY();", conn);
            cmd.Parameters.AddWithValue("@dialyserID", txtDID.Text.ToString());
            cmd.Parameters.AddWithValue("@manufacturer", cboManufacturer.Text.ToString());
            cmd.Parameters.AddWithValue("@mfr_ref_number", txtMFRRefNo.Text.ToString());
            cmd.Parameters.AddWithValue("@mfr_lot_number", txtMFRLotNo.Text.ToString());
            cmd.Parameters.AddWithValue("@mfr_date", dtMFRDate.Value);
            cmd.Parameters.AddWithValue("@exp_date", dtExpDate.Value);
            cmd.Parameters.AddWithValue("@start_date", dtStartDate.Value);
            cmd.Parameters.AddWithValue("@closed_status", 0);
            cmd.Parameters.AddWithValue("@deleted_status", 0);
            cmd.Parameters.AddWithValue("@packed_volume", txtPVol.Text.ToString());
            cmd.Parameters.AddWithValue("@dialyzer_size", cboequipmentType.Text.ToString());
            cmd.Parameters.AddWithValue("@patient_id", cboPatientID.SelectedIndex);
            cmd.Parameters.AddWithValue("@user_id", pUserID);
            cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            autoGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
            ((MDIParent1)this.MdiParent).updateUserActivities(autoGenId, 4, cboManufacturer.Text + "Dialyzer detail was added successfully");
             MessageBox.Show("Dialyzer data was successfully added to patient", "DRRS", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}

以上代码工作正常。

但我面临的问题是将透析器分配给特定患者进入表格来自组合框（cboPatientID）的透析器在frmDialyzer中通过c＃windows形式与sql server2008



我不应该在我的组合框列表中找到相同的病人（ cboPatientID ）在 frmDialyzer 中，一旦我的 frmDialyzer 再次加载，我得到了。

是否还需要其他细节？如果有的话请回复！？

任何人都可以帮助我吗？任何有关解决我的问题的帮助/指导将不胜感激。

The above code works OK.
But the problem i am facing is after assigning dialyzer to a particular patient into table dialyser from combobox(cboPatientID) in frmDialyzer through c# windows forms with sql server2008

I should not get the same patient in list of my combobox(cboPatientID) in frmDialyzer once my frmDialyzer forms loads again which i get.
Are there any other details required? If so reply please!?
Can anyone help me please?Any help/guidance in solving of my problem would be greatly appreciated.

推荐答案

where n.patient_id=p.patient_id and n.status =1 --this is a JOIN condition - use and inner join instead

and p.patient_id Not In(Select patient_id from dialyser where deleted_status=0 and closed_status=0)") -- This excludes deleted and closed

您无处选择患者！尝试添加





和n.patientID！= @SelectedPatientID



到你的where子句

Nowhere do you exclude the selected patient! try adding


And n.patientID != @SelectedPatientID

to your where clause

这里有几件事：



首先，如果这是你的好应用程序。重新启动并尝试设置一些结构。在GUI类中执行DataAccess图层通常是一个非常糟糕的主意。



如果（且仅当）我理解正确你得到了组合框或组合框中的非唯一名称会再次加载值并添加它们而不是替换它们。



在第一种情况下，将select语句更改为仅允许唯一值对于患者来说。



在后一种情况下，如果你想保持代码就好了。你应该在填写之前打电话给

a few things here:

First of all, if this is to be your "good" application. Restart and try to setup some structure. Doing DataAccess layers stuff in your GUI classes is generally a very bad idea.

If (and only if) I understand you correctly you get non-unique names in your combobox or your combobox loads the values again and ADDS them instead of replacing.

In the first case change the select statement to only allow unique values for the patients.

In the latter case, if you want to keep the code like it is. You should just call

cboPatientID.Items.Clear();

。



但是，连接到数据库时，您只需填充数据集，然后将数据集分配给组合框架datacontext（或我相信的winforms中的数据源）对象。

before filling it.

However, when connecting to the database you can just fill the dataset and then assign the dataset to the combobox datacontext (or datasource in winforms I believe) object.

cboPatientID.DataSource = [yourdataset].Tables[0].DefaultView;
cboPatientID.DisplayMember = "[columnname to the values that should be SHOWN]"; 
cboPatientID.ValueMember = "[columnname to the code values (can be the same as displaymember]"; 

有这样做的好处是，如果你将这个数据集重置（重新分配）到数据源，组合框就会被清除并为你重新填充。（与使用Items.Add（...）相反）



您可以选择快速修复（调用Clear功能），但我建议您查看您的架构并重新开始。有关N层设计的CP上有几篇文章。（看这里 [ ^ ] eg。）



希望这会有所帮助。

There are tutorials out there. The advantage of this is that if you reset (re-assign) this dataset to the datasource, the combobox is cleared and refilled for you. (in contradiction with using Items.Add(...))

You could go for the quick-fix (calling Clear function), but I would recommend reviewing your architecture and start over. There are several articles here on CP on N-tier design. (look here[^] eg.)

hope this helps.

这篇关于如何使用sql server2008通过c＃windows窗体中的sql select查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！