public List <string> GetDatabaseList()
{
List <string> list = new List <string>();
// Open connection to the database
string conString = GetConfigPath.connectionString();
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con))
{
//List<string> tables = new List<string>();
DataTable dt = con.GetSchema("Tables");
foreach (DataRow row in dt.Rows)
{
string tablename = (string)row[1] + "." + (string)row[2];
list.Add(tablename);
}
}
}
return(list);
}
public void GetDatabaseListwithsizeinMB()
{
// Open connection to the database
string conString = GetConfigPath.connectionString();
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand(@"with fs
as
(
select database_id, type, size* 8.0 / 1024 size
from sys.master_files
)
select
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
", con))
{
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Console.WriteLine(dr[0] + " " + dr[1] + " " + dr[2]);
}
}
}
}
}
private void GetTableData(string tableName, IEnumerable <string> col, model datamodel)
{
var result = new List <string>();
string column = "";
string data = "";
string conString = GetConfigPath.connectionString();
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand("SELECT top 1 * from " + tableName, con))
{
using (IDataReader dr = cmd.ExecuteReader())
{
for (int i = 0; i < col.Count(); i++)
{
column = column + "," + Convert.ToString(col.ElementAt(i));
}
while (dr.Read())
{
for (int i = 0; i < col.Count(); i++)
{
// column = ","+column + Convert.ToString(col.ElementAt(i));
data = data + "," + Convert.ToString(dr[i]);
}
}
}
}
}
datamodel.Columnname = column;
datamodel.Data = data;
}
public void AppendCSVFile(model data)
{
File.AppendAllText(GetConfigPath.sharedOutputFilePath(), data.tablename);
File.AppendAllText(GetConfigPath.sharedOutputFilePath(), Environment.NewLine);
File.AppendAllText(GetConfigPath.sharedOutputFilePath(), data.Columnname);
File.AppendAllText(GetConfigPath.sharedOutputFilePath(), Environment.NewLine);
File.AppendAllText(GetConfigPath.sharedOutputFilePath(), data.Data);
File.AppendAllText(GetConfigPath.sharedOutputFilePath(), Environment.NewLine);
File.AppendAllText(GetConfigPath.sharedOutputFilePath(), Environment.NewLine);
}
public model Getdata(string tablename)
{
model datamodel = new model();
datamodel.tablename = tablename;
IEnumerable <string> col = GetColumnNames(GetConfigPath.connectionString(), tablename);
GetTableData(tablename, col, datamodel);
return(datamodel);
}
public List <string> GetDatabaseList()
{
List <string> list = new List <string>();
// Open connection to the database
string conString = GetConfigPath.connectionString();
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con))
{
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
}
return(list);
}
public void GetallthetablenameswithrowcountNumber()
{
// Open connection to the database
string conString = GetConfigPath.connectionString();
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand(@"DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
+ 'SELECT '
+ '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+ '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
, COUNT(*) AS [RowCount] FROM '
+ QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+ '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString", con))
{
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Console.WriteLine(dr[0] + " " + dr[1]);
}
}
}
}
}