<connectionStrings>
<add connectionString="uid=sa;pwd=peace;database=TEST;server=." providerName="System.Data.SqlClient" />
<!--<add connectionString="server=.;data source=peace;user id=cct;password=cct;enlist=true" providerName="System.Data.OracleClient"/>-->
</connectionStrings>
protected void Page_Load(object sender, EventArgs e)
{
//测试DataReader,SQLSERVER和ORACLE都通过
//DataProviderFactory fac = new DataProviderFactory();
//DbParameterCollection ParaCollect = fac.GetParmCollection();
//fac.AddParam("USERID", 100, ParaCollect);
//DbDataReader dr = fac.ExecuteDataReader(fac.conn, "SELECT * FROM USER_TEST WHERE USERID=$USERID");
//while (dr.Read())
//{
// string a = dr[1].ToString();
//}
//fac.conn.Close();//在调用处显示关闭
//无参数DataSet测试 SQLSERVER和ORACLE都通过
//DataTable dt = fac.ExecDataSet("SELECT * FROM USER_TEST").Tables[0];
//带参数DataSet测试 SQLSERVER和ORACLE都通过
//DbParameterCollection ParaCollect = fac.GetParmCollection();
//fac.AddParam("USERID", 100, ParaCollect);
//fac.AddParam("USERNAME", "局%", ParaCollect);//这里的参数名可以任意成其它，不一定非要和字段名相同（下同）
//DataTable dt = fac.ExecDataSet("SELECT * FROM USER_TEST WHERE USERNAME LIKE $USERNAME").Tables[0];
//DataTable dt = fac.ExecDataSet("SELECT * FROM USER_TEST WHERE USERID=$USERID OR USERNAME LIKE $USERNAME").Tables[0];//多参数测试
//单值测试(带参数) SQLSERVER和ORACLE都通过
//DbParameterCollection ParaCollect = fac.GetParmCollection();
//fac.AddParam("USERID", 100, ParaCollect);
//string retValue = fac.ReturnValue("SELECT USERNAME FROM USER_TEST WHERE USERID=$USERID");
//带参存储过程测试返回结果集 SQLSERVER和ORACLE都通过
//DbParameterCollection ParaCollect = fac.GetParmCollection();
//fac.AddParam("StartDate", "2009-8-1", ParaCollect);
//fac.AddParam("EndDate", "2009-8-21", ParaCollect);
//DataTable dt = fac.RunProcedure("USP_GetMixedReport").Tables[0];
//带参数测试存储过程的输出参数值和返回值，方法不返回结果集 SQLSERVER通过
//int flag = 0, sign = 0, ret = 0;
//DbParameterCollection ParaCollect = fac.GetParmCollection();
//fac.AddParam("USER_ACCOUNT", DbType.String, "admin", ParaCollect);
//fac.AddParam("USER_PWD", DbType.String, "68053af2923e00204c3ca7c6a3150cf7", ParaCollect);
//fac.AddParam("FLAG", DbType.Int32, "", ParaCollect);
//ParaCollect["@FLAG"].Direction = System.Data.ParameterDirection.Output;
//fac.AddParam("SIGN", DbType.Int32, "", ParaCollect);
//ParaCollect["@SIGN"].Direction = System.Data.ParameterDirection.Output;
//fac.AddParam("RetValue", DbType.String, "", ParaCollect);
//ParaCollect["@RetValue"].Direction = System.Data.ParameterDirection.ReturnValue;
//fac.RunVoidProcedure("SP_ValideLogin");
//flag = int.Parse(ParaCollect["@FLAG"].Value.ToString());
//sign = int.Parse(ParaCollect["@SIGN"].Value.ToString());
//ret = int.Parse(ParaCollect["@RetValue"].Value.ToString());//存储过程约定返回值必须是int型
//改进后带参数测试存储过程的输出参数值和返回值的测试 SQLSERVER和ORACLE都通过
//int flag = 0, sign = 0, ret = 0;
//DataProviderFactory fac = new DataProviderFactory();
//DbParameterCollection ParaCollect = fac.GetParmCollection();
//fac.AddInputParam("USER_ACCOUNT", "admin", ParaCollect);
//fac.AddInputParam("USER_PWD", "68053af2923e00204c3ca7c6a3150cf7", ParaCollect);
//fac.AddOutputParam("FLAG", ParaCollect);
//fac.AddOutputParam("SIGN", ParaCollect);
//fac.AddReturnParam("RetValue", ParaCollect);
//fac.RunVoidProcedure("SP_ValideLogin");
//string prefix = fac.retParaformat.Replace(":","");//Oracle存储过程参数前冒号移除掉
//flag = int.Parse(ParaCollect[string.Format(prefix,"FLAG")].Value.ToString());
//sign = int.Parse(ParaCollect[string.Format(prefix, "SIGN")].Value.ToString());
//ret = int.Parse(ParaCollect[string.Format(prefix, "RetValue")].Value.ToString());//存储过程约定返回值必须是int型
//调用存储过程测试 SQLSERVER和ORACLE都通通过
//DataProviderFactory fac = new DataProviderFactory();
//DbParameterCollection ParaCollect = fac.GetParmCollection();
//fac.AddInputParam("P_UserID", 7, ParaCollect);
//fac.AddInputParam("P_UserName", "peace", ParaCollect);
//fac.AddInputParam("P_UserAge", 100, ParaCollect);
//fac.RunVoidProcedure("PROC_USER_TEST_ADD");
//多条提交事务处理测试 SQLSERVER和ORACLE都通过
//List<string> SqlList = new List<string>();
//DataProviderFactory fac = new DataProviderFactory();
//DbParameterCollection ParaCollect = fac.GetParmCollection();
//fac.AddParam("UserName", "peaceli", ParaCollect);
//fac.AddParam("UserAge", 150, ParaCollect);
//SqlList.Add("INSERT INTO USER_TEST(UserName,UserAge) VALUES($UserName,$UserAge)");
//SqlList.Add("INSERT INTO USER_TEST(UserName,UserAge) VALUES($UserName,$UserAge)");
//SqlList.Add("INSERT INTO USER_TEST(UserName,UserAge) VALUES($UserName,$UserAge)");
//fac.ExecSqlTran(SqlList);
//插入操作参数测试(SQL SERVER) 通过
//UserInfo ui = new UserInfo();
//ui.UserName = "hello peace";
//ui.UserAge = 100;
//Addinn(ui);
//插入操作参数测试(Oracle) 通过
//UserInfo ui = new UserInfo();
//ui.USERID = 10;
//ui.USERNAME = "hello peace";
//ui.USERAGE = 120;
//Addin(ui);
//插入操作反射参数转换测试 SQLSERVER和ORACLE都通过
//UserInfo ui = new UserInfo();
//ui.USERNAME = "peaceli";
//ui.USERAGE = 110;
//Add(ui);
//返回实体对象测试 SQLSERVER和ORACLE都通过
UserInfo ui = new UserInfo();
ui.USERID = 1;
GetInfo(ui);
}
//private void Addinn(UserInfo ui)
//{
// DataProviderFactory fac = new DataProviderFactory();
// DbParameterCollection ParaCollect = fac.GetParmCollection();
// fac.AddParam("@UserName", ui.UserName, ParaCollect);
// fac.AddParam("@UserAge", ui.UserAge, ParaCollect);
// fac.ExecSql("INSERT INTO USER_TEST(UserName,UserAge) VALUES(@UserName,@UserAge)");
//}
private void Addin(UserInfo ui)
{
DataProviderFactory fac = new DataProviderFactory();
DbParameterCollection ParaCollect = fac.GetParmCollection();
//fac.AddParam(":UserName", ui.UserName, ParaCollect);//给参数赋值时冒号可以不加，但有的版本可能必须加
//fac.AddParam(":UserAge", ui.UserAge, ParaCollect);
//fac.AddParam("UserID", ui.USERID, ParaCollect); //这行注释放开在ORACLE下同不过，ORACLE要求所全参数匹配，有多余参数就不行，这点有些变态
fac.AddParam("UserName", ui.USERNAME, ParaCollect);//SQL SERVER只要求用到的参数包含在参数集合里就行了，其它多余参数并不影响执行
fac.AddParam("UserAge", ui.USERAGE, ParaCollect);
fac.ExecSql("INSERT INTO USER_TEST(UserName,UserAge) VALUES(:UserName,:UserAge)");
}
private void Add(UserInfo ui)
{
DataProviderFactory fac = new DataProviderFactory();
DbParameterCollection ParaCollect = fac.GetParmCollection();
string[] fields = { "USERNAME", "USERAGE" };//要求参数化的实体属性
List<string> ListFields = new List<string>(fields);
fac.ConvertToParameters(ui, ParaCollect, ListFields);//如果新增记录有很多参数的话，可能AddParam很多次，采用反射批量转换
fac.ExecSql("INSERT INTO USER_TEST(USERNAME,USERAGE) VALUES($USERNAME,$USERAGE)");
}
private void GetInfo(UserInfo ui)
{
DataProviderFactory fac = new DataProviderFactory();
DbParameterCollection ParaCollect = fac.GetParmCollection();
fac.AddParam("USERID", ui.USERID, ParaCollect);
fac.GetModel(ui, "SELECT USERNAME,USERAGE FROM USER_TEST WHERE USERID=$USERID");
}
}
UserInfo类如下：
复制代码 代码如下: