using CSFramework.DB;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Threading;
|
using System.Windows.Forms;
|
|
namespace CSFramework.DBTester
|
{
|
public partial class frmTestDB : Form
|
{
|
//string _Conn = "Server=.;Database=CSFramework_WebAPI_Log;User ID=sa;Password=test; Max Pool Size=512; Pooling=true;Connect Timeout=30";
|
|
DatabaseType _DatabaseType = DatabaseType.SqlServer;
|
|
public frmTestDB()
|
{
|
InitializeComponent();
|
}
|
|
private void frmTestDB_Load(object sender, EventArgs e)
|
{
|
ShowResult("默认为SQLServer连接");
|
btnMSSQL_Click(btnMSSQL, null);
|
}
|
|
private void btnMSSQL_Click(object sender, EventArgs e)
|
{
|
_DatabaseType = DatabaseType.SqlServer;
|
txtConn.Text = "Server=.;Database=CSFrameworkV5_Normal;User ID=sa;Password=test; Max Pool Size=512; Pooling=true;Connect Timeout=30";
|
ShowResult("设置为SQLServer连接");
|
}
|
|
private void btnMySql_Click(object sender, EventArgs e)
|
{
|
_DatabaseType = DatabaseType.MySQL;
|
txtConn.Text = "Server=120.79.85.97;Port=3306;Database=cs5_normal;User ID=root;Password=xxxxxxxx;Connection TimeOut=30;Charset=utf8;Pooling=true;Min Pool Size=0;Max Pool Size=500;Connection Lifetime=0;Convert Zero Datetime=True;";
|
ShowResult("设置为MySQL连接");
|
MessageBox.Show("请修改connstring的密码");
|
txtConn.Focus();
|
}
|
|
public class UserModel
|
{
|
public string Account { get; set; }
|
public string Password { get; set; }
|
}
|
|
private IDatabase _DB
|
{
|
get
|
{
|
return DatabaseFactory.CreateMDB(DatabaseType.SqlServer, txtConn.Text);
|
}
|
}
|
|
|
private void button1_Click(object sender, EventArgs e)
|
{
|
|
string s = _DB.ConnectionString;
|
ShowResult(s);
|
|
DbCommand o2 = _DB.CreateCommand("SELECT GETDATE()", System.Data.CommandType.Text);
|
object o9 = _DB.ExecuteCommand(o2);
|
ShowResult(o9);
|
|
DbCommand c1 = _DB.CreateCommand("SELECT * FROM sys_Log", System.Data.CommandType.Text);
|
object o20 = _DB.GetDataSet(c1);
|
ShowResult(o20);
|
|
object o3 = _DB.CreateCommandBuilder();
|
ShowResult(o3);
|
|
object o4 = _DB.CreateConnection(txtConn.Text);
|
ShowResult(o4);
|
(o4 as IDisposable).Dispose();
|
|
|
object o5 = _DB.CreateDataAdapter();
|
ShowResult(o5);
|
|
object o6 = _DB.CreateParameter("@asd", "asdf");
|
ShowResult(o6);
|
|
object o8 = _DB.DateTimeType;
|
ShowResult(o8);
|
|
object o10 = _DB.ExecuteReader("SELECT * FROM sys_Log");
|
ShowResult(o10);
|
|
object o11 = _DB.ExecuteScalar("SELECT * FROM sys_Log");
|
ShowResult(o11);
|
|
object o12 = _DB.ExecuteSQL("SELECT * FROM sys_Log");
|
ShowResult(o12);
|
|
DbTransaction tran = _DB.TransBegin();
|
object o13 = _DB.ExecuteTrans(tran, "SELECT * FROM sys_Log");
|
ShowResult(o13);
|
_DB.TransRollback(tran);
|
|
object o14 = _DB.GetDataRow("SELECT TOP 1 * FROM sys_Log");
|
ShowResult(o14);
|
|
object o15 = _DB.GetDataSet("SELECT TOP 1 * FROM sys_Log");
|
ShowResult(o15);
|
|
object o16 = _DB.GetTable("SELECT TOP 1 * FROM sys_Log");
|
ShowResult(o16);
|
|
object o17 = _DB.ParamSymboName;
|
ShowResult(o17);
|
|
object o18 = _DB.GetServerTime();
|
ShowResult(o18);
|
|
DbParameter p = _DB.CreateParameter("@TableName", "sys_Log");
|
object o19 = _DB.GetDataSet("sp_sys_GetTableFieldDef", System.Data.CommandType.StoredProcedure, new System.Data.IDataParameter[] { p });
|
ShowResult(o19);
|
}
|
|
private void ShowResult(object o)
|
{
|
if (o != null)
|
txtResult.Text = txtResult.Text + "\r\n" + o.ToString();
|
else
|
txtResult.Text = txtResult.Text + "\r\n对象=null";
|
|
txtResult.SelectionStart = txtResult.Text.Length;
|
txtResult.ScrollToCaret();
|
}
|
|
private void button2_Click(object sender, EventArgs e)
|
{
|
DbProviderFactory factory1 = DataProviderFactory.GetFactory(DatabaseType.SqlServer);
|
ShowResult(factory1);
|
|
DbProviderFactory factory2 = DataProviderFactory.GetFactory(DatabaseType.MySQL);
|
ShowResult(factory2);
|
|
DbProviderFactory factory3 = DataProviderFactory.GetFactory(DatabaseType.Oracle);
|
ShowResult(factory3);
|
}
|
|
private void button3_Click(object sender, EventArgs e)
|
{
|
string _Conn = txtConn.Text;
|
|
IDatabase db1 = DatabaseFactory.CreateMDB(_DatabaseType, _Conn);
|
ShowResult(db1);
|
ShowResult(db1.GetServerTime());
|
|
//IDatabase db2 = DatabaseFactory.CreateMDB(_DatabaseType, _Conn);
|
//ShowResult(db2);
|
//ShowResult(db2.GetServerTime());
|
|
//IDatabase db3 = DatabaseFactory.CreateMDB(DatabaseType.Oracle, _Conn);
|
//ShowResult(db3);
|
//ShowResult(db3.GetServerTime());
|
|
//IDatabase db4 = DatabaseFactory.CreateDatabase(_DatabaseType, _Conn);
|
//ShowResult(db4);
|
//ShowResult(db4.GetServerTime());
|
|
//IDatabase db5 = DatabaseFactory.CreateDatabase(DatabaseType.Oracle, _Conn);
|
//ShowResult(db5);
|
//ShowResult(db5.GetServerTime());
|
|
IDatabase db6 = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, _Conn);
|
ShowResult(db6);
|
ShowResult(db6.GetServerTime());
|
|
}
|
|
private void button4_Click(object sender, EventArgs e)
|
{
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, txtConn.Text);
|
DataTable dt = db.GetTable("SELECT TOP 10 * FROM dbo.sys_Log", "sys_Log");
|
dataGridView1.AutoGenerateColumns = true;
|
dataGridView1.DataSource = dt;
|
}
|
|
private void button5_Click(object sender, EventArgs e)
|
{
|
DatabaseFactory.CommandTimeOut = 60 * 5; //设置DbCommand命令超时：5分钟
|
DatabaseFactory.ConnectionTimeOut = 35;//设置数据库连接超时：35秒钟
|
|
//连接字符串指定连接超时，25秒
|
string connectionString = "Server=.;Database=CSFramework_WebAPI;User ID=sa;Password=test;Connection Timeout=25;";
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, connectionString);
|
DbConnection conn = db.CreateConnection();//35s
|
int a = conn.ConnectionTimeout;
|
ShowResult(a);
|
|
//连接字符串没有指定连接超时，自动添加Connection Timeout属性
|
string connectionString1 = "Server=.;Database=CSFramework_WebAPI;User ID=sa;Password=test;";
|
IDatabase db1 = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, connectionString1);
|
DbConnection conn1 = db1.CreateConnection();
|
int a1 = conn1.ConnectionTimeout;//35s
|
ShowResult(a1);
|
|
//Console.Write(a1);
|
//Console.Write(conn1.ConnectionString);
|
|
//测试命令超时
|
DbCommand cmd = db.CreateCommand("SELECT COUNT(*) FROM Api_User", CommandType.Text);
|
cmd.Connection = conn;
|
int i = cmd.CommandTimeout;
|
object o = cmd.ExecuteScalar();
|
ShowResult(o);
|
Console.Write(a);//300s
|
|
conn.Dispose();
|
conn1.Dispose();
|
}
|
|
private void button6_Click(object sender, EventArgs e)
|
{
|
//
|
//测试案例：提交DataTable，批量修改提交多条记录
|
//
|
string conn = "Data Source=127.0.0.1;Initial Catalog=CSFramework_License;User ID=sa;Password =test;Persist Security Info=True;Connect Timeout=15;";
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, conn);
|
|
var dt = db.GetTable("SELECT TOP 10 * FROM dbo.sys_RegisterLog");
|
dt.Rows[0]["HID"] = "修改HID";//修改一条记录
|
dt.Rows[1].Delete();//删除一条记录
|
|
var row = dt.Rows.Add();//新增一条记录
|
row["LicenseID"] = "xxxx";
|
row["RegisterNo"] = "xxxx";
|
row["RegisterTime"] = DateTime.Now;
|
row["LogConent"] = "xxxx";
|
row["HID"] = "xxxx";
|
|
//根据Model提交数据
|
int i = new DbFramework(db).Update(dt, typeof(sys_RegisterLog), new string[] { "isid" }, new string[] { "isid" });
|
|
ShowResult($"共更新记录数：{i}");
|
}
|
|
private void button7_Click(object sender, EventArgs e)
|
{
|
//
|
//测试案例：提交DataSet，批量修改、提交多表多条记录
|
//
|
string conn = "Data Source=127.0.0.1;Initial Catalog=CSFramework_License;User ID=sa;Password =test;Persist Security Info=True;Connect Timeout=15;";
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, conn);
|
|
//表1
|
var dt = db.GetTable("SELECT TOP 10 * FROM dbo.sys_RegisterLog ORDER BY isid ASC", "sys_RegisterLog");
|
dt.Rows[0]["HID"] = "修改HID";//修改一条记录
|
dt.Rows[1].Delete();//删除一条记录
|
|
var row = dt.Rows.Add();//新增一条记录
|
row["LicenseID"] = "xxxx";
|
row["RegisterNo"] = "xxxx";
|
row["RegisterTime"] = DateTime.Now;
|
row["LogConent"] = "xxxx";
|
row["HID"] = "xxxx";
|
|
var row2 = dt.Rows.Add();//新增一条记录
|
row2["LicenseID"] = "xxxx22";
|
row2["RegisterNo"] = "xxxx22";
|
row2["RegisterTime"] = DateTime.Now;
|
row2["LogConent"] = "xxxx22";
|
row2["HID"] = "xxxx22";
|
|
//表2
|
var dt1 = db.GetTable("SELECT TOP 10 * FROM dbo.sys_AdminUser ORDER BY isid DESC", "sys_AdminUser");
|
var row1 = dt1.Rows[0];
|
row1["AccountName"] = row1["AccountName"].ToString() + "-1";
|
row1["LastLoginTime"] = DateTime.Now;//修改数据
|
|
//
|
//重要！！！ 构建ORM对象模型与表结构的关系
|
//sys_RegisterLog表模型关系
|
//sys_AdminUser表模型关系
|
List<DbDataUpdateORM> orms = new List<DbDataUpdateORM>();
|
orms.Add(new DbDataUpdateORM(typeof(sys_RegisterLog), new string[] { "isid" }, new string[] { "isid" }));
|
orms.Add(new DbDataUpdateORM(typeof(sys_AdminUser), new string[] { "Account" }, new string[] { "isid" }));
|
|
//组合DataSet, 一次性提交多张表
|
DataSet ds = new DataSet();
|
ds.Tables.Add(dt.GetChanges());
|
ds.Tables.Add(dt1.GetChanges());
|
|
//提交数据
|
int i = 0;
|
DbTransaction tran = db.TransBegin();
|
DbFramework updater = new DbFramework(db, tran);
|
try
|
{
|
i = updater.Update(ds, orms);//提交数据
|
|
tran.Commit();
|
}
|
catch (Exception ex)
|
{
|
tran.Rollback();
|
ShowResult(ex.Message);
|
}
|
|
ShowResult($"共更新记录数：{i}");
|
}
|
|
private void button8_Click(object sender, EventArgs e)
|
{
|
//
|
//测试案例：新增一个对象，根据对象ORM自动存储到数据库
|
//
|
string conn = "Data Source=127.0.0.1;Initial Catalog=CSFramework_License;User ID=sa;Password =test;Persist Security Info=True;Connect Timeout=15;";
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, conn);
|
|
sys_AdminUser user0 = new sys_AdminUser
|
{
|
Account = "Test-" + DateTime.Now.ToString("yyMMddHHmmss"),
|
AccountName = "test",
|
AccountType = "xx",
|
Password = "pwd",
|
LoginCount = 1929,
|
Amount = decimal.Parse("1231.22"),
|
};
|
|
//新增一个对象
|
bool b = new DbFramework(db).AddObject(user0, new string[] { "isid" });
|
ShowResult($"更新{b}");
|
|
}
|
|
private void button9_Click(object sender, EventArgs e)
|
{
|
//
|
//测试案例：修改对象，根据对象ORM自动存储到数据库
|
//
|
string conn = "Data Source=127.0.0.1;Initial Catalog=CSFramework_License;User ID=sa;Password =test;Persist Security Info=True;Connect Timeout=15;";
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, conn);
|
|
sys_RegisterLog log = db.ExecuteReader<sys_RegisterLog>("SELECT * FROM dbo.sys_RegisterLog where isid=133");
|
log.HID = "xxxxxxxxxxxxxxxxxxxx";
|
log.LicenseID = "asdfasdfasf";
|
log.LogConent = "asfdasdfasdfasdfasdf";
|
log.RegisterNo = "xxxxxxxxxxxxxxxxx";
|
log.RegisterTime = DateTime.Now;
|
|
//修改对象
|
bool ok = new DbFramework(db).UpdateObject(log, new string[] { "isid" }, new string[] { "isid" });
|
ShowResult($"操作{ok}");
|
}
|
|
private void button10_Click(object sender, EventArgs e)
|
{
|
//
|
//测试案例：删除一个对象，根据对象ORM自动自动删除记录
|
//
|
|
string conn = "Data Source=127.0.0.1;Initial Catalog=CSFramework_License;User ID=sa;Password =test;Persist Security Info=True;Connect Timeout=15;";
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, conn);
|
|
var isid = db.ExecuteScalar("SELECT TOP 1 ISID FROM sys_RegisterLog");
|
|
sys_RegisterLog log = new sys_RegisterLog();
|
log.isid = int.Parse(isid.ToString());
|
|
//删除一个对象
|
bool ok = new DbFramework(db).DeleteObject(log, new string[] { "isid" });
|
ShowResult($"操作{ok}");
|
}
|
|
private void button11_ChangeUICues(object sender, UICuesEventArgs e)
|
{
|
|
}
|
|
private void button11_Click(object sender, EventArgs e)
|
{
|
//
|
//测试案例：修改对象列表，根据对象ORM自动存储到数据库
|
//
|
|
string conn = "Data Source=127.0.0.1;Initial Catalog=CSFramework_License;User ID=sa;Password =test;Persist Security Info=True;Connect Timeout=15;";
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, conn);
|
|
sys_AdminUser user = new sys_AdminUser
|
{
|
Account = "000011",
|
AccountName = "test11",
|
AccountType = "xx111111111111111111",
|
Password = "pwd11111111"
|
};
|
|
sys_AdminUser user2 = new sys_AdminUser
|
{
|
Account = "000022",
|
AccountName = "test22222222222",
|
AccountType = "xx222222222222222222",
|
Password = "pwd222222222"
|
};
|
|
List<sys_AdminUser> list = new List<sys_AdminUser>();
|
list.Add(user);
|
list.Add(user2);
|
|
//修改对象列表
|
int i = new DbFramework(db).UpdateList<sys_AdminUser>(list, new string[] { "Account" }, new string[] { "isid" });
|
ShowResult($"批量更新记录{i}");
|
|
}
|
|
private void button12_Click(object sender, EventArgs e)
|
{
|
//
|
//测试案例：删除对象列表，根据对象ORM自动删除记录
|
//
|
string conn = "Data Source=127.0.0.1;Initial Catalog=CSFramework_License;User ID=sa;Password =test;Persist Security Info=True;Connect Timeout=15;";
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, conn);
|
|
var isid = db.GetStringList("SELECT TOP 2 ISID FROM sys_RegisterLog");
|
|
sys_RegisterLog R1 = new sys_RegisterLog
|
{
|
isid = int.Parse(isid[0]),
|
};
|
|
sys_RegisterLog R2 = new sys_RegisterLog
|
{
|
isid = int.Parse(isid[1]),
|
};
|
|
List<sys_RegisterLog> list = new List<sys_RegisterLog>();
|
list.Add(R1);
|
list.Add(R2);
|
|
//删除一个对象列表
|
int i = new DbFramework(db).DeleteList<sys_RegisterLog>(list, new string[] { "isid" });
|
ShowResult($"批量删除记录{i}");
|
}
|
|
private void button13_Click(object sender, EventArgs e)
|
{
|
//
|
//测试案例：新增对象列表，根据对象ORM自动存储到数据库
|
//
|
string conn = "Data Source=127.0.0.1;Initial Catalog=CSFramework_License;User ID=sa;Password =test;Persist Security Info=True;Connect Timeout=15;";
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, conn);
|
|
sys_AdminUser user = new sys_AdminUser
|
{
|
Account = "Test-" + DateTime.Now.ToString("yyMMddHHmmssfff"),
|
AccountName = "test",
|
AccountType = "xx",
|
Password = "pwd"
|
};
|
|
Thread.Sleep(50);
|
|
sys_AdminUser user2 = new sys_AdminUser
|
{
|
Account = "Test-" + DateTime.Now.ToString("yyMMddHHmmssfff"),
|
AccountName = "test2",
|
AccountType = "xx",
|
Password = "pwd"
|
};
|
|
List<sys_AdminUser> list = new List<sys_AdminUser>();
|
list.Add(user);
|
list.Add(user2);
|
|
//提交数据
|
int i = 0;
|
DbTransaction tran = db.TransBegin();
|
DbFramework updater = new DbFramework(db, tran);
|
try
|
{
|
i = updater.AddList<sys_AdminUser>(list, new string[] { "isid" });
|
|
tran.Commit();
|
}
|
catch (Exception ex)
|
{
|
tran.Rollback();
|
ShowResult(ex.Message);
|
}
|
|
ShowResult($"共更新记录数：{i}");
|
}
|
|
private void button14_Click(object sender, EventArgs e)
|
{
|
string conn = "Data Source=127.0.0.1;Initial Catalog=CSFrameworkV5_Normal;User ID=sa;Password =test;Persist Security Info=True;Connect Timeout=15;";
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, conn);
|
|
var dt = db.GetTable("select TOP 1 * from _TestDataUpdate");
|
|
ShowResult($"查询成功,记录{dt.Rows.Count}");
|
|
DbFramework update = new DbFramework(db);
|
|
_TestDataUpdate m = new _TestDataUpdate
|
{
|
_Binary = new byte[] { 1, 2, 3, 4, 5, 56 },
|
_Bit = true,
|
_Char = 'A',
|
_Int = 100000,
|
_Datetime2 = DateTime.Now,
|
_DateTimeOffset = new DateTimeOffset(DateTime.Now),
|
_Timestamp = new DateTime(),
|
_Image = new byte[] { 1, 2, 3, 4, 5, 56 },
|
_Ntext = "_Ntext_Ntext_Ntext_Ntext_Ntext",
|
_Sql_variant = "_Sql_variant",//可变数据类型
|
_UniqueIdentifier = Guid.NewGuid(),
|
_Varbinary = new byte[] { 1, 2, 3, 4, 5, 56 },
|
|
//标准XML格式
|
_Xml = "<?xml version=\"1.0\"?><configuration><startup><supportedRuntime version=\"v4.0\" sku=\".NETFramework,Version=v4.5\"/></startup></configuration>",
|
|
//SQL SERVER 2008支持两种空间数据类型，geometry和geography.其中geometry数据类型支持平面或平面球数据,
|
//geography可用于存储GPS经度和纬度坐标等椭球体数据。
|
//geometry和geography数据类型基于geometry层次结构。
|
_Geography = "POINT(55.9271035250276 -3.29431266523898)", //Geography 类型数据结构
|
_Geometry = "POINT(55.9271035250276 -3.29431266523898)", //Geometry 类型数据结构
|
|
//摘要:hierarchyid 数据类型，是sqlserver 2008中新增的一个CLR数据类型，此数据类型应用于存储具有层级的结构图
|
_Hierarchyid = "/1/2/2/1/5/3/4/",
|
};
|
|
int isid = 0;
|
|
bool ok1 = update.AddObject<_TestDataUpdate>(m, new string[] { "isid", "_Timestamp" }, out isid);
|
ShowResult($"新增对象结果{ok1}");
|
|
var o = update.Select<_TestDataUpdate>("isid", isid);
|
ShowResult($"查询对象结果{o._UniqueIdentifier}");
|
|
m.isid = 12;
|
bool ok2 = update.UpdateObject<_TestDataUpdate>(m, new string[] { "isid", }, new string[] { "isid", "_Timestamp" });
|
ShowResult($"修改对象结果{ok2}");
|
|
m.isid = 2;
|
bool ok3 = update.DeleteObject<_TestDataUpdate>(m, new string[] { "isid", });
|
ShowResult($"删除对象结果{ok3}");
|
|
}
|
|
private void button15_Click(object sender, EventArgs e)
|
{
|
//解决方案：
|
//连接字符串删除：Allow Zero Datetime=True; 加上：Convert Zero Datetime=True;
|
string conn = "Server={0};Port={1};Database={2};User ID={3};Password={4};Connection TimeOut={5};Charset=utf8;Pooling=true;Min Pool Size=0;Max Pool Size=500;Connection Lifetime=0;Convert Zero Datetime=True;";
|
conn = txtConn.Text;
|
|
IDatabase db = DatabaseFactory.CreateMySqlDatabase(conn);
|
|
var DT = db.GetTable("SELECT * FROM tb_MyUserRoles LIMIT 10;");
|
var row = DT.Rows.Add();
|
row["CreateTime"] = DateTime.Now;
|
DT.AcceptChanges();
|
|
MessageBox.Show(row["CreateTime"].ToString());
|
|
}
|
|
private void button16_Click(object sender, EventArgs e)
|
{
|
IDatabase db = DatabaseFactory.CreateMySqlDatabase(txtConn.Text);
|
|
CommandHelper sp = db.CreateSqlProc("usp_SearchCustomer");
|
sp.AddParam("CustomerFrom", "");
|
sp.AddParam("CustomerTo", "");
|
sp.AddParam("CustomerName", "");
|
sp.AddParam("Attribute", "");
|
|
var DT = db.GetTable(sp.Command, "tb_Customer");
|
}
|
|
private void button17_Click(object sender, EventArgs e)
|
{
|
IDatabase db = DatabaseFactory.CreateMySqlDatabase(txtConn.Text);
|
|
_testdataupdate m = new _testdataupdate
|
{
|
_BinaryCHAR = new byte[] { 1, 2, 3, 4, 5, 56 },
|
_Bit = true,
|
_Char = 'A',
|
_Int = 100000,
|
_Datetime = DateTime.Now,
|
_Timestamp = new DateTime(),
|
_Image = new byte[] { 1, 2, 3, 4, 5, 56 },
|
_Ntext = "_Ntext_Ntext_Ntext_Ntext_Ntext",
|
_Sql_variant = "_Sql_variant",//可变数据类型
|
_UniqueIdentifier = Guid.NewGuid().ToString(),
|
_Varbinary = new byte[] { 1, 2, 3, 4, 5, 56 },
|
|
//标准XML格式
|
_Json = "{}",
|
|
//SQL SERVER 2008支持两种空间数据类型，geometry和geography.其中geometry数据类型支持平面或平面球数据,
|
//geography可用于存储GPS经度和纬度坐标等椭球体数据。
|
//geometry和geography数据类型基于geometry层次结构。
|
_Geography = "POINT(55.9271035250276 -3.29431266523898)", //Geography 类型数据结构
|
_Geometry = "POINT(55.9271035250276 -3.29431266523898)", //Geometry 类型数据结构
|
|
//摘要:hierarchyid 数据类型，是sqlserver 2008中新增的一个CLR数据类型，此数据类型应用于存储具有层级的结构图
|
_Hierarchyid = "/1/2/2/1/5/3/4/",
|
};
|
|
DbFramework update = new DbFramework(db);
|
|
bool ok1 = update.AddObject<_testdataupdate>(m, new string[] { "isid", "_Timestamp" });
|
ShowResult($"新增对象结果{ok1}");
|
|
m.isid = 12;
|
bool ok2 = update.UpdateObject<_testdataupdate>(m, new string[] { "isid", }, new string[] { "isid", "_Timestamp" });
|
ShowResult($"修改对象结果{ok2}");
|
|
m.isid = 2;
|
bool ok3 = update.DeleteObject<_testdataupdate>(m, new string[] { "isid", });
|
ShowResult($"删除对象结果{ok3}");
|
}
|
|
private void button18_Click(object sender, EventArgs e)
|
{
|
IDatabase db = DatabaseFactory.CreateMySqlDatabase(txtConn.Text);
|
|
//查询原数据
|
_testdataupdate o = db.ExecuteReader<_testdataupdate>("SELECT * FROM _testdataupdate WHERE isid=1;");
|
_testdataupdate m = new _testdataupdate();//用于修改资料的对象
|
|
//获取timestamp的值
|
DateTime ts = o._Timestamp;
|
ShowResult($"timestamp值:{ts}");
|
|
//CSFramework.DB组件
|
CSFramework.DB.DbFramework update = new DbFramework(db);
|
|
//测试步骤1：模拟其他用户修改记录
|
m.isid = 1; //isid作为主键更新记录
|
m._Ntext = "模拟其他用户修改记录-isid作为主键更新记录" + Guid.NewGuid().ToString();
|
bool ok1 = update.UpdateObject<_testdataupdate>(m, new string[] { "isid", }, new string[] { "isid", "_Timestamp" });
|
ShowResult($"修改对象结果:{ok1}");
|
|
//查询修改后Timestamp的值
|
_testdataupdate o1 = db.ExecuteReader<_testdataupdate>("SELECT * FROM _testdataupdate WHERE isid=1;");
|
ShowResult($"timestamp值（修改后）:{o1._Timestamp}");
|
|
//测试步骤2：Timestamp类型的字段作为主键更新数据
|
m._Timestamp = o._Timestamp;
|
m._Ntext = "Timestamp类型的字段作为主键更新数据";
|
bool ok2 = update.UpdateObject<_testdataupdate>(m, new string[] { "_Timestamp", }, new string[] { "isid", "_Timestamp" });
|
if (ok2)
|
ShowResult($"修改对象结果:{ok2}");
|
else
|
ShowResult($"原记录已被修改（并发操作）！");
|
|
|
}
|
|
private void button19_Click(object sender, EventArgs e)
|
{
|
//
|
//测试案例：修改对象，根据对象ORM自动存储到数据库
|
//
|
string conn = "Data Source=127.0.0.1;Initial Catalog=CSFramework_License;User ID=sa;Password =test;Persist Security Info=True;Connect Timeout=15;";
|
IDatabase db = DatabaseFactory.CreateDatabase(DatabaseType.SqlServer, conn);
|
|
//从数据库获取对象,最新数据
|
sys_AdminUser user = db.ExecuteReader<sys_AdminUser>("SELECT * FROM sys_AdminUser where isid=49");
|
user.AccountName = "修改用户名称";
|
user.LoginCount = 111999;
|
user.Amount = decimal.Parse("111999.99");
|
|
string[] PKFields = new string[] { "isid" };//主键字段
|
string[] ignoreFields = new string[] { "isid", "Account" };//忽略的字段列表，比如自增字段、时间戳等。
|
string[] updateFields = new string[] { "AccountName", "Amount" };//指定提交数据的字段(ignoreFields参数可以不传值)
|
|
//指定忽略更新的字段
|
bool ok1 = new DbFramework(db).UpdateObject(user, PKFields, ignoreFields);
|
//UPDATE SYS_ADMINUSER SET AccountType=@AccountType,AccountName=@AccountName,Password=@Password,FlagLock=@FlagLock,LastLoginTime=@LastLoginTime,LoginCount=@LoginCount,Amount=@Amount WHERE isid=@isid
|
|
//指定更新的字段
|
bool ok2 = new DbFramework(db).UpdateObject(user, PKFields, null, updateFields);
|
//UPDATE SYS_ADMINUSER SET AccountName=@AccountName,Amount=@Amount WHERE isid=@isid
|
|
//指定忽略更新+更新的字段，此方案ignoreFields参数无效（程序不处理）
|
bool ok3 = new DbFramework(db).UpdateObject(user, PKFields, ignoreFields, updateFields);
|
//UPDATE SYS_ADMINUSER SET AccountName=@AccountName,Amount=@Amount WHERE isid=@isid
|
}
|
|
private void button20_Click(object sender, EventArgs e)
|
{
|
IDatabase db = DatabaseFactory.CreateDatabase(_DatabaseType, txtConn.Text);
|
var list = db.meta_GetTableNamesList();
|
ShowResult($"list={list.Count}");
|
foreach (var o in list) ShowResult(o.name);
|
}
|
|
private void button21_Click(object sender, EventArgs e)
|
{
|
IDatabase db = DatabaseFactory.CreateDatabase(_DatabaseType, txtConn.Text);
|
var list = db.meta_GetDatabaseList();
|
ShowResult($"list={list.Count}");
|
foreach (var o in list) ShowResult(o.DBName);
|
}
|
|
private void button22_Click(object sender, EventArgs e)
|
{
|
IDatabase db = DatabaseFactory.CreateDatabase(_DatabaseType, txtConn.Text);
|
var list = db.meta_GetTableStruList("sys_Log");
|
ShowResult($"list={list.Count}");
|
foreach (var o in list) ShowResult(o.FieldName);
|
}
|
|
private void button23_Click(object sender, EventArgs e)
|
{
|
IDatabase db = DatabaseFactory.CreateDatabase(_DatabaseType, txtConn.Text);
|
|
//测试GetTop方法, 默认参数
|
DataTable dt = db.GetTop(100, "sys_Log", "", null, "");
|
ShowResult($"list={dt.Rows.Count}");
|
|
//测试GetTop方法 - 传入指定参数
|
DbParameter p1 = db.CreateParameter("TableName", "tb_POs");
|
DbParameter p2 = db.CreateParameter("LogUser", "admin");
|
List<DbParameter> list = new List<DbParameter>();
|
list.Add(p1);
|
list.Add(p2);
|
|
DataTable dt1 = db.GetTop(100, "sys_Log", "isid,LogUser,LogDate,TableName", list, "isid DESC");
|
ShowResult($"list={dt1.Rows.Count}");
|
|
dataGridView1.AutoGenerateColumns = true;
|
dataGridView1.DataSource = dt1;
|
}
|
}
|
}
|
