Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "SQL Server database table names, field comparison example analysis", the content is easy to understand, clear, hope to help you solve your doubts, the following let Xiaobian lead you to study and learn "SQL Server database table names, field comparison example analysis" this article.

Preface

The project is generally divided into test environment (QAS) and production environment (PRD). When our project has experienced a long-span update, when we release to the production environment, the first task is to update the new tables and fields to the production database. A lot of times, when we release updates, it's hard to remember what changes have been made.

Of course, some people will say, 1.EF Code First has history records, this is a way, is it reliable? Unreliable. I believe that even with Code First, I am definitely not the only one who directly changes the database.

two。 This is also one way to view the entity class change record. What if you use DB First? Of course, you can see it, but it's troublesome.

3. During the development process, write down the changes to the database. I'm sure I'm not the only one who did this. Manual dog head

.

At noon, I was thinking that another project would be updated next month. If I changed more than N things, how would the database be updated then? Just want to write a tool to compare the differences between two versions of databases, table names, fields, and field types.

Say and do (originally thought that using EF,DBContext should be able to achieve, but not good at learning skills, and finally returned to ADO.Net).

Console applications, currently can only be compared to add, modify (SQl Server).

Using System;using System.Collections.Generic;using System.Data.SqlClient;using System.Linq;using System.Text;using Microsoft.EntityFrameworkCore;namespace EFGetTable {class Program {static void Main (string [] args) {string prdconnectionstring = "Data Source=localhost;initial catalog=ttPRD;user id=sa;password=password;MultipleActiveResultSets=True"; var prd = GetTableNames (prdconnectionstring); string qasconnectionstring = "Data Source=localhost;initial catalog=ttqas;user id=sa;password=password;MultipleActiveResultSets=True"; var qas = GetTableNames (qasconnectionstring); CompareTable (prd, qas) } public static List GetTableNames (string connectionstr) {var tableresult = new List (); string sqlTableName = "Select * From Information_Schema.Tables"; using (SqlConnection connection = new SqlConnection (connectionstr)) {using (SqlCommand cmd = new SqlCommand (sqlTableName, connection)) {try {connection.Open (); SqlDataReader dr = cmd.ExecuteReader () / / while (dr.Read ()) {/ / Table name TableInfo table = new TableInfo (); table.TableName = dr ["Table_Name"] .ToString (); table.columns.AddRange (GetColumnNamesByTable (dr ["Table_Name"]. ToString (), connection)); tableresult.Add (table);} connection.Close () } catch (System.Data.SqlClient.SqlException e) {Console.ForegroundColor = ConsoleColor.Red; Console.Error.WriteLine (e.Message); connection.Close ();} return tableresult;}} public static List GetColumnNamesByTable (string tableName, SqlConnection connection) {var Columnresults = new List (); string sqlcolum = $"Select * From Information_Schema.Columns t Where t.Table_Name =\'{tableName}\" Using (SqlCommand cmd = new SqlCommand (sqlcolum, connection)) {SqlDataReader dr = cmd.ExecuteReader (); / / while (dr.Read ()) {/ / Table name CloumnInfo column = new CloumnInfo (); column.CloumnName = dr ["Column_name"]. ToString (); column.DateType = dr ["DATA_TYPE"]. ToString () + dr ["CHARACTER_MAXIMUM_LENGTH"]. ToString (); Columnresults.Add (column);} return Columnresults }} public static void CompareTable (List prd, List qas) {foreach (var p in qas) {var tablequery = prd.AsQueryable (). Where (t = > t.TableName.Equals (p.TableName)); if (! tablequery.Any ()) {Console.WriteLine ($"New Created Table {p.TableName}"); continue;} else {var querytable = tablequery.FirstOrDefault () P.columns.ForEach (c = > {var Cloumnquery = querytable.columns.Select (cc = > cc.CloumnName) .Concluded (c.CloumnName); if (! Cloumnquery) {Console.WriteLine ($"New add cloumn: {c.CloumnName} on Table {p.TableName}");} else {var querycloumn = querytable.columns.Where (qt = > qt.CloumnName.Equals (c.CloumnName)). FirstOrDefault () If (! querycloumn.DateType.Equals (c.DateType)) {Console.WriteLine ($"DateType Different: cloumn: {c.CloumnName}, {querycloumn.DateType} = = > {c.DateType} on Table {p.TableName}");} public class TableInfo {public TableInfo () {columns = new List ();} public string TableName {get; set;} public List columns {get Set;}} public class CloumnInfo {public string CloumnName {get; set;} public string DateType {get; set;}

Test result

The above is all the contents of the article "sample Analysis of Table names and Field comparisons in SQL Server Database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.