Retrieving tablenames from database in string formatted text
I have a query like this
@"SELECT
TABLE_NAME
AS
TABLES
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = 'PRIMARY KEY'
开发者_C百科 AND
TABLE_NAME <> 'dtProperties'
ORDER BY
TABLE_NAME";
Is there any way to convert the tabular form to string formatted form when retrieving list of tables from this query.
Via .NET it is neither - that is just the SSMS display. Based on the previous questions, it sounds like you are just having difficulties reading this column into strings;
Perhaps just something like:
List<string> list = new List<string>();
using(var conn = new SqlConnection(connectionString))
using (var cmd = conn.CreateCommand()) {
cmd.CommandText = @"
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_NAME <> 'dtProperties'
ORDER BY TABLE_NAME";
conn.Open();
using (var reader = cmd.ExecuteReader()) {
while (reader.Read()) {
list.Add(reader.GetString(0));
}
}
}
(tested locally; works fine)
If you want something delimited, then perhaps:
string s = string.Join("|", list);
Or something involving a StringBuilder
(newlines in this example):
StringBuilder sb = new StringBuilder();
using(var conn = new SqlConnection(connectionString))
using (var cmd = conn.CreateCommand()) {
cmd.CommandText = @"
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_NAME <> 'dtProperties'
ORDER BY TABLE_NAME";
conn.Open();
using (var reader = cmd.ExecuteReader()) {
while (reader.Read()) {
sb.AppendLine(reader.GetString(0));
}
}
}
string s = sb.ToString();
Something like this helps?
string[] myObjArray = new string[DataTable1.Rows.Count];
DataTable1.Rows.CopyTo(myObjArray, 0);
I don't know what you mean with formatted form. You can retrieve a string with all table names with following sql statement:
declare @out varchar(max)
set @out = ''
select @out = @out + table_name + char(10) + char(13) from information_schema.table_constraints
where constraint_type = 'PRIMARY KEY'
and table_name <> 'dtProperties'
order by table_name
select @out
精彩评论