public static void Populate(string connectionString)
{
GenFu.Configure <Genre>().Fill(x => x.Name).AsMusicGenreName();
GenFu.Configure <Artist>().Fill(x => x.Name).AsMusicArtistName();
GenFu.Configure <Album>().Fill(x => x.AlbumArtUrl, "/Content/Images/placeholder.gif").Fill(x => x.Price).WithinRange(100, 500);
var rand = new System.Random(100);
using (var conn = new Npgsql.NpgsqlConnection(connectionString))
{
if (conn.ExecuteScalar <int>("select count(1) from albums ") == 0)
{
for (int i = 0; i < 10; i++)
{
var genre = A.New <Genre>();
var artist = A.New <Artist>();
var genreid = conn.ExecuteScalar <int>("insert into genres (name,description) values (@name,@description) returning genreid;", new{ genre.Name, genre.Description });
var artistid = conn.ExecuteScalar <int>("insert into artists (name) values (@name) returning artistid;", new{ artist.Name });
var albums = A.ListOf <Album>(10);
albums.ForEach(a => {
conn.Execute("insert into albums(genreid,artistid,title,price,albumarturl) values(@genreid,@artistid,@title,@price,@albumarturl) returning albumid;", new { genreid, artistid, a.Title, a.Price, a.AlbumArtUrl });
});
}
}
}
}
/// <summary>
/// 获取数量（配合分页使用）
/// </summary>
/// <param name="keyword"></param>
/// <returns></returns>
public int GetCount(string keyword)
{
string sql = "select count(1) from t_user where 1=1 " + GetCondition(keyword);
using (var conn = new Npgsql.NpgsqlConnection(ConnectionString))
{
return(Convert.ToInt32(conn.ExecuteScalar(sql, new { keyword = "%" + keyword + "%" })));
}
}
public void SavePlayer(PlayerStoreData playerStoreData)
{
Task.Run(() =>
{
using (IDbConnection con = new Npgsql.NpgsqlConnection(connectionString))
{
con.ExecuteScalar <PlayerStoreData>(
@"UPDATE users SET name=@Name, is_activated = @isActivated, rating=@rating
WHERE player_id=@playerId AND player_type=@playerTypeString", playerStoreData);
}
});
}
static void Main(string[] args)
{
var tablenames = File.ReadAllText(@"C:\MyFile\abc\tablenames.txt").Split(',');
var pgconnection = File.ReadAllText(@"C:\MyFile\abc\pgconnection.txt");
var queryPgSqls = File.ReadAllLines(@"C:\MyFile\abc\tablesql.txt");
foreach (var tablename in tablenames)
{
Console.WriteLine($"开始搬运{tablename}数据");
using (var con = new Npgsql.NpgsqlConnection(pgconnection))
{
var deleteSql = con.ExecuteScalar <string>(queryPgSqls[0], new { keyname = tablename });
var selectql = con.ExecuteScalar <string>(queryPgSqls[1], new { keyname = tablename });
var watch = new Stopwatch();
watch.Start();
var date = DateTime.Now.AddDays(-1);
new GenerPgSql().MigrationData(tablename, deleteSql, selectql, date);
Console.WriteLine($"搬运{tablename}数据总共花费{watch.Elapsed.TotalMilliseconds}ms.");
}
}
}
/// <summary>
/// 保存时判断数据是否存在
/// </summary>
/// <param name="system_name">系统名称</param>
/// <param name="system_id">系统id（新增传入空字符串；修改传入id）</param>
/// <returns>存在返回true；不存在返回false</returns>
public bool IsExists(string system_name, string system_id = "")
{
string sql = "";
if (string.IsNullOrWhiteSpace(system_id))//添加判断
{
sql = "select count(1) from t_system where system_id=@system_id and system_name=@system_name ";
using (var conn = new Npgsql.NpgsqlConnection(ConnectionString))
{
object obj = conn.ExecuteScalar(sql, new { system_id = system_id, system_name = system_name });
if (obj != null)
{
return(Convert.ToInt32(obj) > 0 ? true : false);
}
else
{
return(false);
}
}
}
else//修改判断
{
sql = "select system_id from t_system where system_name=@system_name and system_id<>@system_id";
using (var conn = new Npgsql.NpgsqlConnection(ConnectionString))
{
object obj = conn.ExecuteScalar(sql, new { system_id = system_id, system_name = system_name });
if (obj == null)
{
return(false);
}
else
{
string id = obj.ToString();
if (system_id.Equals(id))
{
return(false);
}
else
{
return(true);
}
}
}
}
}