如何在重新打开应用程序时创建新数据库
本文关键字:创建 数据库 应用程序 新打开 | 更新日期: 2023-09-27 18:31:35
我有一个日志查看应用程序,它将日志语句写入数据库,然后将它们从数据库发送到日志查看器 GUI。 我希望能够打开日志查看器的多个实例,但是当我这样做时,它将创建一个与查看器的上一个实例同名的数据库。 如果已经创建了一个数据库,我尝试创建一个具有不同名称的数据库,但这似乎不起作用。 有什么建议吗? 以下是创建/访问/销毁数据库的代码:
public class Database
{
public bool hasAdminPriv
{
get;
set;
}
public bool hasBeenCreated
{
get;
set;
}
public Database()
{
hasAdminPriv = true;
//Construction checks to see if user has Admin priveleges
bool isElevated;
WindowsIdentity identity = WindowsIdentity.GetCurrent();
WindowsPrincipal principal = new WindowsPrincipal(identity);
isElevated = principal.IsInRole(WindowsBuiltInRole.Administrator);
if (!isElevated)
hasAdminPriv = false;
}
//returns true if the database creation was successful
public bool CreateDatabase()//creates a database dynamically by making a query request to the server
{
String str;
SqlConnection myConn = new SqlConnection("Data Source=" + Environment.UserName + "-D1SD''SQLEXPRESS; Initial Catalog=Master;Integrated Security=True");
str = "CREATE DATABASE MyDatabase ON PRIMARY " +
"(NAME = MyDatabase_Data, " +
"FILENAME = 'C:''Program Files (x86)''Microsoft SQL Server''MSSQL.1''MSSQL''Data''MyDatabaseData.mdf', " +
"SIZE = 30MB, MAXSIZE = 10GB, FILEGROWTH = 20%) " +
"LOG ON (NAME = MyDatabase_Log, " +
"FILENAME = 'C:''Program Files (x86)''Microsoft SQL Server''MSSQL.1''MSSQL''Data''MyDatabaseLog.ldf', " +
"SIZE = 10MB, " +
"MAXSIZE = 1GB, " +
"FILEGROWTH = 10%)";
SqlCommand myCommand = new SqlCommand(str, myConn);
try
{
myConn.Open();
myCommand.ExecuteNonQuery();
}
catch (System.Exception ex)
{
int done = 0;
while (done < 10)
{
String str2 = "CREATE DATABASE" + done + " MyDatabase ON PRIMARY " +
"(NAME = MyDatabase_Data, " +
"FILENAME = 'C:''Program Files (x86)''Microsoft SQL Server''MSSQL.1''MSSQL''Data''MyDatabaseData.mdf', " +
"SIZE = 30MB, MAXSIZE = 10GB, FILEGROWTH = 20%) " +
"LOG ON (NAME = MyDatabase_Log, " +
"FILENAME = 'C:''Program Files (x86)''Microsoft SQL Server''MSSQL.1''MSSQL''Data''MyDatabaseLog.ldf', " +
"SIZE = 10MB, " +
"MAXSIZE = 1GB, " +
"FILEGROWTH = 10%)";
SqlCommand myCommand2 = new SqlCommand(str2, myConn);
try{
myCommand2.ExecuteNonQuery();
}
catch{
++done;
}
myConn.Close();
hasBeenCreated = true;
return true;
}
return false;
}
finally
{
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}
hasBeenCreated = true;
return true;
}
//Creates the table in the database by a query request
//a return value of true means Database was created succesfully
public bool CreateTable()
{
SqlConnection myConn = new SqlConnection("Data Source=" + Environment.UserName + "-D1SD''SQLEXPRESS; Initial Catalog=MyDatabase;Integrated Security=True");
string createString = "CREATE TABLE storage (ID INT NOT NULL, Level varchar(255) , LevelInt INT, DateTime varchar(255),Counter smallint,Device varchar(255), Source varchar(255), Description varchar(255),PRIMARY KEY (ID))"; //YOUR SQL COMMAND TO CREATE A TABLE
SqlCommand create = new SqlCommand(createString, myConn);
myConn.Open();
create.ExecuteNonQuery();
myConn.Close();
return true;
}
//Add the element's values to the database/table to later recall/reorder
public bool addElement(LogParse log,int num)
{
SqlConnection con = new SqlConnection("Data Source=" + Environment.UserName + "-D1SD''SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=True");
try
{
con.Open();
SqlCommand cmd = new SqlCommand("Insert into storage(ID, Level, LevelInt, DateTime, Counter, Device, Source, Description) values(" + num + ",@Level, @LevelInt, @DataTimeItem,@counterItem,@deviceItem,@sourceItem,@descItem)", con);
cmd.Parameters.AddWithValue("@Level", log.Level);
cmd.Parameters.AddWithValue("@LevelInt", log.LevelInt);
cmd.Parameters.AddWithValue("@DataTimeItem", log.TimeStamp);
cmd.Parameters.AddWithValue("@counterItem", log.SequentialNumber);
cmd.Parameters.AddWithValue("@deviceItem", log.Device);
cmd.Parameters.AddWithValue("@sourceItem", log.Source);
cmd.Parameters.AddWithValue("@descItem", log.Description);
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ee)
{
return false;
}
return true;
}
//outputs a string array with all the values in the database
public LogParse[] readValue(int start, int end)
{
SqlConnection con = new SqlConnection("Data Source=" + Environment.UserName + "-D1SD''SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=True");
con.Open();
LogParse[] s = new LogParse[end - start];
try
{
using (var oCommand = new SqlCommand("SELECT * From storage WHERE ID BETWEEN @Start AND @End", con))
{
oCommand.Parameters.AddWithValue("@Start", start);
oCommand.Parameters.AddWithValue("@End", end);
using (var oReader = oCommand.ExecuteReader())
{
int i = 0;
while (oReader.Read() && i < end-start)
{
//s[i] = oReader.GetString(1) + oReader.GetString(2) + oReader.GetString(3);
String Level = oReader.GetString(1);
Int32 LevelInt = oReader.GetInt32(2);
String Datetime = oReader.GetString(3);
Int16 SequentialNumber = (Int16)oReader.GetValue(4);
String Device = oReader.GetString(5);
String Source = oReader.GetString(6);
String Description = oReader.GetString(7);
s[i] = new LogParse();
s[i].Level = Level;
s[i].LevelInt = LevelInt;
s[i].TimeStamp = DateTime.Parse(Datetime);
s[i].SequentialNumber = SequentialNumber;
s[i].Device = Device;
s[i].Description = Description;
s[i].Source = Source;
++i;
}
}
}
}
catch
{
}
con.Close();
return s;
}
//Deletes the database by a query statement
//a return value of true means the delete was succesful
public static bool deleteDatabase()
{
String str;
SqlConnection myConn = new SqlConnection("Data Source=" + Environment.UserName + "-D1SD''SQLEXPRESS; Initial Catalog=Master;Integrated Security=True");
str = @"ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DROP DATABASE [MyDatabase]";
SqlCommand myCommand = new SqlCommand(str, myConn);
try
{
myConn.Open();
myCommand.ExecuteNonQuery();
}
catch (System.Exception ex)
{
return false;
}
finally
{
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}
return true;
}
}
不要创建另一个数据库或表,这是邪恶的。只需将实例 ID 添加到表 storage
中即可。写入新日志时只需添加实例 ID,从数据库读取时也是如此,将实例 ID 放入 where 子句中。
另一个问题是选择好的实例 ID,我的方案不是很清楚,但如果您希望每个新的应用程序实例都有单独的数据,只需创建新的 GUID 并将其用作实例 ID。
例如,您可以在数据库类中具有静态属性 InstanceID,您的类可能如下所示:
public class Database
{
public static Guid InstanceID = new Guid();
//Add the element's values to the database/table to later recall/reorder
public bool addElement(LogParse log,int num)
{
SqlConnection con = new SqlConnection("Data Source=" + Environment.UserName + "-D1SD''SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=True");
try
{
con.Open();
SqlCommand cmd = new SqlCommand("Insert into storage(ID, InstanceID, Level, LevelInt, DateTime, Counter, Device, Source, Description) values(" + num + ",@Level, @LevelInt, @DataTimeItem,@counterItem,@deviceItem,@sourceItem,@descItem)", con);
// writing InstanceID
cmd.Parameters.AddWithValue("@InstanceID", Database.InstanceID);
cmd.Parameters.AddWithValue("@Level", log.Level);
cmd.Parameters.AddWithValue("@LevelInt", log.LevelInt);
cmd.Parameters.AddWithValue("@DataTimeItem", log.TimeStamp);
cmd.Parameters.AddWithValue("@counterItem", log.SequentialNumber);
cmd.Parameters.AddWithValue("@deviceItem", log.Device);
cmd.Parameters.AddWithValue("@sourceItem", log.Source);
cmd.Parameters.AddWithValue("@descItem", log.Description);
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ee)
{
return false;
}
return true;
}
//outputs a string array with all the values in the database
public LogParse[] readValue(int start, int end)
{
SqlConnection con = new SqlConnection("Data Source=" + Environment.UserName + "-D1SD''SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=True");
con.Open();
LogParse[] s = new LogParse[end - start];
try
{
// select with InstanceID
using (var oCommand = new SqlCommand("SELECT * From storage WHERE InstanceID = @InsID ID BETWEEN @Start AND @End", con))
{
oCommand.Parameters.AddWithValue("@Start", start);
oCommand.Parameters.AddWithValue("@End", end);
oCommand.Parameters.AddWithValue("@InsID", Database.InstanceID);
using (var oReader = oCommand.ExecuteReader())
{
int i = 0;
while (oReader.Read() && i < end-start)
{
//s[i] = oReader.GetString(1) + oReader.GetString(2) + oReader.GetString(3);
String Level = oReader.GetString(1);
Int32 LevelInt = oReader.GetInt32(2);
String Datetime = oReader.GetString(3);
Int16 SequentialNumber = (Int16)oReader.GetValue(4);
String Device = oReader.GetString(5);
String Source = oReader.GetString(6);
String Description = oReader.GetString(7);
s[i] = new LogParse();
s[i].Level = Level;
s[i].LevelInt = LevelInt;
s[i].TimeStamp = DateTime.Parse(Datetime);
s[i].SequentialNumber = SequentialNumber;
s[i].Device = Device;
s[i].Description = Description;
s[i].Source = Source;
++i;
}
}
}
}
catch
{
}
con.Close();
return s;
}
}
顺便说一句。不要吞下例外!我保留您的代码不变,但请不要这样做,这也是邪恶的,另一件事是如果您的 addElement 方法中存在异常,您的连接将不会关闭,请使用 Using 语句。
听起来一点也不安全,也许一个数据库有多个用户表来存储每个用户的日志文件,但这对我来说听起来不对。
否则,在启动应用程序时随机分配的变量,代替创建数据库时数据库名称。
但是如果你做错了什么,就会有很多数据库,但看起来你已经弄清楚了大小和其他一些事情,以防止它们压倒服务器机器。
来自评论的其他信息
如果您要在数据库上使用表(我认为这是您真正需要的),然后单独创建数据库,然后在每次打开应用程序时在应用程序中创建随机命名的表,您可能希望有另一个表来跟踪表和它们的打开时间, 甚至可能是什么用户打开了它们之类的东西