sql server to .NET Framework data type mapping.

[nccsbim071]

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

[sknake]

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; }
    }
  }

[nccsbim071]

Thanks,

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

Thanks Again

[sknake]

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

You're welcome and good luck!