public DataSet DataAdapter(CommandType cmdType, string cmdText)
{
Loghelper.WriteLog("SQL语句： " + cmdText);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
DbDataAdapter dda = null;
try
{
EstablishFactoryConnection();
dda = oFactory.CreateDataAdapter();
PrepareCommand(mblTransaction, cmdType, cmdText);
dda.SelectCommand = oCommand;
DataSet ds = new DataSet();
dda.Fill(ds);
return(ds);
}
catch (Exception ex)
{
Loghelper.WriteLog("错误： " + ex.Message.ToString());
//return null;
throw ex;
}
finally
{
if (null != oCommand)
{
oCommand.Dispose();
}
CloseFactoryConnection();
}
}
public DBHelper(ConnectionStringSettings conectionStringSetting)
{
S_CONNECTION = conectionStringSetting.ConnectionString;
S_PROVIDER = conectionStringSetting.ProviderName;
setConnectionType(S_PROVIDER);
oFactory = DbProviderFactories.GetFactory(S_PROVIDER);
mblTransaction = false;
Loghelper.WriteLog("连接参数: " + S_CONNECTION);
}
public int ExecuteNonQuery(CommandType cmdType, string cmdText)
{
try
{
Loghelper.WriteLog("SQL语句： " + cmdText);
EstablishFactoryConnection();
PrepareCommand(false, cmdType, cmdText);
return(oCommand.ExecuteNonQuery());
}
catch (Exception ex)
{
Loghelper.WriteLog("错误： " + ex.Message.ToString());
throw ex;
}
finally
{
if (null != oCommand)
{
oCommand.Dispose();
}
CloseFactoryConnection();
}
}
public void EstablishFactoryConnection()
{
/*
* // This check is not required as it will throw "Invalid Provider Exception" on the contructor itself.
* if (0 == DbProviderFactories.GetFactoryClasses().Select("InvariantName='" + S_PROVIDER + "'").Length)
* throw new Exception("Invalid Provider");
*/
try
{
oConnection = oFactory.CreateConnection();
if (oConnection.State == ConnectionState.Closed)
{
oConnection.ConnectionString = S_CONNECTION;
oConnection.Open();
oConnectionState = ConnectionState.Open;
}
}
catch (Exception e)
{
Loghelper.WriteErrorLog("数据库连接错误", e);
throw new Exception("数据库连接错误");
}
}
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
/// <param name="areaCode"></param>
/// <param name="csId"></param>
/// <returns></returns>
public static List <JObject> DataTableToListJObject(string tableName, DataTable dt, string areaCode, string csId)
{
List <JObject> result = new List <JObject>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
try
{
JObject item = new JObject();
for (int i = 0; i < dr.Table.Columns.Count; i++)
{
Type dataType = dr[dr.Table.Columns[i].ColumnName].GetType();
switch (dataType.Name)
{
case "String":
item[dr.Table.Columns[i].ColumnName] = dr[dr.Table.Columns[i].ColumnName].ToString();
break;
case "Int32":
item[dr.Table.Columns[i].ColumnName] = Int32.Parse(dr[dr.Table.Columns[i].ColumnName].ToString());
break;
case "Double":
item[dr.Table.Columns[i].ColumnName] = Double.Parse(dr[dr.Table.Columns[i].ColumnName].ToString());
break;
case "Float":
item[dr.Table.Columns[i].ColumnName] = float.Parse(dr[dr.Table.Columns[i].ColumnName].ToString());
break;
case "DateTime":
item[dr.Table.Columns[i].ColumnName] = Convert.ToDateTime(dr[dr.Table.Columns[i].ColumnName]);
break;
case "Decimal":
item[dr.Table.Columns[i].ColumnName] = Decimal.Parse(dr[dr.Table.Columns[i].ColumnName].ToString());
break;
case "DBNull":
item[dr.Table.Columns[i].ColumnName] = null;
break;
default:
item[dr.Table.Columns[i].ColumnName] = dr[dr.Table.Columns[i].ColumnName].ToString();
break;
}
}
item["AreaCode"] = areaCode;
item["CsId"] = csId;
switch (tableName)
{
case "YB_DeviceData":
case "PCJC_DeviceData":
break;
default:
if (item.Property("DataID") != null)
{
item.Remove("DataID");
}
break;
}
if (item.Property("CreateUserID") != null)
{
item.Remove("CreateUserID");
}
if (item.Property("CreateTime") != null)
{
item.Remove("CreateTime");
}
if (item.Property("ModifyUserID") != null)
{
item.Remove("ModifyUserID");
}
if (item.Property("ModifyTime") != null)
{
item.Remove("ModifyTime");
}
if (item.Property("DV1") != null)
{
item.Remove("DV1");
}
if (item.Property("DV2") != null)
{
item.Remove("DV2");
}
if (item.Property("DV3") != null)
{
item.Remove("DV3");
}
if (item.Property("Id") != null)
{
item.Remove("Id");
}
result.Add(item);
}
catch (Exception ex)
{
LogRepository lr = new LogRepository();
//日志处理
Loghelper.WriteErrorLog("数据转换失败，此过程发生在读取设备数据后，录入标准库过程中", ex);
lr.AddLogInfo(ex.ToString(), "数据转换失败，此过程发生在读取设备数据后，录入标准库过程中", "数据转换失败，此过程发生在读取设备数据后，录入标准库过程中", "Error");
}
}
}
return(result);
}
