1：整形
View Code
AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);
ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test (id TINYINT, idu TINYINT UNSIGNED, i INT UNSIGNED)");
IDataParameter[] param1 = new IDataParameter[]{
ado.GetParameter("id", DbType.Int16,-98),
ado.GetParameter("idu", DbType.UInt16,140),
ado.GetParameter("i", DbType.UInt16,20),
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test VALUES (?id, ?idu, ?i)", param1);
IDataParameter[] param2 = new IDataParameter[]{
ado.GetParameter("id", DbType.Int16,"0"),
ado.GetParameter("idu", DbType.UInt16,"0"),
ado.GetParameter("i", DbType.UInt16,"0"),
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test VALUES (?id, ?idu, ?i)", param2);
using (IDataReader reader = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test"))
{
Assert.IsTrue(reader.Read());
Assert.AreEqual(-98, (sbyte)reader.GetByte(0));
Assert.AreEqual(140, reader.GetByte(1));
Assert.IsTrue(reader.GetBoolean(1));
Assert.AreEqual(20, reader.GetInt32(2));
Assert.AreEqual(20, reader.GetInt32(2));
Assert.IsTrue(reader.Read());
Assert.AreEqual(0, reader.GetByte(0));
Assert.AreEqual(0, reader.GetByte(1));
Assert.IsFalse(reader.GetBoolean(1));
Assert.IsFalse(reader.Read());
}
2：小数
View Code
AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);
//ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test3 (fl FLOAT, db DOUBLE, dec1 DECIMAL(5,2))");
IDataParameter[] param1 = new IDataParameter[]{
ado.GetParameter("fl", DbType.Double,2.3),
ado.GetParameter("db", DbType.Double,4.6),
ado.GetParameter("dec", DbType.Decimal,23.82),
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test3 VALUES (?fl, ?db, ?dec)", param1);
IDataParameter[] param2 = new IDataParameter[]{
ado.GetParameter("fl", DbType.Double,1.5),
ado.GetParameter("db", DbType.Double,47.85),
ado.GetParameter("dec", DbType.Decimal,123.85),
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test3 VALUES (?fl, ?db, ?dec)", param2);
//将Double定义为int，数值会被四舍五入
//Double和Decimal在范围允许的情况下可以互换
using (IDataReader reader = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test3"))
{
Assert.IsTrue(reader.Read());
Assert.AreEqual(2.3, (decimal)reader.GetFloat(0));
Assert.AreEqual(4.6, reader.GetDouble(1));
Assert.AreEqual(23.82, reader.GetDecimal(2));
Assert.IsTrue(reader.Read());
Assert.AreEqual(1.5, (decimal)reader.GetFloat(0));
Assert.AreEqual(47.85, reader.GetDouble(1));
Assert.AreEqual(123.85, reader.GetDecimal(2));
}
3：时间
View Code
AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);
//ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test4 (id INT NOT NULL, name VARCHAR(100), d DATE, dt DATETIME, tm TIME, PRIMARY KEY(id))");
IDataParameter[] param1 = new IDataParameter[]{
ado.GetParameter("id", DbType.Int16,1),
ado.GetParameter("tm", DbType.String,"00:00"),
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test4 (id, tm) VALUES (?id, ?tm)", param1);
IDataParameter[] param2 = new IDataParameter[]{
ado.GetParameter("id", DbType.Int16,2),
ado.GetParameter("tm", DbType.String,"512:45:17"),
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test4 (id, tm) VALUES (?id, ?tm)", param2);
IDataParameter[] param3 = new IDataParameter[]{
ado.GetParameter("id", DbType.Int16,3),
ado.GetParameter("tm", DbType.String,"-07:24:00"),
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test4 (id, tm) VALUES (?id, ?tm)", param3);
//TimeSpan在用驱动的时候一定要选择String而不是Time。对于读取没有影响。
//时间可谓负
using (IDataReader reader = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test4"))
{
reader.Read();
object value = reader["tm"];
Assert.AreEqual(value.GetType(), typeof(TimeSpan));
TimeSpan ts = (TimeSpan)reader["tm"];
Assert.AreEqual(0, ts.Hours);
Assert.AreEqual(0, ts.Minutes);
Assert.AreEqual(0, ts.Seconds);
reader.Read();
value = reader["tm"];
Assert.AreEqual(value.GetType(), typeof(TimeSpan));
ts = (TimeSpan)reader["tm"];
Assert.AreEqual(21, ts.Days);
Assert.AreEqual(8, ts.Hours);
Assert.AreEqual(45, ts.Minutes);
Assert.AreEqual(17, ts.Seconds);
reader.Read();
value = reader["tm"];
Assert.AreEqual(value.GetType(), typeof(TimeSpan));
ts = (TimeSpan)reader["tm"];
Assert.AreEqual(-7, ts.Hours);
Assert.AreEqual(-24, ts.Minutes);
Assert.AreEqual(0, ts.Seconds);
}
View Code
AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);
ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test6");
ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test6 (id int, dt DATETIME, ts TIMESTAMP)");
//ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
//"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");
DateTime time = DateTime.Now;
IDataParameter[] param1 = new IDataParameter[]{
ado.GetParameter("id", DbType.Int16,1),
ado.GetParameter("dt", DbType.String,time),
ado.GetParameter("ts", DbType.String,time),
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt,ts) VALUES (?id, ?dt,?ts)", param1);
//time,datetime,timespan类型可以为string
using (DataSet ds = ado.ExecuteDataset(conn, CommandType.Text, "SELECT * FROM Test6"))
{
DataTable dt = ds.Tables[0];
DateTime now = (DateTime)dt.Rows[0]["dt"];
Assert.AreEqual(1, dt.Rows[0]["id"]);
DateTime ts2 = (DateTime)dt.Rows[0]["ts"];
Assert.AreEqual(now.Year, ts2.Year);
}
4：bit
View Code
AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);
ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test7");
ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test7 (bt1 BIT(2), bt4 BIT(4), bt11 BIT(11), bt23 BIT(23), bt32 BIT(32)) engine=myisam");
//ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
//"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");
IDataParameter[] param1 = new IDataParameter[]{
ado.GetParameter("bt1", DbType.Int16,2),
ado.GetParameter("bt4", DbType.Int16,3),
ado.GetParameter("bt11", DbType.Int16,120),
ado.GetParameter("bt23", DbType.Int16,240),
ado.GetParameter("bt32", DbType.Int16,1000)
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test7 VALUES (?bt1, ?bt4, ?bt11, ?bt23, ?bt32)", param1);
//BIT类型可为Null
IDataParameter[] param2 = new IDataParameter[]{
ado.GetParameter("bt1", DbType.Int16,null),
ado.GetParameter("bt4", DbType.Int16,null),
ado.GetParameter("bt11", DbType.Int16,120),
ado.GetParameter("bt23", DbType.Int16,null),
ado.GetParameter("bt32", DbType.Int16,null)
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test7 VALUES (?bt1, ?bt4, ?bt11, ?bt23, ?bt32)", param2);
using (IDataReader reader = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test7"))
{
Assert.IsTrue(reader.Read());
Assert.AreEqual(2, reader.GetInt32(0));
Assert.AreEqual(3, reader.GetInt32(1));
Assert.AreEqual(120, reader.GetInt32(2));
Assert.AreEqual(240, reader.GetInt32(3));
Assert.AreEqual(1000, reader.GetInt32(4));
Assert.IsTrue(reader.Read());
Assert.IsTrue(reader.IsDBNull(0));
Assert.IsTrue(reader.IsDBNull(1));
Assert.AreEqual(120, reader.GetInt32(2));
Assert.IsTrue(reader.IsDBNull(3));
Assert.IsTrue(reader.IsDBNull(4));
}
5：binary
View Code
AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);
ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test8");
ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test8 (id INT, g BINARY(16), c VARBINARY(16), c1 BINARY(255))");
//ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
//"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");
Guid g = Guid.NewGuid();
byte[] bytes = g.ToByteArray();
IDataParameter[] param1 = new IDataParameter[]{
ado.GetParameter("g",bytes),
ado.GetParameter("c", bytes),
ado.GetParameter("c1", g.ToString())
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test8 VALUES (1, @g, @c, @c1)", param1);
//字节数组传递过程中是以Object类型传递的,BINARY(16)可为空
using (DataSet ds = ado.ExecuteDataset(conn, CommandType.Text, "SELECT * FROM Test8"))
{
DataTable dt = ds.Tables[0];
Assert.IsTrue(dt.Rows[0][1] is Guid);
Assert.IsTrue(dt.Rows[0][2] is byte[]);
Assert.IsTrue(dt.Rows[0][3] is byte[]);
Assert.AreEqual(g, dt.Rows[0][1]);
}
7：char
View Code
AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);
ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test9");
ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test9 (id INT, name CHAR(12) CHARSET utf8)");
//ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
//"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");
IDataParameter[] param1 = new IDataParameter[]{
ado.GetParameter("name","文学")
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test9 VALUES (1, ?name)", param1);
using (IDataReader reader = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test9"))
{
reader.Read();
string s = reader.GetString(1);
Assert.AreEqual("Name", s);
}
8：LongBlob
View Code
AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);
ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test10");
ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test10 (id INT NOT NULL, blob1 LONGBLOB, PRIMARY KEY(id))");
//ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
//"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");
int lenIn = 1000000;
byte[] dataIn = CreateBlob(lenIn);
IDataParameter[] param1 = new IDataParameter[]{
ado.GetParameter("id",1),
ado.GetParameter("b1",dataIn)
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test10 VALUES (?id, ?b1)", param1);
//LONGBLOB最大存储100万字节
using (DataSet ds = ado.ExecuteDataset(conn, CommandType.Text, "SELECT * FROM Test10"))
{
byte[] dataOut = ds.Tables[0].Rows[0]["blob1"] as byte[];
for (int x = 0; x < dataIn.Length; x++)
Assert.AreEqual(dataIn[x], dataOut[x], "Checking first binary array at " + x);
}
9：LongText
View Code
AdoHelper ado = AdoHelper.CreateHelper(DbProvideType.MySql);
ado.ExecuteNonQuery(conn, CommandType.Text, "DROP TABLE IF EXISTS Test11");
ado.ExecuteNonQuery(conn, CommandType.Text, "CREATE TABLE Test11 (id INT NOT NULL, text1 LONGTEXT, PRIMARY KEY(id))");
//ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test6 (id, dt, ts2, ts4, ts6, ts8, ts10, ts12, ts14) " +
//"VALUES (1, Now(), Now(), Now(), Now(), Now(), Now(), Now(), Now())");
int num = 25000;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < num; i++)
sb.Append("我我我我我我我我我我");
IDataParameter[] param1 = new IDataParameter[]{
ado.GetParameter("id",1),
ado.GetParameter("text1",sb.ToString())
};
ado.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO Test11 VALUES (?id, ?text1)", param1);
//LONGTEXT最大存储25万个汉字
using (IDataReader dr = ado.ExecuteReader(conn, CommandType.Text, "SELECT * FROM Test11"))
{
dr.Read();
string aa = dr.GetString(1);
}
10：总结
1：没有发现数组类型
2：大对象容量大，可以存储25万汉字