/// <summary>
/// 获得数据列表
/// </summary>
public List <testtable> GetList(string strWhere)
{
List <testtable> listTable = new List <testtable>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select col1,col2 ");
strSql.Append(" FROM testtable ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
DataSet set = DbHelperOleDb.Query(strSql.ToString());
int rowCount = set.Tables[0].Rows.Count;
DataTable setTable = set.Tables[0];
if (rowCount > 0)
{
for (int i = 0; i < rowCount; i++)
{
testtable table = new testtable();
table.col1 = setTable.Rows[i]["col1"].ToString();
table.col2 = setTable.Rows[i]["col2"].ToString();
listTable.Add(table);
}
}
return(listTable);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public testtable GetModel(string id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select col1,col2 from testtable ");
strSql.Append(" where col1 =@id");
OleDbParameter parameter = new OleDbParameter("@id", OleDbType.VarChar);
parameter.Value = id;
OleDbParameter[] parameters = { parameter };
testtable model = new testtable();
DataSet ds = DbHelperOleDb.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
model.col1 = ds.Tables[0].Rows[0]["col1"].ToString();
model.col2 = ds.Tables[0].Rows[0]["col2"].ToString();
return(model);
}
else
{
return(null);
}
}
public List <testtable> GetList(string where)
{
List <testtable> testList = new List <testtable>();
StringBuilder strSql = new StringBuilder();
//select col1,col2 from testtable;
strSql.Append("select col1,col2 from testtable");
strSql.Append(where);
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
IEnumerable collections = dataContext.ExecuteQuery((new testtable()).GetType(), strSql.ToString());
foreach (var item in collections)
{
testtable temp = item as testtable;
testList.Add(temp);
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
return(testList);
}
public void Delete(testtable table)
{
StringBuilder strSql = new StringBuilder();
//delete from testtable where col1='55'
strSql.Append("delete from testtable ");
strSql.Append(string.Format("where col1='{0}'", table.col1));
//strSql.Append("where '{0}'='{0}' and col1='{1}'");
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
int x = dataContext.ExecuteCommand(strSql.ToString());
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
}
public void Save(testtable table)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into testtable(");
strSql.Append("col1,col2)");
strSql.Append(" values (");
strSql.Append(string.Format("'{0}','{1}'", table.col1, table.col2));
strSql.Append(")");
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
int x = dataContext.ExecuteCommand(strSql.ToString());
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
ContexttestTableService service = new ContexttestTableService();
testtable table = new testtable();
table.col1 = "66";
service.Delete(table);
MessageBox.Show("删除成功！");
}
private void btnAddLinq_Click(object sender, EventArgs e)
{
ContexttestTableService service = new ContexttestTableService();
testtable table = new testtable();
table.col1 = "99999";
table.col2 = "000000";
service.Save(table);
MessageBox.Show("增加数据成功！");
}
public void Insert_And_Delete_Test()
{
// Arrange
IDBConnector pConnector = new MySqlConnector();
pConnector.DoOpen();
testtable sTable = new testtable();
sTable.intvalue = 1;
sTable.stringvalue = "asdf";
// Act
// Insert
pConnector.PopCommand($"INSERT INTO {pConnector.ConvertQueryString(sTable)}").ExecuteNonQuery();
// Assert
DataTable pTable = new DataTable();
var pCommand_Select = pConnector.PopCommand($"SELECT * FROM {sTable.GetTableName()} WHERE {nameof(testtable.stringvalue)} = '{sTable.stringvalue}';");
using (MySqlDataAdapter adpt = new MySqlDataAdapter())
{
adpt.SelectCommand = (MySqlCommand)pCommand_Select;
adpt.Fill(pTable);
}
Assert.IsTrue(pTable.Rows.Count > 0);
// Act
// Delete
pConnector.PopCommand($"DELETE FROM {sTable.GetTableName()} WHERE {nameof(testtable.stringvalue)} = '{sTable.stringvalue}'; ").ExecuteNonQuery();
pTable.Clear();
// Assert
pCommand_Select = pConnector.PopCommand($"SELECT * FROM {sTable.GetTableName()} WHERE {nameof(testtable.stringvalue)} = '{sTable.stringvalue}';");
using (MySqlDataAdapter adpt = new MySqlDataAdapter())
{
adpt.SelectCommand = (MySqlCommand)pCommand_Select;
adpt.Fill(pTable);
}
pCommand_Select.Dispose();
Assert.IsTrue(pTable.Rows.Count == 0);
pConnector.DoClose();
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(testtable model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update testtable set ");
strSql.Append("col1=@col1,");
strSql.Append("col2=@col2");
strSql.Append(" where ");
OleDbParameter[] parameters =
{
new OleDbParameter("@col1", OleDbType.VarChar, 255),
new OleDbParameter("@col2", OleDbType.VarChar, 255)
};
parameters[0].Value = model.col1;
parameters[1].Value = model.col2;
DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
}
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(testtable model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into testtable(");
strSql.Append("col1,col2)");
strSql.Append(" values (");
strSql.Append("@col1,@col2)");
OleDbParameter[] parameters =
{
new OleDbParameter("@col1", OleDbType.VarChar, 255),
new OleDbParameter("@col2", OleDbType.VarChar, 255)
};
parameters[0].Value = model.col1;
parameters[1].Value = model.col2;
DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
}
public void Delete(testtable table)
{
}
public void Update(testtable table)
{
dal.Update(table);
}
public void Save(testtable table)
{
dal.Add(table);
}
public void Update(testtable table)
{
}