从C＃映射所有数据库表的结构/模式 [英] Map structures/schema of all database tables from C#

问题描述

我有以下查询，我从前端传递给ex： -

 创建  TABLE  [  + TableName +]   
 +（  
 + [Fname] [ varchar ]（ 100 ） NOT   NULL ，   
 + [Lname] [ varchar ]（ 300 ） NOT   NULL ，  
 + [代码] [ varchar ]（ 6 ） NULL ，  
 + [ TYPE ] [ varchar ]（ 4 ） NULL ，  
 + [AMT] [  float ]  NULL    
 +） ON  [ PRIMARY ] < span class =code-string> ;  

我想做的是如果表中不存在数据库我想创建它。但如果它存在则我想将该表结构与查询结构进行比较，如果存在一些差异，我想更新数据库表。但是不要丢失数据库表的数据。我想对特定数据库的所有表执行此操作。

从哪里开始的任何建议？如何实现这个任务？有什么想法吗？

帮帮我..

解决方案

你好，



你可以看看 ActiveRecord模式实现 [ ^ ]在本文中描述。



问候，

你没有说数据库是什么，所以我认为它是SQL Server。如果是这种情况，那么各种 information_schema 视图就是你的朋友;如果您使用的是古老版本，那么您可以使用 sysobjects 。他们会告诉你几乎所有关于数据库的信息。



如果你使用的是另一个数据库，那么可能会有类似的功能。

  string  connectionPath =  @  Database = xxx; Server = yyy; User ID = zzz; Password = password; Network Library = dbmssocn; Connect Timeout = 10;; 
 
  //  获取表格的列。 
  string  getTableDef =  @  select * from information_schema.columns其中table_name =' myTable的'; 
 
  / *   
获取以下信息。加上其他一些可能的结果。 
 COLUMN_NAME，IS_NULLABLE，DATA_TYPE，
 CHARACTER_MAXIMUM_LENGTH，CHARACTER_OCTET_LENGTH，
 NUMERIC_PRECISION，NUMERIC_PRECISION_RADIX，
 NUMERIC_SCALE，DATETIME_PRECISION 
 * /  
 
 DataSet ds =  new  DataSet（）; 
 使用（SqlConnection connection =  new  SqlConnection（connectionPath））
 {
 SqlDataAdapter da =  new  SqlDataAdapter（getTableDef，connection）; 
 da.Fill（ds）; 
 ds.WriteXml（xmlFilePath，XmlWriteMode.WriteSchema）; 
 
 DataView dv =  new  DataView（ds.Tables [ 0 ]）; 
 
  if （dv.count ==  0 ）{
  //  创建表格 
} 
  else  {
  //  将数据视图中保存的每列的定义与您的比较 
  //  表的参考定义和使用： 
  //   ALTER TABLE {my table name} ALTER COLUMN {my column name}或 
 < span class =code-comment> //   ALTER TABLE {my table name} ADD {my new column}或 
  //   ALTER TABLE {我的表名} DROP COLUMN {要丢失的列}如有必要。 
 } 
 
} 
 

I have the following query which i am passing from front end for ex:-

CREATE TABLE [" + TableName + "]"
                                 + "("
                                 + "[Fname] [varchar](100) NOT NULL,"
                                 + "[Lname] [varchar](300) NOT NULL,"
                                 + "[CODE] [varchar](6) NULL,"
                                 + "[TYPE] [varchar](4) NULL,"
                                 + "[AMT] [float] NULL"
                                 + ") ON [PRIMARY]";

what i want to do is if the table doesnt exists in database i want to create it.but if it exists then i want to compare that tables structure with the query structure and if there is some difference i want to update the DB table.But without loosing the data of DB table.And i want to do it for all the tables of a particular database.
Any suggestions from where to start?How to achieve this task?Any Ideas?
Help Me..

解决方案

Hello,

You can have a look at ActiveRecord Pattern implementations[^] described in this article.

Regards,

You don't say what the DB is so I've assumed that it's SQL Server. If that is the case then the various information_schema views are your friend; that or you can use sysobjects if you're using an ancient version. They'll tell you pretty much everything you might want to know about a DB.

If you're using a.n.other DB then there's likely to be a similar feature.

string connectionPath = @"Database=xxx;Server=yyy;User ID=zzz;Password=password;Network Library=dbmssocn;Connect Timeout=10;";

// Get the table's columns.
string getTableDef  = @"select * from information_schema.columns where table_name = 'myTable'";

/*
 Gets the following info. plus some other odds and ends.
 COLUMN_NAME, IS_NULLABLE, DATA_TYPE,
 CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH,
 NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX,
 NUMERIC_SCALE, DATETIME_PRECISION
*/

DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionPath))
{
  SqlDataAdapter da = new SqlDataAdapter(getTableDef , connection);
  da.Fill(ds);
  ds.WriteXml(xmlFilePath, XmlWriteMode.WriteSchema);

  DataView dv = new DataView(ds.Tables[0]);

  if (dv.count == 0) {
   // Create the table
  }
  else {
   // Compare the definition of each column held in the dataview with your
   // reference definition for the table and use:
   //         ALTER TABLE {my table name} ALTER COLUMN {my column name} or
   //         ALTER TABLE {my table name} ADD {my new column} or
   //         ALTER TABLE {my table name} DROP COLUMN {column to lose} as/if necessary.
  }

}

这篇关于从C＃映射所有数据库表的结构/模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！