static void GetIdentityWithDapper(System.Data.OleDb.OleDbConnection cn)
{
// 2回回しても問題なく動作する
for (int i = 0; i < 2; i++)
{
var sql = "INSERT INTO Item(ItemName) VALUES (@ItemName)";
cn.Execute(sql, new { ItemName = "秋映" });
var id = (int)cn.Query("SELECT @@IDENTITY as ID").First().ID;
Console.WriteLine("@@IDENTITY -> " + id.ToString());
var results = cn.Query <Item>("SELECT * FROM Item WHERE ID = @Id", new { @Id = id });
Console.WriteLine(results.First().ID + ":" + results.First().ItemName);
}
}
public static List <ChargeOnByClient> OpenSourseFile(object filePath)
{
var excelapp = new Excel.Application {
Visible = false
};
var excelappworkbook = excelapp.Workbooks.Open((string)filePath,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
var excelsheets = excelappworkbook.Worksheets;
var excelworksheet = (Excel.Worksheet)excelsheets.Item[1];
var wsName = excelworksheet.Name;
excelapp.Quit();
Marshal.FinalReleaseComObject(excelapp);
var oConn = new System.Data.OleDb.OleDbConnection
{
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=" + (string)filePath +
";Extended Properties=\"Excel 12.0;HDR=Yes;\";"
};
var r = oConn.Query <ChargeOnByClient>($"select * from [{wsName}$]").ToList();
return(r);
}
static void SelectWithoutClass(System.Data.OleDb.OleDbConnection cn)
{
var results = cn.Query(SQLSelect, new { ItemName = SelectParameter });
var messages = results.Select(a => a.ID.ToString() + ":" + a.ItemName);
Console.WriteLine(string.Join(Environment.NewLine, messages));
}
static void SelectAll(System.Data.OleDb.OleDbConnection cn)
{
var sql = @"SELECT * FROM Item";
var messages = cn.Query <Item>(sql).Select(a => a.ID.ToString() + ":" + a.ItemName);
Console.WriteLine("------------------------------------");
Console.WriteLine(string.Join(Environment.NewLine, messages));
Console.WriteLine("------------------------------------");
}
static void SelectWithClass(System.Data.OleDb.OleDbConnection cn)
{
// この第二引数にはインテリセンスはきかない
var results = cn.Query <Item>(SQLSelect, new { ItemName = SelectParameter });
// ここでインテリセンスがきく
var messages = results.Select(a => a.ID.ToString() + ":" + a.ItemName);
Console.WriteLine(string.Join(Environment.NewLine, messages));
}
//function that uses Dapper to fetch data from the database
private static List <T> Fetch <T>(string query, out bool fetchSuccessful)
{
try
{
IDbConnection connection = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings[DB_NAME].ConnectionString);
List <T> results = new List <T>();
using (connection)
{
results = connection.Query <T>(query).ToList();
}
fetchSuccessful = true;
return(results);
}
catch
{
fetchSuccessful = false;
return(null);
}
}