Hi,
Recently i have been creating a Generator. That extracts all the names of the tables from the database tables and generates the class file for each of the tables in the database. I have successfully extracted the names of database tables and their corresponding column names with thier data type and length of the datatype. Now i want to place columns as variable names in class file. That can be done but what about the datatype of the variable in class file.

I found an article on MSDN website that matches SQL Server Data Types and Their .NET Framework Equivalents at the following location http://msdn.microsoft.com/en-us/library/ms131092%28SQL.90%29.aspx.

I want to know, is it ok to replace the SQL Server Data Type with their .NET Framework Equivalents comfortably.

Is there any better method to that.


please help


Thanks,
randhir

That is what you have to do. You work with varchar()s in C# as a string. I do the same thing and what I would suggest is using attributes on the fields that mirrors your database setup so you can do client-side validation before submitting queries.

[
  DBTableInfo("vea_Job"),
  DBChildTableInfo("vea_JobRecip"),
  DBChildTableInfo("vea_JobReport")
  ]
  public sealed class Job : SqlObject
  {
    [DBFieldInfo("JobId", SqlDbType.Int, PrimaryKey=true, Identity=true)]
    private int _jobId = 0;
    [DBFieldInfo("JobName", SqlDbType.VarChar, Required = true, Unique = true, Size = DataLength.JobName)]
    private string _jobName;
    [DBFieldInfo("Subject", SqlDbType.VarChar, Size = DataLength.Subject, Required=true)]
    private string _subject;
    [DBFieldInfo("Body", SqlDbType.VarChar, Required=true)]
    private string _body;
    [DBFieldInfo("ImageFormat", SqlDbType.VarChar, Size = DataLength.ImageFormat, Required=true)]
    private ChartExportFormat _imageFormat = ChartExportFormat.PDF;
    [DBFieldInfo("ResolutionX", SqlDbType.Int)]
    private int _resolutionX = 800;
    [DBFieldInfo("ResolutionY", SqlDbType.Int)]
    private int _resolutionY = 600;
    [DBFieldInfo("ImageOption", SqlDbType.VarChar, Size = DataLength.ImageOption)]
    private string _imageOption = Constants.ImageFormat_SingleFileMultPage;
    [DBFieldInfo("FileName", SqlDbType.VarChar)]
    private string _fileName;
    /* -------------------------------------------------------------------- */
    public int JobId
    {
      get { return _jobId; }
    }
    public string JobName
    {
      get { return _jobName; }
      set { _jobName = value; }
    }

The attributes I use:

[AttributeUsage(AttributeTargets.Class, AllowMultiple = false)]
  internal sealed class DBTableInfo : Attribute
  {
    private string _tableName;
    /// <summary>
    /// Marks the SQL table a business object serializes with
    /// </summary>
    /// <param name="TableName">SQL Table Name</param>
    public DBTableInfo(string TableName)
    {
      _tableName = TableName;
    }
    /// <summary>
    /// SQL Table Name
    /// </summary>
    public string TableName
    {
      get { return _tableName; }
    }
  }

  [AttributeUsage(AttributeTargets.Class, AllowMultiple = true)]
  internal sealed class DBChildTableInfo : Attribute
  {
    //Required for AllowMultiple=True
    public override object TypeId { get { return this; } }
    private string _tableName;
    private string _columnName;
    /// <summary>
    /// Marks the SQL table a business object serializes with
    /// </summary>
    /// <param name="TableName">SQL Table Name</param>
    public DBChildTableInfo(string TableName)
    {
      _tableName = TableName;
      _columnName = string.Empty;
    }
    /// <summary>
    /// Marks the SQL table a business object serializes with
    /// </summary>
    /// <param name="TableName">SQL Table Name</param>
    public DBChildTableInfo(string TableName, string ColumnName)
      : this(TableName)
    {
      _columnName = ColumnName;
    }
    /// <summary>
    /// SQL Table Name
    /// </summary>
    public string TableName
    {
      get { return _tableName; }
    }
    public string ColumnName
    {
      get { return _columnName; }
    }
  }
  [AttributeUsage(AttributeTargets.Field, AllowMultiple = false)]
  internal sealed class DBFieldInfo : Attribute
  {
    private string _fieldName;
    private bool _primaryKey;
    private SqlDbType _dataType;
    private int _size;
    private bool _required = false;
    private bool _unique = false;
    private bool _identity = false;
    public DBFieldInfo(string FieldName, SqlDbType DataType)
    {
      _fieldName = FieldName;
      _dataType = DataType;
      _primaryKey = false;
      _size = 0;
      _required = false;
      _identity = false;
    }
    public string FieldName
    {
      get { return _fieldName; }
    }
    public bool PrimaryKey
    {
      get { return _primaryKey; }
      set { _primaryKey = value; }
    }
    public SqlDbType DataType
    {
      get { return _dataType; }
    }
    public int Size
    {
      get { return _size; }
      set { _size = value; }
    }
    public bool Required
    {
      get { return (_required || _unique); }
      set { _required = value; }
    }
    public bool Unique
    {
      get { return _unique; }
      set { _unique = value; }
    }
    public bool Identity
    {
      get { return _identity; }
      set { _identity = value; }
    }
  }
Comments
Informative post.

Thanks,

I got a whole lot of information from you.
I really appreciate the time and effort you invested to reply.

Thanks Again

There is an option to "Add to sknake's Reputation" if this post was very helpful ;)

You're welcome and good luck!

Comments
His post was very helpful and to the point. I got what is was looking for. His reply was complete with all the information. He seems to be very helpful.
This question has already been answered. Start a new discussion instead.