public SqliteWrapper(string directory, string databaseName)
{
this.dataBaseName = databaseName;
this.dataBasePath = BuildDataBasePath(directory, databaseName);
dbConnection = GetDatabase();
dbConnection.Open();
}
protected override void InitConnection(System.Data.Common.DbConnection conn)
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
dbName = conn.Database;
}
public void OpenConnectionTest()
{
SurgeryModelContainer smc = new SurgeryModelContainer();
IDBModels dbm = new DBModels(smc);
System.Data.Common.DbConnection dbcon = smc.Database.Connection;
dbcon.Open();
Assert.AreEqual(smc.Database.Connection.State, ConnectionState.Open);
}
/// <summary>
/// 登陆
/// </summary>
/// <returns></returns>
public bool Login()
{
if (Conn == null)
{
return(false);
}
bool result = false;
try
{
Conn.Open();
result = (Conn.State == ConnectionState.Open);
}
catch (Exception e)
{
Error.Add(new string[] { "连接字符" }, new string[] { Conn.ConnectionString });
Error.Add(e);
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="pFileString"></param>
/// <param name="pTableName"></param>
/// <param name="pIsConnectionString"></param>
/// <returns></returns>
public override bool CheckDatabaseExistance(string pFileString, string pTableName, bool pIsConnectionString = false)
{
if (pIsConnectionString)
{
System.Data.Common.DbConnection Conn = null;
//System.Data.Common.DbDataAdapter Adapter = null;
System.Data.DataTable DataTable = new System.Data.DataTable();
bool result = false;
string connString = pFileString;
string[] restrictions = new string[] { null, null, pTableName };
if (DataSource != null)
{
IDbDriver driver = this;
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
try
{
Conn.Open();
DataTable = Conn.GetSchema("Tables", restrictions);
if (DataTable.Rows.Count == 0)
{
//Table does not exist
result = false;
}
else
{
//Table exists
result = true;
}
}
catch (Exception ex)
{
// do nothing
}
finally
{
Conn.Close();
}
}
return(result);
}
else
{
return(System.IO.File.Exists(pFileString));
}
}
} // End Property ConnectionStringWithoutPassword
protected System.Data.Common.DbConnection GetConnection(bool opened)
{
System.Data.Common.DbConnection cn = this.m_factory.CreateConnection();
cn.ConnectionString = this.ConnectionString;
if (opened && cn.State != System.Data.ConnectionState.Open)
{
cn.Open();
}
return(cn);
} // End Function GetConnection
/// <summary>
/// Insert Data
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select Statement</param>
/// <param name="pDataRows">DataRows</param>
/// <returns>bool</returns>
public static bool InsertData(string pFileString, string pSelectSQL, System.Data.DataRow[] pDataRows)
{
bool result = false;
System.Data.Common.DbConnection Conn = null;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
System.Data.Common.DbCommand cmd = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
Adapter = driver.GetDbAdapter(pSelectSQL);
Adapter.FillSchema(dataSet, SchemaType.Source);
Adapter.Fill(Temp);
builder = driver.GetDbCommandBuilder(Adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
try
{
Conn.Open();
//cmd = builder.GetInsertCommand(true);
cmd = Conn.CreateCommand();
cmd.CommandTimeout = 1500;
//builder.GetInsertCommand((true);
foreach (DataRow R in pDataRows)
{
//string SQL = GetInsertSQL(pSelectSQL.Substring(pSelectSQL.LastIndexOf (" From ") + 6, pSelectSQL.Length - pSelectSQL.LastIndexOf(" From ") - 6),R);
string SQL = GetInsertSQL(R.Table.TableName, R);
cmd.CommandText = SQL;
cmd.ExecuteNonQuery();
}
}
finally
{
Conn.Close();
}
}
result = true;
return(result);
}
/// <summary>
/// Gets metadata data set from connection string
/// </summary>
/// <param name="connectionString">The connection</param>
/// <returns>The metadata data set</returns>
DataSet IDataSetFactory.GetData(string connectionString)
{
IDataSetFactory f = this;
System.Data.Common.DbConnection connection = f.Connection;
connection.ConnectionString = connectionString;
DataSet s = new DataSet();
connection.Open();
DataTable table = connection.GetSchema("Columns");
s.Tables.Add(table);
return(s);
}
/// <summary>
/// 测试数据库能否连接成功
/// </summary>
/// <returns></returns>
static bool TestDbConnect()
{
try
{
SqlServerExecute sqlExe = new SqlServerExecute();
System.Data.Common.DbConnection conn = SqlServerExecute.CreateConnection();
conn.Open();
return(true);
}
catch
{
return(false);
}
}
public virtual DataTable GetSchema()
{
using (System.Data.Common.DbConnection conn = (System.Data.Common.DbConnection)_dialect.CreateConnection(_ConnectionString))
{
conn.Open();
try
{
return(conn.GetSchema());
}
finally
{
conn.Close();
}
}
}
public void Load()
{
var locale = PlayerPrefs.GetString("locale", "");
if (string.IsNullOrEmpty(locale))
{
InitStorage();
}
Config = Ch.Config.Parser.ParseFrom(PlayerPrefs.GetString("config").HexToByteArray());
GameData = Ch.GameData.Parser.ParseFrom(PlayerPrefs.GetString("gamedata").HexToByteArray());
LocalDbm = new SqliteConnection("Data Source=nch.db");
LocalDbm.Open();
CreateTable(SCHEMA_VERSION_TABLE, "name STRING NOT NULL PRIMARY KEY, gen INTEGER NOT NULL");
}
public static void InsertList <T>(System.Data.IDbCommand cmd
, System.Collections.Generic.IEnumerable <T> listToInsert
, callbackAddData_t <T> addDataCallback)
{
using (System.Data.Common.DbConnection conn = GetConnection())
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
using (System.Data.Common.DbTransaction transact = conn.BeginTransaction())
{
cmd.Transaction = transact;
try
{
foreach (T thisItem in listToInsert)
{
addDataCallback(cmd, thisItem);
if (cmd.ExecuteNonQuery() != 1)
{
//'handled as needed,
//' but this snippet will throw an exception to force a rollback
throw new System.InvalidProgramException();
}
} // Next thisObject
transact.Commit();
} // End Try
catch (System.Exception)
{
transact.Rollback();
throw;
} // End Catch
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
} // End Finally
} // End Using transact
} // End Using conn
} // End Sub
bool TestConnection(System.Data.Common.DbConnection connection)
{
try
{
connection.Open();
}
catch
{
throw;
}
finally
{
connection.Close();
}
return(true);
}
public static System.Data.Common.DbDataReader GetDataReader(System.Data.Common.DbCommand cmd)
{
System.Data.Common.DbDataReader dr = null;
System.Data.Common.DbConnection con = GetConnection();
cmd.Connection = con;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection);
return(dr);
}
/// <summary>
///
/// </summary>
/// <param name="pFileString"></param>
/// <param name="pTableName"></param>
/// <param name="pIsConnectionString"></param>
/// <returns></returns>
public override bool CheckDatabaseExistance(string pFileString, string pTableName, bool pIsConnectionString = false)
{
System.Data.Common.DbConnection Conn = null;
//System.Data.Common.DbDataAdapter Adapter = null;
System.Data.DataTable DataTable = new System.Data.DataTable();
bool result = false;
string connString = pFileString;
string[] restrictions = new string[] { null, null, pTableName };
if (DataSource != null)
{
IDbDriver driver = this;
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
try
{
Conn.Open();
Conn.ChangeDatabase("master");
System.Data.Common.DbCommand cmd = Conn.CreateCommand();
cmd.CommandText = "SELECT database_id FROM sys.databases WHERE Name = @name";
System.Data.Common.DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = "@name";
parameter.Value = driver.DbName;
cmd.Parameters.Add(parameter);
object ret = cmd.ExecuteScalar();
if (ret != null)
{
int databaseID = (int)ret;
result = databaseID > 0;
}
}
catch (Exception ex)
{
// do nothing
}
finally
{
Conn.Close();
}
}
return(result);
}
public virtual DataTable GetSchema(string collectionName, params string[] restrictionValues)
{
using (System.Data.Common.DbConnection conn = (System.Data.Common.DbConnection)_dialect.CreateConnection(_ConnectionString))
{
conn.Open();
try
{
return(restrictionValues != null && restrictionValues.Length > 0 ?
conn.GetSchema(collectionName, restrictionValues) :
conn.GetSchema(collectionName));
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// Update Data
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select Statement</param>
/// <param name="pDataReader">DataReader</param>
/// <returns>bool</returns>
public static bool UpdateData(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader)
{
bool result = false;
System.Data.Common.DbConnection Conn = null;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
System.Data.Common.DbCommand cmd = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
Adapter = driver.GetDbAdapter(pSelectSQL);
Adapter.FillSchema(dataSet, SchemaType.Source);
Adapter.Fill(Temp);
builder = driver.GetDbCommandBuilder(Adapter);
}
try
{
Conn.Open();
cmd = builder.GetInsertCommand(true);
cmd.CommandTimeout = 1500;
while (pDataReader.Read())
{
foreach (System.Data.Common.DbParameter param in cmd.Parameters)
{
param.Value = pDataReader[param.SourceColumn];
}
cmd.ExecuteNonQuery();
}
}
finally
{
Conn.Close();
}
result = true;
return(result);
}
private void Frm_Main_Load(object sender, EventArgs e)
{
//从配置文件中取连接字符串
string conStr = "Data Source=WIN-GI7E47AND9R\\LS;Database=db_TomeTwo;UID=sa;Pwd=hbyt2008!@#;";
DataClassesDataContext dc = new DataClassesDataContext(conStr); //创建数据上下文实例
System.Data.Common.DbConnection con = dc.Connection; //获取其连接
con.Open(); //打开连接
//显示数据库连接的信息
label1.Text = "连接字符串：" + con.ConnectionString + "\n";
label1.Text += "连接超时时间：" + con.ConnectionTimeout.ToString() + "\n";
label1.Text += "数据库名：" + con.Database + "\n";
label1.Text += "数据源：" + con.DataSource + "\n";
label1.Text += "服务器版本：" + con.ServerVersion + "\n";
label1.Text += "状态：" + con.State.ToString() + "\n";
label1.Text += "执行命令的最大时间：" + dc.CommandTimeout.ToString() + "\n";
label1.Text += "是否延时加载：" + dc.DeferredLoadingEnabled.ToString();
}
private static bool CheckDB(object obj)
{
System.Data.Common.DbConnection conn_inst = (System.Data.Common.DbConnection)obj;
try
{
conn_inst.Open();
conn_inst.Close();
return(true);
}
catch
{
return(false);
}
finally
{
conn_inst.Close();
}
}
private void GetView(MyBlogCore.SqlFactory factory, string viewPath)
{
string query = @"
SELECT Content, LastModified FROM Views WHERE Location = @Path;
UPDATE Views SET LastRequested = GetUtcDate() WHERE Location = @Path;
";
try
{
using (System.Data.Common.DbConnection cnn = factory.Connection)
{
using (System.Data.Common.DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
cmd.AddWithValue("@Path", viewPath);
if (cnn.State != System.Data.ConnectionState.Open)
{
cnn.Open();
}
using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
{
_exists = reader.HasRows;
if (_exists)
{
reader.Read();
_viewContent = System.Text.Encoding.UTF8.GetBytes(reader["Content"].ToString());
_lastModified = System.Convert.ToDateTime(reader["LastModified"]);
} // End if (_exists)
} // End Using reader
} // End Using cmd
if (cnn.State != System.Data.ConnectionState.Closed)
{
cnn.Close();
}
} // End Using cnn
}
catch (System.Exception ex)
{
// if something went wrong, Exists will be false
}
}
public async Task <ActionResult> Post(string layer_name)
{
System.Data.Common.DbConnection conn = _context.Database.GetDbConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//cmd.CommandText = "dbo.add_feature_2";
cmd.CommandText = "dbo.add_feature_3";
cmd.Connection = (SqlConnection)conn;
string json = "";
using (StreamReader reader = new StreamReader(Request.Body, Encoding.UTF8))
{
json = await reader.ReadToEndAsync();
}
//cmd.Parameters.AddWithValue("@table_name", layer_name);
cmd.Parameters.AddWithValue("@layer_name", layer_name);
cmd.Parameters.AddWithValue("@json", json);
cmd.Parameters.Add("@fid", System.Data.SqlDbType.Int);
cmd.Parameters["@fid"].Direction = System.Data.ParameterDirection.Output;
int fid;
try
{
conn.Open();
cmd.ExecuteNonQuery();
fid = Convert.ToInt32(cmd.Parameters["@fid"].Value);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(Content(fid.ToString())); // needs to be new qgs_fid
}
private static System.Data.Common.DbConnection GetOpenedDbConnectionFromDataSource(DataSource ds)
{
string providerInvariantName = ds.ManagedProvider;
if (string.IsNullOrEmpty(providerInvariantName))
{
providerInvariantName = "System.Data.OleDb";
}
System.Data.Common.DbConnection conn = System.Data.Common.DbProviderFactories.GetFactory(providerInvariantName).CreateConnection();
string sConnectionString = String.Empty;
bool bUsesSQLSecurity = false;
foreach (string part in ds.ConnectionString.Split(';'))
{
//we can't retrieve the password, so remove the User ID and make it a trusted connection
if (!part.ToLower().StartsWith("user id=") && !part.ToLower().StartsWith("uid="))
{
sConnectionString += part + ";";
bUsesSQLSecurity = true;
}
else
{
sConnectionString += "Trusted_Connection=Yes;";
}
}
conn.ConnectionString = sConnectionString;
try
{
conn.Open();
}
catch (Exception ex)
{
if (bUsesSQLSecurity)
{
throw new Exception("Could not open connection to data source " + ds.Name + ". Because the password for a SQL security connection cannot be retreived from SSAS, they are not supported. Register this assembly with impersonation settings which have access to connect to the data source using Integrated Security. Exception was: " + ex.Message);
}
else
{
throw new Exception("Could not open connection to data source " + ds.Name + ". Register this assembly with impersonation settings which have access to connect to the data source using Integrated Security. Exception was: " + ex.Message);
}
}
return(conn);
}
private object ExecuteScalarScript(DBDatabase db, DBScript script, bool supportsMultipleStatements)
{
if (supportsMultipleStatements)
{
return(db.ExecuteScalar(script));
}
else
{
object value = null;
using (System.Data.Common.DbConnection con = db.CreateConnection())
{
con.Open();
foreach (DBQuery q in script)
{
value = db.ExecuteScalar(con, q);
}
}
return(value);
}
}
public static List <SQLTableName> GetSQLList(string list)
{
Kit.CheckDataBase();
List <SQLTableName> lsttab = null;
if (Keel.DB.Common.NowDataBase.GetType() == typeof(Keel.DB.SQLServer))
{
DBHelper <SQLTableName> dbt = new DBHelper <SQLTableName>();
lsttab = dbt.GetDataViewForObjectList(list);
}
else if (Keel.DB.Common.NowDataBase.GetType() == typeof(Keel.DB.MSAccess))
{
lsttab = new List <SQLTableName>();
KeelKit.Core.DBInformer dbi = new DBInformer(Kit.SlnKeel.ConnectString);
Hashtable dtm = list != "ViewInfo" ? dbi.GetUserTables() : dbi.GetUserViews();
foreach (DictionaryEntry tb in dtm)
{
SQLTableName st = new SQLTableName();
st.name = tb.Key.ToString();
lsttab.Add(st);
}
}
else
{
lsttab = new List <SQLTableName>();
System.Data.Common.DbConnection dbc = Keel.DB.Common.NowDataBase.GetProviderFactory().CreateConnection();
dbc.ConnectionString = Keel.DB.Common.NowDataBase.ConnectString;
dbc.Open();
DataTable dt = dbc.GetSchema(list != "ViewInfo" ? "Tables" : "Views");
for (int i = 0; i < dt.Rows.Count; i++)
{
lsttab.Add(new SQLTableName()
{
name = (string)dt.Rows[i]["TABLE_NAME"]
});
}
dbc.Close();
}
return(lsttab);
}
public ConnectionServerStateEventArgs(System.Data.Common.DbConnection connection)
{
if (connection != null)
{
if (connection.State == System.Data.ConnectionState.Open)
{
connectionMessage = "Connection Status: OPEN";
statusConnection = ConnectionServerStateEnum.Open;
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
try
{
connection.Open();
statusConnection = ConnectionServerStateEnum.Open;
connectionMessage = "Connection Status Changed to: CLOSED";
}
catch (Exception e)
{
connection.Close();
statusConnection = ConnectionServerStateEnum.Closed;
throw new Exception(e.Message);
}
}
else
{
statusConnection = ConnectionServerStateEnum.Unknown;
connectionMessage = "Connection Status: UNKNOWN";
}
}
else
{
connection.Close();
statusConnection = ConnectionServerStateEnum.Closed;
connectionMessage = "Connection Status: CLOSED.";
}
}
public async Task <ActionResult> Put(string layer_name)
{
System.Data.Common.DbConnection conn = _context.Database.GetDbConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//cmd.CommandText = "dbo.update_feature_2";
cmd.CommandText = "dbo.update_feature_3";
cmd.Connection = (SqlConnection)conn;
string json = "";
using (StreamReader reader = new StreamReader(Request.Body, Encoding.UTF8))
{
json = await reader.ReadToEndAsync();
}
//cmd.Parameters.AddWithValue("@table_name", layer_name);
cmd.Parameters.AddWithValue("@json", json);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(Content("Success"));
}
}//Execute(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <returns>返回受影响行数</returns>
static public int ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbTransaction tran)
{
if (Conn == null)
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
return(0);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
cmd.Transaction = tran;
cmd.CommandText = sql;
try
{
var count = cmd.ExecuteNonQuery();
cmd.Dispose();
return(count);
}
catch (Exception ex)
{
cmd.Dispose();
if (ex.Message.Contains("死锁"))
{
WriteLog(ex.Message + " 再做列锁循环！ExecuteWithTrans");
System.Threading.Thread.Sleep(200);
return(Execute(sql, Conn));
}
else
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "/nsql=" + sql);
return(0);
}
}
}//Execute(string sql)
} // End Function ExecuteScalar(strSQL)
public virtual void ExecuteReader(System.Data.IDbCommand cmd, DataReaderCallback_t callback)
{
using (System.Data.Common.DbConnection con = this.Connection)
{
cmd.Connection = con;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbDataReader idr = (System.Data.Common.DbDataReader)cmd.ExecuteReader(
System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection
))
{
callback(idr);
}
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using con
}
} // End Function ExecuteScalar(cmd)
public static System.Data.DataTable GetDataTable(System.Data.IDbCommand cmd)
{
System.Data.DataTable dt = new System.Data.DataTable();
using (System.Data.Common.DbConnection conn = GetConnection())
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
try
{
using (var da = fact.CreateDataAdapter())
{
da.SelectCommand = (System.Data.Common.DbCommand)cmd;
da.Fill(dt);
}
} // End Try
catch (System.Exception)
{
throw;
} // End Catch
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
} // End Finally
} // End Using conn
return(dt);
}
}//Execute(string sql,System.Data.IDataParameter[] param)
/// <summary>
/// 执行一个事务
/// </summary>
/// <param name="sqls">Sql语句组</param>
/// <returns>成功时返回true</returns>
static public bool ExecuteTrans(string[] sqls, System.Data.Common.DbConnection Conn)
{
System.Data.IDbTransaction myTrans;
if (Conn == null)
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteTrans(string[] sqls):连接对象为空!");
return(false);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
myTrans = Conn.BeginTransaction();
cmd.Transaction = myTrans;
try
{
foreach (string sql in sqls)
{
if (sql != null)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
myTrans.Commit();
}
catch (Exception ex)
{
myTrans.Rollback();
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteTrans(string[] sqls):" + ex.Message);
return(false);
}
return(true);
}//Execute(string sql)