public DataTable ExecTableObjectInfo(TableObject type, string table_name, string table_owner)
{
MSSqlService sql = new MSSqlService();
sql.AddParameter("@table_name", SqlDbType.VarChar, table_name);
sql.AddParameter("@table_owner", SqlDbType.VarChar, table_owner);
return(sql.ExecuteSPDataSet("sp_" + type.ToString()).Tables[0]);
}
public DataTable ExecSPObjectInfo(StoredProcedureObject type, string procedure_name, string procedure_owner)
{
MSSqlService sql = new MSSqlService();
sql.AddParameter("@procedure_name", SqlDbType.VarChar, procedure_name);
sql.AddParameter("@procedure_owner", SqlDbType.VarChar, procedure_owner);
return(sql.ExecuteSPDataSet("sp_" + type.ToString()).Tables[0]);
}
public void BindDefault(string db_name, string table_name, string column_name, string default_value)
{
MSSqlService sql = new MSSqlService();
sql.AddParameter("@defname", SqlDbType.VarChar, default_value);
sql.AddParameter("@objname", SqlDbType.VarChar, db_name + "." + table_name + "." + column_name);
sql.ExecuteSP(sp_bindefault);
}
public void SetStoredProcedureExec(string sp_name, bool exec)
{
MSSqlService sql = new MSSqlService();
sql.AddParameter("@ProcName", SqlDbType.VarChar, sp_name);
sql.AddParameter("@OptionName", SqlDbType.VarChar, "start_up");
sql.AddParameter("@OptionValue", SqlDbType.VarChar, exec?"on":"off");
sql.ExecuteSP(sp_procoption);
}
public bool UpdateExtendedProperty(DBObjectExtendedProperty type, string value, string object_name, string column_name = "", string property_name = "MS_Description")
{
MSSqlService sql = new MSSqlService();
sql.AddParameter("@name", SqlDbType.VarChar, property_name);
sql.AddParameter("@value", SqlDbType.VarChar, value);
sql.AddParameter("@level0type", SqlDbType.VarChar, "user");
sql.AddParameter("@level0name", SqlDbType.VarChar, "dbo");
sql.AddParameter("@level1type", SqlDbType.VarChar, type.ToString());
sql.AddParameter("@level1name", SqlDbType.VarChar, object_name);
if (type == DBObjectExtendedProperty.Column)
{
sql.AddParameter("@level2type", SqlDbType.VarChar, "column");
sql.AddParameter("@level2name", SqlDbType.VarChar, column_name);
}
try
{
sql.ExecuteSP(sp_updateextendedproperty);
return(true);
}
catch
{
return(false);
}
}
public DataTable ExecObjectDependInfo(string obj_name)
{
MSSqlService sql = new MSSqlService();
sql.AddParameter("@objname", SqlDbType.VarChar, obj_name);
return(sql.ExecuteSPDataSet(sp_depends).Tables[0]);
}
public void DeleteBackupHistory(DateTime date)
{
MSSqlService sql = new MSSqlService();
sql.AddParameter("@oldest_date", SqlDbType.DateTime, date);
sql.ExecuteSP(sp_delete_backuphistory);
}
public bool InsertData(object obj, string table = "")
{
if (table == String.Empty)
{
table = this.GetTableName(obj);
}
PropertyInfo[] propertys = obj.GetType().GetProperties();
MSSqlService sql = new MSSqlService();
StringBuilder queryParameters = new StringBuilder();
foreach (PropertyInfo prop in propertys)
{
if (prop.CanWrite)//&&prop.Name.ToLower()!="id")
{
string data_type = prop.PropertyType.Name.ToLower();
if (data_type == "list`1")
{
continue;
}
switch (data_type)
{
case "string": sql.AddParameter("@" + prop.Name, SqlDbType.VarChar, prop.GetValue(obj, null)); queryParameters.Append(",@" + prop.Name); break;
case "int32": sql.AddParameter("@" + prop.Name, SqlDbType.Int, prop.GetValue(obj, null)); queryParameters.Append(",@" + prop.Name); break;
case "decimal": sql.AddParameter("@" + prop.Name, SqlDbType.Decimal, prop.GetValue(obj, null)); queryParameters.Append(",@" + prop.Name); break;
case "datetime": sql.AddParameter("@" + prop.Name, SqlDbType.DateTime, prop.GetValue(obj, null)); queryParameters.Append(",@" + prop.Name); break;
case "int64": sql.AddParameter("@" + prop.Name, SqlDbType.BigInt, prop.GetValue(obj, null)); queryParameters.Append(",@" + prop.Name); break;
case "boolean": sql.AddParameter("@" + prop.Name, SqlDbType.Bit, prop.GetValue(obj, null)); queryParameters.Append(",@" + prop.Name); break;
case "double": sql.AddParameter("@" + prop.Name, SqlDbType.Float, prop.GetValue(obj, null)); queryParameters.Append(",@" + prop.Name); break;
case "byte": sql.AddParameter("@" + prop.Name, SqlDbType.SmallInt, prop.GetValue(obj, null)); queryParameters.Append(",@" + prop.Name); break;
}
}
}
string query = String.Format("Insert Into [" + table + "] ({0}) Values ({1})", queryParameters.ToString().Replace("@", "").TrimStart(',').ToLower(), queryParameters.ToString().TrimStart(',').ToLower());
SqlDataReader reader = sql.ExecuteSqlReader(query);
int record = reader.RecordsAffected;
reader.Close();
return(record > 0);
}
public DataSet ExecObjectInfo(string obj_name = "")
{
MSSqlService sql = new MSSqlService();
if (obj_name != String.Empty)
{
sql.AddParameter("@objname", SqlDbType.VarChar, obj_name);
}
return(sql.ExecuteSPDataSet(sp_help));
}