private void ViewTable_Load(object sender, EventArgs e)
{
using (OdbcCommand cmd = _odbcConnection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM " + _selectedTable;
using (OdbcDataReader reader = cmd.ExecuteReader())
{
int fieldCount = reader.FieldCount;
for (int i = 0; i < fieldCount; i++)
{
dataGrid.Columns.Add(reader.GetName(i), reader.GetName(i));
}
MessageBox.Show("Does this column has rows: " + reader.HasRows);
while (reader.Read())
{
DataGridViewRow newRow = new DataGridViewRow();
for (int i = 0; i < fieldCount; i++)
{
DataGridViewCell newCell = new DataGridViewTextBoxCell();
newCell.Value = reader.GetValue(i);
newCell.ValueType = reader.GetFieldType(i);
newRow.Cells.Add(newCell);
newCell.ReadOnly = true;
}
dataGrid.Rows.Add(newRow);
}
}
}
}
private string GetReaderString(OdbcDataReader reader, int idx)
{
Type type;
string sType;
string str;
type = reader.GetFieldType(idx);
sType = type.Name;
if (sType == "String")
{
str = reader.GetString(idx);
}
else if (sType == "Int32")
{
str = reader.GetInt32(idx).ToString();
}
else if (sType == "DateTime")
{
str = reader.GetString(idx);
}
else
{
str = "";
}
return(str);
}
private unRetourRequete copieDonnees(ref OdbcDataReader source)
{
unRetourRequete retour = new unRetourRequete();
try
{
retour.Tables.Add("Resultat1");
for (int i = 0; i <= source.FieldCount - 1; i++)
{
retour.Tables[0].Columns.Add(source.GetName(i), source.GetFieldType(i));
}
while (source.Read())
{
retour.Tables[0].Rows.Add();
for (int numColonne = 0; numColonne <= source.FieldCount - 1; numColonne++)
{
retour.Tables[0].Rows[retour.Tables[0].Rows.Count - 1][numColonne] = source[numColonne];
}
}
return(retour);
}
catch (Exception ex)
{
_lastError = ex.Message;
}
finally
{
try
{
source.Close();
}
catch {}
}
return(null);
}
private string getReaderString(OdbcDataReader reader, int idx)
{
Type type;
string sType;
string str;
if (reader.IsDBNull(idx) == true)
{
str = "";
}
else
{
type = reader.GetFieldType(idx);
sType = type.Name;
if (sType == "String")
{
str = reader.GetString(idx);
}
else if (sType == "Int32")
{
str = reader.GetInt32(idx).ToString();
}
else
{
str = "";
}
}
return(str);
}
private string GetReaderString(OdbcDataReader reader, int idx)
{
Type type;
string sType;
string str;
str = "";
try
{
type = reader.GetFieldType(idx);
sType = type.Name;
if (sType == "String")
{
str = reader.GetString(idx);
}
else if (sType == "Int32")
{
str = reader.GetInt32(idx).ToString();
}
}
catch (Exception ex)
{
App.ErrorLogAppend("GetReaderString");
App.LogOut(ex.ToString());
}
return(str);
}
private string GetReaderString(OdbcDataReader reader, int idx)
{
Type type;
string sType;
string str;
if (DBNull.Value.Equals(reader.GetValue(idx)))
{
return("");
}
type = reader.GetFieldType(idx);
sType = type.Name;
if (sType == "String")
{
str = reader.GetString(idx);
}
else if (sType == "Int32")
{
str = reader.GetInt32(idx).ToString();
}
else
{
str = "";
}
return(str);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_SAProle.
/// </summary>
/// <param name="Name"></param>
/// <param name="SubProcessID"></param>
/// <param name="System"></param>
/// <param name="Platform"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewSAProle(string Name, int SubProcessID, string System, string Platform)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_SAProle\"(\"c_u_Name\",\"c_r_SubProcess\",\"c_u_System\",\"c_u_Platform\") VALUES(?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (Name == null)
{
throw new Exception("Name must not be null!");
}
cmd.Parameters.Add("c_u_Name", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_Name"].Value = (Name != null ? (object)Name : DBNull.Value);
cmd.Parameters.Add("c_r_SubProcess", OdbcType.Int);
cmd.Parameters["c_r_SubProcess"].Value = (object)SubProcessID;
if (System == null)
{
throw new Exception("System must not be null!");
}
cmd.Parameters.Add("c_u_System", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_System"].Value = (System != null ? (object)System : DBNull.Value);
if (Platform == null)
{
throw new Exception("Platform must not be null!");
}
cmd.Parameters.Add("c_u_Platform", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_Platform"].Value = (Platform != null ? (object)Platform : DBNull.Value);
OdbcDataReader dri = cmd.ExecuteReader();
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : (int)dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_BusRoleOwner.
/// </summary>
/// <param name="EID"></param>
/// <param name="Geography"></param>
/// <param name="Rank"></param>
/// <param name="BusRoleID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewBusRoleOwner(string EID, string Geography, string Rank, int BusRoleID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_BusRoleOwner\"(\"c_u_EID\",\"c_u_Geography\",\"c_u_Rank\",\"c_r_BusRole\") VALUES(?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (EID == null)
{
cmd.Dispose(); DBClose(); throw new Exception("EID must not be null!");
}
cmd.Parameters.Add("c_u_EID", OdbcType.NVarChar, 10);
cmd.Parameters["c_u_EID"].Value = (EID != null ? (object)EID : DBNull.Value);
if (Geography == null)
{
cmd.Dispose(); DBClose(); throw new Exception("Geography must not be null!");
}
cmd.Parameters.Add("c_u_Geography", OdbcType.NVarChar, 10);
cmd.Parameters["c_u_Geography"].Value = (Geography != null ? (object)Geography : DBNull.Value);
if (Rank == null)
{
cmd.Dispose(); DBClose(); throw new Exception("Rank must not be null!");
}
cmd.Parameters.Add("c_u_Rank", OdbcType.NVarChar, 10);
cmd.Parameters["c_u_Rank"].Value = (Rank != null ? (object)Rank : DBNull.Value);
cmd.Parameters.Add("c_r_BusRole", OdbcType.Int);
cmd.Parameters["c_r_BusRole"].Value = (object)BusRoleID;
OdbcDataReader dri = null; try{ dri = cmd.ExecuteReader(); }catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
try{ dri.Read(); } catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
cmd.Dispose(); DBClose(); throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_SAPauthObj.
/// </summary>
/// <param name="Name"></param>
/// <param name="Description"></param>
/// <param name="SAPauthClassID"></param>
/// <param name="Status"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewSAPauthObj(string Name, string Description, int SAPauthClassID, string Status)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_SAPauthObj\"(\"c_u_Name\",\"c_u_Description\",\"c_r_SAPauthClass\",\"c_u_Status\") VALUES(?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (Name == null)
{
throw new Exception("Name must not be null!");
}
cmd.Parameters.Add("c_u_Name", OdbcType.NVarChar, 30);
cmd.Parameters["c_u_Name"].Value = (Name != null ? (object)Name : DBNull.Value);
cmd.Parameters.Add("c_u_Description", OdbcType.NText);
cmd.Parameters["c_u_Description"].Value = (object)Description;
cmd.Parameters.Add("c_r_SAPauthClass", OdbcType.Int);
cmd.Parameters["c_r_SAPauthClass"].Value = (object)SAPauthClassID;
if (Status == null)
{
throw new Exception("Status must not be null!");
}
cmd.Parameters.Add("c_u_Status", OdbcType.NVarChar, 1);
cmd.Parameters["c_u_Status"].Value = (Status != null ? (object)Status : DBNull.Value);
OdbcDataReader dri = cmd.ExecuteReader();
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : (int)dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_EASfileAttachment.
/// </summary>
/// <param name="Filename"></param>
/// <param name="Comment"></param>
/// <param name="UploadDate"></param>
/// <param name="EntAssignmentSetID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewEASfileAttachment(string Filename, string Comment, DateTime UploadDate, int EntAssignmentSetID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_EASfileAttachment\"(\"c_u_Filename\",\"c_u_Comment\",\"c_u_UploadDate\",\"c_r_EntAssignmentSet\") VALUES(?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (Filename == null)
{
throw new Exception("Filename must not be null!");
}
cmd.Parameters.Add("c_u_Filename", OdbcType.NVarChar, 256);
cmd.Parameters["c_u_Filename"].Value = (Filename != null ? (object)Filename : DBNull.Value);
if (Comment == null)
{
throw new Exception("Comment must not be null!");
}
cmd.Parameters.Add("c_u_Comment", OdbcType.NVarChar, 1024);
cmd.Parameters["c_u_Comment"].Value = (Comment != null ? (object)Comment : DBNull.Value);
cmd.Parameters.Add("c_u_UploadDate", OdbcType.DateTime);
cmd.Parameters["c_u_UploadDate"].Value = HELPERS.SetSafeDBDate(UploadDate);
cmd.Parameters.Add("c_r_EntAssignmentSet", OdbcType.Int);
cmd.Parameters["c_r_EntAssignmentSet"].Value = (object)EntAssignmentSetID;
OdbcDataReader dri = cmd.ExecuteReader();
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
/// Gets the value of the column at the specified index as a float.
/// </summary>
/// <param name="columnIndex">Index of column to examine.</param>
/// <returns>Value of the column as a float.</returns>
public float GetFloat(int columnIndex)
{
Type columnType = _reader.GetFieldType(columnIndex);
if (columnType == typeof(decimal))
{
return((float)_reader.GetDecimal(columnIndex));
}
if (columnType == typeof(double))
{
return((float)_reader.GetDouble(columnIndex));
}
return(_reader.GetFloat(columnIndex));
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_User.
/// </summary>
/// <param name="EID"></param>
/// <param name="Name"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewUser(string EID, string Name)
{
// DFSklar added on 02-Oct-2010:
EID = EID.ToUpper().Trim();
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_User\"(\"c_u_EID\",\"c_u_Name\") VALUES(?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (EID == null)
{
cmd.Dispose(); DBClose(); throw new Exception("EID must not be null!");
}
cmd.Parameters.Add("c_u_EID", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_EID"].Value = (EID != null ? (object)EID : DBNull.Value);
if (Name == null)
{
cmd.Dispose(); DBClose(); throw new Exception("Name must not be null!");
}
cmd.Parameters.Add("c_u_Name", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_Name"].Value = (Name != null ? (object)Name : DBNull.Value);
OdbcDataReader dri = null; try{ dri = cmd.ExecuteReader(); }catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
try{ dri.Read(); } catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
cmd.Dispose(); DBClose(); throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
/// 查询字段属性
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="Field">字段名</param>
/// <param name="FieldType">字段类型</param>
/// <returns>true 字段存在，false 字段不存在</returns>
public bool CheckField(string TableName, string Field, out System.Type FieldType, out bool CanBeNull, out bool IsPrimaryKey)
{
FieldType = typeof(object);
CanBeNull = false;
IsPrimaryKey = false;
if (string.IsNullOrEmpty(TableName))
{
throw new NullReferenceException("TableName Is Null Or Empty");
}
else if (string.IsNullOrEmpty(Field))
{
throw new NullReferenceException("Field Is Null Or Empty");
}
try
{
OdbcConnection conn = (OdbcConnection)GetConnection();
bool res = false;
DataTable dt = conn.GetSchema("Columns", new string[] { null, null, TableName });
int m = dt.Columns.IndexOf("COLUMN_NAME");
int n = dt.Columns.IndexOf("ISNULLABLE");
int o = dt.Columns.IndexOf("COLUMN_KEY");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
if (dr.ItemArray.GetValue(m).ToString().ToUpper() == Field.ToUpper())
{
CanBeNull = dr.ItemArray.GetValue(n).ToString().ToUpper().Contains("YES");
IsPrimaryKey = dr.ItemArray.GetValue(o).ToString().ToUpper().Contains("PRI");
res = true;
break;
}
}
if (res)
{
OdbcCommand cmd = new OdbcCommand("select " + Field + " from " + TableName + "", conn);
cmd.CommandTimeout = this.Timeout * 1000;
OdbcDataReader da = cmd.ExecuteReader();
FieldType = da.GetFieldType(0);
}
CloseConnection(conn);
return(res);
}
catch
{
return(false);
}
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_EditingWorkspace.
/// </summary>
/// <param name="Commentary"></param>
/// <param name="TimeOfBirth"></param>
/// <param name="SubProcessID"></param>
/// <param name="UserID"></param>
/// <param name="EntAssignmentSetID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewEditingWorkspace(string Commentary, DateTime TimeOfBirth, int SubProcessID, int UserID, int EntAssignmentSetID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_EditingWorkspace\"(\"c_u_Commentary\",\"c_u_TimeOfBirth\",\"c_r_SubProcess\",\"c_r_User\",\"c_r_EntAssignmentSet\") VALUES(?,?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (Commentary == null)
{
cmd.Dispose(); DBClose(); throw new Exception("Commentary must not be null!");
}
cmd.Parameters.Add("c_u_Commentary", OdbcType.NVarChar, 2048);
cmd.Parameters["c_u_Commentary"].Value = (Commentary != null ? (object)Commentary : DBNull.Value);
cmd.Parameters.Add("c_u_TimeOfBirth", OdbcType.DateTime);
cmd.Parameters["c_u_TimeOfBirth"].Value = HELPERS.SetSafeDBDate(TimeOfBirth);
cmd.Parameters.Add("c_r_SubProcess", OdbcType.Int);
cmd.Parameters["c_r_SubProcess"].Value = (object)SubProcessID;
cmd.Parameters.Add("c_r_User", OdbcType.Int);
cmd.Parameters["c_r_User"].Value = (object)UserID;
cmd.Parameters.Add("c_r_EntAssignmentSet", OdbcType.Int);
cmd.Parameters["c_r_EntAssignmentSet"].Value = (object)EntAssignmentSetID;
OdbcDataReader dri = null; try{ dri = cmd.ExecuteReader(); }catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
try{ dri.Read(); } catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
cmd.Dispose(); DBClose(); throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_BusRole.
/// </summary>
/// <param name="Name"></param>
/// <param name="Description"></param>
/// <param name="SubProcessID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewBusRole(string Name, string Description, int SubProcessID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_BusRole\"(\"c_u_Name\",\"c_u_Description\",\"c_r_SubProcess\") VALUES(?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (Name == null)
{
cmd.Dispose(); DBClose(); throw new Exception("Name must not be null!");
}
cmd.Parameters.Add("c_u_Name", OdbcType.NVarChar, 100);
cmd.Parameters["c_u_Name"].Value = (Name != null ? (object)Name : DBNull.Value);
if (Description == null)
{
cmd.Dispose(); DBClose(); throw new Exception("Description must not be null!");
}
cmd.Parameters.Add("c_u_Description", OdbcType.NVarChar, 250);
cmd.Parameters["c_u_Description"].Value = (Description != null ? (object)Description : DBNull.Value);
cmd.Parameters.Add("c_r_SubProcess", OdbcType.Int);
cmd.Parameters["c_r_SubProcess"].Value = (object)SubProcessID;
OdbcDataReader dri = null; try{ dri = cmd.ExecuteReader(); }catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
try{ dri.Read(); } catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
cmd.Dispose(); DBClose(); throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_EntAssignment.
/// </summary>
/// <param name="EntAssignmentSetID"></param>
/// <param name="BusRoleID"></param>
/// <param name="EntitlementID"></param>
/// <param name="Status"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewEntAssignment(int EntAssignmentSetID, int BusRoleID, int EntitlementID, string Status)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_EntAssignment\"(\"c_r_EntAssignmentSet\",\"c_r_BusRole\",\"c_r_Entitlement\",\"c_u_Status\") VALUES(?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
cmd.Parameters.Add("c_r_EntAssignmentSet", OdbcType.Int);
cmd.Parameters["c_r_EntAssignmentSet"].Value = (object)EntAssignmentSetID;
cmd.Parameters.Add("c_r_BusRole", OdbcType.Int);
cmd.Parameters["c_r_BusRole"].Value = (object)BusRoleID;
cmd.Parameters.Add("c_r_Entitlement", OdbcType.Int);
cmd.Parameters["c_r_Entitlement"].Value = (object)EntitlementID;
if (Status == null)
{
cmd.Dispose(); DBClose(); throw new Exception("Status must not be null!");
}
cmd.Parameters.Add("c_u_Status", OdbcType.NVarChar, 1);
cmd.Parameters["c_u_Status"].Value = (Status != null ? (object)Status : DBNull.Value);
OdbcDataReader dri = null; try { dri = cmd.ExecuteReader(); }
catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
try { dri.Read(); }
catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
cmd.Dispose(); DBClose(); throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_ReconcDiffItem.
/// </summary>
/// <param name="DiffType"></param>
/// <param name="ReconcReportID"></param>
/// <param name="DiffObject"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewReconcDiffItem(string DiffType, int ReconcReportID, string DiffObject)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_ReconcDiffItem\"(\"c_u_DiffType\",\"c_r_ReconcReport\",\"c_u_DiffObject\") VALUES(?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (DiffType == null)
{
throw new Exception("DiffType must not be null!");
}
cmd.Parameters.Add("c_u_DiffType", OdbcType.NVarChar, 3);
cmd.Parameters["c_u_DiffType"].Value = (DiffType != null ? (object)DiffType : DBNull.Value);
cmd.Parameters.Add("c_r_ReconcReport", OdbcType.Int);
cmd.Parameters["c_r_ReconcReport"].Value = (object)ReconcReportID;
if (DiffObject == null)
{
throw new Exception("DiffObject must not be null!");
}
cmd.Parameters.Add("c_u_DiffObject", OdbcType.NVarChar, 4);
cmd.Parameters["c_u_DiffObject"].Value = (DiffObject != null ? (object)DiffObject : DBNull.Value);
OdbcDataReader dri = cmd.ExecuteReader();
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : (int)dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_OrgValue1252.
/// </summary>
/// <param name="FieldName"></param>
/// <param name="TcodeAssignmentSetID"></param>
/// <param name="SAProleID"></param>
/// <param name="EditStatus"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewOrgValue1252(string FieldName, int TcodeAssignmentSetID, int SAProleID, int EditStatus)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_OrgValue1252\"(\"c_u_FieldName\",\"c_r_TcodeAssignmentSet\",\"c_r_SAProle\",\"c_u_EditStatus\") VALUES(?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (FieldName == null)
{
throw new Exception("FieldName must not be null!");
}
cmd.Parameters.Add("c_u_FieldName", OdbcType.NVarChar, 8);
cmd.Parameters["c_u_FieldName"].Value = (FieldName != null ? (object)FieldName : DBNull.Value);
cmd.Parameters.Add("c_r_TcodeAssignmentSet", OdbcType.Int);
cmd.Parameters["c_r_TcodeAssignmentSet"].Value = (object)TcodeAssignmentSetID;
cmd.Parameters.Add("c_r_SAProle", OdbcType.Int);
cmd.Parameters["c_r_SAProle"].Value = (object)SAProleID;
cmd.Parameters.Add("c_u_EditStatus", OdbcType.Int);
cmd.Parameters["c_u_EditStatus"].Value = (object)EditStatus;
OdbcDataReader dri = cmd.ExecuteReader();
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : (int)dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_SAPsecurityOrgAxis.
/// </summary>
/// <param name="English_Name"></param>
/// <param name="SAP_Name"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewSAPsecurityOrgAxis(string English_Name, string SAP_Name)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_SAPsecurityOrgAxis\"(\"c_u_English_Name\",\"c_u_SAP_Name\") VALUES(?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (English_Name == null)
{
throw new Exception("English_Name must not be null!");
}
cmd.Parameters.Add("c_u_English_Name", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_English_Name"].Value = (English_Name != null ? (object)English_Name : DBNull.Value);
if (SAP_Name == null)
{
throw new Exception("SAP_Name must not be null!");
}
cmd.Parameters.Add("c_u_SAP_Name", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_SAP_Name"].Value = (SAP_Name != null ? (object)SAP_Name : DBNull.Value);
OdbcDataReader dri = cmd.ExecuteReader();
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_SAPsecurityOrgValue.
/// </summary>
/// <param name="ValueString"></param>
/// <param name="SAPsecurityOrgAxisID"></param>
/// <param name="SAPsecurityOrgID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewSAPsecurityOrgValue(string ValueString, int SAPsecurityOrgAxisID, int SAPsecurityOrgID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_SAPsecurityOrgValue\"(\"c_u_ValueString\",\"c_r_SAPsecurityOrgAxis\",\"c_r_SAPsecurityOrg\") VALUES(?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (ValueString == null)
{
cmd.Dispose(); DBClose(); throw new Exception("ValueString must not be null!");
}
cmd.Parameters.Add("c_u_ValueString", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_ValueString"].Value = (ValueString != null ? (object)ValueString : DBNull.Value);
cmd.Parameters.Add("c_r_SAPsecurityOrgAxis", OdbcType.Int);
cmd.Parameters["c_r_SAPsecurityOrgAxis"].Value = (object)SAPsecurityOrgAxisID;
cmd.Parameters.Add("c_r_SAPsecurityOrg", OdbcType.Int);
cmd.Parameters["c_r_SAPsecurityOrg"].Value = (object)SAPsecurityOrgID;
OdbcDataReader dri = null; try{ dri = cmd.ExecuteReader(); }catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
try{ dri.Read(); } catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
cmd.Dispose(); DBClose(); throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
public static string SafeGetString(this OdbcDataReader reader, int colIndex)
{
//Debug.Print("" + reader.GetFieldType(colIndex));
//Debug.Print("" + reader.GetDataTypeName(colIndex));
if (!reader.IsDBNull(colIndex))
{
if (reader.GetFieldType(colIndex) == typeof(String))
{
return(reader.GetString(colIndex));
}
else // if(reader.GetFieldType(colIndex) == typeof(Single) )
{
return(string.Empty);
}
}
else
{
return(string.Empty);
}
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_MVFormula.
/// </summary>
/// <param name="KEYapplication"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewMVFormula(string KEYapplication)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_MVFormula\"(\"c_u_KEYapplication\") VALUES(?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (KEYapplication == null)
{
cmd.Dispose(); DBClose(); throw new Exception("KEYapplication must not be null!");
}
cmd.Parameters.Add("c_u_KEYapplication", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_KEYapplication"].Value = (KEYapplication != null ? (object)KEYapplication : DBNull.Value);
OdbcDataReader dri = null; try{ dri = cmd.ExecuteReader(); }catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
try{ dri.Read(); } catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
cmd.Dispose(); DBClose(); throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_FuncApplNotes.
/// </summary>
/// <param name="BusRoleID"></param>
/// <param name="REFapplication"></param>
/// <param name="Comment"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewFuncApplNotes(int BusRoleID, int REFapplication, string Comment)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_FuncApplNotes\"(\"c_r_BusRole\",\"c_u_REFapplication\",\"c_u_Comment\") VALUES(?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
cmd.Parameters.Add("c_r_BusRole", OdbcType.Int);
cmd.Parameters["c_r_BusRole"].Value = (object)BusRoleID;
cmd.Parameters.Add("c_u_REFapplication", OdbcType.Int);
cmd.Parameters["c_u_REFapplication"].Value = (object)REFapplication;
cmd.Parameters.Add("c_u_Comment", OdbcType.NText);
cmd.Parameters["c_u_Comment"].Value = (object)Comment;
OdbcDataReader dri = cmd.ExecuteReader();
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : (int)dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
private int GetReaderInt32(OdbcDataReader reader, int idx)
{
Type type;
string sType;
int nNum;
nNum = 0;
try
{
type = reader.GetFieldType(idx);
sType = type.Name;
if (sType == "Int32")
{
nNum = reader.GetInt32(idx);
}
}
catch (Exception ex)
{
App.ErrorLogAppend("GetReaderString");
App.LogOut(ex.ToString());
}
return(nNum);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_EntAssignmentSet.
/// </summary>
/// <param name="SubProcessID"></param>
/// <param name="UserID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewEntAssignmentSet(int SubProcessID, int UserID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_EntAssignmentSet\"(\"c_r_SubProcess\",\"c_r_User\") VALUES(?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
cmd.Parameters.Add("c_r_SubProcess", OdbcType.Int);
cmd.Parameters["c_r_SubProcess"].Value = (object)SubProcessID;
cmd.Parameters.Add("c_r_User", OdbcType.Int);
cmd.Parameters["c_r_User"].Value = (object)UserID;
OdbcDataReader dri = null; try{ dri = cmd.ExecuteReader(); }catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
try{ dri.Read(); } catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
cmd.Dispose(); DBClose(); throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_ReconcReport.
/// </summary>
/// <param name="CreationTime"></param>
/// <param name="UserID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewReconcReport(DateTime CreationTime, int UserID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_ReconcReport\"(\"c_u_CreationTime\",\"c_r_User\") VALUES(?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
cmd.Parameters.Add("c_u_CreationTime", OdbcType.DateTime);
cmd.Parameters["c_u_CreationTime"].Value = (object)CreationTime;
cmd.Parameters.Add("c_r_User", OdbcType.Int);
cmd.Parameters["c_r_User"].Value = (object)UserID;
OdbcDataReader dri = cmd.ExecuteReader();
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : (int)dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
static int Main(string[] args)
{
int
Result = -1;
StreamWriter
fstr_out = null;
string
tmpString = "log.log",
TableName;
#if TEST_DB_BY_ODBC
OdbcConnection
odbc_conn = null;
OdbcCommand
odbc_cmd = null;
OdbcDataReader
odbc_rdr = null;
OdbcDataAdapter
odbc_da = null;
#endif
OleDbConnection
conn = null;
OleDbCommand
cmd = null;
OleDbDataReader
rdr = null;
OleDbDataAdapter
da = null;
DataTable
tmpDataTable;
int
tmpInt;
object[]
tmpObjects;
FileStream
fs;
byte[]
Blob;
try
{
try
{
fstr_out = new StreamWriter(tmpString, false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
string
PathToDb = "E:\\Soft.src\\CBuilder\\Tests\\Paradox\\Test.#1\\db",
CommonDbTableName = "Common",
CommonDbTableSQLCreate = @"
create table " + CommonDbTableName + @"(
FInt integer,
FChar char(254)
)";
#if TEST_DB_BY_ODBC
if (!PathToDb.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDb += Path.DirectorySeparatorChar;
}
tmpString = "Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;DefaultDir=" + PathToDb + ";Dbq=" + PathToDb + ";CollatingSequence=ASCII";
odbc_conn = new OdbcConnection(tmpString);
odbc_conn.Open();
fstr_out.WriteLine("ConnectionString: " + odbc_conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + odbc_conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + odbc_conn.Database);
fstr_out.WriteLine("DataSource: " + odbc_conn.DataSource);
fstr_out.WriteLine("Driver: " + odbc_conn.Driver);
fstr_out.WriteLine("ServerVersion: " + odbc_conn.ServerVersion);
fstr_out.WriteLine("State: " + odbc_conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDb + CommonDbTableName + ".db";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
odbc_cmd = odbc_conn.CreateCommand();
odbc_cmd.CommandType = CommandType.Text;
odbc_cmd.CommandText = CommonDbTableSQLCreate;
odbc_cmd.ExecuteNonQuery();
odbc_cmd.CommandText = "insert into " + CommonDbTableName + " values (1,'FChar (‘„ар)')";
odbc_cmd.ExecuteNonQuery();
#if TEST_BLOB
if (odbc_cmd == null)
{
odbc_cmd = odbc_conn.CreateCommand();
}
odbc_cmd.CommandType = CommandType.Text;
odbc_cmd.CommandText = "select * from TestTypes";
odbc_cmd.Parameters.Clear();
odbc_rdr = odbc_cmd.ExecuteReader();
do
{
if (odbc_rdr.HasRows)
{
for (int i = 0; i < odbc_rdr.FieldCount; ++i)
{
fstr_out.WriteLine(odbc_rdr.GetName(i) + " GetDataTypeName(): \"" + odbc_rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + odbc_rdr.GetFieldType(i) + "\"");
}
tmpInt = odbc_rdr.GetOrdinal("FGraphic");
while (odbc_rdr.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = (byte[])odbc_rdr["FGraphic"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = new byte[odbc_rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue)];
rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
}while(rdr.NextResult());
odbc_rdr.Close();
#endif
odbc_conn.Close();
#endif
if (PathToDb.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDb = PathToDb.Remove(PathToDb.Length - 1, 1);
}
tmpString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathToDb + ";Extended Properties=Paradox 5.x";
conn = new OleDbConnection(tmpString);
conn.Open();
fstr_out.WriteLine("ConnectionString: " + conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + conn.Database);
fstr_out.WriteLine("DataSource: " + conn.DataSource);
fstr_out.WriteLine("Provider: " + conn.Provider);
fstr_out.WriteLine("ServerVersion: " + conn.ServerVersion);
fstr_out.WriteLine("State: " + conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDb + Path.DirectorySeparatorChar + CommonDbTableName + ".db";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = CommonDbTableSQLCreate;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + CommonDbTableName + " values (1,'FChar (‘„ар)')";
cmd.ExecuteNonQuery();
#if TEST_BLOB
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
TableName = "TestTypes";
tmpObjects = new object[] { null, null, TableName, null };
fstr_out.WriteLine("OleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns)");
tmpDataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, tmpObjects);
fstr_out.WriteLine("Columns in " + TableName + " table:");
foreach (DataRow row in tmpDataTable.Rows)
{
fstr_out.WriteLine("\t" + row["TABLE_CATALOG"] + " " + row["TABLE_NAME"] + " " + row["COLUMN_NAME"].ToString() + " " + row["DATA_TYPE"] + " " + row["TABLE_SCHEMA"]);
}
fstr_out.WriteLine();
#if TEST_BLOB_SAVE
cmd.CommandText = "update TestTypes set FGraphic = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("FGraphic", OleDbType.LongVarBinary);
fs = new FileStream("welcome.bmp", FileMode.Open, FileAccess.Read);
Blob = new byte[fs.Length];
fs.Read(Blob, 0, Blob.Length);
cmd.Parameters["FGraphic"].Value = Blob;
tmpInt = cmd.ExecuteNonQuery();
#endif
cmd.CommandText = "select * from TestTypes";
cmd.Parameters.Clear();
rdr = cmd.ExecuteReader();
do
{
if (rdr.HasRows)
{
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.WriteLine(rdr.GetName(i) + " GetDataTypeName(): \"" + rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + rdr.GetFieldType(i) + "\"");
}
tmpInt = rdr.GetOrdinal("FGraphic");
while (rdr.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = (byte[])rdr["FGraphic"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = new byte[rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue)];
rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
}while(rdr.NextResult());
rdr.Close();
#endif
Result = 0;
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace);
}
}
finally
{
#if TEST_DB_BY_ODBC
if (odbc_rdr != null && !odbc_rdr.IsClosed)
{
odbc_rdr.Close();
}
if (odbc_conn != null && odbc_conn.State == System.Data.ConnectionState.Open)
{
odbc_conn.Close();
}
#endif
if (rdr != null && !rdr.IsClosed)
{
rdr.Close();
}
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
if (fstr_out != null)
{
fstr_out.Close();
}
}
return(Result);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_Entitlement.
/// </summary>
/// <param name="StandardActivity"></param>
/// <param name="RoleType"></param>
/// <param name="System"></param>
/// <param name="Platform"></param>
/// <param name="EntitlementName"></param>
/// <param name="EntitlementValue"></param>
/// <param name="Application"></param>
/// <param name="CHECKSUM"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewEntitlement(string StandardActivity, string RoleType, string System, string Platform, string EntitlementName, string EntitlementValue, string Application, string CHECKSUM)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_Entitlement\"(\"c_u_StandardActivity\",\"c_u_RoleType\",\"c_u_System\",\"c_u_Platform\",\"c_u_EntitlementName\",\"c_u_EntitlementValue\",\"c_u_Application\",\"c_u_CHECKSUM\") VALUES(?,?,?,?,?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sqlsrv"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (StandardActivity == null)
{
throw new Exception("StandardActivity must not be null!");
}
cmd.Parameters.Add("c_u_StandardActivity", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_StandardActivity"].Value = (StandardActivity != null ? (object)StandardActivity : DBNull.Value);
if (RoleType == null)
{
throw new Exception("RoleType must not be null!");
}
cmd.Parameters.Add("c_u_RoleType", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_RoleType"].Value = (RoleType != null ? (object)RoleType : DBNull.Value);
if (System == null)
{
throw new Exception("System must not be null!");
}
cmd.Parameters.Add("c_u_System", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_System"].Value = (System != null ? (object)System : DBNull.Value);
if (Platform == null)
{
throw new Exception("Platform must not be null!");
}
cmd.Parameters.Add("c_u_Platform", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_Platform"].Value = (Platform != null ? (object)Platform : DBNull.Value);
if (EntitlementName == null)
{
throw new Exception("EntitlementName must not be null!");
}
cmd.Parameters.Add("c_u_EntitlementName", OdbcType.NVarChar, 100);
cmd.Parameters["c_u_EntitlementName"].Value = (EntitlementName != null ? (object)EntitlementName : DBNull.Value);
if (EntitlementValue == null)
{
throw new Exception("EntitlementValue must not be null!");
}
cmd.Parameters.Add("c_u_EntitlementValue", OdbcType.NVarChar, 1024);
cmd.Parameters["c_u_EntitlementValue"].Value = (EntitlementValue != null ? (object)EntitlementValue : DBNull.Value);
if (Application == null)
{
throw new Exception("Application must not be null!");
}
cmd.Parameters.Add("c_u_Application", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_Application"].Value = (Application != null ? (object)Application : DBNull.Value);
if (CHECKSUM == null)
{
throw new Exception("CHECKSUM must not be null!");
}
cmd.Parameters.Add("c_u_CHECKSUM", OdbcType.NVarChar, 100);
cmd.Parameters["c_u_CHECKSUM"].Value = (CHECKSUM != null ? (object)CHECKSUM : DBNull.Value);
OdbcDataReader dri = cmd.ExecuteReader();
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : (int)dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
protected bool LoadTableRows()
{
bool isDone = false;
List <SpatialObject> addedRows = new List <SpatialObject>();
List <SpatialObject> updatedRows = new List <SpatialObject>();
List <SpatialObject> deletedRows = new List <SpatialObject>();
List <SpatialObject> currentRows = new List <SpatialObject>();
using (OdbcConnection connection = new OdbcConnection(this.ConnectionString))
{
using (OdbcCommand command = new OdbcCommand(this.FeatureLayer.SelectSQL, connection))
{
try
{
connection.Open();
using (OdbcDataReader dr = command.ExecuteReader())
{
if (dr.HasRows)
{
int count = 0;
while (dr.Read())
{
count++;
SpatialObject obj = new SpatialObject();
obj.uniqueId = ((int)this.UpdateMode == 4) ? Convert.ToString(dr.GetValue(0)) : count.ToString();
obj.x = ((int)this.UpdateMode < 3) ? Convert.ToDecimal(dr.GetValue(1)) : -1;
obj.y = ((int)this.UpdateMode < 3) ? Convert.ToDecimal(dr.GetValue(2)) : -1;
obj.fieldValues = new FieldValueList();
for (int i = 0; i < dr.FieldCount; i++)
{
obj.fieldValues.Add(dr.GetName(i), dr.GetFieldType(i), dr.GetValue(i));
}
currentRows.Add(obj);
}
}
if (isLoaded && loadedRows != null)
{
currentRows.ForEach(current =>
{
SpatialObject loaded = loadedRows.FirstOrDefault(existed => existed.uniqueId == current.uniqueId);
if (loaded == null)
{
addedRows.Add(current);
loadedRows.Add(current);
}
else if (!loaded.Equals(current, this.UpdateMode, true))
{
updatedRows.Add(loaded);
}
});
deletedRows = loadedRows.Where(existed => !currentRows.Any(current => existed.uniqueId == current.uniqueId)).ToList();
deletedRows.ForEach(item => loadedRows.Remove(item));
isDone = ApplyEdits(addedRows, updatedRows, deletedRows);
}
else
{
this.isLoaded = true;
loadedRows = currentRows.ToList();
isDone = ApplyEdits(currentRows, null, null);
}
}
}
catch (Exception ex)
{
isDone = true;
isErrorLogged = true;
if (logCallback != null)
{
logCallback(string.Format("Query {0} Table Error - {1}", this.FeatureLayer.Name, ex.Message), EventLogEntryType.Error);
}
}
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
}
return(isDone);
}
//Laws Lu,2005/10/28,修改 缓解性能问题,改用Reader
public override DataSet Query(string commandText, string[] parameters, Type[] parameterTypes, object[] parameterValues)
{
OpenConnection();
//OleDbDataAdapter dataAdapter = (OleDbDataAdapter)this.GetDbDataAdapter();
using (OdbcCommand command = (OdbcCommand)this.Connection.CreateCommand())
{
command.CommandText = this.changeParameterPerfix(commandText);
for (int i = 0; i < parameters.Length; i++)
{
command.Parameters.Add(parameters[i], CSharpType2OdbcType(parameterTypes[i])).Value = parameterValues[i];
// dataAdapter.SelectCommand.CommandText = ChangeParameterPerfix(dataAdapter.SelectCommand.CommandText, parameters[i]);
}
if (this.Transaction != null)
{
command.Transaction = (OdbcTransaction)this.Transaction;
}
DataSet dataSet = new DataSet();
DateTime dtStart = new DateTime();
OdbcDataReader reader = null;
try
{
//修改 在Debug模式下不允许Log日志文件
#if DEBUG
dtStart = DateTime.Now;
Log.Info("************StartDateTime:" + dtStart.ToString() + "," + dtStart.Millisecond);
Log.Info(" Parameter SQL:" + this.spellCommandText(command.CommandText, parameterValues));
#endif
reader = command.ExecuteReader();
//command.
#if DEBUG
DateTime dtEnd = DateTime.Now;
TimeSpan ts = dtEnd - dtStart;
Log.Info("************EndDateTime:" + dtEnd.ToString() + "," + dtEnd.Millisecond + "*********"
+ "Cost: " + ts.Seconds + ":" + ts.Milliseconds);
#endif
DataTable dt = new DataTable();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (!dt.Columns.Contains(reader.GetName(i)))
{
DataColumn dc = new DataColumn(reader.GetName(i), reader.GetFieldType(i));
dt.Columns.Add(dc);
}
}
DataRow dr = dt.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetValue(i) is String)
{
string dbValue = reader.GetValue(i).ToString();
dbValue = dbValue.Replace("\0", "");
dbValue = dbValue.Replace("\r\n", "");
dbValue = dbValue.Replace("\r", "");
dbValue = dbValue.Replace("\n", "");
dr[reader.GetName(i)] = dbValue;
}
else
{
dr[reader.GetName(i)] = reader.GetValue(i);
}
}
dt.Rows.Add(dr);
}
reader.Close();
dataSet.Tables.Add(dt);
//dataSet.Tables.Add(dt);
//dataAdapter.Fill(dataSet);
}
catch (Exception e)
{
reader.Close();
//added by leon.li @20130311
Log.Error(e.StackTrace);
//end added
Log.Error(e.Message + " Parameter SQL:" + this.spellCommandText(command.CommandText, parameterValues));
#if DEBUG
DateTime dtEnd = DateTime.Now;
TimeSpan ts = dtEnd - dtStart;
Log.Info("************EndDateTime:" + dtEnd.ToString() + "," + dtEnd.Millisecond + "*********"
+ "Cost: " + ts.Seconds + ":" + ts.Milliseconds);
#endif
ExceptionManager.Raise(this.GetType(), "$Error_Command_Execute", e);
}
finally
{
if (this.Transaction == null)
{
CloseConnection();
}
}
return(dataSet);
}
}
