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 }