public void AddCost()
{
CostModel costModel = new CostModel();
List <CostLine> costLines = new List <CostLine>();
for (int i = 0; i < 5; i++)
{
CostLine costLine = new CostLine();
costLines.Add(costLine);
}
costModel.Costs = costLines;
string sqlScript = "update set id = @id, name = @name, total price = @totalprice where age = @age";
string[] columnArray = DiscoverParams(sqlScript);
this.View.BindCosts(costModel);
}
private void btnAddNew_Click(object sender, EventArgs e)
{
List <CostLine> costLines = new List <CostLine>();
for (int i = 0; i < 10; i++)
{
CostLine line = new CostLine();
line.Id = i + 1000;
line.Name = "Cost " + i.ToString();
Random random = new Random(i);
line.Price = random.Next();
Random random1 = new Random(i);
line.Popularity = random.Next();
line.Type = (i % 2 == 0) ? "Annual" : "Non-Annual";
costLines.Add(line);
}
//var o = GetOrderByQueryRequest(new OrderQueryRequest() { Id = 1000, HostInfo = "Test" })
//this.View.Action("AddCost");
//Create Instance 1: DbGateway db = new DbGateway("MySql")
//Create Instance 2: DbGateway.Default
//Create Instance 3: DbGateway db = new DbGateway(DatabaseType.SqlServer, "server=localhost\OSPTTESTDEV;database=TESTDB;User ID=sa;Password=sa");
// Actually, no need to create and open a connection.
//DbConnection connection = DbGateway.Default.OpenConnectiion();
// We can use BeginTransaction to first create and open a connection
// and then create a transaction using the opened connection. we don't need to care the connection.
DbConnection connection = DbGateway.Default.OpenConnection();
DbTransaction trans = DbGateway.Default.BeginTransaction(connection);
try
{
// Delete
DbGateway.Default.Delete("user",
"user_id > @user_id and user_email = @user_email",
new object[] { 1000, "*****@*****.**" }, trans);
string userName;
string email = "*****@*****.**";
string password;
string topicName;
string topicDesc;
// Insert
for (int index = 0; index < 10; index++)
{
userName = "******" + (index + 1).ToString();
password = "******" + index.ToString();
topicName = "Topic " + (index + 1).ToString();
topicDesc = "Description " + (index + 1).ToString();
DbGateway.Default.Insert("user",
new object[] { userName, null, email, password },
trans);
DbGateway.Default.Insert("topic",
new object[] { topicName, topicDesc }, trans);
//DbGateway.Default.Insert("user",
// new string[] { "user_name", "user_email", "user_password" },
// new object[] { userName, email, password }, trans);
}
// Update
DbGateway.Default.Update("user",
new string[] { "user_nick_name" },
new object[] { "卡洛斯" },
"user_id=@id",
new object[] { 1000 },
trans);
// customized Non Query SQL scripts.
DbGateway.Default.ExecuteNonQuery("update [user] set [user_email]=@email where [user_id]=@id",
new object[] { "*****@*****.**", 1000 },
trans);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
DbGateway.Default.CloseConnection(connection);
}
// 注意，transaction 提交之后， transaction 的 connection 将变为null,
// 所以 调用 transaction 的Commit 之后，不能再使用带 transaction参数的方法,
// 应该直接调用无transaction 参数的方法。
string userQuerySql = @"select [user_name], [user_email], [user_password] from [user] where [user_id]=@id";
//读取后直接关闭connection. 这样也避免了死锁。
DataSet ds = DbGateway.Default.ExecuteDataSet(userQuerySql, new object[] { 1000 });
// 以下方法在 Commit 调用 之后 再执行 是错误的，如果需要在Commit 之后 调用 带transaction的方法那说明你的数据库操作逻辑本身就有问题
// 你应该在Commit 之前获取你想要的数据，用ReadCommitted 的隔离级别。可研究 转账 业务,要保证是同一个事物里面，所以不管读取还是
// 更新都应该在事物提交之前去做。提交之后读数据已经没必要在 用事物去读了，Query执行后直接关闭connecton.
//DataSet ds = DbGateway.Default.ExecuteDataSet(userQuerySql, new object[] { 1000 }, trans);
DataTable dt = ds.Tables[0];
string userEmail = dt.Rows[0]["user_email"].ToString();
using (IDataReader dr = DbGateway.Default.ExecuteReader(userQuerySql, new object[] { 1000 }))
{
dr.Read();
string userPassword = dr["user_password"].ToString();
if (!dr.IsClosed)
{
dr.Close();
dr.Dispose();
}
}
IDataReader dr1 = DbGateway.Default.SelectReader("user",
new string[] { "user_name", "user_email", "user_password" },
"user_id > @id",
new object[] { 1 },
"user_name desc");
while (dr1.Read())
{
string userPassword = dr1["user_password"].ToString();
}
if (!dr1.IsClosed)
{
dr1.Close();
dr1.Dispose();
}
DataSet ds1 = DbGateway.Default.SelectDataSet("user",
new string[] { "user_name", "user_email", "user_password" },
"user_id > @id",
new object[] { 1 },
"user_name desc");
string passwordString = ds1.Tables[0].Rows[1]["user_password"].ToString();
}
