private void bwExecStoredProc_DoWork(object sender, DoWorkEventArgs e)
{
MainForm.StoredProcRequirement storedProcReq = (MainForm.StoredProcRequirement)e.Argument;
MSSQLBase.SQLBase s = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
e.Result = s.ExecuteQuery(string.Format("[{0}].[dbo].[{1}]", storedProcReq.DBName, storedProcReq.StoredProcName), MSSQLBase.SQLBase.QueryType.StoredProc, storedProcReq.ParameterByValue);
if (bwExecStoredProc.CancellationPending)
{
e.Cancel = true;
}
}
public static List <TableDetail> GetTables(string dbName)
{
string query = string.Format(@"SELECT TABLE_NAME AS 'Table'
FROM {0}.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME", dbName);
MSSQLBase.SQLBase b = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
DataTable dt = b.ExecuteQuery(query);
return((from DataRow row in dt.Rows
select new TableDetail
{
TableName = row["Table"].ToString()
}).ToList());
}
public static List <StoredProcDetail> GetStoredProcs(string dbName)
{
string query = string.Format(@"SELECT SPECIFIC_NAME AS 'StoredProcedure'
FROM {0}.information_schema.routines
WHERE routine_type = 'PROCEDURE'
ORDER BY SPECIFIC_NAME", dbName);
MSSQLBase.SQLBase b = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
DataTable dt = b.ExecuteQuery(query);
List <StoredProcDetail> storedProcs = (from DataRow row in dt.Rows
select new StoredProcDetail
{
StoredProcName = row["StoredProcedure"].ToString()
}).ToList();
return(storedProcs);
}
public static List <StoredProcDetail.ParameterDetail> GetParameters(string dbName, string storedProcName)
{
string query = string.Format(@"SELECT PARAMETER_NAME as 'Parameter'
,DATA_TYPE as 'DataType'
FROM {0}.information_schema.parameters
WHERE specific_name = '{1}'", dbName, storedProcName);
MSSQLBase.SQLBase b = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
DataTable dt = b.ExecuteQuery(query);
List <ParameterDetail> parameters = (from DataRow row in dt.Rows
select new StoredProcDetail.ParameterDetail
{
ParameterName = row["Parameter"].ToString(),
DataType = row["DataType"].ToString()
}).ToList();
return(parameters);
}
private void bgTableQuery_DoWork(object sender, DoWorkEventArgs e)
{
string query = e.Argument.ToString();
using (SqlConnection con = new SqlConnection(DBConnection.DbCon.Connection))
{
SqlCommand com = new SqlCommand(query, con);
}
MSSQLBase.SQLBase b = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
e.Result = b.ExecuteQuery(query);
if (bgTableQuery.CancellationPending)
{
e.Cancel = true;
}
}
public static List <ColumnDetail> GetColumns(string dbName, string tableName)
{
string query = string.Format(@"use {0};
SELECT col.COLUMN_NAME AS 'ColumnName'
,col.IS_NULLABLE AS 'IsNullable'
,col.DATA_TYPE AS 'DataType'
,ISNULL(CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR), 'Not Applicable') AS 'MaxLength'
,CASE COLUMNPROPERTY(OBJECT_ID(col.TABLE_NAME), col.COLUMN_NAME, 'IsIdentity')
WHEN 1
THEN CAST(1 AS BIT)
WHEN 0
THEN CAST(0 AS BIT)
ELSE 'W'
END AS 'IsIdentity'
,CASE
WHEN colUsage.COLUMN_NAME IS NULL
THEN CAST(0 AS BIT)
ELSE CAST(1 AS BIT)
END AS 'IsPrimaryKey'
FROM {0}.INFORMATION_SCHEMA.COLUMNS col
LEFT JOIN {0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE colUsage ON OBJECTPROPERTY(OBJECT_ID(colUsage.constraint_name), 'IsPrimaryKey') = 1
AND colUsage.COLUMN_NAME = col.COLUMN_NAME
AND colUsage.TABLE_NAME = col.TABLE_NAME
WHERE col.TABLE_NAME = '{1}'", dbName, tableName);
MSSQLBase.SQLBase b = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
DataTable dt = b.ExecuteQuery(query);
return((from DataRow row in dt.Rows
select new ColumnDetail
{
ColumnName = row["ColumnName"].ToString(),
IsPrimaryKey = Convert.ToBoolean(row["IsPrimaryKey"]),
DataType = row["DataType"].ToString(),
IsIdentity = Convert.ToBoolean(row["IsIdentity"]),
ColumnValue = ""
}).ToList());
}