TOC
Having worked with the SQLDMO, I found some limitations, so I decided to make my own SQLDMO called SQLReader. It is a read-only DLL that takes SQL meta data and puts it into classes that one can work with. The SQLReader can not assign anything in the SQL Server.
This demo consists of two projects:
SQL2ClassDemo is the actual code generator, and the SQLReader is the code generator information supplier.
I work a lot with database rich applications, and I needed an app that could make class shells from a database table.
To load databases from a SQL Server database is a straightforward process. Here, I will show how to use the the SQLReader, and afterwards, I will show how to use the information to make classes from tables.
Here is how to load the server databases:
SQLServer SqlSrv = new SQLServer();
SqlSrv.ConnectionSetting.DataSource = Environment.MachineName;
SqlSrv.ConnectionSetting.IntegratedSecurity = false;
SqlSrv.ConnectionSetting.UserID = "sa";
SqlSrv.ConnectionSetting.Password = "P@ssw0rd";
SqlSrv.LoadDatabases();
Here is how we load just one database:
SqlSrv.LoadDatabases("AdventureWorks");
This is how we loop through the objects in the SQL Server database:
foreach (Database db in SqlSrv.Databases.Items)
{
foreach (Table t in db.Tables.Items)
{
foreach (Column c in t.Columns.Items)
{
}
foreach (Index i in t.Indexes.Items)
{
}
foreach (foreign_key fk in t.ForeignKeys.Items)
{
}
foreach (KeyConstraint kc in t.Keys.Items)
{
}
}
}
A full documentation of the SQLReader DLL is available in the ZIP file. Please look into it. Or look here.
- Type the name or the IP of the server you want to connect to.
- Choose if you want to connect with Integrated Security or not.
- If you do not use the Integrated Security, please type the username and password.
- If you want to load only one database, then type the name of the database and check the box.
- Then, click Connect.
This section shows you how to list the SQLReader properties.
In this section, you can select or deselect the databases / tables you want to make an output file (source code file) from. You will also see the property of the SQLReader
class of the selected item in the tree.
This section allows you to set the output directory, where the source code files will appear when done creating them.
In this section, you can set some basic settings that the code generator will perform when creating the source code files. Set what kind of languages you want the source code to be in, in the language group box. Here, you can also set pre names and post names for the fields, if you desire that.
In the Class Setting group, some other values can be set. For example, what kind of modifiers there shall be on the fields and properties.
- Add Comments, will add information to the field about the SQL object, e.g., what kind of data types is in the database and so on.
- Map MS_Description, will add the
Description
attribute to the property with the same text as the description in the SQL Server Management Studio. - Try to correct propertyname, will try to correct the name of the column from the SQL table. Example: orderId = OrderId or Order id = Orderid.
- Try to correct property displayname, will try to correct the column name from the SQL table. Example: UserId = User Id or OrderMadeByUser = Order Made By User.
- Create Reference Object Property, will take any foreign keys related to the table, and create properties that assign the corresponding class types of the related table in the foreign key.
- User Databasename as Namespace, as it says, uses the database name as the namespace name.
- Add Schema to the namespace, can be helpful if you use multiple schemas within one database, and have the same table names under each schema.
- Class namespace imports, here you can select what kind of namespaces you want to import into the class.
- Class BaseTypes, add or remove some base type objects to the class.
In this section, just click Create, and you are on your way.
You can just double click on the file to open it. Or navigate to the output folder, which should look like this if you have selected the AdventureWorks database:
Using CodeDom to create source code has its limitations. Here's a short list of some of the limits one may encounter. CodeDom can not make:
while
loops (when trying to make this, CodeDom actually creates a for
loop which looks very nasty). using
statements foreach
statements - Static classes; instead it makes a sealed abstract class
- Variable plus-plus increment (
i++
); instead it makes (i = (i + 1)
) - Add comments to the same line as the code
- Child namespaces
But there are some ways to get around this. I am working on a CodeDom cleaner project which will correct this and make some more nice looking code. CodeDom version:
if(CountVarUp)
{
i = (i + 1);
}
for(int i = 0; i < 100; (i = (i + 1)))
{
}
A programmer's version:
if(CountVarUp)
i++;
for(int i = 0; i < 100; i++)
In this section, I added some basic information about this little application:
Class layout:
AdventureWorks.HumanResources
public void Select(string ConnectionString)
public int Insert(string ConnectionString)
public int Update(string ConnectionString)
public int Delete(string ConnectionString)
public string[] GetSqlCommandStrings()
private void AddFromRecordSet(SqlDataReader rs)
private SqlParameter[] GetSqlParameters()
internal static string _SQL_Select
internal static string _SQL_Insert
internal static string _SQL_Update
internal static string _SQL_Delete
public int EmployeeID
public string NationalIDNumber
public int ContactID
public string LoginID
public int ManagerID
public string Title
public DateTime BirthDate
public string MaritalStatus
public string Gender
public DateTime HireDate
public bool SalariedFlag
public short VacationHours
public short SickLeaveHours
public bool CurrentFlag
public Guid rowguid
public DateTime ModifiedDate
public Contact ContactID_Contact
public Employee ManagerID_Employee
public EmployeeAddressCollection EmployeeAddressCollection
public EmployeeDepartmentHistoryCollection EmployeeDepartmentHistoryCollection
public EmployeePayHistoryCollection EmployeePayHistoryCollection
public JobCandidateCollection JobCandidateCollection
public PurchaseOrderHeaderCollection PurchaseOrderHeaderCollection
public SalesPersonCollection SalesPersonCollection
[Description("Employee information such as salary, department, and title.")]
public class Employee : Object
{
#region Static SQL String Memebers
internal static string _SQL_Select = "@"SELECT [EmployeeID], [NationalIDNumber], " +
"[ContactID], [LoginID], [ManagerID], " +
"[Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], " +
"[SalariedFlag], [VacationHours], [SickLeaveHours], " +
"[CurrentFlag], [rowguid], [ModifiedDate] FROM " +
"[HumanResources].[Employee] WHERE [EmployeeID]=@EmployeeID ";
/// This field represents the full INSERT INTO string for the table
/// Employee.
internal static string _SQL_Insert = "@"INSERT INTO " +
"[HumanResources].[Employee] ([EmployeeID], [NationalIDNumber]," +
" [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], " +
"[MaritalStatus], [Gender], [HireDate], [SalariedFlag], " +
"[VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], " +
"[ModifiedDate]) VALUES([EmployeeID], [NationalIDNumber], " +
"[ContactID], [LoginID], [ManagerID], [Title], [BirthDate], " +
"[MaritalStatus], [Gender], [HireDate], [SalariedFlag], " +
"[VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) ";
internal static string _SQL_Update =
"@"UPDATE [HumanResources].[Employee] SET [EmployeeID] = @EmployeeID, " +
"[NationalIDNumber] = @NationalIDNumber, [ContactID] = " +
"@ContactID, [LoginID] = @LoginID, [ManagerID] = @ManagerID, " +
"[Title] = @Title, [BirthDate] = @BirthDate, " +
"[MaritalStatus] = @MaritalStatus, [Gender] = @Gender, [HireDate] = @HireDate," +
" [SalariedFlag] = @SalariedFlag, [VacationHours] = @VacationHours, " +
"[SickLeaveHours] = @SickLeaveHours, [CurrentFlag] = " +
"@CurrentFlag, [rowguid] = @rowguid, [ModifiedDate] = " +
"@ModifiedDate WHERE [EmployeeID]=@EmployeeID ";
/// This field represents the DELETE string for the table Employee,
/// with the WHERE clause.
internal static string _SQL_Delete =
"DELETE FROM [HumanResources].[Employee] WHERE [EmployeeID]=@EmployeeID ";
#endregion
#region Tables Memebers
/// SQL Type:int - Primary key for Employee records.
private int _EmployeeID;
[Description("Primary key for Employee records.")]
[DisplayName("Employee ID")]
[Category("Primary Key")]
public int EmployeeID
{
get
{
try
{
return _EmployeeID;
}
catch (System.Exception err)
{
throw new Exception("Error getting EmployeeID", err);
}
}
set
{
try
{
_EmployeeID = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting EmployeeID", err);
}
}
}
/// SQL Type:nvarchar - Unique national identification number such
/// as a social security number.
private string _NationalIDNumber;
[Description("Unique national identification number" +
" such as a social security number.")]
[DisplayName("National IDNumber")]
[Category("Column")]
public string NationalIDNumber
{
get
{
try
{
return _NationalIDNumber;
}
catch (System.Exception err)
{
throw new Exception("Error getting NationalIDNumber", err);
}
}
set
{
try
{
if ((value.Length <= 30))
{
_NationalIDNumber = value;
}
else
{
throw new OverflowException("Error setting" +
" NationalIDNumber, " +
"Length of value is to long. Maximum Length: 30");
}
}
catch (System.Exception err)
{
throw new Exception("Error setting NationalIDNumber", err);
}
}
}
/// SQL Type:int - Identifies the employee in the Contact table.
/// Foreign key to Contact.ContactID.
private int _ContactID;
[Description("Identifies the employee in the Contact table." +
" Foreign key to Contact.ContactID.")]
[DisplayName("Contact ID")]
[Category("Foreign Key")]
public int ContactID
{
get
{
try
{
return _ContactID;
}
catch (System.Exception err)
{
throw new Exception("Error getting ContactID", err);
}
}
set
{
try
{
_ContactID = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting ContactID", err);
}
}
}
/// SQL Type:nvarchar - Network login.
private string _LoginID;
[Description("Network login.")]
[DisplayName("Login ID")]
[Category("Column")]
public string LoginID
{
get
{
try
{
return _LoginID;
}
catch (System.Exception err)
{
throw new Exception("Error getting LoginID", err);
}
}
set
{
try
{
if ((value.Length <= 512))
{
_LoginID = value;
}
else
{
throw new OverflowException("Error " +
"setting LoginID, " +
"Length of value is to long. " +
"Maximum Length: 512");
}
}
catch (System.Exception err)
{
throw new Exception("Error setting LoginID", err);
}
}
}
/// SQL Type:int - Manager to whom the employee is assigned.
/// Foreign Key to Employee.M
private int _ManagerID;
[Description("Manager to whom the employee is " +
"assigned. Foreign Key to Employee.M")]
[DisplayName("Manager ID")]
[Category("Foreign Key")]
public int ManagerID
{
get
{
try
{
return _ManagerID;
}
catch (System.Exception err)
{
throw new Exception("Error getting ManagerID", err);
}
}
set
{
try
{
_ManagerID = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting ManagerID", err);
}
}
}
/// SQL Type:nvarchar - Work title such as Buyer or Sales
/// Representative.
private string _Title;
[Description("Work title such as Buyer or Sales Representative.")]
[DisplayName("Title")]
[Category("Column")]
public string Title
{
get
{
try
{
return _Title;
}
catch (System.Exception err)
{
throw new Exception("Error getting Title", err);
}
}
set
{
try
{
if ((value.Length <= 100))
{
_Title = value;
}
else
{
throw new OverflowException("Error " +
"setting Title, Length of value " +
"is to long. Maximum Length: 100");
}
}
catch (System.Exception err)
{
throw new Exception("Error setting Title", err);
}
}
}
/// SQL Type:datetime - Date of birth.
private System.DateTime _BirthDate;
[Description("Date of birth.")]
[DisplayName("Birth Date")]
[Category("Column")]
public System.DateTime BirthDate
{
get
{
try
{
return _BirthDate;
}
catch (System.Exception err)
{
throw new Exception("Error getting BirthDate", err);
}
}
set
{
try
{
_BirthDate = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting BirthDate", err);
}
}
}
/// SQL Type:nchar - M = Married, S = Single
private string _MaritalStatus;
[Description("M = Married, S = Single")]
[DisplayName("Marital Status")]
[Category("Column")]
public string MaritalStatus
{
get
{
try
{
return _MaritalStatus;
}
catch (System.Exception err)
{
throw new Exception("Error getting MaritalStatus", err);
}
}
set
{
try
{
if ((value.Length <= 2))
{
_MaritalStatus = value;
}
else
{
throw new OverflowException("Error " +
"setting MaritalStatus, " +
"Length of value is to long. Maximum Length: 2");
}
}
catch (System.Exception err)
{
throw new Exception("Error setting MaritalStatus", err);
}
}
}
/// SQL Type:nchar - M = Male, F = Female
private string _Gender;
[Description("M = Male, F = Female")]
[DisplayName("Gender")]
[Category("Column")]
public string Gender
{
get
{
try
{
return _Gender;
}
catch (System.Exception err)
{
throw new Exception("Error getting Gender", err);
}
}
set
{
try
{
if ((value.Length <= 2))
{
_Gender = value;
}
else
{
throw new OverflowException("Error setting Gender, " +
"Length of value is to long. Maximum Length: 2");
}
}
catch (System.Exception err)
{
throw new Exception("Error setting Gender", err);
}
}
}
/// SQL Type:datetime - Employee hired on this date.
private System.DateTime _HireDate;
[Description("Employee hired on this date.")]
[DisplayName("Hire Date")]
[Category("Column")]
public System.DateTime HireDate
{
get
{
try
{
return _HireDate;
}
catch (System.Exception err)
{
throw new Exception("Error getting HireDate", err);
}
}
set
{
try
{
_HireDate = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting HireDate", err);
}
}
}
/// SQL Type:Flag - Job classification. 0 = Hourly, not exempt from
/// collective bargaining. 1 = Salaried, exempt from collective bargaining.
private bool _SalariedFlag;
[Description("Job classification. 0 = Hourly, " +
"not exempt from collective bargaining." +
"1 = Salaried, exempt from collective bargaining.")]
[DisplayName("Salaried Flag")]
[Category("Column")]
public bool SalariedFlag
{
get
{
try
{
return _SalariedFlag;
}
catch (System.Exception err)
{
throw new Exception("Error getting SalariedFlag", err);
}
}
set
{
try
{
_SalariedFlag = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting SalariedFlag", err);
}
}
}
/// SQL Type:smallint - Number of available vacation hours.
private short _VacationHours;
[Description("Number of available vacation hours.")]
[DisplayName("Vacation Hours")]
[Category("Column")]
public short VacationHours
{
get
{
try
{
return _VacationHours;
}
catch (System.Exception err)
{
throw new Exception("Error getting VacationHours", err);
}
}
set
{
try
{
_VacationHours = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting VacationHours", err);
}
}
}
/// SQL Type:smallint - Number of available sick leave hours.
private short _SickLeaveHours;
[Description("Number of available sick leave hours.")]
[DisplayName("Sick Leave Hours")]
[Category("Column")]
public short SickLeaveHours
{
get
{
try
{
return _SickLeaveHours;
}
catch (System.Exception err)
{
throw new Exception("Error getting SickLeaveHours", err);
}
}
set
{
try
{
_SickLeaveHours = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting SickLeaveHours", err);
}
}
}
/// SQL Type:Flag - 0 = Inactive, 1 = Active
private bool _CurrentFlag;
[Description("0 = Inactive, 1 = Active")]
[DisplayName("Current Flag")]
[Category("Column")]
public bool CurrentFlag
{
get
{
try
{
return _CurrentFlag;
}
catch (System.Exception err)
{
throw new Exception("Error getting CurrentFlag", err);
}
}
set
{
try
{
_CurrentFlag = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting CurrentFlag", err);
}
}
}
/// SQL Type:uniqueidentifier - ROWGUIDCOL number uniquely identifying
/// the record. Used to support a merge replication sample.
private System.Guid _rowguid;
[Description(
"ROWGUIDCOL number uniquely identifying " +
"the record. Used to support a merge" +
"replication sample.")]
[DisplayName("rowguid")]
[Category("Column")]
public System.Guid rowguid
{
get
{
try
{
return _rowguid;
}
catch (System.Exception err)
{
throw new Exception("Error getting rowguid", err);
}
}
set
{
try
{
_rowguid = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting rowguid", err);
}
}
}
/// SQL Type:datetime - Date and time the record was last updated.
private System.DateTime _ModifiedDate;
[Description("Date and time the record was last updated.")]
[DisplayName("Modified Date")]
[Category("Column")]
public System.DateTime ModifiedDate
{
get
{
try
{
return _ModifiedDate;
}
catch (System.Exception err)
{
throw new Exception("Error getting ModifiedDate", err);
}
}
set
{
try
{
_ModifiedDate = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting ModifiedDate", err);
}
}
}
#endregion
#region Related Objects
/// Represents the foreign key object
private Contact _ContactID_Contact;
[Description("Represents the foreign key object of the type Contact")]
public Contact ContactID_Contact
{
get
{
try
{
return _ContactID_Contact;
}
catch (System.Exception err)
{
throw new Exception("Error getting ContactID_Contact", err);
}
}
set
{
try
{
_ContactID_Contact = value;
_ContactID = _ContactID_Contact.ContactID;
}
catch (System.Exception err)
{
throw new Exception("Error setting ContactID_Contact", err);
}
}
}
/// Represents the foreign key object
private Employee _ManagerID_Employee;
[Description("Represents the foreign key object of the type Employee")]
public Employee ManagerID_Employee
{
get
{
try
{
return _ManagerID_Employee;
}
catch (System.Exception err)
{
throw new Exception("Error getting ManagerID_Employee", err);
}
}
set
{
try
{
_ManagerID_Employee = value;
_ManagerID = _ManagerID_Employee.EmployeeID;
}
catch (System.Exception err)
{
throw new Exception("Error setting ManagerID_Employee", err);
}
}
}
#endregion
#region Related Object Collections
/// Represents the foreign key object
private EmployeeAddressCollection _EmployeeAddressCollection;
[Description("Represents the foreign key relation." +
" This is an Collection of Employee.")]
public EmployeeAddressCollection EmployeeAddressCollection
{
get
{
try
{
return _EmployeeAddressCollection;
}
catch (System.Exception err)
{
throw new Exception("Error getting EmployeeAddressCollection", err);
}
}
set
{
try
{
_EmployeeAddressCollection = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting EmployeeAddressCollection", err);
}
}
}
/// Represents the foreign key object
private EmployeeDepartmentHistoryCollection _EmployeeDepartmentHistoryCollection;
[Description("Represents the foreign key relation." +
" This is an Collection of Employee.")]
public EmployeeDepartmentHistoryCollection EmployeeDepartmentHistoryCollection
{
get
{
try
{
return _EmployeeDepartmentHistoryCollection;
}
catch (System.Exception err)
{
throw new Exception("Error getting Employee" +
"DepartmentHistoryCollection", err);
}
}
set
{
try
{
_EmployeeDepartmentHistoryCollection = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting Employee" +
"DepartmentHistoryCollection", err);
}
}
}
/// Represents the foreign key object
private EmployeePayHistoryCollection _EmployeePayHistoryCollection;
[Description("Represents the foreign key relation." +
" This is an Collection of Employee.")]
public EmployeePayHistoryCollection EmployeePayHistoryCollection
{
get
{
try
{
return _EmployeePayHistoryCollection;
}
catch (System.Exception err)
{
throw new Exception("Error getting EmployeePayHistoryCollection", err);
}
}
set
{
try
{
_EmployeePayHistoryCollection = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting EmployeePayHistoryCollection", err);
}
}
}
/// Represents the foreign key object
private JobCandidateCollection _JobCandidateCollection;
[Description("Represents the foreign key relation. This is an Collection of Employee.")]
public JobCandidateCollection JobCandidateCollection
{
get
{
try
{
return _JobCandidateCollection;
}
catch (System.Exception err)
{
throw new Exception("Error getting JobCandidateCollection", err);
}
}
set
{
try
{
_JobCandidateCollection = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting JobCandidateCollection", err);
}
}
}
/// Represents the foreign key object
private PurchaseOrderHeaderCollection _PurchaseOrderHeaderCollection;
[Description("Represents the foreign key relation. This is an Collection of Employee.")]
public PurchaseOrderHeaderCollection PurchaseOrderHeaderCollection
{
get
{
try
{
return _PurchaseOrderHeaderCollection;
}
catch (System.Exception err)
{
throw new Exception("Error getting PurchaseOrderHeaderCollection", err);
}
}
set
{
try
{
_PurchaseOrderHeaderCollection = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting PurchaseOrderHeaderCollection", err);
}
}
}
/// Represents the foreign key object
private SalesPersonCollection _SalesPersonCollection;
[Description("Represents the foreign key relation. This is an Collection of Employee.")]
public SalesPersonCollection SalesPersonCollection
{
get
{
try
{
return _SalesPersonCollection;
}
catch (System.Exception err)
{
throw new Exception("Error getting SalesPersonCollection", err);
}
}
set
{
try
{
_SalesPersonCollection = value;
}
catch (System.Exception err)
{
throw new Exception("Error setting SalesPersonCollection", err);
}
}
}
#endregion
#region Public Methods
public void Select(string ConnectionString)
{
try
{
SqlConnection Conn = new SqlConnection(ConnectionString);
SqlCommand Com = Conn.CreateCommand();
Com.CommandText = Employee._SQL_Select;
Com.Parameters.AddRange(GetSqlParameters());
Conn.Open();
SqlDataReader rs = Com.ExecuteReader();
while(rs.Read())
{
AddFromRecordSet(rs);
}
rs.Close();
Conn.Close();
rs.Dispose();
Com.Dispose();
Conn.Dispose();
}
catch (System.Exception )
{
throw;
}
}
public int Insert(string ConnectionString)
{
try
{
SqlConnection Conn = new SqlConnection(ConnectionString);
SqlCommand Com = Conn.CreateCommand();
Com.CommandText = Employee._SQL_Insert;
Com.Parameters.AddRange(GetSqlParameters());
Conn.Open();
int rowseffected = Com.ExecuteNonQuery();
Conn.Close();
Com.Dispose();
Conn.Dispose();
return rowseffected;
}
catch (System.Exception )
{
throw;
}
}
public int Update(string ConnectionString)
{
try
{
SqlConnection Conn = new SqlConnection(ConnectionString);
SqlCommand Com = Conn.CreateCommand();
Com.CommandText = Employee._SQL_Update;
Com.Parameters.AddRange(GetSqlParameters());
Conn.Open();
int rowseffected = Com.ExecuteNonQuery();
Conn.Close();
Com.Dispose();
Conn.Dispose();
return rowseffected;
}
catch (System.Exception )
{
throw;
}
}
public int Delete(string ConnectionString)
{
try
{
SqlConnection Conn = new SqlConnection(ConnectionString);
SqlCommand Com = Conn.CreateCommand();
Com.CommandText = Employee._SQL_Delete;
Com.Parameters.AddRange(GetSqlParameters());
Conn.Open();
int rowseffected = Com.ExecuteNonQuery();
Conn.Close();
Com.Dispose();
Conn.Dispose();
return rowseffected;
}
catch (System.Exception )
{
throw;
}
}
public string[] GetSqlCommandStrings()
{
try
{
string[] CommStr = new string[4];
CommStr[0] = "SELECT [EmployeeID], [NationalIDNumber], [ContactID],
[LoginID], [ManagerID], [Title], [BirthDate]," +
" [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours],
[SickLeaveHours], [CurrentFlag], [rowguid]," +
" [ModifiedDate] FROM [HumanResources].[Employee] WHERE [EmployeeID] =
" + _EmployeeID + ";
CommStr[1] = "INSERT INTO [HumanResources].[Employee] ([EmployeeID],
[NationalIDNumber], [ContactID], [LoginID]," +
" [ManagerID], [Title], [BirthDate], " +
"[MaritalStatus], [Gender], [HireDate],
[SalariedFlag], [VacationHours], " +
"[SickLeaveHours], [CurrentFlag], [rowguid],
[ModifiedDate]) VALUES(" + _EmployeeID + ", '" +
_NationalIDNumber.Replace("'","''") + "', " + _ContactID + ", '" +
_LoginID.Replace("'","''") + "', " +
_ManagerID + ", '" + _Title.Replace("'","''") +
"', '" + _BirthDate.Replace("'","''") +
"', '" + _MaritalStatus.Replace("'","''") +
"', '" + _Gender.Replace("'","''") +
"', '" + _HireDate.Replace("'","''") + "',
" + _SalariedFlag + ", " + _VacationHours +
", " + _SickLeaveHours + ", " +
_CurrentFlag + ", " + _rowguid + ", '" +
_ModifiedDate.Replace("'","''") + "')";
CommStr[2] = "UPDATE [HumanResources].[Employee] SET [NationalIDNumber] = '" +
_NationalIDNumber.Replace("'","''") + "' ,[ContactID] = " + _ContactID + " ,
[LoginID] = '" + _LoginID.Replace("'","''") +
"' ,[ManagerID] = " + _ManagerID +
" ,[Title] = '" + _Title.Replace("'","''") + "' ,[BirthDate] = '" +
_BirthDate.Replace("'","''") + "' ,[MaritalStatus] = '" +
_MaritalStatus.Replace("'","''") +
"' ,[Gender] = '" + _Gender.Replace("'","''") +
"' ,[HireDate] = '" + _HireDate.Replace("'","''") +
"' ,[SalariedFlag] = " + _SalariedFlag + " ,[VacationHours] = " +
_VacationHours + " ,[SickLeaveHours] = " +
_SickLeaveHours + " ,[CurrentFlag] = " + _CurrentFlag + " ,[rowguid] = " +
_rowguid + " ,[ModifiedDate] = '" + _ModifiedDate.Replace("'","''") +
"' WHERE [EmployeeID] = " + _EmployeeID + ";
CommStr[3] = "DELETE FROM [HumanResources].[Employee] WHERE [EmployeeID] =
" + _EmployeeID + ";
return CommStr;
}
catch (System.Exception )
{
throw;
}
}
#endregion
#region Private Methods
private void AddFromRecordSet(SqlDataReader rs)
{
try
{
if ((rs.IsDBNull(rs.GetOrdinal("EmployeeID")) == false))
{
EmployeeID = rs.GetInt32(rs.GetOrdinal("EmployeeID"));
}
if ((rs.IsDBNull(rs.GetOrdinal(
"NationalIDNumber")) == false))
{
NationalIDNumber = rs.GetString(
rs.GetOrdinal("NationalIDNumber"));
}
if ((rs.IsDBNull(rs.GetOrdinal("ContactID")) == false))
{
ContactID = rs.GetInt32(rs.GetOrdinal("ContactID"));
}
if ((rs.IsDBNull(rs.GetOrdinal("LoginID")) == false))
{
LoginID = rs.GetString(rs.GetOrdinal("LoginID"));
}
if ((rs.IsDBNull(rs.GetOrdinal("ManagerID")) == false))
{
ManagerID = rs.GetInt32(rs.GetOrdinal("ManagerID"));
}
if ((rs.IsDBNull(rs.GetOrdinal("Title")) == false))
{
Title = rs.GetString(rs.GetOrdinal("Title"));
}
if ((rs.IsDBNull(rs.GetOrdinal("BirthDate")) == false))
{
BirthDate = rs.GetDateTime(rs.GetOrdinal("BirthDate"));
}
if ((rs.IsDBNull(rs.GetOrdinal("MaritalStatus")) == false))
{
MaritalStatus =
rs.GetString(rs.GetOrdinal("MaritalStatus"));
}
if ((rs.IsDBNull(rs.GetOrdinal("Gender")) == false))
{
Gender = rs.GetString(rs.GetOrdinal("Gender"));
}
if ((rs.IsDBNull(rs.GetOrdinal("HireDate")) == false))
{
HireDate = rs.GetDateTime(rs.GetOrdinal("HireDate"));
}
if ((rs.IsDBNull(rs.GetOrdinal("SalariedFlag")) == false))
{
SalariedFlag =
rs.GetBoolean(rs.GetOrdinal("SalariedFlag"));
}
if ((rs.IsDBNull(rs.GetOrdinal("VacationHours")) == false))
{
VacationHours = rs.GetInt16(rs.GetOrdinal("VacationHours"));
}
if ((rs.IsDBNull(rs.GetOrdinal("SickLeaveHours")) == false))
{
SickLeaveHours = rs.GetInt16(rs.GetOrdinal("SickLeaveHours"));
}
if ((rs.IsDBNull(rs.GetOrdinal("CurrentFlag")) == false))
{
CurrentFlag = rs.GetBoolean(rs.GetOrdinal("CurrentFlag"));
}
if ((rs.IsDBNull(rs.GetOrdinal("rowguid")) == false))
{
rowguid = rs.GetGuid(rs.GetOrdinal("rowguid"));
}
if ((rs.IsDBNull(rs.GetOrdinal("ModifiedDate")) == false))
{
ModifiedDate = rs.GetDateTime(rs.GetOrdinal("ModifiedDate"));
}
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception Exc)
{
throw Exc;
}
}
private SqlParameter[] GetSqlParameters()
{
List<SqlParameter> SqlParmColl = new List<SqlParameter>();
try
{
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@EmployeeID", EmployeeID, SqlDbType.Int));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@NationalIDNumber", NationalIDNumber, SqlDbType.NVarChar));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@ContactID", ContactID, SqlDbType.Int));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@LoginID", LoginID, SqlDbType.NVarChar));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@ManagerID", ManagerID, SqlDbType.Int));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@Title", Title, SqlDbType.NVarChar));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@BirthDate", BirthDate, SqlDbType.DateTime));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@MaritalStatus", MaritalStatus, SqlDbType.NChar));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@Gender", Gender, SqlDbType.NChar));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@HireDate", HireDate, SqlDbType.DateTime));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@SalariedFlag", SalariedFlag, ));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@VacationHours", VacationHours, SqlDbType.SmallInt));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@SickLeaveHours", SickLeaveHours, SqlDbType.SmallInt));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@CurrentFlag", CurrentFlag, ));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@rowguid", rowguid, SqlDbType.UniqueIdentifier));
SqlParmColl.Add(AdventureWorks.AddSqlParm(
"@ModifiedDate", ModifiedDate, SqlDbType.DateTime));
return SqlParmColl.ToArray();
}
catch (Exception Exc)
{
throw Exc;
}
}
#endregion
}
16 July 2008
17 July 2008
- Version 1.0.0.1 uploaded.
- Fixed the Server name box, can now connect to remote SQL Server (thanks to: jklucker).
- Fixed the word lenght to Length (thanks to: CincDev).
- Fixed some basic exception handling, but not finished (thanks to: jklucker).
- Added Catalog select box (thanks to: CincDev).
- Added Save settings and Load settings (thanks to: CincDev).
31 July 2008
- Added
Select
, Insert
, Update
, Delete
methods to the classes. - Added the
GetSQLCommandStrings
method, which will return an array of strings.
19 August 2008
- Added a demo on how to make a
ClassCollection
(inherited from CollectionBase
).
16 September 2008
- Added the ability to generate code from Views.
- Fixed the
System.Collections.Generic
missing in the Import list. - The source files are now in Visual Studio 2008 and not Visual Studio 2005 (sorry, I had to upgrade; I think we can correct this (go from VS 2008 to VS 2005) by deleting the solution files and making them by ourselves).
- Note: The CP article wizard is playing tricks on me; for some reason, all my quote signs in all code examples are now marked with HTML quote tags ["], don't know why... sorry.
9 January 2009
- Updated the following classes so they can handle case sensitive collations:
IndexColumn
, IndexColumns
, IdentityColumn
, IdentityColumns
, View
, Views
. - Note: The CP article wizard is playing tricks on me; for some reason, all my quote signs in all code examples are now marked with HTML quote tags ["], don't know why... sorry.