public void save(Category category) { String insertQuery = "insert into " + category.Table; int id; StringBuilder columns = new StringBuilder("("); StringBuilder values = new StringBuilder("("); if(category.Name!=null&&!category.Name.Equals("")) { columns.Append("category_name,"); values.Append("'").Append(category.Name).Append("'").Append(","); } if(category.Pid>=0) { columns.Append("category_pid,"); values.Append(category.Pid.ToString()).Append(","); } if (category.Sort != null) { columns.Append("category_sort,"); values.Append(category.Sort.ToString()).Append(","); } if (category.Description != null && !category.Description.Equals("")) { columns.Append("category_desc,"); values.Append("'").Append(category.Description).Append("'").Append(","); } if (category.Status >= 0) { switch (category.Status) { case State.Delete: values.Append("0").Append(","); break; case State.Normal: values.Append("1").Append(","); break; case State.Expiration: values.Append("4").Append(","); break; case State.Forbidden: values.Append("3").Append(","); break; case State.Verify: values.Append("2").Append(","); break; case State.Unknow: values.Append("5").Append(","); break; default: values.Append("5").Append(","); break; } columns.Append("category_status,"); values.Append(Convert.ToInt32(category.Status).ToString()).Append(","); } if (category.AddUser >= 0) { columns.Append("add_user"); values.Append(category.AddUser.ToString()).Append(","); } columns.Append("category_weight").Append(","); values.Append(category.Weight.ToString()).Append(","); columns.Append("category_page").Append(","); if (category.IsFirstPage) { values.Append("1").Append(","); } else { values.Append("0").Append(","); } columns.Append("add_date").Append(")"); values.Append("GETDATE()").Append(")") ; insertQuery = insertQuery + columns + " values" + values; String insertResult = "select max(category_id) from category"; SqlConnection conn = new SqlConnection(WebApplicationUtil.getConnectionString()); try { conn.Open(); SqlTransaction tran = conn.BeginTransaction(); //tran.IsolationLevel = System.Data.IsolationLevel.Serializable; SqlCommand command = new SqlCommand(); command.Connection = conn; command.Transaction = tran; command.CommandText = insertQuery; try { command.ExecuteNonQuery(); SqlCommand queryCommand = new SqlCommand(insertResult, conn); queryCommand.Transaction = tran; id = (int)queryCommand.ExecuteScalar(); tran.Commit(); } catch { tran.Rollback(); throw new Exception("execute query has erro!"); } conn.Close(); category.Id = id; } catch { if(conn!=null) { if (conn.State == ConnectionState.Open) { conn.Close(); } } } }
public void save(Category category) { //thisdao = new CategoryDao(); dao.save(category); }
public List<Category> findCategorys(String query,List<SqlParameter> parameters) { List<Category> categorys = new List<Category>(); SqlConnection conn = new SqlConnection(WebApplicationUtil.getConnectionString()); try { conn.Open(); SqlCommand command = new SqlCommand(query, conn); if (parameters != null) { foreach (SqlParameter param in parameters) { command.Parameters.Add(param); } } SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Category category = new Category(); category.Id = reader.GetInt32(reader.GetOrdinal("category_id")); category.Name = reader.GetString(reader.GetOrdinal("category_name")); category.Pid = reader.GetInt32(reader.GetOrdinal("category_pid")); category.Sort = reader.GetInt32(reader.GetOrdinal("category_sort")); category.Weight = reader.GetInt32(reader.GetOrdinal("category_weight")); category.AddUser = reader.GetInt32(reader.GetOrdinal("add_user")); category.AddTime = reader.GetDateTime(reader.GetOrdinal("add_date")); object desc = reader.GetValue(reader.GetOrdinal("category_desc")); if (desc != null) { category.Description = (String)desc; } int stats = reader.GetInt32(reader.GetOrdinal("category_status")); switch (stats) { case 0: category.Status = State.Delete; break; case 1: category.Status = State.Normal; break; case 2: category.Status = State.Verify; break; case 3: category.Status = State.Forbidden; break; case 4: category.Status = State.Expiration; break; default: category.Status = State.Unknow; break; } int isFirstPage =(int)reader.GetValue(reader.GetOrdinal("category_page")); if (isFirstPage == 1) { category.IsFirstPage = true; } else { category.IsFirstPage = false; } categorys.Add(category); } conn.Close(); } catch { if (conn.State == ConnectionState.Open) { conn.Close(); } } return categorys; }