数据库安装文件制作（C#）
最近在做项目时，将软件打包给技术部门用，里面因为用到数据库，所以最好是在安装文件执行完毕之后，自动还原数据库（sql）
想到了2个方法
- 做成安装包（自定义安装，用数据库备份文件还原）
- 利用sql脚本，生成，但osql还是有限制
这2个方法都是可行的，第二个方法会出现多个文件，第一个方法只有一个安装包；
显然一个方法方便些；
实现思路：
- 找到数据库备份文件
- 安装时还原数据库
- 卸载时删除数据库
重写 public override void Install(IDictionary stateSaver)和public override void Uninstall(IDictionary savedState)方法
自己编写好代码，编译运行，安装，却出现1001错误，找不到数据库备份文件
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Configuration.Install;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Threading;
using System.Diagnostics;
namespace SmartHomeSetup
{
[RunInstaller(true)]
public class Form1 : System.Configuration.Install.Installer
{
private System.ComponentModel.Container components = null;
public Form1()
{
InitializeComponent();
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose(disposing);
}
//返回路径
private static string PrepareSQL(string name)
{
string tempfile = "";
try
{
Assembly asm = Assembly.GetExecutingAssembly();
Stream fs = asm.GetManifestResourceStream(asm.GetName().Name + "." + name);
BinaryReader br = new BinaryReader(fs);
tempfile = Path.GetTempFileName();
FileStream fsWrite = new FileStream(tempfile, FileMode.Create);
BinaryWriter bw = new BinaryWriter(fsWrite);
byte[] data = br.ReadBytes(1024);
while (data != null && data.Length > 0)
{
bw.Write(data);
data = br.ReadBytes(1024);
}
br.Close();
fs.Close();
bw.Close();
fsWrite.Close();
}
catch (System.Exception e)
{
throw e;
}
return tempfile;
}
private void InitializeComponent()
{
components = new System.ComponentModel.Container();
}
public override void Install(IDictionary stateSaver)
{
base.Install(stateSaver);
//创建数据库的连接字符串
string DbConnection =
"Server=" + Context.Parameters["databaseServer"] + ";" +
"Trusted_Connection=true;" +
"Uid=" + Context.Parameters["userName"] + ";" + //用户名
"Pwd=" + Context.Parameters["userPass"]; //密码
//string conn = "Data Source=localhost;Initial Catalog=master;Integrated Security=True";
//根据连接字符串创建 SqlConnetion 连接句柄
SqlConnection Connection = new SqlConnection(DbConnection);
try
{
string path = PrepareSQL("SmartHome.bak");
//MessageBox.Show(path);
string FilepPath = Context.Parameters["sitdb"];//日志目录
if (!Directory.Exists(FilepPath))
{
Directory.CreateDirectory(FilepPath);
}
//还原数据库SmartHome
string ExecuteQuery = "RESTORE DATABASE SmartHome FROM DISK = '" +
path + "' WITH MOVE 'SmartHome ' TO '" + FilepPath + "SmartHome.mdf ', " +
"MOVE 'SmartHome_log ' TO '" + FilepPath + "SmartHome_log.ldf ' ";
SqlCommand command = new SqlCommand(ExecuteQuery, Connection);
command.Connection.Open();
command.ExecuteNonQuery();
Connection.Close();
ExecuteQuery = "USE master " +
"IF NOT EXISTS (SELECT name FROM master.dbo.sysxlogins WHERE name = N'SmartHomeUser') " +
"EXEC sp_addlogin 'SmartHomeUser', '123' ";
SqlCommand cmd = new SqlCommand(ExecuteQuery, Connection);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
Connection.Close();
ExecuteQuery = "USE SmartHome " +
"DECLARE @dd INT " +
"SELECT @dd = count(usu.name) " +
"from " +
"sysusers usu left outer join " +
"(sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid " +
"left outer join master.dbo.syslogins lo on usu.sid = lo.sid " +
"where " +
"(usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1) and " +
"(usg.issqlrole = 1 or usg.uid is null) and usu.name = 'SmartHomeUser' " +
"IF @dd <> 0 " +
"BEGIN " +
"EXEC sp_dropuser 'SmartHomeUser' " +
"END ";
SqlCommand cmd1 = new SqlCommand(ExecuteQuery, Connection);
cmd1.Connection.Open();
cmd1.ExecuteNonQuery();
Connection.Close();
//注释掉这个账户下边的安全性账户程序将不起作用
ExecuteQuery = "USE SmartHome " +
"EXEC sp_adduser 'SmartHomeUser','SmartHomeUser' ";
SqlCommand cmd2 = new SqlCommand(ExecuteQuery, Connection);
cmd2.Connection.Open();
cmd2.ExecuteNonQuery();
Connection.Close();
ExecuteQuery = "USE SmartHome " +
"EXEC sp_addrolemember 'db_owner', 'SmartHomeUser' ";
SqlCommand cmd3 = new SqlCommand(ExecuteQuery, Connection);
cmd3.Connection.Open();
cmd3.ExecuteNonQuery();
Connection.Close();
//增加安全性登录账户SmartHomeUser
ExecuteQuery="USE [SmartHome] if not exists (select * from master.dbo.syslogins where loginname = N'SmartHomeUser')"+
"BEGIN"+
"exec sp_addlogin 'SmartHomeUser' ,'123', 'SmartHome'"+
"END"+
"exec sp_grantdbaccess 'SmartHomeUser'"+
"exec sp_addrolemember 'db_owner', 'SmartHomeUser'";
SqlCommand cmd4 = new SqlCommand(ExecuteQuery, Connection);
cmd4.Connection.Open();
cmd4.ExecuteNonQuery();
Connection.Close();
}
catch (SqlException ex)
{
Console.Write(ex.Message.ToString());
}
finally
{
//关闭数据库连接
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
}
}
public override void Uninstall(IDictionary savedState)
{
base.Uninstall(savedState);
//创建数据库的连接字符串
string DbConnection =
"Server=" + Context.Parameters["databaseServer"] + ";" +
"Trusted_Connection=true;" +
"Uid=" + Context.Parameters["userName"] + ";" + //用户名
"Pwd=" + Context.Parameters["userPass"]; //密码
//根据连接字符串创建 SqlConnetion 连接句柄
SqlConnection Connection = new SqlConnection("Data Source=localhost;Initial Catalog=master;Integrated Security=True");
string ExecuteQuery = "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'SmartHome')"+" DROP DATABASE SmartHome";
SqlCommand command = new SqlCommand(ExecuteQuery, Connection);
command.Connection.Open();
command.ExecuteNonQuery();
Connection.Close();
}
}
}
网上有说，是customerAction路径问题,特在安装程序里面加MessageBox,来跟踪，发现木有问题。
幸好有网友说，资源要内嵌，特查找msnd，
将数据库备份文件更改下（右键数据库备份文件）
重新编译，安装OK
