public string ShowMatInText(string strCondition)
{
string[] strArr = strCondition.Split(':');
System.Collections.ArrayList mass = new System.Collections.ArrayList();
System.Collections.ArrayList key1 = new System.Collections.ArrayList();
System.Collections.ArrayList key2 = new System.Collections.ArrayList();
System.Collections.ArrayList key3 = new System.Collections.ArrayList();
using (SqlText sql = new SqlText(String.Format("select m.MassPartsIDFlag, m.ExpansionKey1, m.ExpansionKey2, m.ExpansionKey3 from MaterialNumber as m join Customer as c on m.ShiptoParty = c.Code where c.PlantCode = '{1}' and shiptoParty like '{0}%' and m.customerItemNumber = '{2}'", strArr[0], strArr[1], strArr[2])))
{
System.Data.Common.DbDataReader readerSender = sql.ExecuteReader();
while (readerSender.Read())
{
mass.Add(readerSender["MassPartsIDFlag"].ToString());
key1.Add(readerSender["ExpansionKey1"].ToString());
key2.Add(readerSender["ExpansionKey2"].ToString());
key3.Add(readerSender["ExpansionKey3"].ToString());
}
readerSender.Close();
readerSender.Dispose();
}
MN[] l = new MN[mass.Count];
for (int i = 0; i <= mass.Count - 1; i++)
{
l[i] = new MN();
l[i].MassPartsIDFlag = mass[i].ToString();
l[i].ExpansionKey1 = key1[i].ToString();
l[i].ExpansionKey2 = key2[i].ToString();
l[i].ExpansionKey3 = key3[i].ToString();
}
return(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(l));
}
public async Task <ITEM> LoadItemAsync(int?ID_ITEM)
{
ITEM temp = new ITEM();
var con = _db.Database.GetDbConnection();
con.Open();
using (var command = con.CreateCommand())
{
// this does a few things. First it returns the given item id, the item name, and the current rating aggregate across all ratings for it.
// additionally it finds the next seqential ID, and the previous seqential ID. If none found then 0.
// this is important for the 'next/previous' buttons.
string q = @" SELECT top(1) b.ID
,b.TITLE
,isnull(C.RATING, 0) as RATING
,( SELECT isnull(min(a.id), 0)
FROM [test].[dbo].[ITEM] a
where a.id > b.ID) as [NEXT]
,( SELECT isnull(max(a.id), 0)
FROM [test].[dbo].[ITEM] a
where a.id < b.ID) as [PREVIOUS]
FROM [test].[dbo].[ITEM] b
left JOIN (
SELECT y.[ID]
,AVG(CAST(z.RATING AS FLOAT)) as RATING
FROM [dbo].[ITEM] y
JOIN [dbo].[ITEM_RATING] z
ON y.ID = z.ID_ITEM
GROUP BY y.ID
) c ON B.ID = c.ID ";
if (ID_ITEM != null)
{
// IF there is a specific ID (say from someone clicking 'next' then load that specific item's info, next, previous, name, aggregate rating, etc.
q += " WHERE b.ID = @ID_ITEM ";
DbParameter tempParameter = command.CreateParameter();
tempParameter.ParameterName = "@ID_ITEM";
tempParameter.Value = ID_ITEM;
command.Parameters.Add(tempParameter);
}
command.CommandText = q;
System.Data.Common.DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
reader.Read();
temp = new ITEM();
temp.ID = (int)reader["ID"];
temp.TITLE = (string)reader["TITLE"];
temp.RATING = Convert.ToDecimal(reader["RATING"]);
temp.NEXT = (int)reader["NEXT"];
temp.PREVIOUS = (int)reader["PREVIOUS"];
}
reader.Dispose();
}
return(temp);
}
public T[] List <T>(string Sql, System.Data.Common.DbTransaction transaction = null)
{
DbBase.LastSQL = Sql;
System.Data.Common.DbCommand cmd = null;
System.Data.Common.DbDataReader dataReader = null;
T[] result = null;
try
{
if (transaction == null && DbConnection.State != ConnectionState.Open)
{
DbConnection.Open();
}
cmd = DbBase.DbConnection.CreateCommand();
cmd.CommandText = Sql;
if (transaction != null)
{
cmd.Transaction = transaction;
}
dataReader = cmd.ExecuteReader();
List <string> columns = GetDataReaderColumns(dataReader);
result = List <T>(dataReader, columns);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
dataReader = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (transaction == null)
{
DbConnection.Close();
}
}
if (result == null)
{
return(new T[] { });
}
return(result);
}
public static string GetLookupValue(string sDisplayField, string sLinkField, string sTable, string sValue, TypeCode TType, bool isMultiline)
{
string sGetLookupValue = "";
string sqlSelect = "SELECT " + sDisplayField + " FROM " + sTable + " WHERE ";
try
{
QueryCommand cmd = new QueryCommand(sqlSelect);
if (isMultiline)
{
int i = 0;
foreach (string s in sValue.Split(','))
{
string paramName = "param" + i.ToString();
cmd.CommandSql += sLinkField + "=@" + paramName + " Or ";
cmd.AddParameter(paramName, s);
i += 1;
}
if (cmd.CommandSql.Length > 2)
{
cmd.CommandSql = cmd.CommandSql.Remove(cmd.CommandSql.Length - 3);
}
}
else
{
cmd.CommandSql += sLinkField + "= @LinkField";
cmd.AddParameter("LinkField", sValue);
}
System.Data.Common.DbDataReader dbDr = (System.Data.Common.DbDataReader)DataService.GetReader(cmd);
if (dbDr.HasRows)
{
if (isMultiline)
{
while (dbDr.Read())
{
sGetLookupValue += dbDr[0].ToString() + ",";
}
sGetLookupValue = sGetLookupValue.Trim(",".ToCharArray());
}
else if (dbDr.Read())
{
sGetLookupValue = dbDr[0].ToString();
}
}
dbDr.Dispose();
}
finally
{
}
return(sGetLookupValue);
}
//Get a list of all the ratings for a given ID.
public async Task <List <ITEM_RATING> > LoadItemsRatingsAsync(int ID_ITEM)
{
List <ITEM_RATING> temp = new List <ITEM_RATING>();
var con = _db.Database.GetDbConnection();
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (var command = con.CreateCommand())
{
string q = @"SELECT [ID_ITEM]
,[ID_USER]
,b.NAME
,[RATING]
,[DATE_ENTERED]
FROM dbo.ITEM_RATING a WITH(NOLOCK)
join [USER] b WITH(NOLOCK) ON a.ID_USER = b.ID
WHERE [ID_ITEM] = @ID_ITEM
ORDER BY DATE_ENTERED DESC";
command.CommandText = q;
DbParameter tempParameter = command.CreateParameter();
tempParameter.ParameterName = "@ID_ITEM";
tempParameter.Value = ID_ITEM;
command.Parameters.Add(tempParameter);
System.Data.Common.DbDataReader reader = await command.ExecuteReaderAsync();
temp = new List <ITEM_RATING>();
if (reader.HasRows)
{
while (reader.Read())
{
var row = new ITEM_RATING
{
ID_ITEM = (int)reader["ID_ITEM"],
ID_USER = (int)reader["ID_USER"],
RATING = (int)(reader["RATING"]),
DATE_ENTERED = Convert.ToDateTime(reader["DATE_ENTERED"]),
USER_NAME = (string)(reader["NAME"]),
};
temp.Add(row);
}
}
reader.Dispose();
}
return(temp);
}
static int Dispose(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 1);
System.Data.Common.DbDataReader obj = (System.Data.Common.DbDataReader)ToLua.CheckObject(L, 1, typeof(System.Data.Common.DbDataReader));
obj.Dispose();
return(0);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
/// <summary>
/// Execute a command that returns multiple result sets, and access each in turn
/// </summary>
public static async Task <GridReader> QueryMultipleAsync(this IDbConnection cnn, CommandDefinition command)
{
object param = command.Parameters;
Identity identity = new Identity(command.CommandText, command.CommandType, cnn, typeof(GridReader), param?.GetType(), null);
CacheInfo info = GetCacheInfo(identity, param, command.AddToCache);
DbCommand cmd = null;
IDataReader reader = null;
bool wasClosed = cnn.State == ConnectionState.Closed;
try
{
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false);
}
cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader);
reader = await cmd.ExecuteReaderAsync(wasClosed?CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess : CommandBehavior.SequentialAccess, command.CancellationToken).ConfigureAwait(false);
var result = new GridReader(cmd, reader, identity, command.Parameters as DynamicParameters, command.AddToCache, command.CancellationToken);
wasClosed = false; // *if* the connection was closed and we got this far, then we now have a reader
// with the CloseConnection flag, so the reader will deal with the connection; we
// still need something in the "finally" to ensure that broken SQL still results
// in the connection closing itself
return(result);
}
catch
{
if (reader != null)
{
if (!reader.IsClosed)
{
try
{ cmd.Cancel(); }
catch
{ /* don't spoil the existing exception */ }
}
reader.Dispose();
}
cmd?.Dispose();
if (wasClosed)
{
cnn.Close();
}
throw;
}
}
public string cbPlantCode(string strCondition)
{
System.Collections.ArrayList receiverList = new System.Collections.ArrayList();
using (SqlText sql = new SqlText(String.Format("select distinct(PlantCode) from Customer where Code like '{0}%' and PlantCode is not null", strCondition)))
{
System.Data.Common.DbDataReader readerSender = sql.ExecuteReader();
while (readerSender.Read())
{
receiverList.Add(readerSender["PlantCode"].ToString());
}
readerSender.Close();
readerSender.Dispose();
}
MN[] l = new MN[receiverList.Count];
for (int i = 0; i <= receiverList.Count - 1; i++)
{
l[i] = new MN();
l[i].strItem = receiverList[i].ToString();
}
return(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(l));
}
public string cbMatCode(string strCondition)
{
string[] strArr = strCondition.Split(':');
System.Collections.ArrayList receiverList = new System.Collections.ArrayList();
using (SqlText sql = new SqlText(String.Format("select m.customerItemNumber from MaterialNumber as m join Customer as c on m.ShiptoParty = c.Code where c.PlantCode = '{1}' and shiptoParty like '{0}%'", strArr[0], strArr[1])))
{
System.Data.Common.DbDataReader readerSender = sql.ExecuteReader();
while (readerSender.Read())
{
receiverList.Add(readerSender["customerItemNumber"].ToString());
}
readerSender.Close();
readerSender.Dispose();
}
MN[] l = new MN[receiverList.Count];
for (int i = 0; i <= receiverList.Count - 1; i++)
{
l[i] = new MN();
l[i].strItem = receiverList[i].ToString();
}
return(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(l));
}
public static List<object> GetColumnFromDataReader(DbDataReader dr, string columnName)
{
List<object> column = new List<object>();
try
{
while (dr.Read())
{
column.Add(dr[columnName]);
}
}
finally
{
if (dr != null && !dr.IsClosed)
{
dr.Close(); // Data Reader is done (read only, forward only)
dr.Dispose();
}
}
return column;
}
public String ViewTableTxt(String TableName)
{
ESData.GetInst.Open_Conn();
String txt = "TabeName:" + TableName + "\n";
System.Data.Common.DbDataReader dr = ESData.GetInst.Reader(String.Format("select * from {0};", TableName));
for (int i = 0; i < dr.FieldCount; i++)
{
txt += dr.GetName(i) + "\t ";
}
txt += "\n"; //if (dr.HasRows)
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
try{ txt += dr[i].ToString() + "\t "; }
catch { MessageBox.Show(dr[0].ToString()); }
}
txt += "\n";
}
dr.Close();
dr.Dispose();
return(txt);
}
/// <summary>
/// Closes the current connection and disposes of the reader passed in
/// </summary>
/// <param name="reader">The reader object to dispose of (if there is one, else pass 'null')</param>
public void CleanUp(DbDataReader reader)
{
if (_connection != null && _connection.State == ConnectionState.Open && _transaction == null)
_connection.Close();
if (reader != null)
{
if (!reader.IsClosed)
reader.Close();
reader.Dispose();
}
}
/// <summary>
/// Disposes the reader.
/// </summary>
/// <param name="reader"></param>
protected void SafeDispose(DbDataReader reader)
{
if (reader != null)
{
reader.Dispose();
}
}
public static DataTable JoinData(DbDataReader dr1, DbDataReader dr2, KeyValuePair<string, string> relation)
{
// Create the DataTables
DataTable dt1 = new DataTable("Table1");
DataTable dt2 = new DataTable("Table2");
dt1.Load(dr1);
dt2.Load(dr2);
dr1.Close();
dr1.Dispose();
dr2.Close();
dr2.Dispose();
return JoinData(dt1, dt2, relation);
}
/// <summary>
/// Converts a DbDataReader object with one row to a dictionary.
/// Dict Keys are DbDataReader Columns and Values is the Row.
/// Will throw if there is more or less than one result.
/// Note that DbDataReader is read only / forward only, so at then end, the reader object is closed and disposed.
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public static Dictionary<string, object> GetDictFromDataReader(DbDataReader dr)
{
Dictionary<string, object> dict = new Dictionary<string, object>();
try
{
if (!dr.HasRows)
{
throw new Exception("DataReader has no rows. Needs to have one.");
}
dr.Read();
for (int i = 0; i < dr.FieldCount; i++)
{
dict.Add(dr.GetName(i), dr[i]);
}
if (dr.Read())
{
throw new Exception("DataReader has more than one row. Needs to have only one.");
}
}
finally
{
if (dr != null && !dr.IsClosed)
{
dr.Close(); // DataReader is done (read only, forward only)
dr.Dispose();
}
}
return dict;
}
private void button2_Click(object sender, EventArgs e)
{
try {
//////Microsoft.Office.Tools.Excel.Workbook theWorkbook = Microsoft.Office.Tools.Excel.ex .ExcelObj.Workbooks.Open(
////// this.txtXmlPath.Text, 0, true, 5,
////// "", "", true, Microsoft.Office.Tools.Excel.xl.xlWindows, "\t", false, false,
////// 0, true);
//////Excel.Sheets sheets = theWorkbook.Worksheets;
//////Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
//////int i=1;
//////string[] strArray;
//////System.Array myvalues;
//////do {
////// Excel.Range range = worksheet.get_Range("A" + i.ToString(), "O" + i.ToString());
////// myvalues = (System.Array)range.Cells.Value;
////// strArray = ConvertToStringArray(myvalues);
//////} while (strArray[0].Length > 0);
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.txtXlsPath.Text + @";Extended Properties=""Excel 8.0;HDR=YES;""";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
////using (DbCommand command = connection.CreateCommand())
////{
//// command.CommandText = "INSERT INTO [Cities$]
//// (ID, City, State) VALUES(4,\"Tampa\",\"Florida\")";
connection.Open();
//// command.ExecuteNonQuery();
////}
System.Data.Common.DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM [volumes$]";
System.Data.Common.DbDataReader drdr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(drdr);
drdr.Dispose();
cmd.Dispose();
XmlDocument docXml = new XmlDocument();
XmlNode nodeRoot = docXml.AppendChild(docXml.CreateNode(XmlNodeType.Element, "root", docXml.NamespaceURI));
XmlNode nodeVolumes = nodeRoot.AppendChild(docXml.CreateNode(XmlNodeType.Element, "volumes", docXml.NamespaceURI));
XmlNode nodeVolume;
XmlNode nodeColumns = nodeRoot.AppendChild(docXml.CreateNode(XmlNodeType.Element, "columns", docXml.NamespaceURI));
foreach (DataColumn dc in dt.Columns)
{
XmlNode nodeColumn = nodeColumns.AppendChild(docXml.CreateNode(XmlNodeType.Element, "column", docXml.NamespaceURI));
XmlAttribute attFldName = nodeColumn.Attributes.Append((XmlAttribute)docXml.CreateNode(XmlNodeType.Attribute, "FldName", docXml.NamespaceURI));
attFldName.InnerText = dc.ColumnName;
}
foreach (DataRow dr in dt.Rows)
{
nodeVolume = nodeVolumes.SelectSingleNode("./volume[@volume_code = '" + dr["Volume"].ToString() + "']");
if (nodeVolume == null)
{
nodeVolume = nodeVolumes.AppendChild(docXml.CreateNode(XmlNodeType.Element, "volume", docXml.NamespaceURI));
XmlAttribute attVolumeCode = nodeVolume.Attributes.Append((XmlAttribute)docXml.CreateNode(XmlNodeType.Attribute, "volume_code", docXml.NamespaceURI));
attVolumeCode.InnerText = dr["volume"].ToString();
}
XmlNode nodeIssue = nodeVolume.AppendChild(docXml.CreateNode(XmlNodeType.Element, "issue", docXml.NamespaceURI));
foreach (DataColumn dc in dt.Columns)
{
if (string.Compare(dc.ColumnName, "volume", true) != 0)
{
XmlNode nodeFld;
nodeFld = nodeIssue.AppendChild(docXml.CreateNode(XmlNodeType.Element, "Fld", docXml.NamespaceURI));
XmlAttribute attFldName = nodeFld.Attributes.Append((XmlAttribute)(docXml.CreateNode(XmlNodeType.Attribute, "Name", docXml.NamespaceURI)));
attFldName.InnerText = dc.ColumnName;//.Replace(" ", "_");
nodeFld.InnerText = dr[dc].ToString();
}
}
}
string strXmlFilePath = this.txtXlsPath.Text.Substring(0, this.txtXlsPath.Text.LastIndexOf(".xls")) + ".xml";
docXml.Save(strXmlFilePath);
this.txtXmlPath.Text = strXmlFilePath;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}