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

This article mainly introduces the relevant knowledge of "table names and field instance analysis in SQL Server database". The editor shows you the operation process through actual cases, and the operation method is simple, fast and practical. I hope this article "table names and field instance analysis in SQL Server database" can help you solve the problem.

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.

Thinking that another project will be updated next month, I have changed more than N things, so how to update the database 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;}} about "Table names and field instance analysis in SQL Server database", thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.