需要在数据库种保存32位的无符号整型数据，如何选择存储类型呢？可选类型如下
1.数值类型 BIGINT是最的类型但只能保存8位
2.用字符VARCHAR可以，但是需要将 大数=>byte[] =>字符 然后再逆向转出（数据=>字符=>byte[] ）。
3.用二进制字符串，大数=>byte[] =>字符 ,出库 数据=>byte[]
2.代码using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySqlConnector;
namespace 二进制保存试验
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("二进制保存试验");
Program program = new Program();
program.main();
Console.ReadKey();
}
public void main() {
test1();
test2();
}
public void test1() {
string connStr = "server=127.0.0.1;port=3306;user=root;password=123456; database=db1;";
MySqlConnection mySqlConnection = new MySqlConnection(connStr);
mySqlConnection.Open();
MySqlCommand mySqlCommand = new MySqlCommand();
mySqlCommand.Connection = mySqlConnection;
byte[] bytes = new byte[2];
bytes[0] = 0b_0000_1000;
bytes[1] = 0b_0000_1000;
bytes.ToString();
string sql = "insert into tb3(v1) values('" + System.Text.Encoding.ASCII.GetString(bytes) + "')";
mySqlCommand.CommandText = sql;
mySqlCommand.ExecuteNonQuery();
sql = "select v1 from tb3";
MySqlCommand mySqlCommand1 = new MySqlCommand(sql, mySqlConnection);
MySqlDataReader mySqlDataReader = mySqlCommand1.ExecuteReader();
if (mySqlDataReader.Read())
{
byte[] byteArray = (byte[])mySqlDataReader[0];
int a = byteArray[0];
int b = byteArray[1];
Console.WriteLine("a:"+a+" b:"+b);
}
mySqlConnection.Close();
}
public void test2()
{
string connStr = "server=127.0.0.1;port=3306;user=root;password=123456; database=db1;";
MySqlConnection mySqlConnection = new MySqlConnection(connStr);
mySqlConnection.Open();
MySqlCommand mySqlCommand = new MySqlCommand();
mySqlCommand.Connection = mySqlConnection;
byte[] bytes = new byte[2];
bytes[0] = 0b_0000_1000;
bytes[1] = 0b_0000_1000;
bytes.ToString();
string sql = "insert into tb4(v1) values('" + System.Text.Encoding.ASCII.GetString(bytes) + "')";
mySqlCommand.CommandText = sql;
mySqlCommand.ExecuteNonQuery();
sql = "select v1 from tb4";
MySqlCommand mySqlCommand1 = new MySqlCommand(sql, mySqlConnection);
MySqlDataReader mySqlDataReader = mySqlCommand1.ExecuteReader();
if (mySqlDataReader.Read())
{
string str = mySqlDataReader[0].ToString();
byte[] byteArray = System.Text.Encoding.Default.GetBytes(str);
int a = byteArray[0];
int b = byteArray[1];
Console.WriteLine("a:" + a + " b:" + b);
}
mySqlConnection.Close();
}
}
}
3.运行结果
附录
1.字符与byte[]转换
string str = System.Text.Encoding.Default.GetString( byteArray );
byte[] byteArray = System.Text.Encoding.Default.GetBytes( str );
string str = System.Text.Encoding.ASCII.GetString ( byteArray );
byte[] byteArray = System.Text.Encoding.ASCII.GetBytes ( str );
2.插入Blob数据
byte[] imageData = File.ReadAllBytes(图片路径)
string insertSql = "insert into emailimage(image) values(@img)";
MySQLCommand cmd = New MySQLCommand(insertSql, conn);
MySQLParameter param = New MySQLParameter("@img", MySqlDbType.Blob);
param.Value = imageData;
cmd.Parameters.Add(param);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
3.C#实现导出Excel
////// DataTable导出到Excel /// /// DataTable类型的数据源 /// 需要导出的文件路径 public void dataTableToCsv(DataTable table, string file) { string title = ""; FileStream fs = new FileStream(file, FileMode.OpenOrCreate); StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default); for (int i = 0; i < table.Columns.Count; i++) { title += table.Columns[i].ColumnName + "t"; //栏位：自动跳到下一单元格 } title = title.Substring(0, title.Length - 1) + "n"; sw.Write(title); foreach (DataRow row in table.Rows) { string line = ""; for (int i = 0; i < table.Columns.Count; i++) { line += row[i].ToString().Trim() + "t"; //内容：自动跳到下一单元格 } line = line.Substring(0, line.Length - 1) + "n"; sw.Write(line); } sw.Close(); fs.Close(); }
////// DataTable导出到Excel /// /// DataTable类型的数据源 /// 文件类型 /// 文件名 public void CreateExcel(DataTable dt, string FileType, string FileName) { Response.Clear(); Response.Charset = "UTF-8"; Response.Buffer = true; Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); Response.AppendHeader("Content-Disposition", "attachment;filename="" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls""); Response.ContentType = FileType; string colHeaders = string.Empty; string ls_item = string.Empty; DataRow[] myRow = dt.Select(); int i = 0; int cl = dt.Columns.Count; for (int j = 0; j < dt.Columns.Count; j++) { ls_item += dt.Columns[j].ColumnName + "t"; //栏位：自动跳到下一单元格 } ls_item = ls_item.Substring(0, ls_item.Length - 1) + "n"; foreach (DataRow row in myRow) { for (i = 0; i < cl; i++) { if (i == (cl - 1)) { ls_item += row[i].ToString() + "n"; } else { ls_item += row[i].ToString() + "t"; } } Response.Output.Write(ls_item); ls_item = string.Empty; } Response.Output.Flush(); Response.End(); }
this.dataTableToCsv(dt, @"C:UsersAdminDesktop收听率" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".xls"); //调用函数
4.C#中二进制表示方法
//十进制转二进制 Console.WriteLine(Convert.ToString(69, 2)); //十进制转八进制 Console.WriteLine(Convert.ToString(69, 8)); //十进制转十六进制 Console.WriteLine(Convert.ToString(69, 16)); //二进制转十进制 Console.WriteLine(Convert.ToInt32(”100111101〃, 2)); //八进制转十进制 Console.WriteLine(Convert.ToInt32(”76〃, 8)); //十六进制转十进制 Console.WriteLine(Convert.ToInt32(”FF”, 16));
