1.首先創建一個測試數據表
CREATE TABLE people ( SNO VARCHAR2(10 BYTE), SNAME VARCHAR2(10 BYTE), SSEX VARCHAR2(10 BYTE), SAGE number, SDEPT VARCHAR2(10 BYTE), BTDATE DATE ) SET DEFINE OFF; Insert into PEOPLE (SNO, SNAME, SSEX, SAGE, SDEPT,BTDATE) Values ('200215122', '劉晨', '女', '19', 'CS', TO_DATE('12/31/2014 18:25:19', 'MM/DD/YYYY HH24:MI:SS')); Insert into PEOPLE (SNO, SNAME, SSEX, SAGE, SDEPT,BTDATE) Values ('200215123', '王敏', '女', '18', 'MA', TO_DATE('12/31/2014 18:25:19', 'MM/DD/YYYY HH24:MI:SS')); Insert into PEOPLE (SNO, SNAME, SSEX, SAGE, SDEPT,BTDATE) Values ('200215125', '張力', '男', '19', 'IS', TO_DATE('12/31/2014 18:25:19', 'MM/DD/YYYY HH24:MI:SS')); Insert into PEOPLE (SNO, SNAME, SSEX, SAGE, SDEPT,BTDATE) Values ('200215122', '小趙', '男', '19', 'MA', TO_DATE('12/31/2015 18:25:19', 'MM/DD/YYYY HH24:MI:SS')); Insert into PEOPLE (SNO, SNAME, SSEX, SAGE, SDEPT,BTDATE) Values ('200215121', '李勇', '男', '23', 'CS', TO_DATE('1/31/2015 18:25:20', 'MM/DD/YYYY HH24:MI:SS')); Insert into PEOPLE (SNO, SNAME, SSEX, SAGE, SDEPT,BTDATE) Values ('200215126', '小翠', '男', '22', 'CS', TO_DATE('12/31/2014 18:25:20', 'MM/DD/YYYY HH24:MI:SS')); COMMIT;
2.設計一個WINFROM的查詢介面
3.後臺主要代碼如下：
public DataTable XxSelect(string sql, DateTime? begintime, DateTime? endtime, string[,] array, string btime, string dtime) { string constr = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.93.64.242)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=wmstest)));User ID=asrs;Password=asrs123"; //string constr = @"Data Source=orcl;Persist Security Info=True;User ID=DBSNMP;Password=dbsnmp"; OracleConnection con = new OracleConnection(constr); //數組處理--去除空數據 int count = 0; for (int i = 0; i < array.GetLength(0); i++) { if (array[i, 1] != null) { count++; } } ArrayList al = new ArrayList(); ArrayList ar = new ArrayList(); for (int i = 0; i < count; i++) { if (array[i, 1] != string.Empty) { al.Add(array[i, 0]); ar.Add(array[i, 1]); } } string[,] alr = new string[al.Count, 2]; for (int m = 0; m < alr.GetLength(0); m++) { alr[m, 0] = (string)al[m]; alr[m, 1] = (string)ar[m]; } try { //變量賦值 con.Open(); OracleParameter[] myParamArray = new OracleParameter[alr.Length + 2]; OracleCommand command = new OracleCommand(sql, con); OracleDataAdapter da = new OracleDataAdapter(); string ccBTime = @":" + btime; string ccDTime = @":" + dtime; if (begintime==null && endtime==null) { for (int j = 0; j < alr.GetLength(0); j++) { string ccStr = @":" + alr[j, 0]; myParamArray[j] = new OracleParameter(ccStr, OracleType.VarChar, 50); myParamArray[j].Value = alr[j, 1]; command.Parameters.Add(myParamArray[j]); } } if (begintime != null && endtime == null) { myParamArray[0] = new OracleParameter(ccBTime, OracleType.DateTime); myParamArray[0].Value = begintime; command.Parameters.Add(myParamArray[0]); for (int j = 1; j < alr.GetLength(0) + 1; j++) { string ccStr = @":" + alr[j - 1, 0]; myParamArray[j] = new OracleParameter(ccStr, OracleType.VarChar, 50); myParamArray[j].Value = alr[j - 1, 1]; command.Parameters.Add(myParamArray[j]); } } if (begintime == null && endtime != null) { myParamArray[0] = new OracleParameter(ccDTime, OracleType.DateTime); myParamArray[0].Value = endtime; command.Parameters.Add(myParamArray[0]); for (int j = 1; j < alr.GetLength(0) + 1; j++) { string ccStr = @":" + alr[j - 1, 0]; myParamArray[j] = new OracleParameter(ccStr, OracleType.VarChar, 50); myParamArray[j].Value = alr[j - 1, 1]; command.Parameters.Add(myParamArray[j]); } } if (begintime != null && endtime != null) { myParamArray[0] = new OracleParameter(ccBTime, OracleType.DateTime); myParamArray[0].Value = begintime; command.Parameters.Add(myParamArray[0]); myParamArray[1] = new OracleParameter(ccDTime, OracleType.DateTime); myParamArray[1].Value = endtime; command.Parameters.Add(myParamArray[1]); for (int j = 2; j < alr.GetLength(0) + 2; j++) { string ccStr = @":" + alr[j - 2, 0]; myParamArray[j] = new OracleParameter(ccStr, OracleType.VarChar, 50); myParamArray[j].Value = alr[j - 2, 1]; command.Parameters.Add(myParamArray[j]); } } da.SelectCommand = command; DataTable dt = new DataTable(); da.Fill(dt); return dt; } catch (Exception e) { MessageBox.Show("異常" + e.Message); return null; } finally { con.Close(); } }
int arr = 0; string[,] array = new string[10, 2]; string btime = "SBEGINDATE"; //时间参数 string dtime = "SENDDATE"; //时间参数 string sql = string.Format(@"select * from people where 1=1"); string sno = textBox1.Text.Trim(); string sname = textBox2.Text.Trim(); string sage = textBox3.Text.Trim(); DateTime? beginDate = null; DateTime? endDate = null; if (this.dateTimePicker1.Checked==true) { sql += string.Format(" and BTDATE>=to_date(to_char(:SBEGINDATE,'YYYY/MM/DD hh24:mi:ss'),'yyyy/MM/dd HH24:mi:ss')"); beginDate = dateTimePicker1.Value; } if (this.dateTimePicker2.Checked == true) { sql += string.Format(" and BTDATE<=to_date(to_char(:SENDDATE,'YYYY/MM/DD hh24:mi:ss'),'yyyy/MM/dd HH24:mi:ss')"); endDate = dateTimePicker2.Value; } if (sno != "") { sql += string.Format(" and SNO =:SNO"); array[arr, 0] = "SNO"; array[arr, 1] = sno; arr++; } if (sname != "") { sql += string.Format(" and SNAME=:SNAME"); array[arr, 0] = "SNAME"; array[arr, 1] = sname; arr++; } if (sage != "") { sql += string.Format(" and SAGE=:SAGE"); array[arr, 0] = "SAGE"; array[arr, 1] = sage; arr++; } MyMothed mySel = new MyMothed(); dataGridView1.DataSource = mySel.XxSelect(sql, beginDate, endDate, array, btime, dtime);
附注：是否提高查詢速度，減輕DB服務器負擔，還需實際上線觀察。