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
('', '劉晨', '女', '', '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
('', '王敏', '女', '', '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
('', '張力', '男', '', '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
('', '小趙', '男', '', '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
('', '李勇', '男', '', '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
('', '小翠', '男', '', '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 = ;
for (int i = ; i < array.GetLength(); i++)
{
if (array[i, ] != null)
{
count++;
}
}
ArrayList al = new ArrayList();
ArrayList ar = new ArrayList();
for (int i = ; i < count; i++)
{
if (array[i, ] != string.Empty)
{
al.Add(array[i, ]);
ar.Add(array[i, ]);
}
}
string[,] alr = new string[al.Count, ];
for (int m = ; m < alr.GetLength(); m++)
{
alr[m, ] = (string)al[m];
alr[m, ] = (string)ar[m];
}
try
{ //變量賦值
con.Open();
OracleParameter[] myParamArray = new OracleParameter[alr.Length + ];
OracleCommand command = new OracleCommand(sql, con);
OracleDataAdapter da = new OracleDataAdapter();
string ccBTime = @":" + btime;
string ccDTime = @":" + dtime;
if (begintime==null && endtime==null)
{
for (int j = ; j < alr.GetLength(); j++)
{
string ccStr = @":" + alr[j, ];
myParamArray[j] = new OracleParameter(ccStr, OracleType.VarChar, );
myParamArray[j].Value = alr[j, ];
command.Parameters.Add(myParamArray[j]);
}
}
if (begintime != null && endtime == null)
{
myParamArray[] = new OracleParameter(ccBTime, OracleType.DateTime);
myParamArray[].Value = begintime;
command.Parameters.Add(myParamArray[]);
for (int j = ; j < alr.GetLength() + ; j++)
{
string ccStr = @":" + alr[j - , ];
myParamArray[j] = new OracleParameter(ccStr, OracleType.VarChar, );
myParamArray[j].Value = alr[j - , ];
command.Parameters.Add(myParamArray[j]);
}
}
if (begintime == null && endtime != null)
{
myParamArray[] = new OracleParameter(ccDTime, OracleType.DateTime);
myParamArray[].Value = endtime;
command.Parameters.Add(myParamArray[]);
for (int j = ; j < alr.GetLength() + ; j++)
{
string ccStr = @":" + alr[j - , ];
myParamArray[j] = new OracleParameter(ccStr, OracleType.VarChar, );
myParamArray[j].Value = alr[j - , ];
command.Parameters.Add(myParamArray[j]);
}
}
if (begintime != null && endtime != null)
{
myParamArray[] = new OracleParameter(ccBTime, OracleType.DateTime);
myParamArray[].Value = begintime;
command.Parameters.Add(myParamArray[]);
myParamArray[] = new OracleParameter(ccDTime, OracleType.DateTime);
myParamArray[].Value = endtime;
command.Parameters.Add(myParamArray[]); for (int j = ; j < alr.GetLength() + ; j++)
{
string ccStr = @":" + alr[j - , ];
myParamArray[j] = new OracleParameter(ccStr, OracleType.VarChar, );
myParamArray[j].Value = alr[j - , ];
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 = ;
string[,] array = new string[, ];
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, ] = "SNO";
array[arr, ] = sno;
arr++;
}
if (sname != "")
{
sql += string.Format(" and SNAME=:SNAME");
array[arr, ] = "SNAME";
array[arr, ] = sname;
arr++;
}
if (sage != "")
{
sql += string.Format(" and SAGE=:SAGE");
array[arr, ] = "SAGE";
array[arr, ] = sage;
arr++;
}
MyMothed mySel = new MyMothed();
dataGridView1.DataSource = mySel.XxSelect(sql, beginDate, endDate, array, btime, dtime);
附注：是否提高查詢速度，減輕DB服務器負擔，還需實際上線觀察。