ps:
1. select [列名] from...与select sum/max[列名] 等 用Reader去读 效果是不一样的
2. DataReader.GetName(int iColumnIndex)
dr.GetName(0) 可以获得0即第一列的列名
public void deleteandsearch(string text)
{
if (deletefirst(t3.Text))
{
text = text.Trim();
text = Regex.Replace(text, " * ", "");
SqlConnection objConn = new SqlConnection();
objConn.ConnectionString = "uid=sa;pwd=zczc1244;server='.';Database=db";
SqlCommand objSqlCmd;
SqlDataAdapter objSqlAdp;
DataTable objDT;
DataSet objDS = new DataSet();
string strSQL3;
strSQL3 = "Select max(全年补贴) as 全年补贴 from " + comboBox1.Text + " where 姓名 = '" + text + "'";
string strSQL4;
strSQL4 = "Select min(扣保险) as 扣保险 from " + comboBox1.Text + " where 姓名 = '" + text + "'";
string strSQL5;
strSQL5 = "Select * from " + comboBox1.Text + " where 姓名 = '" + text + "'order by (就诊日期) desc";
objConn.Open();
objSqlCmd = new SqlCommand();
objSqlCmd.Connection = objConn;
objSqlCmd.CommandType = CommandType.Text;
objSqlCmd.CommandText = strSQL3;
t10.Text = objSqlCmd.ExecuteScalar().ToString();
objSqlCmd.CommandText = strSQL4;
t11.Text = objSqlCmd.ExecuteScalar().ToString();
//=========================================
t8.Text = comboBox2.Text + "-" + comboBox3.Text + "-" + comboBox4.Text;
string strSQL8 = "delete from " + comboBox1.Text + " where 姓名 = '" + t3.Text + "'and 就诊日期 = '" + t8.Text + "'";
objSqlCmd.CommandText = strSQL8;
try { objSqlCmd.ExecuteNonQuery(); l1.Text = "delete successfully!not only one"; }
catch { l1.Text = "delete fail"; }
string strSQL2;
strSQL2 = "Select sum(费用) as 费用 from " + comboBox1.Text + " where 姓名 = '" + text + "'";
objSqlCmd.CommandText = strSQL2;
t2.Text = objSqlCmd.ExecuteScalar().ToString();
string strSQL9 = "select 就诊日期 from " + comboBox1.Text + " where 姓名='" + text + "'";//仔细看这里 注释1 ,只有select *读到空返回true
objSqlCmd.CommandText = strSQL9;
SqlDataReader dr2 = objSqlCmd.ExecuteReader();
if (dr2.Read())
{
dr2.Close();
string strSQL1 = "select max(convert(varchar(30),就诊日期,23)) as 就诊日期 from " + comboBox1.Text + " where 姓名='" + text + "'";
objSqlCmd.CommandText = strSQL1;
tt1.Text = objSqlCmd.ExecuteScalar().ToString();
zc = decimal.Parse(t2.Text) + decimal.Parse(t10.Text) + decimal.Parse(t11.Text);
t12.Text = zc.ToString();
string strSQL6;
strSQL6 = "update " + comboBox1.Text + " set 总费用='" + t2.Text + "', 补贴结余='" + t12.Text + "',全年补贴='" + t10.Text + "', 扣保险='" + t11.Text + "'where 姓名 = '" + text + "' and 就诊日期 ='" + tt1.Text + "' ";
string strSQL7;
strSQL7 = "update " + comboBox1.Text + " set 总费用=null, 补贴结余=null,全年补贴='0', 扣保险='0' where 姓名 = '" + text + "' and 就诊日期 <>'" + tt1.Text + "' ";
objSqlCmd.CommandText = strSQL6;
objSqlCmd.ExecuteNonQuery();
objSqlCmd.CommandText = strSQL7;
objSqlCmd.ExecuteNonQuery();
}
else
{
dr2.Close();
l1.Text = "delete successfully!";
}
objSqlCmd.CommandText = strSQL5;
objSqlAdp = new SqlDataAdapter();
objSqlAdp.SelectCommand = objSqlCmd;
objDT = new DataTable();
objSqlAdp.Fill(objDT);
dataGrid1.DataSource = objDT;
}
else
{
try
{
l1.Text = "记录未找到,删除失败!";
}
catch
{
l1.Text = "表不存在";
}
}
}
public bool deletefirst(string text)
{
text = text.Trim();
text = Regex.Replace(text, " * ", "");
t8.Text = comboBox2.Text + "-" + comboBox3.Text + "-" + comboBox4.Text;
SqlConnection objConn = new SqlConnection();
objConn.ConnectionString = "uid=sa;pwd=zczc1244;server='.';Database=db";
string strSQL;
System.Data.SqlClient.SqlCommandBuilder objCB;
System.Data.SqlClient.SqlDataAdapter objDA;
System.Data.DataSet objDS = new System.Data.DataSet();
strSQL = "Select * from " + comboBox1.Text + " where 姓名 = '" + text + "'and 就诊日期 = '" + t8.Text + "'";
try
{
objConn.Open();
objDA = new System.Data.SqlClient.SqlDataAdapter(strSQL, objConn);
objCB = new System.Data.SqlClient.SqlCommandBuilder(objDA);
objDA.Fill(objDS, "" + comboBox1.Text + "");
if (objDS.Tables["" + comboBox1.Text + ""].Rows.Count == 0)
{
return false;
}
else
return true;
}
catch
{
l1.Text = "warning";
return false;
}
finally
{
objDA = null;
objDS = null;
objCB = null;
}
}
1. select [列名] from...与select sum/max[列名] 等 用Reader去读 效果是不一样的
2. DataReader.GetName(int iColumnIndex)
dr.GetName(0) 可以获得0即第一列的列名
public void deleteandsearch(string text)
{
if (deletefirst(t3.Text))
{
text = text.Trim();
text = Regex.Replace(text, " * ", "");
SqlConnection objConn = new SqlConnection();
objConn.ConnectionString = "uid=sa;pwd=zczc1244;server='.';Database=db";
SqlCommand objSqlCmd;
SqlDataAdapter objSqlAdp;
DataTable objDT;
DataSet objDS = new DataSet();
string strSQL3;
strSQL3 = "Select max(全年补贴) as 全年补贴 from " + comboBox1.Text + " where 姓名 = '" + text + "'";
string strSQL4;
strSQL4 = "Select min(扣保险) as 扣保险 from " + comboBox1.Text + " where 姓名 = '" + text + "'";
string strSQL5;
strSQL5 = "Select * from " + comboBox1.Text + " where 姓名 = '" + text + "'order by (就诊日期) desc";
objConn.Open();
objSqlCmd = new SqlCommand();
objSqlCmd.Connection = objConn;
objSqlCmd.CommandType = CommandType.Text;
objSqlCmd.CommandText = strSQL3;
t10.Text = objSqlCmd.ExecuteScalar().ToString();
objSqlCmd.CommandText = strSQL4;
t11.Text = objSqlCmd.ExecuteScalar().ToString();
//=========================================
t8.Text = comboBox2.Text + "-" + comboBox3.Text + "-" + comboBox4.Text;
string strSQL8 = "delete from " + comboBox1.Text + " where 姓名 = '" + t3.Text + "'and 就诊日期 = '" + t8.Text + "'";
objSqlCmd.CommandText = strSQL8;
try { objSqlCmd.ExecuteNonQuery(); l1.Text = "delete successfully!not only one"; }
catch { l1.Text = "delete fail"; }
string strSQL2;
strSQL2 = "Select sum(费用) as 费用 from " + comboBox1.Text + " where 姓名 = '" + text + "'";
objSqlCmd.CommandText = strSQL2;
t2.Text = objSqlCmd.ExecuteScalar().ToString();
string strSQL9 = "select 就诊日期 from " + comboBox1.Text + " where 姓名='" + text + "'";//仔细看这里 注释1 ,只有select *读到空返回true
objSqlCmd.CommandText = strSQL9;
SqlDataReader dr2 = objSqlCmd.ExecuteReader();
if (dr2.Read())
{
dr2.Close();
string strSQL1 = "select max(convert(varchar(30),就诊日期,23)) as 就诊日期 from " + comboBox1.Text + " where 姓名='" + text + "'";
objSqlCmd.CommandText = strSQL1;
tt1.Text = objSqlCmd.ExecuteScalar().ToString();
zc = decimal.Parse(t2.Text) + decimal.Parse(t10.Text) + decimal.Parse(t11.Text);
t12.Text = zc.ToString();
string strSQL6;
strSQL6 = "update " + comboBox1.Text + " set 总费用='" + t2.Text + "', 补贴结余='" + t12.Text + "',全年补贴='" + t10.Text + "', 扣保险='" + t11.Text + "'where 姓名 = '" + text + "' and 就诊日期 ='" + tt1.Text + "' ";
string strSQL7;
strSQL7 = "update " + comboBox1.Text + " set 总费用=null, 补贴结余=null,全年补贴='0', 扣保险='0' where 姓名 = '" + text + "' and 就诊日期 <>'" + tt1.Text + "' ";
objSqlCmd.CommandText = strSQL6;
objSqlCmd.ExecuteNonQuery();
objSqlCmd.CommandText = strSQL7;
objSqlCmd.ExecuteNonQuery();
}
else
{
dr2.Close();
l1.Text = "delete successfully!";
}
objSqlCmd.CommandText = strSQL5;
objSqlAdp = new SqlDataAdapter();
objSqlAdp.SelectCommand = objSqlCmd;
objDT = new DataTable();
objSqlAdp.Fill(objDT);
dataGrid1.DataSource = objDT;
}
else
{
try
{
l1.Text = "记录未找到,删除失败!";
}
catch
{
l1.Text = "表不存在";
}
}
}
public bool deletefirst(string text)
{
text = text.Trim();
text = Regex.Replace(text, " * ", "");
t8.Text = comboBox2.Text + "-" + comboBox3.Text + "-" + comboBox4.Text;
SqlConnection objConn = new SqlConnection();
objConn.ConnectionString = "uid=sa;pwd=zczc1244;server='.';Database=db";
string strSQL;
System.Data.SqlClient.SqlCommandBuilder objCB;
System.Data.SqlClient.SqlDataAdapter objDA;
System.Data.DataSet objDS = new System.Data.DataSet();
strSQL = "Select * from " + comboBox1.Text + " where 姓名 = '" + text + "'and 就诊日期 = '" + t8.Text + "'";
try
{
objConn.Open();
objDA = new System.Data.SqlClient.SqlDataAdapter(strSQL, objConn);
objCB = new System.Data.SqlClient.SqlCommandBuilder(objDA);
objDA.Fill(objDS, "" + comboBox1.Text + "");
if (objDS.Tables["" + comboBox1.Text + ""].Rows.Count == 0)
{
return false;
}
else
return true;
}
catch
{
l1.Text = "warning";
return false;
}
finally
{
objDA = null;
objDS = null;
objCB = null;
}
}