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]); } } } } }