数据库的操作
多个数据库连接，通过不同的连接字符串进行连接
public static class _DbContext
{
//private static DbContextOptions<IgbomContext> _contextOptions;
public static IgbomContext igbomContext(string mySqlConnectionString)
{
DbContextOptions<IgbomContext> _contextOptions = new DbContextOptionsBuilder<IgbomContext>()
.UseMySql(mySqlConnectionString)
.Options;
var _igbomContext = new IgbomContext(_contextOptions);
return _igbomContext;
}
public static ModelInfoContext ModelInfoContext(string mySqlConnectionString)
{
DbContextOptions<ModelInfoContext> _contextOptions = new DbContextOptionsBuilder<ModelInfoContext>()
.UseMySql(mySqlConnectionString)
.Options;
var _modelInfoContext = new ModelInfoContext(_contextOptions);
return _modelInfoContext;
}
public static NodeContext NodeContext(string mySqlConnectionString)
{
DbContextOptions<NodeContext> _contextOptions = new DbContextOptionsBuilder<NodeContext>()
.UseMySql(mySqlConnectionString)
.Options;
var _nodeContext = new NodeContext(_contextOptions);
return _nodeContext;
}
public static NewIgbomContext NewIgbomContext(string mySqlConnectionString)
{
DbContextOptions<NewIgbomContext> _contextOptions = new DbContextOptionsBuilder<NewIgbomContext>()
.UseMySql(mySqlConnectionString)
.Options;
var _newIgbomContext = new NewIgbomContext(_contextOptions);
return _newIgbomContext;
}
}
原生的数据库连接
/// <summary>
/// 通用数据库类MySQL
/// </summary>
public class Class_mysql_conn
{
//public static string ConnStr = @"server=数据库;uid=帐号;pwd=密码;database=数据库;charset=utf8";
public static string ConnStr = @"server=139.224.43.134:3306;uid=galp;pwd=galp123456;database=igbom;charset=utf8";
//打开数据库链接
public static MySqlConnection Open_Conn(string ConnStr)
{
MySqlConnection Conn = new MySqlConnection(ConnStr);
Conn.Open();
return Conn;
}
//关闭数据库链接
public static void Close_Conn(MySqlConnection Conn)
{
if (Conn != null)
{
Conn.Close();
Conn.Dispose();
}
GC.Collect();
}
//运行MySql语句
public static int Run_SQL(string SQL, string ConnStr)
{
MySqlConnection Conn = Open_Conn(ConnStr);
MySqlCommand Cmd = Create_Cmd(SQL, Conn);
try
{
int result_count = Cmd.ExecuteNonQuery();
Close_Conn(Conn);
return result_count;
}
catch (Exception e)
{
Close_Conn(Conn);
return 0;
}
}
// 生成Command对象
public static MySqlCommand Create_Cmd(string SQL, MySqlConnection Conn)
{
MySqlCommand Cmd = new MySqlCommand(SQL, Conn);
return Cmd;
}
// 运行MySql语句返回 DataTable
public static DataTable Get_DataTable(string SQL, string ConnStr, string Table_name)
{
MySqlDataAdapter Da = Get_Adapter(SQL, ConnStr);
DataTable dt = new DataTable(Table_name);
Da.Fill(dt);
return dt;
}
// 运行MySql语句返回 MySqlDataReader对象
public static MySqlDataReader Get_Reader(string SQL, string ConnStr)
{
MySqlConnection Conn = Open_Conn(ConnStr);
MySqlCommand Cmd = Create_Cmd(SQL, Conn);
MySqlDataReader Dr;
try
{
Dr = Cmd.ExecuteReader(CommandBehavior.Default);
}
catch
{
throw new Exception(SQL);
}
Close_Conn(Conn);
return Dr;
}
// 运行MySql语句返回 MySqlDataAdapter对象
public static MySqlDataAdapter Get_Adapter(string SQL, string ConnStr)
{
MySqlConnection Conn = Open_Conn(ConnStr);
MySqlDataAdapter Da = new MySqlDataAdapter(SQL, Conn);
return Da;
}
// 运行MySql语句,返回DataSet对象
public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds)
{
MySqlDataAdapter Da = Get_Adapter(SQL, ConnStr);
try
{
Da.Fill(Ds);
}
catch (Exception Err)
{
throw Err;
}
return Ds;
}
// 运行MySql语句,返回DataSet对象
public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds, string tablename)
{
MySqlDataAdapter Da = Get_Adapter(SQL, ConnStr);
try
{
Da.Fill(Ds, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
return Ds;
}
// 运行MySql语句,返回DataSet对象，将数据进行了分页
public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds, int StartIndex, int PageSize, string tablename)
{
MySqlConnection Conn = Open_Conn(ConnStr);
MySqlDataAdapter Da = Get_Adapter(SQL, ConnStr);
try
{
Da.Fill(Ds, StartIndex, PageSize, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
Close_Conn(Conn);
return Ds;
}
// 返回MySql语句执行结果的第一行第一列
public static string Get_Row1_Col1_Value(string SQL, string ConnStr)
{
MySqlConnection Conn = Open_Conn(ConnStr);
string result;
MySqlDataReader Dr;
try
{
Dr = Create_Cmd(SQL, Conn).ExecuteReader();
if (Dr.Read())
{
result = Dr[0].ToString();
Dr.Close();
}
else
{
result = "";
Dr.Close();
}
}
catch
{
throw new Exception(SQL);
}
Close_Conn(Conn);
return result;
}
}
使用：
/////批量插入造数据:3个建筑，3*10个楼层，3*10*1000个墙，3*10*1000*2个窗
/// <summary>
/// test
/// </summary>
/// <returns></returns>
[HttpGet("insertData")]
public IActionResult insertData()
{
string ConnStr = @"Server = 192.168.0.31; database = modelinfo; uid = user1; pwd = 123456;sslmode=none";
for (int i = 0; i < 3; i++)
{
int buildingId = i + 1;
string sqlbuilding = "INSERT INTO `building` (`id`,`name` ,`province`,`city` ,`project_id` ,`longitude` ,`latitude`,`number_of_people`, `area` ,`building_type_id` ,`image` ,`add`,`years`,`num_floors`,`building_structure`)" +
" VALUES (" + buildingId + ",'建筑" + buildingId + "', '上海', '上海', '1', '222', '222', '1111', '1231', '1','','某某路12号','251','555','10')";
Class_mysql_conn.Run_SQL(sqlbuilding, ConnStr);
for (int j = 0; j < 10; j++)
{
int floorId = i * 10 + (j + 1);
string sqlfloor = "INSERT INTO `floor` (`id`,`name` ,`number_of_people`, `area` ,`building_id` ,`image`)" +
" VALUES (" + floorId + ",'楼层" + floorId + "', '222', '222', " + i + ", '1231')";
Class_mysql_conn.Run_SQL(sqlfloor, ConnStr);
for (int n = 0; n < 1000; n++)
{
Random rd = new Random();
int wallId = (i * 10 + j) * 1000 + (n + 1);
string sqlwall = "INSERT INTO `wall` (`id`,`name` ,`height`,`width` ,`area` ,`drs` ,`consistency`,`project_id`, `building_id` ,`floor_id` )" +
" VALUES (" + wallId + ",'墙" + wallId + "', " + (rd.Next(1, 100) * 0.11 + n * rd.Next(100) * 0.01) + ", " + (rd.Next(1, 100) * 0.11 + n * rd.Next(100) * 0.01) + ", " + (rd.Next(1, 100) * 0.11 + n * rd.Next(100) * 0.01) + ", " + (rd.Next(1, 100) * 0.11 + n * rd.Next(100) * 0.01) + "," + (rd.Next(1, 100) * 0.11 + n * rd.Next(100) * 0.01) + ",1," + buildingId + "," + floorId + ")";
Class_mysql_conn.Run_SQL(sqlwall, ConnStr);
for (int win = 0; win < 2; win++)
{
int windowId = ((i * 10 + j) * 1000 + n) * 2 + (win + 1);
string sqlwindow = "INSERT INTO `window` (`id`,`name` ,`height`,`width` ,`area` ,`drs` ,`consistency`,`project_id`, `building_id` ,`floor_id`,`wall_id`)" +
" VALUES (" + windowId + ",'窗" + windowId + "'," + (rd.Next(1, 100) * 0.11 + n * win * rd.Next(100) * 0.01) + ", " + (rd.Next(1, 100) * 0.11 + n * win * rd.Next(100) * 0.01) + ", " + (rd.Next(1, 100) * 0.11 + n * win * rd.Next(100) * 0.01) + ", " + (rd.Next(1, 100) * 0.11 + n * win * rd.Next(100) * 0.01) + "," + (rd.Next(1, 100) * 0.11 + n * win * rd.Next(100) * 0.01) + ",1," + buildingId + "," + floorId + "," + wallId + ")";
Class_mysql_conn.Run_SQL(sqlwindow, ConnStr);
}
}
}
}
return Content("ok");
}
}
