C#コードCRUD操作MySQLデータベース
まずプログラムcsコードは以下の通りです.
product.txtデータは以下の通りです.
b2cshop.sql足どり内容:
class Program
{
static void Main(string[] args)
{
//
CrudDemo app = new CrudDemo("localhost", "b2cshop", "root", "123456");
//
app.TestConnection();
app.CreateData();
app.ReadData();
app.UpdateData(7);
app.ReadData();
app.DeleteData(8);
app.ReadData();
app.BulkData();
}
} public class CrudDemo
{
private string connString;
//
public CrudDemo(string server,string database,string uid,string password)
{
connString = string.Format("server={0};database={1};user={2};password={3}",
server, database, uid, password);
}
//
public void TestConnection()
{
try
{
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
Console.WriteLine("Connected");
conn.Close();
Console.WriteLine("Closed");
}catch(MySqlException e)
{
Console.WriteLine("Error: " + e.Message);
}
}
//
public void CreateData()
{
try
{
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
Console.WriteLine("Connected");
string query = "insert into product(name,price,created) values(@name,@price,@created)";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.Add("@name", MySqlDbType.VarChar, 45);
cmd.Parameters.Add("@price", MySqlDbType.Float);
cmd.Parameters.Add("@created", MySqlDbType.DateTime);
Console.Write("Inserting 10 data....");
DateTime now = DateTime.Now;
for (int i = 1; i <= 10;i++ )
{
cmd.Parameters[0].Value = "product-" + i;
cmd.Parameters[1].Value = 0.26 * i;
cmd.Parameters[2].Value = now;
cmd.ExecuteNonQuery();
}
Console.WriteLine("Done");
conn.Close();
Console.WriteLine("Closed");
}catch(MySqlException e)
{
Console.WriteLine("Error: " + e.Message);
}
}
//
public void BulkData()
{
try
{
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
Console.WriteLine("Connected");
MySqlBulkLoader bulk = new MySqlBulkLoader(conn);
bulk.TableName = "product";
bulk.FieldTerminator = "\t";
bulk.LineTerminator = "
";
bulk.FileName = "D:/product.txt";
bulk.NumberOfLinesToSkip = 0;
bulk.Columns.Add("name");
bulk.Columns.Add("price");
bulk.Columns.Add("created");
Console.Write("Inserting bulk data....");
int count = bulk.Load();
Console.WriteLine("Done-" + count.ToString());
conn.Close();
Console.WriteLine("Closed");
}
catch (MySqlException e)
{
Console.WriteLine("Error: " + e.Message);
}
}
//
public void ReadData()
{
try
{
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
Console.WriteLine("Connected");
string query = "select idproduct,name,price,created from product";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader rd = cmd.ExecuteReader();
while(rd.Read())
{
Console.WriteLine("Id: " + rd["idproduct"].ToString());
Console.WriteLine("Name: " + rd["name"].ToString());
Console.WriteLine("Price: " + rd["price"].ToString());
Console.WriteLine("Created: " + rd["created"].ToString());
Console.WriteLine("---------------------------");
}
rd.Close();
conn.Close();
Console.WriteLine("Closed");
}catch(MySqlException e)
{
Console.WriteLine("Error: " + e.Message);
}
}
//
public void UpdateData(int id)
{
try
{
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
Console.WriteLine("Connected");
string query = "update product set name=@name,price=@price where idproduct=@id";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.Add("@name", MySqlDbType.VarChar, 45);
cmd.Parameters.Add("@price", MySqlDbType.Float);
cmd.Parameters.Add("@id", MySqlDbType.Int32);
Console.Write("Updating data....");
cmd.Parameters[0].Value = "product-update";
cmd.Parameters[1].Value = 0.75;
cmd.Parameters[2].Value = id;
cmd.ExecuteNonQuery();
Console.WriteLine("Done");
conn.Close();
Console.WriteLine("Closed");
}catch(MySqlException e)
{
Console.WriteLine("Error: " + e.Message);
}
}
//
public void DeleteData(int id)
{
try
{
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
Console.WriteLine("Connected");
string query = "delete from product where idproduct=@id";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.Add("@id", MySqlDbType.Int32);
Console.Write("Deleting data....");
cmd.Parameters[0].Value = id;
cmd.ExecuteNonQuery();
Console.WriteLine("Done");
conn.Close();
Console.WriteLine("Closed");
}
catch (MySqlException e)
{
Console.WriteLine("Error: " + e.Message);
}
}
}product.txtデータは以下の通りです.
product10 105 2014-01-09 19:26:30
product11 105 2014-01-09 19:26:30
product12 105 2014-01-09 19:26:30
product13 105 2014-01-09 19:26:30
product14 105 2014-01-09 19:26:30b2cshop.sql足どり内容:
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50617
Source Host : localhost:3306
Source Database : b2cshop
Target Server Type : MYSQL
Target Server Version : 50617
File Encoding : 65001
Date: 2016-07-31 22:06:36
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `product`
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`idproduct` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
`price` float DEFAULT NULL,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`idproduct`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1', 'product-1', '0.26', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('2', 'product-2', '0.52', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('3', 'product-3', '0.78', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('4', 'product-4', '1.04', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('5', 'product-5', '1.3', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('6', 'product-6', '1.56', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('7', 'product-update', '0.75', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('9', 'product-9', '2.34', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('10', 'product-10', '2.6', '2016-07-30 00:36:02');
INSERT INTO `product` VALUES ('11', 'product10', '105', '2014-01-09 19:26:30');
INSERT INTO `product` VALUES ('12', 'product11', '105', '2014-01-09 19:26:30');
INSERT INTO `product` VALUES ('13', 'product12', '105', '2014-01-09 19:26:30');
INSERT INTO `product` VALUES ('14', 'product13', '105', '2014-01-09 19:26:30');
INSERT INTO `product` VALUES ('15', 'product14', '105', '2014-01-09 19:26:30');