public string GetStoredText(string DataBaseName, string StoredName)
{
string getStoredText = "";
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database dbs = new SQLDMO.DatabaseClass();
dbs = (SQLDMO.Database)svr.Databases.Item(DataBaseName, "owner");
foreach (SQLDMO.StoredProcedure tb in dbs.StoredProcedures)
{
if (tb.Name == StoredName)
{
getStoredText = tb.Text;
}
}
}
catch (Exception err)
{
//throw (new Exception("！" + err.Message));
ShowError("修改存储过程失败!" + err.Message);
getStoredText = "";
}
finally
{
svr.DisConnect();
}
return(getStoredText);
}
public ArrayList GetStored(string DatabaseName)
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database mydb = new SQLDMO.DatabaseClass();
mydb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
foreach (SQLDMO.StoredProcedure db in mydb.StoredProcedures)
{
if (db.Name != null)
{
alDbs.Add(db.Name);
}
}
}
catch (Exception e)
{
//throw (new Exception("连接数据库出错：" + e.Message));
ShowError("连接数据库出错：" + e.Message);
return(null);
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return(alDbs);
}
public bool InsertTable(string DatabaseName, string TableName, DataTable dt)
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
string keyName = "";
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database myDb = new SQLDMO.DatabaseClass();
myDb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
SQLDMO.Table myTb = new SQLDMO.TableClass();
myTb.Name = TableName;
foreach (DataRow dr in dt.Rows)
{
SQLDMO.Column column = new SQLDMO.ColumnClass();
column.Name = dr["ColumnName"].ToString();
column.Datatype = dr["ColumnType"].ToString();
column.Length = int.Parse(dr["ColumnSize"].ToString());
if (bool.Parse(dr["ColumnKey"].ToString()))
{
keyName = column.Name;
}
column.AllowNulls = bool.Parse(dr["ColumnNull"].ToString());
myTb.Columns.Add(column);
}
if (keyName != "")
{
SQLDMO.Key key = new SQLDMO.KeyClass();
key.Name = keyName;
key.Type = SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
key.KeyColumns.Add(keyName);
myTb.Keys.Add(key);
}
myDb.Tables.Add(myTb);
return(true);
}
catch (Exception e)
{
//throw (new Exception("连接数据库出错：" + e.Message));
ShowError("添加数据库失败!" + e.Message);
return(false);
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
}
public bool CreateDB(string dbName, string path)
{
//SQLDMO.SQLServer.EnumDirectories(string path);
// 创建数据库文件
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
SQLDMO.DBFile dbFile = new SQLDMO.DBFileClass();
try
{
svr.Connect(ServerName, UserName, Password);
svr.EnumDirectories("c:");
dbFile.Name = dbName + "_Data";
dbFile.PhysicalName = Path.Combine(path, dbName + "_Data.MDF");
dbFile.PrimaryFile = true;
//dbFile.Size = 2; // 设置初始化大小(MB)
//dbFile.FileGrowthType = SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB; // 设置文件增长方式
//dbFile.FileGrowth=1; // 设置增长幅度
// 创建日志文件
SQLDMO._LogFile logFile = new SQLDMO.LogFileClass();
logFile.Name = dbName + "_Log";
logFile.PhysicalName = Path.Combine(path, dbName + "_Log.MDF");
//logFile.Size = 3;
//logFile.FileGrowthType=SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
//logFile.FileGrowth=1;
// 创建数据库
SQLDMO.Database db = new SQLDMO.DatabaseClass();
db.Name = dbName;
db.FileGroups.Item("PRIMARY").DBFiles.Add(dbFile);
db.TransactionLog.LogFiles.Add(logFile);
// 建立数据库联接，并添加数据库到服务器
svr.Databases.Add(db);
return(true);
}
catch (Exception err)
{
//throw (new Exception("！" + err.Message));
ShowError("添加数据库失败!" + err.Message);
return(false);
}
finally
{
svr.DisConnect();
}
}
public bool UpdateStored(string DataBaseName, string StoredName, string StoredText)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
if (StoredName == "")
{
SQLDMO.StoredProcedure spd = new SQLDMO.StoredProcedureClass();
spd.Text = StoredText;
svr.Databases.Item(DataBaseName, "owner").StoredProcedures.Add(spd);
}
else
{
SQLDMO.Database dbs = new SQLDMO.DatabaseClass();
SQLDMO.StoredProcedure spd = new SQLDMO.StoredProcedureClass();
dbs = (SQLDMO.Database)svr.Databases.Item(DataBaseName, "owner");
foreach (SQLDMO.StoredProcedure sp in dbs.StoredProcedures)
{
if (sp.Name == StoredName)
{
spd = sp;
}
}
spd.Alter(StoredText);
}
return(true);
}
catch (Exception err)
{
//throw (new Exception("！" + err.Message));
ShowError("修改存储过程失败!" + err.Message);
return(false);
}
finally
{
svr.DisConnect();
}
}
public bool UpdateStored(string DataBaseName, string StoredName,string StoredText)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
if (StoredName == "")
{
SQLDMO.StoredProcedure spd = new SQLDMO.StoredProcedureClass();
spd.Text = StoredText;
svr.Databases.Item(DataBaseName, "owner").StoredProcedures.Add(spd);
}
else
{
SQLDMO.Database dbs = new SQLDMO.DatabaseClass();
SQLDMO.StoredProcedure spd = new SQLDMO.StoredProcedureClass();
dbs = (SQLDMO.Database)svr.Databases.Item(DataBaseName, "owner");
foreach (SQLDMO.StoredProcedure sp in dbs.StoredProcedures)
{
if (sp.Name == StoredName)
spd = sp;
}
spd.Alter(StoredText);
}
return true;
}
catch (Exception err)
{
//throw (new Exception("��" + err.Message));
ShowError("�Ĵ洢����ʧ��!" + err.Message);
return false;
}
finally
{
svr.DisConnect();
}
}
public bool InsertTable(string DatabaseName, string TableName,DataTable dt)
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
string keyName = "";
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database myDb = new SQLDMO.DatabaseClass();
myDb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
SQLDMO.Table myTb = new SQLDMO.TableClass();
myTb.Name = TableName;
foreach (DataRow dr in dt.Rows)
{
SQLDMO.Column column = new SQLDMO.ColumnClass();
column.Name = dr["ColumnName"].ToString();
column.Datatype = dr["ColumnType"].ToString();
column.Length = int.Parse(dr["ColumnSize"].ToString());
if (bool.Parse(dr["ColumnKey"].ToString()))
{
keyName = column.Name;
}
column.AllowNulls = bool.Parse(dr["ColumnNull"].ToString());
myTb.Columns.Add(column);
}
if (keyName != "")
{
SQLDMO.Key key = new SQLDMO.KeyClass();
key.Name = keyName;
key.Type = SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
key.KeyColumns.Add(keyName);
myTb.Keys.Add(key);
}
myDb.Tables.Add(myTb);
return true;
}
catch (Exception e)
{
//throw (new Exception("�������ݿ�����" + e.Message));
ShowError("������ݿ�ʧ��!" + e.Message);
return false;
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
}
public ArrayList GetTables(string DatabaseName)
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database mydb = new SQLDMO.DatabaseClass();
mydb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
foreach (SQLDMO.Table db in mydb.Tables)
{
if (db.Name != null)
alDbs.Add(db.Name);
}
}
catch (Exception e)
{
//throw (new Exception("�������ݿ�����" + e.Message));
ShowError("�������ݿ�����" + e.Message);
return null;
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return alDbs;
}
public string GetStoredText(string DataBaseName, string StoredName)
{
string getStoredText = "";
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database dbs = new SQLDMO.DatabaseClass();
dbs = (SQLDMO.Database)svr.Databases.Item(DataBaseName, "owner");
foreach (SQLDMO.StoredProcedure tb in dbs.StoredProcedures)
{
if (tb.Name == StoredName)
getStoredText= tb.Text;
}
}
catch (Exception err)
{
//throw (new Exception("��" + err.Message));
ShowError("�Ĵ洢����ʧ��!" + err.Message);
getStoredText= "";
}
finally
{
svr.DisConnect();
}
return getStoredText;
}
public bool CreateDB(string dbName, string path)
{
//SQLDMO.SQLServer.EnumDirectories(string path);
// �������ݿ��ļ�
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
SQLDMO.DBFile dbFile = new SQLDMO.DBFileClass();
try
{
svr.Connect(ServerName, UserName, Password);
svr.EnumDirectories("c:");
dbFile.Name = dbName + "_Data";
dbFile.PhysicalName = Path.Combine(path, dbName + "_Data.MDF");
dbFile.PrimaryFile = true;
//dbFile.Size = 2; // ���ó�ʼ����С(MB)
//dbFile.FileGrowthType = SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB; // �����ļ�������ʽ
//dbFile.FileGrowth=1; // ������������
// ������־�ļ�
SQLDMO._LogFile logFile = new SQLDMO.LogFileClass();
logFile.Name = dbName + "_Log";
logFile.PhysicalName = Path.Combine(path, dbName + "_Log.MDF");
//logFile.Size = 3;
//logFile.FileGrowthType=SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
//logFile.FileGrowth=1;
// �������ݿ�
SQLDMO.Database db = new SQLDMO.DatabaseClass();
db.Name = dbName;
db.FileGroups.Item("PRIMARY").DBFiles.Add(dbFile);
db.TransactionLog.LogFiles.Add(logFile);
// �������ݿ����ӣ���������ݿ������
svr.Databases.Add(db);
return true;
}
catch (Exception err)
{
//throw (new Exception("��" + err.Message));
ShowError("������ݿ�ʧ��!" + err.Message);
return false;
}
finally
{
svr.DisConnect();
}
}
/// <summary>
/// 执行SQL脚本文件，用到SQLDMO.DLL
/// 该组件需要注册
/// </summary>
/// <param name="dbServerName"></param>
/// <param name="dbUser"></param>
/// <param name="dbPwd"></param>
/// <param name="dbName"></param>
/// <param name="sqlFile"></param>
/// <param name="cmdEncod">发送的编码格式</param>
public static void ExecSqlFileByDMO(string dbServerName, string dbUser, string dbPwd, string dbName, string sqlFile,Encoding cmdEncod)
{
SQLDMO.SQLServer2 sqlserver = new SQLDMO.SQLServer2Class();
sqlserver.Connect(dbServerName , dbUser , dbPwd);
SQLDMO.Databases mydbs = sqlserver.Databases;
SQLDMO.Database mydb = new SQLDMO.DatabaseClass();
mydb = (SQLDMO.Database)mydbs.Item(dbName, dbUser );
System.IO.StreamReader s = null;
string sql = string.Empty;
try
{
s = new System.IO.StreamReader(sqlFile, cmdEncod);
sql = s.ReadToEnd();
mydb.ExecuteImmediate(sql, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, sql.Length);
}
finally
{
s.Close();
s.Dispose();
s = null;
sqlserver.DisConnect();
}//try
}
