/// <summary>
/// 数据库是否存在
/// </summary>
public Boolean dbExists(String dbName)
{
bool exist = false;
using (var dr = SQLServerHelper.ExecuteReader(
String.Format(connectionString3, dataSource),
String.Format(Sql.DB_EXISTS, Path.GetFileNameWithoutExtension(dbName), Path.GetDirectoryName(dbName)),
CommandType.Text)) {
exist = dr != null && dr.HasRows;
}
// 数据库文件被手动删除，drop一次
if (exist && !File.Exists(dbName))
{
try {
SQLServerHelper.ExecuteNonQuery(
String.Format(connectionString3, dataSource),
String.Format(Sql.DELETE_DB, Path.GetFileNameWithoutExtension(dbName)),
CommandType.Text);
}
catch (SqlException) { }
finally {
exist = false;
}
}
return(exist);
}
public Boolean InsertSafe(User user)
{
if (user == null)
{
return(false);
}
Int32 recordsAffected = SQLServerHelper.ExecuteNonQuery(
String.Format(connectionString1, getDBName()),
this.getSafeSqlForInsert(),
CommandType.Text,
this.getSafeInsertParams(user));
//// 防止SQL注入
//Int32 recordsAffected = SQLServerHelper.ExecuteNonQuery(
// String.Format(connectionString1, getDBName()),
// "insert into [user] (username,password) values (@username,@password)",
// CommandType.Text,
// new SqlParameter[] {
// new SqlParameter("@username", user.UserName),
// new SqlParameter("@password", user.PassWord),
// });
return(recordsAffected > 0);
}
/// <summary>
/// 创建表
/// </summary>
public Boolean createTable(String dbName, String tableName, Type type)
{
Int32 row = SQLServerHelper.ExecuteNonQuery(
String.Format(connectionString1, dataSource),
String.Format(Sql.CREATE_TABLE, tableName, getTableColumnDefinition(type)),
CommandType.Text);
//using (var dr = SQLServerHelper.ExecuteReader(
// String.Format(connectionString1, dataSource),
// String.Format(Sql.CREATE_TABLE, getFullTableName(dbName, tableName), getTableColumnDefinition(type)),
// CommandType.Text)) {}
return(tableExists(dbName, tableName));
}
public Boolean Insert(User user)
{
if (user == null)
{
return(false);
}
// SQL拼接
Int32 recordsAffected = SQLServerHelper.ExecuteNonQuery(
String.Format(connectionString1, getDBName()),
String.Format("insert into {0} ({1}) values ({2})",
this.getTableName(), this.getFieldString(String.Empty), this.getValueString(user, "'")),
CommandType.Text);
return(recordsAffected > 0);
}
/// <summary>
/// 创建数据库
/// </summary>
public void createDB(String dbName)
{
// 查询数据库是否存在
bool exist = dbExists(dbName);
if (!exist)
{
// 创建数据库
SQLServerHelper.ExecuteNonQuery(
String.Format(connectionString3, dataSource),
String.Format(Sql.CREATE_DB, Path.GetFileNameWithoutExtension(dbName), dbName.Replace(".MDF", "")),
CommandType.Text);
// 数据库是否创建成功
exist = File.Exists(dbName);
}
}
