// 新增用户信息
public static uint SQL_AddUser(KMS_USER info)
{
uint ulResult = 0;
try
{
String strconn = "Data Source=" + server + ";" + "port=" + port + ";" + "Database=" + database + ";" + "User Id=" + uid + ";" + "Password="******";" + "CharSet=utf8;";
MySqlConnection myconn = new MySqlConnection(strconn);
myconn.Open();
string query = "INSERT INTO user(_name,_sec_msg,_nickname,_cert_type,_address,_mobile,_email,_public_key) values(@_name,@_sec_msg,@_nickname,@_cert_type,@_address,@_mobile,@_email,@_public_key)";
MySqlParameter _name = new MySqlParameter("@_name", MySqlDbType.VarChar, 60);
_name.Value = info.strUserName;
MySqlParameter _sec_msg = new MySqlParameter("@_sec_msg", MySqlDbType.VarChar, 255);
_sec_msg.Value = info.strNote;
MySqlParameter _nickname = new MySqlParameter("@_nickname", MySqlDbType.VarChar, 60);
_nickname.Value = info.strUserName;
MySqlParameter _cert_type = new MySqlParameter("@_cert_type", MySqlDbType.VarChar, 1);
_cert_type.Value = (int)info.ulType;
MySqlParameter _mobile = new MySqlParameter("@_mobile", MySqlDbType.VarChar, 15);
_mobile.Value = info.strMobile;
MySqlParameter _address = new MySqlParameter("@_address", MySqlDbType.VarChar, 255);
_address.Value = info.strDepartment;
MySqlParameter _email = new MySqlParameter("@_email", MySqlDbType.VarChar, 60);
_email.Value = info.strEmail;
MySqlParameter _public_key = new MySqlParameter("@_public_key", MySqlDbType.Blob, 255);
_public_key.Value = info.pubKey.pbValue;
//create command and assign the query and connection from the constructor
MySqlCommand cmd = new MySqlCommand(query, myconn);
cmd.Parameters.Add(_name);
cmd.Parameters.Add(_sec_msg);
cmd.Parameters.Add(_nickname);
cmd.Parameters.Add(_cert_type);
cmd.Parameters.Add(_mobile);
cmd.Parameters.Add(_address);
cmd.Parameters.Add(_email);
cmd.Parameters.Add(_public_key);
//Execute command
cmd.ExecuteNonQuery();
//close connection
myconn.Close();
ulResult = 0;
}
catch (Exception ex)
{
#if __SQL_MSG__
DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message);
#endif
ulResult = (uint)eErrorCode.EStateErrorAddUser;
}
return ulResult;
}
// 查询用户信息
public static uint SQL_QueryUser(string strUserName,
string szIssueBeginTime,
string szIssueEndTime,
uint lPageSize, ulong lPageIndex,
/*IN/OUT*/ref ulong ulTotalCount,
/*OUT*/ref KMS_USER[] refInfoVector,
/*IN/OUT*/ref uint refInfoCount)
{
uint ulResult = 0;
string where = "";
bool add = false;
if (!add)
{
if (null != strUserName && 0 != strUserName.Length)
{
where += "_nickname='" + strUserName + "'";
add = true;
}
}
else
{
if (null != strUserName && 0 != strUserName.Length)
{
where += " and _nickname='" + strUserName + "'";
add = true;
}
}
if (!add)
{
if (null != szIssueBeginTime && 0 != szIssueBeginTime.Length)
{
where += "_DATETIME between '" + szIssueBeginTime + "' and '" + szIssueEndTime + "'";
add = true;
}
}
else
{
if (null != szIssueBeginTime && 0 != szIssueBeginTime.Length)
{
where += " and _DATETIME between'" + szIssueBeginTime + "' and '" + szIssueEndTime + "'";
add = true;
}
}
try
{
String strconn = "Data Source=" + server + ";" + "port=" + port + ";" + "Database=" + database + ";" + "User Id=" + uid + ";" + "Password="******";" + "CharSet=utf8;";
MySqlConnection myconn = new MySqlConnection(strconn);
myconn.Open();
MySqlCommand mycomm = new MySqlCommand("proc_query", myconn);
mycomm.CommandType = CommandType.StoredProcedure;
MySqlParameter[] para = new MySqlParameter[]{
new MySqlParameter("$fieldList",MySqlDbType.VarChar, 200),
new MySqlParameter("$TableList",MySqlDbType.VarChar, 200),
new MySqlParameter("$whereList",MySqlDbType.VarChar, 2000),
new MySqlParameter("$orderList",MySqlDbType.VarChar, 100),
new MySqlParameter("$pageSize",MySqlDbType.Int32, 1000),
new MySqlParameter("$pageIndex",MySqlDbType.Int64, 1000),
new MySqlParameter("$totalCount",MySqlDbType.Int64)
};
para[0].Value = "_nickname";
para[1].Value = "user";
para[2].Value = where;//"创建时间 between '2014-02-13 11:02:00' and '2014-02-13 11:18:00' and 版本ID=10";
para[3].Value = "";
para[4].Value = lPageSize;
para[5].Value = lPageIndex;
para[6].Value = ulTotalCount;
mycomm.Parameters.AddRange(para);
//用DataSet获取数据
//////////////////////////////////////////////////////////////////////////
MySqlDataAdapter da = new MySqlDataAdapter(mycomm);
DataSet MyDataSet = new DataSet();
da.Fill(MyDataSet);
DataTable tbl = MyDataSet.Tables[0];//获取第一张表
if (MyDataSet.Tables.Count == 2)
{
DataTable tb2 = MyDataSet.Tables[1];
string colName = tb2.Columns[0].ColumnName;
ulTotalCount = ulong.Parse(tb2.Rows[0][colName].ToString());
}
int i = 0;
foreach (DataRow row in tbl.Rows)
{
i++;
}
refInfoCount = (uint)i;
//close connection
myconn.Close();
ulResult = 0;
}
catch (Exception ex)
{
#if __SQL_MSG__
DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message);
#endif
ulResult = (uint)eErrorCode.EStateErrorQueryUser;
}
return ulResult;
}
void FunctionCreateAdmin()
{
ulong m_ulRecordCount = 0;
uint m_ulInfoCount = 0;
KMS_USER[] m_szInfoUser = new KMS_USER[10];
KMC_API.SQL_Init(textBoxIP.Text, textBoxPORT.Text, textBoxDBName.Text, textBoxDBPin.Text, "CertificateKMS");
KMC_API.ulResult = KMC_API.SQL_DataBaseExist();
if (0 != KMC_API.ulResult)
{
return;
}
//KMC_API.CAPI_KEY_SetStr("abcdg");
//StringBuilder buffer = new StringBuilder(300);
//uint tempPath = KMC_API.CAPI_KEY_GetStr(buffer);
//string path = buffer.ToString();
//if (tempPath == 0)
//{
// int errorcode = Marshal.GetLastWin32Error();
// Win32Exception win32expection = new Win32Exception(errorcode);
//}
KMC_API.ulResult = KMC_API.SQL_QueryUser(textBoxUserName.Text, null, null, 10, 0, ref m_ulRecordCount, ref m_szInfoUser, ref m_ulInfoCount);
if (0 != KMC_API.ulResult)
{
return;
}
if (m_ulInfoCount > 0)
{
KMC_API.ulResult = (uint)eErrorCode.EStateErrorAddUser;
goto EXIT;
}
StringBuilder strKeyOn = new StringBuilder(1024);
KMC_API.ulResult = KMC_API.CAPI_KEY_SetPin(strKeyOn, 0, textBoxPinOK.Text, textBoxPinOK.Text);
if (0 != KMC_API.ulResult)
{
goto EXIT;
}
OPT_ST_USB_META meta = new OPT_ST_USB_META();
meta.ulUSBMetaUseType = (uint)OPE_USB_META_USE_TYPE.OPE_USB_META_USE_TYPE_AUTH;
meta.ulUSBMetaManType = (uint)OPE_USB_META_MAN_TYPE.OPE_USB_META_MAN_TYPE_ADMIN;
//meta.szName = textBoxUserName.Text;
string s211 = textBoxUserName.Text;
meta.szName = new byte[256];
UTF8Encoding utf8 = new UTF8Encoding();
byte [] encodedBytes = utf8.GetBytes(textBoxUserName.Text);
Array.Copy(encodedBytes, 0, meta.szName, 0, encodedBytes.Length);
int ulRetry = 0;
KMC_API.ulResult = KMC_API.CAPI_KEY_SetMeta(strKeyOn, 0,ref meta, textBoxPinOK.Text, ref ulRetry); // 其他USBKey 传输
if (0 != KMC_API.ulResult)
{
goto EXIT;
}
OPT_ST_USB_META metaref = new OPT_ST_USB_META();
KMC_API.ulResult = KMC_API.CAPI_KEY_GetMeta(strKeyOn, 0, ref metaref); // 其他USBKey 传输
if (0 != KMC_API.ulResult)
{
goto EXIT;
}
KMC_API.ulResult = KMC_API.CAPI_KEY_GenKeyPair(strKeyOn, 0, textBoxPinOK.Text, ref ulRetry);
if (0 != KMC_API.ulResult)
{
goto EXIT;
}
byte [] btPK = new byte [64];
KMC_API.ulResult = KMC_API.CAPI_KEY_ExportPK(strKeyOn, 0,1, btPK);
if (0 != KMC_API.ulResult)
{
goto EXIT;
}
KMS_USER user = new KMS_USER();
//// 用户名
user.strUserName = textBoxUserName.Text;
//// 用户权限
user.ulType = OPE_USB_META_MAN_TYPE.OPE_USB_META_MAN_TYPE_ADMIN;
//
user.strDepartment = textBoxUnit.Text;
user.strMobile = textBoxPhone.Text;
user.strEmail = textBoxEmail.Text;
user.strNote = textBoxNote.Text;
user.pubKey.pbValue = btPK;
KMC_API.ulResult = KMC_API.SQL_AddUser(user);
if (0 != KMC_API.ulResult)
{
goto EXIT;
}
EXIT:
return ;
}
